Bug #109001 Result set mismatch with <=> in where clause

• Submitted: 4 Nov 2022 6:23
• Modified: 4 Nov 2022 6:38
• Reporter: yi qian
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.0.25
• OS: Any
• CPU Architecture: Any
• Tags: <=>; dml

[4 Nov 2022 6:23] yi qian

Description:
This bug is reported by GoldenDB Team.

Problem description：
When <=> is used for filtering in the where condition, the result set returned by SQL is inconsistent with the expectation.

How to repeat:
1.prepare data:
mysql> create table t1(c0 decimal(10,0) unsigned, c1 tinyint(1));
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> insert into t1 values (1060773755,NULL),(NULL,5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

2.prepare select:
mysql> select t1.c1,((((t1.c1)<=>(t1.c0)))<(t1.c1)) from t1;
+------+---------------------------------+
| c1   | ((((t1.c1)<=>(t1.c0)))<(t1.c1)) |
+------+---------------------------------+
| NULL |                            NULL |
|    5 |                               1 |
+------+---------------------------------+
2 rows in set (0.00 sec)

3.abnormal result:
As the result above, when we execute "select t1.c1 from t1 where ((((t1.c1)<=>(t1.c0)))<(t1.c1)) is NULL;",the expected results is single NULL. However, the results were not as expected, as shown below:

mysql> select t1.c1 from t1 where ((((t1.c1)<=>(t1.c0)))<(t1.c1)) is NULL;
+------+
| c1   |
+------+
| NULL |
|    5 |
+------+
2 rows in set (0.02 sec)

[4 Nov 2022 6:38] MySQL Verification Team

Hello yi qian,

Thank you for the report and test case.
Could you please check in latest release (8.0.31)? Thank you.

###
 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table t1(c0 decimal(10,0) unsigned, c1 tinyint(1));
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> insert into t1 values (1060773755,NULL),(NULL,5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select t1.c1,((((t1.c1)<=>(t1.c0)))<(t1.c1)) from t1;
+------+---------------------------------+
| c1   | ((((t1.c1)<=>(t1.c0)))<(t1.c1)) |
+------+---------------------------------+
| NULL |                            NULL |
|    5 |                               1 |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql> select t1.c1 from t1 where ((((t1.c1)<=>(t1.c0)))<(t1.c1)) is NULL;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

regards,
Umesh