>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 ----------------------------------------------------
>Fix:
No idea.
>Submitter-Id: <submitter ID>
>Originator: Matt Loschert
>Organization: ServInt Internet Services
Matt Loschert | email: [EMAIL PROTECTED] |
Software Engineer | web: http://www.servint.net/ |
ServInt Internet Services | phone: (703) 847-1381 |
>
>MySQL support: none
>Synopsis: Cannot DELETE all records with NULL entries in UNIQUE KEY fields
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.36 (Source distribution)
>Environment:
System: FreeBSD delft.servint.com 4.3-RC FreeBSD 4.3-RC #0: Thu Mar 29 11:50:53 EST
2001
[EMAIL PROTECTED]:/disk2/cvsup/branches/releng_4/obj/disk2/cvsup/branches/releng_4/src/sys/QUICKIE
i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1176254 Mar 29 12:07 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Mar 29 12:07 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 566004 Mar 29 12:07 /usr/lib/libc.so.4
Configure command: ./configure --with-low-memory --prefix=/usr/local/mysql-23.36
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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
