planner: unique partial index causes DISTINCT and GROUP BY to return wrong results
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
A unique partial index is treated as a globally unique key by logical optimization. When duplicate index-key values exist in rows outside the index predicate, TiDB incorrectly eliminates `DISTINCT` and aggregation operators and returns wrong results.
Partial index documentation: https://docs.pingcap.com/tidb/stable/sql-statement-create-index/#partial-indexes-new-in-v857
### 1. Minimal reproduce step (Required)
```sql
CREATE DATABASE partial_index_bug;
USE partial_index_bug;
CREATE TABLE t (
id BIGINT PRIMARY KEY,
domain VARCHAR(253) NOT NULL,
deleted_at DATETIME NULL,
UNIQUE KEY uk_domain_active (domain) WHERE deleted_at IS NULL
);
INSERT INTO t VALUES
(1, 'example.com', NULL),
(2, 'example.com', '2026-01-01'),
(3, 'example.com', '2026-01-02'),
(4, 'example.com', '2026-01-03');
SELECT COUNT(DISTINCT domain) AS distinct_domains FROM t;
SELECT DISTINCT domain FROM t;
SELECT domain, COUNT(*) AS cnt FROM t GROUP BY domain;
EXPLAIN FORMAT='brief' SELECT domain, COUNT(*) AS cnt FROM t GROUP BY domain;
```
All four rows are stored and have the same `domain`; only the first row satisfies the partial-index predicate.
The observed plan contains no aggregation operator:
```text
+-------------------+----------+-----------+---------------+-----------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------+----------+-----------+---------------+-----------------------------------------+
| Projection | 10000.00 | root | | partial_index_bug.t.domain, 1->Column#4 |
| └─TableReader | 10000.00 | root | | data:TableFullScan |
| └─TableFullScan | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------+----------+-----------+---------------+-----------------------------------------+
```
As an additional diagnostic, grouping by `CONCAT(domain, '')` returns the correct single group because that expression prevents the incorrect unique-key inference.
### 2. What did you expect to see? (Required)
```text
COUNT(DISTINCT domain) = 1
SELECT DISTINCT domain:
example.com
GROUP BY result:
example.com | 4
```
The optimizer should only use the uniqueness property of a unique partial index when the query predicate implies the index predicate (`deleted_at IS NULL`). For queries over the entire table or over rows outside the predicate, the indexed columns are not globally unique.
### 3. What did you see instead (Required)
```text
COUNT(DISTINCT domain) = 4
SELECT DISTINCT domain:
example.com
example.com
example.com
example.com
GROUP BY result:
example.com | 1
example.com | 1
example.com | 1
example.com | 1
```
This is a wrong-result issue: the logical optimizer appears to propagate the partial unique index as an unconditional unique key and removes semantically required `DISTINCT`/aggregation operators.
### 4. What is your TiDB version? (Required)
```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-07 08:10:56
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
```
Contributor guide
Research direction
Start by running the SQL reproduction and comparing the results and EXPLAIN plan with the expected output. Trace the logical optimizer's unique-key inference for the partial index, especially whether it checks that the query predicate implies deleted_at IS NULL. Done means DISTINCT and aggregation are retained for whole-table queries while uniqueness is used only when that predicate is implied, with regression coverage for the reported cases.
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
- 45/100