List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 19 2002 5:57pm
Subject:RE: delete where column <=> null
View as plain text  
At 12:38 -0500 11/19/02, Arthur Fuller wrote:
>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

That is false.  The <=> operator is like = except that it is also true
for NULL comparisons.  And when I try the queries below using <=>, the
records containing NULL *are* deleted.  I'm not sure why David is observing
different behavior.

>
>-----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)
>);

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