Description:
I found this while developing Super Speedy Search, a WooCommerce search plugin
(https://www.superspeedyplugins.com/product/super-speedy-search/). Shops reported products vanishing from search results straight after they were saved.
A boolean-mode MATCH should return the same rows whether a row's words are still in the FTS index cache or already synced to the auxiliary index tables. It does not once a phrase has been evaluated.
After a phrase ("...") or proximity ("..."@n) has been evaluated, every later wildcard term that is evaluated through the union path (no operator, >, <, ~, or any term inside a parenthesised group) or the difference path (-term*) misses rows that are only in the cache. The phrase itself, terms evaluated before it, non-wildcard terms and top-level +term* (the intersection path, which does not test query->flags) are unaffected.
Consequences in production: a product disappears from search straight after it is saved (its row stays cache-only until the next sync; we measured over 19 minutes on a quiet server), and a row that a -term* exclusion should remove is returned.
Order matters only as InnoDB evaluates the query: per expression list, operator-less terms first, then + terms, then - terms, each in textual order, with sub-expressions evaluated where they fall in that order.
Cause: fts_query_phrase_search() in storage/innobase/fts/fts0que.cc does not reset query->flags at func_exit, so the FTS_PHRASE / FTS_PROXIMITY flag leaks into every term evaluated later in the same query; fts_query_cache() and fts_query_difference() then take the exact-word lookup path for a wildcard term instead of fts_cache_find_wildcard(). This is still the case on the 8.0, 8.4 and trunk branches of mysql-server (checked 21 October 2026).
MariaDB had the same defect and fixed it as MDEV-41022
(https://jira.mariadb.org/browse/MDEV-41022), commit 6fd5edbbe202d93b99b16de7f182678018e40016.
Defaults throughout: innodb_ft_min_token_size 3, innodb_ft_cache_size 8000000,
innodb_ft_enable_stopword ON.
How to repeat:
-- Run as a user allowed to SET GLOBAL innodb_optimize_fulltext_only (only needed to show the
-- synced result). The rows are still in the FTS cache straight after the INSERT.
CREATE TABLE zz77_min (id INT PRIMARY KEY, txt TEXT, FULLTEXT KEY ft (txt)) ENGINE=InnoDB;
INSERT INTO zz77_min VALUES (1, 'usb c adapter'), (2, 'plain adapter');
-- While the rows are only in the FTS cache:
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('"usb c" (plain*)' IN BOOLEAN MODE);
-- expected 1,2 actual 1
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('+"usb c" +(adapter*)' IN BOOLEAN MODE);
-- expected 1 actual NULL
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('"usb c" -adapter*' IN BOOLEAN MODE);
-- expected NULL actual 1 (the exclusion is ignored)
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('+(adapter*) +"usb c"' IN BOOLEAN MODE);
-- 1 (correct: the same terms with the wildcard evaluated before the phrase)
-- Sync the cache to the auxiliary tables; the same queries are then correct:
SET GLOBAL innodb_optimize_fulltext_only=ON; OPTIMIZE TABLE zz77_min; SET GLOBAL innodb_optimize_fulltext_only=OFF;
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('"usb c" (plain*)' IN BOOLEAN MODE); -- 1,2
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('+"usb c" +(adapter*)' IN BOOLEAN MODE); -- 1
SELECT GROUP_CONCAT(id ORDER BY id) FROM zz77_min WHERE MATCH(txt) AGAINST('"usb c" -adapter*' IN BOOLEAN MODE); -- NULL
Suggested fix:
As in MariaDB commit 6fd5edbbe202: reset query->flags = 0 at func_exit of
fts_query_phrase_search(), so the phrase/proximity state cannot reach later terms.
Workaround for applications: wrap each operator-less wildcard term, in any query that contains a phrase, in its own required sub-expression: (+term*) instead of term*. +term* uses the intersection path, which looks the wildcard up in the cache correctly, and the one-term sub-expression unions its result into the enclosing list, so matched rows and relevance are unchanged on a synced index. Example: +"usb c" +((+adapter*)) returns 1 while cache-only.
