Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1753

Push expressions into null-generating side of a join if they are "strong" (null-preserving)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.13.0
    • Component/s: core
    • Labels:
      None

      Description

      ProjectJoinTransposeRule with preserveExprCondition pushes projects below outer-joins.

      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.

      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
      

      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.

      LogicalProject with case statement
         LogicalJoin
            LogicalTableScan(table=[t1])
            LogicalTableScan(table=[t3])
      
      LogicalProject 
         LogicalJoin
            LogicalProject with case statement
              LogicalTableScan(table=[t1])
            LogicalTableScan(table=[t3])
      

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              minjikim MinJi Kim
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: