Re: Reference to parent query from ANY sublink

On 10/31/2013 09:37 PM, Antonin Houska wrote:
> On 10/31/2013 03:46 PM, Antonin Houska wrote:
> I'm not sure if it's legal for the WHERE clause to reference LHS of the
> original outer join (a.j). Some more restriction may be needed. I need
> to think about it a bit more.

For a subquery or sublink expression referencing the outer table of an
OJ (see tab1)

SELECT *
FROM    tab1 a
    LEFT JOIN
    tab2 b
    ON a.i = ANY (
        SELECT  k
        FROM    tab3 c
        WHERE    k = a.i);

I started my considerations by inserting the SEMI JOIN in a form of
subquery, instead of a join node - see SJ_subquery here:

SELECT  *
FROM    tab1 a
    LEFT JOIN
    (
       SELECT *
       tab2 b
       SEMI JOIN
       (  SELECT  k
          FROM    tab3 c
          WHERE   k = a.i
       ) AS ANY_subquery
       ON a.i = ANY_subquery.k
    ) AS SJ_subquery
    ON true;

(To allow a.i in the sublink expression, we'd only need to pass both
tab1 and tab2 to pull_up_sublinks_qual_recurse() in available_rels1.)

However it seem to be these lateral references (from the subquery and/or
the sublink expression) to tab1 that make it impossible for
SJ_subquery to be pulled up into the parent query's join tree - see
jointree_contains_lateral_outer_refs(). I'm not sure if it makes much
sense to pull up the sublink in such a case, does it?

I ended up with this logic: if the join is INNER, both the subquery and
sublink expression can reference either side. If the join is OUTER, only
the inner side can be referenced. Otherwise no attempt to introduce the
SEMI JOIN.

Can this be considered a patch, or is it wrong/incomplete?

// Antonin Houska (Tony)