[ https://issues.apache.org/jira/browse/CALCITE-2135?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16326910#comment-16326910 ]
Julian Hyde commented on CALCITE-2135: -------------------------------------- I agree, the query is not valid, so the validator should give error. But conversely, can you check that MIN can be used provided that there is a GROUP BY, e.g. I think the following is valid: {code:sql}SELECT empno, SUM(empno) OVER (PARTITION BY deptno ORDER BY MIN(sal)) empno_sum, deptno FROM emp GROUP BY empno, deptno{code} > Calcite wrongly validates invalid query when using aggregate function inside > over clause > ---------------------------------------------------------------------------------------- > > Key: CALCITE-2135 > URL: https://issues.apache.org/jira/browse/CALCITE-2135 > Project: Calcite > Issue Type: Bug > Reporter: Volodymyr Tkach > Assignee: Julian Hyde > Priority: Major > > I wrote 2 tests with queries: > 1. Inside SqlValidatorTest class: > {noformat} > @Test public void testAggregateInsideOverWithoutGroupByFails() { > check("SELECT empno, SUM(empno) OVER (PARTITION BY deptno ORDER BY > MIN(sal)) empno_sum, deptno FROM emp "); > } > {noformat} > 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: > {noformat} > @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."); > } > {noformat} > Results with error: > {noformat} > 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) > {noformat} > If we remove `MIN` aggregate from inside `OVER` clause then 2 query succeeds. -- This message was sent by Atlassian JIRA (v7.6.3#76005)