Re: Bug or No bug - Composite Unique Key using null values
> 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]
Re: Bug or No bug - Composite Unique Key using null values
Dan, this is documented behavior: http://dev.mysql.com/doc/refman/5.0/en/create-index.html Specifically, "A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL." Dan On 10/6/06, Dan Julson <[EMAIL PROTECTED]> wrote: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug or No bug - Composite Unique Key using null values
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]
