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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.5.0
    • Component/s: core
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: