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
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 60c3372d8e5 [fix](Nereids): fix elimiate join by pkfk when there are
multi joins (#32703)
60c3372d8e5 is described below
commit 60c3372d8e516e69b523f451433de7ee5328b9f0
Author: 谢健 <[email protected]>
AuthorDate: Tue Mar 26 10:59:23 2024 +0800
[fix](Nereids): fix elimiate join by pkfk when there are multi joins
(#32703)
---
.../nereids/rules/rewrite/ForeignKeyContext.java | 2 +-
.../data/nereids_rules_p0/pkfk/eliminate_inner.out | 28 ++++++++++++++++++++++
.../nereids_rules_p0/pkfk/eliminate_inner.groovy | 8 ++++++-
3 files changed, 36 insertions(+), 2 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
index 9cbac5cf09c..600f3d1ac5a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
@@ -171,7 +171,7 @@ public class ForeignKeyContext {
return true;
}
// There are some predicates in primary key but there is no
predicate in foreign key
- if (slotWithPredicates.containsKey(pf.getValue()) &&
slotWithPredicates.get(pf.getValue()).isEmpty()) {
+ if (!slotWithPredicates.containsKey(pf.getValue()) ||
slotWithPredicates.get(pf.getValue()).isEmpty()) {
return false;
}
Set<Expression> primaryPredicates =
slotWithPredicates.get(pf.getKey()).stream()
diff --git a/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
index fbbbf32b065..5a9c194c373 100644
--- a/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
+++ b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
@@ -285,3 +285,31 @@ PhysicalResultSink
-- !res --
+-- !name --
+multi_table_join_with_pk_predicate
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((fkt_not_null.fk = fkt_not_null2.fk))
otherCondition=()
+----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+------filter((cast(p as DOUBLE) = 1.0))
+--------PhysicalOlapScan[pkt]
+------PhysicalOlapScan[fkt_not_null]
+----PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+multi_table_join_with_pk_fk_predicate
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((fkt_not_null.fk = fkt_not_null2.fk))
otherCondition=()
+----filter((fkt_not_null.fk = 1))
+------PhysicalOlapScan[fkt_not_null]
+----filter((fkt_not_null2.fk = 1))
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+1 John
+
diff --git
a/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
index a2e750f3dd2..652dc1b1250 100644
--- a/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
+++ b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
@@ -88,6 +88,7 @@ suite("eliminate_inner") {
qt_shape "explain shape plan ${sql}"
order_qt_res "${sql}"
}
+
// not nullable
check_shape_res("select fkt_not_null.* from pkt inner join fkt_not_null on
pkt.pk = fkt_not_null.fk;", "simple_case")
check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join
fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col")
@@ -112,6 +113,11 @@ suite("eliminate_inner") {
check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fk from
fkt order by fk limit 1 ) fkt on pkt.pk = fkt.fk;", "fk with limit")
check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.pk = 1 and fkt.fk = 1;", "pk with filter that same as fk")
check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.pk = 1 and fkt.fk = 1 and fkt.f = 1;", "pk with filter that
included same as fk")
- check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.p = 1 and fkt.fk = 1 and fkt.f = 1;;", "pk with filter that
not same as fk")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.p = 1 and fkt.fk = 1 and fkt.f = 1;", "pk with filter that not
same as fk")
+
+ // Test multiple table joins, where fkt_not_null and pkt have a primary
key-foreign key relationship, and fkt_not_null2 is an foreign table.
+ check_shape_res("select fkt_not_null.* from pkt inner join fkt_not_null on
pkt.pk = fkt_not_null.fk inner join fkt_not_null as fkt_not_null2 on
fkt_not_null.fk = fkt_not_null2.fk where pkt.p = 1;",
"multi_table_join_with_pk_predicate")
+ // Test multiple table joins, where fkt_not_null and pkt have a primary
key-foreign key relationship, fkt_not_null2 is an foreign table, and predicates
exist in both tables.
+ check_shape_res("select fkt_not_null.* from pkt inner join fkt_not_null on
pkt.pk = fkt_not_null.fk inner join fkt_not_null as fkt_not_null2 on
fkt_not_null.fk = fkt_not_null2.fk where pkt.pk = 1;",
"multi_table_join_with_pk_fk_predicate")
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
