More fixes for planner's handling of LATERAL.

Re-allow subquery pullup for LATERAL subqueries, except when the subquery
is below an outer join and contains lateral references to relations outside
that outer join.  If we pull up in such a case, we risk introducing lateral
cross-references into outer joins' ON quals, which is something the code is
entirely unprepared to cope with right now; and I'm not sure it'll ever be
worth coping with.

Support lateral refs in VALUES (this seems to be the only additional path
type that needs such support as a consequence of re-allowing subquery
pullup).

Put in a slightly hacky fix for joinpath.c's refusal to consider
parameterized join paths even when there cannot be any unparameterized
ones.  This was causing "could not devise a query plan for the given query"
failures in queries involving more than two FROM items.

Put in an even more hacky fix for distribute_qual_to_rels() being unhappy
with join quals that contain references to rels outside their syntactic
scope; which is to say, disable that test altogether.  Need to think about
how to preserve some sort of debugging cross-check here, while not
expending more cycles than befits a debugging cross-check.

2 files changed, 130 insertions, 13 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6503dd1d2f8..6705706f02e 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2999,12 +2999,12 @@ from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
 explain (costs off)
   select unique2, x.*
   from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
-            QUERY PLAN            
-----------------------------------
+                   QUERY PLAN                    
+-------------------------------------------------
  Nested Loop
-   ->  Seq Scan on tenk1 a
    ->  Seq Scan on int4_tbl b
-         Filter: (f1 = a.unique1)
+   ->  Index Scan using tenk1_unique1 on tenk1 a
+         Index Cond: (unique1 = b.f1)
 (4 rows)
 
 select unique2, x.*
@@ -3022,7 +3022,7 @@ explain (costs off)
  Nested Loop
    ->  Seq Scan on int4_tbl x
    ->  Index Scan using tenk1_unique1 on tenk1
-         Index Cond: (x.f1 = unique1)
+         Index Cond: (unique1 = x.f1)
 (4 rows)
 
 explain (costs off)
@@ -3033,7 +3033,7 @@ explain (costs off)
  Nested Loop
    ->  Seq Scan on int4_tbl x
    ->  Index Scan using tenk1_unique1 on tenk1
-         Index Cond: (x.f1 = unique1)
+         Index Cond: (unique1 = x.f1)
 (4 rows)
 
 select unique2, x.*
@@ -3050,15 +3050,13 @@ from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 =
 explain (costs off)
   select unique2, x.*
   from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on f1 = unique1;
-                     QUERY PLAN                      
------------------------------------------------------
+                  QUERY PLAN                   
+-----------------------------------------------
  Nested Loop Left Join
    ->  Seq Scan on int4_tbl x
-   ->  Subquery Scan on ss
-         Filter: (x.f1 = ss.unique1)
-         ->  Index Scan using tenk1_unique1 on tenk1
-               Index Cond: (x.f1 = unique1)
-(6 rows)
+   ->  Index Scan using tenk1_unique1 on tenk1
+         Index Cond: (x.f1 = unique1)
+(4 rows)
 
 -- check scoping of lateral versus parent references
 -- the first of these should return int8_tbl.q2, the second int8_tbl.q1
@@ -3135,6 +3133,109 @@ select * from generate_series(100,200) g,
  123 | 4567890123456789 |              123
 (3 rows)
 
+-- lateral with VALUES
+explain (costs off)
+  select count(*) from tenk1 a,
+    tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Aggregate
+   ->  Hash Join
+         Hash Cond: ("*VALUES*".column1 = b.unique2)
+         ->  Nested Loop
+               ->  Index Only Scan using tenk1_unique1 on tenk1 a
+               ->  Values Scan on "*VALUES*"
+         ->  Hash
+               ->  Index Only Scan using tenk1_unique2 on tenk1 b
+(8 rows)
+
+select count(*) from tenk1 a,
+  tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ count 
+-------
+ 10000
+(1 row)
+
+-- lateral injecting a strange outer join condition
+explain (costs off)
+  select * from int8_tbl a,
+    int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+      on x.q2 = ss.z;
+                QUERY PLAN                
+------------------------------------------
+ Nested Loop Left Join
+   Join Filter: (x.q2 = ($0))
+   ->  Nested Loop
+         ->  Seq Scan on int8_tbl a
+         ->  Materialize
+               ->  Seq Scan on int8_tbl x
+   ->  Seq Scan on int4_tbl y
+(7 rows)
+
+select * from int8_tbl a,
+  int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+    on x.q2 = ss.z;
+        q1        |        q2         |        q1        |        q2         |        z         
+------------------+-------------------+------------------+-------------------+------------------
+              123 |               456 |              123 |               456 |                 
+              123 |               456 |              123 |  4567890123456789 |                 
+              123 |               456 | 4567890123456789 |               123 |              123
+              123 |               456 | 4567890123456789 |               123 |              123
+              123 |               456 | 4567890123456789 |               123 |              123
+              123 |               456 | 4567890123456789 |               123 |              123
+              123 |               456 | 4567890123456789 |               123 |              123
+              123 |               456 | 4567890123456789 |  4567890123456789 |                 
+              123 |               456 | 4567890123456789 | -4567890123456789 |                 
+              123 |  4567890123456789 |              123 |               456 |                 
+              123 |  4567890123456789 |              123 |  4567890123456789 |                 
+              123 |  4567890123456789 | 4567890123456789 |               123 |              123
+              123 |  4567890123456789 | 4567890123456789 |               123 |              123
+              123 |  4567890123456789 | 4567890123456789 |               123 |              123
+              123 |  4567890123456789 | 4567890123456789 |               123 |              123
+              123 |  4567890123456789 | 4567890123456789 |               123 |              123
+              123 |  4567890123456789 | 4567890123456789 |  4567890123456789 |                 
+              123 |  4567890123456789 | 4567890123456789 | -4567890123456789 |                 
+ 4567890123456789 |               123 |              123 |               456 |                 
+ 4567890123456789 |               123 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 |               123 |                 
+ 4567890123456789 |               123 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |               123 | 4567890123456789 | -4567890123456789 |                 
+ 4567890123456789 |  4567890123456789 |              123 |               456 |                 
+ 4567890123456789 |  4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |               123 |                 
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 |  4567890123456789 | 4567890123456789 | -4567890123456789 |                 
+ 4567890123456789 | -4567890123456789 |              123 |               456 |                 
+ 4567890123456789 | -4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 |              123 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |               123 |                 
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 |  4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |                 
+(57 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
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 40db5602dd4..30ea48cb926 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -885,6 +885,22 @@ select * from generate_series(100,200) g,
   lateral (select * from int8_tbl a where g = q1 union all
            select * from int8_tbl b where g = q2) ss;
 
+-- lateral with VALUES
+explain (costs off)
+  select count(*) from tenk1 a,
+    tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+select count(*) from tenk1 a,
+  tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+
+-- lateral injecting a strange outer join condition
+explain (costs off)
+  select * from int8_tbl a,
+    int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+      on x.q2 = ss.z;
+select * from int8_tbl a,
+  int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+    on x.q2 = ss.z;
+
 -- 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;