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)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
