postgres.git / summary / log / commit / refs
commit 89deca582a345b9c423bed8ebcf24b6ee81a9953
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Apr 07 16:18:38 2017 +0000
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
src/backend/optimizer/prep/prepunion.c | 12 +++++++++++-
src/test/regress/expected/union.out | 25 +++++++++++++++++++++++++
src/test/regress/sql/union.sql | 6 ++++++
3 files changed, 42 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index b5cb4de6a26..881d85e26cc 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -337,6 +337,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)
{
@@ -346,7 +356,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 4d697bada72..5c4edd1c166 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -320,6 +320,31 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
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 48e6850798e..5e0eff2ca30 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -112,6 +112,12 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
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
--
[parent: 60f11b87a234]