From: | "Aaron Logue" <gyro(at)cryogenius(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #2961: NULL values in subselects force NOT IN to false |
Date: | 2007-02-02 19:01:23 |
Message-ID: | 200702021901.l12J1Nip094976@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 2961
Logged by: Aaron Logue
Email address: gyro(at)cryogenius(dot)com
PostgreSQL version: 8.2.1
Operating system: Linux (various flavors)
Description: NULL values in subselects force NOT IN to false
Details:
SELECT X FROM (SELECT 42 AS X) AS FOO WHERE X NOT IN (7,NULL);
returns 0 rows. Shouldn't "X NOT IN (7,NULL)" be
true if X is neither 7 nor NULL? Removing the NULL causes the row to be
returned.
Here's a form of the problem using normal tables:
CREATE TABLE test1 (
test_id numeric(28,0)
);
CREATE TABLE test2 (
test_id numeric(28,0)
);
INSERT INTO test1 (test_id) VALUES (1);
INSERT INTO test2 (test_id) VALUES (2);
INSERT INTO test2 (test_id) VALUES (NULL);
SELECT test_id FROM test1 WHERE test_id NOT IN (SELECT test_id FROM test2);
will return 0 rows. Deleting the null field from test2 or updating
test2.test_id to a non-null value will cause it to behave as expected.
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Chambers | 2007-02-02 22:54:26 | BUG #2962: 8.2.1 lo_creat Documentation incorrect? |
Previous Message | Dave Claes | 2007-02-02 10:57:58 | BUG #2960: missing string replace function ODBC driver |