Support flattening of empty-FROM subqueries and one-row VALUES tables.

We can't handle this in the general case due to limitations of the
planner's data representations; but we can allow it in many useful cases,
by being careful to flatten only when we are pulling a single-row subquery
up into a FROM (or, equivalently, inner JOIN) node that will still have at
least one remaining relation child.  Per discussion of an example from
Kyotaro Horiguchi.

4 files changed, 69 insertions, 9 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 8c17ef41b92..57fc910133b 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3633,6 +3633,26 @@ select * from generate_series(100,200) g,
 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
+   ->  Merge Join
+         Merge Cond: (a.unique1 = b.unique2)
+         ->  Index Only Scan using tenk1_unique1 on tenk1 a
+         ->  Index Only Scan using tenk1_unique2 on tenk1 b
+(5 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 with VALUES, no flattening possible
+explain (costs off)
+  select count(*) from tenk1 a,
+    tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
                             QUERY PLAN                            
 ------------------------------------------------------------------
  Aggregate
@@ -3646,7 +3666,7 @@ explain (costs off)
 (8 rows)
 
 select count(*) from tenk1 a,
-  tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+  tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
  count 
 -------
  10000
@@ -4214,7 +4234,7 @@ select * from
     cross join
     lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
   ) on c.q2 = ss2.q1,
-  lateral (select ss2.y) ss3;
+  lateral (select ss2.y offset 0) ss3;
                                                                                   QUERY PLAN                                                                                  
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop
@@ -4296,9 +4316,9 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
 -- check processing of postponed quals (bug #9041)
 explain (verbose, costs off)
 select * from
-  (select 1 as x) x cross join (select 2 as y) y
+  (select 1 as x offset 0) x cross join (select 2 as y offset 0) y
   left join lateral (
-    select * from (select 3 as z) z where z.z = x.x
+    select * from (select 3 as z offset 0) z where z.z = x.x
   ) zz on zz.z = y.y;
                   QUERY PLAN                  
 ----------------------------------------------
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 7991e993f14..6dabe503cc0 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -2034,7 +2034,7 @@ select x from int8_tbl, extractq2(int8_tbl) f(x);
 (5 rows)
 
 create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
-  select extractq2(t)
+  select extractq2(t) offset 0
 $$ language sql immutable;
 explain (verbose, costs off)
 select x from int8_tbl, extractq2_2(int8_tbl) f(x);
@@ -2058,3 +2058,25 @@ select x from int8_tbl, extractq2_2(int8_tbl) f(x);
  -4567890123456789
 (5 rows)
 
+-- without the "offset 0", this function gets optimized quite differently
+create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
+  select extractq2(t)
+$$ language sql immutable;
+explain (verbose, costs off)
+select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+         QUERY PLAN          
+-----------------------------
+ Seq Scan on public.int8_tbl
+   Output: int8_tbl.q2
+(2 rows)
+
+select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+         x         
+-------------------
+               456
+  4567890123456789
+               123
+  4567890123456789
+ -4567890123456789
+(5 rows)
+
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index db7aefee283..06a27ea151d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1137,6 +1137,13 @@ explain (costs off)
 select count(*) from tenk1 a,
   tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
 
+-- lateral with VALUES, no flattening possible
+explain (costs off)
+  select count(*) from tenk1 a,
+    tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+select count(*) from tenk1 a,
+  tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+
 -- lateral injecting a strange outer join condition
 explain (costs off)
   select * from int8_tbl a,
@@ -1247,7 +1254,7 @@ select * from
     cross join
     lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
   ) on c.q2 = ss2.q1,
-  lateral (select ss2.y) ss3;
+  lateral (select ss2.y offset 0) ss3;
 
 -- case that breaks the old ph_may_need optimization
 explain (verbose, costs off)
@@ -1265,9 +1272,9 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
 -- check processing of postponed quals (bug #9041)
 explain (verbose, costs off)
 select * from
-  (select 1 as x) x cross join (select 2 as y) y
+  (select 1 as x offset 0) x cross join (select 2 as y offset 0) y
   left join lateral (
-    select * from (select 3 as z) z where z.z = x.x
+    select * from (select 3 as z offset 0) z where z.z = x.x
   ) zz on zz.z = y.y;
 
 -- test some error cases where LATERAL should have been used but wasn't
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 470571b0fb6..9484023f97b 100644
--- a/src/test/regress/sql/rangefuncs.sql
+++ b/src/test/regress/sql/rangefuncs.sql
@@ -621,10 +621,21 @@ select x from int8_tbl, extractq2(int8_tbl) f(x);
 select x from int8_tbl, extractq2(int8_tbl) f(x);
 
 create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
-  select extractq2(t)
+  select extractq2(t) offset 0
 $$ language sql immutable;
 
 explain (verbose, costs off)
 select x from int8_tbl, extractq2_2(int8_tbl) f(x);
 
 select x from int8_tbl, extractq2_2(int8_tbl) f(x);
+
+-- without the "offset 0", this function gets optimized quite differently
+
+create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
+  select extractq2(t)
+$$ language sql immutable;
+
+explain (verbose, costs off)
+select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+
+select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);