> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
