You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/02/28 11:00:34 UTC

(doris) 03/06: [fix](Nereids): other cond should be kept for each anti join when expanding anti join such as (#31521)

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

yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git

commit d871852ba9385ada79c081acc69c9e698d320e9c
Author: 谢健 <ji...@gmail.com>
AuthorDate: Wed Feb 28 18:14:47 2024 +0800

    [fix](Nereids): other cond should be kept for each anti join when expanding anti join such as (#31521)
---
 .../doris/nereids/rules/rewrite/OrExpansion.java   |  24 +-
 .../data/nereids_p0/union/or_expansion.out         | 301 +++++++++++++++++++++
 .../suites/nereids_p0/union/or_expansion.groovy    | 152 ++++++++---
 3 files changed, 425 insertions(+), 52 deletions(-)

diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/OrExpansion.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/OrExpansion.java
index 987bfebc403..ff531ffce38 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/OrExpansion.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/OrExpansion.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.CascadesContext;
 import org.apache.doris.nereids.rules.Rule;
 import org.apache.doris.nereids.rules.RuleType;
 import org.apache.doris.nereids.rules.exploration.OneExplorationRuleFactory;
+import org.apache.doris.nereids.rules.exploration.join.JoinReorderContext;
 import org.apache.doris.nereids.trees.expressions.Alias;
 import org.apache.doris.nereids.trees.expressions.Expression;
 import org.apache.doris.nereids.trees.expressions.IsNull;
@@ -150,11 +151,11 @@ public class OrExpansion extends OneExplorationRuleFactory {
     }
 
     // expand Anti Join:
-    // Left Anti join cond1 or cond2           Left Anti join cond1
-    // /                      \                /               \
-    //left                   right   ===>  Anti join cond2    CTERight2
-    //                                      /      \
-    //                                  CTELeft CTERight1
+    // Left Anti join cond1 or cond2, other           Left Anti join cond1 and other
+    // /                      \                         /                        \
+    //left                   right          ===>  Anti join cond2 and other    CTERight2
+    //                                                  /      \
+    //                                               CTELeft CTERight1
     private Plan expandLeftAntiJoin(CascadesContext ctx,
             Pair<List<Expression>, List<Expression>> hashOtherConditions,
             LogicalJoin<? extends Plan, ? extends Plan> originJoin,
@@ -171,14 +172,14 @@ public class OrExpansion extends OneExplorationRuleFactory {
         replaced.putAll(right.getProducerToConsumerOutputMap());
         List<Expression> disjunctions = hashOtherConditions.first;
         List<Expression> otherConditions = hashOtherConditions.second;
-        otherConditions = otherConditions.stream()
+        List<Expression> newOtherConditions = otherConditions.stream()
                 .map(e -> e.rewriteUp(s -> replaced.containsKey(s) ? replaced.get(s) : s)).collect(Collectors.toList());
 
         Expression hashCond = disjunctions.get(0);
         hashCond = hashCond.rewriteUp(s -> replaced.containsKey(s) ? replaced.get(s) : s);
         Plan newPlan = new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN, Lists.newArrayList(hashCond),
-                otherConditions, originJoin.getDistributeHint(),
-                originJoin.getMarkJoinSlotReference(), left, right, null);
+                newOtherConditions, originJoin.getDistributeHint(),
+                originJoin.getMarkJoinSlotReference(), left, right, JoinReorderContext.EMPTY);
         if (hashCond.children().stream().anyMatch(e -> !(e instanceof Slot))) {
             Plan normalizedPlan = PushDownExpressionsInHashCondition.pushDownHashExpression(
                     (LogicalJoin<? extends Plan, ? extends Plan>) newPlan);
@@ -192,10 +193,13 @@ public class OrExpansion extends OneExplorationRuleFactory {
             ctx.putCTEIdToConsumer(newRight);
             Map<Slot, Slot> newReplaced = new HashMap<>(left.getProducerToConsumerOutputMap());
             newReplaced.putAll(newRight.getProducerToConsumerOutputMap());
+            newOtherConditions = otherConditions.stream()
+                    .map(e -> e.rewriteUp(s -> newReplaced.containsKey(s) ? newReplaced.get(s) : s))
+                    .collect(Collectors.toList());
             hashCond = hashCond.rewriteUp(s -> newReplaced.containsKey(s) ? newReplaced.get(s) : s);
             newPlan = new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN, Lists.newArrayList(hashCond),
-                    new ArrayList<>(), originJoin.getDistributeHint(),
-                    originJoin.getMarkJoinSlotReference(), newPlan, newRight, null);
+                    newOtherConditions, originJoin.getDistributeHint(),
+                    originJoin.getMarkJoinSlotReference(), newPlan, newRight, JoinReorderContext.EMPTY);
             if (hashCond.children().stream().anyMatch(e -> !(e instanceof Slot))) {
                 newPlan = PushDownExpressionsInHashCondition.pushDownHashExpression(
                         (LogicalJoin<? extends Plan, ? extends Plan>) newPlan);
diff --git a/regression-test/data/nereids_p0/union/or_expansion.out b/regression-test/data/nereids_p0/union/or_expansion.out
index 4779bf2ace1..3e5df5b3e42 100644
--- a/regression-test/data/nereids_p0/union/or_expansion.out
+++ b/regression-test/data/nereids_p0/union/or_expansion.out
@@ -2,11 +2,18 @@
 -- !order_ij --
 \N	\N
 1	\N
+1	\N
+\N	1
+1	1
+1	1
+1	1
+1	1
 1	1
 1	1
 
 -- !order_laj --
 \N
+\N
 0
 2
 3
@@ -20,8 +27,10 @@
 -- !order_loj --
 \N	\N
 \N	\N
+\N	\N
 0	\N
 1	\N
+1	\N
 2	\N
 3	\N
 4	\N
@@ -30,6 +39,11 @@
 7	\N
 8	\N
 9	\N
+\N	1
+1	1
+1	1
+1	1
+1	1
 1	1
 1	1
 
@@ -37,8 +51,89 @@
 \N	\N
 \N	\N
 \N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+1	1
+1	1
+1	1
+1	1
+1	1
+1	1
+\N	20
+\N	21
+\N	22
+\N	23
+\N	24
+\N	25
+\N	26
+\N	27
+\N	28
+\N	29
+
+-- !order_ij_multi_cond --
+\N	\N
+1	\N
+1	\N
+\N	1
+1	1
+1	1
+
+-- !order_laj_multi_cond --
+\N
+\N
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+-- !order_loj_multi_cond --
+\N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+1	1
+1	1
+
+-- !order_foj_multi_cond --
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
 0	\N
 1	\N
+1	\N
+1	\N
 2	\N
 3	\N
 4	\N
@@ -47,6 +142,8 @@
 7	\N
 8	\N
 9	\N
+\N	1
+\N	1
 1	1
 1	1
 \N	20
@@ -60,3 +157,207 @@
 \N	28
 \N	29
 
+-- !order_loj_unary_cond --
+\N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+1	1
+1	1
+1	1
+1	1
+
+-- !order_foj_unary_cond --
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+1	1
+1	1
+1	1
+1	1
+\N	20
+\N	21
+\N	22
+\N	23
+\N	24
+\N	25
+\N	26
+\N	27
+\N	28
+\N	29
+
+-- !order_loj_unary_cond --
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+1	\N
+1	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+\N	1
+\N	1
+\N	1
+1	1
+1	1
+1	1
+1	1
+1	1
+1	1
+\N	20
+\N	20
+1	20
+1	20
+\N	21
+\N	21
+1	21
+1	21
+\N	22
+\N	22
+1	22
+1	22
+\N	23
+\N	23
+1	23
+1	23
+\N	24
+\N	24
+1	24
+1	24
+\N	25
+\N	25
+1	25
+1	25
+\N	26
+\N	26
+1	26
+1	26
+\N	27
+\N	27
+1	27
+1	27
+\N	28
+\N	28
+1	28
+1	28
+\N	29
+\N	29
+1	29
+1	29
+
+-- !order_foj_unary_cond --
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+\N	\N
+0	\N
+1	\N
+1	\N
+1	\N
+1	\N
+1	\N
+1	\N
+2	\N
+3	\N
+4	\N
+5	\N
+6	\N
+7	\N
+8	\N
+9	\N
+\N	1
+\N	1
+\N	1
+\N	1
+1	1
+1	1
+1	1
+1	1
+1	1
+1	1
+\N	20
+\N	20
+1	20
+1	20
+\N	21
+\N	21
+1	21
+1	21
+\N	22
+\N	22
+1	22
+1	22
+\N	23
+\N	23
+1	23
+1	23
+\N	24
+\N	24
+1	24
+1	24
+\N	25
+\N	25
+1	25
+1	25
+\N	26
+\N	26
+1	26
+1	26
+\N	27
+\N	27
+1	27
+1	27
+\N	28
+\N	28
+1	28
+1	28
+\N	29
+\N	29
+1	29
+1	29
+
diff --git a/regression-test/suites/nereids_p0/union/or_expansion.groovy b/regression-test/suites/nereids_p0/union/or_expansion.groovy
index 2d68098cd2d..eb335b6caeb 100644
--- a/regression-test/suites/nereids_p0/union/or_expansion.groovy
+++ b/regression-test/suites/nereids_p0/union/or_expansion.groovy
@@ -19,12 +19,14 @@ suite("or_expansion") {
     sql "SET enable_nereids_planner=true"
     sql "SET enable_fallback_to_original_planner=false"
     sql "SET enable_pipeline_engine = true"
+    
     sql "drop table if exists oe1"
     sql "drop table if exists oe2"
     sql """
         CREATE TABLE IF NOT EXISTS oe1 (
             k0 bigint,
-            k1 bigint
+            k1 bigint,
+            k2 bigint
         )
         DUPLICATE KEY(k0)
         DISTRIBUTED BY HASH(k0) BUCKETS 1
@@ -36,7 +38,8 @@ suite("or_expansion") {
     sql """
         CREATE TABLE IF NOT EXISTS oe2 (
             k0 bigint,
-            k1 bigint
+            k1 bigint,
+            k2 bigint
         )
         DUPLICATE KEY(k0)
         DISTRIBUTED BY HASH(k0) BUCKETS 1
@@ -57,53 +60,63 @@ suite("or_expansion") {
     sql """
     alter table oe2 modify column k1 set stats ('row_count'='1000', 'ndv'='1000', 'min_value'='1000', 'max_value'='2000', 'avg_size'='1000', 'max_size'='1000' )
     """
+    sql """
+    alter table oe1 modify column k2 set stats ('row_count'='1000', 'ndv'='1000', 'min_value'='1000', 'max_value'='2000', 'avg_size'='1000', 'max_size'='1000' )
+    """
+    sql """
+    alter table oe2 modify column k2 set stats ('row_count'='1000', 'ndv'='1000', 'min_value'='1000', 'max_value'='2000', 'avg_size'='1000', 'max_size'='1000' )
+    """
 
-    explain {
-        sql("""
-            select oe1.k0, oe2.k0
-            from oe1 inner join oe2
-            on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
-        """)
-        contains "VHASH JOIN"
-    }
+    // explain {
+    //     sql("""
+    //         select oe1.k0, oe2.k0
+    //         from oe1 inner join oe2
+    //         on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
+    //     """)
+    //     contains "VHASH JOIN"
+    // }
 
-    explain {
-        sql("""
-            select oe1.k0
-            from oe1 left anti join oe2
-            on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
-        """)
-        contains "VHASH JOIN"
-    }
+    // explain {
+    //     sql("""
+    //         select oe1.k0
+    //         from oe1 left anti join oe2
+    //         on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
+    //     """)
+    //     contains "VHASH JOIN"
+    // }
 
-    explain {
-        sql("""
-            select oe1.k0, oe2.k0
-            from oe1 left outer join oe2
-            on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
-        """)
-        contains "VHASH JOIN"
-    }
+    // explain {
+    //     sql("""
+    //         select oe1.k0, oe2.k0
+    //         from oe1 left outer join oe2
+    //         on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
+    //     """)
+    //     contains "VHASH JOIN"
+    // }
 
-    explain {
-        sql("""
-            select oe1.k0, oe2.k0
-            from oe1 full outer join oe2
-            on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
-        """)
-        contains "VHASH JOIN"
-    }
+    // explain {
+    //     sql("""
+    //         select oe1.k0, oe2.k0
+    //         from oe1 full outer join oe2
+    //         on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
+    //     """)
+    //     contains "VHASH JOIN"
+    // }
 
     for (int i = 0; i < 10; i++) {
-        sql "insert into oe1 values(${i}, ${i})"
-        sql "insert into oe2 values(${i+20}, ${i+20})"
+        sql "insert into oe1 values(${i}, ${i}, ${i})"
+        sql "insert into oe2 values(${i+20}, ${i+20}, ${i+20})"
     }
-    sql "insert into oe1 values(null, 1)"
-    sql "insert into oe1 values(1, null)"
-    sql "insert into oe1 values(null, null)"
-    sql "insert into oe2 values(null, 1)"
-    sql "insert into oe2 values(1, null)"
-    sql "insert into oe2 values(null, null)"
+    sql "insert into oe1 values(1, 1, 1)"
+    sql "insert into oe1 values(null, null, null)"
+    sql "insert into oe1 values(null, 1, 1)"
+    sql "insert into oe1 values(1, null, null)"
+    sql "insert into oe1 values(null, null, 1)"
+    sql "insert into oe2 values(1, 1, 1)"
+    sql "insert into oe2 values(null, null, null)"
+    sql "insert into oe2 values(null, 1, 1)"
+    sql "insert into oe2 values(1, null, null)"
+    sql "insert into oe2 values(null, null, 1)"
 
     qt_order_ij """
         select oe1.k0, oe2.k0
@@ -132,4 +145,59 @@ suite("or_expansion") {
             on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2
             order by oe2.k0, oe1.k0
         """
+
+    qt_order_ij_multi_cond """
+        select oe1.k0, oe2.k0
+        from oe1 inner join oe2
+        on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = oe2.k2
+        order by oe2.k0, oe1.k0
+    """
+
+    qt_order_laj_multi_cond """
+            select oe1.k0
+            from oe1 left anti join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = oe2.k2
+            order by oe1.k0
+        """
+
+    qt_order_loj_multi_cond """
+            select oe1.k0, oe2.k0
+            from oe1 left outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = oe2.k2
+            order by oe2.k0, oe1.k0
+        """
+
+    qt_order_foj_multi_cond """
+            select oe1.k0, oe2.k0
+            from oe1 full outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = oe2.k2
+            order by oe2.k0, oe1.k0
+        """
+    
+    qt_order_loj_unary_cond """
+            select oe1.k0, oe2.k0
+            from oe1 left outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = 1
+            order by oe2.k0, oe1.k0
+        """
+
+    qt_order_foj_unary_cond """
+            select oe1.k0, oe2.k0
+            from oe1 full outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) and oe1.k2 = 1
+            order by oe2.k0, oe1.k0
+        """
+    qt_order_loj_unary_cond """
+            select oe1.k0, oe2.k0
+            from oe1 left outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) or oe1.k2 = 1
+            order by oe2.k0, oe1.k0
+        """
+
+    qt_order_foj_unary_cond """
+            select oe1.k0, oe2.k0
+            from oe1 full outer join oe2
+            on (oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2) or oe1.k2 = 1
+            order by oe2.k0, oe1.k0
+        """
 }


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org