1

Does the order of the patterns under against affect something? Here is a table I have:

create table df_test
(
  t1 text,
  t2 text,
  FULLTEXT df_test_idx (t1 ASC, t2 ASC)
);

Here are the rows I have:

t1                    |  t2
--------------------------------------------
1word2 1sec71 5word5  |  word2 1sec8 6word6
word1 sec8 5word5     |  word2 sec7 word6
word1 sec8 5word5     |  word2 1sec17 word6
word1 sec7 5word5     |  word2 1sec8 6word6
word1 sec7 5word5     |  word2 sec8 word6
word1 sec7 5word5     |  word2 sec7 word6
sometrash9            |  sometrash10
sometrash7            |  sometrash8
sometrash5            |  sometrash6
sometrash3            |  sometrash4
sometrash13           |  sometrash14
sometrash11           |  sometrash12
sometrash1            |  sometrash2

The query select * from df_test where match(t1, t2) against('6word*' IN BOOLEAN MODE) returns 2 rows, which is expected:

t1                    | t2
-------------------------------------------
1word2 1sec71 5word5  | word2 1sec8 6word6
word1 sec7 5word5     | word2 1sec8 6word6

The query select * from df_test where match(t1, t2) against('"word1 sec8"' IN BOOLEAN MODE) also returns 2 rows, which is also expected:

t1                    | t2
-------------------------------------------
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

The query select * from df_test where match(t1, t2) against('"word1 sec8" 6word*' IN BOOLEAN MODE) is expected to merge the results but it returns the same 2 rows as above:

t1                    | t2
-------------------------------------------
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

Nevertheless, if I change the sequence of patterns, it returns as expected. The query select * from df_test where match(t1, t2) against('6word*"word1 sec8"' IN BOOLEAN MODE). The result:

t1                    | t2
-------------------------------------------
1word2 1sec71 5word5  | word2 1sec8 6word6
word1 sec7 5word5     | word2 1sec8 6word6
word1 sec8 5word5     | word2 sec7 word6
word1 sec8 5word5     | word2 1sec17 word6

Could someone please explain what I am missing?


UPDATE#1: The table is InnoDB

SQL to fill the table in:

INSERT INTO df_test
VALUES ('1word2 1sec71 5word5', 'word2 1sec8 6word6'),
       ('word1 sec8 5word5', 'word2 sec7 word6'),
       ('word1 sec8 5word5', 'word2 1sec17 word6'),
       ('word1 sec7 5word5', 'word2 1sec8 6word6'),
       ('word1 sec7 5word5', 'word2 sec8 word6'),
       ('word1 sec7 5word5', 'word2 sec7 word6'),
       ('sometrash9', 'sometrash10'),
       ('sometrash7', 'sometrash8'),
       ('sometrash5', 'sometrash6'),
       ('sometrash3', 'sometrash4'),
       ('sometrash13', 'sometrash14'),
       ('sometrash11', 'sometrash12'),
       ('sometrash1', 'sometrash2')

UPDATE#2: Here is the link to the bug opened with MySQL: https://bugs.mysql.com/bug.php?id=95434

  • MyISAM? or InnoDB? – Rick James Apr 30 at 17:08
  • Hey, it is InnoDB. – Denis Fatkhudinov May 1 at 7:22
  • Can you modify your Question to include the SQL for a test case that we can run? – Rick James May 1 at 19:45
  • Which SQL do you mean exactly? I posted SQL to create a table and each query to index. Do you want me to add an insert statement? – Denis Fatkhudinov May 1 at 23:05
  • Yes, insert the rows and perform the SELECT. Imagine you are receiving a bug report. You would like to have a test case that fails and that you can run against various versions of the product to see if it is a 'new' bug (ie, a 'regression') or one that has existed a long time. – Rick James May 2 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.