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