[
https://issues.apache.org/jira/browse/CALCITE-5345?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alessandro Solimando updated CALCITE-5345:
------------------------------------------
Description:
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 (if needed).
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{_}.
was:
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{_}.
> 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
> Priority: Major
>
> 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 (if needed).
> 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)
