[CALCITE-5675] Infer predicates for anti-join

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 25/Apr/23 14:08
• Updated: 27/Jul/23 10:22
• Resolved: 26/Apr/23 09:52

Description

Enhance RelMdPredicates to be able to infer predicates for anti-joins.

Consider the following plans with an anti join between EMP and DEPT tables.
PulledUpPredicates

LogicalJoin(condition=[=($7, $8)], joinType=[anti])
  LogicalFilter(condition=[=($1, 'Victor')])
    LogicalTableScan(table=[[scott, EMP]])
  LogicalFilter(condition=[=($1, 'CSD')])
    LogicalTableScan(table=[[scott, DEPT]])

We can infer that the >($1, 'Victor') predicate holds on the result of the join.

RightInferredPredicates

LogicalJoin(condition=[=($7, $8)], joinType=[anti])
  LogicalFilter(condition=[>($7, 10)])
    LogicalTableScan(table=[[scott, EMP]])
  LogicalTableScan(table=[[scott, DEPT]])

We can infer that the >($0, 10) predicate holds on the right relation (DEPT).

Issue Links

• blocks: HIVE-25953 Drop HiveRelMdPredicates::getPredicates(Join...) to use that of RelMdPredicates
• links to: GitHub Pull Request #3176