pingcap / pingcap/tidb

Partial index implication check can choose an unsafe access path and return wrong results

Open
#69,779 2 comments 0 reactions 0 assignees View on GitHub
affects-8.5 found-by-ai impact/wrong-result severity/critical sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

TiDB can choose a partial index when the query predicate does not imply the partial-index predicate. The query then silently misses rows that are not present in the partial index.

This is a planner correctness issue, not index corruption: the partial index contains exactly the rows allowed by its predicate, and `ADMIN CHECK TABLE` passes. The planner must reject this access path unless it can prove:

```
query predicate => partial-index predicate
```

## Minimal Reproduction

This reproduces on current master `13282a8bd06bd33324a4dbfd3c1c03685f3cd9aa` with pseudo statistics and no test-only injection:

```sql
DROP DATABASE IF EXISTS partial_index_wrong_result;
CREATE DATABASE partial_index_wrong_result;
USE partial_index_wrong_result;

CREATE TABLE t (
id INT PRIMARY KEY,
a INT NOT NULL,
b INT NOT NULL,
INDEX pi(b) WHERE a < 3
);

INSERT INTO t VALUES
(1, 0, 1),
(2, 1, 2),
(3, 2, 3),
(4, 3, 4),
(5, 10, 5);

EXPLAIN FORMAT='brief'
SELECT id, a, b
FROM t
WHERE a >= 0
ORDER BY b
LIMIT 5;

SELECT id, a, b
FROM t IGNORE INDEX(pi)
WHERE a >= 0
ORDER BY b
LIMIT 5;

SELECT id, a, b
FROM t
WHERE a >= 0
ORDER BY b
LIMIT 5;

ADMIN CHECK TABLE t;
```

The query predicate `a >= 0` does not imply the partial-index predicate `a < 3`.

Expected result for both SELECT statements:

```
1 0 1
2 1 2
3 2 3
4 3 4
5 10 5
```

Actual default plan/result observed:

```
IndexLookUp
IndexFullScan(Build) table:t,index:pi(b),keep order:true,stats:pseudo
Selection(Probe) ge(t.a,0)

1 0 1
2 1 2
3 2 3
```

The `IGNORE INDEX(pi)` control returns all five rows. `USE INDEX(pi)` and `FORCE INDEX(pi)` also return only the two rows in the partial index. `ADMIN CHECK TABLE t` produces no error, because the stored index contents are internally consistent.

## User Impact

A normal query can silently return an incomplete result set when the optimizer prefers the partial index, for example because the index satisfies `ORDER BY b` and the table/index statistics are pseudo or incomplete. This can affect application reads without an SQL error, and an explicit index hint makes the same unsafe path deterministic.

Running `ANALYZE TABLE` may change the chosen plan for this small reproduction, but statistics must not determine whether an access path is semantically valid. Any plan that scans `pi` for `WHERE a >= 0` is incorrect.

## Suspected Root Cause

The relevant path is:

- `pkg/planner/core/operator/logicalop/logical_datasource.go:815-817` parses the stored partial-index condition and calls `partidx.CheckConstraints`.
- `pkg/planner/core/partidx/check_constraint.go:92-128` builds ranges for the partial predicate and query filters, then accepts the index when the union appears equal to the predicate range.

For the reproduction, the raw parsed partial predicate `a < 3` is passed directly to `ranger.BuildColumnRange`. In the planner probe, that first proof range is `[-inf,+inf]`; the same expression after the normal predicate normalization path produces `[-inf,3)`. The proof therefore incorrectly treats `a >= 0` as satisfying `a < 3`.

The proof checker should either normalize the metadata predicate using the same semantic/type normalization as query predicates or use a proof operation that cannot widen an unsupported/under-normalized predicate into a full range.

## Validation

- The table scan and `IGNORE INDEX(pi)` are the reference result.
- The default plan uses `pi` and returns fewer rows.
- `USE/FORCE INDEX(pi)` produces the same incomplete result.
- `ADMIN CHECK TABLE t` is green.
- No TiKV failure, malformed data, or failpoint is required.

Contributor guide

Open the contributing guide

Research direction

Reproduce the wrong result using the SQL case in the issue, then inspect pkg/planner/core/operator/logicalop/logical_datasource.go:815-817 and pkg/planner/core/partidx/check_constraint.go:92-128. Compare how the stored partial-index predicate and query filters are normalized before ranger.BuildColumnRange. Done means the planner rejects the unsafe partial-index path while preserving valid implications, with the table scan and IGNORE INDEX results as references.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.