Re: IN query operator and NULL values

The non-compliance fix is described here:

http://www.postgresql.org/docs/8.3/interactive/functions-
comparison.html says:

>> To check whether a value is or is not null, use the constructs
>>
>> expression IS NULL
>> expression IS NOT NULL
>> or the equivalent, but nonstandard, constructs
>>
>> ....
>>
>> Note: If the expression is row-valued, then IS NULL is true when
>> the row expression itself is null or when all the row's fields are
>> null, while IS NOT NULL is true when the row expression itself is
>> non-null and all the row's fields are non-null. This definition
>> conforms to the SQL standard, and is a change from the
>> inconsistent behavior exhibited by PostgreSQL versions prior to 8.2.

-- Andy

On May 16, 2008, at 12:54 PM, Alban Hertroys wrote:

> 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!
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general