David Fetter <da...@fetter.org> writes:
> Please find attached a patch which implements approach 3. The vast
> majority of it is changes to the regression tests. The removed
> regression tests in join.{sql,out} are no longer errors, although some
> of them are pretty standard DoS attacks, hence they're all removed.
Here's a less quick-hack-y approach to that.
regards, tom lane
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index bcee9468240e8a10b8e491a8f1ab8a1e2c5d9ede..caa9f1b3389e5ce57e2e50d13011e41c0ed3d11b 100644
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
*************** SELECT *
*** 717,730 ****
</indexterm>
<para>
! Subqueries and table functions appearing in <literal>FROM</> can be
preceded by the key word <literal>LATERAL</>. This allows them to
reference columns provided by preceding <literal>FROM</> items.
! (Without <literal>LATERAL</literal>, each <literal>FROM</> item is
evaluated independently and so cannot cross-reference any other
<literal>FROM</> item.)
A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree; in the latter
case it can also refer to any items that are on the left-hand side of a
<literal>JOIN</> that it is on the right-hand side of.
</para>
--- 717,740 ----
</indexterm>
<para>
! Subqueries appearing in <literal>FROM</> can be
preceded by the key word <literal>LATERAL</>. This allows them to
reference columns provided by preceding <literal>FROM</> items.
! (Without <literal>LATERAL</literal>, each subquery is
evaluated independently and so cannot cross-reference any other
<literal>FROM</> item.)
+ </para>
+
+ <para>
+ Table functions appearing in <literal>FROM</> can also be
+ preceded by the key word <literal>LATERAL</>, but for functions the
+ key word is optional; the function's arguments can contain references
+ to columns provided by preceding <literal>FROM</> items in any case.
+ </para>
+
+ <para>
A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree. In the latter
case it can also refer to any items that are on the left-hand side of a
<literal>JOIN</> that it is on the right-hand side of.
</para>
*************** FROM polygons p1 CROSS JOIN LATERAL vert
*** 770,776 ****
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</programlisting>
! or in several other equivalent formulations.
</para>
<para>
--- 780,788 ----
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</programlisting>
! or in several other equivalent formulations. (As already mentioned,
! the <literal>LATERAL</> key word is unnecessary in this example, but
! we use it for clarity.)
</para>
<para>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 26d511fad8c5b8d02bda618006ce2606036db7c7..0f9d52753d832fa458aca563fa2bfcf558120818 100644
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** TABLE [ ONLY ] <replaceable class="param
*** 504,521 ****
<varlistentry>
<term><literal>LATERAL</literal></term>
<listitem>
! <para>The <literal>LATERAL</literal> key word can precede a
! sub-<command>SELECT</command> or function-call <literal>FROM</>
! item. This allows the sub-<command>SELECT</command> or function
! expression to refer to columns of <literal>FROM</> items that appear
! before it in the <literal>FROM</> list. (Without
! <literal>LATERAL</literal>, each <literal>FROM</> item is evaluated
! independently and so cannot cross-reference any other
! <literal>FROM</> item.) A <literal>LATERAL</literal> item can
! appear at top level in the <literal>FROM</> list, or within a
! <literal>JOIN</> tree; in the latter case it can also refer to any
! items that are on the left-hand side of a <literal>JOIN</> that it is
! on the right-hand side of.
</para>
<para>
--- 504,531 ----
<varlistentry>
<term><literal>LATERAL</literal></term>
<listitem>
! <para>
! The <literal>LATERAL</literal> key word can precede a
! sub-<command>SELECT</command> <literal>FROM</> item. This allows the
! sub-<command>SELECT</command> to refer to columns of <literal>FROM</>
! items that appear before it in the <literal>FROM</> list. (Without
! <literal>LATERAL</literal>, each sub-<command>SELECT</command> is
! evaluated independently and so cannot cross-reference any other
! <literal>FROM</> item.)
! </para>
!
! <para>
! <literal>LATERAL</literal> can also precede a function-call
! <literal>FROM</> item, but in this case it is a noise word, because
! the function expression can refer to earlier <literal>FROM</> items
! in any case.
! </para>
!
! <para>
! A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree. In the
! latter case it can also refer to any items that are on the left-hand
! side of a <literal>JOIN</> that it is on the right-hand side of.
</para>
<para>
*************** SELECT distributors.* WHERE distributors
*** 1738,1744 ****
sub-<command>SELECT</command>; that is, the syntax
<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
is approximately equivalent to
! <literal>FROM (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
</para>
</refsect2>
--- 1748,1759 ----
sub-<command>SELECT</command>; that is, the syntax
<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
is approximately equivalent to
! <literal>FROM LATERAL (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
! Note that <literal>LATERAL</> is considered to be implicit; this is
! because the standard requires <literal>LATERAL</> semantics for an
! <literal>UNNEST()</> item in <literal>FROM</>.
! <productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
! same as other set-returning functions.
</para>
</refsect2>
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index dd78500aa9328bbf8ed29d2ef6388cdc201eb03f..b9655954cde32d9525e971b61a668822be040b49 100644
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
*************** transformRangeFunction(ParseState *pstat
*** 503,508 ****
--- 503,509 ----
{
Node *funcexpr;
char *funcname;
+ bool is_lateral;
RangeTblEntry *rte;
/*
*************** transformRangeFunction(ParseState *pstat
*** 514,525 ****
funcname = FigureColname(r->funccallnode);
/*
! * If the function is LATERAL, make lateral_only names of this level
! * visible to it. (LATERAL can't nest within a single pstate level, so we
! * don't need save/restore logic here.)
*/
Assert(!pstate->p_lateral_active);
! pstate->p_lateral_active = r->lateral;
/*
* Transform the raw expression.
--- 515,530 ----
funcname = FigureColname(r->funccallnode);
/*
! * We make lateral_only names of this level visible, whether or not the
! * function is explicitly marked LATERAL. This is needed for SQL spec
! * compliance in the case of UNNEST(), and seems useful on convenience
! * grounds for all functions in FROM.
! *
! * (LATERAL can't nest within a single pstate level, so we don't need
! * save/restore logic here.)
*/
Assert(!pstate->p_lateral_active);
! pstate->p_lateral_active = true;
/*
* Transform the raw expression.
*************** transformRangeFunction(ParseState *pstat
*** 534,543 ****
assign_expr_collations(pstate, funcexpr);
/*
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r, r->lateral, true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
--- 539,554 ----
assign_expr_collations(pstate, funcexpr);
/*
+ * Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
+ * there are any lateral cross-references in it.
+ */
+ is_lateral = r->lateral || contain_vars_of_level(funcexpr, 0);
+
+ /*
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r, is_lateral, true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 22265d7a7c88fa3e331588dbc1bbea0803155fbc..3421a559f25e2d59a7e715db28a1bff03a81af4c 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** select *, (select r from (select q1 as q
*** 3157,3163 ****
4567890123456789 | -4567890123456789 | 4567890123456789
(5 rows)
! -- lateral SRF
select count(*) from tenk1 a, lateral generate_series(1,two) g;
count
-------
--- 3157,3163 ----
4567890123456789 | -4567890123456789 | 4567890123456789
(5 rows)
! -- lateral with function in FROM
select count(*) from tenk1 a, lateral generate_series(1,two) g;
count
-------
*************** explain (costs off)
*** 3184,3189 ****
--- 3184,3200 ----
-> Function Scan on generate_series g
(4 rows)
+ -- don't need the explicit LATERAL keyword for functions
+ explain (costs off)
+ select count(*) from tenk1 a, generate_series(1,two) g;
+ QUERY PLAN
+ ------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk1 a
+ -> Function Scan on generate_series g
+ (4 rows)
+
-- lateral with UNION ALL subselect
explain (costs off)
select * from generate_series(100,200) g,
*************** select * from
*** 3578,3602 ****
(26 rows)
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, generate_series(0, f1) g;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a, generate_series(0, a.f1) g;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join generate_series(0, f1) g;
ERROR: column "f1" does not exist
! LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
--- 3589,3613 ----
(26 rows)
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a, (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join (select f1 as g) ss;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 0fe8ca4c4e927da12cfe9ae75c9479b3ed8c4c22..16782776f45222b9b9ad75c5c776993baf5554dc 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** INSERT INTO foo2 VALUES(1, 11);
*** 19,30 ****
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: invalid reference to FROM-clause entry for table "foo2"
! LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
! HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
--- 19,33 ----
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- function with implicit LATERAL
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! fooid | f2 | fooid | f2
! -------+-----+-------+-----
! 1 | 11 | 1 | 11
! 2 | 22 | 2 | 22
! 1 | 111 | 1 | 111
! (3 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 6c1e3394adca36c837bff3d20f62602fafd70f5b..6f51b8532763eb2bddde7872be2ce2874e51240b 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** explain (costs off)
*** 901,912 ****
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
! -- lateral SRF
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
-- lateral with UNION ALL subselect
explain (costs off)
--- 901,915 ----
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
! -- lateral with function in FROM
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
+ -- don't need the explicit LATERAL keyword for functions
+ explain (costs off)
+ select count(*) from tenk1 a, generate_series(1,two) g;
-- lateral with UNION ALL subselect
explain (costs off)
*************** select * from
*** 987,996 ****
lateral (select ss2.y) ss3;
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, generate_series(0, f1) g;
! select f1,g from int4_tbl a, generate_series(0, a.f1) g;
! select f1,g from int4_tbl a cross join generate_series(0, f1) g;
! select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
--- 990,999 ----
lateral (select ss2.y) ss3;
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, (select f1 as g) ss;
! select f1,g from int4_tbl a, (select a.f1 as g) ss;
! select f1,g from int4_tbl a cross join (select f1 as g) ss;
! select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 54cfc178c057c40f82e27cbd1f3a8e0e271c34b3..f1a405a5f7eb5716d66dd815b0908d8e3accc95a 100644
*** a/src/test/regress/sql/rangefuncs.sql
--- b/src/test/regress/sql/rangefuncs.sql
*************** INSERT INTO foo2 VALUES(1, 111);
*** 7,13 ****
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
-- function in subselect
--- 7,13 ----
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- function with implicit LATERAL
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
-- function in subselect
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
