[
https://issues.apache.org/jira/browse/CALCITE-1753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15968419#comment-15968419
]
Julian Hyde commented on CALCITE-1753:
--------------------------------------
[~jni] is correct about the "null-if-null" behavior. I call a predicate
"strong" if it is null if any of its inputs is null, and we have some utility
methods in
https://calcite.apache.org/apidocs/org/apache/calcite/plan/Strong.html.
Rather than making this change specifically in terms of CASE, could you make
this in terms of strong-ness? And rather than passing join type, maybe you
could pass an ImmutableBitSet of columns it needs to be a strong in?
In your example, {{case when t3.a3 is not null then t3.a3 else 100 end}} is not
strong in t3.a3. If t3.a3 is null, the expression will not necessarily be null.
Let's check whether it is "if", "only if" or "if and only if". I believe we
need "if", and let's prove it by considering an expression which is "if" but
not "only if": "nullif(t3.a3, t3.a4)". I believe we can safely push this down.
[~jcamachorodriguez] and [~jni], can you check my reasoning?
> 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)
