[CALCITE-3198] Enhance RexSimplify to handle (x<>a or x<>b)

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.20.0
• Fix Version/s: 1.21.0
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 15/Jul/19 09:31
• Updated: 20/Aug/20 10:50
• Resolved: 08/Aug/19 06:58

Description

Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a query like this one (see RelOptRulesTest#testReduceConstantsDup):

// 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=[[]])

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:

// 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, when $0's type is NOT nullable, filter removed):
LogicalProject(DEPTNO=[$0])
  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

// plan after (expected, when $0's type IS nullable, filter simplified as IS NOT NULL):
LogicalProject(DEPTNO=[$0])
  LogicalFilter(condition=[IS NOT NULL($0)])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

Issue Links

• is related to: CALCITE-2608 Simplifying using predicates should consider not equals in case its a predicate
• relates to: CALCITE-2921 Allow NULL as argument to function calls
• links to: GitHub Pull Request #1322
• links to: GitHub Pull Request #1365
• links to: GitHub Pull Request #1368