This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 8905945e7ad branch-3.1: [enhance](nereids) add eliminate order by key
by data trait #46225 (#52090)
8905945e7ad is described below
commit 8905945e7ad2e1fed09503ed5a93afe7db9637dd
Author: feiniaofeiafei <[email protected]>
AuthorDate: Wed Jul 23 12:15:03 2025 +0800
branch-3.1: [enhance](nereids) add eliminate order by key by data trait
#46225 (#52090)
cherry-pick from #46225
---
.../doris/nereids/jobs/executor/Rewriter.java | 4 +-
.../apache/doris/nereids/properties/FuncDeps.java | 20 +-
.../org/apache/doris/nereids/rules/RuleType.java | 1 +
.../nereids/rules/rewrite/EliminateOrderByKey.java | 167 +++++++
.../trees/plans/logical/LogicalOlapScan.java | 7 +
.../trees/plans/logical/LogicalProject.java | 9 +-
.../rules/rewrite/EliminateOrderByKeyTest.java | 512 +++++++++++++++++++++
.../push_down_count_through_join_one_side.out | Bin 32829 -> 32822 bytes
.../eliminate_order_by_key.out | Bin 0 -> 6764 bytes
.../limit_push_down/order_push_down.out | Bin 19633 -> 19605 bytes
.../tpcds_sf100/noStatsRfPrune/query16.out | Bin 2361 -> 2440 bytes
.../tpcds_sf100/noStatsRfPrune/query94.out | Bin 2260 -> 2339 bytes
.../tpcds_sf100/noStatsRfPrune/query95.out | Bin 3061 -> 3144 bytes
.../tpcds_sf100/noStatsRfPrune/query96.out | Bin 1450 -> 1511 bytes
.../tpcds_sf100/no_stats_shape/query16.out | Bin 2361 -> 2440 bytes
.../tpcds_sf100/no_stats_shape/query94.out | Bin 2260 -> 2339 bytes
.../tpcds_sf100/no_stats_shape/query95.out | Bin 3490 -> 3573 bytes
.../tpcds_sf100/no_stats_shape/query96.out | Bin 1450 -> 1511 bytes
.../shape_check/tpcds_sf100/rf_prune/query16.out | Bin 2373 -> 2452 bytes
.../shape_check/tpcds_sf100/rf_prune/query94.out | Bin 2272 -> 2351 bytes
.../shape_check/tpcds_sf100/rf_prune/query95.out | Bin 2984 -> 3067 bytes
.../shape_check/tpcds_sf100/rf_prune/query96.out | Bin 1450 -> 1511 bytes
.../data/shape_check/tpcds_sf100/shape/query16.out | Bin 2373 -> 2452 bytes
.../data/shape_check/tpcds_sf100/shape/query94.out | Bin 2272 -> 2351 bytes
.../data/shape_check/tpcds_sf100/shape/query95.out | Bin 3381 -> 3464 bytes
.../data/shape_check/tpcds_sf100/shape/query96.out | Bin 1450 -> 1511 bytes
.../tpcds_sf1000/bs_downgrade_shape/query95.out | Bin 3037 -> 3120 bytes
.../data/shape_check/tpcds_sf1000/hint/query16.out | Bin 2489 -> 2568 bytes
.../data/shape_check/tpcds_sf1000/hint/query94.out | Bin 2335 -> 2414 bytes
.../data/shape_check/tpcds_sf1000/hint/query95.out | Bin 3037 -> 3120 bytes
.../data/shape_check/tpcds_sf1000/hint/query96.out | Bin 1549 -> 1610 bytes
.../shape_check/tpcds_sf1000/shape/query16.out | Bin 2381 -> 2460 bytes
.../shape_check/tpcds_sf1000/shape/query94.out | Bin 2335 -> 2414 bytes
.../shape_check/tpcds_sf1000/shape/query95.out | Bin 3037 -> 3120 bytes
.../shape_check/tpcds_sf1000/shape/query96.out | Bin 1450 -> 1511 bytes
.../shape_check/tpcds_sf10t_orc/shape/query16.out | Bin 2361 -> 2440 bytes
.../shape_check/tpcds_sf10t_orc/shape/query31.out | Bin 4563 -> 4280 bytes
.../shape_check/tpcds_sf10t_orc/shape/query94.out | Bin 2254 -> 2333 bytes
.../shape_check/tpcds_sf10t_orc/shape/query95.out | Bin 3024 -> 3107 bytes
.../shape_check/tpcds_sf10t_orc/shape/query96.out | Bin 1451 -> 1512 bytes
.../push_down_count_through_join_one_side.groovy | 1 +
.../eliminate_order_by_key.groovy | 193 ++++++++
42 files changed, 908 insertions(+), 6 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index 26e7b14242f..ec3a0ed2ec7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -69,6 +69,7 @@ import org.apache.doris.nereids.rules.rewrite.EliminateLimit;
import org.apache.doris.nereids.rules.rewrite.EliminateNotNull;
import org.apache.doris.nereids.rules.rewrite.EliminateNullAwareLeftAntiJoin;
import org.apache.doris.nereids.rules.rewrite.EliminateOrderByConstant;
+import org.apache.doris.nereids.rules.rewrite.EliminateOrderByKey;
import org.apache.doris.nereids.rules.rewrite.EliminateSemiJoin;
import org.apache.doris.nereids.rules.rewrite.EliminateSort;
import org.apache.doris.nereids.rules.rewrite.EliminateSortUnderSubqueryOrView;
@@ -340,7 +341,8 @@ public class Rewriter extends AbstractBatchJobExecutor {
),
// this rule should invoke after ColumnPruning
custom(RuleType.ELIMINATE_UNNECESSARY_PROJECT,
EliminateUnnecessaryProject::new),
-
+ topic("Eliminate Order By Key",
+ topDown(new EliminateOrderByKey())),
topic("Eliminate GroupBy",
topDown(new EliminateGroupBy(),
new MergeAggregate())
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
index 5eebf08ddd7..dcd9673b5ef 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
@@ -33,9 +33,10 @@ import java.util.stream.Collectors;
* Function dependence items.
*/
public class FuncDeps {
- static class FuncDepsItem {
- final Set<Slot> determinants;
- final Set<Slot> dependencies;
+ /**FuncDepsItem*/
+ public static class FuncDepsItem {
+ public final Set<Slot> determinants;
+ public final Set<Slot> dependencies;
public FuncDepsItem(Set<Slot> determinants, Set<Slot> dependencies) {
this.determinants = ImmutableSet.copyOf(determinants);
@@ -64,16 +65,21 @@ public class FuncDeps {
private final Set<FuncDepsItem> items;
// determinants -> dependencies
private final Map<Set<Slot>, Set<Set<Slot>>> edges;
+ // dependencies -> determinants
+ private final Map<Set<Slot>, Set<Set<Slot>>> redges;
public FuncDeps() {
items = new HashSet<>();
edges = new HashMap<>();
+ redges = new HashMap<>();
}
public void addFuncItems(Set<Slot> determinants, Set<Slot> dependencies) {
items.add(new FuncDepsItem(determinants, dependencies));
edges.computeIfAbsent(determinants, k -> new HashSet<>());
edges.get(determinants).add(dependencies);
+ redges.computeIfAbsent(dependencies, k -> new HashSet<>());
+ redges.get(dependencies).add(determinants);
}
public int size() {
@@ -185,6 +191,14 @@ public class FuncDeps {
return items;
}
+ public Map<Set<Slot>, Set<Set<Slot>>> getEdges() {
+ return edges;
+ }
+
+ public Map<Set<Slot>, Set<Set<Slot>>> getREdges() {
+ return redges;
+ }
+
/**
* find the determinants of dependencies
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
index 4b65c79a848..7d74dedcd82 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
@@ -252,6 +252,7 @@ public enum RuleType {
ELIMINATE_JOIN_BY_FK(RuleTypeClass.REWRITE),
ELIMINATE_GROUP_BY_KEY(RuleTypeClass.REWRITE),
ELIMINATE_GROUP_BY_KEY_BY_UNIFORM(RuleTypeClass.REWRITE),
+ ELIMINATE_ORDER_BY_KEY(RuleTypeClass.REWRITE),
ELIMINATE_FILTER_GROUP_BY_KEY(RuleTypeClass.REWRITE),
ELIMINATE_DEDUP_JOIN_CONDITION(RuleTypeClass.REWRITE),
ELIMINATE_NULL_AWARE_LEFT_ANTI_JOIN(RuleTypeClass.REWRITE),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKey.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKey.java
new file mode 100644
index 00000000000..d4ede4deafe
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKey.java
@@ -0,0 +1,167 @@
+// 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.Type;
+import org.apache.doris.nereids.annotation.DependsRules;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.properties.DataTrait;
+import org.apache.doris.nereids.properties.FuncDeps;
+import org.apache.doris.nereids.properties.OrderKey;
+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.Expression;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.OrderExpression;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.WindowExpression;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
+import org.apache.doris.nereids.trees.plans.logical.LogicalWindow;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * 1.eliminate by duplicate
+ * select a from t1 order by a, a;
+ * ->
+ * select a from t1 order by a;
+ * 2.eliminate by function dependency
+ * select a from t1 order by a, a+1;
+ * select a from t1 order by a, abs(a) ;
+ * select a from t1 where a=c order by a,c
+ * ->
+ * select a from t1 order by a;
+ * 3.eliminate by uniform
+ * select a,b,c from test where a=1 order by a;
+ * ->
+ * select a,b,c from test where a=1;
+ * */
+@DependsRules({
+ NormalizeSort.class,
+ ExtractAndNormalizeWindowExpression.class,
+ CheckAndStandardizeWindowFunctionAndFrame.class})
+public class EliminateOrderByKey implements RewriteRuleFactory {
+ @Override
+ public List<Rule> buildRules() {
+ return ImmutableList.of(
+
logicalSort(any()).then(EliminateOrderByKey::eliminateSort).toRule(RuleType.ELIMINATE_ORDER_BY_KEY),
+ logicalWindow(any()).then(EliminateOrderByKey::eliminateWindow)
+ .toRule(RuleType.ELIMINATE_ORDER_BY_KEY));
+ }
+
+ private static Plan eliminateWindow(LogicalWindow<Plan> window) {
+ DataTrait dataTrait = window.child().getLogicalProperties().getTrait();
+ List<NamedExpression> newNamedExpressions = new ArrayList<>();
+ boolean changed = false;
+ for (NamedExpression expr : window.getWindowExpressions()) {
+ Alias alias = (Alias) expr;
+ WindowExpression windowExpression = (WindowExpression)
alias.child();
+ List<OrderExpression> orderExpressions =
windowExpression.getOrderKeys();
+ if (orderExpressions.stream().anyMatch((
+ orderKey -> orderKey.getDataType().isOnlyMetricType()))) {
+ throw new AnalysisException(Type.OnlyMetricTypeErrorMsg);
+ }
+ List<OrderKey> orderKeys = new ArrayList<>();
+ for (OrderExpression orderExpression : orderExpressions) {
+ orderKeys.add(orderExpression.getOrderKey());
+ }
+ List<OrderKey> retainExpression = eliminate(dataTrait, orderKeys);
+ if (retainExpression.size() == orderKeys.size()) {
+ newNamedExpressions.add(expr);
+ continue;
+ }
+ changed = true;
+ List<OrderExpression> newOrderExpressions = new ArrayList<>();
+ for (OrderKey orderKey : retainExpression) {
+ newOrderExpressions.add(new OrderExpression(orderKey));
+ }
+ WindowExpression newWindowExpression =
windowExpression.withOrderKeys(newOrderExpressions);
+
newNamedExpressions.add(alias.withChildren(ImmutableList.of(newWindowExpression)));
+ }
+ return changed ? window.withExpressionsAndChild(newNamedExpressions,
window.child()) : window;
+ }
+
+ private static Plan eliminateSort(LogicalSort<Plan> sort) {
+ DataTrait dataTrait = sort.child().getLogicalProperties().getTrait();
+ List<OrderKey> retainExpression = eliminate(dataTrait,
sort.getOrderKeys());
+ if (retainExpression.isEmpty()) {
+ return sort.child();
+ } else if (retainExpression.size() == sort.getOrderKeys().size()) {
+ return sort;
+ }
+ return sort.withOrderKeys(retainExpression);
+ }
+
+ private static List<OrderKey> eliminate(DataTrait dataTrait,
List<OrderKey> inputOrderKeys) {
+ Set<Slot> validSlots = new HashSet<>();
+ for (OrderKey inputOrderKey : inputOrderKeys) {
+ Expression expr = inputOrderKey.getExpr();
+ if (!(expr instanceof Slot)) {
+ return inputOrderKeys;
+ }
+ validSlots.add((Slot) expr);
+ validSlots.addAll(dataTrait.calEqualSet((Slot) expr));
+ }
+ FuncDeps funcDeps = dataTrait.getAllValidFuncDeps(validSlots);
+ Map<Set<Slot>, Set<Set<Slot>>> redges = funcDeps.getREdges();
+
+ List<OrderKey> retainExpression = new ArrayList<>();
+ Set<Expression> orderExprWithEqualSet = new HashSet<>();
+ for (OrderKey inputOrderKey : inputOrderKeys) {
+ Expression expr = inputOrderKey.getExpr();
+ // eliminate by duplicate
+ if (orderExprWithEqualSet.contains(expr)) {
+ continue;
+ }
+ // eliminate by uniform
+ if (dataTrait.isUniformAndNotNull((Slot) expr)) {
+ orderExprWithEqualSet.add(expr);
+ orderExprWithEqualSet.addAll(dataTrait.calEqualSet((Slot)
expr));
+ continue;
+ }
+ // eliminate by fd
+ Set<Slot> set = ImmutableSet.of((Slot) expr);
+ boolean shouldRetain = true;
+ if (redges.containsKey(set)) {
+ Set<Set<Slot>> dominants = redges.get(set);
+ for (Set<Slot> dominant : dominants) {
+ if (orderExprWithEqualSet.containsAll(dominant)) {
+ shouldRetain = false;
+ break;
+ }
+ }
+ }
+ if (!shouldRetain) {
+ continue;
+ }
+ retainExpression.add(inputOrderKey);
+ orderExprWithEqualSet.add(expr);
+ orderExprWithEqualSet.addAll(dataTrait.calEqualSet((Slot) expr));
+ }
+ return retainExpression;
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalOlapScan.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalOlapScan.java
index bcd1778fadb..83b5acb7c35 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalOlapScan.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalOlapScan.java
@@ -18,6 +18,7 @@
package org.apache.doris.nereids.trees.plans.logical;
import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.KeysType;
import org.apache.doris.catalog.MTMV;
import org.apache.doris.catalog.OlapTable;
import org.apache.doris.catalog.Table;
@@ -547,6 +548,12 @@ public class LogicalOlapScan extends
LogicalCatalogRelation implements OlapScan
builder.addUniqueSlot(originalPlan.getLogicalProperties().getTrait());
builder.replaceUniqueBy(constructReplaceMap(mtmv));
} else if (getTable().getKeysType().isAggregationFamily() &&
!getTable().isRandomDistribution()) {
+ // When skipDeleteBitmap is set to true, in the unique model, rows
that are replaced due to having the same
+ // unique key will also be read. As a result, the uniqueness of
the unique key cannot be guaranteed.
+ if (ConnectContext.get().getSessionVariable().skipDeleteBitmap
+ && getTable().getKeysType() == KeysType.UNIQUE_KEYS) {
+ return;
+ }
ImmutableSet.Builder<Slot> uniqSlots =
ImmutableSet.builderWithExpectedSize(outputSet.size());
for (Slot slot : outputSet) {
if (!(slot instanceof SlotReference)) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalProject.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalProject.java
index 159552b4653..c33ec52ecde 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalProject.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalProject.java
@@ -287,9 +287,14 @@ public class LogicalProject<CHILD_TYPE extends Plan>
extends LogicalUnary<CHILD_
public void computeFd(DataTrait.Builder builder) {
builder.addFuncDepsDG(child().getLogicalProperties().getTrait());
for (NamedExpression expr : getProjects()) {
- if (!expr.isSlot()) {
- builder.addDeps(expr.getInputSlots(),
ImmutableSet.of(expr.toSlot()));
+ if (!(expr instanceof Alias)) {
+ continue;
+ }
+ // a+random(1,10) should continue, otherwise the a(determinant),
a+random(1,10) (dependency) will be added.
+ if (expr.containsNonfoldable()) {
+ continue;
}
+ builder.addDeps(expr.getInputSlots(),
ImmutableSet.of(expr.toSlot()));
}
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKeyTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKeyTest.java
new file mode 100644
index 00000000000..5da987237b5
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateOrderByKeyTest.java
@@ -0,0 +1,512 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.trees.expressions.WindowExpression;
+import org.apache.doris.nereids.util.MemoPatternMatchSupported;
+import org.apache.doris.nereids.util.PlanChecker;
+import org.apache.doris.utframe.TestWithFeService;
+
+import org.junit.jupiter.api.Test;
+
+public class EliminateOrderByKeyTest extends TestWithFeService implements
MemoPatternMatchSupported {
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ createTable("create table test.eliminate_order_by_constant_t(a int
null, b int not null,"
+ + "c varchar(10) null, d date, dt datetime, id int)\n"
+ + "distributed by hash(a)
properties(\"replication_num\"=\"1\");");
+ createTable("create table test.eliminate_order_by_constant_t2(a int, b
int, c int, d int) "
+ + "distributed by hash(a)
properties(\"replication_num\"=\"1\");");
+ createTable("create table test.test_unique_order_by2(a int not null, b
int not null, c int, d int) "
+ + "unique key(a,b) distributed by hash(a)
properties('replication_num'='1');");
+ connectContext.setDatabase("test");
+
connectContext.getSessionVariable().setDisableNereidsRules("PRUNE_EMPTY_PARTITION,DEFER_MATERIALIZE_TOP_N_RESULT");
+ }
+
+ @Test
+ void testEliminateByCompositeKeys() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from test_unique_order_by2 order by
a,'abc',d,b,d,c")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+ }
+
+ @Test
+ void testEliminateByFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from
test.eliminate_order_by_constant_t order by a,abs(a),a+1")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByFdAndDup() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,abs(a),a,abs(a),a+1")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByFdTopN() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,abs(a),a+1 limit 5")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalTopN().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByFdAndDupTopN() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,abs(a),a,abs(a),a+1,id limit 5")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalTopN().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+
+ @Test
+ void testEliminateByDup() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,a")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByDupExpr() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a+1,a+1")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByDupTopN() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,a limit 5")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalTopN().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByUniformPredicate() {
+ PlanChecker.from(connectContext)
+ .analyze("select 1 as c1,a from eliminate_order_by_constant_t
where a=1 order by a")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalSort());
+ }
+
+ @Test
+ void testEliminateByUniformWithAgg() {
+ PlanChecker.from(connectContext)
+ .analyze("select 1 as c1,a from eliminate_order_by_constant_t
where a=1 group by c1,a order by a")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalSort());
+ }
+
+ @Test
+ void testEliminateByUniformMultiKey() {
+ PlanChecker.from(connectContext)
+ .analyze("select 1 as c1,a,b,c from
eliminate_order_by_constant_t where a=1 order by a,'abc',b,c1")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEliminateByUniformTopN() {
+ PlanChecker.from(connectContext)
+ .analyze("select 1 as c1,a,b,c from
eliminate_order_by_constant_t where a=1 order by a,'abc',c1 limit 5")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalTopN());
+ }
+
+ @Test
+ void notEliminateNonDeterministic() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,a+random(1,10)")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+
+ @Test
+ void testMultiColumnFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,b,a+b")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+
+ @Test
+ void testMultiColumnFdWithOtherInMiddle() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,c,b,a+b")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+ }
+
+ @Test
+ void testWindowDup() {
+ PlanChecker.from(connectContext)
+ .analyze("select sum(a) over (partition by a order by a,a)
from eliminate_order_by_constant_t")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalWindow()
+ .when(window -> ((WindowExpression)
window.getWindowExpressions().get(0).child(0))
+ .getOrderKeys().size() == 1));
+ }
+
+ @Test
+ void testWindowFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select sum(a) over (partition by a order by
a,a+1,abs(a),1-a,b) from eliminate_order_by_constant_t")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalWindow()
+ .when(window -> ((WindowExpression)
window.getWindowExpressions().get(0).child(0))
+ .getOrderKeys().size() == 2));
+ }
+
+ @Test
+ void testWindowUniform() {
+ PlanChecker.from(connectContext)
+ .analyze("select sum(a) over (partition by a order by b) from
eliminate_order_by_constant_t where b=100")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalWindow()
+ .when(window -> ((WindowExpression)
window.getWindowExpressions().get(0).child(0))
+ .getOrderKeys().isEmpty()));
+ }
+
+ @Test
+ void testWindowMulti() {
+ PlanChecker.from(connectContext)
+ .analyze("select sum(a) over (partition by a order by
a,a+1,abs(a),1-a,b)"
+ + ", max(a) over (partition by a order by
b,b+1,b,abs(b)) from eliminate_order_by_constant_t")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalWindow()
+ .when(window -> ((WindowExpression)
window.getWindowExpressions().get(0).child(0))
+ .getOrderKeys().size() == 2
+ && ((WindowExpression)
window.getWindowExpressions().get(1).child(0))
+ .getOrderKeys().size() == 1));
+ }
+
+ @Test
+ void testWindowMultiDesc() {
+ PlanChecker.from(connectContext)
+ .analyze("select sum(a) over (partition by a order by a
desc,a+1 asc,abs(a) desc,1-a,b),"
+ + "max(a) over (partition by a order by b desc,b+1
desc,b asc,abs(b) desc) "
+ + "from eliminate_order_by_constant_t")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalWindow()
+ .when(window -> ((WindowExpression)
window.getWindowExpressions().get(0).child(0))
+ .getOrderKeys().size() == 2
+ && ((WindowExpression)
window.getWindowExpressions().get(1).child(0))
+ .getOrderKeys().size() == 1));
+ }
+
+ @Test
+ void testEqualSet() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t where
a=id order by a,id")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t where
a=id order by id,a")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t where
a=id and a=b order by id,a,b")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testEqualSetAndFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b,c,d,dt from eliminate_order_by_constant_t
where a=b order by a,a+b, b ")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testUniformAndFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select a,b from eliminate_order_by_constant_t where
a=b and a=1 order by a,b,a+b")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalSort());
+ }
+
+ @Test
+ void testUniformAndFd2() {
+ PlanChecker.from(connectContext)
+ .analyze("select a from eliminate_order_by_constant_t where
a=1 order by a,b,b,abs(a),a ")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ @Test
+ void testFdValidSlotsAddEqualSet() {
+ PlanChecker.from(connectContext)
+ .analyze("select c,d,a,a+100,b+a+100,b from
eliminate_order_by_constant_t where b=a order by c,d,a,a+100,b+a+100")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+
+ // TODO After removing b from the projection column, b+a+100 cannot be
deleted from sort.
+ // This is because the equal set after the project does not output
a=b because the b projection column does not output it.
+ PlanChecker.from(connectContext)
+ .analyze("select c,d,a,a+100,b+a+100 from
eliminate_order_by_constant_t where b=a order by c,d,a,a+100,b+a+100")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 4));
+ }
+
+ @Test
+ void testEqualSetUniform() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t2 where
b=a and a=c and d=1 order by d,a,b,c,c,b,a,d,d")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t2 where
b=a and a=d and a=c and d=1 order by d,a,b,c,c,b,a,d,d")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalSort());
+ }
+
+ @Test
+ void testEqualSetUniformFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t2 where
b=a and a=d and a=c and d=1 order by d,a,a+1,b+1,c+1,c,b,a,d,d")
+ .rewrite()
+ .printlnTree()
+ .nonMatch(logicalSort());
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t2 where
d=1 order by d,a,b,c,d+b+a-100,d+b+a,b,a,d,d")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+ }
+
+ @Test
+ void testEqualSetFd() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from eliminate_order_by_constant_t2 where
d=b and a=d order by a,c,d+b+a,b,a,d,d+1,abs(d)")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+
+ @Test
+ void testInnerJoinEqual() {
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t2.a,t1.b, t2.c, t2.d from
eliminate_order_by_constant_t t1 "
+ + "inner join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a "
+ + "order by t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d,
t1.a+1, t2.c;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 4));
+ }
+
+ @Test
+ void testLeftJoinEqual() {
+ // t1.a=t2.a but should not eliminate
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b, t2.c, t2.d from
eliminate_order_by_constant_t t1 "
+ + "left outer join eliminate_order_by_constant_t2 t2
on t1.a = t2.a "
+ + "order by t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d,
t1.a+1, t2.c;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 5));
+ // left is converted to inner join
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b, t2.c, t2.d from
eliminate_order_by_constant_t t1 "
+ + "left outer join eliminate_order_by_constant_t2 t2
on t1.a = t2.a where t2.c=100 "
+ + "order by t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d,
t1.a+1, t2.c;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+ }
+
+ @Test
+ void testRightJoinEqual() {
+ // right join and has uniform
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b, t2.c, t2.d from
eliminate_order_by_constant_t t1"
+ + " right outer join eliminate_order_by_constant_t2 t2
on t1.a = t2.a where t2.a=1 "
+ + "order by t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d,
t1.a+1, t2.c;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 4));
+ }
+
+ @Test
+ void testSemiAntiJoin() {
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b from eliminate_order_by_constant_t
t1 "
+ + "left semi join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a order by t1.a, t1.b, t1.a+1,t1.a+t1.b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ PlanChecker.from(connectContext)
+ .analyze("select t2.a, t2.b from eliminate_order_by_constant_t
t1 "
+ + " right semi join eliminate_order_by_constant_t2 t2
on t1.a = t2.a "
+ + " order by t2.a, t2.b, t2.a+1,t2.a+t2.b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b "
+ + "from eliminate_order_by_constant_t t1 "
+ + "left anti join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a "
+ + "order by t1.a, t1.b, t1.a+1,t1.a+t1.b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ PlanChecker.from(connectContext)
+ .analyze("select t2.a, t2.b"
+ + " from eliminate_order_by_constant_t t1"
+ + " right anti join eliminate_order_by_constant_t2 t2
on t1.a = t2.a"
+ + " order by t2.a, t2.b, t2.a+1,t2.a+t2.b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+
+ @Test
+ void testAgg() {
+ PlanChecker.from(connectContext)
+ .analyze("select a, count(b) as cnt "
+ + "from eliminate_order_by_constant_t2 "
+ + "group by a "
+ + "order by a, cnt, a,a+cnt,a+100;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+
+ PlanChecker.from(connectContext)
+ .analyze("select a, b, count(c) as cnt"
+ + " from eliminate_order_by_constant_t2"
+ + " group by cube(a, b)"
+ + " order by a, b, cnt, a, b+1;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 3));
+ }
+
+ @Test
+ void testJoinWindow() {
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b, t2.c, t2.d, t2.a,"
+ + " row_number() over (partition by t1.a order by
t1.b) as rn"
+ + " from eliminate_order_by_constant_t t1"
+ + " inner join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a"
+ + " order by t1.a, t2.a,t2.c, t1.b, t2.d, abs(t1.a),
abs(t2.a), t2.c,rn,rn+100;;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 5));
+ PlanChecker.from(connectContext)
+ .analyze("select a, b, count(c) as cnt, "
+ + " row_number() over (partition by a order by b) as
rn "
+ + " from eliminate_order_by_constant_t2 "
+ + " group by a, b "
+ + " order by a, b, cnt, a+100, b, rn, rn+cnt,
abs(rn+cnt);")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 4));
+ }
+
+ @Test
+ void testAggWindowJoin() {
+ PlanChecker.from(connectContext)
+ .analyze("select t1.a, t1.b, count(t2.c) as cnt,"
+ + " row_number() over (partition by t1.a order by
t1.b) as rn"
+ + " from eliminate_order_by_constant_t t1"
+ + " inner join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a"
+ + " group by t1.a, t1.b ,t2.a"
+ + " order by t1.a,t2.a,t1.b, cnt, -t1.a,
-t1.b-1000,rn, cnt, rn+111;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 4));
+ }
+
+ @Test
+ void testUnionAll() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from ("
+ + " select a, b from eliminate_order_by_constant_t2
"
+ + " union all "
+ + " select a, b from eliminate_order_by_constant_t
) t "
+ + " order by a, b, abs(a),abs(a)+b,a+b,a,b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ PlanChecker.from(connectContext)
+ .analyze("select * from ("
+ + " select a, b from eliminate_order_by_constant_t2
"
+ + " union all "
+ + " select a, b from eliminate_order_by_constant_t
) t "
+ + " where a=b order by a, b,
abs(a),abs(a)+b,a+b,a,b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 1));
+ }
+
+ // TODO LogicalUnion compute uniform can expand support scope when each
child has same uniform output
+ // and corresponding same position
+ @Test
+ void testUnionJoin() {
+ PlanChecker.from(connectContext)
+ .analyze("select * from (select t1.a, t1.b "
+ + " from eliminate_order_by_constant_t t1 "
+ + " inner join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a "
+ + " union "
+ + " select t1.a, t1.b "
+ + " from eliminate_order_by_constant_t t1 "
+ + " left join eliminate_order_by_constant_t2 t2 on
t1.a = t2.a ) t"
+ + " where a=1"
+ + " order by a, b, a+100,abs(a)+b;")
+ .rewrite()
+ .printlnTree()
+ .matches(logicalSort().when(sort -> sort.getOrderKeys().size()
== 2));
+ }
+}
diff --git
a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.out
b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.out
index 1f546d131d3..ea92b203dcf 100644
Binary files
a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.out
and
b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.out
differ
diff --git
a/regression-test/data/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.out
b/regression-test/data/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.out
new file mode 100644
index 00000000000..5ba1fcce552
Binary files /dev/null and
b/regression-test/data/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.out
differ
diff --git
a/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
index 694e609eaeb..0b450dc89b4 100644
Binary files
a/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out and
b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query16.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query16.out
index c6e88456a7e..02559f87cb3 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query16.out and
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query16.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query94.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query94.out
index 75a20cb4e35..d26128a78f3 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query94.out and
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query94.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query95.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query95.out
index fce09b1b604..4435e9fb23d 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query95.out and
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query95.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query96.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query96.out
index 11217d6de3e..9028fb34a8d 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query96.out and
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query96.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query16.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query16.out
index c6e88456a7e..02559f87cb3 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query16.out and
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query16.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query94.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query94.out
index 75a20cb4e35..d26128a78f3 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query94.out and
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query94.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query95.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query95.out
index aaaba243279..253f14febe6 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query95.out and
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query95.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query96.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query96.out
index 11217d6de3e..9028fb34a8d 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query96.out and
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query96.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query16.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query16.out
index 1733e793f1f..b1b5037f23d 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query16.out and
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query16.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query94.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query94.out
index 6a25137bf51..df854654271 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query94.out and
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query94.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query95.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query95.out
index ad87360cc8a..623fe7152ec 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query95.out and
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query95.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query96.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query96.out
index c50ffa373c8..a7d441a3e3d 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query96.out and
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query96.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query16.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query16.out
index 1733e793f1f..b1b5037f23d 100644
Binary files a/regression-test/data/shape_check/tpcds_sf100/shape/query16.out
and b/regression-test/data/shape_check/tpcds_sf100/shape/query16.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query94.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query94.out
index 6a25137bf51..df854654271 100644
Binary files a/regression-test/data/shape_check/tpcds_sf100/shape/query94.out
and b/regression-test/data/shape_check/tpcds_sf100/shape/query94.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query95.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query95.out
index 269d330c090..2e4d4278eaa 100644
Binary files a/regression-test/data/shape_check/tpcds_sf100/shape/query95.out
and b/regression-test/data/shape_check/tpcds_sf100/shape/query95.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query96.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query96.out
index c50ffa373c8..a7d441a3e3d 100644
Binary files a/regression-test/data/shape_check/tpcds_sf100/shape/query96.out
and b/regression-test/data/shape_check/tpcds_sf100/shape/query96.out differ
diff --git
a/regression-test/data/shape_check/tpcds_sf1000/bs_downgrade_shape/query95.out
b/regression-test/data/shape_check/tpcds_sf1000/bs_downgrade_shape/query95.out
index 21c6fa60d37..84bdd6cf3d8 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf1000/bs_downgrade_shape/query95.out
and
b/regression-test/data/shape_check/tpcds_sf1000/bs_downgrade_shape/query95.out
differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query16.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query16.out
index 247f0f8c777..391b7d7cc41 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/hint/query16.out
and b/regression-test/data/shape_check/tpcds_sf1000/hint/query16.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query94.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query94.out
index 0f35f2dc29e..bd89dd58eff 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/hint/query94.out
and b/regression-test/data/shape_check/tpcds_sf1000/hint/query94.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query95.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query95.out
index 21c6fa60d37..84bdd6cf3d8 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/hint/query95.out
and b/regression-test/data/shape_check/tpcds_sf1000/hint/query95.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query96.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query96.out
index 188b18e2bc0..465d33cd653 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/hint/query96.out
and b/regression-test/data/shape_check/tpcds_sf1000/hint/query96.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query16.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query16.out
index db5bf9b39a0..21dcd5cf6df 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/shape/query16.out
and b/regression-test/data/shape_check/tpcds_sf1000/shape/query16.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query94.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query94.out
index 0f35f2dc29e..bd89dd58eff 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/shape/query94.out
and b/regression-test/data/shape_check/tpcds_sf1000/shape/query94.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query95.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query95.out
index 21c6fa60d37..84bdd6cf3d8 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/shape/query95.out
and b/regression-test/data/shape_check/tpcds_sf1000/shape/query95.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query96.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query96.out
index b4b739a9bf4..7e3d08cf261 100644
Binary files a/regression-test/data/shape_check/tpcds_sf1000/shape/query96.out
and b/regression-test/data/shape_check/tpcds_sf1000/shape/query96.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query16.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query16.out
index e9d35f9e131..ff30193149f 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query16.out and
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query16.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query31.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query31.out
index 177a0397587..f6b0f237d63 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query31.out and
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query31.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query94.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query94.out
index 792f183626c..2f12dce9e26 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query94.out and
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query94.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query95.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query95.out
index 4146fd70b83..140ec5cb678 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query95.out and
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query95.out differ
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query96.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query96.out
index 5b0549e0eb3..297341a8714 100644
Binary files
a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query96.out and
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query96.out differ
diff --git
a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.groovy
b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.groovy
index 72f695d75e1..b2ae28153ae 100644
---
a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.groovy
+++
b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join_one_side.groovy
@@ -23,6 +23,7 @@ suite("push_down_count_through_join_one_side") {
sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
sql "set be_number_for_test=1"
sql "set DISABLE_NEREIDS_RULES='ONE_PHASE_AGGREGATE_WITHOUT_DISTINCT,
ONE_PHASE_AGGREGATE_SINGLE_DISTINCT_TO_MULTI'"
+ sql "set topn_opt_limit_threshold=1024"
sql """
DROP TABLE IF EXISTS count_t_one_side;
"""
diff --git
a/regression-test/suites/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.groovy
b/regression-test/suites/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.groovy
new file mode 100644
index 00000000000..15a9ad86f8c
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/eliminate_order_by_key/eliminate_order_by_key.groovy
@@ -0,0 +1,193 @@
+// 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.
+
+suite("eliminate_order_by_key") {
+ sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
+ sql "drop table if exists eliminate_order_by_constant_t"
+ sql """create table eliminate_order_by_constant_t(a int null, b int not
null, c varchar(10) null, d date, dt datetime, id int)
+ distributed by hash(a) properties("replication_num"="1");
+ """
+// sql "set disable_nereids_rules='eliminate_order_by_key'"
+ sql """
+ INSERT INTO eliminate_order_by_constant_t (a, b, c, d, dt,id) VALUES
+ (1, 100, 'apple', '2023-01-01', '2023-01-01 10:00:00',1),
+ (1, 100, 'apple', '2023-01-01', '2023-01-01 10:00:00',2),
+ (2, 101, 'banana', '2023-01-02', '2023-01-02 11:00:00',3),
+ (3, 102, 'cherry', '2023-01-03', '2023-01-03 12:00:00',4),
+ (3, 102, 'cherry', '2023-01-03', '2023-01-03 12:00:00',5),
+ (NULL, 103, 'date', '2023-01-04', '2023-01-04 13:00:00',6),
+ (4, 104, 'elderberry', '2023-01-05', '2023-01-05 14:00:00',7),
+ (5, 105, NULL, '2023-01-06', '2023-01-06 15:00:00',8),
+ (5, 105, NULL, '2023-01-06', '2023-01-06 15:00:00',9),
+ (6, 106, 'fig', '2023-01-07', '2023-01-07 16:00:00',10),
+ (NULL, 107, 'grape', '2023-01-08', '2023-01-08 17:00:00',11);
+ """
+ qt_predicate "select 1 as c1,a from eliminate_order_by_constant_t where
a=1 order by a"
+ qt_predicate_order_by_two "select 1 as c1,a from
eliminate_order_by_constant_t where a=1 order by a,c1"
+ qt_with_group_by """select 1 as c1,a from eliminate_order_by_constant_t
where a=1 group by c1,a order by a"""
+ qt_predicate_multi_other """select 1 as c1,a,b,c from
eliminate_order_by_constant_t where a=1 order by a,'abc',b,c"""
+ qt_with_group_by_shape """explain shape plan select 1 as c1,a from
eliminate_order_by_constant_t where a=1 group by c1,a order by a"""
+
+ // fd
+ qt_fd "select a,b,c,d,dt from eliminate_order_by_constant_t order by
a,abs(a),a+1,id"
+ qt_fd_duplicate "select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,abs(a),a,abs(a),a+1,id"
+ qt_fd_topn "select a,b,c,d,dt from eliminate_order_by_constant_t order by
a,abs(a),a+1,id limit 5"
+ qt_fd_duplicate_topn "select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,abs(a),a,abs(a),a+1,id limit 5"
+ qt_fd_multi_column "select a,b,c,d,dt from eliminate_order_by_constant_t
order by a,b,a+b"
+ qt_fd_desc "select a,b,c,d,dt from eliminate_order_by_constant_t order by
a desc,abs(a) asc,a+1 desc,id"
+ qt_fd_multi_column_desc "select a,b,c,d,dt from
eliminate_order_by_constant_t order by a,b desc,a+b"
+ qt_fd_multi_column_desc_with_other_in_middle "select a,b,c,d,dt from
eliminate_order_by_constant_t order by a,c,b desc,a+b asc"
+ qt_equalset_fd "select a,b,c,d,dt from eliminate_order_by_constant_t
where a=b order by a,a+b, b"
+ qt_uniform_fd "select a,b from eliminate_order_by_constant_t where a=b and
a=1 order by a,b,a+b"
+ qt_fd_valid_slot_add_equalset "select c,d,a,a+100,b+a+100,b from
eliminate_order_by_constant_t where b=a order by c,d,a,a+100,b+a+100"
+
+ // duplicate
+ qt_dup_shape "select a,b,c,d,dt from eliminate_order_by_constant_t order
by a,a,id"
+ qt_dup_expr_shape "select a,b,c,d,dt from eliminate_order_by_constant_t
order by a+1,a+1,id"
+
+ // window
+ qt_dup_window "select sum(a) over (partition by a order by a,a) from
eliminate_order_by_constant_t order by 1"
+ qt_fd_window "select sum(a) over (partition by a order by
a,a+1,abs(a),1-a,b) from eliminate_order_by_constant_t order by 1"
+ qt_uniform_window "select sum(a) over (partition by a order by b) from
eliminate_order_by_constant_t where b=100 order by 1"
+ qt_uniform_window "select first_value(c) over (partition by a order by b)
from eliminate_order_by_constant_t where b=100 order by 1"
+ qt_multi_window """select sum(a) over (partition by a order by
a,a+1,abs(a),1-a,b), max(a) over (partition by a order by b,b+1,b,abs(b))
+ from eliminate_order_by_constant_t order by 1,2"""
+ qt_multi_window_desc """select sum(a) over (partition by a order by a
desc,a+1 asc,abs(a) desc,1-a,b), max(a) over (partition by a order by b
desc,b+1 desc,b asc,abs(b) desc)
+ from eliminate_order_by_constant_t order by 1,2"""
+
+
+ sql "drop table if exists eliminate_order_by_constant_t2"
+ sql """create table eliminate_order_by_constant_t2(a int, b int, c int, d
int) distributed by hash(a) properties("replication_num"="1");"""
+ sql """INSERT INTO eliminate_order_by_constant_t2 (a, b, c, d)
+ VALUES(1, 2, 3, 4),(2, 3, 3, 5),(3, 4, 5, 6),(4, 5, 6, 7),(5, 6, 7, 8),(6,
7, 8, 9),(7, 8, 9, 10),(8, 9, 10, 11),(9, 10, 11, 12),(10, 11, 12, 13);"""
+ qt_equal_set_uniform """select * from eliminate_order_by_constant_t2 where
b=a and a=c and d=1 order by d,a,b,c,c,b,a,d,d"""
+ qt_equal_set_uniform2 """select * from eliminate_order_by_constant_t2
where b=a and a=d and a=c and d=1 order by d,a,b,c,c,b,a,d,d"""
+ qt_equal_set_uniform_fd """select * from eliminate_order_by_constant_t2
where b=a and a=d and a=c and d=1 order by d,a,a+1,b+1,c+1,c,b,a,d,d"""
+ qt_fd_uniform """select * from eliminate_order_by_constant_t2 where d=1
order by d,a,b,c,d+b+a-100,d+b+a,b,a,d,d"""
+ qt_equalset_fd "select * from eliminate_order_by_constant_t2 where d=b and
a=d order by a,c,d+b+a,b,a,d,d+1,abs(d)"
+
+ // other operator
+ // join
+ qt_join_inner_order_by """
+ select t1.a, t1.b, t2.c, t2.d from eliminate_order_by_constant_t t1
+ inner join eliminate_order_by_constant_t2 t2 on t1.a = t2.a order by t1.a,
t2.a, t2.c, t1.a+t2.a, t1.b, t2.d, t1.a+1, t2.c;
+ """
+
+ qt_join_left_outer_order_by """
+ select t1.a, t1.b, t2.c, t2.d from eliminate_order_by_constant_t t1
+ left outer join eliminate_order_by_constant_t2 t2 on t1.a = t2.a order by
t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d, t1.a+1, t2.c;
+ """
+
+ qt_join_right_outer_order_by """
+ select t1.a, t1.b, t2.c, t2.d from eliminate_order_by_constant_t t1
+ right outer join eliminate_order_by_constant_t2 t2 on t1.a = t2.a order by
t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d, t1.a+1, t2.c;
+ """
+
+ qt_join_right_outer_order_by_predicate """select t1.a, t1.b, t2.c, t2.d
from eliminate_order_by_constant_t t1
+ right outer join eliminate_order_by_constant_t2 t2 on t1.a = t2.a where
t2.a=1 order by t1.a, t2.a, t2.c, t1.a+t2.a, t1.b, t2.d, t1.a+1, t2.c;"""
+
+ qt_join_left_semi_order_by """
+ select t1.a, t1.b from eliminate_order_by_constant_t t1
+ left semi join eliminate_order_by_constant_t2 t2 on t1.a = t2.a order by
t1.a, t1.b, t1.a+1,t1.a+t1.b;
+ """
+
+ qt_join_right_semi_order_by """
+ select t2.a, t2.b
+ from eliminate_order_by_constant_t t1
+ right semi join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ order by t2.a, t2.b, t2.a+1,t2.a+t2.b;
+ """
+
+ qt_join_left_anti_order_by """
+ select t1.a, t1.b
+ from eliminate_order_by_constant_t t1
+ left anti join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ order by t1.a, t1.b, t1.a+1,t1.a+t1.b;
+ """
+
+ qt_join_right_anti_order_by """
+ select t2.a, t2.b
+ from eliminate_order_by_constant_t t1
+ right anti join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ order by t2.a, t2.b, t2.a+1,t2.a+t2.b;
+ """
+ // agg
+ qt_agg_order_by """
+ select a, count(b) as cnt
+ from eliminate_order_by_constant_t2
+ group by a
+ order by a, cnt, a,a+cnt,a+100;
+ """
+ // agg+grouping
+ qt_agg_grouping_order_by """
+ select a, b, count(c) as cnt
+ from eliminate_order_by_constant_t2
+ group by cube(a, b)
+ order by a, b, cnt, a, b+1;
+ """
+ // join+window
+ qt_join_window_order_by """
+ select t1.a, t1.b, t2.c, t2.d, t2.a,
+ row_number() over (partition by t1.a order by t1.b) as rn
+ from eliminate_order_by_constant_t t1
+ inner join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ order by t1.a, t2.c, t1.b, t2.d, abs(t1.a), abs(t2.a), t2.c,rn,rn+100;
+ """
+ // agg+window
+ qt_agg_window_order_by """
+ select a, b, count(c) as cnt,
+ row_number() over (partition by a order by b) as rn
+ from eliminate_order_by_constant_t2
+ group by a, b
+ order by a, b, cnt, a+100, b, rn, rn+cnt, abs(rn+cnt);
+ """
+ // join + agg+ window
+ qt_join_agg_window_order_by """
+ select t1.a, t1.b, count(t2.c) as cnt,
+ row_number() over (partition by t1.a order by t1.b) as rn
+ from eliminate_order_by_constant_t t1
+ inner join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ group by t1.a, t1.b ,t2.a
+ order by t1.a,t2.a,t1.b, cnt, -t1.a, -t1.b-1000,rn, cnt, rn+111;
+ """
+ // union all + order by
+ qt_union_all_order_by """
+ select * from (
+ select a, b from eliminate_order_by_constant_t2
+ union all
+ select a, b from eliminate_order_by_constant_t ) t
+ order by a, b, abs(a),abs(a)+b,a+b,a,b;
+ """
+ // union + join + order by
+ qt_union_join_order_by """
+ select * from (select t1.a, t1.b
+ from eliminate_order_by_constant_t t1
+ inner join eliminate_order_by_constant_t2 t2 on t1.a = t2.a
+ union
+ select t1.a, t1.b
+ from eliminate_order_by_constant_t t1
+ left join eliminate_order_by_constant_t2 t2 on t1.a = t2.a ) t
+ where a=1
+ order by a, b, a+100,abs(a)+b;
+ """
+
+ // test composite key
+ sql """create table test_unique_order_by2(a int not null, b int not null,
c int, d int) unique key(a,b) distributed by hash(a)
properties('replication_num'='1');"""
+ sql """INSERT INTO test_unique_order_by2 (a, b, c, d)
+ VALUES(1, 2, 3, 4),(2, 3, 3, 5),(3, 4, 5, 6),(4, 5, 6, 7),(5, 6, 7, 8),(6,
7, 8, 9),(7, 8, 9, 10),(8, 9, 10, 11),(9, 10, 11, 12),(10, 11, 12, 13);"""
+ qt_composite_key """select * from test_unique_order_by2 order by
a,'abc',d,b,d,c;"""
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
