Description
A WITH query using AVG throws AssertionError: type mismatch. The query
WITH EmpAnalytics AS ( SELECT deptno, job, AVG(sal) AS avg_sal FROM Emp GROUP BY deptno, job) SELECT job, avg(avg_sal) AS avg_sal2 FROM EmpAnalytics WHERE deptno = 30 GROUP BY job
gives error
java.lang.AssertionError: type mismatch: ref: DECIMAL(19, 2) input: DECIMAL(7, 2) at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32) at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2209) at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:129) at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:61) at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:114) at org.apache.calcite.rel.core.Project.isValid(Project.java:219) at org.apache.calcite.rel.core.Project.<init>(Project.java:98) at org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:69) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:126) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:114) at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:178) at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:1645) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1417) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1389) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1378) at org.apache.calcite.rel.rules.AggregateRemoveRule.onMatch(AggregateRemoveRule.java:120)
Here is a patch that reproduces:
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index ba5ce1053..d036da3d2 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -1065,6 +1065,16 @@ Expression 'DEPTNO' is not being grouped !use scott +WITH EmpAnalytics as ( + SELECT deptno, job, AVG(sal) AS avg_sal + FROM "scott".emp + GROUP BY deptno, job) +SELECT job, AVG(avg_sal) AS avg_sal2 +FROM EmpAnalytics +WHERE deptno = 30 +GROUP BY job; +!ok + # ORDER BY expression with SELECT DISTINCT select distinct deptno, job from "scott".emp
If you run the same query from SQLLine, you get a different error, but I think they are probably related:
Error while applying rule ProjectMergeRule, args [rel#406:LogicalProject.NONE.[](input=RelSubset#301,exprs=[$1, $2, CASE(IS NOT NULL($2), 1:BIGINT, 0:BIGINT)]), rel#362:LogicalProject.NONE.[](input=RelSubset#361,exprs=[$1, $0, $2])] (state=,code=0)
Attachments
Issue Links
- is related to
-
CALCITE-3866 "numeric field overflow" when running the generated SQL in PostgreSQL
- Closed