Re: WIP patch for LATERAL subqueries

On Sun, Aug 5, 2012 at 5:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I've been idly amusing myself by trying to hack up support for
> SQL-standard LATERAL subqueries.

Cool!

> Currently the patch only implements the syntax called out in the standard,
> namely that you can put LATERAL in front of a <derived table>, which is
> to say a parenthesized sub-SELECT in FROM. It strikes me that it might be
> worth allowing LATERAL with a function-in-FROM as well. So basically
> LATERAL func(args) <alias>
> would be an allowed abbreviation for
> LATERAL (SELECT * FROM func(args)) <alias>
> Since the standard doesn't have function-in-FROM, it has nothing to say
> about whether this is sane or not. The argument for this is mainly that
> SRFs are one of the main use-cases for LATERAL (replacing SRF-in-the-
> SELECT-list usages), so we might as well make it convenient. Any opinions
> pro or con about that?

Apparently Sybase and Microsoft SQL server use a slightly different
syntax, CROSS APPLY, for this.

http://iablog.sybase.com/paulley/2008/07/cross-and-outer-apply/

It may make sense to consider mimicking that instead of inventing our
own way of doing it, but I haven't investigated much so it's also
possible that it doesn't make sense.

> While fooling around in the planner I realized that I have no idea what
> outer-level aggregates mean in a LATERAL subquery, and neither does
> Postgres:
> regression=# select 1 from tenk1 a, lateral (select * from int4_tbl b where f1 = max(a.unique1)) x;
> ERROR: plan should not reference subplan's variable
> I don't see anything prohibiting this in SQL:2008, but ordinarily this
> would be taken to be an outer-level aggregate, and surely that is not
> sensible in the LATERAL subquery. For the moment it seems like a good
> idea to disallow it, though I am not sure where is a convenient place
> to test for such things. Has anyone got a clue about whether this is
> well-defined, or is it simply an oversight in the spec?

My mental picture of LATERAL (which might be inaccurate) is that it
has the semantics that you'd get from a parameterized nestloop. So I
can't assign any meaning to that either.

> Comments, better ideas?

Thanks for working on this - sorry I don't have more thoughts right at
the moment.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company