MySQL Bugs: #66512: Unique index allows duplicate records containing nulls

Bug #66512	Unique index allows duplicate records containing nulls
Submitted:	23 Aug 2012 9:58	Modified:	23 Aug 2012 10:45
Reporter:	Matt Kusnierz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.48-community-log	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	duplicate, INDEX, null, unique

Description:
Although documented (http://dev.mysql.com/doc/refman/5.0/en/create-index.html):

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

The fact that you can insert duplicate records into a table that has a unique index/constraint must be considered a bug, especially when it works on some storage engines and not others. 

How to repeat:
create table foo (a varchar(10) null, b varchar(10 null);
create unique index uix on foo (a, b);
insert into foo (a, b) values ('abc', null);
insert into foo (a, b) values ('abc', null);

Second insert should fail, but does not!

Suggested fix:
unique indexes should not allow duplicates, even for null values.

This is explicitly documented, moreover, it works this way in other RDBMSes I know (see http://en.wikipedia.org/wiki/Null_%28SQL%29#Effect_on_index_operation). So, this is not a bug.

The fact that the behaviour is inconsistent between the different mysql table storage engines for me makes this a bug. It means that you need to have detailed knowledge of how the table was created in order to know how the index will function and because a DBA could change the underlying storage engine, it means that code cannot relay on consistent functional behaviour when it comes to unique index constaints.

Oracle databases for example do consider nulls equal when enforcing unique index constraints. As does MS SQL server (at least according to the wikipedia page you linked to:
For example, Microsoft SQL Server documentation states the following:[14]

    For indexing purposes, NULL values compare as equal. Therefore, a unique index, or UNIQUE constraint, cannot be created if the key values are NULL in more than one row.)