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 c21f89de209 branch-3.1: [fix](nereids) fix
pushdownLimit/topnThroughJoin dead loop #58305 (#58697)
c21f89de209 is described below
commit c21f89de209fc9abfb9de35268516f9bbfd91456
Author: minghong <[email protected]>
AuthorDate: Fri Dec 5 10:55:45 2025 +0800
branch-3.1: [fix](nereids) fix pushdownLimit/topnThroughJoin dead loop
#58305 (#58697)
picked from #58305
---
.../rewrite/PushDownLimitDistinctThroughJoin.java | 14 +-
.../rewrite/PushDownTopNDistinctThroughJoin.java | 11 +-
.../rules/rewrite/PushDownTopNThroughJoin.java | 17 ++-
.../mv/pre_rewrite/limit/query_with_limit.groovy | 159 +++++++++++++++++++++
4 files changed, 191 insertions(+), 10 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownLimitDistinctThroughJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownLimitDistinctThroughJoin.java
index 21f777204b2..d310fb64994 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownLimitDistinctThroughJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownLimitDistinctThroughJoin.java
@@ -21,6 +21,7 @@ import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.Limit;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
@@ -47,7 +48,7 @@ public class PushDownLimitDistinctThroughJoin implements
RewriteRuleFactory {
LogicalJoin<Plan, Plan> join = agg.child();
Plan newJoin = pushLimitThroughJoin(limit, join);
- if (newJoin == null ||
join.children().equals(newJoin.children())) {
+ if (newJoin == null) {
return null;
}
return
limit.withChildren(agg.withChildren(newJoin));
@@ -63,7 +64,7 @@ public class PushDownLimitDistinctThroughJoin implements
RewriteRuleFactory {
LogicalJoin<Plan, Plan> join = project.child();
Plan newJoin = pushLimitThroughJoin(limit, join);
- if (newJoin == null ||
join.children().equals(newJoin.children())) {
+ if (newJoin == null) {
return null;
}
return
limit.withChildren(agg.withChildren(project.withChildren(newJoin)));
@@ -77,25 +78,26 @@ public class PushDownLimitDistinctThroughJoin implements
RewriteRuleFactory {
.flatMap(e ->
e.getInputSlots().stream()).collect(Collectors.toList());
switch (join.getJoinType()) {
case LEFT_OUTER_JOIN:
- if (join.left().getOutputSet().containsAll(groupBySlots)
+ if (!(join.left() instanceof Limit)
+ && join.left().getOutputSet().containsAll(groupBySlots)
&&
join.left().getOutputSet().equals(agg.getOutputSet())) {
return
join.withChildren(limit.withLimitChild(limit.getLimit() + limit.getOffset(), 0,
agg.withChildren(join.left())), join.right());
}
return null;
case RIGHT_OUTER_JOIN:
- if (join.right().getOutputSet().containsAll(groupBySlots)
+ if (!(join.right() instanceof Limit) &&
join.right().getOutputSet().containsAll(groupBySlots)
&&
join.right().getOutputSet().equals(agg.getOutputSet())) {
return join.withChildren(join.left(),
limit.withLimitChild(limit.getLimit() + limit.getOffset(), 0,
agg.withChildren(join.right())));
}
return null;
case CROSS_JOIN:
- if (join.left().getOutputSet().containsAll(groupBySlots)
+ if (!(join.left() instanceof Limit) &&
join.left().getOutputSet().containsAll(groupBySlots)
&&
join.left().getOutputSet().equals(agg.getOutputSet())) {
return
join.withChildren(limit.withLimitChild(limit.getLimit() + limit.getOffset(), 0,
agg.withChildren(join.left())), join.right());
- } else if
(join.right().getOutputSet().containsAll(groupBySlots)
+ } else if (!(join.right() instanceof Limit) &&
join.right().getOutputSet().containsAll(groupBySlots)
&&
join.right().getOutputSet().equals(agg.getOutputSet())) {
return join.withChildren(join.left(),
limit.withLimitChild(limit.getLimit() + limit.getOffset(), 0,
agg.withChildren(join.right())));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNDistinctThroughJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNDistinctThroughJoin.java
index f2dde7ba2a8..2a925c36d1b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNDistinctThroughJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNDistinctThroughJoin.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.TopN;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
@@ -93,6 +94,9 @@ public class PushDownTopNDistinctThroughJoin implements
RewriteRuleFactory {
.flatMap(e ->
e.getInputSlots().stream()).collect(Collectors.toSet());
switch (join.getJoinType()) {
case LEFT_OUTER_JOIN: {
+ if (join.left() instanceof TopN) {
+ return null;
+ }
List<OrderKey> pushedOrderKeys =
getPushedOrderKeys(groupBySlots,
join.left().getOutputSet(), topN.getOrderKeys());
if (!pushedOrderKeys.isEmpty()) {
@@ -104,6 +108,9 @@ public class PushDownTopNDistinctThroughJoin implements
RewriteRuleFactory {
return null;
}
case RIGHT_OUTER_JOIN: {
+ if (join.right() instanceof TopN) {
+ return null;
+ }
List<OrderKey> pushedOrderKeys =
getPushedOrderKeys(groupBySlots,
join.right().getOutputSet(), topN.getOrderKeys());
if (!pushedOrderKeys.isEmpty()) {
@@ -119,14 +126,14 @@ public class PushDownTopNDistinctThroughJoin implements
RewriteRuleFactory {
Plan rightChild = join.right();
List<OrderKey> leftPushedOrderKeys =
getPushedOrderKeys(groupBySlots,
join.left().getOutputSet(), topN.getOrderKeys());
- if (!leftPushedOrderKeys.isEmpty()) {
+ if (!(join.left() instanceof TopN) &&
!leftPushedOrderKeys.isEmpty()) {
leftChild = topN.withLimitOrderKeyAndChild(
topN.getLimit() + topN.getOffset(), 0,
leftPushedOrderKeys,
PlanUtils.distinct(join.left()));
}
List<OrderKey> rightPushedOrderKeys =
getPushedOrderKeys(groupBySlots,
join.right().getOutputSet(), topN.getOrderKeys());
- if (!rightPushedOrderKeys.isEmpty()) {
+ if (!(join.right() instanceof TopN) &&
!rightPushedOrderKeys.isEmpty()) {
rightChild = topN.withLimitOrderKeyAndChild(
topN.getLimit() + topN.getOffset(), 0,
rightPushedOrderKeys,
PlanUtils.distinct(join.right()));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNThroughJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNThroughJoin.java
index 28a7f2688be..9ba3dd6e9a5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNThroughJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownTopNThroughJoin.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.TopN;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalTopN;
@@ -48,7 +49,7 @@ public class PushDownTopNThroughJoin implements
RewriteRuleFactory {
.then(topN -> {
LogicalJoin<Plan, Plan> join = topN.child();
Plan newJoin = pushLimitThroughJoin(topN, join);
- if (newJoin == null ||
topN.child().children().equals(newJoin.children())) {
+ if (newJoin == null) {
return null;
}
return topN.withChildren(newJoin);
@@ -75,7 +76,7 @@ public class PushDownTopNThroughJoin implements
RewriteRuleFactory {
}
Plan newJoin = pushLimitThroughJoin(topN, join);
- if (newJoin == null ||
join.children().equals(newJoin.children())) {
+ if (newJoin == null) {
return null;
}
return
topN.withChildren(project.withChildren(newJoin));
@@ -88,6 +89,9 @@ public class PushDownTopNThroughJoin implements
RewriteRuleFactory {
.flatMap(e ->
e.getInputSlots().stream()).collect(Collectors.toList());
switch (join.getJoinType()) {
case LEFT_OUTER_JOIN:
+ if (join.left() instanceof TopN) {
+ return null;
+ }
if (join.left().getOutputSet().containsAll(orderbySlots)) {
return join.withChildren(
topN.withLimitChild(topN.getLimit() +
topN.getOffset(), 0, join.left()),
@@ -95,6 +99,9 @@ public class PushDownTopNThroughJoin implements
RewriteRuleFactory {
}
return null;
case RIGHT_OUTER_JOIN:
+ if (join.right() instanceof TopN) {
+ return null;
+ }
if (join.right().getOutputSet().containsAll(orderbySlots)) {
return join.withChildren(
join.left(),
@@ -104,10 +111,16 @@ public class PushDownTopNThroughJoin implements
RewriteRuleFactory {
case CROSS_JOIN:
if (join.left().getOutputSet().containsAll(orderbySlots)) {
+ if (join.left() instanceof TopN) {
+ return null;
+ }
return join.withChildren(
topN.withLimitChild(topN.getLimit() +
topN.getOffset(), 0, join.left()),
join.right());
} else if
(join.right().getOutputSet().containsAll(orderbySlots)) {
+ if (join.right() instanceof TopN) {
+ return null;
+ }
return join.withChildren(
join.left(),
topN.withLimitChild(topN.getLimit() +
topN.getOffset(), 0, join.right()));
diff --git
a/regression-test/suites/nereids_rules_p0/mv/pre_rewrite/limit/query_with_limit.groovy
b/regression-test/suites/nereids_rules_p0/mv/pre_rewrite/limit/query_with_limit.groovy
new file mode 100644
index 00000000000..0105d4bf0f1
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/pre_rewrite/limit/query_with_limit.groovy
@@ -0,0 +1,159 @@
+package mv.pre_rewrite.limit
+// 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("query_with_limit") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "set runtime_filter_mode=OFF";
+ sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
+
+ sql """
+ drop table if exists orders
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS orders (
+ o_orderkey INTEGER NOT NULL,
+ o_custkey INTEGER NOT NULL,
+ o_orderstatus CHAR(1) NOT NULL,
+ o_totalprice DECIMALV3(15,2) NOT NULL,
+ o_orderdate DATE NOT NULL,
+ o_orderpriority CHAR(15) NOT NULL,
+ o_clerk CHAR(15) NOT NULL,
+ o_shippriority INTEGER NOT NULL,
+ O_COMMENT VARCHAR(79) NOT NULL
+ )
+ DUPLICATE KEY(o_orderkey, o_custkey)
+ DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """
+ drop table if exists lineitem
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS lineitem (
+ l_orderkey INTEGER NOT NULL,
+ l_partkey INTEGER NOT NULL,
+ l_suppkey INTEGER NOT NULL,
+ l_linenumber INTEGER NOT NULL,
+ l_quantity DECIMALV3(15,2) NOT NULL,
+ l_extendedprice DECIMALV3(15,2) NOT NULL,
+ l_discount DECIMALV3(15,2) NOT NULL,
+ l_tax DECIMALV3(15,2) NOT NULL,
+ l_returnflag CHAR(1) NOT NULL,
+ l_linestatus CHAR(1) NOT NULL,
+ l_shipdate DATE NOT NULL,
+ l_commitdate DATE NOT NULL,
+ l_receiptdate DATE NOT NULL,
+ l_shipinstruct CHAR(25) NOT NULL,
+ l_shipmode CHAR(10) NOT NULL,
+ l_comment VARCHAR(44) NOT NULL
+ )
+ DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
+ DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ drop table if exists partsupp
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS partsupp (
+ ps_partkey INTEGER NOT NULL,
+ ps_suppkey INTEGER NOT NULL,
+ ps_availqty INTEGER NOT NULL,
+ ps_supplycost DECIMALV3(15,2) NOT NULL,
+ ps_comment VARCHAR(199) NOT NULL
+ )
+ DUPLICATE KEY(ps_partkey, ps_suppkey)
+ DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """ insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-08', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-09', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-10', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (4, 3, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-11', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (5, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-12-12', '2023-12-12',
'2023-12-13', 'c', 'd', 'xxxxxxxxx');
+ """
+
+ sql """
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-12-09', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-12-09', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-12-09', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 33.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'),
+ (4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'),
+ (4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'),
+ (5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'),
+ (5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'),
+ (5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'),
+ (5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi');
+ """
+
+ sql """
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (2, 3, 10, 11.01, 'supply2');
+ """
+
+ sql """analyze table partsupp with sync"""
+ sql """analyze table lineitem with sync"""
+ sql """analyze table orders with sync"""
+ sql """alter table lineitem modify column l_comment set stats
('row_count'='5');"""
+ sql """alter table orders modify column O_COMMENT set stats
('row_count'='8');"""
+ sql """alter table partsupp modify column ps_comment set stats
('row_count'='2');"""
+
+ explain {
+ sql """
+ select
+ o_orderdate,
+ o_shippriority,
+ o_comment,
+ l_orderkey,
+ l_partkey
+ from
+ orders left
+ join lineitem on l_orderkey = o_orderkey
+ left join partsupp on ps_partkey = l_partkey and l_suppkey =
ps_suppkey
+ order by l_orderkey
+ limit 2
+ offset 1;
+ """
+ notContains "group expression count exceeds
memo_max_group_expression_size(10000)"
+ }
+}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
