Re: LATERAL

>>>>> "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.

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.

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

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>").

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;

--
Andrew (irc:RhodiumToad)