diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 74ea907..6b2da66 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,6 +25,8 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
+ [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -56,6 +58,24 @@ DELETE FROM [ ONLY ] table_name [ *
+ If the ORDER BY clause is present, the rows will be
+ processed in the specified order. In its absence, rows are
+ processed in whichever order the system finds fastest to
+ produce.
+
+
+
+ If the LIMIT (or FETCH FIRST) clause
+ is present, processing will stop after the system has attempted
+ to delete the specified amount of rows. In particular, if a row
+ was concurrently changed not to match the given WHERE
+ clause, it will count towards the LIMIT despite it
+ not being actually deleted. Unlike in SELECT, the
+ OFFSET clause is not available in
+ DELETE.
+
+
+
The optional RETURNING clause causes DELETE
to compute and return value(s) based on each row actually deleted.
Any expression using the table's columns, and/or columns of other
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 7f565be..c2c9bfe 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -27,6 +27,8 @@ UPDATE [ ONLY ] table_name [ * ] [
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
+ [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -50,6 +52,24 @@ UPDATE [ ONLY ] table_name [ * ] [
+ If the ORDER BY clause is present, the rows will be
+ processed in the specified order. In its absence, rows are
+ processed in whichever order the system finds fastest to
+ produce.
+
+
+
+ If the LIMIT (or FETCH FIRST) clause
+ is present, processing will stop after the system has attempted
+ to update the specified amount of rows. In particular, if a row
+ was concurrently changed not to match the given WHERE
+ clause, it will count towards the LIMIT despite it
+ not being actually updated. Unlike in SELECT, the
+ OFFSET clause is not available in
+ UPDATE.
+
+
+
The optional RETURNING clause causes UPDATE
to compute and return value(s) based on each row actually updated.
Any expression using the table's columns, and/or columns of other
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 6f0f47e..e20d439 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -321,7 +321,7 @@ ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
- bool canSetTag)
+ int64_t *processed)
{
ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
@@ -484,8 +484,7 @@ ldelete:;
*/
}
- if (canSetTag)
- (estate->es_processed)++;
+ (*processed)++;
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid);
@@ -577,7 +576,7 @@ ExecUpdate(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
- bool canSetTag)
+ int64_t *processed)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
@@ -784,8 +783,7 @@ lreplace:;
estate);
}
- if (canSetTag)
- (estate->es_processed)++;
+ (*processed)++;
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple,
@@ -897,12 +895,37 @@ ExecModifyTable(ModifyTableState *node)
return NULL;
/*
- * On first call, fire BEFORE STATEMENT triggers before proceeding.
+ * On first call, evaluate LIMIT expression (assuming there is one), and
+ * fire BEFORE STATEMENT triggers before proceeding.
*/
- if (node->fireBSTriggers)
+ if (node->firstCall)
{
+ if (node->limitCount)
+ {
+ ExprContext *econtext = node->ps.ps_ExprContext;
+ Datum val;
+ bool isNull;
+
+ val = ExecEvalExprSwitchContext(node->limitCount,
+ econtext,
+ &isNull,
+ NULL);
+ if (isNull)
+ node->maxProcessed = -1;
+ else
+ {
+ node->maxProcessed = DatumGetInt64(val);
+ if (node->maxProcessed < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
+ errmsg("LIMIT must not be negative")));
+ }
+ }
+ else
+ node->maxProcessed = -1;
+
fireBSTriggers(node);
- node->fireBSTriggers = false;
+ node->firstCall = false;
}
/* Preload local variables */
@@ -928,6 +951,16 @@ ExecModifyTable(ModifyTableState *node)
for (;;)
{
/*
+ * Check for LIMIT. We must do this inside the loop in case we don't
+ * have a RETURNING clause.
+ */
+ if (node->processed == node->maxProcessed)
+ {
+ node->mt_done = true;
+ break;
+ }
+
+ /*
* Reset the per-output-tuple exprcontext. This is needed because
* triggers expect to use that context as workspace. It's a bit ugly
* to do this below the top level of the plan, however. We might need
@@ -1012,14 +1045,21 @@ ExecModifyTable(ModifyTableState *node)
{
case CMD_INSERT:
slot = ExecInsert(slot, planSlot, estate, node->canSetTag);
+ /* estate->es_processed already updated */
break;
case CMD_UPDATE:
slot = ExecUpdate(tupleid, oldtuple, slot, planSlot,
- &node->mt_epqstate, estate, node->canSetTag);
+ &node->mt_epqstate, estate, &node->processed);
+ /* keep EState up to date */
+ if (node->canSetTag)
+ estate->es_processed = node->processed;
break;
case CMD_DELETE:
slot = ExecDelete(tupleid, oldtuple, planSlot,
- &node->mt_epqstate, estate, node->canSetTag);
+ &node->mt_epqstate, estate, &node->processed);
+ /* keep EState up to date */
+ if (node->canSetTag)
+ estate->es_processed = node->processed;
break;
default:
elog(ERROR, "unknown operation");
@@ -1089,7 +1129,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
/* set up epqstate with dummy subplan data for the moment */
EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, NIL, node->epqParam);
- mtstate->fireBSTriggers = true;
+ mtstate->firstCall = true;
/*
* call ExecInitNode on each of the plans to be executed and save the
@@ -1365,6 +1405,19 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate);
/*
+ * If we had a LIMIT, initialize it. We will evaluate it before the first
+ * row is processed.
+ */
+ if (node->limitCount)
+ {
+ /* also create a context if RETURNING didn't already do that */
+ if (!mtstate->ps.ps_ExprContext)
+ mtstate->ps.ps_ExprContext = CreateExprContext(estate);
+ mtstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
+ (PlanState *) mtstate);
+ }
+
+ /*
* Lastly, if this is not the primary (canSetTag) ModifyTable node, add it
* to estate->es_auxmodifytables so that it will be run to completion by
* ExecPostprocessPlan. (It'd actually work fine to add the primary
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c89d808..5e54453 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -183,6 +183,7 @@ _copyModifyTable(const ModifyTable *from)
COPY_NODE_FIELD(fdwPrivLists);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
+ COPY_NODE_FIELD(limitCount);
return newnode;
}
@@ -2516,6 +2517,8 @@ _copyDeleteStmt(const DeleteStmt *from)
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
+ COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(limitClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
@@ -2531,6 +2534,8 @@ _copyUpdateStmt(const UpdateStmt *from)
COPY_NODE_FIELD(targetList);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
+ COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(limitClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 9793cf5..aa7f203 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -894,6 +894,8 @@ _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b)
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
+ COMPARE_NODE_FIELD(sortClause);
+ COMPARE_NODE_FIELD(limitClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
@@ -907,6 +909,8 @@ _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b)
COMPARE_NODE_FIELD(targetList);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
+ COMPARE_NODE_FIELD(sortClause);
+ COMPARE_NODE_FIELD(limitClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 123f2a6..4f2a918 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2970,6 +2970,10 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->whereClause, context))
return true;
+ if (walker(stmt->sortClause, context))
+ return true;
+ if (walker(stmt->limitClause, context))
+ return true;
if (walker(stmt->returningList, context))
return true;
if (walker(stmt->withClause, context))
@@ -2988,6 +2992,10 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->fromClause, context))
return true;
+ if (walker(stmt->sortClause, context))
+ return true;
+ if (walker(stmt->limitClause, context))
+ return true;
if (walker(stmt->returningList, context))
return true;
if (walker(stmt->withClause, context))
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index bfb4b9f..7e56e85 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -337,6 +337,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node)
WRITE_NODE_FIELD(fdwPrivLists);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
+ WRITE_NODE_FIELD(limitCount);
}
static void
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 184d37a..a3450bc 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4719,7 +4719,7 @@ make_modifytable(PlannerInfo *root,
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List *returningLists,
- List *rowMarks, int epqParam)
+ List *rowMarks, int epqParam, Node *limitCount)
{
ModifyTable *node = makeNode(ModifyTable);
Plan *plan = &node->plan;
@@ -4772,6 +4772,7 @@ make_modifytable(PlannerInfo *root,
node->returningLists = returningLists;
node->rowMarks = rowMarks;
node->epqParam = epqParam;
+ node->limitCount = limitCount;
/*
* For each result relation that is a foreign table, allow the FDW to
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 35bda67..e029a1f 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -609,7 +609,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
withCheckOptionLists,
returningLists,
rowMarks,
- SS_assign_special_param(root));
+ SS_assign_special_param(root),
+ parse->limitCount);
}
}
@@ -1015,7 +1016,8 @@ inheritance_planner(PlannerInfo *root)
withCheckOptionLists,
returningLists,
rowMarks,
- SS_assign_special_param(root));
+ SS_assign_special_param(root),
+ parse->limitCount);
}
/*--------------------
@@ -2428,6 +2430,11 @@ limit_needed(Query *parse)
{
Node *node;
+ /* ModifyTable handles the LIMIT for us if this is an UPDATE or a DELETE */
+ if (parse->commandType == CMD_UPDATE ||
+ parse->commandType == CMD_DELETE)
+ return false;
+
node = parse->limitCount;
if (node)
{
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7225bb6..b402892 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -386,6 +386,21 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /*
+ * Transform the sort clause. It might need to add items to our target
+ * list, but that's fine.
+ */
+ qry->targetList = NIL;
+ qry->sortClause = transformSortClause(pstate,
+ stmt->sortClause,
+ &qry->targetList,
+ EXPR_KIND_ORDER_BY,
+ true,
+ false);
+
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+ EXPR_KIND_LIMIT, "LIMIT");
+
qry->returningList = transformReturningList(pstate, stmt->returningList);
/* done building the range table and jointree */
@@ -1941,8 +1956,27 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
- qry->targetList = transformTargetList(pstate, stmt->targetList,
- EXPR_KIND_UPDATE_SOURCE);
+ /*
+ * Transform the sort clause and the target list. As stmt->targetList
+ * contains the SET expressions and not actual columns of the underlying
+ * table, we have to transform the ORDER BY first, potentially adding
+ * resjunk columns to the target list. Afterwards the actual SET
+ * expressions from stmt->targetList are added to the end of the target
+ * list.
+ */
+ qry->targetList = NIL;
+ qry->sortClause = transformSortClause(pstate,
+ stmt->sortClause,
+ &qry->targetList,
+ EXPR_KIND_ORDER_BY,
+ true,
+ false);
+ qry->targetList = list_concat(qry->targetList,
+ transformTargetList(pstate,
+ stmt->targetList,
+ EXPR_KIND_UPDATE_SOURCE));
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+ EXPR_KIND_LIMIT, "LIMIT");
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e3060a4..e551aaf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -388,7 +388,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type opt_column event cursor_options opt_hold opt_set_data
%type reindex_type drop_type comment_type security_label_type
-%type fetch_args limit_clause select_limit_value
+%type fetch_args limit_clause opt_limit_clause select_limit_value
offset_clause select_offset_value
select_offset_value2 opt_select_fetch_first_value
%type row_or_rows first_or_next
@@ -9125,13 +9125,15 @@ returning_clause:
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
- using_clause where_or_current_clause returning_clause
+ using_clause where_or_current_clause opt_sort_clause opt_limit_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
n->usingClause = $5;
n->whereClause = $6;
- n->returningList = $7;
+ n->sortClause = $7;
+ n->limitClause = $8;
+ n->returningList = $9;
n->withClause = $1;
$$ = (Node *)n;
}
@@ -9191,6 +9193,8 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
+ opt_sort_clause
+ opt_limit_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
@@ -9198,7 +9202,9 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
n->targetList = $5;
n->fromClause = $6;
n->whereClause = $7;
- n->returningList = $8;
+ n->sortClause = $8;
+ n->limitClause = $9;
+ n->returningList = $10;
n->withClause = $1;
$$ = (Node *)n;
}
@@ -9704,6 +9710,11 @@ limit_clause:
{ $$ = $3; }
;
+opt_limit_clause:
+ limit_clause { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
offset_clause:
OFFSET select_offset_value
{ $$ = $2; }
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index a301a08..35ec6ac 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1087,7 +1087,10 @@ typedef struct ModifyTableState
ResultRelInfo *resultRelInfo; /* per-subplan target relations */
List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */
EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */
- bool fireBSTriggers; /* do we need to fire stmt triggers? */
+ bool firstCall; /* have we processed any rows yet? */
+ ExprState *limitCount; /* LIMIT expression, if any */
+ int64_t maxProcessed; /* maximum number of rows we're allowed to process */
+ int64_t processed; /* number of rows we've processed so far */
} ModifyTableState;
/* ----------------
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b5011af..ecf634d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1043,6 +1043,8 @@ typedef struct DeleteStmt
RangeVar *relation; /* relation to delete from */
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
+ List *sortClause; /* ORDER BY */
+ Node *limitClause; /* LIMIT */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
} DeleteStmt;
@@ -1058,6 +1060,8 @@ typedef struct UpdateStmt
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
+ List *sortClause; /* ORDER BY */
+ Node *limitClause; /* LIMIT */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
} UpdateStmt;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 38c039c..e81ae4a 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -177,6 +177,7 @@ typedef struct ModifyTable
List *fdwPrivLists; /* per-target-table FDW private data lists */
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
+ Node *limitCount; /* maximum number of rows to process */
} ModifyTable;
/* ----------------
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 8bdb7db..3e5ed1f 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -84,7 +84,7 @@ extern ModifyTable *make_modifytable(PlannerInfo *root,
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List *returningLists,
- List *rowMarks, int epqParam);
+ List *rowMarks, int epqParam, Node *limitCount);
extern bool is_projection_capable_plan(Plan *plan);
/*
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 71b856f..d884a2a 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -41,6 +41,91 @@ SELECT * FROM update_test;
(2 rows)
--
+-- test ORDER BY
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b;
+ QUERY PLAN
+-------------------------------------
+ Update on update_test
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on update_test
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on update_test t
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoesnotexist;
+ERROR: column "columndoesnotexist" does not exist
+LINE 1: ...COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoes...
+ ^
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b + 0;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: ((b + 0))
+ -> Seq Scan on update_test t
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: ((b + 0))
+ -> Seq Scan on update_test t
+ Filter: (a = 10)
+(5 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t1 SET b = 20 FROM update_test t2 WHERE t1.a = t2.a ORDER BY t2.b;
+ QUERY PLAN
+----------------------------------------------------
+ Update on update_test t1
+ -> Sort
+ Sort Key: t2.b
+ -> Merge Join
+ Merge Cond: (t1.a = t2.a)
+ -> Sort
+ Sort Key: t1.a
+ -> Seq Scan on update_test t1
+ -> Sort
+ Sort Key: t2.a
+ -> Seq Scan on update_test t2
+(11 rows)
+
+UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+--
+-- test LIMIT
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 LIMIT 1;
+ QUERY PLAN
+-------------------------------
+ Update on update_test
+ -> Seq Scan on update_test
+(2 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b LIMIT 1;
+ QUERY PLAN
+-------------------------------------
+ Update on update_test
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on update_test
+(4 rows)
+
+UPDATE update_test SET b = 0 LIMIT 0 RETURNING *;
+ a | b | c
+---+---+---
+(0 rows)
+
+--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index a8a028f..0c91f8f 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -27,6 +27,32 @@ UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
--
+-- test ORDER BY
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b;
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoesnotexist;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b + 0;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t1 SET b = 20 FROM update_test t2 WHERE t1.a = t2.a ORDER BY t2.b;
+
+UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+
+--
+-- test LIMIT
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 LIMIT 1;
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b LIMIT 1;
+
+UPDATE update_test SET b = 0 LIMIT 0 RETURNING *;
+
+--
-- Test VALUES in FROM
--