Re: IN query operator and NULL values

On May 16, 2008, at 5:40 PM, Denis Gasparin wrote:

> Hi all.
>
> I have a problem with the IN operator in PostgreSQL 8.2.7. Here it
> is an example that reproduce the problem:
>
> test=# select * from test where b in(1,null);
> a | b
> ---+---
> 1 | 1
>
> In the last resultset, i was expecting two records the one with b =
> 1 and the one with b = null.
> PostgreSQL instead returns only the value with not null values.

Yes, of course it does. NULL means "unknown". Comparing it to
anything results in NULL, as the result is "unknown" again. What
happens is this:

development=> select b, coalesce( (b in (1, null))::text, 'NULL')
from test;
b | coalesce
---+----------
1 | true
2 | NULL
| NULL
(3 rows)

The where clause can only handle true or false (as per the SQL spec),
so it assumes "unknown" means the record wasn't a match.

> I tested the example also in PostgreSQL 8.1 and it works correctly
> (two records).

That looks like a bug in 8.1.

> So the question is: what has changed from 8.1 to 8.2?

I think a bug was fixed ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,482dbc5e927668957138674!