HAWQ-1697. fix bug of window func unsafe push down
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 689c30a..6bc069c 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -97,6 +97,8 @@
bool *differentTypes);
static bool qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual,
bool *differentTypes);
+static bool qual_contains_winref(Query *topquery, Index rti, Node *qual);
+static bool qual_is_pushdown_safe_set_operation(Query *subquery, Node *qual);
static void subquery_push_qual(Query *subquery,
RangeTblEntry *rte, Index rti, Node *qual);
static void recurse_push_qual(Node *setOp, Query *topquery,
@@ -1463,6 +1465,16 @@
return false;
/*
+ * (point 2)
+ * if we try to push quals below set operation, make
+ * sure that qual is pushable to below set operation children
+ */
+ if (NULL != subquery->setOperations
+ && !qual_is_pushdown_safe_set_operation(subquery, qual))
+ {
+ return false;
+ }
+ /*
* Examine all Vars used in clause; since it's a restriction clause, all
* such Vars must refer to subselect output columns.
*/
@@ -1561,6 +1573,72 @@
}
/*
+ * qual_contains_winref
+ *
+ * does qual include a window ref node?
+ *
+ */
+static bool qual_contains_winref(Query *topquery,
+ Index rti,
+ /* index of RTE of subquery where qual needs to be checked */
+ Node *qual)
+{
+ /*
+ * extract subquery where qual needs to be checked
+ */
+ RangeTblEntry *rte = rt_fetch(rti, topquery->rtable);
+ Query *subquery = rte->subquery;
+ bool result = false;
+
+ if (NULL != subquery && NIL != subquery->windowClause)
+ {
+ /*
+ * qual needs to be resolved first to map qual columns to the
+ * underlying set of produced columns, e.g., if we work on a setop
+ * child
+ */
+ Node *qualNew = ResolveNew(qual, rti, 0, rte, subquery->targetList,
+ CMD_SELECT, 0);
+
+ result = contain_window_functions(qualNew);
+ pfree(qualNew);
+ }
+
+ return result;
+}
+
+/*
+ * qual_is_pushdown_safe_set_operation
+ *
+ * is a particular qual safe to push down set operation?
+ *
+ */
+static bool qual_is_pushdown_safe_set_operation(Query *subquery, Node *qual)
+{
+ SetOperationStmt *setop = (SetOperationStmt *) subquery->setOperations;
+
+ /*
+ * MPP-21075
+ * for queries of the form:
+ * SELECT * from (SELECT max(i) over () as w from X Union Select 1 as w) as foo where w > 0
+ * the qual (w > 0) is not push_down_safe since it uses a window ref
+ *
+ * we check if this is the case for either left or right setop inputs
+ *
+ */
+ Index rtiLeft = ((RangeTblRef *) setop->larg)->rtindex;
+ Index rtiRight = ((RangeTblRef *) setop->rarg)->rtindex;
+
+ if (qual_contains_winref(subquery, rtiLeft, qual)
+ || qual_contains_winref(subquery, rtiRight, qual))
+ {
+ return false;
+ }
+
+ return true;
+}
+
+/*
* subquery_push_qual - push down a qual that we have determined is safe
*/
static void
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a3d67f2..2dbeceb 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -93,6 +93,7 @@
static bool contain_subplans_walker(Node *node, void *context);
static bool contain_mutable_functions_walker(Node *node, void *context);
static bool contain_volatile_functions_walker(Node *node, void *context);
+static bool contain_windowfuncs(Node *node);
static bool contain_window_functions_walker(Node *node, void *context);
static bool contain_nonstrict_functions_walker(Node *node, void *context);
static Relids find_nonnullable_rels_walker(Node *node, bool top_level);
@@ -862,10 +863,25 @@
* window functions because if there are any, they pertain to a different
* planning level.
*/
-bool
-contain_window_functions(Node *clause)
+bool contain_window_functions(Node *clause)
{
- return contain_window_functions_walker(clause, NULL);
+ return contain_windowfuncs(clause);
+}
+
+/*
+ * contain_windowfuncs -
+ * Check if an expression contains a window function call of the
+ * current query level.
+ */
+static bool contain_windowfuncs(Node *node)
+{
+ /*
+ * Must be prepared to start with a Query or a bare expression tree; if
+ * it's a Query, we don't want to increment sublevels_up.
+ */
+ return query_or_expression_tree_walker(node,
+ contain_window_functions_walker,
+ NULL, 0);
}
static bool
diff --git a/src/test/feature/planner/ans/subplan.ans b/src/test/feature/planner/ans/subplan.ans
index bb3df84..f68b8b8 100644
--- a/src/test/feature/planner/ans/subplan.ans
+++ b/src/test/feature/planner/ans/subplan.ans
@@ -351,6 +351,21 @@
{1,2,3,4,5,6,7,8,9,10}
(1 row)
+-- This one contains one subquery and window function.
+create table aa(a int);
+CREATE TABLE
+insert into aa values (1);
+INSERT 0 1
+SELECT t1.sum
+FROM ( SELECT sum(a) OVER() AS sum FROM aa
+UNION ALL
+SELECT sum(a) OVER() AS sum FROM aa) t1 WHERE t1.sum = 1;
+ sum
+-----
+ 1
+ 1
+(2 rows)
+
-- start_ignore
drop schema subplan_tests cascade;
psql:/tmp/TestSubplan_TestSubplanAll.sql:142: NOTICE: drop cascades to append only table toy
diff --git a/src/test/feature/planner/sql/subplan.sql b/src/test/feature/planner/sql/subplan.sql
index eb4619b..540affa 100644
--- a/src/test/feature/planner/sql/subplan.sql
+++ b/src/test/feature/planner/sql/subplan.sql
@@ -132,6 +132,13 @@
select * from toy; -- only for debugging
select array(select nop(i) from toy order by i);
+-- This one contains one subquery and window function.
+create table aa(a int);
+insert into aa values (1);
+SELECT t1.sum
+FROM ( SELECT sum(a) OVER() AS sum FROM aa
+UNION ALL
+SELECT sum(a) OVER() AS sum FROM aa) t1 WHERE t1.sum = 1;
-- start_ignore
drop schema subplan_tests cascade;
-- end_ignore
