Re: WIP patch for LATERAL subqueries

From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
Sent: Monday, August 06, 2012 8:07 PM
On Mon, Aug 6, 2012 at 10:07 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> 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

I think in SQL specs it is not clearly mentioned about functions.
The same is mentioned by Tom in his mail
" 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."

> ...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 think if specs doesn't mention clearly about functions then we can use
LATERAL syntax similar to CROSS APPLY
which is proposed by Tom.

With Regards,
Amit Kapila.