From: | Mike Porter <mike(at)udel(dot)edu> |
---|---|
To: | Andres Freund <andres(at)2ndquadrant(dot)com> |
Cc: | Kevin Perais <kevin(dot)perais(at)trivia-marketing(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #12556: Clause IN and NOT IN buggy |
Date: | 2015-01-16 16:55:01 |
Message-ID: | alpine.OSX.2.00.1501161149340.64154@enva |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | alanm | 2015-01-16 21:04:10 | BUG #12571: Web site documentation error for make_timestamp |
Previous Message | David G Johnston | 2015-01-16 16:42:34 | Re: BUG #12556: Clause IN and NOT IN buggy |