I wrote:
> That's about what I thought: it's unique-ifying according to the original
> semijoin qual, without realizing that the pulled-up clause from the lower
> WHERE would need to be treated as part of the semijoin qual. This isn't
> a bug in the existing code, because the case can never arise, since we
> don't treat an IN/=ANY as a semijoin if the sub-select contains any
> outer-level Vars. But if you remove that check from
> convert_ANY_sublink_to_join then you've got to deal with the problem.
> That said, I'm not too sure where the problem is in detail. I'd have
> thought that subquery pullup would stick the subquery's WHERE clause
> into the join quals of the parent JoinExpr node. Is that not happening,
> or is it perhaps not sufficient to cue the UniquePath machinery?
BTW, on further thought, I'm afraid this is a bigger can of worms than
it appears. The remarks above presume that the subquery is simple enough
to be pulled up, which is the case in this example. It might not be too
hard to make that case work. But what if the subquery *isn't* simple
enough to be pulled up --- for instance, it includes grouping or
aggregation? Then there's no way to unify its WHERE clause with the upper
semijoin qual. At the very least, this breaks the uniqueify-then-do-a-
plain-join implementation strategy for semijoins.
So I'm now thinking this patch isn't worth pursuing. Getting all the
corner cases right would be a significant amount of work, and in the
end it would only benefit strangely-written queries.
regards, tom lane