So far, a suquery of ANY sublink located in WHERE/ON clause can't
reference vars exactly one level up, as long as pull-up into the join
tree is expected. Now that we have LATERAL subqueries (there seem to be
no specifics of SEMI JOIN when it comes to parameterization etc), I
think this restriction can be lifted. Thus a subplan should be avoided
often.
Not sure if something like that is applicable to EXISTS: various parts
are cut-off, so there are probably no vars having (varlevelsup == 1).
The attachments show cases where the SEMI JOIN should be inserted above
INNER JOIN and into the nullable side of OUTER JOIN respectively, each
before the patch is applied and after that.
So far I didn't test recursive processing, but don't expect problems here.
Can the change be as simple as this or do I neglect anything?
// Antonin Houska (Tony)