This is an automated email from the ASF dual-hosted git repository.

yujun777 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git


The following commit(s) were added to refs/heads/master by this push:
     new 848e2105571 [opt](nereids) Eliminate empty anti joins while preserving 
CTE checks (#70521)
848e2105571 is described below

commit 848e210557139ba9184a5615ef3cf3191d59a901
Author: yujun <[email protected]>
AuthorDate: Wed Nov 18 10:03:36 2026 +0800

    [opt](nereids) Eliminate empty anti joins while preserving CTE checks 
(#70521)
    
    Nereids propagates logical empty relations through joins, projections
    and set operations. Incremental materialized-view refreshes use anti
    joins to repair the NULL-extended rows of outer joins, so an empty delta
    can leave a repair branch with no possible output.
---
 .../rules/rewrite/EliminateEmptyRelation.java      |  23 ++-
 .../rules/rewrite/EliminateEmptyRelationTest.java  | 170 +++++++++++++++++++++
 .../data/mtmv_p0/ivm/test_ivm_empty_anti_join.out  | 111 ++++++++++++++
 .../data/mtmv_p0/ivm/test_ivm_explain_refresh.out  |  36 +----
 .../eliminate_empty_anti_join.out                  |  94 ++++++++++++
 .../mtmv_p0/ivm/test_ivm_empty_anti_join.groovy    |  71 +++++++++
 .../eliminate_empty_anti_join.groovy               | 112 ++++++++++++++
 7 files changed, 586 insertions(+), 31 deletions(-)

diff --git 
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelation.java
 
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelation.java
index 89c1f6e6588..f10ffcdcbc4 100644
--- 
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelation.java
+++ 
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelation.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.rules.Rule;
 import org.apache.doris.nereids.rules.RuleType;
 import org.apache.doris.nereids.trees.UnaryNode;
 import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.CTEId;
 import org.apache.doris.nereids.trees.expressions.ExprId;
 import org.apache.doris.nereids.trees.expressions.Expression;
 import org.apache.doris.nereids.trees.expressions.NamedExpression;
@@ -34,15 +35,19 @@ import 
org.apache.doris.nereids.trees.plans.algebra.EmptyRelation;
 import org.apache.doris.nereids.trees.plans.algebra.SetOperation;
 import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
 import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTEConsumer;
 import org.apache.doris.nereids.trees.plans.logical.LogicalEmptyRelation;
 import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
 import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
 import org.apache.doris.qe.ConnectContext;
 
+import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableList;
 
+import java.util.HashSet;
 import java.util.List;
 import java.util.Optional;
+import java.util.Set;
 import java.util.stream.Collectors;
 
 /**
@@ -239,6 +244,8 @@ public class EliminateEmptyRelation implements 
RewriteRuleFactory {
             || join.getJoinType() == JoinType.LEFT_SEMI_JOIN
             || join.getJoinType() == JoinType.RIGHT_SEMI_JOIN
             || join.getJoinType() == JoinType.CROSS_JOIN)
+            || (join.getJoinType() == JoinType.LEFT_ANTI_JOIN && join.left() 
instanceof EmptyRelation)
+            || (join.getJoinType() == JoinType.RIGHT_ANTI_JOIN && join.right() 
instanceof EmptyRelation)
             || (join.getJoinType().isLeftOuterJoin() && join.left() instanceof 
EmptyRelation)
             || (join.getJoinType().isAsofLeftOuterJoin() && join.left() 
instanceof EmptyRelation)
             || (join.getJoinType().isRightOuterJoin() && join.right() 
instanceof EmptyRelation)
@@ -255,18 +262,32 @@ public class EliminateEmptyRelation implements 
RewriteRuleFactory {
      * scalar's {@link LogicalAssertNumRows} and return an empty result 
instead of raising the
      * "must return only 1 row" error. Volatile functions are intentionally 
not checked here: their values are
      * non-deterministic but not side-effecting, and they produce no rows 
inside an empty join anyway.
+     * Materialized CTE consumers are leaves, so follow their producers as 
well: removing the last consumer
+     * also removes its producer and any checks inside it.
      */
     private boolean containsSideEffect(Plan plan) {
+        return containsSideEffect(plan, new HashSet<>());
+    }
+
+    private boolean containsSideEffect(Plan plan, Set<CTEId> checkedCteIds) {
         if (plan instanceof LogicalAssertNumRows) {
             return true;
         }
+        if (plan instanceof LogicalCTEConsumer) {
+            CTEId cteId = ((LogicalCTEConsumer) plan).getCteId();
+            if (checkedCteIds.add(cteId)) {
+                Plan producer = 
ConnectContext.get().getStatementContext().getCteProducerByCteId(cteId);
+                Preconditions.checkState(producer != null, "CTE producer for 
CTE: %s not found", cteId);
+                return containsSideEffect(producer, checkedCteIds);
+            }
+        }
         for (Expression expression : plan.getExpressions()) {
             if (expression.containsType(NoneMovableFunction.class)) {
                 return true;
             }
         }
         for (Plan child : plan.children()) {
-            if (containsSideEffect(child)) {
+            if (containsSideEffect(child, checkedCteIds)) {
                 return true;
             }
         }
diff --git 
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelationTest.java
 
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelationTest.java
index 1b4d38b0011..957b1d71558 100644
--- 
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelationTest.java
+++ 
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateEmptyRelationTest.java
@@ -17,16 +17,24 @@
 
 package org.apache.doris.nereids.rules.rewrite;
 
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.trees.expressions.Alias;
 import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
 import 
org.apache.doris.nereids.trees.expressions.AssertNumRowsElement.Assertion;
+import org.apache.doris.nereids.trees.expressions.CTEId;
 import org.apache.doris.nereids.trees.expressions.NamedExpression;
 import org.apache.doris.nereids.trees.expressions.Slot;
 import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.AssertTrue;
+import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
 import org.apache.doris.nereids.trees.plans.JoinType;
 import org.apache.doris.nereids.trees.plans.Plan;
 import org.apache.doris.nereids.trees.plans.RelationId;
 import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
 import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTEConsumer;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTEProducer;
 import org.apache.doris.nereids.trees.plans.logical.LogicalEmptyRelation;
 import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
 import org.apache.doris.nereids.trees.plans.logical.LogicalOlapScan;
@@ -39,13 +47,18 @@ import 
org.apache.doris.nereids.util.MemoPatternMatchSupported;
 import org.apache.doris.nereids.util.MemoTestUtils;
 import org.apache.doris.nereids.util.PlanChecker;
 import org.apache.doris.nereids.util.PlanConstructor;
+import org.apache.doris.qe.ConnectContext;
 
+import com.google.common.collect.ImmutableList;
 import com.google.common.collect.Lists;
 import org.junit.jupiter.api.Assertions;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.EnumSource;
 
 import java.util.ArrayList;
 import java.util.List;
+import java.util.stream.Collectors;
 
 /**
  * Tests for {@link EliminateEmptyRelation}.
@@ -53,6 +66,163 @@ import java.util.List;
 class EliminateEmptyRelationTest implements MemoPatternMatchSupported {
     private static final LogicalOlapScan scan1 = 
PlanConstructor.newLogicalOlapScan(0, "t1", 0);
 
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testAntiJoinWithEmptyOutputSide(JoinType joinType) {
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), scan1);
+        Plan rewritten = eliminateEmptyRelation(join);
+        Assertions.assertInstanceOf(LogicalEmptyRelation.class, rewritten);
+        Assertions.assertEquals(join.getOutput(), rewritten.getOutput());
+        for (int i = 0; i < join.getOutput().size(); i++) {
+            Assertions.assertEquals(join.getOutput().get(i).getDataType(), 
rewritten.getOutput().get(i).getDataType());
+            Assertions.assertEquals(join.getOutput().get(i).nullable(), 
rewritten.getOutput().get(i).nullable());
+        }
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testAntiJoinWithEmptyOtherSide(JoinType joinType) {
+        Assertions.assertInstanceOf(LogicalJoin.class,
+                eliminateEmptyRelation(antiJoin(joinType, scan1, 
emptyRelation())));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testAntiJoinWithBothSidesEmpty(JoinType joinType) {
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), 
emptyRelation());
+        Plan rewritten = eliminateEmptyRelation(join);
+        Assertions.assertInstanceOf(LogicalEmptyRelation.class, rewritten);
+        Assertions.assertEquals(join.getOutput(), rewritten.getOutput());
+    }
+
+    @Test
+    void testEmptyAntiMarkJoinNotEliminated() {
+        LogicalPlan join = new LogicalPlanBuilder(emptyRelation())
+                .markJoin(scan1, JoinType.LEFT_ANTI_JOIN, Pair.of(0, 
0)).build();
+        Assertions.assertInstanceOf(LogicalJoin.class, 
eliminateEmptyRelation(join));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesNestedAssertNumRows(JoinType joinType) {
+        LogicalPlan checked = new LogicalPlanBuilder(new 
LogicalAssertNumRows<>(
+                new AssertNumRowsElement(1, "", Assertion.EQ), 
scan1)).projectAll().build();
+        Assertions.assertInstanceOf(LogicalJoin.class,
+                eliminateEmptyRelation(antiJoin(joinType, emptyRelation(), 
checked)));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesNonMovableProject(JoinType joinType) {
+        LogicalPlan checked = new 
LogicalPlanBuilder(scan1).projectExprs(ImmutableList.of(
+                new Alias(new AssertTrue(BooleanLiteral.FALSE, new 
VarcharLiteral("must fail"))))).build();
+        Assertions.assertInstanceOf(LogicalJoin.class,
+                eliminateEmptyRelation(antiJoin(joinType, emptyRelation(), 
checked)));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesNonMovableCondition(JoinType joinType) {
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), scan1);
+        LogicalPlan checked = new LogicalPlanBuilder((LogicalPlan) 
join.child(0))
+                .join((LogicalPlan) join.child(1), joinType, 
ImmutableList.of(), ImmutableList.of(
+                        new AssertTrue(BooleanLiteral.FALSE, new 
VarcharLiteral("must fail")))).build();
+        Assertions.assertInstanceOf(LogicalJoin.class, 
eliminateEmptyRelation(checked));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesCteNonMovableProject(JoinType joinType) {
+        ConnectContext context = 
MemoTestUtils.createStatementContext("").getConnectContext();
+        LogicalPlan checked = new 
LogicalPlanBuilder(scan1).projectExprs(ImmutableList.of(
+                new Alias(new AssertTrue(BooleanLiteral.FALSE, new 
VarcharLiteral("must fail"))))).build();
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), 
cteConsumer(context, checked));
+        Assertions.assertInstanceOf(LogicalJoin.class, 
eliminateEmptyRelation(context, join));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesCteAssertNumRows(JoinType joinType) {
+        ConnectContext context = 
MemoTestUtils.createStatementContext("").getConnectContext();
+        LogicalPlan checked = new LogicalAssertNumRows<>(new 
AssertNumRowsElement(1, "", Assertion.EQ), scan1);
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), 
cteConsumer(context, checked));
+        Assertions.assertInstanceOf(LogicalJoin.class, 
eliminateEmptyRelation(context, join));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinPreservesNestedCteChecks(JoinType joinType) {
+        ConnectContext context = 
MemoTestUtils.createStatementContext("").getConnectContext();
+        LogicalPlan checked = new 
LogicalPlanBuilder(scan1).projectExprs(ImmutableList.of(
+                new Alias(new AssertTrue(BooleanLiteral.FALSE, new 
VarcharLiteral("must fail"))))).build();
+        LogicalPlan outer = new LogicalPlanBuilder(cteConsumer(context, 
checked)).projectAll().build();
+        LogicalPlan join = antiJoin(joinType, emptyRelation(), 
cteConsumer(context, outer));
+        Assertions.assertInstanceOf(LogicalJoin.class, 
eliminateEmptyRelation(context, join));
+    }
+
+    @ParameterizedTest
+    @EnumSource(value = JoinType.class, names = {"LEFT_ANTI_JOIN", 
"RIGHT_ANTI_JOIN"})
+    void testEmptyAntiJoinEliminatesPureCte(JoinType joinType) {
+        ConnectContext context = 
MemoTestUtils.createStatementContext("").getConnectContext();
+        LogicalCTEConsumer first = cteConsumer(context, scan1);
+        LogicalCTEConsumer second = new 
LogicalCTEConsumer(PlanConstructor.getNextRelationId(), first.getCteId(),
+                "c", 
context.getStatementContext().getCteProducerByCteId(first.getCteId()));
+        LogicalPlan other = new LogicalPlanBuilder(first).joinEmptyOn(second, 
JoinType.CROSS_JOIN).build();
+        Assertions.assertInstanceOf(LogicalEmptyRelation.class,
+                eliminateEmptyRelation(context, antiJoin(joinType, 
emptyRelation(), other)));
+    }
+
+    @Test
+    void testEmptyAntiJoinPropagatesThroughProjectAndNestedUnion() {
+        LogicalPlan left = new 
LogicalPlanBuilder(antiJoin(JoinType.LEFT_ANTI_JOIN, emptyRelation(), scan1))
+                .projectAll().build();
+        LogicalPlan right = antiJoin(JoinType.RIGHT_ANTI_JOIN, emptyRelation(),
+                PlanConstructor.newLogicalOlapScan(1, "t2", 0));
+        LogicalPlan nested = union(left, right);
+        LogicalPlan root = union(nested, antiJoin(JoinType.LEFT_ANTI_JOIN, 
emptyRelation(),
+                PlanConstructor.newLogicalOlapScan(2, "t3", 0)));
+        Plan rewritten = eliminateEmptyRelation(root);
+        Assertions.assertInstanceOf(LogicalEmptyRelation.class, rewritten);
+        Assertions.assertEquals(root.getOutput(), rewritten.getOutput());
+    }
+
+    private LogicalEmptyRelation emptyRelation() {
+        return new LogicalEmptyRelation(PlanConstructor.getNextRelationId(), 
ImmutableList.of(
+                new SlotReference("k", IntegerType.INSTANCE, false),
+                new SlotReference("v", StringType.INSTANCE, true)));
+    }
+
+    private LogicalPlan antiJoin(JoinType joinType, LogicalPlan outputSide, 
LogicalPlan otherSide) {
+        if (joinType == JoinType.LEFT_ANTI_JOIN) {
+            return new LogicalPlanBuilder(outputSide).joinEmptyOn(otherSide, 
joinType).build();
+        }
+        return new LogicalPlanBuilder(otherSide).joinEmptyOn(outputSide, 
joinType).build();
+    }
+
+    private LogicalUnion union(LogicalPlan left, LogicalPlan right) {
+        return new LogicalUnion(Qualifier.ALL, new 
ArrayList<>(left.getOutput()),
+                
ImmutableList.of(left.getOutput().stream().map(SlotReference.class::cast).collect(Collectors.toList()),
+                        
right.getOutput().stream().map(SlotReference.class::cast).collect(Collectors.toList())),
+                ImmutableList.of(), false, ImmutableList.of(left, right));
+    }
+
+    private Plan eliminateEmptyRelation(LogicalPlan plan) {
+        return PlanChecker.from(MemoTestUtils.createConnectContext(), plan)
+                .applyBottomUp(new EliminateEmptyRelation()).getPlan();
+    }
+
+    private Plan eliminateEmptyRelation(ConnectContext context, LogicalPlan 
plan) {
+        return 
PlanChecker.from(MemoTestUtils.createCascadesContext(context.getStatementContext(),
 plan))
+                .applyBottomUp(new EliminateEmptyRelation()).getPlan();
+    }
+
+    private LogicalCTEConsumer cteConsumer(ConnectContext context, LogicalPlan 
body) {
+        CTEId cteId = context.getStatementContext().getNextCTEId();
+        LogicalCTEProducer<LogicalPlan> producer = new 
LogicalCTEProducer<>(cteId, body);
+        context.getStatementContext().setCteProducer(cteId, producer);
+        return new LogicalCTEConsumer(PlanConstructor.getNextRelationId(), 
cteId, "c", producer);
+    }
+
     @Test
     void testEliminateUnionEmptyChild() {
         List<SlotReference> emptyOutput = new ArrayList<>();
diff --git a/regression-test/data/mtmv_p0/ivm/test_ivm_empty_anti_join.out 
b/regression-test/data/mtmv_p0/ivm/test_ivm_empty_anti_join.out
new file mode 100644
index 00000000000..3166161aff5
--- /dev/null
+++ b/regression-test/data/mtmv_p0/ivm/test_ivm_empty_anti_join.out
@@ -0,0 +1,111 @@
+-- This file is automatically generated. You should know what you did if you 
want to edit this
+-- !initial_mv --
+\N     40      \N
+1      10      100
+2      20      \N
+3      30      300
+
+-- !initial_full --
+\N     40      \N
+1      10      100
+2      20      \N
+3      30      300
+
+-- !left_delta_mv --
+\N     40      \N
+1      10      100
+2      20      \N
+3      30      300
+4      40      \N
+
+-- !left_delta_full --
+\N     40      \N
+1      10      100
+2      20      \N
+3      30      300
+4      40      \N
+
+-- !first_match_mv --
+\N     40      \N
+1      10      100
+2      20      200
+3      30      300
+4      40      \N
+
+-- !first_match_full --
+\N     40      \N
+1      10      100
+2      20      200
+3      30      300
+4      40      \N
+
+-- !last_match_deleted_mv --
+\N     40      \N
+1      10      \N
+2      20      200
+3      30      300
+4      40      \N
+
+-- !last_match_deleted_full --
+\N     40      \N
+1      10      \N
+2      20      200
+3      30      300
+4      40      \N
+
+-- !value_update_mv --
+\N     40      \N
+1      10      \N
+2      20      201
+3      30      300
+4      40      \N
+
+-- !value_update_full --
+\N     40      \N
+1      10      \N
+2      20      201
+3      30      300
+4      40      \N
+
+-- !key_change_mv --
+\N     40      \N
+1      10      \N
+2      20      201
+3      30      \N
+4      40      301
+
+-- !key_change_full --
+\N     40      \N
+1      10      \N
+2      20      201
+3      30      \N
+4      40      301
+
+-- !null_key_mv --
+\N     41      \N
+1      10      \N
+2      20      201
+3      30      \N
+4      40      301
+
+-- !null_key_full --
+\N     41      \N
+1      10      \N
+2      20      201
+3      30      \N
+4      40      301
+
+-- !both_sides_mv --
+\N     41      \N
+1      10      \N
+3      30      \N
+4      40      301
+5      50      500
+
+-- !both_sides_full --
+\N     41      \N
+1      10      \N
+3      30      \N
+4      40      301
+5      50      500
+
diff --git a/regression-test/data/mtmv_p0/ivm/test_ivm_explain_refresh.out 
b/regression-test/data/mtmv_p0/ivm/test_ivm_explain_refresh.out
index fa3f19c0391..90044de0bb1 100644
--- a/regression-test/data/mtmv_p0/ivm/test_ivm_explain_refresh.out
+++ b/regression-test/data/mtmv_p0/ivm/test_ivm_explain_refresh.out
@@ -3,36 +3,12 @@
 PhysicalOlapTableSink
 --PhysicalDistribute[DistributionSpecOlapTableSinkHashPartitioned]
 ----PhysicalProject
-------PhysicalUnion
---------PhysicalDistribute[DistributionSpecExecutionAny]
-----------PhysicalProject
-------------hashJoin[LEFT_OUTER_JOIN joinImpl] hashCondition=((k1 = k1)) 
otherCondition=()
---------------PhysicalProject
-----------------PhysicalOlapScan[test_ivm_explain_refresh_t1]
---------------PhysicalProject
-----------------filter((test_ivm_explain_refresh_t2.__DORIS_DELETE_SIGN__ = 0))
-------------------PhysicalOlapScan[test_ivm_explain_refresh_t2]
---------PhysicalDistribute[DistributionSpecExecutionAny]
-----------PhysicalProject
-------------hashJoin[INNER_JOIN joinImpl] hashCondition=((k1 = 
__DORIS_IVM_NULL_SIDE_EVENT_KEY_0)) otherCondition=()
---------------PhysicalProject
-----------------filter((test_ivm_explain_refresh_t1.__DORIS_DELETE_SIGN__ = 0))
-------------------PhysicalOlapScan[test_ivm_explain_refresh_t1]
---------------PhysicalUnion
-----------------PhysicalDistribute[DistributionSpecExecutionAny]
-------------------PhysicalProject
---------------------hashJoin[LEFT_ANTI_JOIN joinImpl] 
hashCondition=((__DORIS_IVM_NULL_SIDE_EVENT_KEY_0 = k1)) otherCondition=()
-----------------------PhysicalEmptyRelation
-----------------------PhysicalProject
-------------------------filter((test_ivm_explain_refresh_t2.__DORIS_DELETE_SIGN__
 = 0))
---------------------------PhysicalOlapScan[test_ivm_explain_refresh_t2]
-----------------PhysicalDistribute[DistributionSpecExecutionAny]
-------------------PhysicalProject
---------------------hashJoin[LEFT_ANTI_JOIN joinImpl] 
hashCondition=((__DORIS_IVM_NULL_SIDE_EVENT_KEY_0 = k1)) otherCondition=()
-----------------------PhysicalEmptyRelation
-----------------------PhysicalProject
-------------------------filter((test_ivm_explain_refresh_t2.__DORIS_DELETE_SIGN__
 = 0))
---------------------------PhysicalOlapScan[test_ivm_explain_refresh_t2]
+------hashJoin[LEFT_OUTER_JOIN joinImpl] hashCondition=((k1 = k1)) 
otherCondition=()
+--------PhysicalProject
+----------PhysicalOlapScan[test_ivm_explain_refresh_t1]
+--------PhysicalProject
+----------filter((test_ivm_explain_refresh_t2.__DORIS_DELETE_SIGN__ = 0))
+------------PhysicalOlapScan[test_ivm_explain_refresh_t2]
 
 -- !pending_stream_shape_plan --
 PhysicalOlapTableSink
diff --git 
a/regression-test/data/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.out
 
b/regression-test/data/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.out
new file mode 100644
index 00000000000..1ea847188ab
--- /dev/null
+++ 
b/regression-test/data/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.out
@@ -0,0 +1,94 @@
+-- This file is automatically generated. You should know what you did if you 
want to edit this
+-- !left_plan --
+PhysicalResultSink
+--PhysicalEmptyRelation
+
+-- !right_plan --
+PhysicalResultSink
+--PhysicalEmptyRelation
+
+-- !left_result --
+
+-- !right_result --
+
+-- !other_right_empty --
+\N     30
+1      10
+1      10
+2      20
+
+-- !other_left_empty --
+\N     30
+1      10
+1      10
+2      20
+
+-- !left_both_empty --
+
+-- !right_both_empty --
+
+-- !nested_plan --
+PhysicalResultSink
+--PhysicalEmptyRelation
+
+-- !nested_result --
+
+-- !union_survivor --
+10     1
+10     1
+20     2
+30     \N
+
+-- !union_constant --
+99     9
+
+-- !union_distinct --
+\N     30
+1      10
+2      20
+
+-- !global_aggregate --
+0
+
+-- !mark_result --
+\N     true
+1      true
+1      true
+2      true
+
+-- !cte_right_assert_plan --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------PhysicalOlapScan[empty_anti_join_t]
+--PhysicalResultSink
+----hashJoin[RIGHT_ANTI_JOIN joinImpl] hashCondition=((expr_cast(ok as INT) = 
r.k)) otherCondition=()
+------PhysicalProject
+--------PhysicalCteConsumer ( cteId=CTEId#0 )
+------PhysicalEmptyRelation
+
+-- !cte_right_scalar_plan --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------NestedLoopJoin[CROSS_JOIN]
+--------PhysicalProject
+----------PhysicalOneRowRelation
+--------PhysicalProject
+----------PhysicalAssertNumRows
+------------PhysicalDistribute[DistributionSpecGather]
+--------------PhysicalProject
+----------------PhysicalOlapScan[empty_anti_join_t]
+--PhysicalResultSink
+----hashJoin[RIGHT_ANTI_JOIN joinImpl] hashCondition=((c.v = r.k)) 
otherCondition=()
+------PhysicalCteConsumer ( cteId=CTEId#0 )
+------PhysicalEmptyRelation
+
+-- !pure_cte_left_plan --
+PhysicalResultSink
+--PhysicalEmptyRelation
+
+-- !pure_cte_right_plan --
+PhysicalResultSink
+--PhysicalEmptyRelation
+
diff --git a/regression-test/suites/mtmv_p0/ivm/test_ivm_empty_anti_join.groovy 
b/regression-test/suites/mtmv_p0/ivm/test_ivm_empty_anti_join.groovy
new file mode 100644
index 00000000000..4cd1275fdb9
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ivm/test_ivm_empty_anti_join.groovy
@@ -0,0 +1,71 @@
+// 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.
+
+suite("test_ivm_empty_anti_join") {
+    sql "set enable_materialized_view_rewrite = false"
+    sql "set enable_dml_materialized_view_rewrite = false"
+    sql "drop materialized view if exists ivm_empty_anti_mv"
+    sql "drop table if exists ivm_empty_anti_l"
+    sql "drop table if exists ivm_empty_anti_r"
+    sql """create table ivm_empty_anti_l (k int, v int)
+        unique key(k) distributed by hash(k) buckets 1
+        properties ("replication_num" = "1", "changelog.enable" = "true",
+            "changelog.need_historical_value" = "true", 
"enable_unique_key_merge_on_write" = "true")"""
+    sql """create table ivm_empty_anti_r (k int, v int)
+        unique key(k) distributed by hash(k) buckets 1
+        properties ("replication_num" = "1", "changelog.enable" = "true",
+            "changelog.need_historical_value" = "true", 
"enable_unique_key_merge_on_write" = "true")"""
+    sql "insert into ivm_empty_anti_l values (1, 10), (2, 20), (3, 30), (null, 
40)"
+    sql "insert into ivm_empty_anti_r values (1, 100), (3, 300), (null, 400)"
+    def fullQuery = """select l.k, l.v as lv, r.v as rv from ivm_empty_anti_l l
+        left join ivm_empty_anti_r r on l.k = r.k"""
+    sql """create materialized view ivm_empty_anti_mv
+        build deferred refresh incremental on manual
+        distributed by random buckets 1 properties ('replication_num' = '1')
+        as ${fullQuery}"""
+    sql "refresh materialized view ivm_empty_anti_mv complete"
+    waitingMTMVTaskFinishedByMvName("ivm_empty_anti_mv")
+    order_qt_initial_mv "select k, lv, rv from ivm_empty_anti_mv"
+    order_qt_initial_full fullQuery
+
+    // Only the retained side changes: both null-side repair key inputs are 
statically empty.
+    sql "insert into ivm_empty_anti_l values (4, 40)"
+    sql "refresh materialized view ivm_empty_anti_mv incremental"
+    waitingMTMVTaskFinishedByMvName("ivm_empty_anti_mv")
+    order_qt_left_delta_mv "select k, lv, rv from ivm_empty_anti_mv"
+    order_qt_left_delta_full fullQuery
+
+    def changes = [
+        first_match: ["insert into ivm_empty_anti_r values (2, 200)"],
+        last_match_deleted: ["delete from ivm_empty_anti_r where k = 1"],
+        value_update: ["update ivm_empty_anti_r set v = 201 where k = 2"],
+        key_change: ["delete from ivm_empty_anti_r where k = 3",
+                     "insert into ivm_empty_anti_r values (4, 301)"],
+        null_key: ["update ivm_empty_anti_l set v = 41 where k is null",
+                   "update ivm_empty_anti_r set v = 401 where k is null"],
+        both_sides: ["insert into ivm_empty_anti_l values (5, 50)",
+                     "insert into ivm_empty_anti_r values (5, 500)",
+                     "delete from ivm_empty_anti_l where k = 2"]
+    ]
+    changes.each { stage, statements ->
+        statements.each { sql it }
+        sql "refresh materialized view ivm_empty_anti_mv incremental"
+        waitingMTMVTaskFinishedByMvName("ivm_empty_anti_mv")
+        "order_qt_${stage}_mv"("select k, lv, rv from ivm_empty_anti_mv")
+        "order_qt_${stage}_full"(fullQuery)
+    }
+}
diff --git 
a/regression-test/suites/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.groovy
 
b/regression-test/suites/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.groovy
new file mode 100644
index 00000000000..5a9bae1ba54
--- /dev/null
+++ 
b/regression-test/suites/nereids_rules_p0/eliminate_empty_anti_join/eliminate_empty_anti_join.groovy
@@ -0,0 +1,112 @@
+// 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.
+
+suite("eliminate_empty_anti_join") {
+    sql "drop table if exists empty_anti_join_t"
+    sql """create table empty_anti_join_t (k int, v int)
+        duplicate key(k) distributed by hash(k) buckets 1
+        properties ("replication_num" = "1")"""
+    sql "insert into empty_anti_join_t values (1, 10), (1, 10), (2, 20), 
(null, 30)"
+
+    def emptyLeft = """select l.k, l.v from
+        (select k, v from empty_anti_join_t where false) l
+        left anti join empty_anti_join_t r on l.k = r.k"""
+    def emptyRight = """select r.k, r.v from empty_anti_join_t l
+        right anti join (select k, v from empty_anti_join_t where false) r on 
l.k = r.k"""
+    def otherRightEmpty = """select l.k, l.v from empty_anti_join_t l
+        left anti join (select k, v from empty_anti_join_t where false) r on 
l.k = r.k"""
+    def otherLeftEmpty = """select r.k, r.v from
+        (select k, v from empty_anti_join_t where false) l
+        right anti join empty_anti_join_t r on l.k = r.k"""
+
+    qt_left_plan "explain shape plan ${emptyLeft}"
+    qt_right_plan "explain shape plan ${emptyRight}"
+    order_qt_left_result emptyLeft
+    order_qt_right_result emptyRight
+    order_qt_other_right_empty otherRightEmpty
+    order_qt_other_left_empty otherLeftEmpty
+    order_qt_left_both_empty """select l.k, l.v from
+        (select k, v from empty_anti_join_t where false) l
+        left anti join (select k, v from empty_anti_join_t where false) r on 
l.k = r.k"""
+    order_qt_right_both_empty """select r.k, r.v from
+        (select k, v from empty_anti_join_t where false) l
+        right anti join (select k, v from empty_anti_join_t where false) r on 
l.k = r.k"""
+
+    def nested = """select v, k from (${emptyLeft} union all ${emptyRight}) e
+        union all select v, k from (${emptyLeft}) e"""
+    qt_nested_plan "explain shape plan ${nested}"
+    order_qt_nested_result nested
+    order_qt_union_survivor "${nested} union all select v, k from 
empty_anti_join_t"
+    order_qt_union_constant "${nested} union all select 99, 9"
+    order_qt_union_distinct "${emptyLeft} union select k, v from 
empty_anti_join_t"
+    order_qt_global_aggregate "select count(*) from (${emptyLeft}) e"
+    order_qt_mark_result """select k, not exists
+        (select 1 from empty_anti_join_t r where false and r.k = l.k)
+        from empty_anti_join_t l"""
+
+    // Keep a materialized producer whose assertion is hidden behind a 
consumer leaf.
+    sql "set enable_cte_materialize = true"
+    sql "set inline_cte_referenced_threshold = 0"
+    sql "set disable_join_reorder = true"
+    test {
+        sql """with checked as (
+            select assert_true(v < 0, 'empty anti CTE check') as ok from 
empty_anti_join_t
+        ) select l.k from (select k from empty_anti_join_t where false) l
+        left anti join checked c on l.k = cast(c.ok as int)"""
+        exception "empty anti CTE check"
+    }
+    test {
+        sql """with checked as (
+            select assert_true(v < 0, 'nested empty anti CTE check') as ok 
from empty_anti_join_t
+        ), forwarded as (select ok from checked)
+        select l.k from (select k from empty_anti_join_t where false) l
+        left anti join forwarded c on l.k = cast(c.ok as int)"""
+        exception "nested empty anti CTE check"
+    }
+    test {
+        sql """with checked as (select (select v from empty_anti_join_t) as v)
+        select l.k from (select k from empty_anti_join_t where false) l
+        left anti join checked c on l.k = c.v"""
+        exception "Expected EQ 1"
+    }
+    // Check the CTE guard structure without cost-dependent runtime filter 
annotations.
+    sql "set runtime_filter_mode=OFF"
+    // Join distribution is cost-driven; the CTE producer/check must remain in 
every implementation.
+    def explainCteGuardPlan = { tag, query ->
+        delegate.quickRunTest(tag, query, false, { row ->
+            row.collect { value ->
+                value.toString().replaceAll(
+                    /hashJoin\[([A-Z_]+) 
(bucketShuffle|shuffleBucket|broadcast|shuffle|colocated|unknown)\]/,
+                    'hashJoin[$1 joinImpl]')
+            }
+        })
+    }
+    explainCteGuardPlan("cte_right_assert_plan", """explain shape plan with 
checked as (
+        select assert_true(v < 0, 'empty anti CTE check') as ok from 
empty_anti_join_t
+    ) select r.k from checked c
+    right anti join (select k from empty_anti_join_t where false) r on 
cast(c.ok as int) = r.k""")
+    explainCteGuardPlan("cte_right_scalar_plan", """explain shape plan
+        with checked as (select (select v from empty_anti_join_t) as v)
+        select r.k from checked c
+        right anti join (select k from empty_anti_join_t where false) r on c.v 
= r.k""")
+    qt_pure_cte_left_plan """explain shape plan with c as (select k from 
empty_anti_join_t)
+        select l.k from (select k from empty_anti_join_t where false) l
+        left anti join c on l.k = c.k"""
+    qt_pure_cte_right_plan """explain shape plan with c as (select k from 
empty_anti_join_t)
+        select r.k from c
+        right anti join (select k from empty_anti_join_t where false) r on c.k 
= r.k"""
+}


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to