Incorrect Result by LEFT JOIN

[bajinsheng]

Bug Report

Inconsistent results by LEFT JOIN.

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BLOB(5), c1 BLOB(5));
CREATE TABLE t1 (c0 BOOL);
INSERT INTO t1 VALUES(false);
INSERT INTO t0(c0, c1) VALUES ('>', true);

SELECT * FROM t0 LEFT OUTER JOIN t1 ON NULL; -- {>|1|NULL}
SELECT NOT '2' =(t1.c0 AND t0.c1 IS NULL) FROM t0 LEFT OUTER JOIN t1 ON NULL; -- {1}
SELECT * FROM t0 LEFT JOIN t1 ON NULL WHERE NOT '2' =(t1.c0 AND t0.c1 IS NULL); -- Empty set

2. What did you expect to see? (Required)

The first SELECT returns non-empty results and the predicate NOT '2' =(t1.c0 AND t0.c1 IS NULL) is evaluated TRUE in the second SELECT, but the third SELECT returns nothing.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

| Release Version: v6.4.0-alpha-12-g51a6684fb
Edition: Community
Git Commit Hash: 51a6684fb767cfd86a8312e0e6447963b927c791
Git Branch: master
UTC Build Time: 2022-10-05 12:40:23
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |

[Yisaer]

The main reason is that planner transformed left outer join into inner join due to it thinks NOT '2' =(t1.c0 AND t0.c1 IS NULL) must filter the rows which contained NULL for inner table, which is wrong.