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!

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: