Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita6897ef

Browse files
committed
Fix overeager pushdown of HAVING clauses when grouping sets are used.
In61444bf we started to allow HAVING clauses to be fully pushed downinto WHERE, even when grouping sets are in use. That turns out not towork correctly, because grouping sets can "produce" NULLs, meaning thatfiltering in WHERE and HAVING can have different results, even when noaggregates or volatile functions are involved.Instead only allow pushdown of empty grouping sets.It'd be nice to do better, but the exact mechanics of deciding whichcases are safe are still being debated. It's important to give correctresults till we find a good solution, and such a solution might not beappropriate for backpatching anyway.Bug: #13863Reported-By: 'wrb'Diagnosed-By: Dean RasheedAuthor: Andrew GierthReviewed-By: Dean Rasheed and Andres FreundDiscussion: 20160113183558.12989.56904@wrigleys.postgresql.orgBackpatch: 9.5, where grouping sets were introduced
1 parentc477e84 commita6897ef

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
@@ -653,13 +653,19 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
653653
* In some cases we may want to transfer a HAVING clause into WHERE. We
654654
* cannot do so if the HAVING clause contains aggregates (obviously) or
655655
* volatile functions (since a HAVING clause is supposed to be executed
656-
* only once per group). Also, it may be that the clause is so expensive
657-
* to execute that we're better off doing it only once per group, despite
658-
* the loss of selectivity. This is hard to estimate short of doing the
659-
* entire planning process twice, so we use a heuristic: clauses
660-
* containing subplans are left in HAVING. Otherwise, we move or copy the
661-
* HAVING clause into WHERE, in hopes of eliminating tuples before
662-
* aggregation instead of after.
656+
* only once per group). We also can't do this if there are any nonempty
657+
* grouping sets; moving such a clause into WHERE would potentially change
658+
* the results, if any referenced column isn't present in all the grouping
659+
* sets. (If there are only empty grouping sets, then the HAVING clause
660+
* must be degenerate as discussed below.)
661+
*
662+
* Also, it may be that the clause is so expensive to execute that we're
663+
* better off doing it only once per group, despite the loss of
664+
* selectivity. This is hard to estimate short of doing the entire
665+
* planning process twice, so we use a heuristic: clauses containing
666+
* subplans are left in HAVING. Otherwise, we move or copy the HAVING
667+
* clause into WHERE, in hopes of eliminating tuples before aggregation
668+
* instead of after.
663669
*
664670
* If the query has explicit grouping then we can simply move such a
665671
* clause into WHERE; any group that fails the clause will not be in the
@@ -679,7 +685,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
679685
{
680686
Node*havingclause= (Node*)lfirst(l);
681687

682-
if (contain_agg_clause(havingclause)||
688+
if ((parse->groupClause&&parse->groupingSets)||
689+
contain_agg_clause(havingclause)||
683690
contain_volatile_functions(havingclause)||
684691
contain_subplans(havingclause))
685692
{

‎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 (select1from onek bwheresum(distincta.four)=b.four);
185185

186+
-- Tests around pushdown of HAVING clauses, partially testing against previous bugs
187+
select a,count(*)from gstest2group by rollup(a)order by a;
188+
select a,count(*)from gstest2group by rollup(a)having a is distinctfrom1order by a;
189+
explain (costs off)
190+
select a,count(*)from gstest2group by rollup(a)having a is distinctfrom1order by a;
191+
192+
selectv.c, (selectcount(*)from gstest2group by ()havingv.c)
193+
from (values (false),(true)) v(c)order byv.c;
194+
explain (costs off)
195+
selectv.c, (selectcount(*)from gstest2group by ()havingv.c)
196+
from (values (false),(true)) v(c)order byv.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)
[8]ページ先頭
©2009-2025 Movatter.jp