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