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)