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

Lists: pgsql-bugs
From: "Jan Mate" <mate(at)yeea(dot)eu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3937: timestamp null value comparison in subquery using IN/NOT IN
Date: 2008-02-06 20:15:59
Message-ID: 200802062015.m16KFxQo017080@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 3937
Logged by: Jan Mate
Email address: mate(at)yeea(dot)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?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jan Mate" <mate(at)yeea(dot)eu>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3937: timestamp null value comparison in subquery using IN/NOT IN
Date: 2008-02-06 23:28:59
Message-ID: 19919.1202340539@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg토토 베이SQL : Postg토토 베이SQL 메일 링리스트 : 2008-02-06 이후 PGSQL-BUGS 23:28

"Jan Mate" <mate(at)yeea(dot)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