Обсуждение: BUG #2961: NULL values in subselects force NOT IN to false
The following bug has been logged online: Bug reference: 2961 Logged by: Aaron Logue Email address: gyro@cryogenius.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.
On Fri, 2 Feb 2007, Aaron Logue wrote: > The following bug has been logged online: > > Bug reference: 2961 > Logged by: Aaron Logue > Email address: gyro@cryogenius.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. NOT IN with NULLs is defined by spec in a way that most people do not expect if they aren't thinking about three valued logic. x NOT IN RVC is effectively NOT(x = ANY RVC). x = ANY RVC is defined to be true if x = RVCi is true for some RVCi in RVC. x = ANY RVC is defined to be false if x = RVCi is false for all RVCi in RVC. x = ANY RVC is defined to be unknown otherwise. x = NULL is defined as unknown, so what you end up with is x = 7, false x = NULL, unknown so, x IN (7, NULL), unknown so, NOT (x IN (7, NULL)), unknown.
On Mon, 5 Feb 2007, Stephan Szabo wrote: > On Fri, 2 Feb 2007, Aaron Logue wrote: > > The following bug has been logged online: > > > > Bug reference: 2961 > > Logged by: Aaron Logue > > Email address: gyro@cryogenius.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. > > NOT IN with NULLs is defined by spec in a way that most people do not > expect if they aren't thinking about three valued logic. > > x NOT IN RVC is effectively NOT(x = ANY RVC). > ... Shouldn't IS be used to compare x with a NULL rather than = ?