Re: BUG #12556: Clause IN and NOT IN buggy

On Fri, 16 Jan 2015, Andres Freund wrote:

> On 2015-01-16 09:17:43 +0000, Kevin Perais wrote:
>> There is enough info to understand what goes wrong. I've run enough queries to inspect data.
>
> *You* want something. The likelihood of getting something fixed is far
> larger if you present an example that we can actually run. We obviously
> haven't seen the problem ourselves so far, so a testcase is crucial.
>
> We don't even have the actual table definitions, so we really can't say
> much. We really need a SQL script that allows us to reproduce these
> cases.
>
>> The fact that the query with IN does not returns the same result as
>> the JOIN knowing that there are NULL values proves it.
>
> I guess you mean 'no NULL values'? The second problem is perfectly
> explained by Tom's remark about NOT IN(...) returning NULL if *any* of
> the contained values are NULL. E.g. SELECT 1 WHERE 1 NOT IN (1, NULL);
> won't return any rows.

To the original poster:

Perhaps this example makes the correct behavior of postgres more
obvious:

net=# SELECT 1 WHERE 1 NOT IN (2);
  ?column?
----------
         1
(1 row)

net=# SELECT 1 WHERE 1 NOT IN (2, NULL);
  ?column?
----------
(0 rows)

(We can't say 1 is NOT IN (2, NULL) because the NULL value could be a
1.  We don't know what a NULL value is.  That's what NULL means.

Mike

>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund                       http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

-
Mike Porter
PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA  2F D2 37 F3 99 ED D1 C2