pingcap / pingcap/tidb

planner: MATCH AGAINST can fail in pessimistic RC transactions when isolation read engines include TiKV

Open
#68,703 0 comments 0 reactions 0 assignees View on GitHub
affects-9.0 severity/moderate 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)

Prerequisite: the table has an available TiCI full-text access path. In the local regression test this is mocked by marking the TiFlash replica available for the table.

```sql
create table obj_new (
id bigint primary key,
label text,
fulltext index idx_label(label)
);

-- In a real cluster, make the full-text/TiCI path available for the table.
-- For example, set a TiFlash replica and wait until it is available if that is required by the setup.
-- alter table obj_new set tiflash replica 1;

set @@sql_mode = '';
set @@tidb_isolation_read_engines = 'tikv,tiflash,tidb';
set @@tx_isolation = 'READ-COMMITTED';
begin pessimistic;

select id from obj_new
where match(label) against ('"BFACPXUZXEIN"' in boolean mode);

rollback;
```

The same planner path can also be reproduced with `EXPLAIN`, which avoids executing the TiCI request:

```sql
set @@sql_mode = '';
set @@tidb_isolation_read_engines = 'tikv,tiflash,tidb';
set @@tx_isolation = 'READ-COMMITTED';
begin pessimistic;

explain format = 'brief' select id from obj_new
where match(label) against ('"BFACPXUZXEIN"' in boolean mode);

rollback;
```

A local regression test was added while investigating this:

```bash
make failpoint-enable && (
cd pkg/planner/core/casetest/tici
GOCACHE=/tmp/tidb-go-build go test -run TestTiCIIsolationReadEnginesFiltersFTSPath --tags=intest
rc=$?
cd /Users/tailingxiang/go/src/github.com/pingcap/tidb
make failpoint-disable
exit $rc
)
```

The same query succeeds in the same `READ-COMMITTED` pessimistic transaction if `tikv` is removed from `tidb_isolation_read_engines`:

```sql
set @@tidb_isolation_read_engines = 'tiflash,tidb';
set @@tx_isolation = 'READ-COMMITTED';
begin pessimistic;

explain format = 'brief' select id from obj_new
where match(label) against ('"BFACPXUZXEIN"' in boolean mode);

rollback;
```

### 2. What did you expect to see? (Required)

The query should compile successfully in a pessimistic `READ-COMMITTED` transaction when the required TiCI full-text access path is available. Including `tikv` in `tidb_isolation_read_engines` should not make the planner remove the only valid TiCI access path.

### 3. What did you see instead (Required)

The query fails during SQL compilation:

```text
[planner:1815]Internal : No access path for table 'obj_new' is found with 'tidb_isolation_read_engines' = 'tikv,tiflash,tidb', valid values can be 'tici'. Please check tiflash replica.
```

Based on local code inspection, the failure appears to come from this path:

1. In a pessimistic `READ-COMMITTED` transaction, `PlanBuilder.Init` marks the statement as `isForUpdateRead` via `SessionVars.IsPessimisticReadConsistency()`.
2. `findBestTask4LogicalDataSource` runs the late `FilterPathByIsolationRead` branch only when `tidb_isolation_read_engines` contains `tikv`.
3. `MATCH ... AGAINST` over the full-text index keeps only the TiCI path and sets the path store type to `tici`.
4. `tidb_isolation_read_engines` currently accepts `tikv,tiflash,tidb`, but not `tici`, so the TiCI path is filtered out and no access path remains.

This also explains why the same query can succeed in the same pessimistic `READ-COMMITTED` transaction when `tidb_isolation_read_engines = 'tiflash,tidb'`: the late filtering branch guarded by `isolationReadEnginesHasTiKV` is not entered.

### 4. What is your TiDB version? (Required)

The reporter's exact cluster version is not available in this issue yet. The minimal regression test above was verified on current master:

```text
bd92bb9c1dd08d9d702afa024d3462b226904a7a planner: use TiCI FTS row count for upper plan stats (#68620)
```

Suggested labels: `type/bug`, `component/planner`, `may-affects-9.0`.

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.