Description:
`t2` is empty, so `t2 RIGHT JOIN t0 ON FALSE` must preserve the row `t0.c0 = 0` as a null-extended row with `t2.c0 = NULL`. Therefore `IFNULL(t2.c0, t0.c0)` must be `0`, and the downstream `INNER JOIN t1 ON ... >= t1.c0` must keep exactly the row `t1.c0 = 0`. MySQL returns that row on both the direct `SELECT` and an equivalent derived-table wrapper, but loses it when the same result is written into a temporary table via `CREATE TEMPORARY TABLE ... AS SELECT`.
How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 DECIMAL(10,0));
INSERT INTO t0 VALUES (0), (NULL);
INSERT INTO t1 VALUES (0), (1);
SELECT t1.c0 AS ref0
FROM t2 RIGHT JOIN t0 ON FALSE
INNER JOIN t1 ON IFNULL(t2.c0, t0.c0) >= t1.c0
WHERE t0.c0 IS NOT NULL;
DROP TEMPORARY TABLE IF EXISTS temp_0;
CREATE TEMPORARY TABLE temp_0 AS
SELECT t1.c0 AS t1_c0, t0.c0 AS t0_c0
FROM t2 RIGHT JOIN t0 ON FALSE
INNER JOIN t1 ON IFNULL(t2.c0, t0.c0) >= t1.c0;
SELECT t1_c0 AS ref0
FROM temp_0
WHERE t0_c0 IS NOT NULL; -- Expected correct result: both SELECTs should return 0 -- actual Wrong result: direct SELECT returns 0, temp-table readback returns <empty>
