[
https://issues.apache.org/jira/browse/CALCITE-5345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17625111#comment-17625111
]
Alessandro Solimando commented on CALCITE-5345:
-----------------------------------------------
The decimal constant (1.0) is being cast to int (1) because the field _deptno_
is of type int, I think this is desired, we cannot alter the field type in the
top-most rel.
Can you elaborate more on the edge cases for cast? Do you have a running
example ready I can play with?
Since we know the constant value at planning time, its type and the target type
({_}i.e.{_}, the field type), maybe we can detect such cases and bail out.
Of course if this is not possible for whatever reason, it would be a slippery
path to follow, but I want to make sure there is nothing we can do to bail out
only on the problematic cases.
> 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)
