[
https://issues.apache.org/jira/browse/CALCITE-1753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15967998#comment-15967998
]
Jinfeng Ni commented on CALCITE-1753:
-------------------------------------
Seems to me that the reason we could not push the expressions below right table
in left outer join is because those expressions do not have "null-if-null"
properties. For "null-if-null" expressions, it seems to be safe to push down
them below left outer join.
Whether it's performance beneficial to push down the expression for a left
outer join depends on whether left table has more rows than right table. In
case left table has more rows, push down the expression to right side would
means the expression would be evaluated fewer times, which would lead to some
performance benefits.
> 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)
