List:General Discussion« Previous MessageNext Message »
From: Date:January 1 1970 12:00am
Subject:Bug in UNIQUE?
View as plain text  
Hi,

The SQL below illustrates what I believe is a bug in MySQL up to and 
including 3.23.47. Essentially I need a unique key where one or more of 
the component fields of the unique key can be NULL. What seems to happen 
is that you can add "duplicate" rows if the value is NULL.

Has anyone come across this before? Is this a bug? 

Thanks

Jude Insley

Wide Area Communications
www.widearea.co.uk

----------------------------------------------------

mysql> create table bug ( col1 char(16), col2 char(16), unique key(col1, col2)) ;
Query OK, 0 rows affected (0.02 sec)

mysql> replace into bug values('a', 'b') ;
Query OK, 1 row affected (0.00 sec)

mysql> replace into bug values('a', NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> replace into bug values('a', NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> replace into bug values('a', NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from bug ;
+------+------+
| col1 | col2 |
+------+------+
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | b    |
+------+------+
4 rows in set (0.00 sec)

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

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

mysql> insert into bug values('a', NULL) ;
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from bug ;
+------+------+
| col1 | col2 |
+------+------+
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | NULL |
| a    | b    |
+------+------+
8 rows in set (0.00 sec)

Thread
Bug in UNIQUE?Unknown Sender31 Jan
  • Re: Bug in UNIQUE?Paul DuBois31 Jan
  • Re: Bug in UNIQUE?Carsten Gehling1 Feb
    • Re: Bug in UNIQUE?Dan Nelson1 Feb
      • RE: Bug in UNIQUE?J. Ceferino Ortega1 Feb