pingcap / pingcap/tidb

planner: FTS LIKE fallback can return false positives for boolean MATCH AGAINST

Open
#68,488 3 comments 0 reactions 0 assignees View on GitHub
feature/developing may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/critical sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.