Re: not in(subselect) in 8.4

On Sat, Feb 21, 2009 at 10:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Carey <scott(at)richrelevance(dot)com> writes:
>> Are there any optimizations planned for the case where columns are
>> defined as NOT NULL?
>
> We might get around to recognizing that case as an antijoin sometime.
> It's nontrivial though, because you have to check for an intermediate
> outer join causing the column to be possibly nullable after all.
>
>> It turns out to be a rare use case for someone to write a subselect
>> for a NOT IN or IN clause that will have NULL values.
>
> Judging from the steady flow of "why doesn't my NOT IN query work"
> newbie questions, I don't think it's so rare as all that.

I think it's rare to do it on purpose, precisely because of the weird
semantics we all hate. I have done it by accident, more than once,
and then fixed it by adding WHERE blah IS NOT NULL to the subquery.
So I think Scott is basically right.

> There's surely some population of people who know enough or could be
> trained to be careful about using NOT NULL columns, but they could also
> be trained to use NOT EXISTS, and dodge the whole bullet from the start.

There are far more important reasons to make columns NOT NULL than
avoiding strange results from NOT IN. Personally, I have gotten used
to the fact that the planner sucks at handling NOT IN and so always
write LEFT JOIN ... WHERE pk IS NULL, so it's not important to me that
we fix it. But it's certainly a foot-gun for the inexperienced, as it
is both the most compact and (at least IMO) the most intuitive
formulation of an anti-join.

...Robert