Description:
A query using a covering index scan with the expression ((c1) <= (IFNULL(NULL, c2))) >= (c1) correctly returns DISTINCT results 1 and NULL.
When rewritten as a derived table where c1 is obtained via a scalar subquery from the right table r and c2 comes directly from the left table l, the same expression returns only NULL, losing the value 1.
In the execution plan, the optimizer uses a Temporary table with deduplication to deduplicate the derived table results. However, when writing the scalar‑subquery column to the temporary table, it incorrectly sets the NULL flag for all rows of that column to 1, causing actual non‑NULL values (e.g., 0.524) to be treated as NULL in subsequent expression evaluation. As a result, every row evaluates to NULL.
Actual Results and Status
Query Actual Result Status
Single‑table (covering index scan) 1, NULL Correct
Derived table (scalar subquery) NULL (only one row) Incorrect
Note: The correct result should contain both 1 and NULL. The derived‑table version loses the 1 because the non‑NULL value is incorrectly marked as NULL.
EXPLAIN Analysis
Single‑table query (correct):
-> Temporary table with deduplication
-> Covering index scan on src using i8
Direct base‑table scan; all columns come from the base table, and NULL metadata is correct.
Derived‑table query (incorrect):
-> Temporary table with deduplication
-> Nested loop inner join
-> Table scan on l
-> Single-row index lookup on r using PRIMARY
The optimizer creates a temporary table for deduplication, but the scalar‑subquery column c1 has its NULL flags incorrectly set when written.
How to repeat:
DROP DATABASE IF EXISTS repro40;
CREATE DATABASE repro40;
USE repro40;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65,30) NULL, c1 FLOAT NULL,
c2 DECIMAL(65,30) NULL, c3 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
INSERT INTO src (c0, c1, c2, c3) VALUES
(NULL, 0.0, NULL, NULL), (1327760152, 0.029, NULL, NULL),
(NULL, 0.524, 2064196087, 603484058), (NULL, 1.528E9, NULL, NULL);
CREATE INDEX i8 ON src(c2 DESC, c1, c3, c0);
CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c2 DECIMAL(65,30) NULL, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB;
CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 FLOAT NULL, c3 DECIMAL(65,30) NULL) ENGINE=InnoDB;
INSERT INTO l SELECT vp_rowid, c2, c0 FROM src;
INSERT INTO r SELECT vp_rowid, c1, c3 FROM src;
-- Single‑table query (correct): returns 1 and NULL
SELECT DISTINCT ((c1) <= (IFNULL(NULL, c2))) >= (c1) AS ref0 FROM src;
-- Derived‑table rewrite (incorrect): returns only NULL
SELECT DISTINCT ((c1) <= (IFNULL(NULL, c2))) >= (c1) AS ref0
FROM (
SELECT l.vp_rowid, l.c0,
(SELECT r.c1 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c1,
l.c2, (SELECT r.c3 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c3
FROM l
) AS src;
Description: A query using a covering index scan with the expression ((c1) <= (IFNULL(NULL, c2))) >= (c1) correctly returns DISTINCT results 1 and NULL. When rewritten as a derived table where c1 is obtained via a scalar subquery from the right table r and c2 comes directly from the left table l, the same expression returns only NULL, losing the value 1. In the execution plan, the optimizer uses a Temporary table with deduplication to deduplicate the derived table results. However, when writing the scalar‑subquery column to the temporary table, it incorrectly sets the NULL flag for all rows of that column to 1, causing actual non‑NULL values (e.g., 0.524) to be treated as NULL in subsequent expression evaluation. As a result, every row evaluates to NULL. Actual Results and Status Query Actual Result Status Single‑table (covering index scan) 1, NULL Correct Derived table (scalar subquery) NULL (only one row) Incorrect Note: The correct result should contain both 1 and NULL. The derived‑table version loses the 1 because the non‑NULL value is incorrectly marked as NULL. EXPLAIN Analysis Single‑table query (correct): -> Temporary table with deduplication -> Covering index scan on src using i8 Direct base‑table scan; all columns come from the base table, and NULL metadata is correct. Derived‑table query (incorrect): -> Temporary table with deduplication -> Nested loop inner join -> Table scan on l -> Single-row index lookup on r using PRIMARY The optimizer creates a temporary table for deduplication, but the scalar‑subquery column c1 has its NULL flags incorrectly set when written. How to repeat: DROP DATABASE IF EXISTS repro40; CREATE DATABASE repro40; USE repro40; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65,30) NULL, c1 FLOAT NULL, c2 DECIMAL(65,30) NULL, c3 DECIMAL(65,30) NULL ) ENGINE=InnoDB; INSERT INTO src (c0, c1, c2, c3) VALUES (NULL, 0.0, NULL, NULL), (1327760152, 0.029, NULL, NULL), (NULL, 0.524, 2064196087, 603484058), (NULL, 1.528E9, NULL, NULL); CREATE INDEX i8 ON src(c2 DESC, c1, c3, c0); CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c2 DECIMAL(65,30) NULL, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB; CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 FLOAT NULL, c3 DECIMAL(65,30) NULL) ENGINE=InnoDB; INSERT INTO l SELECT vp_rowid, c2, c0 FROM src; INSERT INTO r SELECT vp_rowid, c1, c3 FROM src; -- Single‑table query (correct): returns 1 and NULL SELECT DISTINCT ((c1) <= (IFNULL(NULL, c2))) >= (c1) AS ref0 FROM src; -- Derived‑table rewrite (incorrect): returns only NULL SELECT DISTINCT ((c1) <= (IFNULL(NULL, c2))) >= (c1) AS ref0 FROM ( SELECT l.vp_rowid, l.c0, (SELECT r.c1 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c1, l.c2, (SELECT r.c3 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c3 FROM l ) AS src;