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 JOIN 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 the results are incoherent! For the 1st case, I even take a value randomly and I didn't find it in the Account table. So the NOT IN query just can't return 0 as there is at least one value that is not present in the Account table ;)

-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : jeudi 15 janvier 2015 18:05
À : Kevin Perais
Cc : pgsql-bugs(at)postgresql(dot)org
Objet : Re: [BUGS] BUG #12556: Clause IN and NOT IN buggy

kevin(dot)perais(at)trivia-marketing(dot)com writes:
> I've been noticing several times that clauses IN and NOT IN are often buggy.

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