[CALCITE-5328] The rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN throws error when meeting the rollup's query

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 12/Oct/22 07:27
• Updated: 08/May/24 21:29

Description

The rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN aims to transform aggregate with distince agg-call to join, such as:

 

// origin sql
SELECT deptno,
       job,
       count(DISTINCT ename),
       sum(DISTINCT sal)
FROM sales.emp
GROUP BY deptno,
         job;

// transformed sql
SELECT t1.deptno,
       t2.job,
       c1,
       c2
  (SELECT deptno, job, count(ename) AS c1
   FROM
     (SELECT deptno, job, ename
      GROUP BY deptno, job, ename)
   GROUP BY deptno, job) t1
INNER JOIN
  (SELECT deptno,
          job,
          sum(sal) AS c2
   FROM
     (SELECT deptno,
             job,
             sal
      GROUP BY deptno,
               job,
               sal)
   GROUP BY deptno,
            job) t2 
ON t1.deptno IS NOT DISTINCT FROM t2.deptno AND t1.job IS NOT DISTINCTFROM t2.job;

 

But, this rule throws ex, when meeting aggregate with rollup.

• Test Case

// org.apache.calcite.test.RelOptRulesTest#test
@Test void test() {
    final String sql = "select deptno, job, count(distinct ename), sum(distinct sal)\n"
        + "from sales.emp group by rollup(deptno,job)";
    sql(sql)
        .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN)
        .check();
} 

• Error Message

type mismatch:
ref:
INTEGER
input:
INTEGER NOT NULL
java.lang.AssertionError: type mismatch:
ref:
INTEGER
input:
INTEGER NOT NULL
    at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
    at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2183)
    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:113)
    at org.apache.calcite.rel.core.Project.isValid(Project.java:245)
    at org.apache.calcite.rel.core.Project.<init>(Project.java:106)
    at org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:75)
    at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:166)
    at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:143)
    at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:199)
    at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:2066)
    at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1837)
    at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1820)
    at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1792)
    at org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule.onMatch(AggregateExpandDistinctAggregatesRule.java:265)
    at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337) 

 

Issue Links

• relates to: CALCITE-6332 Optimization CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN produces incorrect results for aggregates with groupSets