Skip to content

Commit 2378d79

Browse files
tglsfdctglsfdc
committed
Make LATERAL implicit for functions in FROM.
The SQL standard does not have general functions-in-FROM, but it does allow UNNEST() there (see the <collection derived table> production), and the semantics of that are defined to include lateral references. So spec compliance requires allowing lateral references within UNNEST() even without an explicit LATERAL keyword. Rather than making UNNEST() a special case, it seems best to extend this flexibility to any function-in-FROM. We'll still allow LATERAL to be written explicitly for clarity's sake, but it's now a noise word in this context. In theory this change could result in a change in behavior of existing queries, by allowing what had been an outer reference in a function-in-FROM to be captured by an earlier FROM-item at the same level. However, all pre-9.3 PG releases have a bug that causes them to match variable references to earlier FROM-items in preference to outer references (and then throw an error). So no previously-working query could contain the type of ambiguity that would risk a change of behavior. Per a suggestion from Andrew Gierth, though I didn't use his patch.
1 parent 8865fe0 commit 2378d79

File tree

7 files changed

+101
-46
lines changed

7 files changed

+101
-46
lines changed

‎doc/src/sgml/queries.sgml

Lines changed: 16 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -717,14 +717,24 @@ SELECT *
717717
</indexterm>
718718

719719
<para>
720-
Subqueries and table functions appearing in <literal>FROM</> can be
720+
Subqueries appearing in <literal>FROM</> can be
721721
preceded by the key word <literal>LATERAL</>. This allows them to
722722
reference columns provided by preceding <literal>FROM</> items.
723-
(Without <literal>LATERAL</literal>, each <literal>FROM</> item is
723+
(Without <literal>LATERAL</literal>, each subquery is
724724
evaluated independently and so cannot cross-reference any other
725725
<literal>FROM</> item.)
726+
</para>
727+
728+
<para>
729+
Table functions appearing in <literal>FROM</> can also be
730+
preceded by the key word <literal>LATERAL</>, but for functions the
731+
key word is optional; the function's arguments can contain references
732+
to columns provided by preceding <literal>FROM</> items in any case.
733+
</para>
734+
735+
<para>
726736
A <literal>LATERAL</literal> item can appear at top level in the
727-
<literal>FROM</> list, or within a <literal>JOIN</> tree; in the latter
737+
<literal>FROM</> list, or within a <literal>JOIN</> tree. In the latter
728738
case it can also refer to any items that are on the left-hand side of a
729739
<literal>JOIN</> that it is on the right-hand side of.
730740
</para>
@@ -770,7 +780,9 @@ FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
770780
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
771781
WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;
772782
</programlisting>
773-
or in several other equivalent formulations.
783+
or in several other equivalent formulations. (As already mentioned,
784+
the <literal>LATERAL</> key word is unnecessary in this example, but
785+
we use it for clarity.)
774786
</para>
775787

776788
<para>

‎doc/src/sgml/ref/select.sgml

Lines changed: 28 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -504,18 +504,28 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
504504
<varlistentry>
505505
<term><literal>LATERAL</literal></term>
506506
<listitem>
507-
<para>The <literal>LATERAL</literal> key word can precede a
508-
sub-<command>SELECT</command> or function-call <literal>FROM</>
509-
item. This allows the sub-<command>SELECT</command> or function
510-
expression to refer to columns of <literal>FROM</> items that appear
511-
before it in the <literal>FROM</> list. (Without
512-
<literal>LATERAL</literal>, each <literal>FROM</> item is evaluated
513-
independently and so cannot cross-reference any other
514-
<literal>FROM</> item.) A <literal>LATERAL</literal> item can
515-
appear at top level in the <literal>FROM</> list, or within a
516-
<literal>JOIN</> tree; in the latter case it can also refer to any
517-
items that are on the left-hand side of a <literal>JOIN</> that it is
518-
on the right-hand side of.
507+
<para>
508+
The <literal>LATERAL</literal> key word can precede a
509+
sub-<command>SELECT</command> <literal>FROM</> item. This allows the
510+
sub-<command>SELECT</command> to refer to columns of <literal>FROM</>
511+
items that appear before it in the <literal>FROM</> list. (Without
512+
<literal>LATERAL</literal>, each sub-<command>SELECT</command> is
513+
evaluated independently and so cannot cross-reference any other
514+
<literal>FROM</> item.)
515+
</para>
516+
517+
<para>
518+
<literal>LATERAL</literal> can also precede a function-call
519+
<literal>FROM</> item, but in this case it is a noise word, because
520+
the function expression can refer to earlier <literal>FROM</> items
521+
in any case.
522+
</para>
523+
524+
<para>
525+
A <literal>LATERAL</literal> item can appear at top level in the
526+
<literal>FROM</> list, or within a <literal>JOIN</> tree. In the
527+
latter case it can also refer to any items that are on the left-hand
528+
side of a <literal>JOIN</> that it is on the right-hand side of.
519529
</para>
520530

521531
<para>
@@ -1738,7 +1748,12 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
17381748
sub-<command>SELECT</command>; that is, the syntax
17391749
<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
17401750
is approximately equivalent to
1741-
<literal>FROM (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
1751+
<literal>FROM LATERAL (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
1752+
Note that <literal>LATERAL</> is considered to be implicit; this is
1753+
because the standard requires <literal>LATERAL</> semantics for an
1754+
<literal>UNNEST()</> item in <literal>FROM</>.
1755+
<productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
1756+
same as other set-returning functions.
17421757
</para>
17431758
</refsect2>
17441759

‎src/backend/parser/parse_clause.c

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -503,6 +503,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
503503
{
504504
Node *funcexpr;
505505
char *funcname;
506+
bool is_lateral;
506507
RangeTblEntry *rte;
507508

508509
/*
@@ -514,12 +515,16 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
514515
funcname = FigureColname(r->funccallnode);
515516

516517
/*
517-
* If the function is LATERAL, make lateral_only names of this level
518-
* visible to it. (LATERAL can't nest within a single pstate level, so we
519-
* don't need save/restore logic here.)
518+
* We make lateral_only names of this level visible, whether or not the
519+
* function is explicitly marked LATERAL. This is needed for SQL spec
520+
* compliance in the case of UNNEST(), and seems useful on convenience
521+
* grounds for all functions in FROM.
522+
*
523+
* (LATERAL can't nest within a single pstate level, so we don't need
524+
* save/restore logic here.)
520525
*/
521526
Assert(!pstate->p_lateral_active);
522-
pstate->p_lateral_active = r->lateral;
527+
pstate->p_lateral_active = true;
523528

524529
/*
525530
* Transform the raw expression.
@@ -533,11 +538,17 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
533538
*/
534539
assign_expr_collations(pstate, funcexpr);
535540

541+
/*
542+
* Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
543+
* there are any lateral cross-references in it.
544+
*/
545+
is_lateral = r->lateral || contain_vars_of_level(funcexpr, 0);
546+
536547
/*
537548
* OK, build an RTE for the function.
538549
*/
539550
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
540-
r, r->lateral, true);
551+
r, is_lateral, true);
541552

542553
/*
543554
* If a coldeflist was supplied, ensure it defines a legal set of names

‎src/test/regress/expected/join.out

Lines changed: 24 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -3157,7 +3157,7 @@ select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from i
31573157
4567890123456789 | -4567890123456789 | 4567890123456789
31583158
(5 rows)
31593159

3160-
-- lateral SRF
3160+
-- lateral with function in FROM
31613161
select count(*) from tenk1 a, lateral generate_series(1,two) g;
31623162
count
31633163
-------
@@ -3184,6 +3184,17 @@ explain (costs off)
31843184
-> Function Scan on generate_series g
31853185
(4 rows)
31863186

3187+
-- don't need the explicit LATERAL keyword for functions
3188+
explain (costs off)
3189+
select count(*) from tenk1 a, generate_series(1,two) g;
3190+
QUERY PLAN
3191+
------------------------------------------------
3192+
Aggregate
3193+
-> Nested Loop
3194+
-> Seq Scan on tenk1 a
3195+
-> Function Scan on generate_series g
3196+
(4 rows)
3197+
31873198
-- lateral with UNION ALL subselect
31883199
explain (costs off)
31893200
select * from generate_series(100,200) g,
@@ -3578,25 +3589,25 @@ select * from
35783589
(26 rows)
35793590

35803591
-- test some error cases where LATERAL should have been used but wasn't
3581-
select f1,g from int4_tbl a, generate_series(0, f1) g;
3592+
select f1,g from int4_tbl a, (select f1 as g) ss;
35823593
ERROR: column "f1" does not exist
3583-
LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
3584-
^
3594+
LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
3595+
^
35853596
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
3586-
select f1,g from int4_tbl a, generate_series(0, a.f1) g;
3597+
select f1,g from int4_tbl a, (select a.f1 as g) ss;
35873598
ERROR: invalid reference to FROM-clause entry for table "a"
3588-
LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
3589-
^
3599+
LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
3600+
^
35903601
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
3591-
select f1,g from int4_tbl a cross join generate_series(0, f1) g;
3602+
select f1,g from int4_tbl a cross join (select f1 as g) ss;
35923603
ERROR: column "f1" does not exist
3593-
LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
3594-
^
3604+
LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
3605+
^
35953606
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
3596-
select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
3607+
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
35973608
ERROR: invalid reference to FROM-clause entry for table "a"
3598-
LINE 1: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
3599-
^
3609+
LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
3610+
^
36003611
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
36013612
-- SQL:2008 says the left table is in scope but illegal to access here
36023613
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;

‎src/test/regress/expected/rangefuncs.out

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -19,12 +19,15 @@ INSERT INTO foo2 VALUES(1, 11);
1919
INSERT INTO foo2 VALUES(2, 22);
2020
INSERT INTO foo2 VALUES(1, 111);
2121
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
22-
-- supposed to fail with ERROR
22+
-- function with implicit LATERAL
2323
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
24-
ERROR: invalid reference to FROM-clause entry for table "foo2"
25-
LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
26-
^
27-
HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.
24+
fooid | f2 | fooid | f2
25+
-------+-----+-------+-----
26+
1 | 11 | 1 | 11
27+
2 | 22 | 2 | 22
28+
1 | 111 | 1 | 111
29+
(3 rows)
30+
2831
-- function in subselect
2932
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
3033
fooid | f2

‎src/test/regress/sql/join.sql

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -901,12 +901,15 @@ explain (costs off)
901901
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
902902
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
903903

904-
-- lateral SRF
904+
-- lateral with function in FROM
905905
select count(*) from tenk1 a, lateral generate_series(1,two) g;
906906
explain (costs off)
907907
select count(*) from tenk1 a, lateral generate_series(1,two) g;
908908
explain (costs off)
909909
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
910+
-- don't need the explicit LATERAL keyword for functions
911+
explain (costs off)
912+
select count(*) from tenk1 a, generate_series(1,two) g;
910913

911914
-- lateral with UNION ALL subselect
912915
explain (costs off)
@@ -987,10 +990,10 @@ select * from
987990
lateral (select ss2.y) ss3;
988991

989992
-- test some error cases where LATERAL should have been used but wasn't
990-
select f1,g from int4_tbl a, generate_series(0, f1) g;
991-
select f1,g from int4_tbl a, generate_series(0, a.f1) g;
992-
select f1,g from int4_tbl a cross join generate_series(0, f1) g;
993-
select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
993+
select f1,g from int4_tbl a, (select f1 as g) ss;
994+
select f1,g from int4_tbl a, (select a.f1 as g) ss;
995+
select f1,g from int4_tbl a cross join (select f1 as g) ss;
996+
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
994997
-- SQL:2008 says the left table is in scope but illegal to access here
995998
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
996999
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;

‎src/test/regress/sql/rangefuncs.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ INSERT INTO foo2 VALUES(1, 111);
77

88
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
99

10-
-- supposed to fail with ERROR
10+
-- function with implicit LATERAL
1111
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
1212

1313
-- function in subselect

0 commit comments

Comments
 (0)