[CALCITE-1558] AggregateExpandDistinctAggregatesRule gets field mapping wrong if groupKey is used in aggregate function

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.12.0
• Assignee: Julian Hyde
• Reporter: Zhenghua Gao
• Created: 31/Dec/16 09:35
• Updated: 24/Mar/17 03:20
• Resolved: 06/Jan/17 20:56

Description

In AggregateExpandDistinctAggregatesRule.convertSingletonDistinct,
if the groupKey is also used in some aggregate function,
the field mapping of top level aggregate would be messy.
Bad cases are:

        // Equivalent SQL:
        //   SELECT deptno, COUNT(deptno), SUM(DISTINCT sal)
        //   FROM emp
        //   GROUP BY deptno

        //   SELECT deptno, SUM(cnt), SUM(sal)
        //   FROM
        //     SELECT deptno, COUNT(deptno) AS cnt, sal
        //     FROM emp
        //     GROUP BY deptno, sal
        //   GROUP BY deptno

or a more complex case:

        // Equivalent SQL:
        //   SELECT deptno, SUM(deptno), SUM(DISTINCT sal), MAX(deptno), MAX(comm)
        //   FROM emp
        //   GROUP BY deptno

        //   SELECT deptno, SUM(sumOfInnerComm), SUM(sal), MAX(maxOfInnerDeptno), MAX(maxOfInnerComm)
        //   FROM
        //     SELECT deptno, sal, SUM(deptno) as sumOfInnerDeptno, MAX(deptno) as maxOfInnerDeptno, MAX(comm) AS maxOfInnerComm
        //     FROM emp
        //     GROUP BY deptno, sal
        //   GROUP BY deptno

I have fixed these cases, and will provide a patch later after more tests.