MySQL Bugs: #16219: Unique index not unique when containing NULL columns

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:		CPU Architecture:	Any

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)

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

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.

I want to add that it's the behaviour defined by the SQL standard, it's how MySQL *must* behave

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...

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?

See also Bug#17825.