Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
1.36.0
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.
Attachments
Issue Links
- links to