[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition

["Leonid Chistov (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712458#comment-17712458 ] 

Leonid Chistov edited comment on CALCITE-5646 at 4/14/23 6:51 PM:
------------------------------------------------------------------

[~julianhyde] 

I am not sure that this a bug in Strong.

I think that following thing had happened:
 * Strong class had a Javadoc comment "A predicate is strong (or null-rejecting) if it is UNKNOWN if *any* of its inputs is UNKNOWN."
 * Actual implementation of class methods works different way:
 ** {{public static boolean isStrong(RexNode e)}} method checks whether it is true, that if we set *all* inputs to UNKNOWN, we will get UNKNOWN as the expression result
 ** Other methods that accept explicit set of inputs via bitset, check whether it is true, that if we set *all* inputs *from specified set* to UNKNOWN, we will get UNKNOWN as the expression result
 * It seems that JoinDeriveIsNotNullFilterRule assumed behavior described in Javadoc, but not the real one

 


was (Author: JIRAUSER298393):
[~julianhyde] 

I am not sure that this a bug in Strong.

I think that following thing had happened:
 * Strong class had a Javadoc comment "A predicate is strong (or null-rejecting) if it is UNKNOWN if *any* of its inputs is UNKNOWN."
 * Actual implementation of class methods works different way:
 ** {\{public static boolean isStrong(RexNode e)}} method checks whether it is true, that if we set *all* inputs to UNKNOWN, we will get UNKNOWN as the expression result
 ** Other methods that accept explicit set of inputs via bitset, check whether it is true, that if we set *all* inputs from specified bit set to UNKNOWN, we will get UNKNOWN as the expression result
 * It seems that JoinDeriveIsNotNullFilterRule assumed behavior described in Javadoc, but not the real one

 

> 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: 10m
>  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)