Repository navigation
planner: FTS LIKE fallback can return false positives for boolean MATCH AGAINST #68488
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
This was found while testing the feature/fts branch after the FTS LIKE fallback changes. This does not affect master.
standard parser
create table t(a int primary key, b varchar(100), fulltext index(b));
insert into t values(1, 'hellotidb');
set @@tidb_opt_enable_alternative_logical_plans = on;
select * from t where match(b) against ('hello' in boolean mode);
explain select * from t where match(b) against ('hello' in boolean mode);
set @@tidb_opt_enable_alternative_logical_plans = off;
select * from t where match(b) against ('hello' in boolean mode);
explain select * from t where match(b) against ('hello' in boolean mode);ngram parser
set @@global.ngram_token_size=3;
CREATE TABLE `t2` (
`a` int NOT NULL,
`b` varchar(100) DEFAULT NULL,
PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
FULLTEXT INDEX `b`(`b`) WITH PARSER NGRAM
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
mysql> select /*+ ignore_index(t2,b)*/ * from t2 where match(b) against('he' in boolean mode);
+---+-----------+
| a | b |
+---+-----------+
| 1 | hellotidb |
+---+-----------+
mysql> select * from t2 where match(b) against('he' in boolean mode);
Empty set (0.14 sec)
With alternative logical plans enabled, the plan is rewritten to an ILIKE fallback:
Selection: ifnull(ilike(test.t.b, "%hello%", 92), 0)
TableFullScan on table t
With alternative logical plans disabled, the plan uses native FTS pushdown:
IndexRangeScan on fulltext index b(b), search func: fts_match_word("hello", test.t.b)
2. What did you expect to see? (Required)
The LIKE fallback should not change the result semantics of boolean MATCH ... AGAINST queries. In this case, searching for the word hello should not match hellotidb if native FTS tokenization treats it as a different word.
If the fallback cannot preserve native FTS word-boundary/tokenization semantics, it should not be selected for this query shape.
3. What did you see instead (Required)
With tidb_opt_enable_alternative_logical_plans = on, TiDB returns one row:
+---+-----------+
| a | b |
+---+-----------+
| 1 | hellotidb |
+---+-----------+
With tidb_opt_enable_alternative_logical_plans = off, native FTS returns an empty result set.
The fallback uses ILIKE '%hello%', which performs substring matching and can return false positives compared with full-text word matching.
4. What is your TiDB version? (Required)
feature/fts branch after PR #68383.
Activity
AilinKid commentedon May 22, 2026
Thanks for the report. This is a valid limitation of the current
MATCH ... AGAINST->ILIKEfallback.The fallback currently lowers a supported FTS term to a substring predicate such as
ILIKE '%hello%'. That can preserve some direct boolean predicate behavior, but it does not preserve native FTS tokenization / word-boundary semantics. As shown here,hellocan matchhellotidbthrough the fallback while the native FTS path treats it as a different token and returns no rows.So for this case, the fallback should not be selected when the native FTS path is viable. We should either:
Given the current implementation uses substring
ILIKE, I think this issue should be treated as a correctness gap in the fallback rule rather than just a costing problem.kennedy8312 commentedon Jul 16, 2026
/sig planner
/severity critical
kennedy8312 commentedon Jul 16, 2026
tidb-triage-bot AI rationale