[CALCITE-3531] AggregateProjectPullUpConstantsRule should not remove deterministic function group key if the function is dynamic

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 22/Nov/19 08:57
• Updated: 28/May/21 10:09
• Resolved: 08/Jan/20 06:09

Description

Now AggregateProjectPullUpConstantsRule simplify the query:

select hiredate
from sales.emp
where sal is null and hiredate = current_timestamp
group by sal, hiredate
having count(*) > 3

from plan:

LogicalProject(HIREDATE=[$1])
  LogicalFilter(condition=[>($2, 3)])
    LogicalAggregate(group=[{0, 1}], agg#0=[COUNT()])
      LogicalProject(SAL=[$5], HIREDATE=[$4])
        LogicalFilter(condition=[AND(IS NULL($5), =($4, CURRENT_TIMESTAMP))])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])

to plan:

LogicalProject(HIREDATE=[$1])
  LogicalFilter(condition=[>($2, 3)])
    LogicalProject(SAL=[$0], HIREDATE=[CURRENT_TIMESTAMP], $f2=[$1])
      LogicalAggregate(group=[{0}], agg#0=[COUNT()])
        LogicalProject(SAL=[$5], HIREDATE=[$4])
          LogicalFilter(condition=[AND(IS NULL($5), =($4, CURRENT_TIMESTAMP))])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])

which is unsafe, because for stream sql, we need to group data by dateTime, also the result is wrong if a batch job runs across days.

Issue Links

• links to: GitHub Pull Request #1602
• links to: GitHub Pull Request #1735