i'm very suprised this table definition works for you.
on a -some elder- version of mysql i could not create something like:
create table test (
t integer,
unique (t)
);
well, i then get a warning form mysql, about the field being able to be NULL
so that it could not be unique.
create table test (
t integer NOT NULL,
unique (t)
);
this works!
other databases (interbase) did not show this behaviour.
i guess the newer mysql one 1 hand now allows you to define the field as
null, but internally does not expect this.
maybe you can skip the unique clause and just use an index for the time
being?
gl
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 09, 2001 6:43 PM
Subject: BUG: Cannot DELETE all records with NULL entries in UNIQUE KEY
fields
> >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/br
anches/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
>
---------------------------------------------------------------------
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
