This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 6b1a74af61 [Enhancement](planner&Nereids) support sql_select_limit for
master (#21138)
6b1a74af61 is described below
commit 6b1a74af615c66b0b4ec8952311290c1cc07b6f9
Author: mch_ucchi <41606806+sohardforan...@users.noreply.github.com>
AuthorDate: Fri Jul 7 17:18:38 2023 +0800
[Enhancement](planner&Nereids) support sql_select_limit for master (#21138)
support sql_select_limit for original planner and Nereids.
if enable the variable
In original planner, add limit to the top planNode
In Nereids, add limit node to the top in preprocess phase.
---
.../doris/nereids/jobs/executor/Rewriter.java | 2 +
.../org/apache/doris/nereids/rules/RuleType.java | 1 +
.../nereids/rules/analysis/AddDefaultLimit.java | 96 ++++++++
.../apache/doris/planner/SingleNodePlanner.java | 51 +++--
.../session_variable/test_default_limit.groovy | 253 +++++++++++++++++++++
.../session_variable/test_default_limit.groovy | 251 ++++++++++++++++++++
6 files changed, 636 insertions(+), 18 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 97af7cc14d..d1d33d2514 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
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.jobs.rewrite.RewriteJob;
import org.apache.doris.nereids.processor.pre.EliminateLogicalSelectHint;
import org.apache.doris.nereids.rules.RuleSet;
import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.rules.analysis.AddDefaultLimit;
import
org.apache.doris.nereids.rules.analysis.AdjustAggregateNullableForEmptySet;
import org.apache.doris.nereids.rules.analysis.AvgDistinctToSumDivCount;
import org.apache.doris.nereids.rules.analysis.CheckAfterRewrite;
@@ -103,6 +104,7 @@ public class Rewriter extends AbstractBatchJobExecutor {
public static final List<RewriteJob> REWRITE_JOBS = jobs(
bottomUp(new InlineCTE()),
+ custom(RuleType.ADD_DEFAULT_LIMIT, AddDefaultLimit::new),
topic("Plan Normalization",
topDown(
new EliminateOrderByConstant(),
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 9427296d34..8c371457a1 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
@@ -77,6 +77,7 @@ public enum RuleType {
ADJUST_NULLABLE_FOR_AGGREGATE_SLOT(RuleTypeClass.REWRITE),
ADJUST_NULLABLE_FOR_HAVING_SLOT(RuleTypeClass.REWRITE),
ADJUST_NULLABLE_FOR_REPEAT_SLOT(RuleTypeClass.REWRITE),
+ ADD_DEFAULT_LIMIT(RuleTypeClass.REWRITE),
CHECK_ROW_POLICY(RuleTypeClass.REWRITE),
CHECK_TYPE_TO_INSERT_TARGET_COLUMN(RuleTypeClass.REWRITE),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AddDefaultLimit.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AddDefaultLimit.java
new file mode 100644
index 0000000000..47cb8469dd
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/AddDefaultLimit.java
@@ -0,0 +1,96 @@
+// 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.analysis;
+
+import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.analyzer.UnboundOlapTableSink;
+import org.apache.doris.nereids.jobs.JobContext;
+import org.apache.doris.nereids.trees.plans.LimitPhase;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTE;
+import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalSort;
+import org.apache.doris.nereids.trees.plans.visitor.CustomRewriter;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
+import org.apache.doris.qe.ConnectContext;
+
+/**
+ * add limit node to the top of the plan tree if sql_select_limit or
default_order_by_limit is set.
+ */
+public class AddDefaultLimit extends DefaultPlanRewriter<StatementContext>
implements CustomRewriter {
+
+ @Override
+ public Plan rewriteRoot(Plan plan, JobContext jobContext) {
+ return plan.accept(this,
jobContext.getCascadesContext().getStatementContext());
+ }
+
+ @Override
+ public Plan visit(Plan plan, StatementContext context) {
+ // check if children contain logical sort and add limit.
+ ConnectContext ctx = context.getConnectContext();
+ if (ctx != null) {
+ long defaultLimit = ctx.getSessionVariable().sqlSelectLimit;
+ if (defaultLimit >= 0 && defaultLimit < Long.MAX_VALUE) {
+ return new LogicalLimit<>(defaultLimit, 0, LimitPhase.ORIGIN,
plan);
+ }
+ }
+ return plan;
+ }
+
+ @Override
+ public LogicalPlan visitLogicalLimit(LogicalLimit<? extends Plan> limit,
StatementContext context) {
+ return limit;
+ }
+
+ @Override
+ public LogicalPlan visitLogicalCTE(LogicalCTE<? extends Plan> cte,
StatementContext context) {
+ Plan child = cte.child().accept(this, context);
+ return ((LogicalPlan) cte.withChildren(child));
+ }
+
+ // we should keep that sink node is the top node of the plan tree.
+ // currently, it's one of the olap table sink and file sink.
+ @Override
+ public LogicalPlan visitUnboundOlapTableSink(UnboundOlapTableSink<?
extends Plan> sink, StatementContext context) {
+ Plan child = sink.child().accept(this, context);
+ return ((LogicalPlan) sink.withChildren(child));
+ }
+
+ @Override
+ public LogicalPlan visitLogicalSort(LogicalSort<? extends Plan> sort,
StatementContext context) {
+ ConnectContext ctx = context.getConnectContext();
+ if (ctx != null) {
+ long defaultLimit = ctx.getSessionVariable().defaultOrderByLimit;
+ long sqlLimit = ctx.getSessionVariable().sqlSelectLimit;
+ if (defaultLimit >= 0 || sqlLimit >= 0) {
+ if (defaultLimit < 0) {
+ defaultLimit = Long.MAX_VALUE;
+ }
+ if (sqlLimit < 0) {
+ sqlLimit = Long.MAX_VALUE;
+ }
+ defaultLimit = Math.min(sqlLimit, defaultLimit);
+ if (defaultLimit < Long.MAX_VALUE) {
+ return new LogicalLimit<>(defaultLimit, 0,
LimitPhase.ORIGIN, sort);
+ }
+ }
+ }
+ return sort;
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
index 4bb5ae4309..82495ce243 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
@@ -180,8 +180,12 @@ public class SingleNodePlanner {
if (LOG.isTraceEnabled()) {
LOG.trace("desctbl: " + analyzer.getDescTbl().debugString());
}
+ long sqlSelectLimit = -1;
+ if (ConnectContext.get() != null &&
ConnectContext.get().getSessionVariable() != null) {
+ sqlSelectLimit =
ConnectContext.get().getSessionVariable().sqlSelectLimit;
+ }
PlanNode singleNodePlan = createQueryPlan(queryStmt, analyzer,
- ctx.getQueryOptions().getDefaultOrderByLimit());
+ ctx.getQueryOptions().getDefaultOrderByLimit(),
sqlSelectLimit);
Preconditions.checkNotNull(singleNodePlan);
analyzer.getDescTbl().materializeIntermediateSlots();
return singleNodePlan;
@@ -241,7 +245,7 @@ public class SingleNodePlanner {
* Create plan tree for single-node execution. Generates PlanNodes for the
* Select/Project/Join/Union [All]/Group by/Having/Order by clauses of the
query stmt.
*/
- private PlanNode createQueryPlan(QueryStmt stmt, Analyzer analyzer, long
defaultOrderByLimit)
+ private PlanNode createQueryPlan(QueryStmt stmt, Analyzer analyzer, long
defaultOrderByLimit, long sqlSelectLimit)
throws UserException {
long newDefaultOrderByLimit = defaultOrderByLimit;
long defaultLimit =
analyzer.getContext().getSessionVariable().defaultOrderByLimit;
@@ -275,7 +279,7 @@ public class SingleNodePlanner {
}
} else {
Preconditions.checkState(stmt instanceof SetOperationStmt);
- root = createSetOperationPlan((SetOperationStmt) stmt, analyzer,
newDefaultOrderByLimit);
+ root = createSetOperationPlan((SetOperationStmt) stmt, analyzer,
newDefaultOrderByLimit, sqlSelectLimit);
}
if (ConnectContext.get().getExecutor() != null) {
ConnectContext.get().getExecutor().getSummaryProfile().setQueryJoinReorderFinishTime();
@@ -304,9 +308,16 @@ public class SingleNodePlanner {
root = new SortNode(ctx.getNextNodeId(), root, stmt.getSortInfo(),
useTopN);
((SortNode) root).setDefaultLimit(limit == -1);
- ((SortNode) root).setOffset(stmt.getOffset());
+ root.setOffset(stmt.getOffset());
if (useTopN) {
- root.setLimit(limit != -1 ? limit : newDefaultOrderByLimit);
+ if (sqlSelectLimit >= 0 && sqlSelectLimit < Long.MAX_VALUE) {
+ newDefaultOrderByLimit = Math.min(newDefaultOrderByLimit,
sqlSelectLimit);
+ }
+ if (newDefaultOrderByLimit == Long.MAX_VALUE) {
+ root.setLimit(limit);
+ } else {
+ root.setLimit(limit != -1 ? limit :
newDefaultOrderByLimit);
+ }
} else {
root.setLimit(limit);
}
@@ -316,7 +327,11 @@ public class SingleNodePlanner {
// from SelectStmt outside
root = addUnassignedConjuncts(analyzer, root);
} else {
- root.setLimitAndOffset(stmt.getLimit(), stmt.getOffset());
+ if (!stmt.hasLimit() && sqlSelectLimit >= 0 && sqlSelectLimit <
Long.MAX_VALUE) {
+ root.setLimitAndOffset(sqlSelectLimit, stmt.getOffset());
+ } else {
+ root.setLimitAndOffset(stmt.getLimit(), stmt.getOffset());
+ }
root.computeStats(analyzer);
}
@@ -325,7 +340,7 @@ public class SingleNodePlanner {
root = createAssertRowCountNode(root,
stmt.getAssertNumRowsElement(), analyzer);
}
- if (analyzer.hasEmptyResultSet()) {
+ if (analyzer.hasEmptyResultSet() || root.getLimit() == 0) {
// Must clear the scanNodes, otherwise we will get NPE in
Coordinator::computeScanRangeAssignment
Set<TupleId> scanTupleIds = new
HashSet<>(root.getAllScanTupleIds());
scanNodes.removeIf(scanNode ->
scanTupleIds.contains(scanNode.getTupleIds().get(0)));
@@ -1657,7 +1672,7 @@ public class SingleNodePlanner {
}
}
- PlanNode rootNode = createQueryPlan(inlineViewRef.getViewStmt(),
inlineViewRef.getAnalyzer(), -1);
+ PlanNode rootNode = createQueryPlan(inlineViewRef.getViewStmt(),
inlineViewRef.getAnalyzer(), -1, -1);
// TODO: we should compute the "physical layout" of the view's
descriptor, so that
// the avg row size is available during optimization; however, that
means we need to
// select references to its resultExprs from the enclosing scope(s)
@@ -2238,7 +2253,7 @@ public class SingleNodePlanner {
*/
private SetOperationNode createSetOperationPlan(
Analyzer analyzer, SetOperationStmt setOperationStmt,
List<SetOperationStmt.SetOperand> setOperands,
- PlanNode result, long defaultOrderByLimit)
+ PlanNode result, long defaultOrderByLimit, long sqlSelectLimit)
throws UserException, AnalysisException {
SetOperationNode setOpNode;
SetOperationStmt.Operation operation = null;
@@ -2297,7 +2312,7 @@ public class SingleNodePlanner {
continue;
}
}
- PlanNode opPlan = createQueryPlan(queryStmt, op.getAnalyzer(),
defaultOrderByLimit);
+ PlanNode opPlan = createQueryPlan(queryStmt, op.getAnalyzer(),
defaultOrderByLimit, sqlSelectLimit);
// There may still be unassigned conjuncts if the operand has an
order by + limit.
// Place them into a SelectNode on top of the operand's plan.
opPlan = addUnassignedConjuncts(analyzer, opPlan.getTupleIds(),
opPlan);
@@ -2327,7 +2342,7 @@ public class SingleNodePlanner {
* use a union node (this is tricky because a union materializes a new
tuple).
*/
private PlanNode createSetOperationPlan(
- SetOperationStmt setOperationStmt, Analyzer analyzer, long
defaultOrderByLimit)
+ SetOperationStmt setOperationStmt, Analyzer analyzer, long
defaultOrderByLimit, long sqlSelectLimit)
throws UserException, AnalysisException {
// TODO(zc): get unassigned conjuncts
// List<Expr> conjuncts =
@@ -2397,10 +2412,10 @@ public class SingleNodePlanner {
if (operation == SetOperationStmt.Operation.INTERSECT
|| operation == SetOperationStmt.Operation.EXCEPT)
{
result = createSetOperationPlan(analyzer,
setOperationStmt, partialOperands, result,
- defaultOrderByLimit);
+ defaultOrderByLimit, sqlSelectLimit);
} else {
result = createUnionPartialSetOperationPlan(analyzer,
setOperationStmt, partialOperands, result,
- defaultOrderByLimit);
+ defaultOrderByLimit, sqlSelectLimit);
}
partialOperands.clear();
}
@@ -2414,10 +2429,10 @@ public class SingleNodePlanner {
if (operation == SetOperationStmt.Operation.INTERSECT
|| operation == SetOperationStmt.Operation.EXCEPT) {
result = createSetOperationPlan(analyzer, setOperationStmt,
partialOperands, result,
- defaultOrderByLimit);
+ defaultOrderByLimit, sqlSelectLimit);
} else {
result = createUnionPartialSetOperationPlan(analyzer,
setOperationStmt, partialOperands, result,
- defaultOrderByLimit);
+ defaultOrderByLimit, sqlSelectLimit);
}
}
@@ -2435,7 +2450,7 @@ public class SingleNodePlanner {
// while the left-hand child(a)'s operation is null
private PlanNode createUnionPartialSetOperationPlan(Analyzer analyzer,
SetOperationStmt setOperationStmt,
List<SetOperationStmt.SetOperand> setOperands,
- PlanNode result, long
defaultOrderByLimit)
+ PlanNode result, long
defaultOrderByLimit, long sqlSelectLimit)
throws UserException {
boolean hasDistinctOps = false;
boolean hasAllOps = false;
@@ -2454,7 +2469,7 @@ public class SingleNodePlanner {
// create DISTINCT tree
if (hasDistinctOps) {
result = createSetOperationPlan(
- analyzer, setOperationStmt, distinctOps, result,
defaultOrderByLimit);
+ analyzer, setOperationStmt, distinctOps, result,
defaultOrderByLimit, sqlSelectLimit);
result = new AggregationNode(ctx.getNextNodeId(), result,
setOperationStmt.getDistinctAggInfo());
result.init(analyzer);
@@ -2462,7 +2477,7 @@ public class SingleNodePlanner {
// create ALL tree
if (hasAllOps) {
result = createSetOperationPlan(analyzer, setOperationStmt, allOps,
- result, defaultOrderByLimit);
+ result, defaultOrderByLimit, sqlSelectLimit);
}
return result;
}
diff --git
a/regression-test/suites/nereids_p0/session_variable/test_default_limit.groovy
b/regression-test/suites/nereids_p0/session_variable/test_default_limit.groovy
new file mode 100644
index 0000000000..c2995fee41
--- /dev/null
+++
b/regression-test/suites/nereids_p0/session_variable/test_default_limit.groovy
@@ -0,0 +1,253 @@
+// 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('test_default_limit') {
+ sql 'use nereids_test_query_db'
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ for (int i = 0; i < 2; ++i) {
+ if (i == 0) {
+ sql 'set enable_pipeline_engine=false'
+ } else if (i == 1) {
+ sql 'set enable_pipeline_engine=true'
+ }
+
+ sql 'set default_order_by_limit = -1'
+ sql 'set sql_select_limit = -1'
+
+ def res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+
+ sql 'set default_order_by_limit = 10'
+ sql 'set sql_select_limit = 5'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = 5'
+ sql 'set sql_select_limit = 10'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 10)
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 10)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = -1'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 15)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = -10'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 15)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = 0'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 0)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = 5'
+ sql 'set default_order_by_limit = -1'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = -10'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = 0'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/session_variable/test_default_limit.groovy
b/regression-test/suites/query_p0/session_variable/test_default_limit.groovy
new file mode 100644
index 0000000000..139c46bcd8
--- /dev/null
+++ b/regression-test/suites/query_p0/session_variable/test_default_limit.groovy
@@ -0,0 +1,251 @@
+// 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('test_default_limit') {
+ sql 'use test_query_db'
+
+ for (int i = 0; i < 2; ++i) {
+ if (i == 0) {
+ sql 'set enable_pipeline_engine=false'
+ } else if (i == 1) {
+ sql 'set enable_pipeline_engine=true'
+ }
+
+ sql 'set default_order_by_limit = -1'
+ sql 'set sql_select_limit = -1'
+
+ def res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+
+ sql 'set default_order_by_limit = 10'
+ sql 'set sql_select_limit = 5'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = 5'
+ sql 'set sql_select_limit = 10'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 10)
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 10)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = -1'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 15)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = -10'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 16)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 15)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = 0'
+
+ res = sql 'select * from baseall'
+ assertEquals(res.size(), 0)
+ res = sql 'select * from baseall limit 7'
+ assertEquals(res.size(), 7)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set sql_select_limit = 5'
+ sql 'set default_order_by_limit = -1'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = -10'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+
+ sql 'set default_order_by_limit = 0'
+
+ res = sql 'select * from baseall order by k1'
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ '''
+ assertEquals(res.size(), 5)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2
+ '''
+ assertEquals(res.size(), 0)
+ res = sql '''
+ with cte as (
+ select baseall.* from baseall, bigtable where baseall.k1 =
bigtable.k1
+ )
+ select * from baseall, (select k1 from cte) c where c.k1 =
baseall.k1
+ order by c.k1, baseall.k2 limit 8
+ '''
+ assertEquals(res.size(), 8)
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
