Thread: 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.
Considerthis 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
understandingwould 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
> 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
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 =3D 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 =3D NULL" as true, but it was changed to conform with standard and typical implementations in ohter databases. best regards depesz --=20 hubert depesz lubaczewski http://www.depesz.pl/ ------------------------------------------------------------------------ M=F3j Bo=BFe, spraw abym milcza=B3, dop=F3ki si=EA nie upewni=EA, =BFe na= prawd=EA mam co=B6 do powiedzenia. (c) 1998 depesz