pingcap / pingcap/tidb

planner: hinting a partial multi-valued index can leave no valid physical plan

Open
#71,374 0 comments 0 reactions 0 assignees View on GitHub
affects-8.5 component/executor may-affects-25.10 may-affects-26.3 may-affects-26.9 may-affects-7.5 may-affects-8.1 severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

`USE INDEX` / `FORCE INDEX` on an index that is both partial and multi-valued can make a valid query fail when the query implies the partial predicate but cannot generate a usable MV IndexMerge path.

```sql
CREATE TABLE t (
id INT PRIMARY KEY,
a INT,
j JSON,
INDEX pmv ((CAST(j AS UNSIGNED ARRAY))) WHERE a > 0
);

INSERT INTO t VALUES
(1, 1, '[1,2]'), (2, 2, '[2,3]'), (3, 0, '[1,3]'),
(4, -1, '[2]'), (5, 1, '[]'), (6, 1, NULL),
(7, NULL, '[1]'), (8, 1, '[1,1]');

SELECT * FROM t USE INDEX(pmv) WHERE a > 0;
```

`EXPLAIN` of this query also fails. Replacing `USE INDEX` with `FORCE INDEX` reproduces the same error.

Two additional reproduced cases:

```sql
-- Empty-array containment cannot provide a safe MV access path.
SELECT * FROM t USE INDEX(pmv)
WHERE a > 0 AND JSON_CONTAINS(j, '[]');

-- Even with a usable membership predicate, disabling IndexMerge triggers it.
SET tidb_enable_index_merge = OFF;
SELECT * FROM t USE INDEX(pmv)
WHERE a > 0 AND 1 MEMBER OF(j);
```

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

The query should execute successfully, falling back to a table scan when the hinted MV index cannot be used. The first query should return IDs `1, 2, 5, 6, 8` (order unspecified), including the row containing an empty array.

Verified controls on the same source build:

- A non-partial MV index with the same `USE INDEX` query falls back to a table scan.
- An ordinary partial index, `INDEX p(b) WHERE a > 0`, supports `WHERE a > 0` with `USE INDEX(p)` / `FORCE INDEX(p)` through `IndexLookUp` + `IndexFullScan`, even without a predicate on `b`.
- If the query does not imply the partial predicate, e.g. `WHERE a >= 0`, the hinted partial index is discarded and a table scan remains available.

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

```text
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query
```

The failure occurs during planning, including plain `EXPLAIN`.

Likely cause: `DataSource.CheckPartialIndexes` sets `partialIndexUsedHint` once the partial predicate is satisfied and the index is forced, then removes all non-forced access paths, including the table-scan fallback. However, `convertToIndexScan` rejects MV indexes; they require a usable IndexMerge path. If no such path is generated, there is no valid physical plan left. This makes a satisfied partial predicate insufficient to decide that the hinted MV index can replace the fallback.

Relevant code: `pkg/planner/core/operator/logicalop/logical_datasource.go` (`CheckPartialIndexes`), `pkg/planner/core/indexmerge_path.go` (`generateIndexMergePath`, `cleanAccessPathForMVIndexHint`), and `pkg/planner/core/find_best_task.go` (`convertToIndexScan`).

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

- Latest `master` (checked at `51263506a5005ca119f3a191a21c3ea91a419b16`).
- TiDB 8.5 (`release-8.5`, checked at `2787a8f1136b7e5a03b1aaf8d1597513d339a4a7`).

The same problematic planner logic is present on both branches. Runtime reproduction was performed on a local master-based build (`f93f8e5ded93d26754794f520ff48d30b6d4a2a7`) with UniStore; the 8.5 assessment is based on source inspection.

Contributor guide

Open the contributing guide

Research direction

Reproduce the failing SQL and start in pkg/planner/core/operator/logicalop/logical_datasource.go at CheckPartialIndexes. Then trace generateIndexMergePath and cleanAccessPathForMVIndexHint in pkg/planner/core/indexmerge_path.go, plus convertToIndexScan in pkg/planner/core/find_best_task.go. Done means hinted partial multi-valued-index queries no longer lose the table-scan fallback and the supplied queries execute or EXPLAIN successfully.

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
Active
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.