Move targetlist SRF handling from expression evaluation to new executor node.

Evaluation of set returning functions (SRFs_ in the targetlist (like SELECT
generate_series(1,5)) so far was done in the expression evaluation (i.e.
ExecEvalExpr()) and projection (i.e. ExecProject/ExecTargetList) code.

This meant that most executor nodes performing projection, and most
expression evaluation functions, had to deal with the possibility that an
evaluated expression could return a set of return values.

That's bad because it leads to repeated code in a lot of places. It also,
and that's my (Andres's) motivation, made it a lot harder to implement a
more efficient way of doing expression evaluation.

To fix this, introduce a new executor node (ProjectSet) that can evaluate
targetlists containing one or more SRFs. To avoid the complexity of the old
way of handling nested expressions returning sets (e.g. having to pass up
ExprDoneCond, and dealing with arguments to functions returning sets etc.),
those SRFs can only be at the top level of the node's targetlist.  The
planner makes sure (via split_pathtarget_at_srfs()) that SRF evaluation is
only necessary in ProjectSet nodes and that SRFs are only present at the
top level of the node's targetlist. If there are nested SRFs the planner
creates multiple stacked ProjectSet nodes.  The ProjectSet nodes always get
input from an underlying node.

We also discussed and prototyped evaluating targetlist SRFs using ROWS
FROM(), but that turned out to be more complicated than we'd hoped.

While moving SRF evaluation to ProjectSet would allow to retain the old
"least common multiple" behavior when multiple SRFs are present in one
targetlist (i.e.  continue returning rows until all SRFs are at the end of
their input at the same time), we decided to instead only return rows till
all SRFs are exhausted, returning NULL for already exhausted ones.  We
deemed the previous behavior to be too confusing, unexpected and actually
not particularly useful.

As a side effect, the previously prohibited case of multiple set returning
arguments to a function, is now allowed. Not because it's particularly
desirable, but because it ends up working and there seems to be no argument
for adding code to prohibit it.

Currently the behavior for COALESCE and CASE containing SRFs has changed,
returning multiple rows from the expression, even when the SRF containing
"arm" of the expression is not evaluated. That's because the SRFs are
evaluated in a separate ProjectSet node.  As that's quite confusing, we're
likely to instead prohibit SRFs in those places.  But that's still being
discussed, and the code would reside in places not touched here, so that's
a task for later.

There's a lot of, now superfluous, code dealing with set return expressions
around. But as the changes to get rid of those are verbose largely boring,
it seems better for readability to keep the cleanup as a separate commit.

Author: Tom Lane and Andres Freund
Discussion: https://postgr.es/m/20160822214023.aaxz5l4igypowyri@alap3.anarazel.de

7 files changed, 71 insertions, 49 deletions

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index fa1f5e7879..0ff80620cc 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -822,8 +822,9 @@ explain (costs off)
      ->  Limit
            ->  Index Only Scan Backward using tenk1_unique2 on tenk1
                  Index Cond: (unique2 IS NOT NULL)
-   ->  Result
-(7 rows)
+   ->  ProjectSet
+         ->  Result
+(8 rows)
 
 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
  max  | g 
diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
index 9c3eecfc3b..65c8c44a9a 100644
--- a/src/test/regress/expected/limit.out
+++ b/src/test/regress/expected/limit.out
@@ -208,13 +208,15 @@ select currval('testseq');
 explain (verbose, costs off)
 select unique1, unique2, generate_series(1,10)
   from tenk1 order by unique2 limit 7;
-                        QUERY PLAN                        
-----------------------------------------------------------
+                                                                         QUERY PLAN                                                                          
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit
    Output: unique1, unique2, (generate_series(1, 10))
-   ->  Index Scan using tenk1_unique2 on public.tenk1
+   ->  ProjectSet
          Output: unique1, unique2, generate_series(1, 10)
-(4 rows)
+         ->  Index Scan using tenk1_unique2 on public.tenk1
+               Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
+(6 rows)
 
 select unique1, unique2, generate_series(1,10)
   from tenk1 order by unique2 limit 7;
@@ -236,7 +238,7 @@ select unique1, unique2, generate_series(1,10)
 --------------------------------------------------------------------
  Limit
    Output: unique1, unique2, (generate_series(1, 10)), tenthous
-   ->  Result
+   ->  ProjectSet
          Output: unique1, unique2, generate_series(1, 10), tenthous
          ->  Sort
                Output: unique1, unique2, tenthous
@@ -263,9 +265,10 @@ explain (verbose, costs off)
 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
                                               QUERY PLAN                                              
 ------------------------------------------------------------------------------------------------------
- Result
+ ProjectSet
    Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
-(2 rows)
+   ->  Result
+(3 rows)
 
 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
  s1 | s2 
@@ -283,9 +286,10 @@ order by s2 desc;
  Sort
    Output: (generate_series(0, 2)), (generate_series(((random() * '0.1'::double precision))::integer, 2))
    Sort Key: (generate_series(((random() * '0.1'::double precision))::integer, 2)) DESC
-   ->  Result
+   ->  ProjectSet
          Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
-(5 rows)
+         ->  Result
+(6 rows)
 
 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
 order by s2 desc;
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 3ae918a63c..1b8f7b69d1 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1320,18 +1320,20 @@ fetch backward all in c1;
 rollback;
 begin;
 explain (costs off) declare c2 cursor for select generate_series(1,3) as g;
- QUERY PLAN 
-------------
- Result
-(1 row)
-
-explain (costs off) declare c2 scroll cursor for select generate_series(1,3) as g;
   QUERY PLAN  
 --------------
- Materialize
+ ProjectSet
    ->  Result
 (2 rows)
 
+explain (costs off) declare c2 scroll cursor for select generate_series(1,3) as g;
+     QUERY PLAN     
+--------------------
+ Materialize
+   ->  ProjectSet
+         ->  Result
+(3 rows)
+
 declare c2 scroll cursor for select generate_series(1,3) as g;
 fetch all in c2;
  g 
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 275b66204a..56481de5c3 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -1995,12 +1995,10 @@ SELECT *,
         END)
 FROM
   (VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);
- id |       str        |      lower       
-----+------------------+------------------
-  1 |                  | 
-  2 | 0000000049404    | 49404
-  3 | FROM 10000000876 | from 10000000876
-(3 rows)
+ id |      str      | lower 
+----+---------------+-------
+  2 | 0000000049404 | 49404
+(1 row)
 
 -- check whole-row-Var handling in nested lateral functions (bug #11703)
 create function extractq2(t int8_tbl) returns int8 as $$
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index eda319d24b..abd3217e86 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -807,24 +807,28 @@ select * from int4_tbl where
 explain (verbose, costs off)
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
-                           QUERY PLAN                           
-----------------------------------------------------------------
- Hash Semi Join
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop Semi Join
    Output: o.f1
-   Hash Cond: (o.f1 = "ANY_subquery".f1)
+   Join Filter: (o.f1 = "ANY_subquery".f1)
    ->  Seq Scan on public.int4_tbl o
          Output: o.f1
-   ->  Hash
+   ->  Materialize
          Output: "ANY_subquery".f1, "ANY_subquery".g
          ->  Subquery Scan on "ANY_subquery"
                Output: "ANY_subquery".f1, "ANY_subquery".g
                Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
-               ->  HashAggregate
-                     Output: i.f1, (generate_series(1, 2) / 10)
-                     Group Key: i.f1
-                     ->  Seq Scan on public.int4_tbl i
-                           Output: i.f1
-(15 rows)
+               ->  Result
+                     Output: i.f1, ((generate_series(1, 2)) / 10)
+                     ->  ProjectSet
+                           Output: i.f1, generate_series(1, 2)
+                           ->  HashAggregate
+                                 Output: i.f1
+                                 Group Key: i.f1
+                                 ->  Seq Scan on public.int4_tbl i
+                                       Output: i.f1
+(19 rows)
 
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
@@ -899,9 +903,10 @@ select * from
  Subquery Scan on ss
    Output: x, u
    Filter: tattle(ss.x, 8)
-   ->  Result
+   ->  ProjectSet
          Output: 9, unnest('{1,2,3,11,12,13}'::integer[])
-(5 rows)
+         ->  Result
+(6 rows)
 
 select * from
   (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
@@ -930,10 +935,11 @@ select * from
   where tattle(x, 8);
                      QUERY PLAN                     
 ----------------------------------------------------
- Result
+ ProjectSet
    Output: 9, unnest('{1,2,3,11,12,13}'::integer[])
-   One-Time Filter: tattle(9, 8)
-(3 rows)
+   ->  Result
+         One-Time Filter: tattle(9, 8)
+(4 rows)
 
 select * from
   (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
@@ -959,9 +965,10 @@ select * from
  Subquery Scan on ss
    Output: x, u
    Filter: tattle(ss.x, ss.u)
-   ->  Result
+   ->  ProjectSet
          Output: 9, unnest('{1,2,3,11,12,13}'::integer[])
-(5 rows)
+         ->  Result
+(6 rows)
 
 select * from
   (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index 7bb6d17fcb..8c47f0f668 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -25,8 +25,8 @@ SELECT generate_series(1, 2), generate_series(1,4);
 -----------------+-----------------
                1 |               1
                2 |               2
-               1 |               3
-               2 |               4
+                 |               3
+                 |               4
 (4 rows)
 
 -- srf, with SRF argument
@@ -43,7 +43,16 @@ SELECT generate_series(1, generate_series(1, 3));
 
 -- srf, with two SRF arguments
 SELECT generate_series(generate_series(1,3), generate_series(2, 4));
-ERROR:  functions and operators can take at most one set argument
+ generate_series 
+-----------------
+               1
+               2
+               2
+               3
+               3
+               4
+(6 rows)
+
 CREATE TABLE few(id int, dataa text, datab text);
 INSERT INTO few VALUES(1, 'a', 'foo'),(2, 'a', 'bar'),(3, 'b', 'bar');
 -- SRF output order of sorting is maintained, if SRF is not referenced
@@ -118,15 +127,15 @@ SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few
 SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]);
  dataa | count | min | max | unnest 
 -------+-------+-----+-----+--------
- a     |     2 |   1 |   1 |      1
  a     |     1 |   1 |   1 |      3
+ a     |     2 |   1 |   1 |      1
 (2 rows)
 
 SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, 5;
  dataa | count | min | max | unnest 
 -------+-------+-----+-----+--------
- a     |     2 |   1 |   1 |      1
  a     |     1 |   1 |   1 |      3
+ a     |     2 |   1 |   1 |      1
 (2 rows)
 
 -- check HAVING works when GROUP BY does [not] reference SRF output
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 67f5fc4361..d22db69c7d 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -636,9 +636,10 @@ ORDER BY x;
          ->  HashAggregate
                Group Key: (1), (generate_series(1, 10))
                ->  Append
+                     ->  ProjectSet
+                           ->  Result
                      ->  Result
-                     ->  Result
-(9 rows)
+(10 rows)
 
 SELECT * FROM
   (SELECT 1 AS t, generate_series(1,10) AS x