expression: collation will cause unexpected Constant.HashCode collision then cause wrong results
- 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
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