List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 6 2006 2:51pm
Subject:Re: Bug or No bug - Composite Unique Key using null values
View as plain text  
> 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> 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.

The behaviour you're seeing is correct according to the SQL Standard.
Given that NULL does not equal NULL, the tuples(x,y) for (3,NULL)
are different and thus valid.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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