[CALCITE-4154] Add a rule to merge a Project onto an Aggregate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.25.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 04/Aug/20 20:13
• Updated: 16/Jan/23 23:51
• Resolved: 07/Aug/20 23:12

Description

Add a rule to merge a Project onto an Aggregate. In practice, it means that aggregate functions that are not used in the Project are removed from the Aggregate.

For example,

SELECT deptno, sum_sal
FROM (
  SELECT deptno, job, SUM(sal) AS sum_sal, MIN(ename) AS min_ename
  FROM emp
  GROUP BY deptno, job)

becomes

SELECT deptno, sum_sal
FROM (
  SELECT deptno, job, SUM(sal) AS sum_sal
  FROM emp
  GROUP BY deptno, job)

Note that min_ename is removed from the Aggregate (because it is not used by the Project). job is not used, but it remains because it is a GROUP BY key.

Issue Links

• relates to: CALCITE-1172 Add rule to flatten two Aggregate operators into one
• relates to: CALCITE-3957 AggregateMergeRule should merge SUM0 into COUNT even if GROUP BY is empty
• relates to: CALCITE-5483 ProjectAggregateMergeRule throws exception if literal is non-numeric
• links to: GitHub Pull Request #2097