Hi. On Tue 2002-11-19 at 12:38:33 -0500, [EMAIL PROTECTED] wrote: > Nothing can be compared to null, not even null, which implies that MySQL is > wrong in returning two rows in the SELECT.
I have to disagree. The "<=>" operator is explicitly defined to allow that. "<=>" is the same as "=", except that it evaluates NULL <=> NULL to true. Also see http://www.mysql.com/doc/en/Comparison_Operators.html I am a bit irritated that you did not wonder about the use of the rather unusual "<=>" operator enough to look it up, but did not realize how it works. > Your query needs to say IS NULL: > > delete from t where id = 1 and num is null; No, it does not need to. Of course, it would also work this way, and what you presented is a good work-around for the problem at hand. > > Without the index it works fine. Must be a bug.... And as he said, is a bug in any case, because an index must be transparent with regard to the result of the query. [...] > 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) -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
