Alessandro Solimando created CALCITE-5345:
---------------------------------------------
Summary: UnionPullUpConstantsRule could also pull up constants
requiring a cast
Key: CALCITE-5345
URL: https://issues.apache.org/jira/browse/CALCITE-5345
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.32.0
Reporter: Alessandro Solimando
Assignee: Alessandro Solimando
Consider the following SQL query:
{code:java}
select deptno, ename from emp where deptno = 1.0
union all
select deptno, ename from emp where deptno = 1.0
{code}
The associated plan is as follows:
{code:java}
LogicalUnion(all=[true])
LogicalProject(DEPTNO=[$1], ENAME=[$0])
LogicalFilter(condition=[=(CAST($1):DECIMAL(11, 1) NOT NULL, 1.0)])
LogicalProject(ENAME=[$1], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$1], ENAME=[$0])
LogicalFilter(condition=[=(CAST($1):DECIMAL(11, 1) NOT NULL, 1.0)])
LogicalProject(ENAME=[$1], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
Note that since _deptno_ is of type {_}int{_}, a cast is needed in the filter
({_}i.e., LogicalFilter(condition=[=(CAST($1):DECIMAL(11, 1) NOT NULL,
1.0)]){_}).
{_}UnionPullUpConstantsRule{_}, as currently written, processes only
(pulled-up) predicates of the form "{_}=($i, $literal){_}", while now that
CALCITE-5337 is present, it could also process "{_}=(CAST($i, $type),
$literal){_}", because the need of a cast is recognized and the cast added in
the projection when the constant is pulled up.
The aforementioned query would be optimized in this way:
{code:java}
LogicalProject(DEPTNO=[1], ENAME=[$0])
LogicalUnion(all=[true])
LogicalProject(ENAME=[$0])
LogicalFilter(condition=[=(CAST($1):DECIMAL(11, 1) NOT NULL, 1.0)])
LogicalProject(ENAME=[$1], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalFilter(condition=[=(CAST($1):DECIMAL(11, 1) NOT NULL, 1.0)])
LogicalProject(ENAME=[$1], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
Without this improvement, the plan would not change after applying
{_}UnionPullUpConstantsRule{_}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
