bug report: delete where column <=> null fails when column is indexed

Description:

Given one or more rows in table T with null values in column C,

delete from T where C <=> null

FAILS when the column C is indexed, but
SUCCEEDS when the column not indexed.

I believe it should succeed regardless of how the table is indexed.


How-To-Repeat:

create table t (
id integer,
index (id)
);
insert into t(id) values(null);
delete from t where id <=> null;
select * from t;


Fix:
The only workarounds I know are to avoid using where <=> null (by
using only IS NULL) or to avoid indexing columns. Neither of these
is a very good workaround, I'm afraid.

Synopsis: delete where column <=> null fails when column is indexed
Submitter-Id: ?
Originator: David Mechner
Organization: Pragma
MySQL support: licence
Severity: serious
Priority: high
Category: mysqld
Class: sw-bug
Release: mysql-3.23.53

Exectutable: mysqld-nt
Environment: Dual Athlon, 2GB ram
System: Win2k adv server
Compiler: VC++ 6.0
Architecture: i


------------------------------------------------------------ ---------
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 bugs-thread13039@lists.mysql.com
To unsubscribe, e-mail

Re: bug report: delete where column <=> null fails when column is

On Wed, 2002-11-20 at 01:51, mysql@mechner.com wrote:
> Description:
>
> Given one or more rows in table T with null values in column C,
>
> delete from T where C <=> null
>
> FAILS when the column C is indexed, but
> SUCCEEDS when the column not indexed.
>
> I believe it should succeed regardless of how the table is indexed.
>
>
> How-To-Repeat:
>
> create table t (
> id integer,
> index (id)
> );
> insert into t(id) values(null);
> delete from t where id <=> null;
> select * from t;
>

Above is expected behaviour. See why:

mysql> select * from t where id <=> 1;
Empty set (0.00 sec)

mysql> select * from t where not(id <=> 1);
+------+
| id |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select * from t where not(id = 1);
Empty set (0.00 sec)


As manual says:

<=>
NULL safe equal:
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0



So in your test case
delete from t where id <=> null;

must not affect any rows.

Looks like you assumed <=> is Null safe NOT_equal



Btw. I tried it in both 3.23 and 4.0 and failed to find any difference
with/without Index.

Best regards

--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668



------------------------------------------------------------ ---------
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 bugs-thread13048@lists.mysql.com
To unsubscribe, e-mail

Re: bug report: delete where column <=> null fails when column isindexed

Hi,

select * from t where id <=> null returns the following :

mysql> select * from t where id <=> null;
+------+
| id |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Then, I don't understand why DELETE FROM t WHERE id <=> NULL should not
delete this row ??

mysql> DELETE FROM t WHERE id <=> NULL ;
Query OK, 0 rows affected (0.00 sec)

With MySQL-4.1, when dropping id key, the bug disappears :

mysql> ALTER TABLE t DROP KEY id;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> DELETE FROM t WHERE id <=> NULL ;
Query OK, 1 row affected (0.00 sec)

select * from t where id <=> null;
Empty set (0.00 sec)

Regards,
Jocelyn

----- Original Message -----
From: "Alexander Keremidarski"
To:
Cc:
Sent: Wednesday, November 20, 2002 2:54 PM
Subject: Re: bug report: delete where column <=> null fails when column
isindexed


> On Wed, 2002-11-20 at 01:51, mysql@mechner.com wrote:
> > Description:
> >
> > Given one or more rows in table T with null values in column C,
> >
> > delete from T where C <=> null
> >
> > FAILS when the column C is indexed, but
> > SUCCEEDS when the column not indexed.
> >
> > I believe it should succeed regardless of how the table is indexed.
> >
> >
> > How-To-Repeat:
> >
> > create table t (
> > id integer,
> > index (id)
> > );
> > insert into t(id) values(null);
> > delete from t where id <=> null;
> > select * from t;
> >
>
> Above is expected behaviour. See why:
>
> mysql> select * from t where id <=> 1;
> Empty set (0.00 sec)
>
> mysql> select * from t where not(id <=> 1);
> +------+
> | id |
> +------+
> | NULL |
> +------+
> 1 row in set (0.00 sec)
>
> mysql> select * from t where not(id = 1);
> Empty set (0.00 sec)
>
>
> As manual says:
>
> <=>
> NULL safe equal:
> mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
> -> 1 1 0
>
>
>
> So in your test case
> delete from t where id <=> null;
>
> must not affect any rows.
>
> Looks like you assumed <=> is Null safe NOT_equal
>
>
>
> Btw. I tried it in both 3.23 and 4.0 and failed to find any difference
> with/without Index.
>
> Best regards
>
> --
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander
Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com M: +359 88 231668
>
>
>
> ------------------------------------------------------------ ---------
> 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 bugs-thread13048@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
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 bugs-thread13049@lists.mysql.com
To unsubscribe, e-mail

Re: bug report: delete where column <=> null fails when column is indexed

On Wednesday 20 November 2002 02:51, mysql@mechner.com wrote:
> Description:
>
> Given one or more rows in table T with null values in column C,
>
> delete from T where C <=> null
>
> FAILS when the column C is indexed, but
> SUCCEEDS when the column not indexed.
>
> I believe it should succeed regardless of how the table is indexed.

Thank you for the bug report. We were able to repeat and fix the bug.
It should be fixed in the next MySQL release.

Good luck.


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
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 bugs-thread13059@lists.mysql.com
To unsubscribe, e-mail

Re: bug report: delete where column <=> null fails when column isindexed

Hi,

I don't know if it's the proper patch to fix this bug, but at least, it
works for me :

===== opt_range.cc 1.68 vs edited =====
--- 1.68/sql/opt_range.cc Fri Nov 15 08:44:20 2002
+++ edited/opt_range.cc Wed Nov 20 22:52:13 2002
@@ -1014,7 +1014,7 @@
field->cmp_type() != value->result_type())
DBUG_RETURN(0);

- if (value->save_in_field(field) > 0)
+ if (value->save_in_field(field) > 0 || field->is_null())
{
// TODO; Check if we can we remove the following block.
if (type == Item_func::EQUAL_FUNC)

Regards,
Jocelyn
----- Original Message -----
From: "Jocelyn Fournier"
To: "Alexander Keremidarski" ;
Cc:
Sent: Wednesday, November 20, 2002 3:10 PM
Subject: Re: bug report: delete where column <=> null fails when column
isindexed


> Hi,
>
> select * from t where id <=> null returns the following :
>
> mysql> select * from t where id <=> null;
> +------+
> | id |
> +------+
> | NULL |
> +------+
> 1 row in set (0.00 sec)
>
> Then, I don't understand why DELETE FROM t WHERE id <=> NULL should not
> delete this row ??
>
> mysql> DELETE FROM t WHERE id <=> NULL ;
> Query OK, 0 rows affected (0.00 sec)
>
> With MySQL-4.1, when dropping id key, the bug disappears :
>
> mysql> ALTER TABLE t DROP KEY id;
> Query OK, 1 row affected (0.04 sec)
> Records: 1 Duplicates: 0 Warnings: 0
>
> mysql> DELETE FROM t WHERE id <=> NULL ;
> Query OK, 1 row affected (0.00 sec)
>
> select * from t where id <=> null;
> Empty set (0.00 sec)
>
> Regards,
> Jocelyn
>
> ----- Original Message -----
> From: "Alexander Keremidarski"
> To:
> Cc:
> Sent: Wednesday, November 20, 2002 2:54 PM
> Subject: Re: bug report: delete where column <=> null fails when column
> isindexed
>
>
> > On Wed, 2002-11-20 at 01:51, mysql@mechner.com wrote:
> > > Description:
> > >
> > > Given one or more rows in table T with null values in column C,
> > >
> > > delete from T where C <=> null
> > >
> > > FAILS when the column C is indexed, but
> > > SUCCEEDS when the column not indexed.
> > >
> > > I believe it should succeed regardless of how the table is indexed.
> > >
> > >
> > > How-To-Repeat:
> > >
> > > create table t (
> > > id integer,
> > > index (id)
> > > );
> > > insert into t(id) values(null);
> > > delete from t where id <=> null;
> > > select * from t;
> > >
> >
> > Above is expected behaviour. See why:
> >
> > mysql> select * from t where id <=> 1;
> > Empty set (0.00 sec)
> >
> > mysql> select * from t where not(id <=> 1);
> > +------+
> > | id |
> > +------+
> > | NULL |
> > +------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select * from t where not(id = 1);
> > Empty set (0.00 sec)
> >
> >
> > As manual says:
> >
> > <=>
> > NULL safe equal:
> > mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
> > -> 1 1 0
> >
> >
> >
> > So in your test case
> > delete from t where id <=> null;
> >
> > must not affect any rows.
> >
> > Looks like you assumed <=> is Null safe NOT_equal
> >
> >
> >
> > Btw. I tried it in both 3.23 and 4.0 and failed to find any difference
> > with/without Index.
> >
> > Best regards
> >
> > --
> > For technical support contracts, visit https://order.mysql.com/?ref=msal
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Alexander
> Keremidarski
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> > /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> > <___/ www.mysql.com M: +359 88 231668
> >
> >
> >
> > ------------------------------------------------------------ ---------
> > 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 bugs-thread13048@lists.mysql.com
> > To unsubscribe, e-mail
> >
> >
> >
> >
>
>
> ------------------------------------------------------------ ---------
> 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 bugs-thread13049@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
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 bugs-thread13097@lists.mysql.com
To unsubscribe, e-mail

Re: bug report: delete where column <=> null fails when column isindexed

Jocelyn Fournier writes:
> Hi,
>
> I don't know if it's the proper patch to fix this bug, but at least, it
> works for me :
>
> ===== opt_range.cc 1.68 vs edited =====
> --- 1.68/sql/opt_range.cc Fri Nov 15 08:44:20 2002
> +++ edited/opt_range.cc Wed Nov 20 22:52:13 2002
> @@ -1014,7 +1014,7 @@
> field->cmp_type() != value->result_type())
> DBUG_RETURN(0);
>
> - if (value->save_in_field(field) > 0)
> + if (value->save_in_field(field) > 0 || field->is_null())
> {
> // TODO; Check if we can we remove the following block.
> if (type == Item_func::EQUAL_FUNC)
>
> Regards,
> Jocelyn

The above works, but is not a correct patch.

Correct patch was posted on Friday to bugs@... list.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
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 internals-thread5689@lists.mysql.com
To unsubscribe, e-mail