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

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

Leonid Chistov created CALCITE-5646:
---------------------------------------

             Summary: 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


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 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)