Bug #765: 'IS NULL' versus '= NULL'
Bhuvan A (bhuvansql@myrealbox.com) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
'IS NULL' versus '= NULL'
Long Description
I am using postgresql 7.2.1. I suppose NULL keyword refers nullity (null values) in general.
'IS NULL' and '= NULL' behaves differently in where conditions in SELECT sql, but behaves as expected in UPDATE sql. Consider this case.
select count(*) from my_table where id is NULL;
count
-------
0
(1 row)
select count(*) from my_table where id = NULL;
count
-------
0
(1 row)
select count(*) from my_table where id in (NULL);
count
-------
0
(1 row)
update my_table set id = NULL where id = 12;
UPDATE 1
select count(*) from my_table where id is NULL;
count
-------
1
(1 row)
select count(*) from my_table where id = NULL;
count
-------
0
(1 row)
select count(*) from my_table where id in (NULL);
count
-------
0
(1 row)
Eventhough my_table contain a record with id as null, the last 2 sqls are not resulting that record. Why? Maybe my understanding would be wrong on this behaviour, if so please kindly apologize and give some description on this difference, since i dont have answer in the documentation.
TIA.
regards,
bhuvaneswaran
Sample Code
No file was uploaded with this report
On Sat, Sep 14, 2002 at 04:43:15AM -0400, pgsql-bugs@postgresql.org wrote:
Eventhough my_table contain a record with id as null, the last 2 sqls
are not resulting that record. Why? Maybe my understanding would be
wrong on this behaviour, if so please kindly apologize and give some
description on this difference, since i dont have answer in the
documentation.
afaik: according to sql documentation (and implementations different
that postgresql), any comparison where one of values is null should
yield "null" as response. is means, that NULL = NULL gives you "null"
instead of "true", and if you want this kind of checks you have to use
"is null" operator.
postgresql used to process "NULL = NULL" as true, but it was changed to
conform with standard and typical implementations in ohter databases.
best regards
depesz
--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz
Eventhough my_table contain a record with id as null, the last 2 sqls
are not resulting that record. Why?
Because they're not the right commands to do that kind of thing. Please
read the chapter on operators and functions in the User's Guide.
--
Peter Eisentraut peter_e@gmx.net