[EMAIL PROTECTED] writes:
> >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
>
Thank you for a repeatable test case.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
