[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714324#comment-17714324
]
Julian Hyde commented on CALCITE-5646:
--------------------------------------
You're right, I was getting confused about the details of this case.
However, my role here is to make sure that the high-level design of Calcite
goes in the right direction. You imply that "Strong ... is out of scope of this
case". That is not true. This case relates to null semantics, null semantics
are reasoned about by the Strong class, and a good fix will leave the Strong
class in a better state.
You pointed to one method in Strong that was poorly documented and said - in
essence - "this class is shit, so I'm not going to use it". Sorry, you can't do
that. The Strong class is the right place for this functionality. You need to
make it work.
I just don't have time to get into the details. I have reviewed probably a
dozen other cases today. As you've noticed, when I get into the details, I make
mistakes, like confusing IS NULL with IS NOT NULL. But I am right about the
high-level abstraction, the Strong class. As part of this fix, you need to
extend and fix that abstraction.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
> ----------------------------------------------------------------------------
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.34.0
> Reporter: Leonid Chistov
> Assignee: Leonid Chistov
> Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
