[jira] [Commented] (CALCITE-2066) RelOptUtil.splitJoinCondition() could not split condition with case after applying FilterReduceExpressionsRule

["Volodymyr Vysotskyi (JIRA)" <ji...@apache.org>]

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

Volodymyr Vysotskyi commented on CALCITE-2066:
----------------------------------------------

I tried to implement ReverseCaseShuttle which moves predicates from a CASE when {{RelOptUtil.splitJoinCondition()}} does not work, but it does not help since simplification has happened before moving predicates back and general condition left unsplittable. 
[~julianhyde], can we add a property into {{ReduceExpressionsRule}} to disable {{new CaseShuttle().mutate(expList);}}?

> RelOptUtil.splitJoinCondition() could not split condition with case after applying FilterReduceExpressionsRule
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-2066
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2066
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Volodymyr Vysotskyi
>            Assignee: Julian Hyde
>
> Query 
> {code:sql}
> select * from emp a emp b where coalesce(a.mgr, -1) = coalesce(b.mgr, -1)
> {code}
> has logical plan
> {noformat}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17])
>   LogicalFilter(condition=[=(CASE(IS NOT NULL($3), $3, -1), CASE(IS NOT NULL($12), $12, -1))])
>     LogicalJoin(condition=[true], joinType=[inner])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> After applying {{FilterReduceExpressionsRule}} condition in filter is changed to
> {noformat}
> CASE(IS NOT NULL($12), CASE(IS NOT NULL($3), =($3, $12), =(-1, $12)), CASE(IS NOT NULL($3), =($3, -1), CAST(true):BOOLEAN))
> {noformat}
> When this condition is passed into {{RelOptUtil.splitJoinCondition()}} method, {{leftJoinKeys}} and  {{rightJoinKeys}} lists are left empty and passed condition is returned.
> *Note:* {{RelOptUtil.splitJoinCondition()}} works correctly for the initial condition without applying {{FilterReduceExpressionsRule}}.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)