LATERAL

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: LATERAL
Date: 2009-09-07 03:59:22
Message-ID: 603c8f070909062059s7c51e055m252745751a0a45df@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=1000000 width=8)
-> Function Scan on generate_series g (cost=0.00..12.50 rows=1000 width=4)
-> Function Scan on generate_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

Responses

  • Re: LATERAL at 2009-09-07 04:45:10 from David Fetter
  • Re: LATERAL at 2009-09-07 07:43:45 from Peter Eisentraut

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2009-09-07 04:45:10 Re: LATERAL
Previous Message David E. Wheeler 2009-09-07 03:41:32 Re: Patch: update Bonjour support to the newer non-deprecated API