On Tue, Sep 8, 2009 at 6:29 PM, Andrew Gierth<and...@tao11.riddles.org.uk> wrote: >>>>>> "David" == David Fetter <da...@fetter.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
