Обсуждение: 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