Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2135

If there is an aggregate function inside an OVER clause, validator should treat query as an aggregate query

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.16.0
    • Component/s: None
    • Labels:
      None

      Description

      If there is an aggregate function inside an OVER clause, validator should treat query as an aggregate query. The only expressions valid are GROUP BY expressions and other aggregate functions, and if there is no GROUP BY clause then "GROUP BY ()" is implicit.

      I wrote 2 tests with queries:

      1. Inside SqlValidatorTest class:

      @Test public void testAggregateInsideOverWithoutGroupByFails() {
            check("SELECT empno, SUM(empno) OVER (PARTITION BY deptno ORDER BY MIN(sal)) empno_sum,  deptno FROM emp ");
          }
      

      The test succeeds but hasn't, because `sal` field and others are not being grouped. Tested on postgress, mysql and hive dbs, all of them demand grouping.

      2. Inside JdbcTest class, similar query:

      @Test public void testOverWithoutGroupBy() {
          CalciteAssert.hr()
                    .query("SELECT  e.\"empid\", SUM( e.\"empid\") OVER (PARTITION BY  e.\"deptno\" ORDER BY MIN( e.\"salary\")) avg_col0,  e.\"deptno\" FROM \"hr\".\"emps\" as e")
           .returnsUnordered(
                  "doesn't matter, execution doesn't reach here.");
        }
      

      Results with error:

      java.lang.RuntimeException: exception while executing [SELECT  e."empid", SUM( e."empid") OVER (PARTITION BY  e."deptno" ORDER BY MIN( e."salary")) avg_col0,  e."deptno" FROM "hr"."emps" as e]
      
      	at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1172)
      	at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1162)
      	at org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1178)
      	at org.apache.calcite.test.JdbcTest.testOverWithoutGroupBy(JdbcTest.java:4571)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:606)
      	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
      	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
      	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
      	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
      	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
      	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
      	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
      	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
      	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
      	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
      	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
      	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
      	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
      	at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
      	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
      	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
      	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
      	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:606)
      	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
      Caused by: java.lang.RuntimeException: With materializationsEnabled=false, limit=0
      	at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:475)
      	at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1168)
      	... 30 more
      Caused by: java.sql.SQLException: error while executing SQL "SELECT  e."empid", SUM( e."empid") OVER (PARTITION BY  e."deptno" ORDER BY MIN( e."salary")) avg_col0,  e."deptno" FROM "hr"."emps" as e": cannot translate call MIN($t3)
      	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112)
      	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
      	at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:451)
      	... 31 more
      Caused by: java.lang.RuntimeException: cannot translate call MIN($t3)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:534)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:508)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:471)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:214)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:693)
      	at org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:189)
      	at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:185)
      	at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:94)
      	at org.apache.calcite.adapter.enumerable.EnumerableWindow.implement(EnumerableWindow.java:163)
      	at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:94)
      	at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:119)
      	at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:99)
      	at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
      	at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1061)
      	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:300)
      
      

      If we remove `MIN` aggregate from inside `OVER` clause then 2 query succeeds.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              volodymyr.tkach Volodymyr Tkach
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: