Description:
Hello,
Recently we find a case that resultset of a query changes after a unique index is created.
Please reference to `How to repeat` part for detail.
How to repeat:
create database if not exists bugtest;
use bugtest;
drop table if exists yyy;
create table yyy(id int primary key,c1 int);
insert into yyy values(1,NULL);
insert into yyy values(2,NULL);
insert into yyy values(3,3);
insert into yyy values(4,NULL);
mysql> select * from yyy where NULL<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 4 | NULL |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from yyy where (INET_ATON('452683762'))<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 4 | NULL |
+----+------+
3 rows in set, 1 warning (0.00 sec)
alter table yyy add unique index c1(c1);
mysql> select * from yyy where NULL<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 4 | NULL |
+----+------+
3 rows in set (0.00 sec)
# Incorrect result
mysql> select * from yyy where (INET_ATON('452683762'))<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
+----+------+
1 row in set, 3 warnings (0.00 sec)
Description: Hello, Recently we find a case that resultset of a query changes after a unique index is created. Please reference to `How to repeat` part for detail. How to repeat: create database if not exists bugtest; use bugtest; drop table if exists yyy; create table yyy(id int primary key,c1 int); insert into yyy values(1,NULL); insert into yyy values(2,NULL); insert into yyy values(3,3); insert into yyy values(4,NULL); mysql> select * from yyy where NULL<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | | 2 | NULL | | 4 | NULL | +----+------+ 3 rows in set (0.00 sec) mysql> select * from yyy where (INET_ATON('452683762'))<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | | 2 | NULL | | 4 | NULL | +----+------+ 3 rows in set, 1 warning (0.00 sec) alter table yyy add unique index c1(c1); mysql> select * from yyy where NULL<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | | 2 | NULL | | 4 | NULL | +----+------+ 3 rows in set (0.00 sec) # Incorrect result mysql> select * from yyy where (INET_ATON('452683762'))<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | +----+------+ 1 row in set, 3 warnings (0.00 sec)