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)
