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.

4 files changed, 41 insertions, 24 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 22265d7a7c..3421a559f2 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 0fe8ca4c4e..16782776f4 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 6c1e3394ad..6f51b85327 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 54cfc178c0..f1a405a5f7 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