[CALCITE-2078] Aggregate functions in OVER clause (Liao Xintao) Close apache/calcite#578
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c7d9b3d5 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c7d9b3d5 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c7d9b3d5 Branch: refs/heads/master Commit: c7d9b3d561243c3cb97595f4fe9a0ccf05ce85ac Parents: d116649 Author: chiyou <[email protected]> Authored: Tue Dec 5 19:57:19 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Tue Dec 5 20:40:53 2017 -0800 ---------------------------------------------------------------------- .../calcite/sql2rel/SqlToRelConverter.java | 11 ++++++++ .../apache/calcite/test/RelOptRulesTest.java | 17 ++++++++++++ .../apache/calcite/test/SqlValidatorTest.java | 11 ++++++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 28 ++++++++++++++++++++ core/src/test/resources/sql/winagg.iq | 23 ++++++++++++++++ 5 files changed, 90 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c7d9b3d5/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index 0672dbe..ede2daf 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -4902,7 +4902,18 @@ public class SqlToRelConverter { // operator). However, do not ignore nested window aggregates. if (call.getOperator().getKind() == SqlKind.OVER) { // Track aggregate nesting levels only within an OVER operator. + List<SqlNode> operandList = call.getOperandList(); + assert operandList.size() == 2; + + // Ignore the top level window aggregates and ranking functions + // positioned as the first operand of a OVER operator inOver = true; + operandList.get(0).accept(this); + + // Normal translation for the second operand of a OVER operator + inOver = false; + operandList.get(1).accept(this); + return null; } // Do not translate the top level window aggregate. Only do so for http://git-wip-us.apache.org/repos/asf/calcite/blob/c7d9b3d5/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 3f1158f..7e1d1ec 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -2780,6 +2780,23 @@ public class RelOptRulesTest extends RelOptTestBase { checkPlanning(program, sql); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2078";>[CALCITE-2078] + * Aggregate functions in OVER clause</a>. */ + @Test public void testWindowFunctionOnAggregations() { + final HepProgram program = HepProgram.builder() + .addRuleInstance(ProjectToWindowRule.PROJECT) + .build(); + final String sql = "SELECT\n" + + " min(empno),\n" + + " sum(sal),\n" + + " sum(sum(sal))\n" + + " over (partition by min(empno) order by sum(sal))\n" + + "from emp\n" + + "group by deptno"; + checkPlanning(program, sql); + } + @Test public void testPushAggregateThroughJoin1() throws Exception { final HepProgram preProgram = new HepProgramBuilder() .addRuleInstance(AggregateProjectMergeRule.INSTANCE) http://git-wip-us.apache.org/repos/asf/calcite/blob/c7d9b3d5/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index c8b9d46..f62b0aa 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -3952,6 +3952,17 @@ public class SqlValidatorTest extends SqlValidatorTestCase { .fails("ORDER BY expression should not contain OVER clause"); } + @Test public void testAggregateFunctionInOver() { + final String sql = "select sum(deptno) over (order by count(empno))\n" + + "from emp\n" + + "group by deptno"; + winSql(sql).ok(); + final String sql2 = "select sum(^empno^) over (order by count(empno))\n" + + "from emp\n" + + "group by deptno"; + winSql(sql2).fails("Expression 'EMPNO' is not being grouped"); + } + @Test public void testWindowFunctions() { // SQL 03 Section 6.10 http://git-wip-us.apache.org/repos/asf/calcite/blob/c7d9b3d5/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index b1c4852..8061eac 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -2849,6 +2849,34 @@ LogicalProject(EXPR$0=[CAST(/($0, $1)):INTEGER NOT NULL]) ]]> </Resource> </TestCase> + <TestCase name="testWindowFunctionOnAggregations"> + <Resource name="sql"> + <![CDATA[SELECT + min(empno), + sum(sal), + sum(sum(sal)) + over (partition by min(empno) order by sum(sal)) +from emp +group by deptno]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EXPR$0=[$1], EXPR$1=[$2], EXPR$2=[SUM($2) OVER (PARTITION BY $1 ORDER BY $2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)]) + LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)], EXPR$1=[SUM($2)]) + LogicalProject(DEPTNO=[$7], EMPNO=[$0], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EXPR$0=[$1], EXPR$1=[$2], $2=[$3]) + LogicalWindow(window#0=[window(partition {1} order by [2] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($2)])]) + LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)], EXPR$1=[SUM($2)]) + LogicalProject(DEPTNO=[$7], EMPNO=[$0], SAL=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testPushSumConstantThroughUnion"> <Resource name="sql"> <