| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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)
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)