MySQL Bugs: #6829: duplicate NULL values allowed in UNIQUE INDEX

Bug #6829	duplicate NULL values allowed in UNIQUE INDEX
Submitted:	25 Nov 2004 17:58	Modified:	26 Nov 2004 10:24
Reporter:	Kirk Wylie	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20 and 4.1.7	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When creating a table with a UNIQUE index which includes columns which may be NULL, inserting values into the table which have NULL values in some columns don't result in uniqueness constraint violations.

For example, if I have a table with columns c1 and c2, both of which can be NULL, and have a UNIQUE INDEX (c1, c2), if I insert (1, NULL) and (1, NULL), the contraint won't be violated, even though the two rows have the same values.

The repeat case below gives the degenerate, minimal test case, with only one column and two NULL values.

This behavior is not consistent with other SQL databases (same functionality also checked on oracle and SQL Server), and causes applications written assuming that functionality to break.

This happens both with InnoDB tables and with MyISAM tables.

How to repeat:
mysql> create table test (i int, unique index (i));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (i) values (null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (i) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| i    |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

Suggested fix:
At least make it an option (perhaps it is and I can't find it, in which case this is a documentation bug) to have uniqueness constraints match for NULL values.

The SQL  Standard allows multiple null values in unique constraints. Null is never equal to anything, 
even not to another null value.

The multiple-NULL behavior is documented here:

http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

Which says:

In MySQL, a UNIQUE index is one in which all values in the index 
must be distinct. An error occurs if you try to add a new row with
a key that matches an existing row. The exception to this is that
if a column in the index is allowed to contain NULL values, it can
contain multiple NULL values. This exception does not apply to BDB
tables, for which indexed columns allow only a single NULL.

Georg is right, the standard specifies:

...
2) If there are no two rows in T such that the value of each column in one
row is non-null and is not distinct from the value of the corresponding
column in the ther row, then the result of the <unique predicate> is True;
otherwise, the result of the <unique predicate> is False.
...

It explicitly says that rows where not "each column is non-null" is excluded from the uniqueness test.