Fix planner error (or assert trap) with nested set operations.

As reported by Sean Johnston in bug #14614, since 9.6 the planner can fail
due to trying to look up the referent of a Var with varno 0.  This happens
because we generate such Vars in generate_append_tlist, for lack of any
better way to describe the output of a SetOp node.  In typical situations
nothing really cares about that, but given nested set-operation queries
we will call estimate_num_groups on the output of the subquery, and that
wants to know what a Var actually refers to.  That logic used to look at
subquery->targetList, but in commit 3fc6e2d7f I'd switched it to look at
subroot->processed_tlist, ie the actual output of the subquery plan not the
parser's idea of the result.  It seemed like a good idea at the time :-(.
As a band-aid fix, change it back.

Really we ought to have an honest way of naming the outputs of SetOp steps,
which suggests that it'd be a good idea for the parser to emit an RTE
corresponding to each one.  But that's a task for another day, and it
certainly wouldn't yield a back-patchable fix.

Report: https://postgr.es/m/20170407115808.25934.51866@wrigleys.postgresql.org

diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index b7147832e0f82db304309ff0e90f996904716158..7d0279ec3ef1e11d33a325419c514272e5500d83 100644 (file)
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -338,6 +338,16 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
         * Estimate number of groups if caller wants it.  If the subquery used
         * grouping or aggregation, its output is probably mostly unique
         * anyway; otherwise do statistical estimation.
+        *
+        * XXX you don't really want to know about this: we do the estimation
+        * using the subquery's original targetlist expressions, not the
+        * subroot->processed_tlist which might seem more appropriate.  The
+        * reason is that if the subquery is itself a setop, it may return a
+        * processed_tlist containing "varno 0" Vars generated by
+        * generate_append_tlist, and those would confuse estimate_num_groups
+        * mightily.  We ought to get rid of the "varno 0" hack, but that
+        * requires a redesign of the parsetree representation of setops, so
+        * that there can be an RTE corresponding to each setop's output.
         */
        if (pNumGroups)
        {
@@ -347,7 +357,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
                *pNumGroups = subpath->rows;
            else
                *pNumGroups = estimate_num_groups(subroot,
-                           get_tlist_exprs(subroot->processed_tlist, false),
+                               get_tlist_exprs(subquery->targetList, false),
                                                  subpath->rows,
                                                  NULL);
        }

diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 016571bd4a98a846e9db2e3ee00cf7e675b8bd03..042ee8a060f97d138423e37a2301f7e619d58b7b 100644 (file)
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -319,6 +319,31 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
 
 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
 ERROR:  FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+ ?column? | ?column? | ?column? 
+----------+----------+----------
+        4 |        5 |        6
+(1 row)
+
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+ ?column? | ?column? | ?column? 
+----------+----------+----------
+        4 |        5 |        6
+(1 row)
+
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+ ?column? | ?column? | ?column? 
+----------+----------+----------
+        1 |        2 |        3
+(1 row)
+
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+ ?column? | ?column? | ?column? 
+----------+----------+----------
+        1 |        2 |        3
+(1 row)
+
 --
 -- Mixed types
 --

diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 9ff1551e5de4087e2ee46e44d45a227fc41c1f2d..7baacba6d671248d1dc5bbab0afdd402fa5ab514 100644 (file)
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -111,6 +111,12 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
 
 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
 
+-- nested cases
+(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
+(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
+
 --
 -- Mixed types
 --