Description:
A query using a covering index scan with the WHERE condition (- ((vp_rowid) != (IFNULL(NULL, c0)))) correctly filters data and returns a SUM result.
When the query is rewritten as a derived table with an inner l LEFT JOIN r ON l.vp_rowid = r.vp_rowid, the same WHERE condition returns NULL, indicating that no rows passed the filter.
In the execution plan, the optimizer pushes the filter condition down to the left table scan, but during this rewriting, it incorrectly binds the c0 reference inside IFNULL(NULL, c0) to the right table r.c0 (or an uninitialized temporary column) instead of l.c0.
Since the right table column is not yet read during the left table scan, its value is NULL, making IFNULL(NULL, NULL) evaluate to NULL, which in turn causes the whole filter condition to become NULL and discard all rows.
Actual Results and Status
Query Actual Result Status
Single‑table (covering index scan) 2 Correct
Derived table (LEFT JOIN) NULL Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Aggregate: sum((bit_count(src.vp_rowid) <> 0.499))
-> Filter: (0 <> -((vp_rowid <> ifnull(NULL, c0))))
-> Covering index scan on src using i5(c0 DESC)
Derived‑table query (incorrect):
-> Aggregate: sum((bit_count(l.vp_rowid) <> 0.499))
-> Nested loop left join
-> Filter: (0 <> -((vp_rowid <> ifnull(NULL, l.c0))))
-> Table scan on l
-> Single-row covering index lookup on r using PRIMARY (vp_rowid = l.vp_rowid)
How to repeat:
DROP DATABASE IF EXISTS repro30;
CREATE DATABASE repro30;
USE repro30;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
INSERT INTO src (c0) VALUES (NULL), (NULL), (NULL), (1), (574442044);
ALTER TABLE src ADD UNIQUE KEY i5 (c0 DESC);
CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB;
CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB;
INSERT INTO l SELECT vp_rowid, c0 FROM src;
INSERT INTO r SELECT vp_rowid, c0 FROM src;
-- Single‑table query (correct): SUM = 2
SELECT SUM((BIT_COUNT(vp_rowid)) NOT IN (0.499)) AS ref0
FROM src
WHERE (- ((vp_rowid) != (IFNULL(NULL, c0))));
-- Derived‑table rewrite (incorrect): SUM = NULL
SELECT SUM((BIT_COUNT(vp_rowid)) NOT IN (0.499)) AS ref0
FROM (SELECT l.vp_rowid, l.c0 FROM l LEFT JOIN r ON l.vp_rowid = r.vp_rowid) AS src
WHERE (- ((vp_rowid) != (IFNULL(NULL, c0))));
Description: A query using a covering index scan with the WHERE condition (- ((vp_rowid) != (IFNULL(NULL, c0)))) correctly filters data and returns a SUM result. When the query is rewritten as a derived table with an inner l LEFT JOIN r ON l.vp_rowid = r.vp_rowid, the same WHERE condition returns NULL, indicating that no rows passed the filter. In the execution plan, the optimizer pushes the filter condition down to the left table scan, but during this rewriting, it incorrectly binds the c0 reference inside IFNULL(NULL, c0) to the right table r.c0 (or an uninitialized temporary column) instead of l.c0. Since the right table column is not yet read during the left table scan, its value is NULL, making IFNULL(NULL, NULL) evaluate to NULL, which in turn causes the whole filter condition to become NULL and discard all rows. Actual Results and Status Query Actual Result Status Single‑table (covering index scan) 2 Correct Derived table (LEFT JOIN) NULL Incorrect EXPLAIN Analysis Single‑table query (correct): -> Aggregate: sum((bit_count(src.vp_rowid) <> 0.499)) -> Filter: (0 <> -((vp_rowid <> ifnull(NULL, c0)))) -> Covering index scan on src using i5(c0 DESC) Derived‑table query (incorrect): -> Aggregate: sum((bit_count(l.vp_rowid) <> 0.499)) -> Nested loop left join -> Filter: (0 <> -((vp_rowid <> ifnull(NULL, l.c0)))) -> Table scan on l -> Single-row covering index lookup on r using PRIMARY (vp_rowid = l.vp_rowid) How to repeat: DROP DATABASE IF EXISTS repro30; CREATE DATABASE repro30; USE repro30; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65,30) NULL ) ENGINE=InnoDB; INSERT INTO src (c0) VALUES (NULL), (NULL), (NULL), (1), (574442044); ALTER TABLE src ADD UNIQUE KEY i5 (c0 DESC); CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB; CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE=InnoDB; INSERT INTO l SELECT vp_rowid, c0 FROM src; INSERT INTO r SELECT vp_rowid, c0 FROM src; -- Single‑table query (correct): SUM = 2 SELECT SUM((BIT_COUNT(vp_rowid)) NOT IN (0.499)) AS ref0 FROM src WHERE (- ((vp_rowid) != (IFNULL(NULL, c0)))); -- Derived‑table rewrite (incorrect): SUM = NULL SELECT SUM((BIT_COUNT(vp_rowid)) NOT IN (0.499)) AS ref0 FROM (SELECT l.vp_rowid, l.c0 FROM l LEFT JOIN r ON l.vp_rowid = r.vp_rowid) AS src WHERE (- ((vp_rowid) != (IFNULL(NULL, c0))));