planner: FTS LIKE fallback can return false positives for boolean MATCH AGAINST
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
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
```sql
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:
```text
Selection: ifnull(ilike(test.t.b, "%hello%", 92), 0)
TableFullScan on table t
```
With alternative logical plans disabled, the plan uses native FTS pushdown:
```text
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:
```text
+---+-----------+
| 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 https://github.com/pingcap/tidb/pull/68383.
Contributor guide
Assessment
This issue has not been assessed yet.