Bug #121011 subquery_to_derived causes wrong result with GROUP BY ... WITH ROLLUP

• Submitted: 27 Jul 9:52
• Modified: 27 Jul 14:18
• Reporter: Jacob Ding
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: MacOS
• CPU Architecture: ARM
• Tags: logical-bug

[27 Jul 9:52] Jacob Ding

Description:
```sql
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 INT);
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 INT);
INSERT INTO t0 VALUES (1,NULL,5),(2,10,5),(3,20,5);
INSERT INTO t1 VALUES (1,10,100),(2,20,200);

SET SESSION optimizer_switch = 'subquery_to_derived=off';
SELECT t0.c0, (SELECT COUNT(*) FROM t1 WHERE t1.c0 = t0.c0) AS cnt
FROM t0 GROUP BY t0.c0 WITH ROLLUP;
-- (NULL,0), (10,1), (20,1), (NULL,0)

SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0, (SELECT COUNT(*) FROM t1 WHERE t1.c0 = t0.c0) AS cnt
FROM t0 GROUP BY t0.c0 WITH ROLLUP;
-- (NULL,0), (10,1), (20,1), (NULL,1)
```

The ROLLUP super-aggregate row (last row, c0=NULL) has cnt=0 by default
but cnt=1 with subquery_to_derived=on. The marker NULL of the ROLLUP row
is incorrectly treated as a data NULL matching t1.c0=NULL.

How to repeat:
Tested on MySQL 8.4.10 and 9.7.1 (Homebrew, macOS arm64). The bug is
reproducible on both versions. Execute the above test case; the last
row of the second SELECT returns cnt=1 instead of cnt=0.

[27 Jul 14:18] Chaithra Marsur Gopala Reddy

Hi Jacob ding,

Thank you for the test case. Verified as described.