Description:
When a query uses WHERE CAST(IFNULL(outer_col, inner_col) AS SIGNED) and the outer column is NULL while the inner column is non‑NULL, a direct base‑table scan correctly filters and returns an aggregate result (e.g., MAX).
However, when the query is rewritten as a derived table using a LATERAL JOIN, the optimizer, during condition pushdown or expression rewriting, incorrectly ignores or replaces the inner‑table column (rr.c1) with NULL. As a result, IFNULL(outer_col, inner_col) returns NULL when outer_col is NULL, causing all rows to be filtered and the aggregate to return NULL.
This is a wrong‑result bug caused by incorrect column‑binding handling for LATERAL derived tables.
Actual Results and Status
Query Actual Result (ref0) Status
Single‑table (FROM vp_s) 2 Correct
LATERAL derived table NULL Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Aggregate: max(vp_s.vp_rowid)
-> Filter: (0 <> cast(ifnull(vp_s.c0,vp_s.c1) as signed))
-> Covering index scan on vp_s using i2 (c1, c0)
LATERAL derived‑table query (incorrect):
-> Aggregate: max(l.vp_rowid)
-> Nested loop inner join
-> Covering index scan on l using l_idx
-> Filter: (0 <> cast(ifnull(l.c0,rr.c1) as signed))
-> Single-row index lookup on rr using PRIMARY (vp_rowid = l.vp_rowid)
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db38;
CREATE DATABASE repro_mysql714_db38;
USE repro_mysql714_db38;
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION big_tables = 0;
SET SESSION internal_tmp_mem_storage_engine = 'MEMORY';
CREATE TABLE vp_s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65, 30) NULL,
c1 DECIMAL(65, 30) NULL
) ENGINE = InnoDB;
INSERT INTO vp_s (c0, c1) VALUES (NULL, NULL), (NULL, 1611551235), (0, NULL);
ALTER TABLE vp_s ADD UNIQUE KEY i2 (c1, c0);
CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65, 30) NULL) ENGINE = InnoDB;
CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65, 30) NULL) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid, c1 FROM vp_s;
CREATE INDEX l_idx ON vp_l(c0);
CREATE INDEX r_idx ON vp_r(c1);
-- Single‑table query (correct): returns MAX = 2
SELECT DISTINCTROW MAX(DISTINCT vp_s.vp_rowid) AS ref0 FROM vp_s
WHERE CAST(IFNULL(vp_s.c0, vp_s.c1) AS SIGNED);
-- LATERAL rewrite (incorrect): returns NULL
SELECT DISTINCTROW MAX(DISTINCT vp_rowid) AS ref0 FROM (
SELECT l.vp_rowid AS vp_rowid, l.c0 AS c0, r.c1 AS c1
FROM vp_l l
JOIN LATERAL (SELECT * FROM vp_r rr WHERE rr.vp_rowid = l.vp_rowid) r ON TRUE
) vp_source
WHERE CAST(IFNULL(vp_source.c0, vp_source.c1) AS SIGNED);
Description: When a query uses WHERE CAST(IFNULL(outer_col, inner_col) AS SIGNED) and the outer column is NULL while the inner column is non‑NULL, a direct base‑table scan correctly filters and returns an aggregate result (e.g., MAX). However, when the query is rewritten as a derived table using a LATERAL JOIN, the optimizer, during condition pushdown or expression rewriting, incorrectly ignores or replaces the inner‑table column (rr.c1) with NULL. As a result, IFNULL(outer_col, inner_col) returns NULL when outer_col is NULL, causing all rows to be filtered and the aggregate to return NULL. This is a wrong‑result bug caused by incorrect column‑binding handling for LATERAL derived tables. Actual Results and Status Query Actual Result (ref0) Status Single‑table (FROM vp_s) 2 Correct LATERAL derived table NULL Incorrect EXPLAIN Analysis Single‑table query (correct): -> Aggregate: max(vp_s.vp_rowid) -> Filter: (0 <> cast(ifnull(vp_s.c0,vp_s.c1) as signed)) -> Covering index scan on vp_s using i2 (c1, c0) LATERAL derived‑table query (incorrect): -> Aggregate: max(l.vp_rowid) -> Nested loop inner join -> Covering index scan on l using l_idx -> Filter: (0 <> cast(ifnull(l.c0,rr.c1) as signed)) -> Single-row index lookup on rr using PRIMARY (vp_rowid = l.vp_rowid) How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db38; CREATE DATABASE repro_mysql714_db38; USE repro_mysql714_db38; SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; SET SESSION big_tables = 0; SET SESSION internal_tmp_mem_storage_engine = 'MEMORY'; CREATE TABLE vp_s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65, 30) NULL, c1 DECIMAL(65, 30) NULL ) ENGINE = InnoDB; INSERT INTO vp_s (c0, c1) VALUES (NULL, NULL), (NULL, 1611551235), (0, NULL); ALTER TABLE vp_s ADD UNIQUE KEY i2 (c1, c0); CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65, 30) NULL) ENGINE = InnoDB; CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65, 30) NULL) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s; INSERT INTO vp_r SELECT vp_rowid, c1 FROM vp_s; CREATE INDEX l_idx ON vp_l(c0); CREATE INDEX r_idx ON vp_r(c1); -- Single‑table query (correct): returns MAX = 2 SELECT DISTINCTROW MAX(DISTINCT vp_s.vp_rowid) AS ref0 FROM vp_s WHERE CAST(IFNULL(vp_s.c0, vp_s.c1) AS SIGNED); -- LATERAL rewrite (incorrect): returns NULL SELECT DISTINCTROW MAX(DISTINCT vp_rowid) AS ref0 FROM ( SELECT l.vp_rowid AS vp_rowid, l.c0 AS c0, r.c1 AS c1 FROM vp_l l JOIN LATERAL (SELECT * FROM vp_r rr WHERE rr.vp_rowid = l.vp_rowid) r ON TRUE ) vp_source WHERE CAST(IFNULL(vp_source.c0, vp_source.c1) AS SIGNED);