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]])
      

        Activity

        Hide
        seanhychu Sean Hsuan-Yi Chu added a comment -
        Show
        seanhychu Sean Hsuan-Yi Chu added a comment - Pull request: https://github.com/apache/incubator-calcite/pull/135 Julian Hyde Can you help review?
        Hide
        julianhyde Julian Hyde added a comment -

        You have generalized pushPredicateIntoCase to match expressions that occur under ANDs and ORs but it seems to me that you could go further. Make the whole thing into a shuttle class, then it will work if CASE occurs in anywhere in any expression.

        Show
        julianhyde Julian Hyde added a comment - You have generalized pushPredicateIntoCase to match expressions that occur under ANDs and ORs but it seems to me that you could go further. Make the whole thing into a shuttle class, then it will work if CASE occurs in anywhere in any expression.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/2fa63dda (thanks for the patch, Sean Hsuan-Yi Chu !) and improved in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/d697ca16 .
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.5.0 (2015-11-10)

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.5.0 (2015-11-10)

          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:

              Development