Обсуждение: IN query operator and NULL values

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=# create table test(a integer ,b integer);
CREATE TABLE
test=# insert into test values(1,1);
INSERT 6838415 1
test=# insert into test values(2,2);
INSERT 6838416 1
test=# insert into test values(3,null);
INSERT 6838417 1
test=# select * from test ;
 a | b
---+---
 1 | 1
 2 | 2
 3 |
(3 rows)

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.

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

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

Thank you in advance for your help,

Denis

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!

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

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

Just remembered a nice option from psql that doesn't quite clutter my
example as much:

development=> \pset null '<NULL>'
Null display is "<NULL>".
development=> select b, b in (1, null) from test;
    b    | ?column?
--------+----------
       1 | t
       2 | <NULL>
  <NULL> | <NULL>
(3 rows)


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,482dbd32927664852116061!

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