This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new caa051d22e [CALCITE-7711] Add a rule to convert LEFT or RIGHT OUTER
JOIN with IS NULL to ANTI JOIN
caa051d22e is described below
commit caa051d22eca673dcd1671441e6bf2e0d9bcd718
Author: zzwqqq <[email protected]>
AuthorDate: Thu Aug 13 10:24:57 2026 +0800
[CALCITE-7711] Add a rule to convert LEFT or RIGHT OUTER JOIN with IS NULL
to ANTI JOIN
---
.../org/apache/calcite/rel/rules/CoreRules.java | 5 +
.../calcite/rel/rules/OuterJoinToAntiJoinRule.java | 222 ++++++++++++++++++
.../calcite/test/OuterJoinToAntiJoinRuleTest.java | 129 +++++++++++
.../calcite/test/OuterJoinToAntiJoinRuleTest.xml | 247 +++++++++++++++++++++
core/src/test/resources/sql/planner.iq | 43 ++++
5 files changed, 646 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index f827843cc6..53fe7c71fd 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -753,6 +753,11 @@ private CoreRules() {}
public static final SemiJoinRule.JoinToSemiJoinRule JOIN_TO_SEMI_JOIN =
SemiJoinRule.JoinToSemiJoinRule.JoinToSemiJoinRuleConfig.DEFAULT.toRule();
+ /** Rule that converts an outer join followed by {@code IS NULL} on its
+ * null-generating side to an anti join. */
+ public static final OuterJoinToAntiJoinRule OUTER_JOIN_TO_ANTI_JOIN =
+ OuterJoinToAntiJoinRule.Config.DEFAULT.toRule();
+
/** Rule that pushes a {@link Join}
* past a non-distinct {@link Union} as its left input. */
public static final JoinUnionTransposeRule JOIN_LEFT_UNION_TRANSPOSE =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/OuterJoinToAntiJoinRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/OuterJoinToAntiJoinRule.java
new file mode 100644
index 0000000000..2b00563370
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/OuterJoinToAntiJoinRule.java
@@ -0,0 +1,222 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.rel.rules;
+
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.plan.Strong;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.logical.LogicalFilter;
+import org.apache.calcite.rel.logical.LogicalJoin;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.ImmutableBitSet;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+import org.immutables.value.Value;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Planner rule that converts an outer join followed by {@code IS NULL}
+ * on its null-generating side to an anti join.
+ *
+ * <p>For example, the query
+ *
+ * <pre>{@code
+ * SELECT e.empno, d.name
+ * FROM Emp AS e
+ * LEFT JOIN Dept AS d ON e.deptno = d.deptno
+ * WHERE d.deptno IS NULL AND e.empno > 10
+ * }</pre>
+ *
+ * <p>has the following plan:
+ *
+ * <pre>{@code
+ * LogicalProject(EMPNO=[$0], NAME=[$10])
+ * LogicalFilter(condition=[AND(IS NULL($9), >($0, 10))])
+ * LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ * }</pre>
+ *
+ * <p>The rule converts it to:
+ *
+ * <pre>{@code
+ * LogicalProject(EMPNO=[$0], NAME=[$10])
+ * LogicalFilter(condition=[>($0, 10)])
+ * LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
+ * HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7],
+ * SLACKER=[$8], DEPTNO0=[null:INTEGER], NAME=[null:VARCHAR(10)])
+ * LogicalJoin(condition=[=($7, $9)], joinType=[anti])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ * }</pre>
+ *
+ * <p>The {@code IS NULL} predicate must be a top-level conjunct over a field
+ * from the null-generating input. A field that is non-nullable in that input
+ * is safe. For a nullable field, the join condition must not be TRUE when its
+ * value is NULL.
+ */
[email protected]
+public class OuterJoinToAntiJoinRule
+ extends RelRule<OuterJoinToAntiJoinRule.Config>
+ implements TransformationRule {
+
+ /** Creates an OuterJoinToAntiJoinRule. */
+ protected OuterJoinToAntiJoinRule(Config config) {
+ super(config);
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Filter filter = call.rel(0);
+ final Join join = call.rel(1);
+
+ // Field indexes below assume that the join has no system-field prefix.
+ if (!join.getSystemFieldList().isEmpty()) {
+ return;
+ }
+ // Rewriting may change the number and order of condition evaluations.
+ if (!RexUtil.isDeterministic(filter.getCondition())
+ || !RexUtil.isDeterministic(join.getCondition())) {
+ return;
+ }
+
+ final boolean leftJoin = join.getJoinType() == JoinRelType.LEFT;
+ // Correlated RIGHT joins are not supported because converting them
requires
+ // swapping the inputs and remapping correlation references.
+ if (!leftJoin && !join.getVariablesSet().isEmpty()) {
+ return;
+ }
+ // Only top-level conjuncts can independently prove that a row is
unmatched.
+ final List<RexNode> remainingConditions =
+ new ArrayList<>(RelOptUtil.conjunctions(filter.getCondition()));
+ final RexNode nullCondition =
+ findSafeNullCondition(remainingConditions, join, leftJoin);
+ if (nullCondition == null) {
+ return;
+ }
+ remainingConditions.remove(nullCondition);
+
+ final RelNode newLeft = leftJoin ? join.getLeft() : join.getRight();
+ final RelNode newRight = leftJoin ? join.getRight() : join.getLeft();
+ final RexNode condition = leftJoin
+ ? join.getCondition()
+ : JoinCommuteRule.swapJoinCond(join.getCondition(), join,
+ join.getCluster().getRexBuilder());
+ final RelBuilder builder = call.builder()
+ .push(newLeft)
+ .push(newRight)
+ .join(JoinRelType.ANTI, condition, join.getVariablesSet())
+ .hints(join.getHints());
+
+ // An anti join projects only its left input. Its rows are unmatched, so
every
+ // field of the null-generating input is NULL. Reinsert typed NULLs to
restore
+ // the outer join's row type.
+ final int leftCount = join.getLeft().getRowType().getFieldCount();
+ final int nullOffset = leftJoin ? leftCount : 0;
+ final List<RexNode> projects = new ArrayList<>(builder.fields());
+ insertNulls(projects, join.getRowType(), nullOffset,
+ newRight.getRowType().getFieldCount(), builder);
+
+ builder.project(projects, join.getRowType().getFieldNames())
+ .filter(filter.getVariablesSet(), remainingConditions)
+ .convert(filter.getRowType(), false);
+ call.transformTo(builder.build());
+ }
+
+ /** Returns an {@code IS NULL} condition on a null-generating input field
+ * that is non-nullable, or for which the join condition cannot be TRUE when
+ * the field is NULL; returns null if there is no such condition. */
+ private static @Nullable RexNode findSafeNullCondition(
+ List<RexNode> conditions, Join join, boolean leftJoin) {
+ final int leftCount = join.getLeft().getRowType().getFieldCount();
+ for (RexNode condition : conditions) {
+ if (!(condition instanceof RexCall)
+ || !condition.isA(SqlKind.IS_NULL)) {
+ continue;
+ }
+ final RexNode operand = ((RexCall) condition).getOperands().get(0);
+ if (!(operand instanceof RexInputRef)) {
+ continue;
+ }
+ final int index = ((RexInputRef) operand).getIndex();
+ final boolean inputOnLeft = index < leftCount;
+ if (inputOnLeft == leftJoin) {
+ continue;
+ }
+ final int inputIndex = inputOnLeft ? index : index - leftCount;
+ final RelNode input = inputOnLeft ? join.getLeft() : join.getRight();
+ final RelDataType type = input.getRowType()
+ .getFieldList().get(inputIndex).getType();
+ // If the input field is nullable, IS NULL may also be true for a matched
+ // row. It proves the row is unmatched only if the field is non-nullable,
+ // or, for a nullable field, the join condition cannot be TRUE when the
+ // field is NULL.
+ if (!type.isNullable()
+ || Strong.isNotTrue(join.getCondition(), ImmutableBitSet.of(index)))
{
+ return condition;
+ }
+ }
+ return null;
+ }
+
+ /** Inserts typed NULL expressions for fields in the original row type. */
+ private static void insertNulls(List<RexNode> projects, RelDataType rowType,
+ int offset, int count, RelBuilder builder) {
+ final List<RexNode> nulls = new ArrayList<>(count);
+ for (int i = 0; i < count; i++) {
+ final RelDataType type =
+ rowType.getFieldList().get(offset + i).getType();
+ nulls.add(builder.getRexBuilder().makeNullLiteral(type));
+ }
+ projects.addAll(offset, nulls);
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableOuterJoinToAntiJoinRule.Config.of()
+ .withOperandFor(LogicalFilter.class, LogicalJoin.class);
+
+ @Override default OuterJoinToAntiJoinRule toRule() {
+ return new OuterJoinToAntiJoinRule(this);
+ }
+
+ /** Defines an operand tree for the given classes. */
+ default Config withOperandFor(Class<? extends Filter> filterClass,
+ Class<? extends Join> joinClass) {
+ return withOperandSupplier(b ->
+ b.operand(filterClass).oneInput(b2 ->
+ b2.operand(joinClass)
+ .predicate(join -> join.getJoinType() == JoinRelType.LEFT
+ || join.getJoinType() == JoinRelType.RIGHT)
+ .anyInputs()))
+ .as(Config.class);
+ }
+ }
+}
diff --git
a/core/src/test/java/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.java
b/core/src/test/java/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.java
new file mode 100644
index 0000000000..64db040386
--- /dev/null
+++
b/core/src/test/java/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.java
@@ -0,0 +1,129 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.test;
+
+import org.apache.calcite.rel.rules.CoreRules;
+import org.apache.calcite.rel.rules.OuterJoinToAntiJoinRule;
+
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Tests for {@link OuterJoinToAntiJoinRule}.
+ *
+ * <p><a
href="https://issues.apache.org/jira/browse/CALCITE-7711";>[CALCITE-7711]
+ * Add a rule to convert LEFT or RIGHT OUTER JOIN with IS NULL to ANTI
JOIN</a>.
+ */
+class OuterJoinToAntiJoinRuleTest {
+
+ private static RelOptFixture fixture() {
+ return RelOptFixture.DEFAULT.withDiffRepos(
+ DiffRepository.lookup(OuterJoinToAntiJoinRuleTest.class));
+ }
+
+ private static RelOptFixture sql(String sql) {
+ return fixture().sql(sql)
+ .withRule(CoreRules.OUTER_JOIN_TO_ANTI_JOIN);
+ }
+
+ @Test void testLeftJoin() {
+ final String sql = "select e.empno, d.name\n"
+ + "from emp e left join dept d on e.deptno = d.deptno\n"
+ + "where d.deptno is null and e.empno > 10";
+ sql(sql).check();
+ }
+
+ @Test void testNullableJoinKey() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join deptnullables d on e.deptno = d.deptno\n"
+ + "where d.deptno is null";
+ sql(sql).check();
+ }
+
+ @Test void testRightJoin() {
+ final String sql = "select e.ename, d.name\n"
+ + "from emp e right join dept d on e.deptno = d.deptno\n"
+ + "where e.empno is null";
+ sql(sql).check();
+ }
+
+ @Test void testCorrelatedLeftJoin() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join dept d\n"
+ + "on e.deptno = d.deptno and exists (\n"
+ + " select 1 from dept d2 where d2.name = d.name)\n"
+ + "where d.deptno is null";
+ sql(sql).check();
+ }
+
+ @Test void testCorrelatedRightJoin() {
+ final String sql = "select d.deptno\n"
+ + "from emp e right join dept d\n"
+ + "on e.deptno = d.deptno and exists (\n"
+ + " select 1 from dept d2 where d2.name = d.name)\n"
+ + "where e.empno is null";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testNullableNonJoinColumn() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join deptnullables d on e.deptno = d.deptno\n"
+ + "where d.name is null";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testIsNullOnPreservedInput() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join dept d on e.deptno = d.deptno\n"
+ + "where e.comm is null";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testNullSafeJoinCondition() {
+ final String sql = "select e.empno\n"
+ + "from empnullables e left join deptnullables d\n"
+ + "on e.deptno is not distinct from d.deptno\n"
+ + "where d.deptno is null";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testIsNullInDisjunction() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join dept d on e.deptno = d.deptno\n"
+ + "where d.deptno is null or e.empno > 10";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testNonDeterministicFilter() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join dept d on e.deptno = d.deptno\n"
+ + "where d.deptno is null and rand() > 0.5";
+ sql(sql).checkUnchanged();
+ }
+
+ @Test void testNonDeterministicJoinCondition() {
+ final String sql = "select e.empno\n"
+ + "from emp e left join dept d\n"
+ + "on e.deptno = d.deptno and rand() > 0.5\n"
+ + "where d.deptno is null";
+ sql(sql).checkUnchanged();
+ }
+
+ @AfterAll static void checkActualAndReferenceFiles() {
+ fixture().diffRepos.checkActualAndReferenceFiles();
+ }
+}
diff --git
a/core/src/test/resources/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.xml
b/core/src/test/resources/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.xml
new file mode 100644
index 0000000000..44be8d1fa0
--- /dev/null
+++
b/core/src/test/resources/org/apache/calcite/test/OuterJoinToAntiJoinRuleTest.xml
@@ -0,0 +1,247 @@
+<?xml version="1.0" ?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to you under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+<Root>
+ <TestCase name="testCorrelatedLeftJoin">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join dept d
+on e.deptno = d.deptno and exists (
+ select 1 from dept d2 where d2.name = d.name)
+where d.deptno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($9)])
+ LogicalJoin(condition=[AND(=($7, $9), EXISTS({
+LogicalFilter(condition=[=($1, $cor0.NAME)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))], joinType=[left], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[null:INTEGER],
NAME=[null:VARCHAR(10)])
+ LogicalJoin(condition=[AND(=($7, $9), EXISTS({
+LogicalFilter(condition=[=($1, $cor0.NAME)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))], joinType=[anti], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testCorrelatedRightJoin">
+ <Resource name="sql">
+ <![CDATA[select d.deptno
+from emp e right join dept d
+on e.deptno = d.deptno and exists (
+ select 1 from dept d2 where d2.name = d.name)
+where e.empno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$9])
+ LogicalFilter(condition=[IS NULL($0)])
+ LogicalJoin(condition=[AND(=($7, $9), EXISTS({
+LogicalFilter(condition=[=($1, $cor0.NAME)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))], joinType=[right], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIsNullInDisjunction">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join dept d on e.deptno = d.deptno
+where d.deptno is null or e.empno > 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(IS NULL($9), >($0, 10))])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIsNullOnPreservedInput">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join dept d on e.deptno = d.deptno
+where e.comm is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($6)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLeftJoin">
+ <Resource name="sql">
+ <![CDATA[select e.empno, d.name
+from emp e left join dept d on e.deptno = d.deptno
+where d.deptno is null and e.empno > 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], NAME=[$10])
+ LogicalFilter(condition=[AND(IS NULL($9), >($0, 10))])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], NAME=[$10])
+ LogicalFilter(condition=[>($0, 10)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[null:INTEGER],
NAME=[null:VARCHAR(10)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[anti])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNonDeterministicFilter">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join dept d on e.deptno = d.deptno
+where d.deptno is null and rand() > 0.5]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[AND(IS NULL($9), >(RAND(), CAST(0.5:DECIMAL(2,
1)):DOUBLE NOT NULL))])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNonDeterministicJoinCondition">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join dept d
+on e.deptno = d.deptno and rand() > 0.5
+where d.deptno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($9)])
+ LogicalJoin(condition=[AND(=($7, $9), >(RAND(), 0.5E0))], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNullSafeJoinCondition">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from empnullables e left join deptnullables d
+on e.deptno is not distinct from d.deptno
+where d.deptno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($9)])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNullableJoinKey">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join deptnullables d on e.deptno = d.deptno
+where d.deptno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($9)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[null:INTEGER],
NAME=[null:VARCHAR(10)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[anti])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNullableNonJoinColumn">
+ <Resource name="sql">
+ <![CDATA[select e.empno
+from emp e left join deptnullables d on e.deptno = d.deptno
+where d.name is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NULL($10)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRightJoin">
+ <Resource name="sql">
+ <![CDATA[select e.ename, d.name
+from emp e right join dept d on e.deptno = d.deptno
+where e.empno is null]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$1], NAME=[$10])
+ LogicalFilter(condition=[IS NULL($0)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[right])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ENAME=[$1], NAME=[$10])
+ LogicalProject(EMPNO=[null:INTEGER], ENAME=[null:VARCHAR(20)],
JOB=[null:VARCHAR(10)], MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)],
SAL=[null:INTEGER], COMM=[null:INTEGER], DEPTNO=[null:INTEGER],
SLACKER=[null:BOOLEAN], DEPTNO0=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($9, $0)], joinType=[anti])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+</Root>
diff --git a/core/src/test/resources/sql/planner.iq
b/core/src/test/resources/sql/planner.iq
index 0adbe5785b..c6f3c72623 100644
--- a/core/src/test/resources/sql/planner.iq
+++ b/core/src/test/resources/sql/planner.iq
@@ -685,4 +685,47 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0],
expr#5=[>($t2, $t4)], expr#6=[>
!ok
!set planner-rules original
+# [CALCITE-7711] Add a rule to convert LEFT or RIGHT OUTER JOIN with IS NULL
to ANTI JOIN
+!set planner-rules "
++CoreRules.OUTER_JOIN_TO_ANTI_JOIN"
+select l.id as left_id, r.id as right_id
+from (values (1), (2), (3)) as l(id)
+left join (values (1), (3)) as r(id) on l.id = r.id
+where r.id is null
+order by l.id;
++---------+----------+
+| LEFT_ID | RIGHT_ID |
++---------+----------+
+| 2 | |
++---------+----------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], proj#0..1=[{exprs}])
+ EnumerableMergeJoin(condition=[=($0, $1)], joinType=[anti])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }, { 3 }]])
+ EnumerableValues(tuples=[[{ 1 }, { 3 }]])
+!plan
+
+# RIGHT JOIN keeps the non-commutative join condition after swapping inputs.
+select l.id as left_id, r.id as right_id
+from (values (1), (3), (5)) as l(id)
+right join (values (0), (4), (6)) as r(id) on l.id > r.id
+where l.id is null
+order by r.id;
++---------+----------+
+| LEFT_ID | RIGHT_ID |
++---------+----------+
+| | 6 |
++---------+----------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], ID=[$t1], ID0=[$t0])
+ EnumerableNestedLoopJoin(condition=[>($1, $0)], joinType=[anti])
+ EnumerableValues(tuples=[[{ 0 }, { 4 }, { 6 }]])
+ EnumerableValues(tuples=[[{ 1 }, { 3 }, { 5 }]])
+!plan
+!set planner-rules original
+
# End planner.iq
