[jira] [Commented] (CALCITE-1753) ProjectJoinTransposeRule with preserveExprCondition pushes projects below outer-joins

["MinJi Kim (JIRA)" <ji...@apache.org>]

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

MinJi Kim commented on CALCITE-1753:
------------------------------------

I debated between adding another preserveExprCondition to PushProjector that would apply to outer join's nullable side.  I couldn't think of a case where that would be useful (at least, use cases that I could think of, I wouldn't want to push any expressions to the nullable side of an outer join).  Let me know if you think it would be.  I will update the pull request.

https://github.com/apache/calcite/pull/425

> ProjectJoinTransposeRule with preserveExprCondition pushes projects below outer-joins
> -------------------------------------------------------------------------------------
>
>                 Key: CALCITE-1753
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1753
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: MinJi Kim
>            Assignee: Julian Hyde
>
> I am pushing case statements past joins using ProjectJoinTransposeRule. For inner joins, the current behavior seems fine, but outer joins can lead to weird behavior, where the project is below the join and nulls can cause issues. 
> {code}
> select 
> 	count(*), case when t3.a3 is not null then t3.a3 else 100 end
> from 
> 	t1 left outer join t3 on t1.c1 = t3.c3
> group by
>         case when t3.a3 is not null then t3.a3 else 100 end
> order by
>         case when t3.a3 is not null then t3.a3 else 100 end
> {code}
> Currently, ProjectJoinTransposeRule will push the case statement below the join as below.  But, this case statement shouldn't be pushed. The query shouldn't return null but instead as 100 for any "unmatched" join condition since it is a left outer join with a case statement.  But, the current plan would not prevent that.
>   
> {code}
> LogicalProject with case statement
>    LogicalJoin
>       LogicalTableScan(table=[t1])
>       LogicalTableScan(table=[t3])
> {code}
> {code}
> LogicalProject 
>    LogicalJoin
>       LogicalProject with case statement
>         LogicalTableScan(table=[t1])
>       LogicalTableScan(table=[t3])
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)