Weihua Zhang created CALCITE-7744:
-------------------------------------
Summary: 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
Assignee: Weihua Zhang
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)
