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