Description:
When using empty tables with ENGINE=MEMORY in a derived table that involves LEFT JOIN or RIGHT JOIN, and the outer query applies an aggregate function (e.g., MIN/MAX) over a constant expression, MySQL incorrectly returns the constant value (e.g., 0 or -1) instead of the standard SQL NULL for an aggregation over zero input rows.
This behavior is specific to the MEMORY engine; InnoDB returns NULL correctly, indicating an optimizer bug.
Required conditions:
Base tables are ENGINE=MEMORY;
Derived table contains LEFT JOIN or RIGHT JOIN on empty tables;
Outer aggregate (MIN/MAX) over a constant expression like EXISTS(SELECT 1 WHERE FALSE) or -CAST(EXISTS(SELECT 1) AS SIGNED);
The optimizer, during the “Rows fetched before execution” phase, incorrectly assumes one row exists and folds the constant value, ignoring the rule that aggregating zero rows yields NULL.
Actual vs Expected Results
Test Case Actual Expected Status
Empty table counts 0/0/0 0/0/0 ✓
Baseline: FROM s NULL NULL ✓
LEFT JOIN derived table 0 NULL ✗
RIGHT JOIN derived table -1 NULL ✗
LEFT JOIN + WHERE 1 0 NULL ✗
InnoDB control NULL NULL ✓
EXPLAIN Analysis
Baseline Query (correct)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Select #2 (subquery in projection; run only once)
-> Zero rows (Impossible WHERE) (cost=0..0 rows=0)
Bug Query (LEFT JOIN derived table)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Select #2 (subquery in projection; run only once)
-> Zero rows (Impossible WHERE) (cost=0..0 rows=0)
Key observation: The plan is nearly identical to the baseline, yet the result differs, indicating the optimizer treats derived tables differently.
Bug Query (RIGHT JOIN derived table)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Select #2 (subquery in projection; run only once)
-> Limit: 1 row(s) (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
Here, Limit: 1 row(s) appears, suggesting the optimizer believes there is one row, causing constant folding.
Root Cause Analysis
Constant propagation: EXISTS(SELECT 1 WHERE FALSE) is folded to constant FALSE (0), and EXISTS(SELECT 1) to TRUE (1).
Row count misestimation: For empty MEMORY tables in outer joins within derived tables, the optimizer fails to estimate zero rows; instead, it assumes one row (likely due to misapplied “NULL‑complemented row” logic for outer joins).
Aggregate shortcut: Given the assumption of one row with a constant expression, the optimizer returns the constant during preprocessing, bypassing the rule that an aggregate over zero rows must return NULL.
How to repeat:
DROP DATABASE IF EXISTS repro_vp_empty;
CREATE DATABASE repro_vp_empty;
USE repro_vp_empty;
CREATE TABLE s (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;
CREATE TABLE l (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;
CREATE TABLE r (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;
-- Verify all tables are empty
SELECT
(SELECT COUNT(*) FROM s) AS s_cnt,
(SELECT COUNT(*) FROM l) AS l_cnt,
(SELECT COUNT(*) FROM r) AS r_cnt;
-- Baseline: empty base table, returns NULL (correct)
SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v
FROM s;
-- Expected: NULL, Actual: NULL ✓
-- Bug case 1: LEFT JOIN derived table, expected NULL, actual 0
SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v
FROM (
SELECT l.id
FROM l LEFT JOIN r ON l.id = r.id
) AS dt;
-- Bug case 2: RIGHT JOIN derived table, expected NULL, actual -1
SELECT MAX(-CAST(EXISTS(SELECT 1) AS SIGNED)) AS v
FROM (
SELECT l.id
FROM l RIGHT JOIN r ON l.id = r.id
) AS dt;
-- Bug case 3: with constant WHERE TRUE, still returns 0
SELECT MIN(+EXISTS(SELECT 1 WHERE FALSE)) AS v
FROM (
SELECT l.id
FROM l LEFT JOIN r ON l.id = r.id
) AS dt
WHERE 1;
-- Control: InnoDB engine returns NULL correctly
CREATE TABLE l_inn (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE r_inn (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v
FROM (
SELECT l_inn.id
FROM l_inn LEFT JOIN r_inn ON l_inn.id = r_inn.id
) AS dt;
-- Expected: NULL, Actual: NULL ✓
Description: When using empty tables with ENGINE=MEMORY in a derived table that involves LEFT JOIN or RIGHT JOIN, and the outer query applies an aggregate function (e.g., MIN/MAX) over a constant expression, MySQL incorrectly returns the constant value (e.g., 0 or -1) instead of the standard SQL NULL for an aggregation over zero input rows. This behavior is specific to the MEMORY engine; InnoDB returns NULL correctly, indicating an optimizer bug. Required conditions: Base tables are ENGINE=MEMORY; Derived table contains LEFT JOIN or RIGHT JOIN on empty tables; Outer aggregate (MIN/MAX) over a constant expression like EXISTS(SELECT 1 WHERE FALSE) or -CAST(EXISTS(SELECT 1) AS SIGNED); The optimizer, during the “Rows fetched before execution” phase, incorrectly assumes one row exists and folds the constant value, ignoring the rule that aggregating zero rows yields NULL. Actual vs Expected Results Test Case Actual Expected Status Empty table counts 0/0/0 0/0/0 ✓ Baseline: FROM s NULL NULL ✓ LEFT JOIN derived table 0 NULL ✗ RIGHT JOIN derived table -1 NULL ✗ LEFT JOIN + WHERE 1 0 NULL ✗ InnoDB control NULL NULL ✓ EXPLAIN Analysis Baseline Query (correct) -> Rows fetched before execution (cost=0..0 rows=1) -> Select #2 (subquery in projection; run only once) -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) Bug Query (LEFT JOIN derived table) -> Rows fetched before execution (cost=0..0 rows=1) -> Select #2 (subquery in projection; run only once) -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) Key observation: The plan is nearly identical to the baseline, yet the result differs, indicating the optimizer treats derived tables differently. Bug Query (RIGHT JOIN derived table) -> Rows fetched before execution (cost=0..0 rows=1) -> Select #2 (subquery in projection; run only once) -> Limit: 1 row(s) (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) Here, Limit: 1 row(s) appears, suggesting the optimizer believes there is one row, causing constant folding. Root Cause Analysis Constant propagation: EXISTS(SELECT 1 WHERE FALSE) is folded to constant FALSE (0), and EXISTS(SELECT 1) to TRUE (1). Row count misestimation: For empty MEMORY tables in outer joins within derived tables, the optimizer fails to estimate zero rows; instead, it assumes one row (likely due to misapplied “NULL‑complemented row” logic for outer joins). Aggregate shortcut: Given the assumption of one row with a constant expression, the optimizer returns the constant during preprocessing, bypassing the rule that an aggregate over zero rows must return NULL. How to repeat: DROP DATABASE IF EXISTS repro_vp_empty; CREATE DATABASE repro_vp_empty; USE repro_vp_empty; CREATE TABLE s (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; CREATE TABLE l (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; CREATE TABLE r (id BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY; -- Verify all tables are empty SELECT (SELECT COUNT(*) FROM s) AS s_cnt, (SELECT COUNT(*) FROM l) AS l_cnt, (SELECT COUNT(*) FROM r) AS r_cnt; -- Baseline: empty base table, returns NULL (correct) SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v FROM s; -- Expected: NULL, Actual: NULL ✓ -- Bug case 1: LEFT JOIN derived table, expected NULL, actual 0 SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v FROM ( SELECT l.id FROM l LEFT JOIN r ON l.id = r.id ) AS dt; -- Bug case 2: RIGHT JOIN derived table, expected NULL, actual -1 SELECT MAX(-CAST(EXISTS(SELECT 1) AS SIGNED)) AS v FROM ( SELECT l.id FROM l RIGHT JOIN r ON l.id = r.id ) AS dt; -- Bug case 3: with constant WHERE TRUE, still returns 0 SELECT MIN(+EXISTS(SELECT 1 WHERE FALSE)) AS v FROM ( SELECT l.id FROM l LEFT JOIN r ON l.id = r.id ) AS dt WHERE 1; -- Control: InnoDB engine returns NULL correctly CREATE TABLE l_inn (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE r_inn (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB; SELECT MIN(EXISTS(SELECT 1 WHERE FALSE)) AS v FROM ( SELECT l_inn.id FROM l_inn LEFT JOIN r_inn ON l_inn.id = r_inn.id ) AS dt; -- Expected: NULL, Actual: NULL ✓