Re: WIP patch for LATERAL subqueries

I've been idly amusing myself by trying to hack up support for
SQL-standard LATERAL subqueries. I've got something that turns over,
more or less:

regression=# select * from int4_tbl a, lateral (select unique1,unique2 from tenk1 b where a.f1 = unique1) x;
f1 | unique1 | unique2
----+---------+---------
0 | 0 | 9998
(1 row)

regression=# explain select * from int4_tbl a, lateral (select unique1,unique2 from tenk1 b where a.f1 = unique1) x;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.00..42.55 rows=5 width=12)
-> Seq Scan on int4_tbl a (cost=0.00..1.05 rows=5 width=4)
-> Index Scan using tenk1_unique1 on tenk1 b (cost=0.00..8.28 rows=1 width=8)
Index Cond: (a.f1 = unique1)
(4 rows)

but there's a good deal of work left to do, some of which could use some
discussion.

Feature/semantics issues:

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?

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?

Parser issues:

I'm reasonably happy with the grammar patch, though tempted to refactor
it to reduce the amount of duplication (and would be more tempted if we
add LATERAL function calls). I'm thinking that an opt_alias production
could be used to eliminate the duplication, and am also strongly tempted
to move the error for no subselect alias out of the grammar and into
transformRangeSubselect.

Note that I made LATERAL be col_name_keyword. It can no longer be allowed
as a function name because this would be formally ambiguous:
LATERAL ((SELECT x FROM t)) t(x)
Is that a call on a function named LATERAL with a scalar-subquery
argument, or is it a LATERAL subquery with extra parentheses? However,
there seems no point in making it fully reserved. The <table_ref>
productions would still have to be repeated, because even with LATERAL
fully reserved, we can't combine them using an "opt_lateral" production.
On seeing "(" at the start of a FROM item, the parser doesn't know enough
to decide whether it should reduce opt_lateral to empty, which would be
the appropriate thing if the "(" starts a sub-select but not if it is,
say, a parenthesized JOIN tree. We could only avoid that by allowing
opt_lateral before every type of table_ref and then throwing explicit
errors for the disallowed cases, which doesn't end up making the grammar
simpler.

Although lateral cross-references work okay for the successive-FROM-items
case, they don't work at all yet for JOIN cases:

regression=# select * from int4_tbl a join lateral (select unique1,unique2 from tenk1 b where f1 = unique1) x on true;
ERROR: column "f1" does not exist
LINE 1: ...ateral (select unique1,unique2 from tenk1 b where f1 = uniqu...
^

regression=# select * from int4_tbl a join lateral (select unique1,unique2 from tenk1 b where a.f1 = unique1) x on true;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 1: ...ateral (select unique1,unique2 from tenk1 b where a.f1 = uni...
^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.

The reason that the separate-FROM-items case works is that
transformFromClause pushes each FROM-clause item into p_relnamespace and
p_varnamespace immediately after parsing it, making those names visible
during parsing of subsequent FROM items. However, transformFromClauseItem
doesn't push the left-hand item into the lists before parsing the
right-hand item.

Now, the way this is being done currently is really pretty broken anyway.
As Andrew Gierth noted some time ago in
http://archives.postgresql.org/message-id/87ocpjscpa.fsf@news-spur.riddles.org.uk
it is incorrect to make these names visible to non-LATERAL subqueries,
because they may capture what should have been a valid reference to a
parent-level variable. Furthermore, it's pretty grotty to allow the
reference and then have to re-scan the subquery in transformRangeSubselect
to see if we allowed anything we should have disallowed.

What I'm thinking of, but have not yet tried to code, is that the
p_relnamespace and p_varnamespace lists should be divided into pairs
(so four lists altogether per ParseState). p_relnamespace/p_varnamespace
should always contain exactly those RTEs that are validly referenceable
by qualified or unqualified Vars (respectively) at the current point in
parsing. The new lists, say p_relnamespace_lateral/p_varnamespace_lateral,
contain RTEs that are validly referenceable inside a LATERAL subquery
occuring at the current point in this ParseState's query. We'd also want
a p_lateral_active boolean to show whether we're inside a LATERAL
subquery; that is what would tell variable lookup whether it should search
the p_xxx_lateral lists. With a data structure like this, I think we
can fix things so that only valid references are ever accepted and there
is no need for rechecking in transformRangeSubselect (or
transformJoinOnClause for that matter). The main reason for the current
arrangement is to be able to throw useful errors in case of an illegal
lateral reference; but I think we can still do that at the point of the
illegal reference, by groveling through the whole p_rtable list looking
to see if there would have been a match (which is more or less what
searchRangeTable already does for qualified references, so we'd just be
extending that approach to unqualified names).

One fine point here is that a LATERAL subquery in the RHS of a JOIN clause
is only allowed to reference the LHS of the JOIN when the join type is not
RIGHT or FULL. The way I am inclined to implement this is to not add the
LHS to the p_xxx_lateral lists when the join type is wrong, so that the
LHS is simply not in scope in the RHS. If you read SQL:2008 carefully,
their notion of how to handle this seems to be that the LHS *is* in scope
(since section 7.6 <table reference> syntax rule 6a doesn't say anything
about join types) but then you have to throw an error if the LHS is
actually referenced and the join type is wrong (section 7.7 <joined table>
syntax rule 2). To do it exactly like they say, we'd need to add some
kind of annotation to the p_xxx_lateral list items when they're on the
wrong side of a join, which would be a real PITA I think. In normal
cases, the simpler implementation would just lead to a different error
message. But it's conceivable that it would accept a query as valid (by
resolving an ambiguous reference as matching some outer query level) when
the spec would say it's invalid. I'm inclined to think that the spec is
simply poorly thought out here. I note that the prohibition against
RIGHT/FULL joins is not there in SQL:99, so they definitely weren't
thinking straight then, and the section 7.7 rule looks like a band-aid
over the SQL:99 mistake rather than a good fix. So I don't feel too bad
about deviating in this corner case, but I wonder if anyone else feels
differently, and if so whether they have an idea for a clean
implementation that matches the spec exactly.

Planner issues:

For the moment, I've hacked prepjointree.c's is_simple_subquery() to
prevent pull-up of LATERAL subqueries. This is just to restrict the scope
of the planner changes to SubqueryScan paths/plans. Relaxing the
restriction will require making sure that all other path/plan types can be
parameterized, which is something I figure can be left for later. This
does mean there are cases that won't be optimized as nicely as one could
wish, but for a work-in-progress implementation that doesn't bother me.

The thing that is most worrisome in this area is that heretofore, the
planner has assumed that every relation has at least one unparameterized
path; but for a LATERAL subquery, or anything we might pull up out of it,
there are no such paths. The main implication of this in the code is that
a RelOptInfo's cheapest_startup_path/cheapest_total_path might not exist,
at least not with the current definition that they're the cheapest
unparameterized paths. For the moment I've dealt with this by lobotomizing
a lot of places where these paths were assumed to not be NULL. I think
however that that's unduly constraining join planning: basically, we won't
ever consider a merge or hash join involving a still-parameterized lateral
subquery, and that's probably not good. I'm considering altering the
definitions of these fields to be "the cheapest minimally-parameterized
paths", but haven't quite decided if that's a good idea or not. There
are various areas such as GEQO that will crash on lateral subqueries
pending a resolution of this, because there wasn't any easy way to just
make them punt.

Also, there are at least three places --- extract_lateral_references,
set_subquery_pathlist, and identify_nestloop_extparams --- that are
independently re-deriving information about the sets of lateral references
in a subquery. This seems like a bit of a crock; I'd be happier if we
could do the work just once in some fashion. I note that SubLink
processing has a very similar problem of needing to pull out all the
upper references to form "args" lists, too. Not sure how to refactor
that, but maybe we should expect the parser to provide annotation about
outer refs instead of making the planner re-derive it?

Executor issues:

AFAICT, there aren't any. Sweet. The parameterization work I did two
years ago held up.

Comments, better ideas?

regards, tom lane

On 08/05/2012 05:58 PM, Tom Lane wrote:
> I've been idly amusing myself by trying to hack up support for
> SQL-standard LATERAL subqueries. I've got something that turns over,
> more or less:

Awesome!!

>
> 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?

Pro. As you say this is the main use case, and the longer syntax just
seems unnecessary fluff.

I'll comment on the rest of you email later, but this is just great
news. Hardly a month goes by that I don't wish for LATERAL.

cheers

andrew

I wrote:
> 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?

On further reflection I think this is indeed disallowed by spec. The
outer query is clearly the "aggregation query" of the aggregate, and the
aggregate appears inside that query's FROM list, therefore it's no good;
see SQL:2008 6.9 <set function specification> syntax rules 6 and 7.
(I missed this before because it's not under the aggregate function
heading.)

So the problem here is just that parseCheckAggregates neglects to grovel
through subqueries-in-FROM looking for aggregates of the current level.
Since AFAICS the case cannot arise without LATERAL, this isn't really a
pre-existing bug.

I find it fairly annoying though that parseCheckAggregates (and likewise
parseCheckWindowFuncs) have to dig through previously parsed query trees
to look for misplaced aggregates; so adding even more of that is grating
on me. It would be a lot cleaner if transformAggregateCall and
transformWindowFuncCall could throw these errors immediately. The
reason they can't is lack of context about what portion of the query we
are currently parsing. I'm thinking it'd be worthwhile to add an enum
field to ParseState that shows whether we're currently parsing the
associated query level's target list, WHERE clause, GROUP BY clause,
etc. The easiest way to ensure this gets set for all cases should be to
add the enum value as another argument to transformExpr(), which
would then save it into the ParseState for access by subsidiary
expression transformation functions.

Thoughts?

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 08/05/2012 05:58 PM, Tom Lane 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.

> Pro. As you say this is the main use case, and the longer syntax just
> seems unnecessary fluff.

After some experimentation it seems that this only works if we promote
LATERAL to a fully reserved keyword. Apparently the reason is that
given non-reserved LATERAL followed by an identifier, it's not clear
without additional lookahead whether we have "LATERAL func_name ..."
or the LATERAL is a table name and the identifier is an alias. And the
parser has to make a shift/reduce decision before it can look beyond the
identifier. (Without the LATERAL func_name syntax, there's no ambiguity
because LATERAL in its keyword meaning must be immediately followed by a
left paren.)

Since LATERAL has been a reserved word in every SQL spec since SQL:99,
I don't feel too bad about making it fully reserved for us too, but
nonetheless this is a cost of adding this syntax.

regards, tom lane

2012/8/6 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 08/05/2012 05:58 PM, Tom Lane 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.
>
>> Pro. As you say this is the main use case, and the longer syntax just
>> seems unnecessary fluff.
>
> After some experimentation it seems that this only works if we promote
> LATERAL to a fully reserved keyword. Apparently the reason is that
> given non-reserved LATERAL followed by an identifier, it's not clear
> without additional lookahead whether we have "LATERAL func_name ..."
> or the LATERAL is a table name and the identifier is an alias. And the
> parser has to make a shift/reduce decision before it can look beyond the
> identifier. (Without the LATERAL func_name syntax, there's no ambiguity
> because LATERAL in its keyword meaning must be immediately followed by a
> left paren.)
>
> Since LATERAL has been a reserved word in every SQL spec since SQL:99,
> I don't feel too bad about making it fully reserved for us too, but
> nonetheless this is a cost of adding this syntax.

+1

Pavel

>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Robert Haas
Sent: Monday, August 06, 2012 6:16 PM
On Sun, Aug 5, 2012 at 5:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.

With Regards,
Amit Kapila.

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Monday, August 06, 2012 3:28 AM
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: [HACKERS] WIP patch for LATERAL subqueries

> I've been idly amusing myself by trying to hack up support for
SQL-standard LATERAL subqueries. I've got something
> that turns over, more or less:

> regression=# select * from int4_tbl a, lateral (select unique1,unique2
from tenk1 b where a.f1 = unique1) x;
> f1 | unique1 | unique2
> ----+---------+---------
> 0 | 0 | 9998
> (1 row)

> regression=# explain select * from int4_tbl a, lateral (select
unique1,unique2 from tenk1 b where a.f1 = unique1) x;
> QUERY PLAN

>
----------------------------------------------------------------------------
-------
> Nested Loop (cost=0.00..42.55 rows=5 width=12)
> -> Seq Scan on int4_tbl a (cost=0.00..1.05 rows=5 width=4)
> -> Index Scan using tenk1_unique1 on tenk1 b (cost=0.00..8.28 rows=1
width=8)
> Index Cond: (a.f1 = unique1)
> (4 rows)

> but there's a good deal of work left to do, some of which could use some
discussion.

> Feature/semantics issues:

> 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?

I have checked Sybase also has similar syntax for functions by other keyword
APPLY. So this should be good way to specify.

> 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?

I have checked in Oracle and it gives error in such query:
SQL> select * from t1, Lateral (select * from t2 where t2.c2 = max(t1.c1));
select * from t1, Lateral (select * from t2 where t2.c2 = max(t1.c1))
*
ERROR at line 1:
ORA-00934: group function is not allowed here

With Regards,
Amit Kapila.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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/

Well, this is only a blog entry and not their manual, but AFAICT that is
just a useless deviation from SQL-standard syntax; it does nothing that
"CROSS JOIN LATERAL" or "LEFT JOIN LATERAL ... ON true" wouldn't do.
I can't tell if the blogger simply doesn't know that LATERAL can be used
in a JOIN nest, or if that's actually a misfeature of the DBMS.

I'm not in favor of duplicating this.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

Uh, what exactly? AFAICT from that blog entry, "x OUTER APPLY y" is
exactly the same as "x LEFT JOIN LATERAL y ON true". Okay, so you
saved three words, but is that a good enough reason to invent a
nonstandard syntax?

regards, tom lane

On Mon, Aug 6, 2012 at 11:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> 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.
>
> Uh, what exactly? AFAICT from that blog entry, "x OUTER APPLY y" is
> exactly the same as "x LEFT JOIN LATERAL y ON true". Okay, so you
> saved three words, but is that a good enough reason to invent a
> nonstandard syntax?

I wasn't sure that x LEFT JOIN LATERAL (SELECT * FROM y) ON true is
valid syntax. I thought that perhaps LATERAL() was only allowed
around a top-level FROM-list item.

However, if it is allowed, then I agree that the extra syntax isn't
adding any functionality; it's just a question of whether you happen
to like their particular choice of notational shorthand.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Aug 6, 2012 at 11:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Uh, what exactly? AFAICT from that blog entry, "x OUTER APPLY y" is
>> exactly the same as "x LEFT JOIN LATERAL y ON true". Okay, so you
>> saved three words, but is that a good enough reason to invent a
>> nonstandard syntax?

> I wasn't sure that x LEFT JOIN LATERAL (SELECT * FROM y) ON true is
> valid syntax. I thought that perhaps LATERAL() was only allowed
> around a top-level FROM-list item.

No. LATERAL is allowed in a <table reference>, which can be either
a top-level FROM item or a component of a JOIN nest. (My current
patch doesn't actually work for the latter case, but I'm going to
work on fixing that next.) What's curious about that Sybase blog
is that the blogger seems to think that LATERAL can only be used
at top level ... but I'm not sure if that's actually a restriction
in Sybase, or just a gap in his knowledge.

regards, tom lane

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.

Here's an updated version of my LATERAL patch.

* Accepts "LATERAL func_name(args)".

* Handles LATERAL in JOIN nests now. I rewrote the way
transformFromClause manages visibility of previously-parsed FROM items.
Rather than my previous idea of adding more namespace lists to a
ParseState, I changed p_relnamespace and p_varnamespace to be lists of
ParseNamespaceItem structs, which carry an RTE pointer plus visibility
flags. This seemed to work out fairly well, and allowed me to implement
the RIGHT/FULL JOIN case exactly as per SQL spec, that is the left side
of a "RIGHT JOIN LATERAL" is visible to the right side but throws error
if used. (I'm a bit tempted now to collapse p_relnamespace and
p_varnamespace into just one list, by adding two more booleans to
ParseNamespaceItem to show whether the item is visible for qualified or
unqualified references. But that would affect code that the current
patch doesn't need to touch, so it seems better to postpone it to a
separate refactoring patch.) This results in some changes in the error
messages output for improper-column-reference errors, as shown in the
regression test deltas. It also fixes the pre-existing spec-conformance
issue about lateral versus parent references, as per Andrew Gierth in
http://archives.postgresql.org/message-id/87ocpjscpa.fsf@news-spur.riddles.org.uk

* Throws error if LATERAL is used to create an aggregate that belongs to
its aggregation query's FROM clause. I did this in a slightly grotty
way: transformAggregateCall looks at the p_lateral_active field of the
appropriate pstate level, relying on the fact that the only way the
case can happen is via LATERAL. As I mentioned earlier, I think it'd
be better to add a ParseState field showing exactly which query part
we're parsing at any instant. However, again that seems better done as
part of a separate refactoring patch.

I have not done anything yet about the planner shortcomings.

What I'd like to do next, barring objections, is to band-aid the places
where the planner could crash on a LATERAL query (probably just make it
throw FEATURE_NOT_SUPPORTED errors), write some documentation, and
then commit what I've got. After that I can go back to improve the
planner and work on the parser refactoring issues as separate patches.

Comments, better ideas?

regards, tom lane

I wrote:
> What I'd like to do next, barring objections, is to band-aid the places
> where the planner could crash on a LATERAL query (probably just make it
> throw FEATURE_NOT_SUPPORTED errors), write some documentation, and
> then commit what I've got. After that I can go back to improve the
> planner and work on the parser refactoring issues as separate patches.

... and done (though the pgsql-committers message seems to have got hung
up for moderation). I put some simplistic examples into section 7.2.1.5
and the SELECT reference page ... if anybody has ideas for
more-compelling small examples, please speak up.

regards, tom lane

On Tue, Aug 7, 2012 at 6:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> What I'd like to do next, barring objections, is to band-aid the places
>> where the planner could crash on a LATERAL query (probably just make it
>> throw FEATURE_NOT_SUPPORTED errors), write some documentation, and
>> then commit what I've got. After that I can go back to improve the
>> planner and work on the parser refactoring issues as separate patches.
>
> ... and done (though the pgsql-committers message seems to have got hung
> up for moderation). I put some simplistic examples into section 7.2.1.5
> and the SELECT reference page ... if anybody has ideas for
> more-compelling small examples, please speak up.

This is just awesome. Anyways, I was looking around the docs for
references to the old methodology of select list SRF function calls.
This paragraph:
http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

could probably use some enhancement describing best practices in a
LATERAL world and more examples of dealing with set returning
functions in general. I also noticed that the build in SRF page
(http://www.postgresql.org/docs/devel/static/functions-srf.html) lies
with the comment "This section describes functions that possibly
return more than one row. Currently the only functions in this class
are series generating functions" since at minimum we have 'unnest' so
that page could use some wordsmithing as well.

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> This is just awesome. Anyways, I was looking around the docs for
> references to the old methodology of select list SRF function calls.
> This paragraph:
> http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

> could probably use some enhancement describing best practices in a
> LATERAL world and more examples of dealing with set returning
> functions in general.

Done, thanks for the suggestion.

> I also noticed that the build in SRF page
> (http://www.postgresql.org/docs/devel/static/functions-srf.html) lies
> with the comment "This section describes functions that possibly
> return more than one row. Currently the only functions in this class
> are series generating functions" since at minimum we have 'unnest' so
> that page could use some wordsmithing as well.

Yeah, there's also some regexp-related SRFs, as well as a boatload of
built-in SRFs that are mainly meant to underlie views. I guess we could
try to force all of those into this page, but it doesn't really seem
like it'd be an improvement. I took out the claim that these were all
such functions, instead.

regards, tom lane