Bug #9157 REPLACE can duplicate on a UNIQUE index
Submitted: 14 Mar 2005 0:58 Modified: 14 Mar 2005 5:14
Reporter: B Jones Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.9 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[14 Mar 2005 0:58] B Jones
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.
[14 Mar 2005 1:30] B Jones
To clarify, the index on (b, c) *IS UNIQUE*.  When I cut and paste I skipped this: Include it in the test case:

ALTER TABLE `piggywiggy` DROP INDEX `b` , ADD UNIQUE `b` ( `b` , `c` ) 

Here's a dump showing the duplicate values on the unique key at the end of the test case.

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: 127.0.0.1:3309
-- Generation Time: Mar 14, 2005 at 11:28 AM
-- Server version: 4.1.9
-- PHP Version: 4.3.8
-- 
-- Database: `test_bj`
-- 

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

-- 
-- Table structure for table `piggywiggy`
-- 

CREATE TABLE piggywiggy (
  a int(11) NOT NULL auto_increment,
  b int(11) default NULL,
  c int(11) default NULL,
  PRIMARY KEY  (a),
  UNIQUE KEY b (b,c)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;

-- 
-- Dumping data for table `piggywiggy`
-- 

INSERT INTO piggywiggy VALUES (1, 10, 100);
INSERT INTO piggywiggy VALUES (2, 11, 106);
INSERT INTO piggywiggy VALUES (6, 12, 109);
INSERT INTO piggywiggy VALUES (4, 13, 110);
INSERT INTO piggywiggy VALUES (7, 14, NULL);
INSERT INTO piggywiggy VALUES (8, 14, NULL);
INSERT INTO piggywiggy VALUES (9, 14, NULL);
[14 Mar 2005 5:14] Heikki Tuuri
Hi!

MySQL follows the Oracle convention and allows the same key value for several rows in a UNIQUE index if one of the column values is NULL. That is, Oracle and MySQL think that NULL != NULL in this context.

Regards,

Heikki