pingcap / pingcap/tidb

planner: unique partial index causes DISTINCT and GROUP BY to return wrong results

Open
#70,261 0 comments 0 reactions 0 assignees View on GitHub
affects-8.5 contribution severity/critical sig/planner type/bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.