MySQL Bugs: #121228: Boolean FTS: "phrase" word* misses rows in FTS cache until OPTIMIZE TABLE

Bug #121228	Boolean FTS: "phrase" word* misses rows in FTS cache until OPTIMIZE TABLE
Submitted:	3 Sep 13:25
Reporter:	Kieran Brahney	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	8,8.4,9	OS:	Any
Assigned to:		CPU Architecture:	Any

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