Description:
When using the a <=> b (NULL-safe comparison) operator and the a = b operator in the same query, MySQL exhibits inconsistent behavior. In one case, the query executes correctly with a <=> b but fails to evaluate a = b correctly when a and b are NULL. BUT another one executes correctly with a <=> b and a = b.This inconsistency can lead to incorrect results in queries where both operators are used.
How to repeat:
Create Test Table and Insert Sample Data:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int,
KEY `i1` (`c1` DESC)
);
INSERT INTO `t1` VALUES (3),(NULL);
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c2` int,
KEY `i2` (`c2`)
) ;
INSERT INTO `t2` VALUES (3),(NULL);
query1:
select distinct
ref_0.c2 as c_1,
subq_0.c_0 as c_2
from
(t2 as ref_0
inner join (select
ref_2.c1 as c_0
from
(t1 as ref_1
cross join t1 as ref_2
)
) as subq_0
on (ref_0.c2 = subq_0.c_0 ))
where
((subq_0.c_0) <=> (ref_0.c2));
output:
+------+------+
| c_1 | c_2 |
+------+------+
| 3 | 3 |
| NULL | NULL |
+------+------+
2 rows in set (0.01 sec)
query2:
select distinct
ref_0.c2 as c_1,
subq_0.c_0 as c_2
from
(t2 as ref_0
inner join (select
ref_2.c1 as c_0
from
(t1 as ref_1
cross join t1 as ref_2
)
) as subq_0
on (ref_0.c2 = subq_0.c_0 ))
where
((subq_0.c_0) <=> (ref_0.c2)) is true;
output:
+------+------+
| c_1 | c_2 |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)
In query1, the condition (ref_0.c2 = subq_0.c_0) results in NULL = NULL, which is not properly executed by MySQL, so the result incorrectly includes (NULL, NULL).
In query2, the condition (ref_0.c2 = subq_0.c_0) is executed correctly, and the result does not include (NULL, NULL).
Anyway, Both queries should return the same result, as the logic behind the comparisons should be equivalent.
mysql version:
github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174
version: 9.1.0
os version:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Description: When using the a <=> b (NULL-safe comparison) operator and the a = b operator in the same query, MySQL exhibits inconsistent behavior. In one case, the query executes correctly with a <=> b but fails to evaluate a = b correctly when a and b are NULL. BUT another one executes correctly with a <=> b and a = b.This inconsistency can lead to incorrect results in queries where both operators are used. How to repeat: Create Test Table and Insert Sample Data: DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` int, KEY `i1` (`c1` DESC) ); INSERT INTO `t1` VALUES (3),(NULL); DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `c2` int, KEY `i2` (`c2`) ) ; INSERT INTO `t2` VALUES (3),(NULL); query1: select distinct ref_0.c2 as c_1, subq_0.c_0 as c_2 from (t2 as ref_0 inner join (select ref_2.c1 as c_0 from (t1 as ref_1 cross join t1 as ref_2 ) ) as subq_0 on (ref_0.c2 = subq_0.c_0 )) where ((subq_0.c_0) <=> (ref_0.c2)); output: +------+------+ | c_1 | c_2 | +------+------+ | 3 | 3 | | NULL | NULL | +------+------+ 2 rows in set (0.01 sec) query2: select distinct ref_0.c2 as c_1, subq_0.c_0 as c_2 from (t2 as ref_0 inner join (select ref_2.c1 as c_0 from (t1 as ref_1 cross join t1 as ref_2 ) ) as subq_0 on (ref_0.c2 = subq_0.c_0 )) where ((subq_0.c_0) <=> (ref_0.c2)) is true; output: +------+------+ | c_1 | c_2 | +------+------+ | 3 | 3 | +------+------+ 1 row in set (0.00 sec) In query1, the condition (ref_0.c2 = subq_0.c_0) results in NULL = NULL, which is not properly executed by MySQL, so the result incorrectly includes (NULL, NULL). In query2, the condition (ref_0.c2 = subq_0.c_0) is executed correctly, and the result does not include (NULL, NULL). Anyway, Both queries should return the same result, as the logic behind the comparisons should be equivalent. mysql version: github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174 version: 9.1.0 os version: Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux