Description:
`(+t1.c0) != (+NULL)` is never true, so the inner subquery should be empty. Therefore the outer `LEFT JOIN` should never match `t1.c0 = 2`, and `ref2` should stay `NULL` for every outer `t0` row. However, MySQL still attaches `ref2 = 2` to the `t0 = 0` and `t0 = 1` rows.
How to repeat:
CREATE TABLE t0(c0 INT UNIQUE);
CREATE TABLE t1 LIKE t0;
INSERT INTO t0 VALUES(0), (1), (3);
INSERT INTO t1 VALUES(2);
SELECT DISTINCT subq0.ref0 AS ref0, t0.c0 AS ref1, t1.c0 AS ref2
FROM (SELECT NULL AS ref0 FROM t0) AS subq0,
t0 LEFT JOIN t1
ON (t0.c0 <= (+t1.c0))
AND (t1.c0 IN (
SELECT t1.c0
FROM t1
LEFT JOIN t0 ON (t1.c0) LIKE (t0.c0)
WHERE (+t1.c0) != (+NULL)))
ORDER BY ref1, ref2; -- Expected correct result: NULL | 0 | NULL ; NULL | 1 | NULL ; NULL | 3 | NULL -- actual Wrong result: NULL | 0 | 2 ; NULL | 1 | 2 ; NULL | 3 | NULL
Description: `(+t1.c0) != (+NULL)` is never true, so the inner subquery should be empty. Therefore the outer `LEFT JOIN` should never match `t1.c0 = 2`, and `ref2` should stay `NULL` for every outer `t0` row. However, MySQL still attaches `ref2 = 2` to the `t0 = 0` and `t0 = 1` rows. How to repeat: CREATE TABLE t0(c0 INT UNIQUE); CREATE TABLE t1 LIKE t0; INSERT INTO t0 VALUES(0), (1), (3); INSERT INTO t1 VALUES(2); SELECT DISTINCT subq0.ref0 AS ref0, t0.c0 AS ref1, t1.c0 AS ref2 FROM (SELECT NULL AS ref0 FROM t0) AS subq0, t0 LEFT JOIN t1 ON (t0.c0 <= (+t1.c0)) AND (t1.c0 IN ( SELECT t1.c0 FROM t1 LEFT JOIN t0 ON (t1.c0) LIKE (t0.c0) WHERE (+t1.c0) != (+NULL))) ORDER BY ref1, ref2; -- Expected correct result: NULL | 0 | NULL ; NULL | 1 | NULL ; NULL | 3 | NULL -- actual Wrong result: NULL | 0 | 2 ; NULL | 1 | 2 ; NULL | 3 | NULL