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 2:56]
chen lee
[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)