zzwqqq created CALCITE-7711:
-------------------------------
Summary: Add a rule to convert LEFT or RIGHT OUTER JOIN with IS
NULL to ANTI JOIN
Key: CALCITE-7711
URL: https://issues.apache.org/jira/browse/CALCITE-7711
Project: Calcite
Issue Type: Improvement
Reporter: zzwqqq
Assignee: zzwqqq
Add an optional rule for LEFT and RIGHT joins. The rule should match a
top-level IS NULL conjunct on the null-generating input. The conversion is safe
when the underlying input field is non-nullable, or when the join condition
cannot be TRUE while that field is NULL. Other filter conjuncts and the
original row type should be preserved.
For example:
{code:sql}
SELECT e.*
FROM Emp AS e
LEFT JOIN Dept AS d
ON e.deptno = d.deptno
WHERE d.deptno IS NULL;
{code}
This query is equivalent to:
{code:sql}
SELECT e.*
FROM Emp AS e
WHERE NOT EXISTS (
SELECT 1
FROM Dept AS d
WHERE e.deptno = d.deptno
);
{code}
Representing the operation as an ANTI join allows subsequent rules to produce
physical anti joins and the planner to cost those alternatives.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
