MySQL Bugs: #9158: REPLACE can duplicate on a UNIQUE index

Bug #9158	REPLACE can duplicate on a UNIQUE index
Submitted:	14 Mar 2005 6:15	Modified:	14 Mar 2005 8:05
Reporter:	B Jones	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I can't edit MySQL Bug #9157 which has been closed, so I'm resubmitting here:

Heikki wrote:
> 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.

Hi Heikki,

I just tested this alongside on our ORACLE and MySQL servers;
ORACLE actually rejects the duplicate inserts, even with the NULLs.
MySQL accepts them without error.  

cheers, bj.

ORACLE -----------------------------------------------------------------
SQL> create table test_bj(a integer, b integer);

Table created.

SQL> create unique index b on test_bj(a, b);

Index created.

SQL> insert into test_bj values(1, null);

1 row created.

SQL> insert into test_bj values(null, 1);

1 row created.

SQL> insert into test_bj values(1, null);
insert into test_bj values(1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.B) violated

How to repeat:
MySQL ----------------------------------------------------------------------
mysql> create table test_bj(a integer, b integer);
--------------
create table test_bj(a integer, b integer)
--------------

Query OK, 0 rows affected (0.04 sec)

mysql> create unique index b on test_bj(a, b);
--------------
create unique index b on test_bj(a, b)
--------------

Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  insert into test_bj values(1, null);
--------------
insert into test_bj values(1, null)
--------------

Query OK, 1 row affected (0.00 sec)

mysql>  insert into test_bj values(null, 1);
--------------
insert into test_bj values(null, 1)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> insert into test_bj values(1, null);
--------------
insert into test_bj values(1, null)
--------------

Query OK, 1 row affected (0.03 sec)

mysql>

Suggested fix:
Please see MySQL Bug #9157; please note this also affects REPLACE.

It's a duplicate of bug#6829. See 
http://bugs.mysql.com/bug.php?id=6829
for details

Hi!

The Oracle behavior is more complex than I knew. For a single-column UNIQUE index Oracle DOES allow several null rows. But for a multi-column UNIQUE index it does only allow several null rows if ALL the columns in the index columns are null!

Regards,

Heikki

http://www-rohan.sdsu.edu/doc/oracle/server803/A54643_01/ch7.htm
"
Combining UNIQUE Key and NOT NULL Integrity Constraints
In Figure 23-3 and Figure 23-4, UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint. 

Columns with both unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter values in the unique key and also eliminates the possibility that any new row's data will ever conflict with an existing row's data. 

Note: Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint. 
"