The manual calls '<=>', which was added in 3.23.0, the 'null safe operator', and says...
- If one or both arguments are `NULL', the result of the comparison is `NULL', except for the `<=>' operator. - indexes can use = and <=> but the <=> operator is not in SQL92, and AFAIK no other major db supports it, so if you want portability, IS | IS NOT NULL is preferable. PB ----- > 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:[EMAIL PROTECTED]] > >Sent: Tuesday, November 19, 2002 12:26 PM > >To: [EMAIL PROTECTED] > >Cc: '[EMAIL PROTECTED]' > >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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > >Sent: 19 November 2002 16:45 > >To: [EMAIL PROTECTED] > >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 <[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
