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

Commite6d8cb7

Browse files
committed
Recognize GROUPING() as a aggregate expression.
Previously GROUPING() was not recognized as a aggregate expression,erroneously allowing the planner to move it from HAVING to WHERE.Author: Jeevan ChalkeReviewed-By: Andrew GierthDiscussion: CAM2+6=WG9omG5rFOMAYBweJxmpTaapvVp5pCeMrE6BfpCwr4Og@mail.gmail.comBackpatch: 9.5, where grouping sets were introduced
1 parent144666f commite6d8cb7

File tree

3 files changed

+85
-1
lines changed

3 files changed

+85
-1
lines changed

‎src/backend/optimizer/util/clauses.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -390,7 +390,7 @@ make_ands_implicit(Expr *clause)
390390

391391
/*
392392
* contain_agg_clause
393-
* Recursively search for Aggref nodes within a clause.
393+
* Recursively search for Aggref/GroupingFunc nodes within a clause.
394394
*
395395
* Returns true if any aggregate found.
396396
*
@@ -417,6 +417,11 @@ contain_agg_clause_walker(Node *node, void *context)
417417
Assert(((Aggref*)node)->agglevelsup==0);
418418
return true;/* abort the tree traversal and return true */
419419
}
420+
if (IsA(node,GroupingFunc))
421+
{
422+
Assert(((GroupingFunc*)node)->agglevelsup==0);
423+
return true;/* abort the tree traversal and return true */
424+
}
420425
Assert(!IsA(node,SubLink));
421426
returnexpression_tree_walker(node,contain_agg_clause_walker,context);
422427
}

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

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -486,6 +486,68 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four);
486486
9 | 3
487487
(25 rows)
488488

489+
-- HAVING with GROUPING queries
490+
select ten, grouping(ten) from onek
491+
group by grouping sets(ten) having grouping(ten) >= 0
492+
order by 2,1;
493+
ten | grouping
494+
-----+----------
495+
0 | 0
496+
1 | 0
497+
2 | 0
498+
3 | 0
499+
4 | 0
500+
5 | 0
501+
6 | 0
502+
7 | 0
503+
8 | 0
504+
9 | 0
505+
(10 rows)
506+
507+
select ten, grouping(ten) from onek
508+
group by grouping sets(ten, four) having grouping(ten) > 0
509+
order by 2,1;
510+
ten | grouping
511+
-----+----------
512+
| 1
513+
| 1
514+
| 1
515+
| 1
516+
(4 rows)
517+
518+
select ten, grouping(ten) from onek
519+
group by rollup(ten) having grouping(ten) > 0
520+
order by 2,1;
521+
ten | grouping
522+
-----+----------
523+
| 1
524+
(1 row)
525+
526+
select ten, grouping(ten) from onek
527+
group by cube(ten) having grouping(ten) > 0
528+
order by 2,1;
529+
ten | grouping
530+
-----+----------
531+
| 1
532+
(1 row)
533+
534+
select ten, grouping(ten) from onek
535+
group by (ten) having grouping(ten) >= 0
536+
order by 2,1;
537+
ten | grouping
538+
-----+----------
539+
0 | 0
540+
1 | 0
541+
2 | 0
542+
3 | 0
543+
4 | 0
544+
5 | 0
545+
6 | 0
546+
7 | 0
547+
8 | 0
548+
9 | 0
549+
(10 rows)
550+
489551
-- FILTER queries
490552
select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
491553
group by rollup(ten);

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -154,6 +154,23 @@ select ten, sum(distinct four) from onek a
154154
group by grouping sets((ten,four),(ten))
155155
having exists (select1from onek bwheresum(distincta.four)=b.four);
156156

157+
-- HAVING with GROUPING queries
158+
select ten, grouping(ten)from onek
159+
group by grouping sets(ten)having grouping(ten)>=0
160+
order by2,1;
161+
select ten, grouping(ten)from onek
162+
group by grouping sets(ten, four)having grouping(ten)>0
163+
order by2,1;
164+
select ten, grouping(ten)from onek
165+
group by rollup(ten)having grouping(ten)>0
166+
order by2,1;
167+
select ten, grouping(ten)from onek
168+
group by cube(ten)having grouping(ten)>0
169+
order by2,1;
170+
select ten, grouping(ten)from onek
171+
group by (ten)having grouping(ten)>=0
172+
order by2,1;
173+
157174
-- FILTER queries
158175
select ten,sum(distinct four) filter (where four::text ~'123')from onek a
159176
group by rollup(ten);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp