Wrong result returned using DISTINCT and <=> operator in JOIN

[sgykfjsm]

Bug Report

TiDB v8.5.1 returned the wrong result using DISTINCT and <=> operator in JOIN.

with base (c1, c2) as (
  select distinct 1, 'Alice'
  union
  select NULL, 'Bob' 
),
base2 (c1, c3) as (
  select 1, 100
  union
  select NULL, NULL
)
select * from base
inner join base2
	on base.c1 <=> base2.c1;

1. Minimal reproduce step (Required)

tidb:4000 > SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 8.0.11-TiDB-v8.5.1 |
+--------------------+
1 row in set (0.01 sec)

tidb:4000 > with base (c1, c2) as (
    ->   select distinct 1, 'Alice'
    ->   union
    ->   select NULL, 'Bob'
    -> ),
    -> base2 (c1, c3) as (
    ->   select 1, 100
    ->   union
    ->   select NULL, NULL
    -> )
    -> select * from base
    -> inner join base2
    -> on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
+------+-------+------+------+
1 row in set (0.00 sec)

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

v8.1.2 returns expected result.

tidb:49590 > SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 8.0.11-TiDB-v8.1.2 |
+--------------------+
1 row in set (0.00 sec)

tidb:49590 > with base (c1, c2) as (
    ->   select distinct 1, 'Alice'
    ->   union
    ->   select NULL, 'Bob'
    -> ),
    -> base2 (c1, c3) as (
    ->   select 1, 100
    ->   union
    ->   select NULL, NULL
    -> )
    -> select * from base
    -> inner join base2
    -> on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
| NULL | Bob   | NULL | NULL |
+------+-------+------+------+
2 rows in set (0.01 sec)

MySQLv8 also returns as expected.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

mysql> with base (c1, c2) as (
    ->   select distinct 1, 'Alice'
    ->   union
    ->   select NULL, 'Bob'
    -> ),
    -> base2 (c1, c3) as (
    ->   select 1, 100
    ->   union
    ->   select NULL, NULL
    -> )
    -> select * from base
    -> inner join base2
    -> on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
| NULL | Bob   | NULL | NULL |
+------+-------+------+------+
2 rows in set (0.01 sec)

If you don't use DISTINCT, you can get the expected result in v8.5.1.

tidb:4000 > with base (c1, c2) as (
    ->   select /* distinct */ 1, 'Alice'
    ->   union
    ->   select NULL, 'Bob'
    -> ),
    -> base2 (c1, c3) as (
    ->   select 1, 100
    ->   union
    ->   select NULL, NULL
    -> )
    -> select * from base
    -> inner join base2
    -> on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
| NULL | Bob   | NULL | NULL |
+------+-------+------+------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

v8.5.1 doesn't return the record joined by NULL | NULL | Bob | NULL | NULL |.

4. What is your TiDB version? (Required)

tidb:4000 > SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 8.0.11-TiDB-v8.5.1 |
+--------------------+
1 row in set (0.00 sec)

[AilinKid]

tidb> with base (c1, c2) as (   select distinct 1, 'Alice'   union   select NULL, 'Bob'  ), base2 (c1, c3) as (   select 1, 100   union   select NULL, NULL ) select * from base inner join base2 on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
+------+-------+------+------+
1 row in set (0.00 sec)

tidb> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.5.1 |
+--------------------+
1 row in set (0.00 sec)

tidb> with base (c1, c2) as (   select distinct 1, 'Alice'   union   select NULL, 'Bob'  ), base2 (c1, c3) as (   select 1, 100   union   select NULL, NULL ) select * from base inner join base2 on base.c1 <=> base2.c1;
+------+-------+------+------+
| c1   | c2    | c1   | c3   |
+------+-------+------+------+
|    1 | Alice |    1 |  100 |
| NULL | Bob   | NULL | NULL |
+------+-------+------+------+
2 rows in set (0.00 sec)

tidb> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.5.0 |
+--------------------+
1 row in set (0.00 sec)