[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov edited comment on CALCITE-5646 at 4/19/23 9:11 PM:
------------------------------------------------------------------
[~zabetak]
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.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
{*}UPDATE{*}: Having said this, I cannot come up with join condition that turns
to FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
was (Author: JIRAUSER298393):
[~zabetak]
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.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
UPDATE: Having said this, I cannot come up with join condition that turns to
FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
> 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)
