Description:
When a query uses GROUP BY ... WITH ROLLUP and the grouping expression is a complex one containing IFNULL (e.g., CAST(IFNULL(c1, vp_rowid) AS SIGNED)), the super‑aggregate row correctly shows NULL for that expression when the query runs directly on the base table.
However, when the query is rewritten as a derived table involving JOINs and materialized aggregate subqueries, the same ROLLUP super‑aggregate row incorrectly returns a non‑NULL value (e.g., 1) instead of the expected NULL.
The root cause is that, in the derived‑table path, the optimizer does not set all columns referenced by the expression to NULL when generating the super‑aggregate row; it only sets some columns, causing the expression to be re‑evaluated with the original column values.
Actual Results and Status
Query Last Row ref3 Status
Single‑table (FROM src) NULL Correct
Derived table (JOIN + materialization) 1 Incorrect
EXPLAIN Analysis
Single‑table query (correct) – key part:
-> Group aggregate with rollup: bit_and(cast(src.vp_rowid as signed))
-> Sort: ref2, vp_rowid, ref3
-> Covering index scan on src using i1
Derived‑table query (incorrect) – key part:
-> Group aggregate with rollup: bit_and(tmp_field)
-> Sort: ref2, vp_rowid, ref3
-> Stream results
-> Nested loop inner join
-> Nested loop inner join
-> Table scan on l
-> Single-row index lookup on r using PRIMARY
-> Index lookup on rg using <auto_key0>
-> Materialize
-> Group aggregate: count(0)
-> Covering index scan on r using PRIMARY
For a grouping expression like CAST(IFNULL(c1, vp_rowid) AS SIGNED), the optimizer can produce NULL either by:
a) directly forcing the whole expression to NULL (short‑circuit), or
b) setting all columns referenced by the expression to NULL and then re‑evaluating.
Derived‑table path: The optimizer attempts approach b but fails to set s.vp_rowid to NULL; only s.c1 is set. Since s.c1 is already NULL in the original row (the row has c1=NULL), IFNULL(NULL, s.vp_rowid) falls back to the untouched s.vp_rowid (1), yielding 1.
After derived‑table merging or materialization, the dependency analysis for the expression may be incomplete, failing to recognize vp_rowid as a dependent column, so it is omitted from the NULL‑overwrite list.
How to repeat:
DROP DATABASE IF EXISTS repro_db5_final;
CREATE DATABASE repro_db5_final;
USE repro_db5_final;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 FLOAT NULL,
c1 DECIMAL(65,30) NULL,
UNIQUE KEY i1(c0, c1)
) ENGINE=InnoDB;
INSERT INTO src(c0, c1) VALUES
(1.1, NULL),
(2.2, NULL),
(3.3, 0),
(4.4, 0);
CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 FLOAT NULL
) ENGINE=InnoDB;
INSERT INTO l
SELECT vp_rowid, c1 FROM src;
INSERT INTO r
SELECT vp_rowid, c0 FROM src;
-- Single‑table query (correct): super‑aggregate row ref3 is NULL
SELECT 'single' AS qt,
src.vp_rowid,
-((src.vp_rowid) = ((src.c0) NOT IN (src.c1))) AS ref2,
CAST(IFNULL(src.c1, src.vp_rowid) AS SIGNED) AS ref3,
BIT_AND(CAST(vp_rowid AS SIGNED)) AS ref4
FROM src
GROUP BY
-((src.vp_rowid) = ((src.c0) NOT IN (src.c1))),
src.vp_rowid,
CAST(IFNULL(src.c1, src.vp_rowid) AS SIGNED)
WITH ROLLUP
ORDER BY src.vp_rowid
LIMIT 4;
-- Derived‑table query (incorrect): super‑aggregate row ref3 is 1
SELECT 'split' AS qt,
s.vp_rowid,
-((s.vp_rowid) = ((s.c0) NOT IN (s.c1))) AS ref2,
CAST(IFNULL(s.c1, s.vp_rowid) AS SIGNED) AS ref3,
BIT_AND(CAST(vp_rowid AS SIGNED)) AS ref4
FROM (
SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0, l.c1 AS c1
FROM l
JOIN (
SELECT vp_rowid, COUNT(*) AS vp_count
FROM r
GROUP BY vp_rowid
) rg ON l.vp_rowid = rg.vp_rowid
JOIN r ON r.vp_rowid = rg.vp_rowid
) s
GROUP BY
-((s.vp_rowid) = ((s.c0) NOT IN (s.c1))),
s.vp_rowid,
CAST(IFNULL(s.c1, s.vp_rowid) AS SIGNED)
WITH ROLLUP
ORDER BY s.vp_rowid
LIMIT 4;
Description: When a query uses GROUP BY ... WITH ROLLUP and the grouping expression is a complex one containing IFNULL (e.g., CAST(IFNULL(c1, vp_rowid) AS SIGNED)), the super‑aggregate row correctly shows NULL for that expression when the query runs directly on the base table. However, when the query is rewritten as a derived table involving JOINs and materialized aggregate subqueries, the same ROLLUP super‑aggregate row incorrectly returns a non‑NULL value (e.g., 1) instead of the expected NULL. The root cause is that, in the derived‑table path, the optimizer does not set all columns referenced by the expression to NULL when generating the super‑aggregate row; it only sets some columns, causing the expression to be re‑evaluated with the original column values. Actual Results and Status Query Last Row ref3 Status Single‑table (FROM src) NULL Correct Derived table (JOIN + materialization) 1 Incorrect EXPLAIN Analysis Single‑table query (correct) – key part: -> Group aggregate with rollup: bit_and(cast(src.vp_rowid as signed)) -> Sort: ref2, vp_rowid, ref3 -> Covering index scan on src using i1 Derived‑table query (incorrect) – key part: -> Group aggregate with rollup: bit_and(tmp_field) -> Sort: ref2, vp_rowid, ref3 -> Stream results -> Nested loop inner join -> Nested loop inner join -> Table scan on l -> Single-row index lookup on r using PRIMARY -> Index lookup on rg using <auto_key0> -> Materialize -> Group aggregate: count(0) -> Covering index scan on r using PRIMARY For a grouping expression like CAST(IFNULL(c1, vp_rowid) AS SIGNED), the optimizer can produce NULL either by: a) directly forcing the whole expression to NULL (short‑circuit), or b) setting all columns referenced by the expression to NULL and then re‑evaluating. Derived‑table path: The optimizer attempts approach b but fails to set s.vp_rowid to NULL; only s.c1 is set. Since s.c1 is already NULL in the original row (the row has c1=NULL), IFNULL(NULL, s.vp_rowid) falls back to the untouched s.vp_rowid (1), yielding 1. After derived‑table merging or materialization, the dependency analysis for the expression may be incomplete, failing to recognize vp_rowid as a dependent column, so it is omitted from the NULL‑overwrite list. How to repeat: DROP DATABASE IF EXISTS repro_db5_final; CREATE DATABASE repro_db5_final; USE repro_db5_final; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 FLOAT NULL, c1 DECIMAL(65,30) NULL, UNIQUE KEY i1(c0, c1) ) ENGINE=InnoDB; INSERT INTO src(c0, c1) VALUES (1.1, NULL), (2.2, NULL), (3.3, 0), (4.4, 0); CREATE TABLE l ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65,30) NULL ) ENGINE=InnoDB; CREATE TABLE r ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL ) ENGINE=InnoDB; INSERT INTO l SELECT vp_rowid, c1 FROM src; INSERT INTO r SELECT vp_rowid, c0 FROM src; -- Single‑table query (correct): super‑aggregate row ref3 is NULL SELECT 'single' AS qt, src.vp_rowid, -((src.vp_rowid) = ((src.c0) NOT IN (src.c1))) AS ref2, CAST(IFNULL(src.c1, src.vp_rowid) AS SIGNED) AS ref3, BIT_AND(CAST(vp_rowid AS SIGNED)) AS ref4 FROM src GROUP BY -((src.vp_rowid) = ((src.c0) NOT IN (src.c1))), src.vp_rowid, CAST(IFNULL(src.c1, src.vp_rowid) AS SIGNED) WITH ROLLUP ORDER BY src.vp_rowid LIMIT 4; -- Derived‑table query (incorrect): super‑aggregate row ref3 is 1 SELECT 'split' AS qt, s.vp_rowid, -((s.vp_rowid) = ((s.c0) NOT IN (s.c1))) AS ref2, CAST(IFNULL(s.c1, s.vp_rowid) AS SIGNED) AS ref3, BIT_AND(CAST(vp_rowid AS SIGNED)) AS ref4 FROM ( SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0, l.c1 AS c1 FROM l JOIN ( SELECT vp_rowid, COUNT(*) AS vp_count FROM r GROUP BY vp_rowid ) rg ON l.vp_rowid = rg.vp_rowid JOIN r ON r.vp_rowid = rg.vp_rowid ) s GROUP BY -((s.vp_rowid) = ((s.c0) NOT IN (s.c1))), s.vp_rowid, CAST(IFNULL(s.c1, s.vp_rowid) AS SIGNED) WITH ROLLUP ORDER BY s.vp_rowid LIMIT 4;