From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: WIP patch for LATERAL subqueries |
Date: | 2012-08-06 12:45:33 |
Message-ID: | CA+TgmobZd4Knv+Uio-oyqZrAqiZDSNN+BTvOca5CgO5ycrCe-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-08-06 12:48:06 | Re: WIP Patch: Use sortedness of CSV foreign tables for query planning |
Previous Message | Heikki Linnakangas | 2012-08-06 11:59:43 | Re: Re: [COMMITTERS] pgsql: Fix mapping of PostgreSQL encodings to Python encodings. |