Re: [HACKERS] WIP patch for LATERAL subqueries
Merlin Moncure mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
On Tue, Aug 7, 2012 at 6:08 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
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@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 binLPTyx5cE7r.bin Description: lateral-2.patch.gz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
On Sun, Aug 5, 2012 at 5:58 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.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 t...@sss.pgh.pa.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane Sent: Monday, August 06, 2012 3:28 AM To: pgsql-hackers@postgreSQL.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
On Mon, Aug 6, 2012 at 10:07 AM, Amit Kapila amit.kap...@huawei.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
Robert Haas robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
Robert Haas robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
On Mon, Aug 6, 2012 at 11:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 6, 2012 at 11:09 AM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Monday, August 06, 2012 8:07 PM On Mon, Aug 6, 2012 at 10:07 AM, Amit Kapila amit.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[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? 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
Re: [HACKERS] WIP patch for LATERAL subqueries
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
Andrew Dunstan and...@dunslane.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
2012/8/6 Tom Lane t...@sss.pgh.pa.us: Andrew Dunstan and...@dunslane.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
