Correlated subqueries on ROLLUP super-aggregate rows use a wrong correlation binding (silent wrong results)
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
In TiDB v8.5.7, when a query contains `GROUP BY ... WITH ROLLUP` together with a
**correlated subquery** (scalar / EXISTS / COUNT(*)), the **super-aggregate row**
(`GROUPING() = 1`, whose grouping key is the structural marker NULL) produces a subquery
result equal to that of **some real group** (in our repro, the first group) instead of the
correct "no match" result (`NULL` / `0` / `0`).
Regular group rows and data-NULL groups are evaluated correctly. The wrong result is
**silent** and occurs under the **default configuration**.
Note: because the super-row's result appears to be polluted by another group's evaluation,
datasets that happen to contain a data-NULL group whose (correctly empty) result is reused
can mask the bug — which is likely why earlier testing missed it.
### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE a (id INT PRIMARY KEY, c0 INT);
CREATE TABLE b (id INT PRIMARY KEY, c0 INT, c1 INT);
INSERT INTO a VALUES (1,10),(2,20),(3,30);
INSERT INTO b VALUES (1,10,100),(2,20,200),(3,20,300);
-- (1) Scalar subquery: super-row should be NULL; TiDB returns 100 (group c0=10's value)
SELECT c0, GROUPING(c0) g, (SELECT b.c1 FROM b WHERE b.c0 = a.c0 LIMIT 1) AS s
FROM a GROUP BY c0 WITH ROLLUP;
-- TiDB: (NULL, 1, 100) ← WRONG
-- MySQL/PG/DuckDB: (NULL, 1, NULL)
-- (2) EXISTS: super-row should be 0; TiDB returns 1
SELECT c0, GROUPING(c0) g, EXISTS(SELECT 1 FROM b WHERE b.c0 = a.c0) AS ex
FROM a GROUP BY c0 WITH ROLLUP;
-- TiDB: (NULL, 1, 1) ← WRONG (oracle: (NULL, 1, 0))
-- (3) COUNT(*): super-row should be 0; TiDB returns 1 (first group's count)
SELECT c0, GROUPING(c0) g, (SELECT COUNT(*) FROM b WHERE b.c0 = a.c0) AS cnt
FROM a GROUP BY c0 WITH ROLLUP;
-- TiDB: (NULL, 1, 1) ← WRONG (oracle: (NULL, 1, 0))
-- (4) EXISTS in WHERE: super-aggregate row is wrongly retained
SELECT c0 FROM a WHERE EXISTS(SELECT 1 FROM b WHERE b.c0 = a.c0) GROUP BY c0 WITH ROLLUP;
-- TiDB output includes the c0=NULL super-row ← WRONG (oracle filters it out)
```
### 2. What did you expect to see? (Required)
For super-aggregate rows, the correlated predicate `b.c0 = a.c0` evaluates `b.c0 = NULL`
→ UNKNOWN → no match: scalar subquery → NULL, EXISTS → 0 (row filtered in WHERE),
COUNT(*) → 0.
### 3. What did you see instead (Required)
ROLLUP super-aggregate rows are produced after per-group processing; their marker-NULL
grouping keys are not data values. The correlated subquery's Apply/SemiApply evaluation
for these rows appears to reuse a stale or sibling correlation binding (the first group's
value in our repro) instead of the marker NULL, yielding a match.
### 4. What is your TiDB version? (Required)
TiDB v8.5.7 (tiup playground), default configuration, macOS arm64.
Contributor guide
Research direction
Reproduce the four SQL cases against TiDB v8.5.7, then trace the ROLLUP super-aggregate rows through the correlated subquery's Apply/SemiApply evaluation. Done means marker-NULL rows use the correct correlation binding: scalar results are NULL, EXISTS and COUNT(*) return 0, and the WHERE case filters the super-row.
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
- 48/100