Обсуждение: 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 = ?