[CALCITE-5646] JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Leonid Chistov
• Reporter: Leonid Chistov
• Created: 14/Apr/23 09:41
• Updated: 15/Aug/24 03:27
• Resolved: 24/Apr/23 20:39

Description

Consider query

select t1.deptno from empnullables t1 inner join
empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' 

When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly transformed to query plan

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

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.

Issue Links

• Blocked: CALCITE-6528 JoinUnifyRule may alter semantics in some cases
• links to: GitHub Pull Request #3149