Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4609

AggregateRemoveRule throws while handling AVG

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.26.0
    • 1.27.0
    • core
    • None

    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

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            julianhyde Julian Hyde
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment