In clause with null causes incorrect results

[jrosenberg-applause]

Bug Report

1. Minimal reproduce step (Required)

create table A(a int primary key, b int);
create table B(b int primary key);
create table C(c int primary key, b int);

insert into A values (2, 1), (3, 2);
insert into B values (1), (2);

-- Returns data as expected
select b.b
from A a
left join (
  B b
  left join C c on b.b = c.b)
on b.b = a.b
where a.a in (2, 3);

-- Returns null
select b.b
from A a
left join (
  B b
  left join C c on b.b = c.b)
on b.b = a.b
where a.a in (2, 3, null);

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

Both queries should return two rows with values

+------+
| b    |
+------+
|    1 |
|    2 |
+------+

3. What did you see instead (Required)

The second query, with the addition of null in the in clause, returns two rows both with value null.

+------+
| b    |
+------+
| NULL |
| NULL |
+------+

In mysql this works as expected and both queries return the correct data.

4. What is your TiDB version? (Required)

Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:30:59
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore

[YangKeao]

It seems that the plans are different:

mysql> select b.b
    -> from A a
    -> left join (
    ->   B b
    ->   left join C c on b.b = c.b)
    -> on b.b = a.b
    -> where a.a in (2, 3);
+------+
| b    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> explain select b.b from A a left join (   B b   left join C c on b.b = c.b) on b.b = a.b where a.a in (2, 3);
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
| id                              | estRows  | task      | access object | operator info                                   |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
| HashJoin_12                     | 3.12     | root      |               | left outer join, equal:[eq(test.a.b, test.b.b)] |
| ├─Batch_Point_Get_13(Build)     | 2.00     | root      | table:A       | handle:[2 3], keep order:false, desc:false      |
| └─HashJoin_14(Probe)            | 12487.50 | root      |               | left outer join, equal:[eq(test.b.b, test.c.b)] |
|   ├─TableReader_20(Build)       | 9990.00  | root      |               | data:Selection_19                               |
|   │ └─Selection_19              | 9990.00  | cop[tikv] |               | not(isnull(test.c.b))                           |
|   │   └─TableFullScan_18        | 10000.00 | cop[tikv] | table:c       | keep order:false, stats:pseudo                  |
|   └─TableReader_17(Probe)       | 10000.00 | root      |               | data:TableFullScan_16                           |
|     └─TableFullScan_16          | 10000.00 | cop[tikv] | table:b       | keep order:false, stats:pseudo                  |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
8 rows in set (0.01 sec)

mysql> select b.b
    -> from A a
    -> left join (
    ->   B b
    ->   left join C c on b.b = c.b)
    -> on b.b = a.b
    -> where a.a in (2, 3, null);
+------+
| b    |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.01 sec)

mysql> explain select b.b from A a left join (   B b   left join C c on b.b = c.b) on b.b = a.b where a.a in (2, 3, null);
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
| id                              | estRows  | task      | access object | operator info                                   |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
| HashJoin_13                     | 3.12     | root      |               | left outer join, equal:[eq(test.a.b, test.b.b)] |
| ├─Batch_Point_Get_14(Build)     | 2.00     | root      | table:A       | handle:[2 3], keep order:false, desc:false      |
| └─HashJoin_26(Probe)            | 12487.50 | root      |               | inner join, equal:[eq(test.c.b, test.b.b)]      |
|   ├─TableReader_29(Build)       | 9990.00  | root      |               | data:Selection_28                               |
|   │ └─Selection_28              | 9990.00  | cop[tikv] |               | not(isnull(test.c.b))                           |
|   │   └─TableFullScan_27        | 10000.00 | cop[tikv] | table:c       | keep order:false, stats:pseudo                  |
|   └─TableReader_31(Probe)       | 10000.00 | root      |               | data:TableFullScan_30                           |
|     └─TableFullScan_30          | 10000.00 | cop[tikv] | table:b       | keep order:false, stats:pseudo                  |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------+
8 rows in set (0.01 sec)

mysql> 

Add sig/planner label.