Bug #106297 Incorrect outputs when combine unique index with NULL

• Submitted: 26 Jan 2022 22:39
• Modified: 27 Jan 2022 5:56
• Reporter: Yu Liang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.27, 8.0.28
• OS: Ubuntu (Ubuntu 20.04.3 LTS)
• CPU Architecture: x86 (x86 (Intel(R) Core(TM) i7-10700 CPU @ 2.90GHz))
• Tags: null, unique

[26 Jan 2022 22:39] Yu Liang

Description:
mysql> CREATE TABLE v0 ( c1 INT, UNIQUE KEY i2 ( c1 ) );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( NULL );
+------------+
| COUNT( * ) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

-- Expected. 2 NULLs are returned. 

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( TIME( 'x' ) );
+------------+
| COUNT( * ) |
+------------+
|          1 |
+------------+
1 row in set, 2 warnings (0.00 sec)

-- Unexpected. We should have 2 NULLs. 

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( ASCII( NULL ) );
+------------+
| COUNT( * ) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

-- Unexpected. We should have 2 NULLs. 

---------------------------------------------

In the query shown above, we create a table v0 with unique Integer column c1. We then insert two NULL values into the column. According to the documentation, that even in the UNIQUE indexed column, multiple NULL values are still permitted. And then we check the value in c1 and see whether the column contains NULL. 

Interestingly, if we compare c1 to NULL directly, the correct number of NULLs in column c1: 2 would be returned. However, if we compare c1 to a function that returns NULL, (e.g., in the example above, we use TIME('x') and ASCII(NULL), where both function should return NULL), the query only returns 1 row from c1. IMHO, the second and the third SELECT statements should return the same value as the first SELECT statement, where both NULLs from table v0 should be returned. 

Additionally, from the sample above, if we remove the UNIQUE KEY i2 from c1, it could fix the problem and the all SELECT statements return 2 NULLs as expected. 

How to repeat:
In MySQL 8.0.27, use the following command: 

mysql> CREATE TABLE v0 ( c1 INT, UNIQUE KEY i2 ( c1 ) );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO v0 ( c1 ) VALUES ( NULL );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( NULL );
+------------+
| COUNT( * ) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

-- Expected. 2 NULLs are returned. 

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( TIME( 'x' ) );
+------------+
| COUNT( * ) |
+------------+
|          1 |
+------------+
1 row in set, 2 warnings (0.00 sec)

-- Unexpected. We should have 2 NULLs. 

mysql> SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( ASCII( NULL ) );
+------------+
| COUNT( * ) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

-- Unexpected. We should have 2 NULLs. 

Suggested fix:
For the three SELECT statements provided from the sample query, they should all return 2 row of NULLs as results. Thus, the outputs should be:

CREATE TABLE v0 ( c1 INT, UNIQUE KEY i2 ( c1 ) );
INSERT INTO v0 ( c1 ) VALUES ( NULL );
INSERT INTO v0 ( c1 ) VALUES ( NULL );

SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( NULL );
-- Return 2
SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( TIME( 'x' ) );
-- Return 2
SELECT COUNT( * ) FROM v0 WHERE  c1 <=> ( ASCII( NULL ) );
-- Return 2

[27 Jan 2022 5:56] MySQL Verification Team

Hello Yu Liang,

Thank you for the report and test case.

regards,
Umesh