7 messages in com.mysql.lists.mysqlRe: BUG: Cannot DELETE all records wi...
FromSent OnAttachments
losc...@servint.com09 Apr 2001 09:43 
Thalis A. Kalfigopoulos09 Apr 2001 10:04 
Matt Loschert09 Apr 2001 10:20 
Sinisa Milivojevic09 Apr 2001 10:23 
Benjamin Pflugmann09 Apr 2001 12:45 
Andreas Karl Wittwer09 Apr 2001 18:39 
René Tegel09 Apr 2001 22:58 
Subject:Re: BUG: Cannot DELETE all records with NULL entries in UNIQUE KEY fields
From:Thalis A. Kalfigopoulos (tha@cs.pitt.edu)
Date:04/09/2001 10:04:39 AM
List:com.mysql.lists.mysql

On 9 Apr 2001 losc@servint.com wrote:

Description:

Attempting to delete all records in a table containing NULL values in a UNIQUE KEY field does not work as expected. Only a single record is deleted, presumably because the server thinks that the table will only have one record with a NULL value in it (as it is in a UNIQUE field).

How-To-Repeat:

Run the following SQL commands:

--- BEGIN SQL TEST STATEMENTS ----------------------------------------------

USE test; # # Create a table with a unique key in addition to a primary key # DROP TABLE IF EXISTS table_with_key; CREATE TABLE table_with_key ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY idx1 (uniq_id) ) TYPE=MyISAM; # # Create a table with only a primary key # DROP TABLE IF EXISTS table_without_key; CREATE TABLE table_without_key ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; # # Insert test data into table with unique key # INSERT INTO table_with_key VALUES (1,NULL); INSERT INTO table_with_key VALUES (2,NULL); INSERT INTO table_with_key VALUES (3,1); INSERT INTO table_with_key VALUES (4,2); INSERT INTO table_with_key VALUES (5,NULL); INSERT INTO table_with_key VALUES (6,NULL); INSERT INTO table_with_key VALUES (7,3); INSERT INTO table_with_key VALUES (8,4); INSERT INTO table_with_key VALUES (9,NULL); INSERT INTO table_with_key VALUES (10,NULL); # # Insert identical data into table without unique key # INSERT INTO table_without_key VALUES (1,NULL); INSERT INTO table_without_key VALUES (2,NULL); INSERT INTO table_without_key VALUES (3,1); INSERT INTO table_without_key VALUES (4,2); INSERT INTO table_without_key VALUES (5,NULL); INSERT INTO table_without_key VALUES (6,NULL); INSERT INTO table_without_key VALUES (7,3); INSERT INTO table_without_key VALUES (8,4); INSERT INTO table_without_key VALUES (9,NULL); INSERT INTO table_without_key VALUES (10,NULL); # # Delete all records from each table where the uniq_id field is null # DELETE FROM table_with_key WHERE uniq_id IS NULL; DELETE FROM table_without_key WHERE uniq_id IS NULL; # # Select what is left -- notice the difference # SELECT * FROM table_with_key ORDER BY uniq_id, id; SELECT * FROM table_without_key ORDER BY uniq_id, id;

--- END SQL TEST STATEMENTS ------------------------------------------------

The output for the last four statements looks like the following:

--- BEGIN SQL TEST OUTPUT --------------------------------------------------

mysql> DELETE FROM table_with_key WHERE uniq_id IS NULL; Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM table_without_key WHERE uniq_id IS NULL; Query OK, 6 rows affected (0.00 sec)

mysql> SELECT * FROM table_with_key ORDER BY uniq_id, id; +----+---------+ | id | uniq_id | +----+---------+ | 2 | NULL | | 5 | NULL | | 6 | NULL | | 9 | NULL | | 10 | NULL | | 3 | 1 | | 4 | 2 | | 7 | 3 | | 8 | 4 | +----+---------+ 9 rows in set (0.00 sec)

mysql> SELECT * FROM table_without_key ORDER BY uniq_id, id; +----+---------+ | id | uniq_id | +----+---------+ | 3 | 1 | | 4 | 2 | | 7 | 3 | | 8 | 4 | +----+---------+ 4 rows in set (0.00 sec)

--- END SQL TEST OUTPUT ----------------------------------------------------

This is perfectly normal from Mysql's part. What you call a uniq_id in the table
where you don't declare it as a unique key is merely another int field and a
table scan will be performed to actually find ALL the occurences of the value
you are deleting. On the other hand in the case where you actually declare the
uniq_id to be unique by building a UNIQUE index on it, Mysql will of course use
the asserted uniqueness and stop processing as soon as the first occurence of
the value you want to delete is found. I.e. it assumes that as a unique field
there will not be another occurence of this value. Of course on the other hand
it allows you to insert multiple cases of NULL even though it is a unique field,
because NULL is just SO DAMN NULL that it is NOT EVEN EQUAL TO ITSELF :-) So when you have a NULL in a unique field and add another NULL Mysql will not
complain about it cause Null!=Null whereas it would complain if you had inserted
a '1' and tried to insert another '1'.

Clear as mud?

regards, thalis