Mihai Budiu created CALCITE-6317:
------------------------------------
Summary: 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
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)
