[jira] [Commented] (CALCITE-5345) UnionPullUpConstantsRule could also pull up constants requiring a cast

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17624359#comment-17624359 ] 

Stamatis Zampetakis commented on CALCITE-5345:
----------------------------------------------

In the example plan after rule application it seems that the constant has changed type from 1.0 (DECIMAL) to 1 (INT). Is this desired/intentional?

Moreover the application of the CAST operator in certain contexts can raise an error or in some systems (e.g., Hive) change the column value to null. It seems a bit dangerous to imply that the column is constant.

> 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)