Skip to content

Commit 75ba14a

Browse files
vbwagnervbwagner
committed
Merge branch 'REL9_5_STABLE' into PGPRO9_5
2 parents 9ab42f3 + 87dbc72 commit 75ba14a

File tree

3 files changed

+81
-8
lines changed

3 files changed

+81
-8
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 15 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -542,13 +542,19 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
542542
* In some cases we may want to transfer a HAVING clause into WHERE. We
543543
* cannot do so if the HAVING clause contains aggregates (obviously) or
544544
* volatile functions (since a HAVING clause is supposed to be executed
545-
* only once per group). Also, it may be that the clause is so expensive
546-
* to execute that we're better off doing it only once per group, despite
547-
* the loss of selectivity. This is hard to estimate short of doing the
548-
* entire planning process twice, so we use a heuristic: clauses
549-
* containing subplans are left in HAVING. Otherwise, we move or copy the
550-
* HAVING clause into WHERE, in hopes of eliminating tuples before
551-
* aggregation instead of after.
545+
* only once per group). We also can't do this if there are any nonempty
546+
* grouping sets; moving such a clause into WHERE would potentially change
547+
* the results, if any referenced column isn't present in all the grouping
548+
* sets. (If there are only empty grouping sets, then the HAVING clause
549+
* must be degenerate as discussed below.)
550+
*
551+
* Also, it may be that the clause is so expensive to execute that we're
552+
* better off doing it only once per group, despite the loss of
553+
* selectivity. This is hard to estimate short of doing the entire
554+
* planning process twice, so we use a heuristic: clauses containing
555+
* subplans are left in HAVING. Otherwise, we move or copy the HAVING
556+
* clause into WHERE, in hopes of eliminating tuples before aggregation
557+
* instead of after.
552558
*
553559
* If the query has explicit grouping then we can simply move such a
554560
* clause into WHERE; any group that fails the clause will not be in the
@@ -568,7 +574,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
568574
{
569575
Node *havingclause = (Node *) lfirst(l);
570576

571-
if (contain_agg_clause(havingclause) ||
577+
if ((parse->groupClause && parse->groupingSets) ||
578+
contain_agg_clause(havingclause) ||
572579
contain_volatile_functions(havingclause) ||
573580
contain_subplans(havingclause))
574581
{

‎src/test/regress/expected/groupingsets.out

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -607,6 +607,60 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four);
607607
9 | 3
608608
(25 rows)
609609

610+
-- Tests around pushdown of HAVING clauses, partially testing against previous bugs
611+
select a,count(*) from gstest2 group by rollup(a) order by a;
612+
a | count
613+
---+-------
614+
1 | 8
615+
2 | 1
616+
| 9
617+
(3 rows)
618+
619+
select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
620+
a | count
621+
---+-------
622+
2 | 1
623+
| 9
624+
(2 rows)
625+
626+
explain (costs off)
627+
select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
628+
QUERY PLAN
629+
----------------------------------
630+
GroupAggregate
631+
Group Key: a
632+
Group Key: ()
633+
Filter: (a IS DISTINCT FROM 1)
634+
-> Sort
635+
Sort Key: a
636+
-> Seq Scan on gstest2
637+
(7 rows)
638+
639+
select v.c, (select count(*) from gstest2 group by () having v.c)
640+
from (values (false),(true)) v(c) order by v.c;
641+
c | count
642+
---+-------
643+
f |
644+
t | 9
645+
(2 rows)
646+
647+
explain (costs off)
648+
select v.c, (select count(*) from gstest2 group by () having v.c)
649+
from (values (false),(true)) v(c) order by v.c;
650+
QUERY PLAN
651+
-----------------------------------------------------------
652+
Sort
653+
Sort Key: "*VALUES*".column1
654+
-> Values Scan on "*VALUES*"
655+
SubPlan 1
656+
-> Aggregate
657+
Group Key: ()
658+
Filter: "*VALUES*".column1
659+
-> Result
660+
One-Time Filter: "*VALUES*".column1
661+
-> Seq Scan on gstest2
662+
(10 rows)
663+
610664
-- HAVING with GROUPING queries
611665
select ten, grouping(ten) from onek
612666
group by grouping sets(ten) having grouping(ten) >= 0

‎src/test/regress/sql/groupingsets.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,18 @@ select ten, sum(distinct four) from onek a
183183
group by grouping sets((ten,four),(ten))
184184
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
185185

186+
-- Tests around pushdown of HAVING clauses, partially testing against previous bugs
187+
select a,count(*) from gstest2 group by rollup(a) order by a;
188+
select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
189+
explain (costs off)
190+
select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
191+
192+
select v.c, (select count(*) from gstest2 group by () having v.c)
193+
from (values (false),(true)) v(c) order by v.c;
194+
explain (costs off)
195+
select v.c, (select count(*) from gstest2 group by () having v.c)
196+
from (values (false),(true)) v(c) order by v.c;
197+
186198
-- HAVING with GROUPING queries
187199
select ten, grouping(ten) from onek
188200
group by grouping sets(ten) having grouping(ten) >= 0

0 commit comments

Comments
 (0)