Incorrect query result caused by LEFT/RIGHT JOIN

[Syang111]

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

[aytrack]

[16:14:44]TiDB root:test> SELECT * 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         | flag |
+------------+------+
| 1.4013e-45 | 1    |
+------------+------+
1 row in set
Time: 0.004s
[16:14:52]TiDB root:test> SELECT * 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 ;
+--------+------+
| c0     | flag |
+--------+------+
| <null> | 1    |
+--------+------+
1 row in set
Time: 0.004s
[16:15:00]TiDB root:test> explain SELECT * 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;
+--------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| id                       | estRows | task      | access object | operator info                                                                                                 |
+--------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| Projection_12            | 2.56    | root      |               | test.t0.c0, istrue(and(istrue_with_null(cast(timediff(2003-07-13, 2007-06-25), double BINARY)), 1))->Column#5 |
| └─HashJoin_14            | 2.56    | root      |               | CARTESIAN left outer join                                                                                     |
|   ├─Selection_15(Build)  | 1.60    | root      |               | eq(istrue(and(istrue_with_null(cast(timediff("2003-07-13", "2007-06-25"), double BINARY)), 1)), 1)            |
|   │ └─TableReader_18     | 2.00    | root      |               | data:TableFullScan_17                                                                                         |
|   │   └─TableFullScan_17 | 2.00    | cop[tikv] | table:t0      | keep order:false                                                                                              |
|   └─Selection_19(Probe)  | 1.60    | root      |               | eq(istrue(and(istrue_with_null(cast(timediff("2003-07-13", "2007-06-25"), double BINARY)), 1)), 1)            |
|     └─TableReader_21     | 2.00    | root      |               | data:TableFullScan_20                                                                                         |
|       └─TableFullScan_20 | 2.00    | cop[tikv] | table:t0      | keep order:false                                                                                              |
+--------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
8 rows in set
Time: 0.004s
[16:15:10]TiDB root:test> explain SELECT * 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 ;
+---------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| id                        | estRows | task      | access object | operator info                                                                                                 |
+---------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| Projection_8              | 4.00    | root      |               | test.t0.c0, istrue(and(istrue_with_null(cast(timediff(2003-07-13, 2007-06-25), double BINARY)), 1))->Column#5 |
| └─HashJoin_10             | 4.00    | root      |               | CARTESIAN left outer join                                                                                     |
|   ├─Projection_11(Build)  | 2.00    | root      |               | 1->Column#8                                                                                                   |
|   │ └─TableReader_13      | 2.00    | root      |               | data:TableFullScan_12                                                                                         |
|   │   └─TableFullScan_12  | 2.00    | cop[tikv] | table:t0      | keep order:false                                                                                              |
|   └─TableReader_15(Probe) | 2.00    | root      |               | data:TableFullScan_14                                                                                         |
|     └─TableFullScan_14    | 2.00    | cop[tikv] | table:t0      | keep order:false                                                                                              |
+---------------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+

[winoros]

The result of the select_15 and select_19 is correct, but the join is wrong.

[windtalker]

The root cause is the same as #51290

[hawkingrei]

it has been fixed by #53235