IN query operator and NULL values

From: Denis Gasparin <denis(at)edistar(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: IN query operator and NULL values
Date: 2008-05-16 15:40:36
Message-ID: 482DAAF4.3070201@edistar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Neufeld 2008-05-16 16:05:23 table creation/modified timestamp
Previous Message Guillaume Lelarge 2008-05-16 15:15:13 Re: move database from the default tablespace to a new one?