[
https://issues.apache.org/jira/browse/CALCITE-7711?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7711:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
>
> 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 nullable. 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)
