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