Thread: BUG #3937: timestamp null value comparison in subquery using IN/NOT IN

The following bug has been logged online:

Bug reference:      3937
Logged by:          Jan Mate
Email address:      mate@yeea.eu
PostgreSQL version: 8.2.6
Operating system:   Linux & Mac OS X
Description:        timestamp null value comparison in subquery using IN/NOT
IN
Details:

Comparison between null and timestamp null value is not correct.

Try this example:

CREATE TABLE _test ("timestamp" timestamp);
INSERT INTO _test (timestamp) VALUES (null);

SELECT 1 WHERE (null) NOT IN (SELECT * FROM _test);
 ?column?
----------
(0 rows)

SELECT 1 WHERE (null) IN (SELECT * FROM _test);
 ?column?
----------
(0 rows)

Is there any other way to compare the subquery result (using IN or NOT IN)
with null value?

"Jan Mate" <mate@yeea.eu> writes:
> Comparison between null and timestamp null value is not correct.

No, it's your expectations that are not correct.  Null is not "equal to"
null --- in fact both the queries you show will evaluate the WHERE
clause as null (unknown).

> Is there any other way to compare the subquery result (using IN or NOT IN)
> with null value?

Don't try to use null as a normal data value.  Sooner or later you'll
always regret it.

            regards, tom lane