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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.16.0
    • None
    • 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

            julianhyde Julian Hyde
            volodymyr.tkach Volodymyr Tkach
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: