Bug #5685 NULL values are populating unique index

• Submitted: 21 Sep 2004 17:05
• Modified: 23 Sep 2004 11:37
• Reporter: Egor Egorov
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.21
• OS: Linux (Linux)
• CPU Architecture: Any

[21 Sep 2004 17:05] Egor Egorov

Description:
NULL values are not generating errors being inserted in UNIQUE index. 

Is this a bug or a SQL99 behaviour (read - feature)? 

How to repeat:
mysql> create table tt (id int (10) not null,name char(25) null, unique key(id,name));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt values (1,111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values (1,111);
ERROR 1062 (23000): Duplicate entry '1-111' for key 1

-- That's good!

mysql> insert into tt values (1,NULL );
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values (1,NULL );
Query OK, 1 row affected (0.00 sec)

-- What??

mysql> insert into tt values (1,NULL );
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values (1,NULL );
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  1 | NULL |
|  1 | NULL |
|  1 | NULL |
|  1 | 111  |
+----+------+
5 rows in set (0.00 sec)

Suggested fix:
Point me out to the documentation. :)

[21 Sep 2004 17:19] Paul DuBois

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It's documented:

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

[23 Sep 2004 11:37] Sergei Golubchik

yes, it's how the standard defines UNIQUE index

[12 Nov 2004 16:02] Roger Zuegs

I have the same problem :(

I would prefer that "insert" or "replace" dosen't allow inserting multiple records withs NULLs in a unique key!
I don't know if there is a mathematical / logical explanation or reason for the current implementation (behavior)???

Is there, or what's about an other entity than "NULL" something like "NIL" that could be inserted instead with also the effect that NIL = NIL gets true....
OK, it's out of the standards.... :(

But, perhaps would have a hint for my???
Thanks a lot.... loving MySQL anyway... :)