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