MySQL Bugs: #27376: Uncorrect unique key implementation on multiple optional columns

Bug #27376	Uncorrect unique key implementation on multiple optional columns
Submitted:	22 Mar 2007 14:51	Modified:	22 Mar 2007 18:18
Reporter:	Andre Timmer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	constraint, key, unique

Description:
Rows that shouldn't be allowed are.

How to repeat:
create table test (
  col1 varchar(10)
, col2 varchar(10)
, UNIQUE KEY(col1, col2)
);

insert into test values ('Amsterdam', 'Test');
insert into test values ('Amsterdam', 'Test'); -- is rejected

insert into test values ('Amsterdam', null);
insert into test values ('Amsterdam', null); -- should be rejected

insert into test values (null, 'Test');
insert into test values (null, 'Test'); -- should be rejected

insert into test values (null, null);
insert into test values (null, null); -- ok

+-----------+------+
| col1      | col2 |
+-----------+------+
| NULL      | NULL |
| NULL      | NULL | 
| NULL      | Test |
| NULL      | Test | --> this row should not be allowed
| Amsterdam | NULL |
| Amsterdam | NULL | --> this row should not be allowed
| Amsterdam | Test |
+-----------+------+

Oracle (tested on 8.1.x) implements it as described above.
Firebird (tested on 2.0) implements it as described above.

Suggested fix:
Fix?

Thank you for the bug report. Please read the Manual:

http://dev.mysql.com/doc/refman/5.1/en/problems-with-null.html

"In SQL, the NULL value is never true in comparison to any other value, even NULL.".

For the above reason MySQL allows the insert of the column you have
mentioned.

I hoped that mentioning other, also good, databases would have you folks at MySQL take a second / deeper look at this.

Can't you ask colleagues with have access to other databases then MySQL to run this testcase???

You'll probably find that MySQL implementation differs / deviates.
And then you have to decide whether this is desirable!

Thank for your comments. Yes for example DB2 even not allows to create
the table with UNIQUE constraint which has a column can allow null values,
SQL Server 2005 reject your last test case insert. We are following
the definition of NULL values according SQL Standard.

I had checked with Oracle 10.2, and can confirm that Oracle works just as described. But in Oracle '' (empty string) is treated as NULL also, so what? It was just a design decision. MySQL has different design decisions, based on standards created after Oracle, and thus taking its problems into account. In this case they are properly documented even.

I can agree that checking UNIQUE constraints exacly as in Oracle should be possible in one of the sql_modes, ORACLE. But this is a feature request then.

Thanks, you got it!
Unique key constraints in one of our projects will protect developers much better from making errors. I'm one of them :-)