Bug #121014 semijoin=off causes missing ROLLUP row with IN subquery + HAVING

• Submitted: 27 Jul 9:59
• Reporter: Jacob Ding
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.4.10
• OS: MacOS
• CPU Architecture: ARM

[27 Jul 9:59] Jacob Ding

Description:
```sql
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
CREATE TABLE t2 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
INSERT INTO t0 VALUES (1,NULL,'0.0001',' 1'),(2,0,'-9999999999999999.9999',''),(3,1,'9999999999999999.9999','1e10'),(4,-1,'0.0001','abc'),(5,-2147483648,'0.0001','-1.5'),(6,1,'9999999999999999.9999',NULL),(7,2147483647,'-9999999999999999.9999',''),(8,-1,'9999999999999999.9999','-0');
INSERT INTO t1 VALUES (1,NULL,'-0.0001','-1.5'),(2,0,NULL,''),(3,1,'0.0001',''),(4,-1,NULL,'100'),(5,NULL,'-0.0001',NULL),(6,0,NULL,'0'),(7,2147483647,'0.0000',NULL),(8,0,'9999999999999999.9999','-1.5');
INSERT INTO t2 VALUES (1,NULL,'9999999999999999.9999','-1.5'),(2,0,'0.0000','0'),(3,1,'-0.0001',NULL),(4,-1,'0.0001','100'),(5,1,'0.0001','-0'),(6,NULL,'0.0000','1e10'),(7,-2147483648,'0.0001','-0'),(8,0,'-9999999999999999.9999',NULL);

SET SESSION optimizer_switch = 'default';
SELECT t0.c0, COUNT(*) AS cnt, SUM(t0.c1) AS sm
FROM t0 WHERE t0.id IN (SELECT t1.id FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE t1.c2 = t2.c2)
GROUP BY t0.c0 WITH ROLLUP HAVING sm > (SELECT MAX(c1) FROM t2 WHERE t2.c0 = t0.c0);
-- 2 rows: (1,1,9999999999999999.9999), (NULL,2,10000000000000000.0000)

SET SESSION optimizer_switch = 'semijoin=off';
SELECT t0.c0, COUNT(*) AS cnt, SUM(t0.c1) AS sm
FROM t0 WHERE t0.id IN (SELECT t1.id FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE t1.c2 = t2.c2)
GROUP BY t0.c0 WITH ROLLUP HAVING sm > (SELECT MAX(c1) FROM t2 WHERE t2.c0 = t0.c0);
-- 1 row: (1,1,9999999999999999.9999)
```

With semijoin=off, the ROLLUP super-aggregate row (NULL,...) is missing.

How to repeat:
Tested on MySQL 8.4.10 (Homebrew, macOS arm64). The bug is reproducible
on 8.4.10: the first SELECT returns 2 rows, the second returns 1 row.