expression: IN-list deduplication causes order-sensitive wrong results for signed/unsigned literals
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
**Affected versions**: master (since #61249) and release-8.5 v8.5.4+ (cherry-pick #63516). Found during a PR-by-PR review of release-8.5.
### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE t1 (a BIGINT UNSIGNED, b INT);
INSERT INTO t1 VALUES (18446744073709551615, 1), (1, 2), (0, 3);
-- (a) returns empty (wrong)
SELECT a, b FROM t1 WHERE a IN (-1, 18446744073709551615);
-- (b) same values, reversed order: returns the row (correct)
SELECT a, b FROM t1 WHERE a IN (18446744073709551615, -1);
```
A signed column with an unsigned literal first is also affected:
```sql
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (-1);
SELECT * FROM t2 WHERE a IN (18446744073709551615, -1); -- empty (wrong)
SELECT * FROM t2 WHERE a IN (-1, 18446744073709551615); -- returns -1 (correct)
```
### 2. What did you expect to see? (Required)
Both orders must return the same rows. For `t1`, both (a) and (b) should return `(18446744073709551615, 1)` — in MySQL semantics `-1` compared against `BIGINT UNSIGNED` converts to `18446744073709551615`, so it matches. For `t2`, both queries should return `-1`.
### 3. What did you see instead (Required)
The result depends on the order of the IN list: one order silently drops matching rows (wrong result). `EXPLAIN` shows the pushed-down expression itself is truncated — the colliding literal is discarded:
```
EXPLAIN SELECT * FROM t1 WHERE a IN (-1, 18446744073709551615);
-- Selection in(test.t1.a, -1) ← 18446744073709551615 is gone
```
### 4. What is your TiDB version? (Required)
Reproduced on v8.5.7 (release-8.5, git 1fdc13626a). Root cause confirmed by code inspection in master @ 6f5bfe198f (2026-08-01), which carries identical code.
### Root cause analysis
#61249 deduplicates constant IN args in `buildHashMapForConstArgs` (`pkg/expression/builtin_other.go`) using `map[int64]bool` keyed by the **raw int64 value** (first occurrence wins), and then truncates `b.args` to the deduplicated set. `-1` (signed) and `18446744073709551615` (unsigned) have the same int64 representation `-1`, so they collide: only the first literal is kept — with its own signedness flag — and the other is removed from the expression entirely (hence the truncated push-down shown by EXPLAIN).
In `builtinInIntSig.evalInt`, a hashSet hit with mismatched unsigned flags and `arg0 < 0` falls through as no-match, so the row is dropped. Before #61249 the hashSet entry was last-write-wins and `b.args` was never truncated, so order (a) above evaluated correctly — this specific order is a regression introduced by the deduplication, and the physical truncation of the expression is new as well. (The underlying single-slot-per-int64 hash design is older, so the reversed order was already broken before; the fix should address the collision itself, not just the dedup order.)
Suggested direction: make the dedup/hash key signedness-aware (e.g. key on the `(value, isUnsigned)` pair, or keep both colliding entries) so that `-1` and `18446744073709551615` are not treated as the same argument.
Related: #61246 (feature request), #61249 (master PR), #63516 (release-8.5 cherry-pick).
Contributor guide
Research direction
Start in pkg/expression/builtin_other.go, reading buildHashMapForConstArgs and builtinInIntSig.evalInt to trace how signedness and raw int64 values are handled. Reproduce the two t1 and t2 queries from the report, then add or run coverage for both IN-list orders. Done means colliding signed and unsigned literals are retained distinctly and both orders return the expected rows.
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
- Clearly specified
- Newbie friendliness
- 68/100