Description:
REPLACE does not detect a match on a UNIQUE key when a NULL value appears in one of the columns.
How to repeat:
mysql> create table piggywiggy (a integer, b integer, c integer null);
--------------
create table piggywiggy (a integer, b integer, c integer null)
--------------
Query OK, 0 rows affected (8.36 sec)
mysql> ALTER TABLE `piggywiggy` ADD PRIMARY KEY ( `a` );
--------------
ALTER TABLE `piggywiggy` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `a` )
--------------
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary key on piggywiggy(a)' at line 1
mysql> ALTER TABLE `piggywiggy` ADD INDEX ( `b` , `c` ) ;
--------------
ALTER TABLE `piggywiggy` ADD INDEX ( `b` , `c` )
--------------
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into piggywiggy (b, c) values (10, 100);
--------------
insert into piggywiggy (b, c) values (10, 100)
--------------
Query OK, 1 row affected (0.05 sec)
mysql> insert into piggywiggy (b, c) values (11, 106);
--------------
insert into piggywiggy (b, c) values (11, 106)
--------------
Query OK, 1 row affected (0.02 sec)
mysql> insert into piggywiggy (b, c) values (12, 109);
--------------
insert into piggywiggy (b, c) values (12, 109)
--------------
Query OK, 1 row affected (0.03 sec)
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 3 | 12 | 109 |
+---+------+------+
3 rows in set (0.00 sec)
mysql> replace into piggywiggy (b, c) values (13, 110);
--------------
replace into piggywiggy (b, c) values (13, 110)
--------------
Query OK, 1 row affected (0.07 sec)
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 3 | 12 | 109 |
| 4 | 13 | 110 |
+---+------+------+
4 rows in set (0.00 sec)
mysql> replace into piggywiggy (b, c) values (12, 109);
--------------
replace into piggywiggy (b, c) values (12, 109)
--------------
Query OK, 2 rows affected (0.05 sec)
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 5 | 12 | 109 |
| 4 | 13 | 110 |
+---+------+------+
4 rows in set (0.00 sec)
mysql> replace into piggywiggy (b, c) select 12, 109;
--------------
replace into piggywiggy (b, c) select 12, 109
--------------
Query OK, 2 rows affected (0.04 sec)
Records: 1 Duplicates: 1 Warnings: 0
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 6 | 12 | 109 |
| 4 | 13 | 110 |
+---+------+------+
4 rows in set (0.00 sec)
mysql> insert into piggywiggy (b, c) values (14, NULL);
--------------
insert into piggywiggy (b, c) values (14, NULL)
--------------
Query OK, 1 row affected (0.07 sec)
mysql> replace into piggywiggy (b, c) select 14, NULL;
--------------
replace into piggywiggy (b, c) select 14, NULL
--------------
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 6 | 12 | 109 |
| 4 | 13 | 110 |
| 7 | 14 | NULL |
| 8 | 14 | NULL |
+---+------+------+
6 rows in set (0.00 sec)
mysql> replace into piggywiggy (b, c) values( 14, NULL);
--------------
replace into piggywiggy (b, c) values( 14, NULL)
--------------
Query OK, 1 row affected (0.03 sec)
mysql> select * from piggywiggy;
--------------
select * from piggywiggy
--------------
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 11 | 106 |
| 6 | 12 | 109 |
| 4 | 13 | 110 |
| 7 | 14 | NULL |
| 8 | 14 | NULL |
| 9 | 14 | NULL |
+---+------+------+
7 rows in set (0.00 sec)
Suggested fix:
It should detect the prior existance of e.g. 14 NULL, and update that record, as opposed to inserting a new one and violating the UNIQUE index.