Description:
The NULL-safe equals operator (<=>) shows unexpected behavior when comparing multiple columns (ROW values) containing NULL values. This inconsistency can lead to incorrect query results that don't match the logical expectations of NULL-safe comparisons.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t1(id int key auto_increment, `col_smallint` smallint, `col_int` int, `col_float` float);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1(col_smallint, col_int, col_float) values(NULL, 3, NULL);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT `col_smallint`,`col_int`,`col_float` FROM `t1` WHERE (`col_smallint`,`col_int`,`col_float`) <=>
-> (select NULL, 1, NULL);
+--------------+---------+-----------+
| col_smallint | col_int | col_float |
+--------------+---------+-----------+
| NULL | 3 | NULL |
+--------------+---------+-----------+
1 row in set (0.00 sec)
How to repeat:
create database test;
use test;
create table t1(id int key auto_increment, `col_smallint` smallint, `col_int` int, `col_float` float);
insert into t1(col_smallint, col_int, col_float) values(NULL, 3, NULL);
SELECT `col_smallint`,`col_int`,`col_float` FROM `t1` WHERE (`col_smallint`,`col_int`,`col_float`) <=>
(select NULL, 1, NULL);
Suggested fix:
The query should return no rows since the ROW values are not equal (the col_int values 3 and 1 are different).
Description: The NULL-safe equals operator (<=>) shows unexpected behavior when comparing multiple columns (ROW values) containing NULL values. This inconsistency can lead to incorrect query results that don't match the logical expectations of NULL-safe comparisons. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed mysql> create table t1(id int key auto_increment, `col_smallint` smallint, `col_int` int, `col_float` float); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1(col_smallint, col_int, col_float) values(NULL, 3, NULL); Query OK, 1 row affected (0.01 sec) mysql> SELECT `col_smallint`,`col_int`,`col_float` FROM `t1` WHERE (`col_smallint`,`col_int`,`col_float`) <=> -> (select NULL, 1, NULL); +--------------+---------+-----------+ | col_smallint | col_int | col_float | +--------------+---------+-----------+ | NULL | 3 | NULL | +--------------+---------+-----------+ 1 row in set (0.00 sec) How to repeat: create database test; use test; create table t1(id int key auto_increment, `col_smallint` smallint, `col_int` int, `col_float` float); insert into t1(col_smallint, col_int, col_float) values(NULL, 3, NULL); SELECT `col_smallint`,`col_int`,`col_float` FROM `t1` WHERE (`col_smallint`,`col_int`,`col_float`) <=> (select NULL, 1, NULL); Suggested fix: The query should return no rows since the ROW values are not equal (the col_int values 3 and 1 are different).