Bug #765: 'IS NULL' versus '= NULL'

Bhuvan A (bhuvansql(at)myrealbox(dot)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