[
https://issues.apache.org/jira/browse/CALCITE-6214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812038#comment-17812038
]
Julian Hyde commented on CALCITE-6214:
--------------------------------------
Does this change take into account GROUP BY keys? For example, if we know that
(ename, deptno) is unique (i.e. no employees in the same department have the
same name) then we know we can remove the {{DISTINCT}} from the following query:
{code}
SELECT deptno, COUNT(DISTINCT ename)
FROM emp
GROUP BY deptno
{code}
We have to be careful if there are grouping sets; we cannot remove {{DISTINCT}}
in the following similar query:
{code}
SELECT deptno, COUNT(DISTINCT ename)
FROM emp
GROUP BY ROLLUP(deptno)
{code}
> Remove DISTINCT in aggregate function if field is unique
> --------------------------------------------------------
>
> Key: CALCITE-6214
> URL: https://issues.apache.org/jira/browse/CALCITE-6214
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Jiajun Xie
> Assignee: Jiajun Xie
> Priority: Minor
> Labels: pull-request-available
>
> For the sql
> {code:java}
> select count(distinct x) cnt
> from(
> select distinct sal x from emp
> ) t {code}
> The distinct that in count can be removed.
> {code:java}
> LogicalAggregate(group=[{}], CNT=[COUNT($0)])
> LogicalAggregate(group=[{0}])
> LogicalProject(X=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> But `CoreRules#AGGREGATE_REMOVE` not support it, so there are two DISTINCT.
> {code:java}
> LogicalAggregate(group=[{}], CNT=[COUNT(DISTINCT $0)])
> LogicalAggregate(group=[{0}])
> LogicalProject(X=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
