[
https://issues.apache.org/jira/browse/CALCITE-5345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17625321#comment-17625321
]
Stamatis Zampetakis commented on CALCITE-5345:
----------------------------------------------
We know the constant value but we don't know the values inside the column so I
am not sure how we can determine if the cast will fail or not.
Consider the following: CAST(name AS INT) = 1 where name is a column of type
VARCHAR. If at runtime you get CAST('Stamatis' AS INT) the cast will throw an
error so I am not sure if it is safe to infer that the name column is a
constant (LogicalProject[NAME=1]).
Actually now that I think of it even when we have deptno = 1 and both types are
INT, deptno can still take the NULL value (if the respective column is
nullable) so claiming that DEPTNO is constant, which we already do as part of
this rule, also seems a bit unsafe.
I haven't thought this through extensively so maybe my concerns are not sound.
> 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)
