[
https://issues.apache.org/jira/browse/CALCITE-7744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Weihua Zhang reassigned CALCITE-7744:
-------------------------------------
Assignee: (was: Weihua Zhang)
> ORDER BY agg(col) on a query where an alias shadows col produces an invalid
> plan containing an aggregate call in a Project
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7744
> URL: https://issues.apache.org/jira/browse/CALCITE-7744
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.43.0
> Reporter: Weihua Zhang
> Priority: Major
>
> When a SELECT list defines an alias that shadows a base column, referencing
> an aggregate over that column in ORDER BY causes SqlToRelConverter to emit an
> illegal relational plan: a LogicalProject whose projection list contains a
> RexCall wrapping a SqlAggFunction. This violates the fundamental Calcite
> invariant that aggregate functions may appear only inside Aggregate.aggCalls.
> The plan is silently accepted by rel-level validation but crashes during
> Enumerable code generation with:
> in dummy.iq
> {code:sql}
> !use scott
> SELECT max(sal) AS sal, deptno, job
> FROM emp
> GROUP BY deptno, job
> ORDER BY max(sal);
> java.sql.SQLException: Error while executing SQL "SELECT max(sal) AS sal,
> deptno, job FROM emp GROUP BY deptno, job ORDER BY max(sal)": Unable to
> implement EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}]): rowcount
> = 1.4, cumulative cost = {33.949999999999996 rows, 257.2 cpu, 0.0 io}, id = 90
> EnumerableSort(sort0=[$3], dir0=[ASC]): rowcount = 1.4, cumulative cost =
> {32.55 rows, 247.39999999999998 cpu, 0.0 io}, id = 84
> EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2], DEPTNO=[$t0], JOB=[$t1],
> EXPR$3=[$t3]): rowcount = 1.4, cumulative cost = {31.15 rows, 208.2 cpu, 0.0
> io}, id = 86
> EnumerableAggregate(group=[{0, 1}], SAL=[MAX($2)], agg#1=[MAX($3)]):
> rowcount = 1.4, cumulative cost = {29.75 rows, 197.0 cpu, 0.0 io}, id = 80
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[MAX($t5)], DEPTNO=[$t7],
> JOB=[$t2], SAL=[$t5], $f3=[$t8]): rowcount = 14.0, cumulative cost = {28.0
> rows, 197.0 cpu, 0.0 io}, id = 88
> EnumerableTableScan(table=[[scott, EMP]]): rowcount = 14.0,
> cumulative cost = {14.0 rows, 15.0 cpu, 0.0 io}, id = 47
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:317)
> at net.hydromatic.quidem.Quidem.access$2600(Quidem.java:54)
> at
> net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1778)
> at
> net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:985)
> at
> net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1522)
> at net.hydromatic.quidem.Quidem.execute(Quidem.java:204)
> at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:353)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:543)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> Caused by: java.lang.IllegalStateException: Unable to implement
> EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}]): rowcount = 1.4,
> cumulative cost = {33.949999999999996 rows, 257.2 cpu, 0.0 io}, id = 90
> EnumerableSort(sort0=[$3], dir0=[ASC]): rowcount = 1.4, cumulative cost =
> {32.55 rows, 247.39999999999998 cpu, 0.0 io}, id = 84
> EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2], DEPTNO=[$t0], JOB=[$t1],
> EXPR$3=[$t3]): rowcount = 1.4, cumulative cost = {31.15 rows, 208.2 cpu, 0.0
> io}, id = 86
> EnumerableAggregate(group=[{0, 1}], SAL=[MAX($2)], agg#1=[MAX($3)]):
> rowcount = 1.4, cumulative cost = {29.75 rows, 197.0 cpu, 0.0 io}, id = 80
> EnumerableCalc(expr#0..7=[{inputs}], expr#8=[MAX($t5)], DEPTNO=[$t7],
> JOB=[$t2], SAL=[$t5], $f3=[$t8]): rowcount = 14.0, cumulative cost = {28.0
> rows, 197.0 cpu, 0.0 io}, id = 88
> EnumerableTableScan(table=[[scott, EMP]]): rowcount = 14.0,
> cumulative cost = {14.0 rows, 15.0 cpu, 0.0 io}, id = 47
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:120)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:113)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1209)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:343)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:677)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:528)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:496)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:654)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:678)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> ... 10 more
> Suppressed: java.lang.RuntimeException: cannot translate call MAX($t5)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1632)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:110)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:208)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:1508)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:110)
> at
> org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:78)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:293)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:287)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:1295)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:233)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:193)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:110)
> at
> org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:99)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:110)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:110)
> at
> org.apache.calcite.adapter.enumerable.EnumerableSort.implement(EnumerableSort.java:74)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:110)
> at
> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:117)
> ... 21 more
> !ok
> {code}
> plan after sqltorel:
> Note the leaf LogicalProject contains $f3=[MAX($5)] where MAX is a
> SqlMinMaxAggFunction (a SqlAggFunction). This is a RexCall(SqlAggFunction)
> inside a Project — a shape that no relational operator should ever produce.
> {code:java}
> LogicalSort(sort0=[$3], dir0=[ASC])
> LogicalProject(SAL=[$2], DEPTNO=[$0], JOB=[$1], EXPR$3=[$3])
> LogicalAggregate(group=[{0,1}], SAL=[MAX($2)], agg#1=[MAX($3)])
> LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], $f3=[MAX($5)])
> LogicalTableScan(table=[[scott, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
