Bug #16219 Unique index not unique when containing NULL columns
Submitted: 5 Jan 2006 12:01 Modified: 5 Jan 2006 12:22
Reporter: Daniel Jaenecke Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.X OS:Linux (Linux)
Assigned to:

[5 Jan 2006 12:01] Daniel Jaenecke
Description:
A combined unique index containing a column which may be NULL is not unique in case the value for that column is null. For example it is possible to 

    INSERT INTO foo ( col1, col2 ) VALUES ( NULL, 1 )

several times even tough (col1, col2) was declared unique.

How to repeat:
mysql> CREATE TABLE t7(
    -> id TINYINT NOT NULL auto_increment,
    -> col1 TINYINT NULL DEFAULT NULL,
    -> col2 TINYINT NOT NULL,
    -> UNIQUE( col1, col2 ),
    -> PRIMARY KEY( id )
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t7 ( col1, col2 ) VALUES ( 1, 2 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t7 ( col1, col2 ) VALUES ( 1, 2 );
ERROR 1062 (23000): Duplicate entry '1-2' for key 2
mysql> INSERT INTO t7 ( col1, col2 ) VALUES ( NULL, 2 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t7 ( col1, col2 ) VALUES ( NULL, 2 );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t7;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    2 |
|  2 | NULL |    2 |
|  3 | NULL |    2 |
+----+------+------+
3 rows in set (0.00 sec)
[5 Jan 2006 12:11] Miguel Solorzano
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please read:

http://dev.mysql.com/doc/refman/4.1/en/problems-with-null.html
[5 Jan 2006 12:29] Miguel Solorzano
If you see in the Manual:

"In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example return NULL:

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

If you want to search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression: "

so in MySQL you got unique values when using NULL as part of an index.
[6 Jan 2006 12:04] Sergei Golubchik
I want to add that it's the behaviour defined by the SQL standard, it's how MySQL *must* behave
[28 Nov 2006 8:27] Andrius Steponavicius
There may be an additional functionality to add to MySQL. The default behaviour may be left as is, but with additional keywords may be easely changed to the new way:

CREATE TABLE t7(
    -> id TINYINT NOT NULL auto_increment,
    -> col1 TINYINT NULL DEFAULT NULL,
    -> col2 TINYINT NOT NULL,
    -> UNIQUE( col1 FORCE NULLS, col2 ),
    -> PRIMARY KEY( id )
    -> );

By that way MySQL may be forced to check for a unique NULLs...
[7 Dec 2009 9:29] Valter Kungla
Hi,

It is a serios problem form me!!!
How can I overcome this behavior?
Who ever needs, behavior like multible nulls when I set a column to be unique.
Unique should menu unique when I define it.

Is there any hope that you fix this behavior or shoul I consider other solutions?
[7 Dec 2009 19:21] Peter Gulutzan
See also Bug#17825.