[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonid Chistov updated CALCITE-5646:
------------------------------------
Description:
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.
was:
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.
> 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
>
> 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)
