Bug #121228 Boolean FTS: "phrase" word* misses rows in FTS cache until OPTIMIZE TABLE
Description:
In BOOLEAN MODE, an InnoDB full-text query that contains a quoted phrase followed by an un-prefixed wildcard term (for example "zzzz" aut*) fails to match rows whose words are still in the in-memory FTS index cache, i.e. rows inserted or updated since the cache was last synced to the auxiliary index tables. The same rows are matched once they have been flushed to disk (OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON, cache size limit reached, or server restart).
The bug is triggered by the ordering of the terms only. The following variants of the same query all return the row correctly:
* the wildcard term placed before the phrase: aut* "zzzz"
* the wildcard term marked as required: "zzzz" +aut*
* an exact word instead of a wildcard: "zzzz" auth
* the wildcard term on its own: aut*
How to repeat:
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, txt TEXT, FULLTEXT(txt)) ENGINE=InnoDB;
INSERT INTO t (txt) VALUES ('auth code out');
SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" aut*' IN BOOLEAN MODE); -- empty (wrong)
SELECT id FROM t WHERE MATCH(txt) AGAINST('aut* "zzzz"' IN BOOLEAN MODE); -- 1
SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" +aut*' IN BOOLEAN MODE); -- 1
SET GLOBAL innodb_optimize_fulltext_only=ON; OPTIMIZE TABLE t;
SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" aut*' IN BOOLEAN MODE); -- 1 once synced to disk
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: In BOOLEAN MODE, an InnoDB full-text query that contains a quoted phrase followed by an un-prefixed wildcard term (for example "zzzz" aut*) fails to match rows whose words are still in the in-memory FTS index cache, i.e. rows inserted or updated since the cache was last synced to the auxiliary index tables. The same rows are matched once they have been flushed to disk (OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON, cache size limit reached, or server restart). The bug is triggered by the ordering of the terms only. The following variants of the same query all return the row correctly: * the wildcard term placed before the phrase: aut* "zzzz" * the wildcard term marked as required: "zzzz" +aut* * an exact word instead of a wildcard: "zzzz" auth * the wildcard term on its own: aut* How to repeat: CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, txt TEXT, FULLTEXT(txt)) ENGINE=InnoDB; INSERT INTO t (txt) VALUES ('auth code out'); SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" aut*' IN BOOLEAN MODE); -- empty (wrong) SELECT id FROM t WHERE MATCH(txt) AGAINST('aut* "zzzz"' IN BOOLEAN MODE); -- 1 SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" +aut*' IN BOOLEAN MODE); -- 1 SET GLOBAL innodb_optimize_fulltext_only=ON; OPTIMIZE TABLE t; SELECT id FROM t WHERE MATCH(txt) AGAINST('"zzzz" aut*' IN BOOLEAN MODE); -- 1 once synced to disk