Incorrect query result caused by LEFT/RIGHT JOIN #51361
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t0(c0 FLOAT);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
INSERT INTO t0(c0) VALUES (NULL);
SELECT t0.c0 FROM v0 LEFT JOIN t0 ON 1 WHERE (TIMEDIFF( '2003-07-13', '2007-06-25') AND true);
SELECT c0 FROM (SELECT t0.c0, (TIMEDIFF('2003-07-13', '2007-06-25') AND true) IS TRUE AS flag FROM v0 LEFT JOIN t0 ON 1) AS t WHERE flag=1;
2. What did you expect to see? (Required)
The following two sqls should return the same result
SELECT t0.c0 FROM v0 LEFT JOIN t0 ON 1 WHERE (TIMEDIFF( '2003-07-13', '2007-06-25') AND true);
SELECT c0 FROM (SELECT t0.c0, (TIMEDIFF('2003-07-13', '2007-06-25') AND true) IS TRUE AS flag FROM v0 LEFT JOIN t0 ON 1) AS t WHERE flag=1;
3. What did you see instead (Required)
These two sqls return different results. If change LEFT JOIN to RIGHT JOIN, you also get the same result as the one below.
mysql> SELECT t0.c0 FROM v0 LEFT JOIN t0 ON 1 WHERE (TIMEDIFF( '2003-07-13', '2007-06-25') AND true);
+------+
| c0 |
+------+
| NULL |
+------+
1 row in set, 2 warnings (0.01 sec)
mysql> SELECT c0 FROM (SELECT t0.c0, (TIMEDIFF('2003-07-13', '2007-06-25') AND true) IS TRUE AS flag FROM v0 LEFT JOIN t0 ON 1) AS t WHERE flag=1;
+------------+
| c0 |
+------------+
| 1.4013e-45 |
+------------+
1 row in set, 4 warnings (0.01 sec)
4. What is your TiDB version? (Required)
TiDB-v7.6.0