[jira] [Created] (CALCITE-1753) ProjectJoinTransposeRule with preserveExprCondition pushes projects below outer-joins

["MinJi Kim (JIRA)" <ji...@apache.org>]

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)