Description:
When a RIGHT JOIN query is rewritten by the optimizer as a LEFT JOIN, and the SELECT list contains IFNULL(left_table.column, right_table.column), the optimizer fails to correctly update all field references during the rewrite.
Specifically, the reference to the right table (r.c0) in IFNULL(l.c1, r.c0) is still bound to the inner table's slot after the rewrite, causing it to read the inner table's corresponding NULL value instead of the outer table's non‑NULL column, and thus IFNULL returns NULL.
The equivalent query executed directly on the base table (without JOIN) produces the correct result.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM t) NULL, 1422858474.000... Correct
RIGHT JOIN + ORDER BY NULL, NULL Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Index scan on t using PRIMARY
RIGHT JOIN query (incorrect):
-> Sort: l.id
-> Stream results
-> Left hash join (l.id = r.id)
-> Covering index scan on r using r_idx
-> Hash
-> Table scan on l
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db15;
CREATE DATABASE repro_mysql714_db15;
USE repro_mysql714_db15;
CREATE TABLE t (
id BIGINT PRIMARY KEY,
c0 DECIMAL(65, 30) NULL,
c1 DECIMAL(65, 30) NULL
) ENGINE = InnoDB;
INSERT INTO t VALUES (1, NULL, NULL), (2, 1422858474, NULL);
CREATE TABLE l (id BIGINT PRIMARY KEY, c1 DECIMAL(65, 30) NULL) ENGINE = InnoDB;
CREATE TABLE r (id BIGINT PRIMARY KEY, c0 DECIMAL(65, 30) NULL) ENGINE = InnoDB;
INSERT INTO l SELECT id, c1 FROM t;
INSERT INTO r SELECT id, c0 FROM t;
CREATE INDEX r_idx ON r(c0, id);
-- Single‑table query (correct): returns NULL, 1422858474
SELECT IFNULL(c1, c0) AS ref2 FROM t ORDER BY id;
-- RIGHT JOIN query (incorrect): returns NULL, NULL
SELECT IFNULL(l.c1, r.c0) AS ref2
FROM l RIGHT JOIN r ON l.id = r.id
ORDER BY l.id;
Description: When a RIGHT JOIN query is rewritten by the optimizer as a LEFT JOIN, and the SELECT list contains IFNULL(left_table.column, right_table.column), the optimizer fails to correctly update all field references during the rewrite. Specifically, the reference to the right table (r.c0) in IFNULL(l.c1, r.c0) is still bound to the inner table's slot after the rewrite, causing it to read the inner table's corresponding NULL value instead of the outer table's non‑NULL column, and thus IFNULL returns NULL. The equivalent query executed directly on the base table (without JOIN) produces the correct result. Actual Results and Status Query Actual Result Status Single‑table (FROM t) NULL, 1422858474.000... Correct RIGHT JOIN + ORDER BY NULL, NULL Incorrect EXPLAIN Analysis Single‑table query (correct): -> Index scan on t using PRIMARY RIGHT JOIN query (incorrect): -> Sort: l.id -> Stream results -> Left hash join (l.id = r.id) -> Covering index scan on r using r_idx -> Hash -> Table scan on l How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db15; CREATE DATABASE repro_mysql714_db15; USE repro_mysql714_db15; CREATE TABLE t ( id BIGINT PRIMARY KEY, c0 DECIMAL(65, 30) NULL, c1 DECIMAL(65, 30) NULL ) ENGINE = InnoDB; INSERT INTO t VALUES (1, NULL, NULL), (2, 1422858474, NULL); CREATE TABLE l (id BIGINT PRIMARY KEY, c1 DECIMAL(65, 30) NULL) ENGINE = InnoDB; CREATE TABLE r (id BIGINT PRIMARY KEY, c0 DECIMAL(65, 30) NULL) ENGINE = InnoDB; INSERT INTO l SELECT id, c1 FROM t; INSERT INTO r SELECT id, c0 FROM t; CREATE INDEX r_idx ON r(c0, id); -- Single‑table query (correct): returns NULL, 1422858474 SELECT IFNULL(c1, c0) AS ref2 FROM t ORDER BY id; -- RIGHT JOIN query (incorrect): returns NULL, NULL SELECT IFNULL(l.c1, r.c0) AS ref2 FROM l RIGHT JOIN r ON l.id = r.id ORDER BY l.id;