Re: BUG #3494: may be Query Error: subplan does not executed

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> I don't understand that code very well. Why does it think that the right
> pathkeys of "test1.id = test2.id" and "test1.id = test1.id+test2.id" are
> equal?

They *will* be equal ... after the join (if correctly implemented :-().
The problem is the code is missing the possibility that one side or the
other can't have been checked yet because it involves variables from
both sides of the current join. The case it is thinking about is like

where a.x1 = b.y1 and a.x1 = b.z1

In this case the x1 = z1 condition need not be checked at the join
because the implied equality b.y1 = b.z1 will have been enforced at the
scan of b. In general the rule is that all conditions involving a set
of mutually equated expressions should be checked as early as possible,
which usually means that only one test is needed at a join point. But
there's no way to constrain test1.id+test2.id at any earlier point than
the join, so that clause isn't redundant.

I believe HEAD gets this right, because it's explicitly recognizing
"newly computable" members of an equivalence class:

* First, scan the EC to identify member values that are computable
* at the outer rel, at the inner rel, or at this relation but not in
* either input rel. The outer-rel members should already be enforced
* equal, likewise for the inner-rel members. We'll need to create
* clauses to enforce that any newly computable members are all equal
* to each other as well as to at least one input member, plus enforce
* at least one outer-rel member equal to at least one inner-rel member.

However the old ad-hoc code didn't have this much structure ...

> Checking for overlapping left and right relation sets in
> join_clause_is_redundant seems to fix the problem, though I'm not sure
> if there's some other cases that that doesn't catch.

No, that doesn't work (at least not in cases involving more than 2
rels). What we have to look for is left or right relids that overlap
both sides of the proposed join, which is a bit of a nuisance because
this subroutine isn't told exactly what the proposed join is. We'll
have to pass down the left/right join relids from a level or two higher.

regards, tom lane