Scott Reynolds created CALCITE-3508:
---------------------------------------
Summary: Strengthen JOINs when Filter enforces the nullable
side(s) to non-nullable
Key: CALCITE-3508
URL: https://issues.apache.org/jira/browse/CALCITE-3508
Project: Calcite
Issue Type: Improvement
Reporter: Scott Reynolds
Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner Join
when the nullable side contains a filter IS_NOT_NULL. Below is the code.
{code:java}
for (RexNode filter : aboveFilters) {
if (joinType.generatesNullsOnLeft()
&& Strong.isNotTrue(filter, leftBitmap)) {
joinType = joinType.cancelNullsOnLeft();
}
if (joinType.generatesNullsOnRight()
&& Strong.isNotTrue(filter, rightBitmap)) {
joinType = joinType.cancelNullsOnRight();
}
if (!joinType.isOuterJoin()) {
break;
}
}
{code}
This code looks at the filter to determine if it is always true, then it can
alter the join type by removing the null on that side.
We can see this in the following test RelOptRules#testStrengthenJoinType, which
executes the following SQL that transforms from a LEFT OUTER JOIN to an INNER
JOIN
{code:sql}
select *
from dept left join emp on dept.deptno = emp.deptno
where emp.deptno is not null and emp.sal > 100
{code}
This ticket is about broadening the application of this rule to a sql like the
following:
{code:sql}
select *
from dept left join emp on dept.deptno = emp.deptno
where emp.sal > 100
{code}
This originally came up on the mailing list:
[https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/browser]
and in that thread it was pointed out that there are filters that prevent this
from being applied:
{code:sql}
SELECT b.title
FROM Book b
LEFT JOIN Author a ON b.author = a.id
WHERE a.name <> 'Victor'
{code}
This means we need to ensure we that the OUTER JOIN doesn't contain – for lack
of a different term – negation filters. If there is a negation – like NOT_EQUAL
– the JOIN cannot be strengthened.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
