Re: BUG #12556: Clause IN and NOT IN buggy - Mailing list pgsql-bugs

From Mike Porter
Subject Re: BUG #12556: Clause IN and NOT IN buggy
Date
Msg-id alpine.OSX.2.00.1501161149340.64154@enva
Whole thread Raw
In response to Re: BUG #12556: Clause IN and NOT IN buggy  (Andres Freund <andres@2ndquadrant.com>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: David G Johnston
Date:
Subject: Re: BUG #12556: Clause IN and NOT IN buggy
Next
From: richard.t.lloyd@gmail.com
Date:
Subject: BUG #12569: pgAdmin3 throws a warning to syslog on startup