This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 119b4a5c189 [Fix](Nereids) fix leading left outer join join constraint
collect problem (#31597)
119b4a5c189 is described below
commit 119b4a5c189e8d86029147ffce530195b8c20bc3
Author: LiBinfeng <[email protected]>
AuthorDate: Tue Mar 5 10:39:26 2024 +0800
[Fix](Nereids) fix leading left outer join join constraint collect problem
(#31597)
cherry-pick pr #31387
---
.../rules/analysis/CollectJoinConstraint.java | 3 +
.../data/nereids_p0/hint/fix_leading.out | 194 ++++++++++++++++++++-
.../suites/nereids_p0/hint/fix_leading.groovy | 112 +++++++++++-
3 files changed, 306 insertions(+), 3 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
index 42bc6d1f9bc..48d3ef0bfea 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
@@ -162,6 +162,9 @@ public class CollectJoinConstraint implements
RewriteRuleFactory {
}
}
}
+ if (minLeftHand == 0L) {
+ minLeftHand = leftHand;
+ }
JoinConstraint newJoinConstraint = new JoinConstraint(minLeftHand,
minRightHand, leftHand, rightHand,
join.getJoinType(), isStrict);
diff --git a/regression-test/data/nereids_p0/hint/fix_leading.out
b/regression-test/data/nereids_p0/hint/fix_leading.out
index a71ca311e75..4eb83a9abd4 100644
--- a/regression-test/data/nereids_p0/hint/fix_leading.out
+++ b/regression-test/data/nereids_p0/hint/fix_leading.out
@@ -4,10 +4,11 @@ PhysicalResultSink
--PhysicalDistribute
----PhysicalProject
------hashJoin[INNER_JOIN](t1.c1 = t3.c3)(t1.c1 = t4.c4)
---------hashJoin[INNER_JOIN](t1.c1 = t2.c2)
+--------NestedLoopJoin[CROSS_JOIN]
----------PhysicalOlapScan[t1]
----------PhysicalDistribute
-------------PhysicalOlapScan[t2]
+------------filter((t2.c2 = t2.c2))
+--------------PhysicalOlapScan[t2]
--------PhysicalDistribute
----------NestedLoopJoin[CROSS_JOIN](t4.c4 = t3.c3)(t3.c3 = t4.c4)
------------PhysicalOlapScan[t3]
@@ -19,3 +20,192 @@ Used: leading({ t1 t2 } { t3 t4 })
UnUsed:
SyntaxError:
+-- !select2_1_1 --
+1876000
+
+-- !select2_1_2 --
+1876000
+
+-- !select2_1_3 --
+1876000
+
+-- !select2_1_4 --
+1876000
+
+-- !select2_1_5 --
+1876000
+
+-- !select2_1_6 --
+1876000
+
+-- !select2_1_7 --
+1876000
+
+-- !select2_1_8 --
+1876000
+
+-- !select2_1_9 --
+1876000
+
+-- !select2_1_10 --
+1876000
+
+-- !select2_1_11 --
+1876000
+
+-- !select2_1_12 --
+1876000
+
+-- !select2_1_13 --
+1876000
+
+-- !select2_2_1 --
+1751202
+
+-- !select2_2_2 --
+1751202
+
+-- !select2_2_3 --
+1751202
+
+-- !select2_2_4 --
+1751202
+
+-- !select2_2_5 --
+1751202
+
+-- !select2_2_6 --
+1751202
+
+-- !select2_2_7 --
+1751202
+
+-- !select2_2_8 --
+1751202
+
+-- !select2_2_9 --
+1751202
+
+-- !select2_2_10 --
+1751202
+
+-- !select2_2_11 --
+1751202
+
+-- !select2_2_12 --
+1751202
+
+-- !select2_2_13 --
+1751202
+
+-- !select2_3_1 --
+816000
+
+-- !select2_3_2 --
+816000
+
+-- !select2_3_3 --
+816000
+
+-- !select2_3_4 --
+816000
+
+-- !select2_3_5 --
+816000
+
+-- !select2_3_6 --
+816000
+
+-- !select2_3_7 --
+816000
+
+-- !select2_3_8 --
+816000
+
+-- !select2_3_9 --
+816000
+
+-- !select2_3_10 --
+816000
+
+-- !select2_3_11 --
+816000
+
+-- !select2_3_12 --
+816000
+
+-- !select2_3_13 --
+816000
+
+-- !select2_4_1 --
+125000
+
+-- !select2_4_2 --
+125000
+
+-- !select2_4_3 --
+125000
+
+-- !select2_4_4 --
+125000
+
+-- !select2_4_5 --
+125000
+
+-- !select2_4_6 --
+125000
+
+-- !select2_4_7 --
+125000
+
+-- !select2_4_8 --
+125000
+
+-- !select2_4_9 --
+125000
+
+-- !select2_4_10 --
+125000
+
+-- !select2_4_11 --
+125000
+
+-- !select2_4_12 --
+125000
+
+-- !select2_4_13 --
+125000
+
+-- !select2_5_1 --
+816000
+
+-- !select2_5_2 --
+816000
+
+-- !select2_5_3 --
+816000
+
+-- !select2_5_5 --
+816000
+
+-- !select2_5_6 --
+816000
+
+-- !select2_5_7 --
+816000
+
+-- !select2_5_8 --
+816000
+
+-- !select2_5_9 --
+816000
+
+-- !select2_5_11 --
+816000
+
+-- !select2_5_12 --
+816000
+
+-- !select2_5_13 --
+816000
+
diff --git a/regression-test/suites/nereids_p0/hint/fix_leading.groovy
b/regression-test/suites/nereids_p0/hint/fix_leading.groovy
index b1da3b44b69..782bfc34647 100644
--- a/regression-test/suites/nereids_p0/hint/fix_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/fix_leading.groovy
@@ -38,6 +38,116 @@ suite("fix_leading") {
sql """create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3
properties('replication_num' = '1');"""
sql """create table t4 (c4 int, c44 int) distributed by hash(c4) buckets 3
properties('replication_num' = '1');"""
+ streamLoad {
+ table "t1"
+ db "fix_leading"
+ set 'column_separator', '|'
+ set 'format', 'csv'
+ file 't1.csv'
+ time 10000
+ }
+
+ streamLoad {
+ table "t2"
+ db "fix_leading"
+ set 'column_separator', '|'
+ set 'format', 'csv'
+ file 't2.csv'
+ time 10000
+ }
+
+ streamLoad {
+ table "t3"
+ db "fix_leading"
+ set 'column_separator', '|'
+ set 'format', 'csv'
+ file 't3.csv'
+ time 10000
+ }
+
+ streamLoad {
+ table "t4"
+ db "fix_leading"
+ set 'column_separator', '|'
+ set 'format', 'csv'
+ file 't4.csv'
+ time 10000
+ }
+
// bug fix 1: {t1 t2}{t3 t4} miss levels
- qt_select1 """explain shape plan select /*+ leading({t1 t2}{t3 t4}) */ *
from t1 join t2 on c1 = c2 join t3 on c1 = c3 join t4 on c1 = c4;"""
+ qt_select1 """explain shape plan select /*+ leading({t1 t2}{t3 t4}) */ *
from t1 join t2 on c2 = c2 join t3 on c1 = c3 join t4 on c1 = c4;"""
+
+ // bug fix 2: fix left outer join without edge with other tables
+ // left join + left join
+ qt_select2_1_1 """select count(*) from t1 left join t2 on c2 = c2 left
join t3 on c2 = c3;"""
+ qt_select2_1_2 """select /*+ leading(t1 t2 t3) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_3 """select /*+ leading(t1 {t2 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_4 """select /*+ leading(t1 t3 t2) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_5 """select /*+ leading(t1 {t2 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_6 """select /*+ leading(t2 t1 t3) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_7 """select /*+ leading(t2 {t1 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_8 """select /*+ leading(t2 t3 t1) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_9 """select /*+ leading(t2 {t3 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_10 """select /*+ leading(t3 t1 t2) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_11 """select /*+ leading(t3 {t1 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_12 """select /*+ leading(t3 t2 t1) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+ qt_select2_1_13 """select /*+ leading(t3 {t2 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left join t3 on c2 = c3;"""
+
+ // left join + right join
+ qt_select2_2_1 """select count(*) from t1 left join t2 on c2 = c2 right
join t3 on c2 = c3;"""
+ qt_select2_2_2 """select /*+ leading(t1 t2 t3) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_3 """select /*+ leading(t1 {t2 t3}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_4 """select /*+ leading(t1 t3 t2) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_5 """select /*+ leading(t1 {t2 t2}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_6 """select /*+ leading(t2 t1 t3) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_7 """select /*+ leading(t2 {t1 t3}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_8 """select /*+ leading(t2 t3 t1) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_9 """select /*+ leading(t2 {t3 t1}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_10 """select /*+ leading(t3 t1 t2) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_11 """select /*+ leading(t3 {t1 t2}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_12 """select /*+ leading(t3 t2 t1) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+ qt_select2_2_13 """select /*+ leading(t3 {t2 t1}) */ count(*) from t1 left
join t2 on c2 = c2 right join t3 on c2 = c3;"""
+
+ // left join + semi join
+ qt_select2_3_1 """select count(*) from t1 left join t2 on c2 = c2 left
semi join t3 on c2 = c3;"""
+ qt_select2_3_2 """select /*+ leading(t1 t2 t3) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_3 """select /*+ leading(t1 {t2 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_4 """select /*+ leading(t1 t3 t2) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_5 """select /*+ leading(t1 {t2 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_6 """select /*+ leading(t2 t1 t3) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_7 """select /*+ leading(t2 {t1 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_8 """select /*+ leading(t2 t3 t1) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_9 """select /*+ leading(t2 {t3 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_10 """select /*+ leading(t3 t1 t2) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_11 """select /*+ leading(t3 {t1 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_12 """select /*+ leading(t3 t2 t1) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_3_13 """select /*+ leading(t3 {t2 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+
+ // left join + anti join
+ qt_select2_4_1 """select count(*) from t1 left join t2 on c2 = c2 left
anti join t3 on c2 = c3;"""
+ qt_select2_4_2 """select /*+ leading(t1 t2 t3) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_3 """select /*+ leading(t1 {t2 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_4 """select /*+ leading(t1 t3 t2) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_5 """select /*+ leading(t1 {t2 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_6 """select /*+ leading(t2 t1 t3) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_7 """select /*+ leading(t2 {t1 t3}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_8 """select /*+ leading(t2 t3 t1) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_9 """select /*+ leading(t2 {t3 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_10 """select /*+ leading(t3 t1 t2) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_11 """select /*+ leading(t3 {t1 t2}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_12 """select /*+ leading(t3 t2 t1) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+ qt_select2_4_13 """select /*+ leading(t3 {t2 t1}) */ count(*) from t1 left
join t2 on c2 = c2 left anti join t3 on c2 = c3;"""
+
+ // right join + semi join
+ qt_select2_5_1 """select count(*) from t1 right join t2 on c2 = c2 left
semi join t3 on c2 = c3;"""
+ qt_select2_5_2 """select /*+ leading(t1 t2 t3) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_3 """select /*+ leading(t1 {t2 t3}) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_5 """select /*+ leading(t1 {t2 t2}) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_6 """select /*+ leading(t2 t1 t3) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_7 """select /*+ leading(t2 {t1 t3}) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_8 """select /*+ leading(t2 t3 t1) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_9 """select /*+ leading(t2 {t3 t1}) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_11 """select /*+ leading(t3 {t1 t2}) */ count(*) from t1
right join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_12 """select /*+ leading(t3 t2 t1) */ count(*) from t1 right
join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
+ qt_select2_5_13 """select /*+ leading(t3 {t2 t1}) */ count(*) from t1
right join t2 on c2 = c2 left semi join t3 on c2 = c3;"""
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
