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:
Tags: constraint, key, unique
Triage: D5 (Feature request)

[22 Mar 2007 14:51] Andre Timmer
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?
[22 Mar 2007 16:02] Miguel Solorzano
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.
[22 Mar 2007 16:31] Andre Timmer
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!
[22 Mar 2007 17:07] Miguel Solorzano
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.
[22 Mar 2007 18:00] Valerii Kravchuk
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.
[22 Mar 2007 18:18] Andre Timmer
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 :-)