This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new 7e26f7b1724 branch-4.1: [feature](fe) Support ORDER BY and LIMIT
clauses for UPDATE and DELETE commands #61681 (#61752)
7e26f7b1724 is described below
commit 7e26f7b1724b236be1c116ad96c91757229995ec
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Mar 26 18:16:31 2026 +0800
branch-4.1: [feature](fe) Support ORDER BY and LIMIT clauses for UPDATE and
DELETE commands #61681 (#61752)
Cherry-picked from #61681
Co-authored-by: morrySnow <[email protected]>
Co-authored-by: Copilot <[email protected]>
---
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 6 +-
.../doris/nereids/parser/LogicalPlanBuilder.java | 43 +++-
.../trees/plans/commands/DeleteFromCommand.java | 4 +-
.../plans/commands/DeleteFromUsingCommand.java | 12 +-
.../nereids/parser/LogicalPlanBuilderTest.java | 251 +++++++++++++++++++++
.../doris/nereids/parser/NereidsParserTest.java | 79 +++++++
.../data/delete_p0/test_delete_order_by_limit.out | 61 +++++
.../data/update/test_update_order_by_limit.out | 85 +++++++
.../delete_p0/test_delete_order_by_limit.groovy | 178 +++++++++++++++
.../update/test_update_order_by_limit.groovy | 175 ++++++++++++++
10 files changed, 886 insertions(+), 8 deletions(-)
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index 32cdb65a71e..eb11ce49cd1 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -151,11 +151,13 @@ supportedDmlStatement
| explain? cte? UPDATE tableName=multipartIdentifier tableAlias
SET updateAssignmentSeq
fromClause?
- whereClause? #update
+ whereClause?
+ queryOrganization #update
| explain? cte? DELETE FROM tableName=multipartIdentifier
partitionSpec? tableAlias
(USING relations)?
- whereClause? #delete
+ whereClause?
+ queryOrganization #delete
| explain? cte? MERGE INTO targetTable=multipartIdentifier
(AS? identifier)? USING srcRelation=relationPrimary
ON expression
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index a5acc362365..13b4eac4308 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -600,6 +600,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.DateV2Literal;
import org.apache.doris.nereids.trees.expressions.literal.DecimalLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DecimalV3Literal;
import org.apache.doris.nereids.trees.expressions.literal.DoubleLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Interval;
import org.apache.doris.nereids.trees.expressions.literal.LargeIntLiteral;
@@ -2037,6 +2038,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
query = withRelations(query, ((FromRelationsContext)
ctx.fromClause()).relations().relation());
}
query = withFilter(query, Optional.ofNullable(ctx.whereClause()));
+ query = withQueryOrganization(query, ctx.queryOrganization());
+ query = convertSortOrdinalsToUnboundSlot(query);
String tableAlias = null;
if (ctx.tableAlias().strictIdentifier() != null) {
tableAlias = ctx.tableAlias().strictIdentifier().getText();
@@ -2067,8 +2070,11 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
tableAlias = ctx.tableAlias().strictIdentifier().getText();
}
+ boolean hasQueryOrganization = ctx.queryOrganization() != null
+ && (ctx.queryOrganization().sortClause() != null
+ || ctx.queryOrganization().limitClause() != null);
Command deleteCommand;
- if (ctx.USING() == null && ctx.cte() == null) {
+ if (ctx.USING() == null && ctx.cte() == null && !hasQueryOrganization)
{
query = withFilter(query, Optional.ofNullable(ctx.whereClause()));
deleteCommand = new DeleteFromCommand(tableName, tableAlias,
partitionSpec.first,
partitionSpec.second, query);
@@ -2078,12 +2084,14 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
query = withRelations(query, ctx.relations().relation());
}
query = withFilter(query, Optional.ofNullable(ctx.whereClause()));
+ query = withQueryOrganization(query, ctx.queryOrganization());
+ query = convertSortOrdinalsToUnboundSlot(query);
Optional<LogicalPlan> cte = Optional.empty();
if (ctx.cte() != null) {
cte = Optional.ofNullable(withCte(query, ctx.cte()));
}
deleteCommand = new DeleteFromUsingCommand(tableName, tableAlias,
- partitionSpec.first, partitionSpec.second, query, cte);
+ partitionSpec.first, partitionSpec.second, query, cte,
hasQueryOrganization);
}
if (ctx.explain() != null) {
return withExplain(deleteCommand, ctx.explain());
@@ -4379,6 +4387,37 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
});
}
+ /**
+ * Convert IntegerLikeLiteral expressions in ORDER BY keys to UnboundSlot.
+ * In SELECT queries, ORDER BY with an integer is treated as an ordinal
(position reference).
+ * In DELETE/UPDATE commands, there is no user-specified SELECT list, so
ordinal resolution
+ * would be meaningless. Convert integer literals to UnboundSlot to
prevent the ordinal
+ * interpretation by BindExpression.
+ */
+ private LogicalPlan convertSortOrdinalsToUnboundSlot(LogicalPlan plan) {
+ if (plan instanceof LogicalSort) {
+ LogicalSort<?> sort = (LogicalSort<?>) plan;
+ List<OrderKey> newOrderKeys = sort.getOrderKeys().stream()
+ .map(key -> {
+ if (key.getExpr() instanceof IntegerLikeLiteral) {
+ return key.withExpression(
+ new UnboundSlot(String.valueOf(
+ ((IntegerLikeLiteral)
key.getExpr()).getIntValue())));
+ }
+ return key;
+ })
+ .collect(ImmutableList.toImmutableList());
+ return sort.withOrderKeys(newOrderKeys);
+ } else if (plan instanceof LogicalLimit) {
+ LogicalPlan child = (LogicalPlan) ((LogicalLimit<?>) plan).child();
+ LogicalPlan newChild = convertSortOrdinalsToUnboundSlot(child);
+ if (newChild != child) {
+ return (LogicalPlan) ((LogicalLimit<?>)
plan).withChildren(newChild);
+ }
+ }
+ return plan;
+ }
+
private LogicalPlan withLimit(LogicalPlan input,
Optional<LimitClauseContext> limitCtx) {
return input.optionalMap(limitCtx, () -> {
long limit = Long.parseLong(limitCtx.get().limit.getText());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromCommand.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromCommand.java
index 04e155b29fc..be067378310 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromCommand.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromCommand.java
@@ -181,7 +181,7 @@ public class DeleteFromCommand extends Command implements
ForwardWithSync, Expla
} catch (Exception e) {
try {
new DeleteFromUsingCommand(nameParts, tableAlias, isTempPart,
partitions,
- logicalQuery, Optional.empty()).run(ctx, executor);
+ logicalQuery, Optional.empty(), false).run(ctx,
executor);
return;
} catch (Exception e2) {
LOG.warn("delete from command failed", e2);
@@ -193,7 +193,7 @@ public class DeleteFromCommand extends Command implements
ForwardWithSync, Expla
if (olapTable.getKeysType() == KeysType.UNIQUE_KEYS &&
olapTable.getEnableUniqueKeyMergeOnWrite()
&& !olapTable.getEnableMowLightDelete()) {
new DeleteFromUsingCommand(nameParts, tableAlias, isTempPart,
partitions, logicalQuery,
- Optional.empty()).run(ctx, executor);
+ Optional.empty(), false).run(ctx, executor);
return;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromUsingCommand.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromUsingCommand.java
index 764ff05c00f..2364c690712 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromUsingCommand.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/DeleteFromUsingCommand.java
@@ -35,14 +35,17 @@ import java.util.Optional;
*/
public class DeleteFromUsingCommand extends DeleteFromCommand {
private final Optional<LogicalPlan> cte;
+ private final boolean hasOrderByLimit;
/**
* constructor
*/
public DeleteFromUsingCommand(List<String> nameParts, String tableAlias,
- boolean isTempPart, List<String> partitions, LogicalPlan
logicalQuery, Optional<LogicalPlan> cte) {
+ boolean isTempPart, List<String> partitions, LogicalPlan
logicalQuery,
+ Optional<LogicalPlan> cte, boolean hasOrderByLimit) {
super(nameParts, tableAlias, isTempPart, partitions, logicalQuery);
this.cte = cte;
+ this.hasOrderByLimit = hasOrderByLimit;
}
@Override
@@ -80,7 +83,12 @@ public class DeleteFromUsingCommand extends
DeleteFromCommand {
@Override
protected void checkTargetTable(OlapTable targetTable) {
if (targetTable.getKeysType() != KeysType.UNIQUE_KEYS) {
- throw new AnalysisException("delete command on with using clause
only supports unique key model");
+ if (hasOrderByLimit) {
+ throw new AnalysisException(
+ "delete command with ORDER BY/LIMIT only supports
unique key model");
+ }
+ throw new AnalysisException(
+ "delete command on with using clause only supports unique
key model");
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/LogicalPlanBuilderTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/LogicalPlanBuilderTest.java
new file mode 100644
index 00000000000..9690e24ea85
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/LogicalPlanBuilderTest.java
@@ -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.
+
+package org.apache.doris.nereids.parser;
+
+import org.apache.doris.nereids.analyzer.UnboundSlot;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral;
+import org.apache.doris.nereids.trees.plans.commands.DeleteFromCommand;
+import org.apache.doris.nereids.trees.plans.commands.DeleteFromUsingCommand;
+import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+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.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Tests for LogicalPlanBuilder to verify DELETE/UPDATE with ORDER BY and LIMIT
+ * produce the correct logical plan tree structure.
+ */
+public class LogicalPlanBuilderTest {
+
+ private final NereidsParser parser = new NereidsParser();
+
+ @Test
+ public void testDeleteWithOrderByLimitProducesCorrectPlanTree() {
+ String sql = "DELETE FROM t ORDER BY c1 LIMIT 10";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ // plan tree: LogicalLimit -> LogicalSort ->
CheckPolicy(UnboundRelation)
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertEquals(10, limit.getLimit());
+ Assertions.assertEquals(0, limit.getOffset());
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ LogicalSort<?> sort = (LogicalSort<?>) limit.child();
+ Assertions.assertEquals(1, sort.getOrderKeys().size());
+ }
+
+ @Test
+ public void testDeleteWithOrderByLimitOffset() {
+ String sql = "DELETE FROM t ORDER BY c1 ASC NULLS FIRST LIMIT 10, 3";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ // LIMIT offset, count: offset=10, limit=3
+ Assertions.assertEquals(3, limit.getLimit());
+ Assertions.assertEquals(10, limit.getOffset());
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ }
+
+ @Test
+ public void testDeleteWithWhereOrderByLimit() {
+ String sql = "DELETE FROM t WHERE c1 > 0 ORDER BY c1 DESC NULLS LAST
LIMIT 5";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ // plan tree: LogicalLimit -> LogicalSort -> LogicalFilter ->
CheckPolicy(UnboundRelation)
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertEquals(5, limit.getLimit());
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ LogicalSort<?> sort = (LogicalSort<?>) limit.child();
+ Assertions.assertInstanceOf(LogicalFilter.class, sort.child());
+ }
+
+ @Test
+ public void testDeleteWithOrderByOnlyProducesDeleteFromUsingCommand() {
+ String sql = "DELETE FROM t ORDER BY c1";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalSort.class, query);
+ }
+
+ @Test
+ public void testDeleteWithLimitOnlyProducesDeleteFromUsingCommand() {
+ String sql = "DELETE FROM t LIMIT 5";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertEquals(5, limit.getLimit());
+ }
+
+ @Test
+ public void testDeleteWithoutOrderByLimitProducesDeleteFromCommand() {
+ String sql = "DELETE FROM t WHERE c1 = 1";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromCommand.class, plan);
+ Assertions.assertFalse(plan instanceof DeleteFromUsingCommand);
+ }
+
+ @Test
+ public void testUpdateWithOrderByLimitProducesCorrectPlanTree() {
+ String sql = "UPDATE t SET c1 = 10 ORDER BY c2 LIMIT 100";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ // plan tree: LogicalLimit -> LogicalSort ->
CheckPolicy(UnboundRelation)
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertEquals(100, limit.getLimit());
+ Assertions.assertEquals(0, limit.getOffset());
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ LogicalSort<?> sort = (LogicalSort<?>) limit.child();
+ Assertions.assertEquals(1, sort.getOrderKeys().size());
+ }
+
+ @Test
+ public void testUpdateWithOrderByLimitOffset() {
+ String sql = "UPDATE t SET c1 = 10 ORDER BY c2 LIMIT 100, 20";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ // LIMIT offset, count: offset=100, limit=20
+ Assertions.assertEquals(20, limit.getLimit());
+ Assertions.assertEquals(100, limit.getOffset());
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ }
+
+ @Test
+ public void testUpdateWithWhereOrderByLimit() {
+ String sql = "UPDATE t SET c1 = 10 WHERE c2 > 5 ORDER BY c2 DESC LIMIT
50";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ // plan tree: LogicalLimit -> LogicalSort -> LogicalFilter ->
CheckPolicy(UnboundRelation)
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertInstanceOf(LogicalSort.class, limit.child());
+ LogicalSort<?> sort = (LogicalSort<?>) limit.child();
+ Assertions.assertInstanceOf(LogicalFilter.class, sort.child());
+ }
+
+ @Test
+ public void testUpdateWithOrderByOnlyProducesCorrectPlanTree() {
+ String sql = "UPDATE t SET c1 = 10 ORDER BY c2";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalSort.class, query);
+ }
+
+ @Test
+ public void testUpdateWithLimitOnlyProducesCorrectPlanTree() {
+ String sql = "UPDATE t SET c1 = 10 LIMIT 50";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalLimit<?> limit = (LogicalLimit<?>) query;
+ Assertions.assertEquals(50, limit.getLimit());
+ }
+
+ @Test
+ public void testUpdateWithoutOrderByLimitProducesUpdateCommand() {
+ String sql = "UPDATE t SET c1 = 10 WHERE c2 = 1";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ // No sort or limit in query
+ Assertions.assertInstanceOf(LogicalFilter.class, query);
+ }
+
+ @Test
+ public void testDeleteWithMultipleOrderByColumns() {
+ String sql = "DELETE FROM t ORDER BY c1 ASC, c2 DESC NULLS LAST LIMIT
10";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalSort<?> sort = (LogicalSort<?>) ((LogicalLimit<?>)
query).child();
+ Assertions.assertEquals(2, sort.getOrderKeys().size());
+ }
+
+ @Test
+ public void testUpdateWithMultipleOrderByColumns() {
+ String sql = "UPDATE t SET c1 = 10 ORDER BY c2 ASC, c3 DESC NULLS
FIRST LIMIT 5";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalSort<?> sort = (LogicalSort<?>) ((LogicalLimit<?>)
query).child();
+ Assertions.assertEquals(2, sort.getOrderKeys().size());
+ }
+
+ @Test
+ public void testDeleteOrderByIntegerOrdinalConvertedToUnboundSlot() {
+ String sql = "DELETE FROM t ORDER BY 1 LIMIT 10";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalSort<?> sort = (LogicalSort<?>) ((LogicalLimit<?>)
query).child();
+ Assertions.assertEquals(1, sort.getOrderKeys().size());
+ // Integer ordinal should be converted to UnboundSlot, not remain as
IntegerLikeLiteral
+ Assertions.assertInstanceOf(UnboundSlot.class,
sort.getOrderKeys().get(0).getExpr());
+ Assertions.assertFalse(sort.getOrderKeys().get(0).getExpr() instanceof
IntegerLikeLiteral);
+ }
+
+ @Test
+ public void testUpdateOrderByIntegerOrdinalConvertedToUnboundSlot() {
+ String sql = "UPDATE t SET c1 = 10 ORDER BY 1 LIMIT 100";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ LogicalPlan query = ((UpdateCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalSort<?> sort = (LogicalSort<?>) ((LogicalLimit<?>)
query).child();
+ Assertions.assertEquals(1, sort.getOrderKeys().size());
+ Assertions.assertInstanceOf(UnboundSlot.class,
sort.getOrderKeys().get(0).getExpr());
+ Assertions.assertFalse(sort.getOrderKeys().get(0).getExpr() instanceof
IntegerLikeLiteral);
+ }
+
+ @Test
+ public void testDeleteOrderByMixedOrdinalAndColumn() {
+ String sql = "DELETE FROM t ORDER BY 1, c2 DESC LIMIT 5";
+ LogicalPlan plan = parser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ LogicalPlan query = ((DeleteFromUsingCommand) plan).getLogicalQuery();
+ Assertions.assertInstanceOf(LogicalLimit.class, query);
+ LogicalSort<?> sort = (LogicalSort<?>) ((LogicalLimit<?>)
query).child();
+ Assertions.assertEquals(2, sort.getOrderKeys().size());
+ // First key: integer ordinal converted to UnboundSlot
+ Assertions.assertInstanceOf(UnboundSlot.class,
sort.getOrderKeys().get(0).getExpr());
+ // Second key: column name remains as UnboundSlot
+ Assertions.assertInstanceOf(UnboundSlot.class,
sort.getOrderKeys().get(1).getExpr());
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
index 48320525a9d..977177161fd 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
@@ -42,11 +42,13 @@ import org.apache.doris.nereids.trees.plans.PlanType;
import
org.apache.doris.nereids.trees.plans.commands.CreateMaterializedViewCommand;
import org.apache.doris.nereids.trees.plans.commands.CreateTableCommand;
import org.apache.doris.nereids.trees.plans.commands.CreateViewCommand;
+import org.apache.doris.nereids.trees.plans.commands.DeleteFromUsingCommand;
import org.apache.doris.nereids.trees.plans.commands.DropTableCommand;
import org.apache.doris.nereids.trees.plans.commands.ExecuteActionCommand;
import org.apache.doris.nereids.trees.plans.commands.ExplainCommand;
import
org.apache.doris.nereids.trees.plans.commands.ExplainCommand.ExplainLevel;
import org.apache.doris.nereids.trees.plans.commands.ReplayCommand;
+import org.apache.doris.nereids.trees.plans.commands.UpdateCommand;
import org.apache.doris.nereids.trees.plans.commands.merge.MergeIntoCommand;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalCTE;
@@ -1524,4 +1526,81 @@ public class NereidsParserTest extends ParserTestBase {
String sql = "SELECT t.* FROM LATERAL unnest([1,2], ['hi','hello'])
WITH ORDINALITY AS t(c1,c2);";
parsePlan(sql).matches(logicalGenerate().when(plan ->
plan.getGenerators().get(0) instanceof Unnest));
}
+
+ @Test
+ public void testDeleteWithOrderByAndLimit() {
+ NereidsParser nereidsParser = new NereidsParser();
+
+ // DELETE with ORDER BY and LIMIT
+ String sql = "DELETE FROM t ORDER BY c1 LIMIT 10";
+ LogicalPlan plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ Assertions.assertEquals(StmtType.DELETE, plan.stmtType());
+
+ // DELETE with WHERE, ORDER BY DESC NULLS LAST, and LIMIT with offset
+ sql = "DELETE FROM t WHERE c1 > 0 ORDER BY c1 DESC NULLS LAST LIMIT 5,
10";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+
+ // DELETE with ORDER BY ASC NULLS FIRST and LIMIT with offset
+ sql = "DELETE FROM t ORDER BY c1 ASC NULLS FIRST LIMIT 10, 3";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+
+ // DELETE with LIMIT only
+ sql = "DELETE FROM t LIMIT 5";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+
+ // DELETE with ORDER BY only
+ sql = "DELETE FROM t ORDER BY c1";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+
+ // DELETE with LIMIT OFFSET syntax
+ sql = "DELETE FROM t ORDER BY c1 LIMIT 10 OFFSET 5";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+
+ // DELETE with multiple ORDER BY columns
+ sql = "DELETE FROM t ORDER BY c1 ASC, c2 DESC LIMIT 10";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(DeleteFromUsingCommand.class, plan);
+ }
+
+ @Test
+ public void testUpdateWithOrderByAndLimit() {
+ NereidsParser nereidsParser = new NereidsParser();
+
+ // UPDATE with ORDER BY and LIMIT
+ String sql = "UPDATE t SET c1 = 10 ORDER BY c2 LIMIT 100";
+ LogicalPlan plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ Assertions.assertEquals(StmtType.UPDATE, plan.stmtType());
+
+ // UPDATE with WHERE, ORDER BY DESC, and LIMIT with offset
+ sql = "UPDATE t SET c1 = 10 WHERE c2 > 5 ORDER BY c2 DESC LIMIT 100,
20";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+
+ // UPDATE with LIMIT only
+ sql = "UPDATE t SET c1 = 10 LIMIT 50";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+
+ // UPDATE with ORDER BY only
+ sql = "UPDATE t SET c1 = 10 ORDER BY c2 ASC NULLS FIRST";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+
+ // UPDATE with LIMIT OFFSET syntax
+ sql = "UPDATE t SET c1 = 10 ORDER BY c2 LIMIT 20 OFFSET 10";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+
+ // UPDATE with multiple ORDER BY columns
+ sql = "UPDATE t SET c1 = 10 ORDER BY c2 ASC, c3 DESC NULLS LAST LIMIT
5";
+ plan = nereidsParser.parseSingle(sql);
+ Assertions.assertInstanceOf(UpdateCommand.class, plan);
+ }
}
diff --git a/regression-test/data/delete_p0/test_delete_order_by_limit.out
b/regression-test/data/delete_p0/test_delete_order_by_limit.out
new file mode 100644
index 00000000000..d8474eb877f
--- /dev/null
+++ b/regression-test/data/delete_p0/test_delete_order_by_limit.out
@@ -0,0 +1,61 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !before_delete --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 h
+9 20 i
+
+-- !delete_order_limit --
+1 100 a
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+
+-- !delete_order_limit_offset --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+9 20 i
+
+-- !delete_where_order_limit --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+8 30 h
+9 20 i
+
+-- !delete_order_desc_limit --
+10 10 j
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 h
+9 20 i
+
+-- !delete_limit_offset_syntax --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+
diff --git a/regression-test/data/update/test_update_order_by_limit.out
b/regression-test/data/update/test_update_order_by_limit.out
new file mode 100644
index 00000000000..7eb546d8a23
--- /dev/null
+++ b/regression-test/data/update/test_update_order_by_limit.out
@@ -0,0 +1,85 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !before_update --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 h
+9 20 i
+
+-- !update_order_limit --
+1 100 a
+10 10 updated
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 updated
+9 20 updated
+
+-- !update_order_limit_offset --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 updated
+7 40 updated
+8 30 updated
+9 20 i
+
+-- !update_where_order_limit --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 updated
+7 40 updated
+8 30 h
+9 20 i
+
+-- !update_order_desc_limit --
+1 100 updated
+10 10 j
+2 90 updated
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 h
+9 20 i
+
+-- !update_limit_offset_syntax --
+1 100 a
+10 10 j
+2 90 b
+3 80 c
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 updated
+9 20 updated
+
+-- !update_multi_set --
+1 999 top3
+10 10 j
+2 999 top3
+3 999 top3
+4 70 d
+5 60 e
+6 50 f
+7 40 g
+8 30 h
+9 20 i
+
diff --git a/regression-test/suites/delete_p0/test_delete_order_by_limit.groovy
b/regression-test/suites/delete_p0/test_delete_order_by_limit.groovy
new file mode 100644
index 00000000000..b81e9b6cbb8
--- /dev/null
+++ b/regression-test/suites/delete_p0/test_delete_order_by_limit.groovy
@@ -0,0 +1,178 @@
+// 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_delete_order_by_limit") {
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+
+ // insert test data: 10 rows with id 1..10
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+ order_qt_before_delete """SELECT * FROM test_delete_obl ORDER BY id;"""
+
+ // test DELETE with ORDER BY and LIMIT: delete 3 rows with smallest c1
values
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8), 40(id=7), 50(id=6), ...
+ // LIMIT 3 means delete the first 3 rows: id=10, id=9, id=8
+ sql "DELETE FROM test_delete_obl ORDER BY c1 ASC LIMIT 3;"
+ order_qt_delete_order_limit """SELECT * FROM test_delete_obl ORDER BY
id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test DELETE with ORDER BY, LIMIT and OFFSET
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8), 40(id=7), 50(id=6), ...
+ // LIMIT 2, 3 means offset=2, limit=3: skip 2 rows (id=10, id=9), delete
next 3: id=8, id=7, id=6
+ sql "DELETE FROM test_delete_obl ORDER BY c1 ASC LIMIT 2, 3;"
+ order_qt_delete_order_limit_offset """SELECT * FROM test_delete_obl ORDER
BY id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test DELETE with WHERE, ORDER BY and LIMIT
+ // filter: c1 > 30 leaves: 100(id=1), 90(id=2), 80(id=3), 70(id=4),
60(id=5), 50(id=6), 40(id=7)
+ // order by c1 ASC: 40(id=7), 50(id=6), 60(id=5), 70(id=4), 80(id=3),
90(id=2), 100(id=1)
+ // LIMIT 2: delete id=7, id=6
+ sql "DELETE FROM test_delete_obl WHERE c1 > 30 ORDER BY c1 ASC LIMIT 2;"
+ order_qt_delete_where_order_limit """SELECT * FROM test_delete_obl ORDER
BY id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test DELETE with LIMIT only (no ORDER BY)
+ // delete any 3 rows (order is non-deterministic without ORDER BY)
+ sql "DELETE FROM test_delete_obl LIMIT 3;"
+ // just check the count
+ def result = sql "SELECT count(*) FROM test_delete_obl;"
+ assertEquals(7, result[0][0] as int)
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test DELETE with ORDER BY DESC and LIMIT
+ // c1 descending: 100(id=1), 90(id=2), 80(id=3), ...
+ // LIMIT 2: delete id=1, id=2
+ sql "DELETE FROM test_delete_obl ORDER BY c1 DESC LIMIT 2;"
+ order_qt_delete_order_desc_limit """SELECT * FROM test_delete_obl ORDER BY
id;"""
+
+ // test DELETE with LIMIT OFFSET syntax
+ sql "DROP TABLE IF EXISTS test_delete_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_delete_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_delete_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // LIMIT 2 OFFSET 1 means skip 1 row then delete next 2 rows
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8), ...
+ // skip 1 (id=10), delete 2 (id=9, id=8)
+ sql "DELETE FROM test_delete_obl ORDER BY c1 ASC LIMIT 2 OFFSET 1;"
+ order_qt_delete_limit_offset_syntax """SELECT * FROM test_delete_obl ORDER
BY id;"""
+}
diff --git a/regression-test/suites/update/test_update_order_by_limit.groovy
b/regression-test/suites/update/test_update_order_by_limit.groovy
new file mode 100644
index 00000000000..442b1857659
--- /dev/null
+++ b/regression-test/suites/update/test_update_order_by_limit.groovy
@@ -0,0 +1,175 @@
+// 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_update_order_by_limit") {
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+
+ // insert test data: 10 rows with id 1..10
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+ order_qt_before_update """SELECT * FROM test_update_obl ORDER BY id;"""
+
+ // test UPDATE with ORDER BY and LIMIT
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8)
+ // LIMIT 3 means update the first 3 rows: set c2='updated' for id=10,
id=9, id=8
+ sql "UPDATE test_update_obl SET c2 = 'updated' ORDER BY c1 ASC LIMIT 3;"
+ order_qt_update_order_limit """SELECT * FROM test_update_obl ORDER BY
id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test UPDATE with ORDER BY, LIMIT and OFFSET
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8), 40(id=7), 50(id=6)
+ // LIMIT 2, 3 means offset=2, limit=3: skip 2 rows (id=10, id=9), update
next 3: id=8, id=7, id=6
+ sql "UPDATE test_update_obl SET c2 = 'updated' ORDER BY c1 ASC LIMIT 2, 3;"
+ order_qt_update_order_limit_offset """SELECT * FROM test_update_obl ORDER
BY id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test UPDATE with WHERE, ORDER BY and LIMIT
+ // filter: c1 > 30 leaves: 100(id=1), 90(id=2), 80(id=3), 70(id=4),
60(id=5), 50(id=6), 40(id=7)
+ // order by c1 ASC: 40(id=7), 50(id=6), 60(id=5), 70(id=4), 80(id=3),
90(id=2), 100(id=1)
+ // LIMIT 2: update id=7, id=6
+ sql "UPDATE test_update_obl SET c2 = 'updated' WHERE c1 > 30 ORDER BY c1
ASC LIMIT 2;"
+ order_qt_update_where_order_limit """SELECT * FROM test_update_obl ORDER
BY id;"""
+
+ // reset data
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // test UPDATE with ORDER BY DESC and LIMIT
+ // c1 descending: 100(id=1), 90(id=2), 80(id=3)
+ // LIMIT 2: update id=1, id=2
+ sql "UPDATE test_update_obl SET c2 = 'updated' ORDER BY c1 DESC LIMIT 2;"
+ order_qt_update_order_desc_limit """SELECT * FROM test_update_obl ORDER BY
id;"""
+
+ // test UPDATE with LIMIT OFFSET syntax
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // LIMIT 2 OFFSET 1 means skip 1 then update next 2
+ // c1 ascending: 10(id=10), 20(id=9), 30(id=8)
+ // skip 1 (id=10), update 2 (id=9, id=8)
+ sql "UPDATE test_update_obl SET c2 = 'updated' ORDER BY c1 ASC LIMIT 2
OFFSET 1;"
+ order_qt_update_limit_offset_syntax """SELECT * FROM test_update_obl ORDER
BY id;"""
+
+ // test UPDATE with multiple SET assignments and ORDER BY LIMIT
+ sql "DROP TABLE IF EXISTS test_update_obl"
+ sql """
+ CREATE TABLE IF NOT EXISTS test_update_obl (
+ id int,
+ c1 int,
+ c2 varchar(32)
+ )
+ UNIQUE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ 'replication_num' = '1'
+ );
+ """
+ sql """
+ INSERT INTO test_update_obl VALUES
+ (1, 100, 'a'), (2, 90, 'b'), (3, 80, 'c'), (4, 70, 'd'), (5, 60,
'e'),
+ (6, 50, 'f'), (7, 40, 'g'), (8, 30, 'h'), (9, 20, 'i'), (10, 10,
'j');
+ """
+
+ // update both c1 and c2 for the 3 rows with largest c1
+ sql "UPDATE test_update_obl SET c1 = 999, c2 = 'top3' ORDER BY c1 DESC
LIMIT 3;"
+ order_qt_update_multi_set """SELECT * FROM test_update_obl ORDER BY id;"""
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
