planner: hinting a partial multi-valued index can leave no valid physical plan
- 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
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