List,
I have been mulling over this for a few days reading docs and going back and
forth with people on this, so I figured I would come here before writing up a
bug report.
First of all, I have tested this on 4.1.18, 5.0.16, and 5.0.22 within Solaris
9 and Mandrake Linux LE 2005 environments.
For the sake of ease, I will just set up a small test table to assist me with
this question.
> Create table test (
x smallint not null,
y char(5) default null,
z char(10) not null default '',
Unique Key `s`(x, y)
) ENGINE=MyISAM
test> Insert into test values (1, 'dan', 'yes'), (2, 'joe', 'no'), (3, NULL,
'maybe');
test> Select * from test;
+---+------+-------+
| x | y | z |
+---+------+-------+
| 1 | dan | yes |
| 2 | joe | no |
| 3 | NULL | maybe |
+---+------+-------+
Now, here is the kicker. When I try to run an Insert Into test Values (3,
NULL, 'yes'). It inserts it without matching the duplicate key which clearly
is in the table, or is it. As the docs state, NULL can never equal NULL, so
this is the correct behavior. Which leads me to my question...
test> Insert into test Values (3, NULL, 'yes');
Query OK, 1 row affected (0.00 sec)
test> Select * from test;
+---+------+-------+
| x | y | z |
+---+------+-------+
| 1 | dan | yes |
| 2 | joe | no |
| 3 | NULL | maybe |
| 3 | NULL | yes |
+---+------+-------+
Should the duplicate key checker be using the null-safe equals operator when
checking for duplicate unique keys? Since primary keys cannot have nulls in
them, then they are fine. This only happens when a unique key with a null
value is encountered.
Thanks, in advance, for any and all input.
--
-Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
