List:General Discussion« Previous MessageNext Message »
From:Dan Julson Date:October 6 2006 2:45pm
Subject:Bug or No bug - Composite Unique Key using null values
View as plain text  
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
Thread
Bug or No bug - Composite Unique Key using null valuesDan Julson6 Oct
  • Re: Bug or No bug - Composite Unique Key using null valuesDan Buettner6 Oct
  • Re: Bug or No bug - Composite Unique Key using null valuesMartijn Tonies6 Oct