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

Simplify "(CASE ... END) = constant" inside AND or OR

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.5.0
    • core
    • None

    Description

      ReduceExpressionsRule.FILTER_INSTANCE does not simplify a Filter, which has “Case-When IsNull” under OR or AND.

      For example, a query like this:

      select *
      from emp
      where case when sal = 1000 then null else 1 end is null OR sal = 2000
      

      The condition in the filter can be rewritten as "sal = 1000 OR sal = 2000". However, for now, Calcite does not do the simplification. Instead, Calcite output this plan:

      LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
        LogicalFilter(condition=[OR(IS NULL(CASE(=($5, 1000), null, 1)), =($5, 2000))])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      

      Attachments

        Activity

          People

            julianhyde Julian Hyde
            seanhychu Sean Hsuan-Yi Chu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: