Bug #109014 is null acts abnormally in select statements
Submitted: 7 Nov 2022 2:56 Modified: 7 Nov 2022 8:17
Reporter: chen lee Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.40 OS:Any
Assigned to: CPU Architecture:Any

[7 Nov 2022 2:56] chen lee
Description:
Hello,
 I'm from goldendb team。
 Recently we find a case that result of is null produces different value。

How to repeat:
DROP DATABASE IF EXISTS database01;
CREATE DATABASE database01;
USE database01
CREATE TABLE t0(id int auto_increment primary key, c0 BIGINT UNSIGNED NOT NULL , c1 VARCHAR(211) , c2 DECIMAL UNSIGNED DEFAULT 1060773755 , c3 BIT(20) , c4 BIT(61) , c5 BOOL , c6 DECIMAL ZEROFILL );
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (NULL, B'1111111111111111111111111111111110111011101011011110111101011011', '0', 443876689, '\\', 917182002, '\\'); 
INSERT IGNORE  INTO t0(c1, c3) VALUES ('-531828911', NULL);
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (0.9070151249339611, '', NULL, 514805161, 1378092258, '5', '9'), ('1954854029', NULL, -14225591, '짒', -1668139522, '-', 0.9141654751291873), ('I', 184787323, '밇섅', NULL, B'1111111111111111111111111111111111111111001001101110111101001001', -1860745424, NULL), ('z', NULL, NULL, NULL, 0.4141590524109303, 'T', '0'); 
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES ('', -1475379731, NULL, '', NULL, '8', 292372005), (929580637, '', 'C', '', '5*1Tg	n2', -1901911380, -275031935), ('', B'100101111011100011101000000101', '/', 0.24271362381830097, '	', '0', 2005214647); 
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (172146949, NULL, 'V', '', 't', 1690429312, 184787323); 
SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
 
 select t0.c2,t0.c5,(((((t0.c5)<=>(t0.c2)))<(t0.c5))) from t0;
+------------+------+-----------------------------------+
| c2         | c5   | (((((t0.c5)<=>(t0.c2)))<(t0.c5))) |
+------------+------+-----------------------------------+
|          0 |  127 |                                 1 |
| 1060773755 | NULL |                              NULL |
|       NULL |    5 |                                 1 |
|          0 |    0 |                                 0 |
|          0 | -128 |                                 0 |
|       NULL |    0 |                                 0 |
|       NULL |    8 |                                 1 |
|          0 | -128 |                                 0 |
|          0 |    0 |                                 0 |
|          0 |  127 |                                 1 |
+------------+------+-----------------------------------+

expected only one rows,but it returns 4 rows.
 SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
+----+
| c0 |
+----+
|  0 |
|  1 |
|  0 |
|  0 |
+----+
4 rows in set (0.00 sec)
[7 Nov 2022 8:17] MySQL Verification Team
Hello chen lee,

Thank you for the report and test case.
Observed that 5.7.40 is affected(8.0.31 seems to be fine).

regards,
Umesh
[7 Nov 2022 8:17] MySQL Verification Team
- 5.7.40

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 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> DROP DATABASE IF EXISTS database01;
CREATE DATABASE database01;
USE database01
CREATE TABLE t0(id int auto_increment primary key, c0 BIGINT UNSIGNED NOT NULL , c1 VARCHAR(211) , c2 DECIMAL UNSIGNED DEFAULT 1060773755 , c3 BIT(20) , c4 BIT(61) , c5 BOOL , c6 DECIMAL ZEROFILL );
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (NULL, B'1111111111111111111111111111111110111011101011011110111101011011', '0', 443876689, '\\', 917182002, '\\');
INSERT IGNORE  INTO t0(c1, c3) VALUES ('-531828911', NULL);
INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (0.9070151249339611, '', NULL, 514805161, 1378092258, '5', '9'), ('1954854029', NULL, -14225591, '짒', -1668139522, '-', 0.9141654751291873), ('I', 184787323, '밇섅', NULL, B'1111111111111111111111111111111111111111001001101110111101001001', -1860745424, NULL), ('z', NULL, NULL, NULL, 0.4141590524109303, 'T', '0');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE database01;
Query OK, 1 row affected (0.00 sec)

mysql> USE database01
Database changed
mysql> CREATE TABLE t0(id int auto_increment primary key, c0 BIGINT UNSIGNED NOT NULL , c1 VARCHAR(211) , c2 DECIMAL UNSIGNED DEFAULT 1060773755 , c3 BIT(20) , c4 BIT(61) , c5 BOOL , c6 DECIMAL ZEROFILL );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (NULL, B'1111111111111111111111111111111110111011101011011110111101011011', '0', 443876689, '\\', 917182002, '\\');
Query OK, 1 row affected, 4 warnings (0.00 sec)

mysql> INSERT IGNORE  INTO t0(c1, c3) VALUES ('-531828911', NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (0.9070151249339611, '', NULL, 514805161, 1378092258, '5', '9'), ('1954854029', NULL, -14225591, '짒', -1668139522, '-', 0.9141654751291873), ('I', 184787323, '밇섅', NULL, B'1111111111111111111111111111111111111111001001101110111101001001', -1860745424, NULL), ('z', NULL, NULL, NULL, 0.4141590524109303, 'T', '0');
Query OK, 4 rows affected, 12 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 12

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES ('', -1475379731, NULL, '', NULL, '8', 292372005), (929580637, '', 'C', '', '5*1Tgn2', -1901911380, -275031935), ('', B'100101111011100011101000000101', '/', 0.24271362381830097, '', '0', 2005214647);
Query OK, 3 rows affected, 6 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 6

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (172146949, NULL, 'V', '', 't', 1690429312, 184787323);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
+----+
| c0 |
+----+
|  0 |
|  1 |
|  0 |
|  0 |
+----+
4 rows in set (0.00 sec)

mysql> select t0.c2,t0.c5,(((((t0.c5)<=>(t0.c2)))<(t0.c5))) from t0;
+------------+------+-----------------------------------+
| c2         | c5   | (((((t0.c5)<=>(t0.c2)))<(t0.c5))) |
+------------+------+-----------------------------------+
|          0 |  127 |                                 1 |
| 1060773755 | NULL |                              NULL |
|       NULL |    5 |                                 1 |
|          0 |    0 |                                 0 |
|          0 | -128 |                                 0 |
|       NULL |    0 |                                 0 |
|       NULL |    8 |                                 1 |
|          0 | -128 |                                 0 |
|          0 |    0 |                                 0 |
|          0 |  127 |                                 1 |
+------------+------+-----------------------------------+
10 rows in set (0.00 sec)

mysql> SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
+----+
| c0 |
+----+
|  0 |
|  1 |
|  0 |
|  0 |
+----+
4 rows in set (0.00 sec)

mysql>
[7 Nov 2022 8:18] MySQL Verification Team
-8.0.31

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> DROP DATABASE IF EXISTS database01;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE DATABASE database01;
Query OK, 1 row affected (0.00 sec)

mysql> USE database01
Database changed
mysql> CREATE TABLE t0(id int auto_increment primary key, c0 BIGINT UNSIGNED NOT NULL , c1 VARCHAR(211) , c2 DECIMAL UNSIGNED DEFAULT 1060773755 , c3 BIT(20) , c4 BIT(61) , c5 BOOL , c6 DECIMAL ZEROFILL );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.                                                  |
| Warning | 1681 | The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (NULL, B'1111111111111111111111111111111110111011101011011110111101011011', '0', 443876689, '\\', 917182002, '\\');
Query OK, 1 row affected, 5 warnings (0.00 sec)

mysql> INSERT IGNORE  INTO t0(c1, c3) VALUES ('-531828911', NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (0.9070151249339611, '', NULL, 514805161, 1378092258, '5', '9'), ('1954854029', NULL, -14225591, '짒', -1668139522, '-', 0.9141654751291873), ('I', 184787323, '밇섅', NULL, B'1111111111111111111111111111111111111111001001101110111101001001', -1860745424, NULL), ('z', NULL, NULL, NULL, 0.4141590524109303, 'T', '0');
Query OK, 4 rows affected, 12 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 12

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES ('', -1475379731, NULL, '', NULL, '8', 292372005), (929580637, '', 'C', '', '5*1Tgn2', -1901911380, -275031935), ('', B'100101111011100011101000000101', '/', 0.24271362381830097, '', '0', 2005214647);
Query OK, 3 rows affected, 7 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 7

mysql> INSERT IGNORE  INTO t0(c0, c1, c2, c3, c4, c5, c6) VALUES (172146949, NULL, 'V', '', 't', 1690429312, 184787323);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
+----+
| c0 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

mysql> select t0.c2,t0.c5,(((((t0.c5)<=>(t0.c2)))<(t0.c5))) from t0;
+------------+------+-----------------------------------+
| c2         | c5   | (((((t0.c5)<=>(t0.c2)))<(t0.c5))) |
+------------+------+-----------------------------------+
|          0 |  127 |                                 1 |
| 1060773755 | NULL |                              NULL |
|       NULL |    5 |                                 1 |
|          0 |    0 |                                 0 |
|          0 | -128 |                                 0 |
|       NULL |    0 |                                 0 |
|       NULL |    8 |                                 1 |
|          0 | -128 |                                 0 |
|          0 |    0 |                                 0 |
|          0 |  127 |                                 1 |
+------------+------+-----------------------------------+
10 rows in set (0.00 sec)

mysql>  SELECT t0.c0 FROM t0 WHERE ((((((t0.c5)<=>(t0.c2)))<(t0.c5))) IS NULL);
+----+
| c0 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)