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

["Julian Hyde (Jira)" <ji...@apache.org>]

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

Julian Hyde commented on CALCITE-5646:
--------------------------------------

It seems that the bug is in Strong. Can you add to the unit test for Strong?

Commit messages should not start with “fix”. 

There are other functions that don’t follow the usual pattern for null-in-means-null-out. For example NVL, NULLIF, TRY_CAST, SAFE_CAST. Can you quickly review the function list in the SQL reference and make sure they are covered in the Strong unit test. 

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