[jira] [Comment Edited] (CALCITE-3198) ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)'

["Ruben Quesada Lopez (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16885045#comment-16885045 ] 

Ruben Quesada Lopez edited comment on CALCITE-3198 at 7/15/19 10:24 AM:
------------------------------------------------------------------------

Same result is obtained with the following test, which is maybe more straightforward (and by solving this, the one in the description might be also solved as a side effect):
{code}
// query:
select d.deptno from dept d where d.deptno<>7 or d.deptno=8

// plan before:
LogicalProject(DEPTNO=[$0])
  LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

// plan after (actual, unchanged):
LogicalProject(DEPTNO=[$0])
  LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

// plan after (expected, filter removed):
LogicalProject(DEPTNO=[$0])
  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}


was (Author: rubenql):
Same result is obtained with the following test:
{code}
// query:
select d.deptno from dept d where d.deptno<>7 or d.deptno=8

// plan before:
LogicalProject(DEPTNO=[$0])
  LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

// plan after (actual, unchanged):
LogicalProject(DEPTNO=[$0])
  LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

// plan after (expected, filter removed):
LogicalProject(DEPTNO=[$0])
  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}

> ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)'
> ------------------------------------------------------------------------
>
>                 Key: CALCITE-3198
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3198
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.20.0
>            Reporter: Ruben Quesada Lopez
>            Priority: Minor
>
> Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a query like this one (see RelOptRulesTest#testReduceConstantsDup):
> {code}
> // query:
> select d.deptno from dept d where d.deptno=7 and d.deptno=8
> // plan before:
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[AND(=($0, 7), =($0, 8))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after:
> LogicalProject(DEPTNO=[$0])
>   LogicalValues(tuples=[[]])
> {code}
> As we can see, since the filter is 'always false', the LogicalTableScan+LogicalFilter are correctly replaced by an empty LogicalValues.
> However, the same filter with a NOT expression, is not correctly simplified:
> {code}
> // query:
> select d.deptno from dept d where not(d.deptno=7 and d.deptno=8)
> // plan before:
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after (actual, NOT distributivity for AND):
> LogicalProject(DEPTNO=[$0])
>   LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
>     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> // plan after (expected, filter removed):
> LogicalProject(DEPTNO=[$0])
>   LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> Since the filter is the negated of an 'always false filter' (the one used in the previous query), it is therefore an 'always true filter', so the expected behavior is that the LogicalFilter should be removed, and it is not.



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)