[CALCITE-367] PushFilterPastJoinRule should strengthen join type

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.9.0-incubating
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 08/Aug/14 23:31
• Updated: 25/Aug/14 19:10
• Resolved: 11/Aug/14 19:48

Description

PushFilterPashJoinRule can already convert a filter on a join into a join. (For example, 'FROM emp JOIN dept ON TRUE WHERE emp.deptno = emp.deptno' into 'FROM emp JOIN dept ON emp.deptno = dept.deptno'.)

But if the filter is strong – i.e. it is guaranteed to return false if one of its input values is NULL – then we can upgrade the join type. For example,

FROM emp FULL JOIN dept WHERE emp.age > 30

becomes

FROM emp LEFT JOIN dept WHERE emp.age > 30

The condition is strong on emp.age, and emp.age comes from the left, so that if a null row is generated on the left the condition will fail. Therefore we can strengthen the join type to LEFT, which only generates nulls on the right.

I also want to work on the kind of queries produced by EXISTS/IN rewrite; for example, the query in RelOptRulesTest.testSemiJoinRule

    FilterRel(condition=[IS TRUE($3)])
      JoinRel(condition=[=($0, $2)], joinType=[left])
        TableAccessRel(table=[[CATALOG, SALES, DEPT]])
        AggregateRel(group=[{0}], agg#0=[MIN($1)])
          ...

should become

    JoinRel(condition=[=($0, $2) AND IS TRUE($3)], joinType=[inner])
      TableAccessRel(table=[[CATALOG, SALES, DEPT]])
      AggregateRel(group=[{0}], agg#0=[MIN($1)])
        ...