Bug #117055 Query result may be incorrect if unique index contains NULL
Submitted: 27 Dec 2024 10:11 Modified: 27 Dec 2024 12:47
Reporter: Huaxiong Song (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.40, 8.4.3 OS:Any
Assigned to: CPU Architecture:Any

[27 Dec 2024 10:11] Huaxiong Song 
Description:
As stated in the title, when the unique index contains NULL, the query results may be incorrect

How to repeat:
CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  `c1` bigint NOT NULL,
  `c2` varchar(50) NOT NULL DEFAULT '' ,
  `c3` bigint DEFAULT NULL ,
  PRIMARY KEY (`id`)
);
CREATE TABLE `t2` (
  `c1` bigint NOT NULL,
  `c2` varchar(50) DEFAULT NULL,
  `c3` bigint DEFAULT NULL,
  UNIQUE KEY `uk_1` (`c1`,`c2`),
  UNIQUE KEY `uk_2` (`c1`,`c3`)
);

INSERT INTO t1 (id, c1, c2, c3) VALUES(1, 1, '', 1);
INSERT INTO t1 (id, c1, c2, c3) VALUES(2, 1, '', 2);
INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 1);
INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 2);

--query 1
select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
left join t2
on
t1.c1=t2.c1
and
(nullif(t1.c2,'') <=> t2.c2)
and
t1.c3 <=> t2.c3
where
t1.id = 1;

--query 2 with force index
select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
left join t2 force index(uk_2)
on
t1.c1=t2.c1
and
(nullif(t1.c2,'') <=> t2.c2)
and
t1.c3 <=> t2.c3
where
t1.id = 1;

We can see different result of queries:
query 1:
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 | NULL |
+----------------------------+----+------+

query 2:
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 |    1 |
+----------------------------+----+------+
[27 Dec 2024 10:18] Zihao Wang 
Most likely, ICP does not take into account the <=> operator.
[27 Dec 2024 10:43] MySQL Verification Team 
Hello Huaxiong Song,

Thank you for the report and test case.

regards,
Umesh
[27 Dec 2024 10:53] MySQL Verification Team 
I quickly tried on 8.0.40 but not seeing any issues, is there anything I'm missing here? Please let me know 

-- 8.0.40

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.40 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 test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t1 (
    ->   id bigint NOT NULL,
    ->   c1 bigint NOT NULL,
    ->   c2 varchar(50) NOT NULL DEFAULT '' ,
    ->   c3 bigint DEFAULT NULL ,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (
    ->   c1 bigint NOT NULL,
    ->   c2 varchar(50) DEFAULT NULL,
    ->   c3 bigint DEFAULT NULL,
    ->   UNIQUE KEY uk_1 (c1,c2),
    ->   UNIQUE KEY uk_2 (c1,c3)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO t1 (id, c1, c2, c3) VALUES(1, 1, '', 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 (id, c1, c2, c3) VALUES(2, 1, '', 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
    -> left join t2
    -> on
    -> t1.c1=t2.c1
    -> and
    -> (nullif(t1.c2,'') <=> t2.c2)
    -> and
    -> t1.c3 <=> t2.c3
    -> where
    -> t1.id = 1;
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 |    1 |
+----------------------------+----+------+
1 row in set (0.00 sec)

mysql> select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
    -> left join t2 force index(uk_2)
    -> on
    -> t1.c1=t2.c1
    -> and
    -> (nullif(t1.c2,'') <=> t2.c2)
    -> and
    -> t1.c3 <=> t2.c3
    -> where
    -> t1.id = 1;
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 |    1 |
+----------------------------+----+------+
1 row in set (0.00 sec)
[27 Dec 2024 12:38] Huaxiong Song 
Sorry, I rechecked my SQL. The order of insertion here will affect the final result. The following is the reproducible SQL I provided (the order of t2 insertion has changed):

CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  `c1` bigint NOT NULL,
  `c2` varchar(50) NOT NULL DEFAULT '' ,
  `c3` bigint DEFAULT NULL ,
  PRIMARY KEY (`id`)
);
CREATE TABLE `t2` (
  `c1` bigint NOT NULL,
  `c2` varchar(50) DEFAULT NULL,
  `c3` bigint DEFAULT NULL,
  UNIQUE KEY `uk_1` (`c1`,`c2`),
  UNIQUE KEY `uk_2` (`c1`,`c3`)
);

INSERT INTO t1 (id, c1, c2, c3) VALUES(1, 1, '', 1);
INSERT INTO t1 (id, c1, c2, c3) VALUES(2, 1, '', 2);
--query insert c3 = 2 first
INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 2);
INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 1);

--query 1
select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
left join t2
on
t2.c1=t1.c1
and
(nullif(t1.c2,'') <=> t2.c2)
and
t1.c3 <=> t2.c3
where
t1.id = 1;

--query 2
select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
left join t2 force index(uk_2)
on
t2.c1=t1.c1
and
(t2.c2 <=> nullif(t1.c2,''))
and
t1.c3 <=> t2.c3
where
t1.id = 1;
[27 Dec 2024 12:47] MySQL Verification Team 
Thank you, Huaxiong Song.

regards,
Umesh
[27 Dec 2024 12:48] MySQL Verification Team 
-- 8.0.40, 8.4.3 affected

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.4.3 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> drop database test;
Query OK, 2 rows affected (0.04 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` bigint NOT NULL,
    ->   `c1` bigint NOT NULL,
    ->   `c2` varchar(50) NOT NULL DEFAULT '' ,
    ->   `c3` bigint DEFAULT NULL ,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `t2` (
    ->   `c1` bigint NOT NULL,
    ->   `c2` varchar(50) DEFAULT NULL,
    ->   `c3` bigint DEFAULT NULL,
    ->   UNIQUE KEY `uk_1` (`c1`,`c2`),
    ->   UNIQUE KEY `uk_2` (`c1`,`c3`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (id, c1, c2, c3) VALUES(1, 1, '', 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (id, c1, c2, c3) VALUES(2, 1, '', 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (c1, c2, c3) VALUES(1, NULL, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
    -> left join t2
    -> on
    -> t2.c1=t1.c1
    -> and
    -> (nullif(t1.c2,'') <=> t2.c2)
    -> and
    -> t1.c3 <=> t2.c3
    -> where
    -> t1.id = 1;
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 | NULL |
+----------------------------+----+------+
1 row in set (0.00 sec)

mysql> select nullif(t1.c2,'') <=> t2.c2, t1.id,t2.c1 from t1 t1
    -> left join t2 force index(uk_2)
    -> on
    -> t2.c1=t1.c1
    -> and
    -> (t2.c2 <=> nullif(t1.c2,''))
    -> and
    -> t1.c3 <=> t2.c3
    -> where
    -> t1.id = 1;
+----------------------------+----+------+
| nullif(t1.c2,'') <=> t2.c2 | id | c1   |
+----------------------------+----+------+
|                          1 |  1 |    1 |
+----------------------------+----+------+
1 row in set (0.00 sec)

mysql>