Re: LATERAL

On Tue, Sep 8, 2009 at 6:29 PM, Andrew
Gierth<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>>>>>> "David" == David Fetter <david(at)fetter(dot)org> writes:
>
>  >> I've attempted to search the archives for references to the SQL
>  >> LATERAL feature, which AIUI is fairly-frequently requested.
>  >> [snip]
>  >> Has anyone poked at this at all?
>
>  David> I believe Andrew (RhodiumToad) Gierth is taking a look at
>  David> implementing the full LATERAL feature from SQL:2008.
>
> I've looked, but I've not actually had time to try any actual work on
> implementation, so anyone else who fancies a go shouldn't hesitate.

Thanks for your thoughts - I appreciate it.

> Just to pick up on some points from the discussion:
>
> 1. LATERAL has to be explicit because it changes the scope of
> references.  For example, in:
> ... (select ... FROM (select a AS b), (select b)) ...
> the "b" in the second subselect could be an outer reference, but
> it can't be a reference to the first subquery; whereas in:
> ... (select ... FROM (select a AS b), LATERAL (select b)) ...
> the "b" in the second subselect refers to the result of the first
> subselect.

Can you provide a more complete example? I'm unable to construct a
working example of this type. For example:

rhaas=# select (select 1 from (select a as b) x, (select b) y) from t1;
ERROR: subquery in FROM cannot refer to other relations of same query
level at character 50

Though this works as expected:
rhaas=# select (select 1 from (select a) x, (select b) y) from t1;

> 2. LATERAL in general constrains both the join order and the join
> plan, assuming any lateral references are actually made.

Peter seemed to be saying that LATERAL() must syntactically follow the
same-level FROM items to which it refers. Is that your understanding
also?

> 3. LATERAL specifically IS allowed with left outer joins, though the
> syntax productions in the spec are sufficiently obscure that this
> isn't obvious.  In general there are (as far as I can tell from the
> syntax rules) two ways to use it:
>
> SELECT ... FROM foo, LATERAL (bar)
>
> or
>
> SELECT ... FROM foo [LEFT] JOIN LATERAL (bar) ON ...
>
> Note that RIGHT JOIN LATERAL and FULL JOIN LATERAL are expressly excluded
> (syntax rule 2 for "<joined table>").

Makes sense to me.

> 4. LATERAL allows some optimizations that aren't currently done, either
> by explicitly rewriting the query, or (in theory) the optimizer itself
> could consider a lateral plan (I believe Oracle does this). This would
> apply to queries of this form:
>
> SELECT ... FROM t1 LEFT JOIN (t2 JOIN t3 ON (t2.a=t3.a)) on (t1.a=t2.a);
>
> which currently forces the t2/t3 join to occur first even where t1 is
> small; this could be rewritten with LATERAL as:
>
> SELECT ...
>  FROM t1
>       LEFT JOIN LATERAL (select * from t2 join t3 on (t2.a=t3.a)
>                                   where t2.a=t1.a) s
>       ON true;

Well, you haven't actually commuted the joins here - how do you have
in mind for PostgreSQL to execute this? I'm guessing that it's
something like a nest loop with t1 as the outer side and the lateral
subquery as the inner side, so that the executor repeatedly executes
"select * from t2 join t3 on t2.a = t3.a where t2.a = $1"?

...Robert