Bug #116517 inner join on = and <=> of null return wrong result

• Submitted: 31 Oct 2024 7:08
• Modified: 31 Oct 2024 7:22
• Reporter: zkong kong
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.1.0
• OS: Any
• CPU Architecture: Any

[31 Oct 2024 7:08] zkong kong

Description:
Inner join on = and <=> with null value return wrong result:

mysql> select * from t1 inner join t3 on (`t3`.`id` = `t1`.`id`) and (`t1`.`id` <=> `t3`.`id`);
+------+------+------+------+------+------+
| id   | a    | b    | id   | a    | b    |
+------+------+------+------+------+------+
| NULL |    5 |    5 | NULL |    3 |    3 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 inner join t3 on t3.id = t1.id and (t1.id = t3.id or (t1.id is null and t3.id is null));
Empty set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.1.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat:
create table t1(id int, a int, b int);
alter table t1 add key(id);
create table t3 like t1;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(null,5,5);
insert into t3 values(null,3,3);

select * from t1 inner join t3 on (`t3`.`id` = `t1`.`id`) and (`t1`.`id` <=> `t3`.`id`);

[31 Oct 2024 7:22] zkong kong

Sorry It's duplicate with this of our partner:
https://bugs.mysql.com/bug.php?id=115283

[31 Oct 2024 10:16] MySQL Verification Team

Hi Mr. kong

Thank you for your feedback.

So, the new status is "Duplicate".