Hi,
I must disagree with you.
There is enough info to understand what goes wrong. I've run enough queries=
to inspect data.
The fact that the query with IN does not returns the same result as the JOI=
N knowing that there are NULL values proves it. Please check the results of=
all the queries together. You'll see that you do not need data and that th=
e results are incoherent! For the 1st case, I even take a value randomly an=
d I didn't find it in the Account table. So the NOT IN query just can't ret=
urn 0 as there is at least one value that is not present in the Account tab=
le ;)
-----Message d'origine-----
De=A0: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Envoy=E9=A0: jeudi 15 janvier 2015 18:05
=C0=A0: Kevin Perais
Cc=A0: pgsql-bugs@postgresql.org
Objet=A0: Re: [BUGS] BUG #12556: Clause IN and NOT IN buggy
kevin.perais@trivia-marketing.com writes:
> I've been noticing several times that clauses IN and NOT IN are often bug=
gy.
None of your examples demonstrate any such thing. What's much more likely
is that you've forgotten about NOT IN's weird (but spec-mandated) behavior
with NULLs, and/or misspelled a field name so that the output of the
sub-SELECT is actually an outer reference.
> I'll show you 2 concrete cases to illustrate that.
My idea of a "concrete case" would be something that someone else could
reproduce from the given information, which would certainly require test
data.
regards, tom lane
