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

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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services