[ https://issues.apache.org/jira/browse/CALCITE-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mihai Budiu reassigned CALCITE-6317: ------------------------------------ Assignee: Mihai Budiu > Optimization CoreRules.PROJECT_REDUCE_EXPRESSIONS is unsound > ------------------------------------------------------------ > > Key: CALCITE-6317 > URL: https://issues.apache.org/jira/browse/CALCITE-6317 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.36.0 > Reporter: Mihai Budiu > Assignee: Mihai Budiu > Priority: Major > Labels: pull-request-available > > Here is a query taken from agg.iq: > {code:sql} > select deptno, gender, grouping_id(deptno, gender, deptno), count(*) as c > from emp > where deptno = 10 > group by rollup(gender, deptno) > {code} > The query plan initially is > {code} > 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 > {code} > After applying PROJECT_REDUCE_EXPRESSIONS the plan looks like: > {code} > 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 > {code} > 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. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)