[
https://issues.apache.org/jira/browse/CALCITE-4609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17346587#comment-17346587
]
Julian Hyde edited comment on CALCITE-4609 at 5/18/21, 5:48 AM:
----------------------------------------------------------------
The cause is that {{AggregateRemoveRule}} sees an AVG (of type {{DECIMAL(19,
2)}}) on top of an {{AVG}} (of type {{DECIMAL(7, 2)}}) and notices that the
{{GROUP BY}} columns are already unique. It replaces {{AVG\(x)}} with {{x}};
the replacement should be {{CAST(x AS DECIMAL(19, 2))}}, in order to preserve
types.
({{AggregateRemoveRule}} is really working with SUM calls, because {{AVG}} has
been decomposed into {{SUM}} and {{COUNT}}, but the above description is close
enough to the truth.)
was (Author: julianhyde):
The cause is that {{AggregateRemoveRule}} sees an AVG (of type {{DECIMAL(19,
2)}}) on top of an {{AVG}} (of type {{DECIMAL(7, 2)}}) and notices that the
{{GROUP BY}} columns are already unique. It replaces {{AVG(x)}} with {{x}}; the
replacement should be {{CAST(x AS DECIMAL(19, 2))}}, in order to preserve types.
({{AggregateRemoveRule}} is really working with SUM calls, because {{AVG}} has
been decomposed into {{SUM}} and {{COUNT}}, but the above description is close
enough to the truth.)
> AggregateRemoveRule throws while handling AVG
> ---------------------------------------------
>
> Key: CALCITE-4609
> URL: https://issues.apache.org/jira/browse/CALCITE-4609
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> A {{WITH}} query using AVG throws {{AssertionError: type mismatch}}. 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)
