Description:
add unique key on a table。The query result set is inconsistent before and after adding the index
How to repeat:
create table t1(id int key,c1 int);
insert into t1 values(1,NULL),(2,NULL),(3,3),(4,NULL);
select * from t1 where (INET_ATON('452683762'))<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 4 | NULL |
+----+------+
3 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton |
+---------+------+--------------------------------------------------------------+
1 row in set (0.01 sec)
alter table t1 add unique index c1(c1);
select * from t1 where (INET_ATON('452683762'))<=>c1;
+----+------+
| id | c1 |
+----+------+
| 1 | NULL |
+----+------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton |
| Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton |
| Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton |
+---------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
Description: add unique key on a table。The query result set is inconsistent before and after adding the index How to repeat: create table t1(id int key,c1 int); insert into t1 values(1,NULL),(2,NULL),(3,3),(4,NULL); select * from t1 where (INET_ATON('452683762'))<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | | 2 | NULL | | 4 | NULL | +----+------+ 3 rows in set, 1 warning (0.01 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton | +---------+------+--------------------------------------------------------------+ 1 row in set (0.01 sec) alter table t1 add unique index c1(c1); select * from t1 where (INET_ATON('452683762'))<=>c1; +----+------+ | id | c1 | +----+------+ | 1 | NULL | +----+------+ 1 row in set, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton | | Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton | | Warning | 1411 | Incorrect string value: ''452683762'' for function inet_aton | +---------+------+--------------------------------------------------------------+ 3 rows in set (0.00 sec)