[CALCITE-6317] Incorrect constant replacement when group keys are NULL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.37.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Mihai Budiu
• Reporter: Mihai Budiu
• Created: 10/Mar/24 03:02
• Updated: 06/May/24 22:19
• Resolved: 12/Mar/24 03:25

Description

Here is a query taken from agg.iq:

select deptno, gender, grouping_id(deptno, gender, deptno), count(*) as c
from emp
where deptno = 10
group by rollup(gender, deptno) 

The query plan initially is

    LogicalProject(DEPTNO=[$1], GENDER=[$0], EXPR$2=[$2], C=[$3]), id = 72
      LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[GROUPING_ID($1, $0, $1)], C=[COUNT()]), id = 71
        LogicalProject(GENDER=[$2], DEPTNO=[$1]), id = 70
          LogicalFilter(condition=[=($1, 10)]), id = 66
            LogicalTableScan(table=[[schema, EMP]]), id = 65

After applying PROJECT_REDUCE_EXPRESSIONS the plan looks like:

    LogicalProject(DEPTNO=[CAST(10):INTEGER], GENDER=[$0], EXPR$2=[$2], C=[$3]), id = 82
      LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[GROUPING_ID($1, $0, $1)], C=[COUNT()]), id = 78
        LogicalProject(GENDER=[$2], DEPTNO=[CAST(10):INTEGER]), id = 84
          LogicalFilter(condition=[=($1, 10)]), id = 74
            LogicalTableScan(table=[[schema, EMP]]), id = 65

The problem is in the outer LogicalProject, where the value 10 has replaced DEPTNO.
However, DEPTNO can also be NULL, because of the groups in the LogicalAggregate.
The constant should not be pushed past the aggregation.

 

 

Issue Links

• links to: GitHub Pull Request #3725