[
https://issues.apache.org/jira/browse/CALCITE-7711?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7711:
----------------------------
Description:
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.
was:
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.
> 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
>
> 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)
