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

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