[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714223#comment-17714223
]
Julian Hyde commented on CALCITE-5646:
--------------------------------------
[~lchistov1987],
{quote}
I think that if we know, that setting input I (of some inner join predicate JP)
to NULL will make JP equal to either NULL or FALSE, we may conclude that we can
push down IS NOT NULL check.
{quote}
That statement is valid, but it does not apply to COALESCE. We do not know that
setting x to null in join predicate "coalesce(x, y) = 'a'" will cause the
predicate to become null.
However, we know something else. If "coalesce(x, y) = 'a'" evaluates to
UNKNOWN, then x and y must both be UNKNOWN. This "backwards reasoning" is what
Strong does.
> 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)
