LATERAL - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | LATERAL |
Date | |
Msg-id | 603c8f070909062059s7c51e055m252745751a0a45df@mail.gmail.com Whole thread Raw |
Responses |
Re: LATERAL
Re: LATERAL |
List | pgsql-hackers |
I've attempted to search the archives for references to the SQL LATERAL feature, which AIUI is fairly-frequently requested. Most of the discussion that I've found harks back to 2003, and that seems to discuss more the need for eventual support of the feature than exactly what it is or how to implement it. Looking around the web a little, I found these links: http://farrago.sourceforge.net/design/CollectionTypes.html http://iablog.sybase.com/paulley/2008/07/cross-and-outer-apply/ Based on reading through this discussion, it appears that LATERAL is mostly a bit of syntactic sugar that requests that the parser allow you to reference tables at the same query level. Assuming that the necessary executor support were present (which it's currently not), it's unclear to me why one couldn't simply allow such references unconditionally. We currently explicitly block such references in parse_clause.c, but the comments indicate that this is for reasons of standards-conformance, not semantic ambiguity. It appears that we're not completely without the ability to handle queries of this type. For example, this works: select g, generate_series(1,g) as h from generate_series(1,10) g; But this doesn't: select g, h from generate_series(1,10) g, generate_series(1,g) h; Just for kicks, I tried removing the code that throws a syntax error on the latter query, which resulted in a core dump inside ExecEvalVar(), execQual.c:546, trying to deference a TupleTableSlot. I'm guessing that this is because it can't get access to the right variables - the plan actually looks quite sane: rhaas=# explain select g, h from generate_series(1,10) g, generate_series(1,g) h; QUERY PLAN --------------------------------------------------------------------------------Nested Loop (cost=0.00..22512.50 rows=1000000width=8) -> Function Scan on generate_series g (cost=0.00..12.50 rows=1000 width=4) -> Function Scan ongenerate_series h (cost=0.00..12.50 rows=1000 width=4) (3 rows) The first query just shows up a function scan, which means there's some projection operation happening here that isn't exposed at the plan level. I'm guessing that what needs to happen here is that the planner needs to be taught that LATERAL queries can only be implemented as a nested loop (right? unless they're not really using the LATERAL-ness...) and that the executor needs to be taught how to make the vars for the outer side of a nestloop visible to the inner side, when necessary. Has anyone poked at this at all? ...Robert
pgsql-hackers by date: