| Bug #115278 | Missing records in result when nullablle field with a unique index<=>NULL | ||
|---|---|---|---|
| Submitted: | 11 Jun 2024 6:54 | Modified: | 11 Jun 2024 8:59 |
| Reporter: | Daniel Lindholm | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.36, 8.0.37 | OS: | Any (Tested on Windows and AWS RDS)) |
| Assigned to: | CPU Architecture: | Any (Tested on x64 and AWS Graviton (Arm)) | |
[11 Jun 2024 8:27]
MySQL Verification Team
Hello Daniel, Thank you for the report and test case. I see no difference in 5.7(last GA) and 8.0.37 behavior, which 5.7 version you checked? Pls let us know. -- 5.7.44 bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 test.table1 (RecNo int NOT NULL,A int DEFAULT NULL,PRIMARY KEY (RecNo)) ENGINE = INNODB; Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE test.table1 ADD UNIQUE INDEX UK_table1 (A); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert INTO table1 (RecNo, A) VALUES (1, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (2, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (3, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (4, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (5, null); Query OK, 1 row affected (0.01 sec) mysql> EXPLAIN SELECT * FROM table1 m WHERE m.A<=>NULL; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | m | NULL | ref | UK_table1 | UK_table1 | 5 | const | 5 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>NULL; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | +-------+------+ 5 rows in set (0.00 sec) mysql> set @p1=null; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT * FROM table1 m WHERE m.A<=>@p1; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | m | NULL | const | UK_table1 | UK_table1 | 5 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>@p1; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | +-------+------+ 1 row in set (0.00 sec) -- 8.0.37 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.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, 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 if not exists test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed mysql> CREATE TABLE test.table1 (RecNo int NOT NULL,A int DEFAULT NULL,PRIMARY KEY (RecNo)) ENGINE = INNODB; Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE test.table1 ADD UNIQUE INDEX UK_table1 (A); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert INTO table1 (RecNo, A) VALUES (1, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (2, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (3, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (4, null); Query OK, 1 row affected (0.00 sec) mysql> insert INTO table1 (RecNo, A) VALUES (5, null); Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT * FROM table1 m WHERE m.A<=>NULL; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | m | NULL | ref | UK_table1 | UK_table1 | 5 | const | 5 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>NULL; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | +-------+------+ 5 rows in set (0.00 sec) mysql> set @p1=null; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT * FROM table1 m WHERE m.A<=>@p1; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | m | NULL | const | UK_table1 | UK_table1 | 5 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>@p1; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | +-------+------+ 1 row in set (0.00 sec) regards, Umesh
[11 Jun 2024 8:28]
MySQL Verification Team
Related - Bug #106297
Description: This bug was discovered after upgrading a production database from MySql 5.7 to MySql 8.0.36. The bug resulted in real problems in production. Please fix ASAP, as it is not easy to locate the affected portions of code in a large project. Short description: If a table contains a nullable field, and you put a unique index on this field, some SELECT..WHERE queries will fail to return all relevant records. SELECT queries that are supposed to return multiple records will return zero or one records. This WHERE clause will work fine: SELECT...WHERE field1<=>NULL But the same WHERE clause will fail when field1 is compared to a variable that contains the value of NULL, instead of comparing with the literal "NULL", like this: SELECT...WHERE field1<=>p1 The bug can also be seen when the field value is compared to the result of a function that returns NULL, like this: SELECT...WHERE field1<=>NULLIF('','') It seems that DELETE...WHERE statement is not affected by the bug. The bug is related (or identical) to this: https://bugs.mysql.com/bug.php?id=106297 But I demonstrate that the WHERE clause may return one row of data, even if more rows exist. Also, this bug deserves to be fixed as it has the potential to break a project after upgrading from MySQL 5.7. How to repeat: mysql> CREATE TABLE test.table1 (RecNo int NOT NULL,A int DEFAULT NULL,PRIMARY KEY (RecNo)) ENGINE = INNODB; Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE test.table1 ADD UNIQUE INDEX UK_table1 (A); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert INTO table1 (RecNo, A) VALUES (1, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (2, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (3, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (4, null); Query OK, 1 row affected (0.01 sec) mysql> insert INTO table1 (RecNo, A) VALUES (5, null); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>NULL; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | +-------+------+ 5 rows in set (0.00 sec) -- OK. As expected, all rows are returned. -- Now try the same queries using a variable containing the value of NULL, instead of the NULL literal. mysql> set @p1=null; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM table1 m WHERE m.A<=>@p1; +-------+------+ | RecNo | A | +-------+------+ | 1 | NULL | +-------+------+ 1 row in set (0.00 sec) -- Unexpected. Only row 1 is returnend. It would be expected that all 5 rows should be returned! mysql> SELECT * FROM table1 m WHERE m.A<=>@p1 AND m.RecNo>1; Empty set (0.00 sec) -- Unexpected. No rows are returnend. It would be expected that 4 rows should be returned! mysql> EXPLAIN SELECT * FROM table1 m WHERE m.A<=>@p1 AND RecNo>1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) -- MySql detects "Impossible WHERE noticed after reading const tables". This should not be the case! Suggested fix: Please fix this problem so that it works like it did in MySql 5.7.