Description:
the inner subquery in `t3.c0 IN (subquery)` is logically empty because the only candidate row has `t1.c2 = 1` and the filter `t1.c2 <= IFNULL(NULL, t1.c2)` evaluates to `NULL`; therefore the `IN (...)` predicate should be false and the `LEFT JOIN` should leave only the unmatched `NULL` row, but the original path incorrectly matches `t3.c0 = '-0.0'`.
How to repeat:
CREATE TABLE t0(c0 DECIMAL ZEROFILL);
CREATE TABLE t1(c0 FLOAT ZEROFILL, c1 TINYTEXT, c2 DECIMAL ZEROFILL);
CREATE TABLE t2(c1 FLOAT ZEROFILL);
CREATE TABLE t3(c0 TEXT);
INSERT INTO t0 VALUES(0),(NULL),(1);
INSERT INTO t1(c1,c0) VALUES('e?o)o+', 0.019643433921495257);
INSERT INTO t1(c1,c2,c0) VALUES('_e',1,NULL);
INSERT INTO t2(c1) VALUES(788518433);
INSERT INTO t3 VALUES('-0.0');
SELECT DISTINCTROW t3.c0 AS ref0
FROM
(SELECT ALL STRAIGHT_JOIN t1.c0 AS ref0 FROM t1 RIGHT JOIN t2 ON (t1.c2) >= (t2.c1) ORDER BY t1.c0) AS subq0,
t0 LEFT JOIN t3
ON ((t0.c0) >= (t3.c0))
&& (((t3.c0) IN
(SELECT ALL t3.c0 AS ref2
FROM t3
INNER JOIN t1
ON ((t3.c0) = (t1.c1)) && ((t1.c1) < ('pJg_N'))
WHERE ((t1.c2) <= (IFNULL(NULL, t1.c2)))
AND ((t3.c0) = (IFNULL(t1.c1, t3.c0)))))
AND ((t3.c0) BETWEEN (COALESCE(COALESCE(t3.c0, '2q', '.h'), 'AX[', '1654515087'))
AND (COALESCE('-1278655551', '_uX[~2X>6', 'al&'))));
-- Expected correct result: NULL -- actual Wrong result: -0.0 ; NULL
Description: the inner subquery in `t3.c0 IN (subquery)` is logically empty because the only candidate row has `t1.c2 = 1` and the filter `t1.c2 <= IFNULL(NULL, t1.c2)` evaluates to `NULL`; therefore the `IN (...)` predicate should be false and the `LEFT JOIN` should leave only the unmatched `NULL` row, but the original path incorrectly matches `t3.c0 = '-0.0'`. How to repeat: CREATE TABLE t0(c0 DECIMAL ZEROFILL); CREATE TABLE t1(c0 FLOAT ZEROFILL, c1 TINYTEXT, c2 DECIMAL ZEROFILL); CREATE TABLE t2(c1 FLOAT ZEROFILL); CREATE TABLE t3(c0 TEXT); INSERT INTO t0 VALUES(0),(NULL),(1); INSERT INTO t1(c1,c0) VALUES('e?o)o+', 0.019643433921495257); INSERT INTO t1(c1,c2,c0) VALUES('_e',1,NULL); INSERT INTO t2(c1) VALUES(788518433); INSERT INTO t3 VALUES('-0.0'); SELECT DISTINCTROW t3.c0 AS ref0 FROM (SELECT ALL STRAIGHT_JOIN t1.c0 AS ref0 FROM t1 RIGHT JOIN t2 ON (t1.c2) >= (t2.c1) ORDER BY t1.c0) AS subq0, t0 LEFT JOIN t3 ON ((t0.c0) >= (t3.c0)) && (((t3.c0) IN (SELECT ALL t3.c0 AS ref2 FROM t3 INNER JOIN t1 ON ((t3.c0) = (t1.c1)) && ((t1.c1) < ('pJg_N')) WHERE ((t1.c2) <= (IFNULL(NULL, t1.c2))) AND ((t3.c0) = (IFNULL(t1.c1, t3.c0))))) AND ((t3.c0) BETWEEN (COALESCE(COALESCE(t3.c0, '2q', '.h'), 'AX[', '1654515087')) AND (COALESCE('-1278655551', '_uX[~2X>6', 'al&')))); -- Expected correct result: NULL -- actual Wrong result: -0.0 ; NULL