Description:
When a query contains a derived table that itself includes an aggregate subquery (e.g., COUNT(*) GROUP BY vp_rowid), the optimizer may apply the AGGREGATE_THEN_MERGE strategy, merging the derived table into the outer query after materializing the aggregate result.
During this merge, column references in the outer GROUP BY expression (e.g., l.c1) are incorrectly remapped to the materialized table (which only contains vp_rowid and the aggregate result), rather than to the original left‑table column. Since the materialized table does not include c1, the value read is always NULL, causing the grouping expression to be evaluated incorrectly.
When the same query is executed directly on the base table, the column references are correct and the expected results (e.g., 0 and NULL) are returned.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM src) (-2, 0), (-1, NULL) Correct
Derived table (with aggregate subquery) (-2, NULL), (-1, NULL) Incorrect
Note: ref1 should be 0 for the first row, but in the derived‑table version it becomes NULL for both rows.
EXPLAIN Analysis
Single‑table query (correct):
-> Table scan on <temporary>
-> Temporary table with deduplication
-> Covering index scan on src using i0(c0 DESC, c1 DESC)
Direct covering index scan; column binding is correct.
Derived‑table query (incorrect):
-> Table scan on <temporary>
-> Temporary table with deduplication
-> Nested loop inner join
-> Nested loop inner join
-> Table scan on rg
-> Materialize
-> Group aggregate: count(0)
-> Covering index scan on r using PRIMARY
-> Single-row index lookup on l using PRIMARY (vp_rowid = rg.vp_rowid)
-> Single-row covering index lookup on r using PRIMARY (vp_rowid = rg.vp_rowid)
The optimizer detects that the aggregate subquery inside the derived table can be materialized early and merged with the outer query to reduce data scanning.
During merging, the optimizer attempts to rebind column references in the outer expression (e.g., IFNULL(NULL, c1) XOR 0). Since the materialized table rg contains only vp_rowid and c (the aggregate), it does not contain l.c1. However, the optimizer incorrectly maps the c1 reference to a position within rg (or an internal temporary column), causing the read of that column to yield NULL.
How to repeat:
DROP DATABASE IF EXISTS repro8;
CREATE DATABASE repro8;
USE repro8;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 FLOAT NULL, c1 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
INSERT INTO src (c0, c1) VALUES (0.0, NULL), (1.16575E9, 1);
ALTER TABLE src ADD KEY i0 (c0 DESC, c1 DESC);
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): returns (-2, 0) and (-1, NULL)
SELECT (- vp_rowid) AS ref0, ('x') = (IFNULL(NULL, c1) XOR 0) AS ref1
FROM src GROUP BY (- vp_rowid), ('x') = (IFNULL(NULL, c1) XOR 0);
-- Derived‑table rewrite (incorrect): ref1 is NULL for all rows
SELECT (- vp_rowid) AS ref0, ('x') = (IFNULL(NULL, c1) XOR 0) AS ref1
FROM (
SELECT l.vp_rowid, r.c0, l.c1
FROM l
JOIN (SELECT vp_rowid, COUNT(*) AS c FROM r GROUP BY vp_rowid) rg ON l.vp_rowid = rg.vp_rowid
JOIN r ON r.vp_rowid = rg.vp_rowid
) AS src GROUP BY (- vp_rowid), ('x') = (IFNULL(NULL, c1) XOR 0);
Description: When a query contains a derived table that itself includes an aggregate subquery (e.g., COUNT(*) GROUP BY vp_rowid), the optimizer may apply the AGGREGATE_THEN_MERGE strategy, merging the derived table into the outer query after materializing the aggregate result. During this merge, column references in the outer GROUP BY expression (e.g., l.c1) are incorrectly remapped to the materialized table (which only contains vp_rowid and the aggregate result), rather than to the original left‑table column. Since the materialized table does not include c1, the value read is always NULL, causing the grouping expression to be evaluated incorrectly. When the same query is executed directly on the base table, the column references are correct and the expected results (e.g., 0 and NULL) are returned. Actual Results and Status Query Actual Result Status Single‑table (FROM src) (-2, 0), (-1, NULL) Correct Derived table (with aggregate subquery) (-2, NULL), (-1, NULL) Incorrect Note: ref1 should be 0 for the first row, but in the derived‑table version it becomes NULL for both rows. EXPLAIN Analysis Single‑table query (correct): -> Table scan on <temporary> -> Temporary table with deduplication -> Covering index scan on src using i0(c0 DESC, c1 DESC) Direct covering index scan; column binding is correct. Derived‑table query (incorrect): -> Table scan on <temporary> -> Temporary table with deduplication -> Nested loop inner join -> Nested loop inner join -> Table scan on rg -> Materialize -> Group aggregate: count(0) -> Covering index scan on r using PRIMARY -> Single-row index lookup on l using PRIMARY (vp_rowid = rg.vp_rowid) -> Single-row covering index lookup on r using PRIMARY (vp_rowid = rg.vp_rowid) The optimizer detects that the aggregate subquery inside the derived table can be materialized early and merged with the outer query to reduce data scanning. During merging, the optimizer attempts to rebind column references in the outer expression (e.g., IFNULL(NULL, c1) XOR 0). Since the materialized table rg contains only vp_rowid and c (the aggregate), it does not contain l.c1. However, the optimizer incorrectly maps the c1 reference to a position within rg (or an internal temporary column), causing the read of that column to yield NULL. How to repeat: DROP DATABASE IF EXISTS repro8; CREATE DATABASE repro8; USE repro8; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 FLOAT NULL, c1 DECIMAL(65,30) NULL ) ENGINE=InnoDB; INSERT INTO src (c0, c1) VALUES (0.0, NULL), (1.16575E9, 1); ALTER TABLE src ADD KEY i0 (c0 DESC, c1 DESC); 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): returns (-2, 0) and (-1, NULL) SELECT (- vp_rowid) AS ref0, ('x') = (IFNULL(NULL, c1) XOR 0) AS ref1 FROM src GROUP BY (- vp_rowid), ('x') = (IFNULL(NULL, c1) XOR 0); -- Derived‑table rewrite (incorrect): ref1 is NULL for all rows SELECT (- vp_rowid) AS ref0, ('x') = (IFNULL(NULL, c1) XOR 0) AS ref1 FROM ( SELECT l.vp_rowid, r.c0, l.c1 FROM l JOIN (SELECT vp_rowid, COUNT(*) AS c FROM r GROUP BY vp_rowid) rg ON l.vp_rowid = rg.vp_rowid JOIN r ON r.vp_rowid = rg.vp_rowid ) AS src GROUP BY (- vp_rowid), ('x') = (IFNULL(NULL, c1) XOR 0);