Description:
`t1` contains one row with `t1.c0 = NULL` and non-NULL `t1.c1`. Since `t1.c1` is stored in a scale-0 `DECIMAL ZEROFILL` column, inserting `0.4218104454595706` makes the stored value effectively `0`. Therefore `IFNULL(t1.c0, t1.c1)` should evaluate to `0`, and all three positive `t4.c0` rows should satisfy `t4.c0 > IFNULL(t1.c0, t1.c1)`. MySQL instead returns only NULL-extended rows on the wrong path.
How to repeat:
CREATE TABLE t0(c0 DECIMAL ZEROFILL UNIQUE COLUMN_FORMAT FIXED NULL COMMENT 'asdf', c1 DECIMAL ZEROFILL);
CREATE TABLE t1 LIKE t0;
CREATE TABLE t2(c0 FLOAT ZEROFILL STORAGE MEMORY COMMENT 'asdf' UNIQUE KEY COLUMN_FORMAT DYNAMIC);
ALTER TABLE t2 STATS_AUTO_RECALC 1, COMPRESSION 'LZ4', FORCE, RENAME t4, DELAY_KEY_WRITE 1, ENABLE KEYS, PACK_KEYS DEFAULT, STATS_PERSISTENT 0;
REPLACE LOW_PRIORITY INTO t4(c0) VALUES(0.39184683846585056);
INSERT HIGH_PRIORITY INTO t1(c1) VALUES(0.4218104454595706);
INSERT LOW_PRIORITY IGNORE INTO t4(c0) VALUES(7.42480155E8);
INSERT IGNORE INTO t4(c0) VALUES(0.7765658125000579);
INSERT LOW_PRIORITY IGNORE INTO t1(c1) VALUES(NULL);
DROP TEMPORARY TABLE IF EXISTS temp_1;
CREATE TEMPORARY TABLE temp_1 (
ref0 FLOAT UNSIGNED ZEROFILL DEFAULT NULL COMMENT 'asdf'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO temp_1
SELECT ALL t4.c0 AS ref0
FROM t4
LEFT JOIN t0 ON (t4.c0) >= (t0.c0);
SELECT ALL (+ (t4.c0)) AS ref0, (+ (t0.c0)) AS ref1
FROM temp_1 AS subq0,
t4 RIGHT JOIN t1
ON ((t4.c0) > (IFNULL(t1.c0, t1.c1)))
&& (((t1.c1) IS NOT NULL) || ((t1.c1) IS NULL))
LEFT JOIN t0
ON ((t1.c0) <= (t0.c0))
&& (((((t0.c0) != ((SELECT ALL t0.c0 AS ref2 FROM t4 RIGHT JOIN t0 ON (t4.c0) = (t0.c0) LIMIT 1)))
AND ((t0.c0) IN (NULL, t0.c0, 1161830205, t0.c0, t0.c0)))
&& ((t0.c0) < (0.14556425313132382)))
&& ((t0.c0) IS NOT NULL)); -- Expected correct result: 00000.391847 | NULL ; 000742480000 | NULL ; 00000.776566 | NULL ; NULL | NULL (each of these rows appears 3 times) -- actual Wrong result: NULL | NULL (appears 6 times)
Description: `t1` contains one row with `t1.c0 = NULL` and non-NULL `t1.c1`. Since `t1.c1` is stored in a scale-0 `DECIMAL ZEROFILL` column, inserting `0.4218104454595706` makes the stored value effectively `0`. Therefore `IFNULL(t1.c0, t1.c1)` should evaluate to `0`, and all three positive `t4.c0` rows should satisfy `t4.c0 > IFNULL(t1.c0, t1.c1)`. MySQL instead returns only NULL-extended rows on the wrong path. How to repeat: CREATE TABLE t0(c0 DECIMAL ZEROFILL UNIQUE COLUMN_FORMAT FIXED NULL COMMENT 'asdf', c1 DECIMAL ZEROFILL); CREATE TABLE t1 LIKE t0; CREATE TABLE t2(c0 FLOAT ZEROFILL STORAGE MEMORY COMMENT 'asdf' UNIQUE KEY COLUMN_FORMAT DYNAMIC); ALTER TABLE t2 STATS_AUTO_RECALC 1, COMPRESSION 'LZ4', FORCE, RENAME t4, DELAY_KEY_WRITE 1, ENABLE KEYS, PACK_KEYS DEFAULT, STATS_PERSISTENT 0; REPLACE LOW_PRIORITY INTO t4(c0) VALUES(0.39184683846585056); INSERT HIGH_PRIORITY INTO t1(c1) VALUES(0.4218104454595706); INSERT LOW_PRIORITY IGNORE INTO t4(c0) VALUES(7.42480155E8); INSERT IGNORE INTO t4(c0) VALUES(0.7765658125000579); INSERT LOW_PRIORITY IGNORE INTO t1(c1) VALUES(NULL); DROP TEMPORARY TABLE IF EXISTS temp_1; CREATE TEMPORARY TABLE temp_1 ( ref0 FLOAT UNSIGNED ZEROFILL DEFAULT NULL COMMENT 'asdf' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO temp_1 SELECT ALL t4.c0 AS ref0 FROM t4 LEFT JOIN t0 ON (t4.c0) >= (t0.c0); SELECT ALL (+ (t4.c0)) AS ref0, (+ (t0.c0)) AS ref1 FROM temp_1 AS subq0, t4 RIGHT JOIN t1 ON ((t4.c0) > (IFNULL(t1.c0, t1.c1))) && (((t1.c1) IS NOT NULL) || ((t1.c1) IS NULL)) LEFT JOIN t0 ON ((t1.c0) <= (t0.c0)) && (((((t0.c0) != ((SELECT ALL t0.c0 AS ref2 FROM t4 RIGHT JOIN t0 ON (t4.c0) = (t0.c0) LIMIT 1))) AND ((t0.c0) IN (NULL, t0.c0, 1161830205, t0.c0, t0.c0))) && ((t0.c0) < (0.14556425313132382))) && ((t0.c0) IS NOT NULL)); -- Expected correct result: 00000.391847 | NULL ; 000742480000 | NULL ; 00000.776566 | NULL ; NULL | NULL (each of these rows appears 3 times) -- actual Wrong result: NULL | NULL (appears 6 times)