[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)

["Ziwei Liu (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ziwei Liu updated CALCITE-4910:
-------------------------------
    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:
{code}
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 
  
{code}

  was:
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:
{code}
materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from \"emps\" where (replace(\"name\",'\\2','a') is not null or replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or replace(\"name\",'(.*)(.)$','d') is not null)
query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
            replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
{code}


> Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
> ---------------------------------------------------------------------------
>
>                 Key: CALCITE-4910
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4910
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Ziwei Liu
>            Assignee: Ziwei Liu
>            Priority: Major
>
> 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:
> {code}
> 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 
>   
> {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)