[CALCITE-6214] Remove DISTINCT in aggregate function if field is unique

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Jiajun Xie
• Reporter: Jiajun Xie
• Created: 21/Jan/24 08:01
• Updated: 06/May/24 22:19
• Resolved: 12/Feb/24 07:10

Description

For the sql

select count(distinct x) cnt
from(
   select distinct sal x from emp
) t  

The distinct that in count can be removed.

LogicalAggregate(group=[{}], CNT=[COUNT($0)])
  LogicalAggregate(group=[{0}])
    LogicalProject(X=[$5])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]]) 

But `CoreRules#AGGREGATE_REMOVE` not support it, so there are two DISTINCT.

LogicalAggregate(group=[{}], CNT=[COUNT(DISTINCT $0)])
  LogicalAggregate(group=[{0}])
    LogicalProject(X=[$5])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]]) 

Issue Links

• duplicates: CALCITE-6201 Merge Aggregate node if count distinct column has been deduplicated
• links to: GitHub Pull Request #3641