diff options
| author | Andres Freund <andres@anarazel.de> | 2015-07-26 15:34:29 +0200 |
|---|---|---|
| committer | Andres Freund <andres@anarazel.de> | 2015-07-26 16:53:13 +0200 |
| commit | 3500d1cc78f61927e05c0e73158b87ff24f81c09 (patch) | |
| tree | 9c17d76fbb254e9d45c835f62d601915187d3de4 /src/test | |
| parent | 65b86c1767a7dac0cc79dcfba7ba4cbd326dc03f (diff) | |
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 Chalke
Reviewed-By: Andrew Gierth
Discussion: CAM2+6=WG9omG5rFOMAYBweJxmpTaapvVp5pCeMrE6BfpCwr4Og@mail.gmail.com
Backpatch: 9.5, where grouping sets were introduced
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 62 | ||||
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 17 |
2 files changed, 79 insertions, 0 deletions
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 2e12a53d69f..bdd77f8979c 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -486,6 +486,68 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four); 9 | 3 (25 rows) +-- HAVING with GROUPING queries +select ten, grouping(ten) from onek +group by grouping sets(ten) having grouping(ten) >= 0 +order by 2,1; + ten | grouping +-----+---------- + 0 | 0 + 1 | 0 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 +(10 rows) + +select ten, grouping(ten) from onek +group by grouping sets(ten, four) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 + | 1 + | 1 + | 1 +(4 rows) + +select ten, grouping(ten) from onek +group by rollup(ten) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 +(1 row) + +select ten, grouping(ten) from onek +group by cube(ten) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 +(1 row) + +select ten, grouping(ten) from onek +group by (ten) having grouping(ten) >= 0 +order by 2,1; + ten | grouping +-----+---------- + 0 | 0 + 1 | 0 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 +(10 rows) + -- FILTER queries select ten, sum(distinct four) filter (where four::text ~ '123') from onek a group by rollup(ten); diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index eeea995f337..8eb580812a9 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -154,6 +154,23 @@ select ten, sum(distinct four) from onek a group by grouping sets((ten,four),(ten)) having exists (select 1 from onek b where sum(distinct a.four) = b.four); +-- HAVING with GROUPING queries +select ten, grouping(ten) from onek +group by grouping sets(ten) having grouping(ten) >= 0 +order by 2,1; +select ten, grouping(ten) from onek +group by grouping sets(ten, four) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by rollup(ten) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by cube(ten) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by (ten) having grouping(ten) >= 0 +order by 2,1; + -- FILTER queries select ten, sum(distinct four) filter (where four::text ~ '123') from onek a group by rollup(ten); |