[ https://issues.apache.org/jira/browse/CALCITE-1753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-1753: --------------------------------- Fix Version/s: 1.13.0 > 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 > Fix For: 1.13.0 > > > 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)