Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.3.2
-
Unexpected results
Description
### Description
|
|
|
When a correlated subquery references a column from an outer query that uses `GROUP BY ... WITH ROLLUP`, the super-aggregate (ROLLUP) row's NULL value in the GROUP BY column is incorrectly treated as a data NULL rather than a structural marker NULL. This causes the correlated subquery to return incorrect results on the ROLLUP row.
|
|
|
The SQL standard requires that `NULL = NULL` evaluates to UNKNOWN (i.e., no match), so a correlated subquery on the ROLLUP row (where the correlation column is NULL) should return 0 / NULL / FALSE. However, MariaDB incorrectly returns a non-zero count or non-NULL value, indicating that the marker NULL is being matched against actual data.
|
|
|
### Minimal Reproduction
|
|
|
```sql
|
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT);
|
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT);
|
INSERT INTO t0 VALUES (1, 10), (2, 20), (3, 30);
|
INSERT INTO t1 VALUES (1, 10), (2, 20), (3, 20), (4, 30);
|
|
|
SELECT t0.c0, (SELECT COUNT(*) FROM t1 WHERE t1.c0 = t0.c0) AS cnt
|
FROM t0 GROUP BY t0.c0 WITH ROLLUP;
|
```
|
|
|
### Expected Result
|
|
|
The ROLLUP super-aggregate row (c0=NULL) should have `cnt=0`, because `t1.c0 = NULL` evaluates to UNKNOWN and matches no rows:
|
|
|
```
|
c0 cnt
|
10 1
|
20 2
|
30 1
|
NULL 0 ← correct
|
```
|
|
|
### Actual Result
|
|
|
MariaDB returns `cnt=1` on the ROLLUP row, indicating the marker NULL is incorrectly matched against data:
|
|
|
```
|
c0 cnt
|
10 1
|
20 2
|
30 1
|
NULL 1 ← WRONG
|
```
|
Attachments
Issue Links
- relates to
-
MDEV-19039 Wrong result from query, using window function and GROUP BY .. WITH ROLLUP
-
- Stalled
-
-
MDEV-22269 GROUPING() function
-
- In Testing
-