Description:
Revisit Bug #8173 (http://bugs.mysql.com/bug.php?id=8173).
I am not sure how comments are treated for bugs, which have been closed, so I am opening a new bug report. There were multiple reasons given as to why this is a bug, but lately there were no responses from MySQL developers. Could we have the SQL Standard reviewed again and provide a fix to the problem.
Description of the bug:
unique index allows duplicates if at least one of the columns is null.
How to repeat:
mysql> create table tb
-> (
-> id int not null auto_increment,
-> a int,
-> b int,
-> primary key (id),
-> index (a),
-> index (b),
-> unique index (a,b)
-> ) type = innodb; /* occurs in myISAM tables as well */
Query OK, 0 rows affected, 1 warning (0.42 sec)
mysql> insert into tb (a,b) values (1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb (a,b) values (1,2); /* fails (correct) */
ERROR 1062 (23000): Duplicate entry '1-2' for key 2
mysql> insert into tb (a,b) values (1,null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb (a,b) values (1,null); /* succeeds (incorrect?) */
Query OK, 1 row affected (0.02 sec)
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.0.0-alpha-nt |
+----------------+
1 row in set (0.00 sec)
Suggested fix:
Return "duplicate key" error.
