>>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:[EMAIL PROTECTED]] Sent: 19 November 2002 17:39 To: Andrew Braithwaite; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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:[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 --------------------------------------------------------------------- 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
