[jira] [Created] (OPTIQ-367) PushFilterPastJoinRule should strengthen join type

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created OPTIQ-367:
---------------------------------

             Summary: PushFilterPastJoinRule should strengthen join type
                 Key: OPTIQ-367
                 URL: https://issues.apache.org/jira/browse/OPTIQ-367
             Project: Optiq
          Issue Type: Bug
            Reporter: Julian Hyde
            Assignee: Julian Hyde


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,

{code}
FROM emp FULL JOIN dept WHERE emp.age > 30
{code}

becomes

{code}
FROM emp LEFT JOIN dept WHERE emp.age > 30
{code}

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

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

should become

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



--
This message was sent by Atlassian JIRA
(v6.2#6252)