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.

5 files changed, 57 insertions, 29 deletions

diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index dd78500aa93..b9655954cde 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -503,6 +503,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
 {
 	Node	   *funcexpr;
 	char	   *funcname;
+	bool		is_lateral;
 	RangeTblEntry *rte;
 
 	/*
@@ -514,12 +515,16 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
 	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.)
+	 * 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 = r->lateral;
+	pstate->p_lateral_active = true;
 
 	/*
 	 * Transform the raw expression.
@@ -534,10 +539,16 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
 	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, r->lateral, true);
+										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 22265d7a7c8..3421a559f25 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3157,7 +3157,7 @@ select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from i
  4567890123456789 | -4567890123456789 | 4567890123456789
 (5 rows)
 
--- lateral SRF
+-- lateral with function in FROM
 select count(*) from tenk1 a, lateral generate_series(1,two) g;
  count 
 -------
@@ -3184,6 +3184,17 @@ explain (costs off)
          ->  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,
@@ -3578,25 +3589,25 @@ select * from
 (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;
+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, generate_series(0, f1) g;
-                                                        ^
+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, generate_series(0, a.f1) g;
+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, generate_series(0, a.f1) g;
-                                                        ^
+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 generate_series(0, f1) g;
+select f1,g from int4_tbl a cross join (select f1 as g) ss;
 ERROR:  column "f1" does not exist
-LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
-                                                                 ^
+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 generate_series(0, a.f1) g;
+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: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
-                                                               ^
+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 0fe8ca4c4e9..16782776f45 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -19,12 +19,15 @@ INSERT INTO foo2 VALUES(1, 11);
 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
+-- function with implicit LATERAL
 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.
+ 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 6c1e3394adc..6f51b853276 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -901,12 +901,15 @@ explain (costs off)
 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
+-- 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)
@@ -987,10 +990,10 @@ select * from
   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;
+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 54cfc178c05..f1a405a5f7e 100644
--- a/src/test/regress/sql/rangefuncs.sql
+++ b/src/test/regress/sql/rangefuncs.sql
@@ -7,7 +7,7 @@ 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
+-- function with implicit LATERAL
 select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
 
 -- function in subselect