Re: WIP patch for LATERAL subqueries

On Mon, Aug 6, 2012 at 10:07 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>>> 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.
>
> There are certain differences mentioned in the link due to which I am
> not sure it can be mimicked exactly, and may be that's why Sybase also has
> both syntaxes.
>
> Differences
> -----------------
> The LATERAL keyword cannot NULL-supply rows, whereas an OUTER APPLY will.
> Additionally, with a LATERAL derived table, the derived table and the outer
> reference must be separated by a comma. For an APPLY operator, the table
> expression on the right and the outer reference cannot be separated by a
> comma, but they can be separated by any other join operator. In other words,
> the APPLY operator allows references to any table within the left table
> expression, whereas the LATERAL keyword allows references to tables outside
> the current table expression.

I think you can always simulate CROSS APPLY using LATERAL. The syntax
is different but the functionality is the same. However, OUTER APPLY
allows you to do something that I don't think is possible using
LATERAL. While it would be nice to have both CROSS APPLY and OUTER
APPLY, my main point was to suggest supporting CROSS APPLY rather than
the extension to the LATERAL syntax Tom proposed. That is, the spec
allows:

FROM x, LATERAL (SELECT * FROM srf(x.a)) y

...and Tom proposed allowing this to be shortened to:

FROM x, LATERAL srf(x.a)

...and what I'm saying is maybe we should instead allow it to be shortened to:

FROM x CROSS APPLY srf(x.a)

...as some other database systems are already doing. I can't think of
any particular reason why Tom's proposed shorthand would be
problematic; I'm just suggesting that it may be better to support the
same shorthand that other people already support rather than inventing
our own, idiosyncratic shorthand.

That having been said, I get paid the same either way.

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