Description:
Depending on the sequence of terms in the boolean mode different results are returned. Wildcard and double quotes are used in the query.
How to repeat:
SQL to create a table:
create table df_test
(
t1 text,
t2 text,
FULLTEXT df_test_idx (t1 ASC, t2 ASC)
);
Insert test data statement:
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')
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