[jira] [Comment Edited] (CALCITE-1172) Add rule to flatten two Aggregate operators into one

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1172?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16788403#comment-16788403 ] 

Julian Hyde edited comment on CALCITE-1172 at 3/9/19 12:20 AM:
---------------------------------------------------------------

It should be safe to merge SUM onto COUNT. For example, {{SELECT SUM\(x) FROM (SELECT COUNT\(y) AS x FROM t GROUP BY a, b) GROUP BY a}} is equivalent to {{SELECT COUNT\(y) FROM t GROUP BY a}}.

Maybe it's not possible to apply the rule if the top query has {{GROUP BY ()}}, e.g. testAggregateMerge6. But otherwise it should be OK to merge SUM-COUNT into COUNT.

In RelOptRulesTest.java, can you format the SQL statements and add \n inside the strings? Then the SQL strings remain readable. More effort goes into maintaining these tests than into writing them, so we want to be nice to the maintainers.


was (Author: julianhyde):
It should be safe to merge SUM onto COUNT. For example, {{SELECT SUM(x) FROM (SELECT COUNT(y) AS x FROM t GROUP BY a, b) GROUP BY a}} is equivalent to {{SELECT COUNT(y) FROM t GROUP BY a}}.

Maybe it's not possible to apply the rule if the top query has {{GROUP BY ()}}, e.g. testAggregateMerge6. But otherwise it should be OK to merge SUM-COUNT into COUNT.

In RelOptRulesTest.java, can you format the SQL statements and add \n inside the strings? Then the SQL strings remain readable. More effort goes into maintaining these tests than into writing them, so we want to be nice to the maintainers.

> Add rule to flatten two Aggregate operators into one
> ----------------------------------------------------
>
>                 Key: CALCITE-1172
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1172
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 1h
>  Remaining Estimate: 0h
>
> If there is an Aggregate on top of an Aggregate, and the top aggregate's group key is a subset of the lower aggregate's group key, and the aggregates are expansions of rollups, then a rule would convert into a single aggregate.
> For example, SUM of SUM becomes SUM; SUM of COUNT becomes COUNT; MAX of MAX becomes MAX; MIN of MIN becomes MIN. AVG of AVG would not match, nor would COUNT of COUNT.
> The rule would convert
> {noformat}
> Aggregate({x}, SUM(c1), MIN(c2), MAX(c3))
>   Aggregate({x, y}, SUM(c1), MIN(c2), MAX(c3))
>     R
> {noformat}
> into
> {noformat}
> Aggregate({x}, SUM(c1), MIN(c2), MAX(c3))
>   R
> {noformat}
> The rule would not work if there was anything between the two {{Aggregate}} operators.
> The implementation would use {{SqlSplittableAggFunction}} to deduce rollups, and in fact would be practically the converse of {{AggregateExchangeTransposeRule}} proposed in CALCITE-907.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)