Description:
When a query targets a table that has only one row (a constant table) and the WHERE condition actually evaluates to FALSE (i.e., matches no rows), the optimizer incorrectly applies a constant‑optimization shortcut to MAX(DISTINCT column) during the “Rows fetched before execution” phase. It returns the column value directly, ignoring the filtering effect of the WHERE clause, resulting in a non‑NULL result where NULL is expected.
In contrast, when the same query is rewritten as a derived table (e.g., using UNION ALL to simulate a FULL OUTER JOIN), the optimizer correctly evaluates the WHERE condition and returns NULL.
Actual vs Expected Results
Query Type Actual Result Expected Result Status
Original single‑table ref0 = 1 ref0 = NULL ✗ Incorrect
Derived‑table rewrite ref0 = NULL ref0 = NULL ✓ Correct
Explanation: The WHERE condition LEAST('Uk]6T', 0.8087354366787728) IN (vp_rowid) evaluates to 0 IN (1) after constant folding, which is FALSE. Therefore, no rows should qualify, and MAX(DISTINCT ...) should return NULL.
EXPLAIN Analysis
Original single‑table query plan
text
-> Rows fetched before execution (cost=0..0 rows=1)
The optimizer treats source as a constant table (only one row) and reads the row (vp_rowid = 1) before execution.
It then pre‑computes MAX(DISTINCT source.vp_rowid) as 1, without verifying whether the WHERE condition is actually satisfied.
Derived‑table rewrite plan
text
-> Aggregate: max(vp_source.vp_rowid)
-> Table scan on vp_source
-> Union all materialize
-> Zero rows (Impossible WHERE noticed after reading const tables)
-> Zero rows (Impossible WHERE noticed after reading const tables)
The derived table is materialized via UNION ALL. During materialization, the optimizer evaluates the WHERE condition per branch, identifies both as “impossible WHERE”, and produces an empty materialized table.
The outer MAX() then operates on an empty set and returns NULL.
Key difference: In the original query, the constant‑table path bypasses actual WHERE checking, while the materialized derived‑table path forces condition evaluation during materialization, leading to correct filtering.
How to repeat:
DROP DATABASE IF EXISTS repro_mysql619_11;
CREATE DATABASE repro_mysql619_11;
USE repro_mysql619_11;
CREATE TABLE source (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO source VALUES ();
CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO l SELECT vp_rowid FROM source;
INSERT INTO r SELECT vp_rowid FROM source;
-- Original single‑table query: incorrectly returns 1
SELECT MAX(DISTINCT source.vp_rowid) AS ref0
FROM source
WHERE LEAST('Uk]6T', 0.8087354366787728) IN (source.vp_rowid);
-- Derived‑table rewrite (SQLancer VP full outer join simulation): correctly returns NULL
SELECT MAX(DISTINCT vp_source.vp_rowid) AS ref0
FROM (
SELECT l.vp_rowid AS vp_rowid
FROM l
LEFT JOIN r ON l.vp_rowid = r.vp_rowid
UNION ALL
SELECT r.vp_rowid AS vp_rowid
FROM l
RIGHT JOIN r ON l.vp_rowid = r.vp_rowid
WHERE l.vp_rowid IS NULL
) AS vp_source
WHERE LEAST('Uk]6T', 0.8087354366787728) IN (vp_source.vp_rowid);
© 2026, Oracle Corporation and/or its affiliates
Description: When a query targets a table that has only one row (a constant table) and the WHERE condition actually evaluates to FALSE (i.e., matches no rows), the optimizer incorrectly applies a constant‑optimization shortcut to MAX(DISTINCT column) during the “Rows fetched before execution” phase. It returns the column value directly, ignoring the filtering effect of the WHERE clause, resulting in a non‑NULL result where NULL is expected. In contrast, when the same query is rewritten as a derived table (e.g., using UNION ALL to simulate a FULL OUTER JOIN), the optimizer correctly evaluates the WHERE condition and returns NULL. Actual vs Expected Results Query Type Actual Result Expected Result Status Original single‑table ref0 = 1 ref0 = NULL ✗ Incorrect Derived‑table rewrite ref0 = NULL ref0 = NULL ✓ Correct Explanation: The WHERE condition LEAST('Uk]6T', 0.8087354366787728) IN (vp_rowid) evaluates to 0 IN (1) after constant folding, which is FALSE. Therefore, no rows should qualify, and MAX(DISTINCT ...) should return NULL. EXPLAIN Analysis Original single‑table query plan text -> Rows fetched before execution (cost=0..0 rows=1) The optimizer treats source as a constant table (only one row) and reads the row (vp_rowid = 1) before execution. It then pre‑computes MAX(DISTINCT source.vp_rowid) as 1, without verifying whether the WHERE condition is actually satisfied. Derived‑table rewrite plan text -> Aggregate: max(vp_source.vp_rowid) -> Table scan on vp_source -> Union all materialize -> Zero rows (Impossible WHERE noticed after reading const tables) -> Zero rows (Impossible WHERE noticed after reading const tables) The derived table is materialized via UNION ALL. During materialization, the optimizer evaluates the WHERE condition per branch, identifies both as “impossible WHERE”, and produces an empty materialized table. The outer MAX() then operates on an empty set and returns NULL. Key difference: In the original query, the constant‑table path bypasses actual WHERE checking, while the materialized derived‑table path forces condition evaluation during materialization, leading to correct filtering. How to repeat: DROP DATABASE IF EXISTS repro_mysql619_11; CREATE DATABASE repro_mysql619_11; USE repro_mysql619_11; CREATE TABLE source ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO source VALUES (); CREATE TABLE l ( vp_rowid BIGINT NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE r ( vp_rowid BIGINT NOT NULL PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO l SELECT vp_rowid FROM source; INSERT INTO r SELECT vp_rowid FROM source; -- Original single‑table query: incorrectly returns 1 SELECT MAX(DISTINCT source.vp_rowid) AS ref0 FROM source WHERE LEAST('Uk]6T', 0.8087354366787728) IN (source.vp_rowid); -- Derived‑table rewrite (SQLancer VP full outer join simulation): correctly returns NULL SELECT MAX(DISTINCT vp_source.vp_rowid) AS ref0 FROM ( SELECT l.vp_rowid AS vp_rowid FROM l LEFT JOIN r ON l.vp_rowid = r.vp_rowid UNION ALL SELECT r.vp_rowid AS vp_rowid FROM l RIGHT JOIN r ON l.vp_rowid = r.vp_rowid WHERE l.vp_rowid IS NULL ) AS vp_source WHERE LEAST('Uk]6T', 0.8087354366787728) IN (vp_source.vp_rowid);