Description:
When a GROUP BY expression is folded to a constant during optimization (e.g., COALESCE((NULL) XOR (col), CAST('x' AS SIGNED)) always evaluates to 0), and the query uses WITH ROLLUP, the optimizer incorrectly sets the value to NULL for base group rows as well, not just for the super‑aggregate rows, in the single‑table path.
In the derived‑table path (involving JOINs and materialized subqueries), the optimizer fails to fold the expression, preserving runtime evaluation. As a result, ROLLUP correctly distinguishes between base rows (0) and super‑aggregate rows (NULL).
This causes two semantically equivalent queries to return different results, violating ROLLUP semantics.
Actual Results and Status
Query 2nd Row ref2 Status
Single‑table NULL Incorrect (should be 0)
Derived table 0 Correct
EXPLAIN Analysis
Single‑table query (incorrect) – key part:
-> Limit: 2 row(s)
-> Sort: id
-> Stream results
-> Group aggregate with rollup: count(exists(select #2))
-> Sort: ref1, ref2
-> Covering index scan on src using PRIMARY
Derived‑table query (correct) – key part:
-> Limit: 2 row(s)
-> Sort: id
-> Stream results
-> Group aggregate with rollup: count(exists(select #2))
-> Nested loop inner join
-> Nested loop inner join
-> Sort: ref1, ref2
-> Covering index scan on l using PRIMARY
-> Single-row covering index lookup on r using PRIMARY
-> Index lookup on rg using <auto_key0>
Expression semantics: (NULL) XOR (src.id) is always NULL, so COALESCE(NULL, CAST('x' AS SIGNED)) is always 0 (since CAST('x' AS SIGNED) returns 0 under non‑strict sql_mode).
Single‑table path: The optimizer folds the entire grouping expression to the constant 0 during parsing. However, the ROLLUP implementation erroneously replaces all grouping keys (including the folded constant) with NULL even for base rows, causing ref2 to appear as NULL for the id=1 row.
Derived‑table path: Because the query involves JOINs and materialized subqueries, the optimizer cannot completely fold the expression, preserving its computation structure. ROLLUP correctly distinguishes base rows (retaining the computed value 0) from super‑aggregate rows (set to NULL).
Core contradiction: The interaction between constant‑folding optimization and ROLLUP's NULL‑replacement logic is flawed—folded constants are incorrectly treated as "super‑aggregate markers" without preserving their original values for base rows.
How to repeat:
DROP DATABASE IF EXISTS repro713_11_final;
CREATE DATABASE repro713_11_final;
USE repro713_11_final;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION big_tables=0;
SET SESSION internal_tmp_mem_storage_engine='MEMORY';
CREATE TABLE src (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 TEXT NULL
) ENGINE=InnoDB;
INSERT INTO src(c0) VALUES
(NULL), (''), ('a'), ('b'), ('c');
CREATE TABLE l (
id BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE r (
id BIGINT NOT NULL PRIMARY KEY,
c0 TEXT NULL
) ENGINE=InnoDB;
INSERT INTO l SELECT id FROM src;
INSERT INTO r SELECT id, c0 FROM src;
-- Single‑table query (incorrect): base group row ref2 is NULL
SELECT 'single' AS q,
COUNT(EXISTS(SELECT 1 WHERE FALSE)) AS ref0,
src.id AS ref1,
COALESCE((NULL) XOR (src.id), CAST('x' AS SIGNED)) AS ref2
FROM src
GROUP BY
src.id,
COALESCE((NULL) XOR (src.id), CAST('x' AS SIGNED))
WITH ROLLUP
ORDER BY src.id
LIMIT 2;
-- Derived‑table query (correct): base group row ref2 is 0
SELECT 'split' AS q,
COUNT(EXISTS(SELECT 1 WHERE FALSE)) AS ref0,
s.id AS ref1,
COALESCE((NULL) XOR (s.id), CAST('x' AS SIGNED)) AS ref2
FROM (
SELECT l.id AS id, r.c0 AS c0
FROM l
JOIN (
SELECT id, COUNT(*) AS c
FROM r
GROUP BY id
) rg ON l.id = rg.id
JOIN r ON r.id = rg.id
) s
GROUP BY
s.id,
COALESCE((NULL) XOR (s.id), CAST('x' AS SIGNED))
WITH ROLLUP
ORDER BY s.id
LIMIT 2;
Description: When a GROUP BY expression is folded to a constant during optimization (e.g., COALESCE((NULL) XOR (col), CAST('x' AS SIGNED)) always evaluates to 0), and the query uses WITH ROLLUP, the optimizer incorrectly sets the value to NULL for base group rows as well, not just for the super‑aggregate rows, in the single‑table path. In the derived‑table path (involving JOINs and materialized subqueries), the optimizer fails to fold the expression, preserving runtime evaluation. As a result, ROLLUP correctly distinguishes between base rows (0) and super‑aggregate rows (NULL). This causes two semantically equivalent queries to return different results, violating ROLLUP semantics. Actual Results and Status Query 2nd Row ref2 Status Single‑table NULL Incorrect (should be 0) Derived table 0 Correct EXPLAIN Analysis Single‑table query (incorrect) – key part: -> Limit: 2 row(s) -> Sort: id -> Stream results -> Group aggregate with rollup: count(exists(select #2)) -> Sort: ref1, ref2 -> Covering index scan on src using PRIMARY Derived‑table query (correct) – key part: -> Limit: 2 row(s) -> Sort: id -> Stream results -> Group aggregate with rollup: count(exists(select #2)) -> Nested loop inner join -> Nested loop inner join -> Sort: ref1, ref2 -> Covering index scan on l using PRIMARY -> Single-row covering index lookup on r using PRIMARY -> Index lookup on rg using <auto_key0> Expression semantics: (NULL) XOR (src.id) is always NULL, so COALESCE(NULL, CAST('x' AS SIGNED)) is always 0 (since CAST('x' AS SIGNED) returns 0 under non‑strict sql_mode). Single‑table path: The optimizer folds the entire grouping expression to the constant 0 during parsing. However, the ROLLUP implementation erroneously replaces all grouping keys (including the folded constant) with NULL even for base rows, causing ref2 to appear as NULL for the id=1 row. Derived‑table path: Because the query involves JOINs and materialized subqueries, the optimizer cannot completely fold the expression, preserving its computation structure. ROLLUP correctly distinguishes base rows (retaining the computed value 0) from super‑aggregate rows (set to NULL). Core contradiction: The interaction between constant‑folding optimization and ROLLUP's NULL‑replacement logic is flawed—folded constants are incorrectly treated as "super‑aggregate markers" without preserving their original values for base rows. How to repeat: DROP DATABASE IF EXISTS repro713_11_final; CREATE DATABASE repro713_11_final; USE repro713_11_final; SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; SET SESSION big_tables=0; SET SESSION internal_tmp_mem_storage_engine='MEMORY'; CREATE TABLE src ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 TEXT NULL ) ENGINE=InnoDB; INSERT INTO src(c0) VALUES (NULL), (''), ('a'), ('b'), ('c'); CREATE TABLE l ( id BIGINT NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE r ( id BIGINT NOT NULL PRIMARY KEY, c0 TEXT NULL ) ENGINE=InnoDB; INSERT INTO l SELECT id FROM src; INSERT INTO r SELECT id, c0 FROM src; -- Single‑table query (incorrect): base group row ref2 is NULL SELECT 'single' AS q, COUNT(EXISTS(SELECT 1 WHERE FALSE)) AS ref0, src.id AS ref1, COALESCE((NULL) XOR (src.id), CAST('x' AS SIGNED)) AS ref2 FROM src GROUP BY src.id, COALESCE((NULL) XOR (src.id), CAST('x' AS SIGNED)) WITH ROLLUP ORDER BY src.id LIMIT 2; -- Derived‑table query (correct): base group row ref2 is 0 SELECT 'split' AS q, COUNT(EXISTS(SELECT 1 WHERE FALSE)) AS ref0, s.id AS ref1, COALESCE((NULL) XOR (s.id), CAST('x' AS SIGNED)) AS ref2 FROM ( SELECT l.id AS id, r.c0 AS c0 FROM l JOIN ( SELECT id, COUNT(*) AS c FROM r GROUP BY id ) rg ON l.id = rg.id JOIN r ON r.id = rg.id ) s GROUP BY s.id, COALESCE((NULL) XOR (s.id), CAST('x' AS SIGNED)) WITH ROLLUP ORDER BY s.id LIMIT 2;