MinJi Kim created CALCITE-1753:
----------------------------------
Summary: ProjectJoinTransposeRule with preserveExprCondition
pushes projects below outer-joins
Key: CALCITE-1753
URL: https://issues.apache.org/jira/browse/CALCITE-1753
Project: Calcite
Issue Type: Bug
Components: core
Reporter: MinJi Kim
Assignee: Julian Hyde
I am pushing case statements past joins using ProjectJoinTransposeRule. For
inner joins, the current behavior seems fine, but outer joins can lead to weird
behavior, where the project is below the join and nulls can cause issues.
{code}
select
count(*), case when t3.a3 is not null then t3.a3 else 100 end
from
t1 left outer join t3 on t1.c1 = t3.c3
group by
case when t3.a3 is not null then t3.a3 else 100 end
order by
case when t3.a3 is not null then t3.a3 else 100 end
{code}
Currently, ProjectJoinTransposeRule will push the case statement below the join
as below. But, this case statement shouldn't be pushed. The query shouldn't
return null but instead as 100 for any "unmatched" join condition since it is a
left outer join with a case statement. But, the current plan would not prevent
that.
{code}
LogicalProject with case statement
LogicalJoin
LogicalTableScan(table=[t1])
LogicalTableScan(table=[t3])
{code}
{code}
LogicalProject
LogicalJoin
LogicalProject with case statement
LogicalTableScan(table=[t1])
LogicalTableScan(table=[t3])
{code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
