List:General Discussion« Previous MessageNext Message »
From:Andrew Braithwaite Date:November 19 2002 5:54pm
Subject:RE: delete where column <=> null
View as plain text  
>>Nothing can be compared to null, not even null

This is not so...

mysql> select NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

As explained in the manual
http://www.mysql.com/doc/en/Comparison_Operators.html
<=> is a NULL safe equal and should work.  It works fine in the delete if
there is no index, which suggests that the problem is with the index, not
with the comparison.

Cheers,

Andrew

-----Original Message-----
From: Arthur Fuller [mailto:afuller@stripped] 
Sent: 19 November 2002 17:39
To: Andrew Braithwaite; mysql@stripped
Cc: mysql@stripped
Subject: RE: delete where column <=> null


Nothing can be compared to null, not even null, which implies that MySQL is
wrong in returning two rows in the SELECT. Your query needs to say IS NULL:

 delete from t where id = 1 and num is null;

Arthur

-----Original Message-----
From: Andrew Braithwaite [mailto:andrew@stripped]
Sent: Tuesday, November 19, 2002 12:26 PM
To: mysql@stripped
Cc: 'mysql@stripped'
Subject: RE: delete where column <=> null


David,

I get the same behaviour with mysql 3.23.47 (not max) and with mysql v
4.0.4-beta

Without the index it works fine.  Must be a bug....

Cheers,

Andrew

-----Original Message-----
From: mysql@stripped [mailto:mysql@stripped]
Sent: 19 November 2002 16:45
To: mysql@stripped
Subject: delete where column <=> null


Hi,

I'm trying to delete from a table where col <=> null, but it doesn't seem to
work. I don't see anything in the manual that says that <=> can't be used in
the where clause of a delete statment, but:


mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  1 | NULL |
|  1 | NULL |
+----+------+
4 rows in set (0.00 sec)

mysql> delete from t where id = 1 and num <=> null;
Query OK, 0 rows affected (0.00 sec)

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



This behavior seems very bizarre and counter-intuitive to me, but it's such
an obvious bug that I'm afraid it must be a feature and not a bug. Can
anyone help/explain?

Using "is null" in the where clause works, of course, but would be
relatively cumbersome to use.

Db is 3.23.53-max-nt

Thanks,

-David

p.s. in case it's relevant, there is a unique index on (id,num):

create table t (
  id  integer not null,
  num integer,
  unique index id_num_idx(id,num)
);


---------------------------------------------------------------------
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 <mysql-thread125310@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-andrew=multimap.com@stripped>
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 <mysql-thread125312@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-afuller=etsys.com@stripped>
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 <mysql-thread125315@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-andrew=multimap.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
delete where column <=> nullmysql19 Nov
RE: delete where column <=> nullAndrew Braithwaite19 Nov
  • RE: delete where column <=> nullArthur Fuller19 Nov
    • RE: delete where column <=> nullPaul DuBois19 Nov
    • Re: delete where column <=> nullBenjamin Pflugmann19 Nov
    • Re: delete where column <=> nullPeter Brawley19 Nov
  • Re[2]: delete where column <=> nullDavid Mechner19 Nov
    • Re[2]: delete where column <=> nullPaul DuBois19 Nov
    • Re: Re[2]: delete where column <=> nullJohn Ragan20 Nov
RE: delete where column <=> nullChung Ha-nyung19 Nov
RE: delete where column <=> nullAndrew Braithwaite19 Nov
Re: Re[2]: delete where column <=> nullHeikki Tuuri20 Nov