>>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:afuller@stripped]
Sent: 19 November 2002 17:39
To: Andrew Braithwaite; mysql@stripped
Cc: mysql@stripped
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: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)
);
---------------------------------------------------------------------
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 <mysql-thread125310@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-andrew=multimap.com@stripped>
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 <mysql-thread125312@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-afuller=etsys.com@stripped>
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 <mysql-thread125315@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-andrew=multimap.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
