[
https://issues.apache.org/jira/browse/CALCITE-1558?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1558:
---------------------------------
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:
{noformat}
// 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
{noformat}
or a more complex case:
{noformat}
// 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
{noformat}
I have fixed these cases, and will provide a patch later after more tests.
was:
In AggregateExpandDistinctAggregatesRule.convertSingletonDistinct,
if the groupKey is also used in some aggregate function,
the field mappting 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.
> Wrong field mapping of top level aggregate for cases when groupKey is used in
> aggregate function
> ------------------------------------------------------------------------------------------------
>
> Key: CALCITE-1558
> URL: https://issues.apache.org/jira/browse/CALCITE-1558
> Project: Calcite
> Issue Type: Bug
> Reporter: Zhenghua Gao
> Assignee: Julian Hyde
> Attachments: patch_1558.v1
>
>
> 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:
> {noformat}
> // 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
> {noformat}
> or a more complex case:
> {noformat}
> // 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
> {noformat}
> I have fixed these cases, and will provide a patch later after more tests.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
