Description:
When a query’s WHERE condition is always FALSE, resulting in an empty result set, the MySQL optimizer uses a short‑circuit optimization (Zero input rows) to directly output one aggregate row, bypassing the normal GROUP BY ... WITH ROLLUP processing.
Under correct semantics, even with empty input, WITH ROLLUP should produce a super‑aggregate row with all grouping column values set to NULL. However, the short‑circuit path reevaluates the grouping expression in an empty context, returning the expression’s computed result (e.g., 0) instead of NULL. This differs from the non‑short‑circuit path where the grouping column values come from the super‑aggregate row’s NULLs.
Actual Results and Status
Query Actual Result Status
Original (FROM src) NULL Correct
Rewritten (scalar subquery) 0 Incorrect
EXPLAIN Analysis
Original query (correct) key plan fragment:
-> Group (no aggregates)
-> Sort: ref0
-> Filter: ((0 <> cast((vp_rowid is not null) as signed)) is false)
-> Table scan on src
The optimizer processes GROUP BY ... WITH ROLLUP normally, producing a super‑aggregate row with NULL for the grouping column even though the filter removes all rows.
Rewritten query (incorrect) key plan fragment:
-> Zero input rows (no matching row in const table), aggregated into one output row
The optimizer detects the always‑false WHERE and short‑circuits, skipping ROLLUP and returning a direct evaluation of the grouping expression, resulting in 0.
How to repeat:
DROP DATABASE IF EXISTS repro;
CREATE DATABASE repro;
USE repro;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT
) ENGINE=InnoDB;
INSERT INTO src (c0) VALUES (1689933404);
CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT) ENGINE=InnoDB;
INSERT INTO l (vp_rowid) SELECT vp_rowid FROM src;
INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;
-- Original query (correct): returns NULL (super‑aggregate row)
SELECT 'single' AS query_type, (((c0) LIKE (NULL)) IS FALSE) IS NULL AS ref0
FROM src
WHERE (CAST((vp_rowid) IS NOT NULL AS SIGNED)) IS FALSE
GROUP BY (((c0) LIKE (NULL)) IS FALSE) IS NULL WITH ROLLUP;
-- Rewritten query (incorrect): returns 0
SELECT 'split' AS query_type, (((c0) LIKE (NULL)) IS FALSE) IS NULL AS ref0
FROM (
SELECT l.vp_rowid,
(SELECT r.c0 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c0
FROM l
) AS src
WHERE (CAST((vp_rowid) IS NOT NULL AS SIGNED)) IS FALSE
GROUP BY (((c0) LIKE (NULL)) IS FALSE) IS NULL WITH ROLLUP;
Description: When a query’s WHERE condition is always FALSE, resulting in an empty result set, the MySQL optimizer uses a short‑circuit optimization (Zero input rows) to directly output one aggregate row, bypassing the normal GROUP BY ... WITH ROLLUP processing. Under correct semantics, even with empty input, WITH ROLLUP should produce a super‑aggregate row with all grouping column values set to NULL. However, the short‑circuit path reevaluates the grouping expression in an empty context, returning the expression’s computed result (e.g., 0) instead of NULL. This differs from the non‑short‑circuit path where the grouping column values come from the super‑aggregate row’s NULLs. Actual Results and Status Query Actual Result Status Original (FROM src) NULL Correct Rewritten (scalar subquery) 0 Incorrect EXPLAIN Analysis Original query (correct) key plan fragment: -> Group (no aggregates) -> Sort: ref0 -> Filter: ((0 <> cast((vp_rowid is not null) as signed)) is false) -> Table scan on src The optimizer processes GROUP BY ... WITH ROLLUP normally, producing a super‑aggregate row with NULL for the grouping column even though the filter removes all rows. Rewritten query (incorrect) key plan fragment: -> Zero input rows (no matching row in const table), aggregated into one output row The optimizer detects the always‑false WHERE and short‑circuits, skipping ROLLUP and returning a direct evaluation of the grouping expression, resulting in 0. How to repeat: DROP DATABASE IF EXISTS repro; CREATE DATABASE repro; USE repro; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT ) ENGINE=InnoDB; INSERT INTO src (c0) VALUES (1689933404); CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT) ENGINE=InnoDB; INSERT INTO l (vp_rowid) SELECT vp_rowid FROM src; INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src; -- Original query (correct): returns NULL (super‑aggregate row) SELECT 'single' AS query_type, (((c0) LIKE (NULL)) IS FALSE) IS NULL AS ref0 FROM src WHERE (CAST((vp_rowid) IS NOT NULL AS SIGNED)) IS FALSE GROUP BY (((c0) LIKE (NULL)) IS FALSE) IS NULL WITH ROLLUP; -- Rewritten query (incorrect): returns 0 SELECT 'split' AS query_type, (((c0) LIKE (NULL)) IS FALSE) IS NULL AS ref0 FROM ( SELECT l.vp_rowid, (SELECT r.c0 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c0 FROM l ) AS src WHERE (CAST((vp_rowid) IS NOT NULL AS SIGNED)) IS FALSE GROUP BY (((c0) LIKE (NULL)) IS FALSE) IS NULL WITH ROLLUP;