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
