[jira] [Created] (CALCITE-4609) WITH query using AVG throws AssertionError: type mismatch

["Julian Hyde (Jira)" <ji...@apache.org>]

Julian Hyde created CALCITE-4609:
------------------------------------

             Summary: WITH query using AVG throws AssertionError: type mismatch
                 Key: CALCITE-4609
                 URL: https://issues.apache.org/jira/browse/CALCITE-4609
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde


The query
{code:sql}
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{code}
gives error
{noformat}
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)
{noformat}
Here is a patch that reproduces:
{noformat}
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
{noformat}
If you run the same query from SQLLine, you get a different error, but I think they are probably related:
{noformat}
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)
{noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)