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

Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B)

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this.

      This case is found out by materialized view test:

      materialized view: 
      select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) 
      and (replace(name,'c','b') is not null or replace(name,'n','d') is not null)
      
      sql:  
      select salary +1 as s,deptno from emps where
      replace(name,'c','b')is not null and replace(name,'a','c') is not null and salary>10
      
      The sql will not be rewritten by materialized view because when splitFilter, 
      $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and  (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) 
      and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null)
      
      can not be simplified to  
      $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null 
      
      
      materialized view:
      select empid,deptno,salary as s from emps where 
      (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') 
      and 
      (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa')
      
      sql:
      select salary  as s,deptno from emps where
      replace(name,'c','b') ='aaa' is not null 
      and replace(name,'a','c') ='aaa'
      
      This case also can not be rewritten.

      Attachments

        Issue Links

          Activity

            People

              Ziwei Liu Ziwei Liu
              Ziwei Liu Ziwei Liu
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 50m
                  1h 50m