Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> One thing that I find curious is that if I alter this example to use
Tom> grouping sets, say

Tom> regression=# explain select * from (select hundred, ten, abs(hundred) a, count(*) c from tenk1 group by grouping sets (1,2)) ss where a = 42;
Tom> QUERY PLAN
Tom> -----------------------------------------------------------------
Tom> HashAggregate (cost=495.00..546.65 rows=2 width=20)
Tom> Hash Key: tenk1.hundred
Tom> Hash Key: tenk1.ten
Tom> Filter: (abs(tenk1.hundred) = 42)
Tom> -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=8)
Tom> (5 rows)

Tom> i.e. it's not seeing the abs() condition as pushable below the
Tom> aggregation. I'm not quite sure if that's a necessary restriction
Tom> or a missed optimization.

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. (Without grouping sets, a HAVING clause is movable to
WHERE if it's non-volatile and has no aggregations, since that implies
it must evaluate to the same value for each row in any group.)

In this example, pushing the condition below the aggregate would be
wrong anyway, no?

--
Andrew (irc:RhodiumToad)