Re: Question about pull_up_sublinks_qual_recurse

Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> After some more self review, I find my proposal has the following side
> effects.

Yeah, I do not think this works at all. The mechanism as it stands
right now is that we can insert pulled-up semijoins above j->larg
if they only need variables from relations in j->larg, and we can
insert them above j->rarg if they only need variables from relations
in j->rarg. You can't just ignore that distinction and insert them
somewhere further up the tree. Per the comment in
pull_up_sublinks_jointree_recurse:

* Now process qual, showing appropriate child relids as available,
* and attach any pulled-up jointree items at the right place. In the
* inner-join case we put new JoinExprs above the existing one (much
* as for a FromExpr-style join). In outer-join cases the new
* JoinExprs must go into the nullable side of the outer join. The
* point of the available_rels machinations is to ensure that we only
* pull up quals for which that's okay.

If the pulled-up join doesn't go into the nullable side of the upper
join then you've changed semantics. In this case, it'd amount to
reassociating a semijoin that was within the righthand side of another
semijoin to above that other semijoin. The discussion of outer join
reordering in optimizer/README says that that doesn't work, and while
I'm too lazy to construct an example right now, I believe it's true.

regards, tom lane