pingcap / pingcap/tidb

expression: collation will cause unexpected Constant.HashCode collision then cause wrong results

Open
#70,645 1 comment 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-6.5 affects-7.5 affects-8.1 affects-8.5 component/expression found-by-ai severity/major sig/execution sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE t_hash(
id int primary key,
c varchar(10) collate utf8mb4_general_ci,
j json,
k int,
index idx_mv(c, (cast(j->'$.a' as signed array))),
index idx_k(k)
);
INSERT INTO t_hash VALUES
(1,'A','{"a":[1]}',1),
(2,'a','{"a":[1]}',1),
(3,'B','{"a":[1]}',1),
(4,'A','{"a":[2]}',1),
(5,'a','{"a":[2]}',1);

-- wrong: returns [1,2]; correct: [1]
SELECT /*+ use_index_merge(t_hash, idx_mv, idx_k) */ id
FROM t_hash
WHERE c = 'A' collate utf8mb4_general_ci
AND c = 'A' collate utf8mb4_bin
AND 1 member of (j->'$.a')
AND k = 1
ORDER BY id;

-- wrong: removes ids 1 AND 2; correct: only id 1
DELETE /*+ use_index_merge(t_hash, idx_mv, idx_k) */
FROM t_hash
WHERE c = 'A' collate utf8mb4_general_ci
AND c = 'A' collate utf8mb4_bin
AND 1 member of (j->'$.a')
AND k = 1;
```

The same predicate pair is also wrong in the **default plan (no hint)**: `PropagateConstant → RemoveDupExprs` deduplicates the two predicates by `Expression.HashCode()` and keeps only one, so the stronger `utf8mb4_bin` predicate is never evaluated.

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

- SELECT returns `[1]` (only rows whose `c` equals `'A'` under both `utf8mb4_general_ci` and `utf8mb4_bin`, plus the MV/k predicates).
- DELETE removes only id `1`.

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

- SELECT returns `[1,2]`: id 2 (`c='a'`) leaks because `'a'='A'` is true under `utf8mb4_general_ci` but false under `utf8mb4_bin`, and the `utf8mb4_bin` predicate is silently dropped.
- DELETE removes ids `1` and `2`.
- `EXPLAIN` shows the `utf8mb4_bin` predicate is not re-checked above the index-merge (`IndexRangeScan` on `idx_mv` covers only the ci-side range `["A" 1]`, which admits `'a'`).

Mechanism: `generateMVIndexMergePartialPaths4And`/`generateANDIndexMerge4ComposedIndex` (pkg/planner/core/indexmerge_path.go) register index-merge access filters in `usedAccessCondsMap[string(accessF.HashCode())]` and drop every predicate whose HashCode is already covered. `Expression.HashCode()` is not a semantic equality: `Constant.HashCode` encodes only the raw value bytes (collation omitted), so `c='A' collate utf8mb4_general_ci` and `c='A' collate utf8mb4_bin` hash identically and the stronger predicate is marked "covered" and never re-checked. The same producer defect (`RemoveDupExprs` in pkg/expression, `PropagateConstant`) affects the default plan.

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

```
Release Version: v9.0.0-beta.2.pre-1927-g13282a8bd0-dirty
Git Commit Hash: ca95cc55e1b
```

Contributor guide

Open the contributing guide

Research direction

Reproduce the SQL case first and confirm the incorrect SELECT and DELETE results. Read pkg/planner/core/indexmerge_path.go, especially generateMVIndexMergePartialPaths4And and generateANDIndexMerge4ComposedIndex, then inspect RemoveDupExprs in pkg/expression and PropagateConstant. Done means predicates with different collations are not incorrectly treated as duplicates or covered, and both queries return only id 1.

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
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.