You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "xzj7019 (via GitHub)" <gi...@apache.org> on 2023/12/19 15:42:24 UTC
[PR] [feature](Nereids) Pull up join from union all [doris]
xzj7019 opened a new pull request, #28682:
URL: https://github.com/apache/doris/pull/28682
## Proposed changes
[feature](Nereids) Pull up join from union all
## Further comments
If this is a relatively large or complex change, kick off the discussion at [dev@doris.apache.org](mailto:dev@doris.apache.org) by explaining why you chose the solution you did and what alternatives you considered, etc...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "XieJiann (via GitHub)" <gi...@apache.org>.
XieJiann commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434649743
##########
fe/fe-core/src/main/java/org/apache/doris/catalog/constraint/ForeignKeyConstraint.java:
##########
@@ -62,6 +62,10 @@ public String getReferencedColumnName(String column) {
return foreignToReference.get(column);
}
+ public ImmutableMap<String, String> getForeignToReference() {
+ return foreignToReference;
+ }
+
Review Comment:
using column is better
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434681278
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
Review Comment:
done
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
Review Comment:
done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866081555
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863070392
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 43.83 seconds
stream load tsv: 569 seconds loaded 74807831229 Bytes, about 125 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.6 seconds inserted 10000000 Rows, about 349K ops/s
storage size: 17184002416 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864247045
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 1825a99c7d512c8d324b0d8fbfbb3ec74a4e1737, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4676 4391 4439 4391
q2 358 167 158 158
q3 1467 1270 1238 1238
q4 1115 892 883 883
q5 3135 3152 3147 3147
q6 250 130 132 130
q7 988 491 476 476
q8 2213 2203 2174 2174
q9 6677 6694 6670 6670
q10 3211 3256 3277 3256
q11 306 187 185 185
q12 360 205 200 200
q13 4563 3823 3828 3823
q14 240 215 215 215
q15 551 529 519 519
q16 439 386 384 384
q17 1012 561 564 561
q18 7203 6843 7005 6843
q19 1521 1379 1383 1379
q20 495 304 304 304
q21 3082 2618 2710 2618
q22 349 278 283 278
Total cold run time: 44211 ms
Total hot run time: 39832 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4333 4320 4312 4312
q2 268 165 172 165
q3 3553 3546 3526 3526
q4 2389 2372 2382 2372
q5 5733 5746 5745 5745
q6 240 120 123 120
q7 2390 1878 1883 1878
q8 3524 3520 3532 3520
q9 8977 9041 9022 9022
q10 3915 4003 3987 3987
q11 490 369 361 361
q12 789 587 608 587
q13 4284 3585 3576 3576
q14 283 275 259 259
q15 570 517 522 517
q16 507 456 458 456
q17 1895 1886 1849 1849
q18 8696 8295 8140 8140
q19 1742 1766 1766 1766
q20 2261 1947 1934 1934
q21 6514 6206 6184 6184
q22 510 426 425 425
Total cold run time: 63863 ms
Total hot run time: 60701 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865500866
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863063684
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 2ce8853ce95ff748ab15d8f3762e7faf44a4deb0, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4675 4375 4408 4375
q2 359 155 159 155
q3 1471 1254 1248 1248
q4 1118 947 868 868
q5 3123 3156 3161 3156
q6 245 127 129 127
q7 975 492 498 492
q8 2172 2202 2185 2185
q9 6680 6649 6650 6649
q10 3220 3282 3271 3271
q11 309 179 177 177
q12 355 216 209 209
q13 4559 3851 3798 3798
q14 245 212 217 212
q15 572 522 523 522
q16 434 390 389 389
q17 994 579 576 576
q18 7121 6824 6935 6824
q19 1520 1393 1388 1388
q20 502 300 280 280
q21 3127 2663 2587 2587
q22 343 281 276 276
Total cold run time: 44119 ms
Total hot run time: 39764 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4313 4346 4315 4315
q2 267 169 174 169
q3 3533 3512 3506 3506
q4 2379 2360 2370 2360
q5 5736 5730 5727 5727
q6 243 123 123 123
q7 2380 1873 1904 1873
q8 3526 3504 3523 3504
q9 9037 8984 9005 8984
q10 3901 4007 3991 3991
q11 485 366 364 364
q12 761 617 598 598
q13 4331 3544 3557 3544
q14 278 261 263 261
q15 573 509 526 509
q16 509 430 439 430
q17 1887 1864 1831 1831
q18 8675 8177 8216 8177
q19 1715 1768 1739 1739
q20 2249 1942 1936 1936
q21 6534 6161 6171 6161
q22 486 409 431 409
Total cold run time: 63798 ms
Total hot run time: 60511 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867263170
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863875162
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.42 seconds
stream load tsv: 571 seconds loaded 74807831229 Bytes, about 124 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.9 seconds inserted 10000000 Rows, about 346K ops/s
storage size: 17184328179 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866009138
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864247465
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866052760
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.13 seconds
stream load tsv: 569 seconds loaded 74807831229 Bytes, about 125 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.6 seconds inserted 10000000 Rows, about 349K ops/s
storage size: 17183744476 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865501750
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867179658
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865838320
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865809558
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864621653
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 45.25 seconds
stream load tsv: 574 seconds loaded 74807831229 Bytes, about 124 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 67 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 33 seconds loaded 861443392 Bytes, about 24 MB/s
insert into select: 29.3 seconds inserted 10000000 Rows, about 341K ops/s
storage size: 17189067036 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864231287
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 43.95 seconds
stream load tsv: 564 seconds loaded 74807831229 Bytes, about 126 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 67 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.6 seconds inserted 10000000 Rows, about 349K ops/s
storage size: 17188462824 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "englefly (via GitHub)" <gi...@apache.org>.
englefly commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434652443
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
Review Comment:
pls, give an example to illustrate the plan before and after this rule
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866205126
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 43.94 seconds
stream load tsv: 569 seconds loaded 74807831229 Bytes, about 125 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 33 seconds loaded 861443392 Bytes, about 24 MB/s
insert into select: 28.9 seconds inserted 10000000 Rows, about 346K ops/s
storage size: 17184090570 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864568065
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864306895
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 43.62 seconds
stream load tsv: 566 seconds loaded 74807831229 Bytes, about 126 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 67 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.6 seconds inserted 10000000 Rows, about 349K ops/s
storage size: 17183453855 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866226505
PR approved by at least one committer and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 merged PR #28682:
URL: https://github.com/apache/doris/pull/28682
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863008488
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866081692
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 46a6b1d35bf482f8d8cb6e710b3371dd590ac2cc, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4687 4454 4409 4409
q2 395 160 160 160
q3 1464 1260 1256 1256
q4 1113 909 912 909
q5 3186 3162 3161 3161
q6 249 131 128 128
q7 1019 505 489 489
q8 2175 2210 2168 2168
q9 6716 6682 6655 6655
q10 3225 3269 3269 3269
q11 310 188 189 188
q12 351 211 214 211
q13 4562 3799 3794 3794
q14 239 217 212 212
q15 572 525 518 518
q16 449 397 383 383
q17 1003 580 553 553
q18 7140 6950 6983 6950
q19 1504 1378 1406 1378
q20 511 337 292 292
q21 3056 2652 2663 2652
q22 348 274 282 274
Total cold run time: 44274 ms
Total hot run time: 40009 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4375 4349 4350 4349
q2 269 166 171 166
q3 3528 3535 3516 3516
q4 2408 2380 2372 2372
q5 5747 5734 5744 5734
q6 243 125 126 125
q7 2377 1869 1905 1869
q8 3521 3539 3514 3514
q9 9049 9007 9026 9007
q10 3929 4022 4006 4006
q11 484 377 366 366
q12 776 606 612 606
q13 4319 3585 3540 3540
q14 289 263 253 253
q15 570 529 515 515
q16 511 450 450 450
q17 1844 1861 1831 1831
q18 8619 8272 8231 8231
q19 1740 1776 1770 1770
q20 2238 1954 1930 1930
q21 6512 6148 6169 6148
q22 508 414 421 414
Total cold run time: 63856 ms
Total hot run time: 60712 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867313024
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.42 seconds
stream load tsv: 566 seconds loaded 74807831229 Bytes, about 126 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 33 seconds loaded 861443392 Bytes, about 24 MB/s
insert into select: 28.1 seconds inserted 10000000 Rows, about 355K ops/s
storage size: 17188017219 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864312681
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit cb5aa1e76f1bdb8c1708b487b2b9e0e12da730f3, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4703 4420 4428 4420
q2 364 150 158 150
q3 1460 1235 1239 1235
q4 1116 938 926 926
q5 3194 3149 3201 3149
q6 249 130 126 126
q7 1008 493 487 487
q8 2187 2211 2161 2161
q9 6688 6677 6665 6665
q10 3255 3289 3254 3254
q11 302 189 184 184
q12 348 212 212 212
q13 4529 3806 3805 3805
q14 236 215 213 213
q15 570 525 530 525
q16 446 397 387 387
q17 998 609 539 539
q18 7150 7052 7031 7031
q19 1513 1365 1363 1363
q20 510 311 326 311
q21 3102 2634 2692 2634
q22 341 282 277 277
Total cold run time: 44269 ms
Total hot run time: 40054 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4339 4363 4328 4328
q2 277 169 174 169
q3 3557 3537 3549 3537
q4 2408 2391 2396 2391
q5 5778 5750 5773 5750
q6 242 123 124 123
q7 2382 1891 1856 1856
q8 3543 3517 3531 3517
q9 9057 9016 8980 8980
q10 3902 3992 3994 3992
q11 491 380 378 378
q12 778 609 605 605
q13 4287 3595 3577 3577
q14 291 254 261 254
q15 566 516 520 516
q16 519 482 461 461
q17 1883 1861 1845 1845
q18 8741 8164 8093 8093
q19 1731 1753 1716 1716
q20 2273 1964 1951 1951
q21 6554 6206 6156 6156
q22 504 422 450 422
Total cold run time: 64103 ms
Total hot run time: 60617 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863863536
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 27d93ee82e9ef0677ad647da6bdb2f22e7466940, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4732 4467 4478 4467
q2 363 152 199 152
q3 1480 1246 1225 1225
q4 1122 898 913 898
q5 3179 3151 3168 3151
q6 248 128 131 128
q7 1026 500 491 491
q8 2204 2211 2180 2180
q9 6703 6670 6672 6670
q10 3259 3280 3313 3280
q11 312 190 195 190
q12 359 216 213 213
q13 4563 3862 3860 3860
q14 250 210 219 210
q15 576 524 523 523
q16 448 399 400 399
q17 1006 577 572 572
q18 7290 7001 6892 6892
q19 1513 1378 1446 1378
q20 557 310 322 310
q21 3107 2683 2666 2666
q22 362 291 298 291
Total cold run time: 44659 ms
Total hot run time: 40146 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4394 4357 4363 4357
q2 275 179 175 175
q3 3577 3526 3517 3517
q4 2396 2396 2396 2396
q5 5742 5738 5758 5738
q6 241 119 122 119
q7 2399 1887 1900 1887
q8 3517 3527 3520 3520
q9 8981 9058 9054 9054
q10 3911 4003 4006 4003
q11 497 364 381 364
q12 775 610 624 610
q13 4315 3568 3561 3561
q14 302 259 259 259
q15 572 523 516 516
q16 512 468 468 468
q17 1896 1870 1860 1860
q18 8645 8158 8332 8158
q19 1729 1770 1736 1736
q20 2284 1962 1936 1936
q21 6562 6245 6181 6181
q22 524 433 444 433
Total cold run time: 64046 ms
Total hot run time: 60848 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867519051
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.88 seconds
stream load tsv: 572 seconds loaded 74807831229 Bytes, about 124 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.8 seconds inserted 10000000 Rows, about 347K ops/s
storage size: 17183891309 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867578084
PR approved by at least one committer and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865888478
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.71 seconds
stream load tsv: 568 seconds loaded 74807831229 Bytes, about 125 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 33 seconds loaded 861443392 Bytes, about 24 MB/s
insert into select: 28.7 seconds inserted 10000000 Rows, about 348K ops/s
storage size: 17183804086 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1865930906
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit a2473e07646f77a79d6531547c3dd0847da96ece, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4699 4396 4430 4396
q2 368 187 158 158
q3 1495 1260 1210 1210
q4 1109 924 958 924
q5 3170 3160 3163 3160
q6 247 124 124 124
q7 1002 488 481 481
q8 2207 2203 2186 2186
q9 6686 6683 6667 6667
q10 3219 3270 3291 3270
q11 304 195 197 195
q12 348 207 211 207
q13 4569 3826 3825 3825
q14 244 212 214 212
q15 565 523 517 517
q16 437 380 384 380
q17 1016 592 561 561
q18 7167 6969 6892 6892
q19 1510 1417 1410 1410
q20 509 299 313 299
q21 3085 2625 2674 2625
q22 344 280 287 280
Total cold run time: 44300 ms
Total hot run time: 39979 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4373 4317 4320 4317
q2 266 163 173 163
q3 3512 3522 3507 3507
q4 2401 2382 2385 2382
q5 5716 5724 5739 5724
q6 241 122 122 122
q7 2415 1893 1888 1888
q8 3528 3514 3515 3514
q9 8983 8989 8974 8974
q10 3916 4000 4004 4000
q11 489 354 365 354
q12 759 603 612 603
q13 4284 3542 3538 3538
q14 299 251 255 251
q15 573 528 524 524
q16 504 482 499 482
q17 1863 1883 1889 1883
q18 8694 8220 8183 8183
q19 1736 1772 1735 1735
q20 2235 1949 1946 1946
q21 6523 6182 6181 6181
q22 487 424 407 407
Total cold run time: 63797 ms
Total hot run time: 60678 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434049416
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
Review Comment:
nit: `instanceOf` is including `!= null`, so we could remove `!= null` condition
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
Review Comment:
why size == 1?
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
+ }
+ }
+ }
+
+ private boolean checkJoinConditionOnPk(LogicalJoin joinRoot, LogicalCatalogRelation table, PullUpContext context) {
+ Set<String> pkInfos = getPkInfoFromConstraint(table);
+ if (pkInfos == null || pkInfos.size() != 1) {
+ return false;
+ }
+ String pkSlot = pkInfos.iterator().next();
+ List<LogicalJoin> joinList = new ArrayList<>();
+ joinList.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean found = false;
+ for (LogicalJoin join : joinList) {
+ List<Expression> conditions = join.getHashJoinConjuncts();
+ List<LogicalCatalogRelation> basicTableList = new ArrayList<>();
+ basicTableList.addAll((Collection<? extends LogicalCatalogRelation>) join
+ .collect(LogicalCatalogRelation.class::isInstance));
+ for (Expression equalTo : conditions) {
+ if (equalTo instanceof EqualTo
Review Comment:
EqualTo -> EqualPredicate? EqualPredicate include EqualTo and NullSafeEqualTo
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
+ }
+ }
+ }
+
+ private boolean checkJoinConditionOnPk(LogicalJoin joinRoot, LogicalCatalogRelation table, PullUpContext context) {
+ Set<String> pkInfos = getPkInfoFromConstraint(table);
+ if (pkInfos == null || pkInfos.size() != 1) {
+ return false;
+ }
+ String pkSlot = pkInfos.iterator().next();
+ List<LogicalJoin> joinList = new ArrayList<>();
+ joinList.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean found = false;
+ for (LogicalJoin join : joinList) {
+ List<Expression> conditions = join.getHashJoinConjuncts();
+ List<LogicalCatalogRelation> basicTableList = new ArrayList<>();
+ basicTableList.addAll((Collection<? extends LogicalCatalogRelation>) join
+ .collect(LogicalCatalogRelation.class::isInstance));
+ for (Expression equalTo : conditions) {
+ if (equalTo instanceof EqualTo
+ && ((EqualTo) equalTo).left() instanceof SlotReference
+ && ((EqualTo) equalTo).right() instanceof SlotReference) {
+ SlotReference leftSlot = (SlotReference) ((EqualTo) equalTo).left();
+ SlotReference rightSlot = (SlotReference) ((EqualTo) equalTo).right();
+ if (table.getOutputExprIds().contains(leftSlot.getExprId())
+ && pkSlot.equals(leftSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation rightTable = findTableFromSlot(rightSlot, basicTableList);
+ if (rightTable != null && getFkInfoFromConstraint(rightTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(rightTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(rightSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(rightSlot);
+ context.pullUpTableToPkSlotMap.put(table, leftSlot);
+ break;
+ }
+ }
+ }
+ }
+ } else if (table.getOutputExprIds().contains(rightSlot.getExprId())
+ && pkSlot.equals(rightSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation leftTable = findTableFromSlot(leftSlot, basicTableList);
+ if (leftTable != null && getFkInfoFromConstraint(leftTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(leftTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(leftSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(leftSlot);
+ context.pullUpTableToPkSlotMap.put(table, rightSlot);
+ break;
+ }
+ }
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ return found;
+ }
+
+ private LogicalCatalogRelation findTableFromSlot(SlotReference targetSlot,
+ List<LogicalCatalogRelation> tableList) {
+ for (LogicalCatalogRelation table : tableList) {
+ if (table.getOutputExprIds().contains(targetSlot.getExprId())) {
+ return table;
+ }
+ }
+ return null;
+ }
+
+ private ForeignKeyConstraint getFkInfoFromConstraint(LogicalCatalogRelation table) {
+ table.getTable().readLock();
Review Comment:
do not need lock, since we have locked table before do optimize
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
Review Comment:
nit: this function is very similar to `checkJoinCondition`. maybe we could reuse some code
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
Review Comment:
nit: could be static
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
+ }
+ }
+ }
+
+ private boolean checkJoinConditionOnPk(LogicalJoin joinRoot, LogicalCatalogRelation table, PullUpContext context) {
+ Set<String> pkInfos = getPkInfoFromConstraint(table);
+ if (pkInfos == null || pkInfos.size() != 1) {
+ return false;
+ }
+ String pkSlot = pkInfos.iterator().next();
+ List<LogicalJoin> joinList = new ArrayList<>();
+ joinList.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean found = false;
+ for (LogicalJoin join : joinList) {
+ List<Expression> conditions = join.getHashJoinConjuncts();
+ List<LogicalCatalogRelation> basicTableList = new ArrayList<>();
+ basicTableList.addAll((Collection<? extends LogicalCatalogRelation>) join
+ .collect(LogicalCatalogRelation.class::isInstance));
+ for (Expression equalTo : conditions) {
+ if (equalTo instanceof EqualTo
+ && ((EqualTo) equalTo).left() instanceof SlotReference
+ && ((EqualTo) equalTo).right() instanceof SlotReference) {
+ SlotReference leftSlot = (SlotReference) ((EqualTo) equalTo).left();
+ SlotReference rightSlot = (SlotReference) ((EqualTo) equalTo).right();
+ if (table.getOutputExprIds().contains(leftSlot.getExprId())
+ && pkSlot.equals(leftSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation rightTable = findTableFromSlot(rightSlot, basicTableList);
+ if (rightTable != null && getFkInfoFromConstraint(rightTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(rightTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(rightSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(rightSlot);
+ context.pullUpTableToPkSlotMap.put(table, leftSlot);
+ break;
+ }
+ }
+ }
+ }
+ } else if (table.getOutputExprIds().contains(rightSlot.getExprId())
+ && pkSlot.equals(rightSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation leftTable = findTableFromSlot(leftSlot, basicTableList);
+ if (leftTable != null && getFkInfoFromConstraint(leftTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(leftTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(leftSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(leftSlot);
+ context.pullUpTableToPkSlotMap.put(table, rightSlot);
+ break;
+ }
+ }
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ return found;
+ }
+
+ private LogicalCatalogRelation findTableFromSlot(SlotReference targetSlot,
+ List<LogicalCatalogRelation> tableList) {
+ for (LogicalCatalogRelation table : tableList) {
+ if (table.getOutputExprIds().contains(targetSlot.getExprId())) {
+ return table;
+ }
+ }
+ return null;
+ }
+
+ private ForeignKeyConstraint getFkInfoFromConstraint(LogicalCatalogRelation table) {
+ table.getTable().readLock();
+ try {
+ for (Map.Entry<String, Constraint> constraintMap : table.getTable().getConstraintsMap().entrySet()) {
+ Constraint constraint = constraintMap.getValue();
+ if (constraint instanceof ForeignKeyConstraint) {
+ return (ForeignKeyConstraint) constraint;
+ }
+ }
+ return null;
+ } finally {
+ table.getTable().readUnlock();
+ }
+ }
+
+ private Set<String> getPkInfoFromConstraint(LogicalCatalogRelation table) {
+ table.getTable().readLock();
+ try {
+ for (Map.Entry<String, Constraint> constraintMap : table.getTable().getConstraintsMap().entrySet()) {
+ Constraint constraint = constraintMap.getValue();
+ if (constraint instanceof PrimaryKeyConstraint) {
+ return ((PrimaryKeyConstraint) constraint).getPrimaryKeyNames();
+ }
+ }
+ return null;
+ } finally {
+ table.getTable().readUnlock();
+ }
+ }
+
+ private Set<String> getUkInfoFromConstraint(LogicalCatalogRelation table) {
+ table.getTable().readLock();
+ try {
+ for (Map.Entry<String, Constraint> constraintMap : table.getTable().getConstraintsMap().entrySet()) {
+ Constraint constraint = constraintMap.getValue();
+ if (constraint instanceof UniqueConstraint) {
+ return ((UniqueConstraint) constraint).getUniqueColumnNames();
+ }
+ }
+ return null;
+ } finally {
+ table.getTable().readUnlock();
+ }
+ }
+
+ private boolean checkJoinRoot(LogicalJoin joinRoot) {
+ List<LogicalPlan> joinChildrenPlans = Lists.newArrayList();
+ joinChildrenPlans.addAll((Collection<? extends LogicalPlan>) joinRoot
+ .collect(LogicalPlan.class::isInstance));
+ boolean planTypeMatch = joinChildrenPlans.stream()
+ .allMatch(p -> SUPPORTED_PLAN_TYPE.stream().anyMatch(c -> c.isInstance(p)));
+ if (!planTypeMatch) {
+ return false;
+ }
+
+ List<LogicalJoin> allJoinNodes = Lists.newArrayList();
+ allJoinNodes.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean joinTypeMatch = allJoinNodes.stream().allMatch(e -> e.getJoinType() == JoinType.INNER_JOIN);
+ boolean joinConditionMatch = allJoinNodes.stream()
+ .allMatch(e -> !e.getHashJoinConjuncts().isEmpty() && e.getOtherJoinConjuncts().isEmpty());
+ if (!joinTypeMatch || !joinConditionMatch) {
+ return false;
+ }
+
+ return true;
+ }
+
+ private boolean checkAggrRoot(LogicalAggregate aggrRoot) {
+ for (Object expr : aggrRoot.getGroupByExpressions()) {
+ if (!(expr instanceof NamedExpression)) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private List<LogicalCatalogRelation> getTableListUnderJoin(LogicalJoin joinRoot) {
+ List<LogicalCatalogRelation> tableLists = new ArrayList<>();
+ tableLists.addAll((Collection<? extends LogicalCatalogRelation>) joinRoot
+ .collect(LogicalCatalogRelation.class::isInstance));
+ return tableLists;
+ }
+
+ private String makeQualifiedName(LogicalCatalogRelation table) {
+ String dbName = table.getTable().getDatabase().getFullName();
+ String tableName = table.getTable().getName();
+ return dbName + ":" + tableName;
Review Comment:
why use `:`?
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
+ }
+ }
+ }
+
+ private boolean checkJoinConditionOnPk(LogicalJoin joinRoot, LogicalCatalogRelation table, PullUpContext context) {
+ Set<String> pkInfos = getPkInfoFromConstraint(table);
+ if (pkInfos == null || pkInfos.size() != 1) {
+ return false;
+ }
+ String pkSlot = pkInfos.iterator().next();
+ List<LogicalJoin> joinList = new ArrayList<>();
+ joinList.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean found = false;
+ for (LogicalJoin join : joinList) {
+ List<Expression> conditions = join.getHashJoinConjuncts();
+ List<LogicalCatalogRelation> basicTableList = new ArrayList<>();
+ basicTableList.addAll((Collection<? extends LogicalCatalogRelation>) join
+ .collect(LogicalCatalogRelation.class::isInstance));
+ for (Expression equalTo : conditions) {
+ if (equalTo instanceof EqualTo
+ && ((EqualTo) equalTo).left() instanceof SlotReference
+ && ((EqualTo) equalTo).right() instanceof SlotReference) {
+ SlotReference leftSlot = (SlotReference) ((EqualTo) equalTo).left();
+ SlotReference rightSlot = (SlotReference) ((EqualTo) equalTo).right();
+ if (table.getOutputExprIds().contains(leftSlot.getExprId())
+ && pkSlot.equals(leftSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation rightTable = findTableFromSlot(rightSlot, basicTableList);
+ if (rightTable != null && getFkInfoFromConstraint(rightTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(rightTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(rightSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(rightSlot);
+ context.pullUpTableToPkSlotMap.put(table, leftSlot);
+ break;
+ }
+ }
+ }
+ }
+ } else if (table.getOutputExprIds().contains(rightSlot.getExprId())
+ && pkSlot.equals(rightSlot.getName())) {
+ // pk-fk join condition, check other side's join key is on fk
+ LogicalCatalogRelation leftTable = findTableFromSlot(leftSlot, basicTableList);
+ if (leftTable != null && getFkInfoFromConstraint(leftTable) != null) {
+ ForeignKeyConstraint fkInfo = getFkInfoFromConstraint(leftTable);
+ if (fkInfo.getReferencedTable().getId() == table.getTable().getId()) {
+ for (Map.Entry<String, String> entry : fkInfo.getForeignToReference().entrySet()) {
+ if (entry.getValue().equals(pkSlot) && entry.getKey().equals(leftSlot.getName())) {
+ found = true;
+ context.replaceColumns.add(leftSlot);
+ context.pullUpTableToPkSlotMap.put(table, rightSlot);
+ break;
+ }
+ }
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ }
+ if (found) {
+ break;
+ }
+ }
+ return found;
+ }
+
+ private LogicalCatalogRelation findTableFromSlot(SlotReference targetSlot,
Review Comment:
we could use SlotReference#table attr for check more accurately
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866226541
PR approved by anyone and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864174645
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866286479
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit c10723c0bb6f0cfee2085431bd1cf7c640eebf52, data reload: true
run tpch-sf100 query with default conf and session variables
q1 4537 4328 4355 4328
q2 380 122 113 113
q3 1460 1191 1208 1191
q4 1136 944 831 831
q5 3174 3174 3201 3174
q6 243 123 123 123
q7 980 485 472 472
q8 2167 2184 2182 2182
q9 6766 6710 6676 6676
q10 3254 3251 3279 3251
q11 302 175 185 175
q12 357 195 200 195
q13 4532 3833 3827 3827
q14 228 209 206 206
q15 559 507 507 507
q16 449 386 379 379
q17 1003 600 543 543
q18 7438 6979 7746 6979
q19 1557 1307 1426 1307
q20 789 310 308 308
q21 3104 2710 2672 2672
q22 338 273 273 273
Total cold run time: 44753 ms
Total hot run time: 39712 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4333 4339 4331 4331
q2 270 165 167 165
q3 3533 3542 3532 3532
q4 2387 2376 2378 2376
q5 5742 5736 5766 5736
q6 238 119 118 118
q7 2432 1854 1880 1854
q8 3547 3536 3514 3514
q9 9091 9033 9089 9033
q10 3894 4015 4007 4007
q11 484 375 371 371
q12 775 607 598 598
q13 4277 3519 3589 3519
q14 283 247 257 247
q15 567 516 518 516
q16 508 452 449 449
q17 1889 1862 1853 1853
q18 8773 8388 8369 8369
q19 1757 1760 1760 1760
q20 2267 1937 1945 1937
q21 6563 6206 6170 6170
q22 505 424 404 404
Total cold run time: 64115 ms
Total hot run time: 60859 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434681857
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
+ }
+ }
+ }
+
+ private boolean checkJoinConditionOnPk(LogicalJoin joinRoot, LogicalCatalogRelation table, PullUpContext context) {
+ Set<String> pkInfos = getPkInfoFromConstraint(table);
+ if (pkInfos == null || pkInfos.size() != 1) {
+ return false;
+ }
+ String pkSlot = pkInfos.iterator().next();
+ List<LogicalJoin> joinList = new ArrayList<>();
+ joinList.addAll((Collection<? extends LogicalJoin>) joinRoot.collect(LogicalJoin.class::isInstance));
+ boolean found = false;
+ for (LogicalJoin join : joinList) {
+ List<Expression> conditions = join.getHashJoinConjuncts();
+ List<LogicalCatalogRelation> basicTableList = new ArrayList<>();
+ basicTableList.addAll((Collection<? extends LogicalCatalogRelation>) join
+ .collect(LogicalCatalogRelation.class::isInstance));
+ for (Expression equalTo : conditions) {
+ if (equalTo instanceof EqualTo
Review Comment:
restrict to EqualTo
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434681702
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
Review Comment:
for restricting the scenario
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867458426
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1864601119
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 93fb58ac85f7f8644f3285e4e95bd3abad271899, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4705 4452 4460 4452
q2 368 146 167 146
q3 1489 1235 1218 1218
q4 1124 910 892 892
q5 3157 3162 3193 3162
q6 246 131 131 131
q7 983 501 490 490
q8 2218 2211 2209 2209
q9 6707 6666 6697 6666
q10 3257 3301 3293 3293
q11 309 185 186 185
q12 362 218 207 207
q13 4558 3809 3812 3809
q14 245 211 220 211
q15 570 533 524 524
q16 450 388 393 388
q17 1008 600 570 570
q18 7101 6849 6942 6849
q19 1526 1323 1455 1323
q20 547 303 298 298
q21 3064 2648 2677 2648
q22 360 292 300 292
Total cold run time: 44354 ms
Total hot run time: 39963 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4390 4373 4357 4357
q2 273 166 173 166
q3 3546 3529 3543 3529
q4 2393 2382 2382 2382
q5 5746 5751 5754 5751
q6 242 123 125 123
q7 2384 1926 1901 1901
q8 3517 3539 3510 3510
q9 9019 9012 8988 8988
q10 3929 4005 4009 4005
q11 493 388 386 386
q12 764 624 597 597
q13 4296 3559 3560 3559
q14 297 257 256 256
q15 569 532 527 527
q16 501 445 473 445
q17 1912 1864 1857 1857
q18 8763 8206 8342 8206
q19 1720 1736 1775 1736
q20 2267 1958 1949 1949
q21 6632 6278 6196 6196
q22 509 432 435 432
Total cold run time: 64162 ms
Total hot run time: 60858 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "XieJiann (via GitHub)" <gi...@apache.org>.
XieJiann commented on code in PR #28682:
URL: https://github.com/apache/doris/pull/28682#discussion_r1434651090
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java:
##########
@@ -0,0 +1,688 @@
+// 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.catalog.constraint.Constraint;
+import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
+import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.UniqueConstraint;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+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.literal.Literal;
+import org.apache.doris.nereids.trees.plans.JoinHint;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Pull up join from union all rule.
+ */
+public class PullUpJoinFromUnionAll extends OneRewriteRuleFactory {
+ private static final Set<Class<? extends LogicalPlan>> SUPPORTED_PLAN_TYPE = ImmutableSet.of(
+ LogicalFilter.class,
+ LogicalJoin.class,
+ LogicalProject.class,
+ LogicalCatalogRelation.class
+ );
+
+ private static class PullUpContext {
+ public final String unifiedOutputAlias = "PULL_UP_UNIFIED_OUTPUT_ALIAS";
+ public final Map<String, List<LogicalCatalogRelation>> pullUpCandidatesMaps = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalJoin> tableToJoinRootMap = Maps.newHashMap();
+ public final Map<LogicalCatalogRelation, LogicalAggregate> tableToAggrRootMap = Maps.newHashMap();
+ public final Map<NamedExpression, SlotReference> origChild0ToNewUnionOutputMap = Maps.newHashMap();
+ public final List<LogicalAggregate> aggrChildList = Lists.newArrayList();
+ public final List<LogicalJoin> joinChildList = Lists.newArrayList();
+ public final List<SlotReference> replaceColumns = Lists.newArrayList();
+ public final Map<LogicalCatalogRelation, Slot> pullUpTableToPkSlotMap = Maps.newHashMap();
+ public int replacedColumnIndex = -1;
+ public LogicalCatalogRelation pullUpTable;
+
+ // the slot will replace the original pk in group by and select list
+ public SlotReference replaceColumn;
+ public boolean needAddReplaceColumn = false;
+
+ public PullUpContext() {}
+
+ public void setReplacedColumn(SlotReference slot) {
+ this.replaceColumn = slot;
+ }
+
+ public void setPullUpTable(LogicalCatalogRelation table) {
+ this.pullUpTable = table;
+ }
+
+ public void setNeedAddReplaceColumn(boolean needAdd) {
+ this.needAddReplaceColumn = needAdd;
+ }
+ }
+
+ @Override
+ public Rule build() {
+ return logicalUnion()
+ .when(union -> union.getQualifier() != Qualifier.DISTINCT)
+ .then(union -> {
+ PullUpContext context = new PullUpContext();
+ if (!checkUnionPattern(union, context)
+ || !checkJoinCondition(context)
+ || !checkGroupByKeys(context)) {
+ return null;
+ }
+ // only support single table pull up currently
+ if (context.pullUpCandidatesMaps.entrySet().size() != 1) {
+ return null;
+ }
+
+ List<LogicalCatalogRelation> pullUpTableList = context.pullUpCandidatesMaps
+ .entrySet().iterator().next().getValue();
+ if (pullUpTableList.size() != union.children().size()
+ || context.replaceColumns.size() != union.children().size()
+ || !checkNoFilterOnPullUpTable(pullUpTableList, context)) {
+ return null;
+ }
+ // make new union node
+ LogicalUnion newUnionNode = makeNewUnionNode(union, pullUpTableList, context);
+ // make new join node
+ LogicalJoin newJoin = makeNewJoin(newUnionNode, pullUpTableList.get(0), context);
+ // add project on pull up table with origin union output
+ List<NamedExpression> newProjectOutputs = makeNewProjectOutputs(union, newJoin, context);
+
+ return new LogicalProject(newProjectOutputs, newJoin);
+ }).toRule(RuleType.PULL_UP_JOIN_FROM_UNIONALL);
+ }
+
+ private boolean checkUnionPattern(LogicalUnion union, PullUpContext context) {
+ int tableListNumber = -1;
+ for (Plan child : union.children()) {
+ if (!(child instanceof LogicalProject
+ && child.child(0) != null
+ && child.child(0) instanceof LogicalAggregate
+ && child.child(0).child(0) != null
+ && child.child(0).child(0) instanceof LogicalProject
+ && child.child(0).child(0).child(0) != null
+ && child.child(0).child(0).child(0) instanceof LogicalJoin)) {
+ return false;
+ }
+ LogicalAggregate aggrRoot = (LogicalAggregate) child.child(0);
+ if (!checkAggrRoot(aggrRoot)) {
+ return false;
+ }
+ context.aggrChildList.add(aggrRoot);
+ LogicalJoin joinRoot = (LogicalJoin) aggrRoot.child().child(0);
+ // check join under union is spj
+ if (!checkJoinRoot(joinRoot)) {
+ return false;
+ }
+ context.joinChildList.add(joinRoot);
+
+ List<LogicalCatalogRelation> tableList = getTableListUnderJoin(joinRoot);
+ // add into table -> joinRoot map
+ for (LogicalCatalogRelation table : tableList) {
+ context.tableToJoinRootMap.put(table, joinRoot);
+ context.tableToAggrRootMap.put(table, aggrRoot);
+ }
+ if (tableListNumber == -1) {
+ tableListNumber = tableList.size();
+ } else {
+ // check all union children have the same number of tables
+ if (tableListNumber != tableList.size()) {
+ return false;
+ }
+ }
+
+ for (LogicalCatalogRelation table : tableList) {
+ // key: qualified table name
+ // value: table list in all union children
+ String qName = makeQualifiedName(table);
+ if (context.pullUpCandidatesMaps.get(qName) == null) {
+ List<LogicalCatalogRelation> newList = new ArrayList<>();
+ newList.add(table);
+ context.pullUpCandidatesMaps.put(qName, newList);
+ } else {
+ context.pullUpCandidatesMaps.get(qName).add(table);
+ }
+ }
+ }
+ int expectedNumber = union.children().size();
+ List<String> toBeRemoved = new ArrayList<>();
+ // check the pull up table candidate exists in all union children
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ if (e.getValue().size() != expectedNumber) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String key : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(key);
+ }
+ return !context.pullUpCandidatesMaps.isEmpty();
+ }
+
+ private boolean checkJoinCondition(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else if (!checkJoinConditionOnPk(joinRoot, table, context)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkGroupByKeys(PullUpContext context) {
+ List<String> toBeRemoved = new ArrayList<>();
+ for (Map.Entry<String, List<LogicalCatalogRelation>> e : context.pullUpCandidatesMaps.entrySet()) {
+ List<LogicalCatalogRelation> tableList = e.getValue();
+ boolean allFound = true;
+ for (LogicalCatalogRelation table : tableList) {
+ LogicalAggregate aggrRoot = context.tableToAggrRootMap.get(table);
+ if (aggrRoot == null) {
+ return false;
+ } else if (!checkAggrKeyOnUkOrPk(aggrRoot, table)) {
+ allFound = false;
+ break;
+ }
+ }
+ if (!allFound) {
+ toBeRemoved.add(e.getKey());
+ }
+ }
+ for (String table : toBeRemoved) {
+ context.pullUpCandidatesMaps.remove(table);
+ }
+
+ if (context.pullUpCandidatesMaps.isEmpty()) {
+ return false;
+ }
+ return true;
+ }
+
+ private boolean checkNoFilterOnPullUpTable(List<LogicalCatalogRelation> pullUpTableList, PullUpContext context) {
+ for (LogicalCatalogRelation table : pullUpTableList) {
+ LogicalJoin joinRoot = context.tableToJoinRootMap.get(table);
+ if (joinRoot == null) {
+ return false;
+ } else {
+ List<LogicalFilter> filterList = new ArrayList<>();
+ filterList.addAll((Collection<? extends LogicalFilter>)
+ joinRoot.collect(LogicalFilter.class::isInstance));
+ for (LogicalFilter filter : filterList) {
+ if (filter.child().equals(context.pullUpTable)) {
+ return false;
+ }
+ }
+ }
+ }
+ return true;
+ }
+
+ private boolean checkAggrKeyOnUkOrPk(LogicalAggregate aggregate, LogicalCatalogRelation table) {
+ List<Expression> groupByKeys = aggregate.getGroupByExpressions();
+ boolean isAllSlotReference = groupByKeys.stream().allMatch(e -> e instanceof SlotReference);
+ if (!isAllSlotReference) {
+ return false;
+ } else {
+ Set<String> ukInfo = getUkInfoFromConstraint(table);
+ Set<String> pkInfo = getPkInfoFromConstraint(table);
+ if (ukInfo == null || pkInfo == null || ukInfo.size() != 1 || pkInfo.size() != 1) {
+ return false;
+ } else {
+ String ukName = ukInfo.iterator().next();
+ String pkName = pkInfo.iterator().next();
+ for (Object expr : aggregate.getGroupByExpressions()) {
+ SlotReference slot = (SlotReference) expr;
+ if (table.getOutputExprIds().contains(slot.getExprId())
+ && (slot.getName().equals(ukName) || slot.getName().equals(pkName))) {
+ return true;
+ }
+ }
+ return false;
Review Comment:
map agg.getGroupExpressions to Slot and use `getLogicalProperty.getFunctionalDependence.isUnique()`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1863818668
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "xzj7019 (via GitHub)" <gi...@apache.org>.
xzj7019 commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1866145623
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [feature](Nereids) Pull up join from union all [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28682:
URL: https://github.com/apache/doris/pull/28682#issuecomment-1867524330
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit f6ce5d21fe39c08082f0ae66bdd806d4cac52a74, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4690 4414 4429 4414
q2 362 154 158 154
q3 1446 1276 1232 1232
q4 1127 933 929 929
q5 3147 3162 3185 3162
q6 247 129 128 128
q7 1032 487 493 487
q8 2175 2191 2173 2173
q9 6693 6690 6638 6638
q10 3214 3264 3269 3264
q11 308 199 192 192
q12 352 205 206 205
q13 4565 3845 3810 3810
q14 243 209 221 209
q15 575 541 514 514
q16 452 385 381 381
q17 1005 543 512 512
q18 7035 6768 6876 6768
q19 1528 1395 1447 1395
q20 561 333 303 303
q21 3076 2627 2666 2627
q22 351 275 281 275
Total cold run time: 44184 ms
Total hot run time: 39772 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4373 4367 4343 4343
q2 270 167 167 167
q3 3494 3492 3487 3487
q4 2405 2375 2370 2370
q5 5702 5706 5710 5706
q6 242 124 127 124
q7 2387 1851 1836 1836
q8 3514 3525 3516 3516
q9 9101 9043 8975 8975
q10 3939 4017 3993 3993
q11 487 385 370 370
q12 765 596 594 594
q13 4301 3568 3546 3546
q14 296 252 261 252
q15 584 522 521 521
q16 500 454 462 454
q17 1880 1851 1852 1851
q18 8477 8124 8213 8124
q19 1736 1752 1732 1732
q20 2257 1952 1940 1940
q21 6513 6155 6132 6132
q22 508 421 406 406
Total cold run time: 63731 ms
Total hot run time: 60439 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org