On 9 Apr 2001 [EMAIL PROTECTED] 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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
