This is an automated email from the ASF dual-hosted git repository.
jakevin 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 c07e3b7b47d [feature](Nereids): eliminate left outer join by unique
(#28853)
c07e3b7b47d is described below
commit c07e3b7b47d98ab77569258d6bea01e3fa26ba3b
Author: 谢健 <jianx...@gmail.com>
AuthorDate: Tue Jan 2 15:50:49 2024 +0800
[feature](Nereids): eliminate left outer join by unique (#28853)
---
.../doris/nereids/jobs/executor/Rewriter.java | 7 +-
.../org/apache/doris/nereids/rules/RuleType.java | 1 +
.../rules/rewrite/EliminateJoinByUnique.java | 48 +++++++++++
.../rules/rewrite/EliminateJoinByUniqueTest.java | 97 ++++++++++++++++++++++
4 files changed, 152 insertions(+), 1 deletion(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index 4fe9df664c3..a97bd61e5c6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -57,6 +57,7 @@ import
org.apache.doris.nereids.rules.rewrite.EliminateEmptyRelation;
import org.apache.doris.nereids.rules.rewrite.EliminateFilter;
import org.apache.doris.nereids.rules.rewrite.EliminateGroupBy;
import org.apache.doris.nereids.rules.rewrite.EliminateJoinByFK;
+import org.apache.doris.nereids.rules.rewrite.EliminateJoinByUnique;
import org.apache.doris.nereids.rules.rewrite.EliminateJoinCondition;
import org.apache.doris.nereids.rules.rewrite.EliminateLimit;
import org.apache.doris.nereids.rules.rewrite.EliminateNotNull;
@@ -295,7 +296,11 @@ public class Rewriter extends AbstractBatchJobExecutor {
),
// this rule should invoke after infer predicate and push down
distinct, and before push down limit
- custom(RuleType.ELIMINATE_JOIN_BY_FOREIGN_KEY,
EliminateJoinByFK::new),
+ topic("eliminate join according unique or foreign key",
+ custom(RuleType.ELIMINATE_JOIN_BY_FOREIGN_KEY,
EliminateJoinByFK::new),
+ topDown(new EliminateJoinByUnique())
+ ),
+
// this rule should be after topic "Column pruning and infer
predicate"
topic("Join pull up",
topDown(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
index 32794946dda..57e62380137 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
@@ -208,6 +208,7 @@ public enum RuleType {
ELIMINATE_UNNECESSARY_PROJECT(RuleTypeClass.REWRITE),
ELIMINATE_OUTER_JOIN(RuleTypeClass.REWRITE),
ELIMINATE_GROUP_BY(RuleTypeClass.REWRITE),
+ ELIMINATE_JOIN_BY_UK(RuleTypeClass.REWRITE),
ELIMINATE_DEDUP_JOIN_CONDITION(RuleTypeClass.REWRITE),
ELIMINATE_NULL_AWARE_LEFT_ANTI_JOIN(RuleTypeClass.REWRITE),
ELIMINATE_ASSERT_NUM_ROWS(RuleTypeClass.REWRITE),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUnique.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUnique.java
new file mode 100644
index 00000000000..6dba90572d5
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUnique.java
@@ -0,0 +1,48 @@
+// 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.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.NullSafeEqual;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+
+/**
+ * Eliminate outer join.
+ */
+public class EliminateJoinByUnique extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalProject(
+ logicalJoin().when(join ->
join.getJoinType().isLeftOuterJoin())
+ ).then(project -> {
+ LogicalJoin<?, ?> join = project.child();
+ if
(!join.left().getOutputSet().containsAll(project.getInputSlots())) {
+ return project;
+ }
+ if
(join.getHashJoinConjuncts().stream().anyMatch(NullSafeEqual.class::isInstance))
{
+ // TODO: support null safe equals in fd and this
+ return project;
+ }
+ if
(!project.getLogicalProperties().getFunctionalDependencies().isUnique(project.getOutputSet()))
{
+ return project;
+ }
+ return project.withChildren(join.left());
+ }).toRule(RuleType.ELIMINATE_JOIN_BY_UK);
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUniqueTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUniqueTest.java
new file mode 100644
index 00000000000..c3b9c9de005
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByUniqueTest.java
@@ -0,0 +1,97 @@
+// 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.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.util.MemoPatternMatchSupported;
+import org.apache.doris.nereids.util.PlanChecker;
+import org.apache.doris.utframe.TestWithFeService;
+
+import org.junit.jupiter.api.Test;
+
+class EliminateJoinByUniqueTest extends TestWithFeService implements
MemoPatternMatchSupported {
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("default_cluster:test");
+ createTables(
+ "CREATE TABLE IF NOT EXISTS t1 (\n"
+ + " id1 int not null,\n"
+ + " id_null int\n"
+ + ")\n"
+ + "DUPLICATE KEY(id1)\n"
+ + "DISTRIBUTED BY HASH(id1) BUCKETS 10\n"
+ + "PROPERTIES (\"replication_num\" = \"1\")\n",
+ "CREATE TABLE IF NOT EXISTS t2 (\n"
+ + " id2 int not null\n"
+ + ")\n"
+ + "DUPLICATE KEY(id2)\n"
+ + "DISTRIBUTED BY HASH(id2) BUCKETS 10\n"
+ + "PROPERTIES (\"replication_num\" = \"1\")\n");
+ addConstraint("Alter table t1 add constraint uk_t1 unique (id1)");
+ addConstraint("Alter table t1 add constraint id_null unique
(id_null)");
+ addConstraint("Alter table t2 add constraint uk_t2 unique (id2)");
+ }
+
+ @Test
+ void testNotNull() throws Exception {
+ String sql = "select t1.id1 from t1 left outer join t2 on t1.id1 =
t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .nonMatch(logicalJoin())
+ .printlnTree();
+
+ sql = "select t1.id1 from t1 left outer join t2 on t1.id1 <=> t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .matches(logicalJoin())
+ .printlnTree();
+
+ sql = "select t2.id2 from t1 left outer join t2 on t1.id1 = t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .matches(logicalJoin())
+ .printlnTree();
+ }
+
+ @Test
+ void testNull() throws Exception {
+ String sql = "select t1.id1 from t1 left outer join t2 on t1.id_null =
t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .nonMatch(logicalJoin())
+ .printlnTree();
+
+ sql = "select t1.id1 from t1 left outer join t2 on t1.id_null <=>
t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .matches(logicalJoin())
+ .printlnTree();
+
+ sql = "select t2.id2 from t1 left outer join t2 on t1.id_null =
t2.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .matches(logicalJoin())
+ .printlnTree();
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
