[
https://issues.apache.org/jira/browse/CALCITE-2135?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-2135:
---------------------------------
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:
{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.
was:
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.
> If there is an aggregate function inside an OVER clause, validator should
> treat query as an aggregate query
> -----------------------------------------------------------------------------------------------------------
>
> 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
>
> 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:
> {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)
