MySQL Bugs: #117168: NULL-safe equals operator (<=>) exhibits incorrect behavior when comparing ROW values containing NULL

Bug #117168	NULL-safe equals operator (<=>) exhibits incorrect behavior when comparing ROW values containing NULL
Submitted:	9 Jan 12:02	Modified:	15 Mar 13:22
Reporter:	hui feng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	9.1.0, 8.0.40, 8.4.3	OS:	Any
Assigned to:		CPU Architecture:	Any

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).

Hello hui feng,

Thank you for the report and feedback.

regards,
Umesh

Documented fix as follows in the MySQL 9.3.0 changelog:

    The NULL-safe equals operator (<=>) showed unexpected
    behavior when comparing multiple columns (ROW values) containing
    NULL values. Fixed by simplifying the implementation of the
    operator.

Closed.