Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> subquery_planner isn't transferring HAVING clauses to WHERE if that
> would cross a nontrivial GROUPING SETS. It could in theory do so by
> inspecting whether the referenced columns are in all grouping sets or
> none, but currently the planner doesn't have any reason to compute that
> intersection and it would add quite a bit of complexity to that specific
> point in the code.
Hm. I see that computing that set is not really trivial. I'd supposed
that we probably had code to do it somewhere, but if we don't, I'm
disinclined to add it for this. So that leads to the conclusion that we
should just shut off push-down in this situation, as per attached quick
hack (no test case) patch.
> In this example, pushing the condition below the aggregate would be
> wrong anyway, no?
Agreed. I hadn't thought hard enough about the semantics, but if
"hundred" goes to null in a particular grouping set, so should
"abs(hundred)".
regards, tom lane
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6da0dcd61c..763e348d52 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3182,6 +3182,15 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
* volatile qual could succeed for some SRF output rows and fail for others,
* a behavior that cannot occur if it's evaluated before SRF expansion.
*
+ * 6. If the subquery has nonempty grouping sets, we cannot push down any
+ * quals. The concern here is that a qual referencing a "constant" grouping
+ * column could get constant-folded, which would be improper because the value
+ * is potentially nullable by grouping-set expansion. This restriction could
+ * be removed if we had a parsetree representation that shows that such
+ * grouping columns are not really constant. (There are other ideas that
+ * could be used to relax this restriction, but that's the approach most
+ * likely to get taken in the future.)
+ *
* In addition, we make several checks on the subquery's output columns to see
* if it is safe to reference them in pushed-down quals. If output column k
* is found to be unsafe to reference, we set safetyInfo->unsafeColumns[k]
@@ -3226,6 +3235,10 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
if (subquery->limitOffset != NULL || subquery->limitCount != NULL)
return false;
+ /* Check point 6 */
+ if (subquery->groupClause && subquery->groupingSets)
+ return false;
+
/* Check points 3, 4, and 5 */
if (subquery->distinctClause ||
subquery->hasWindowFuncs ||
