public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rushabh Lathia <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: ORDER BY ALL
Date: Tue, 24 Mar 2026 11:57:09 +0530
Message-ID: <CAGPqQf2_b6FSsGm2TwtUu0pq5ruQpSYqTo4Y8jYMV4DP2E17Ng@mail.gmail.com> (raw)
Hi hackers,
Please find the attached patch, to implement the ORDER BY ALL clause.
Commit ef38a4d97, implemented GROUP BY ALL clause, and this
feature follows the same pattern.
ORDER BY ALL is a form of ORDER BY that automatically adds all
non-junk columns from the SELECT target list to the ORDER BY clause.
This implementation supports:
- ORDER BY ALL (default ascending order)
- ORDER BY ALL ASC
- ORDER BY ALL DESC
- ORDER BY ALL NULLS FIRST/LAST
- ORDER BY ALL ASC/DESC NULLS FIRST/LAST
The syntax works by creating a marker SortBy node with a NULL
node pointer that carries the sort direction and nulls ordering.
During query transformation, this marker is detected and expanded
to order by all non-junk columns in the target list with the
specified direction.
Implementation details:
- gram.y: Added ORDER BY ALL grammar with optional ASC/DESC and NULLS
in both main sort_clause and PLpgSQL_Expr rules
- parse_clause.c: Implemented ORDER BY ALL expansion logic that iterates
over target list columns
- analyze.c: Updated to pass orderByAll flag through transformation
- parsenodes.h: Added orderByAll boolean to SelectStmt and Query
- ruleutils.c: Added deparsing support for ORDER BY ALL that preserves
sort direction and NULLS ordering in view definitions, including proper
handling of implicit vs explicit ordering
Please take a look at the attached patch and let me know your thoughts.
Thanks,
Rushabh Lathia
www.EnterpriseDB.com
Attachments:
[application/octet-stream] 0001-Add-ORDER-BY-ALL-with-ASC-DESC-NULLS-support.patch (12.6K, 3-0001-Add-ORDER-BY-ALL-with-ASC-DESC-NULLS-support.patch)
download | inline diff:
From 0772e4bf7e22605ebd94e21cf220431a61efc30d Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Tue, 24 Mar 2026 11:34:33 +0530
Subject: [PATCH 1/2] Add ORDER BY ALL with ASC/DESC/NULLS support
ORDER BY ALL is a form of ORDER BY that automatically adds all non-junk
columns from the SELECT target list to the ORDER BY clause. This is
similar to GROUP BY ALL and is useful for data exploration.
This implementation supports:
- ORDER BY ALL (default ascending order)
- ORDER BY ALL ASC
- ORDER BY ALL DESC
- ORDER BY ALL NULLS FIRST/LAST
- ORDER BY ALL ASC/DESC NULLS FIRST/LAST
The syntax works by creating a special marker SortBy node with a NULL
node pointer that carries the sort direction and nulls ordering. During
query transformation, this marker is detected and expanded to order by
all non-junk columns in the target list with the specified direction.
Example usage:
SELECT a, b, c FROM table ORDER BY ALL DESC;
-- Equivalent to: ORDER BY a DESC, b DESC, c DESC
---
src/backend/parser/analyze.c | 17 ++++++++
src/backend/parser/gram.y | 43 ++++++++++++++++++++-
src/backend/parser/parse_agg.c | 1 +
src/backend/parser/parse_clause.c | 57 +++++++++++++++++++++++++++
src/backend/utils/adt/ruleutils.c | 64 ++++++++++++++++++++++++++++++-
src/include/nodes/parsenodes.h | 2 +
src/include/parser/parse_clause.h | 1 +
7 files changed, 182 insertions(+), 3 deletions(-)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 539c16c4f79..b57725b4c37 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1472,9 +1472,17 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt,
*/
qry->sortClause = transformSortClause(pstate,
stmt->sortClause,
+ stmt->orderByAll,
&qry->targetList,
EXPR_KIND_ORDER_BY,
false /* allow SQL92 rules */ );
+ /*
+ * Preserve the orderByAll flag for deparsing. Even though sortClause
+ * is expanded to individual columns during transformation, we need to
+ * remember that the original query used ORDER BY ALL so that
+ * pg_get_viewdef() can recreate it correctly in view definitions.
+ */
+ qry->orderByAll = stmt->orderByAll;
qry->groupClause = transformGroupClause(pstate,
stmt->groupClause,
@@ -1737,9 +1745,17 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
*/
qry->sortClause = transformSortClause(pstate,
stmt->sortClause,
+ stmt->orderByAll,
&qry->targetList,
EXPR_KIND_ORDER_BY,
false /* allow SQL92 rules */ );
+ /*
+ * Preserve the orderByAll flag for deparsing. Even though sortClause
+ * is expanded to individual columns during transformation, we need to
+ * remember that the original query used ORDER BY ALL so that
+ * pg_get_viewdef() can recreate it correctly in view definitions.
+ */
+ qry->orderByAll = stmt->orderByAll;
qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
EXPR_KIND_OFFSET, "OFFSET",
@@ -1981,6 +1997,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
qry->sortClause = transformSortClause(pstate,
sortClause,
+ stmt->orderByAll,
&qry->targetList,
EXPR_KIND_ORDER_BY,
false /* allow SQL92 rules */ );
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9cbe8eafc45..1a59dbe4fb2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -13465,6 +13465,21 @@ opt_sort_clause:
sort_clause:
ORDER BY sortby_list { $$ = $3; }
+ | ORDER BY ALL opt_asc_desc opt_nulls_order
+ {
+ /*
+ * Create a special marker SortBy node for ORDER BY ALL.
+ * We use a SortBy with node=NULL as the marker, and store
+ * the sort direction and nulls ordering to apply to all columns.
+ */
+ SortBy *sortby = makeNode(SortBy);
+ sortby->node = NULL; /* NULL node indicates ORDER BY ALL */
+ sortby->sortby_dir = $4;
+ sortby->sortby_nulls = $5;
+ sortby->useOp = NIL;
+ sortby->location = @2;
+ $$ = list_make1(sortby);
+ }
;
sortby_list:
@@ -17850,7 +17865,25 @@ PLpgSQL_Expr: opt_distinct_clause opt_target_list
n->groupByAll = ($5)->all;
n->havingClause = $6;
n->windowClause = $7;
- n->sortClause = $8;
+ /*
+ * Check for ORDER BY ALL marker (SortBy with NULL node).
+ * We detect this in the grammar action because PLpgSQL_Expr
+ * creates SelectStmt nodes directly without going through
+ * insertSelectOptions(), so we must set the orderByAll flag
+ * here to ensure it gets properly transformed.
+ */
+ if ($8 && list_length($8) == 1 &&
+ IsA(linitial($8), SortBy) &&
+ ((SortBy *) linitial($8))->node == NULL)
+ {
+ n->orderByAll = true;
+ /* Store the SortBy marker to preserve direction/nulls info */
+ n->sortClause = $8;
+ }
+ else
+ {
+ n->sortClause = $8;
+ }
if ($9)
{
n->limitOffset = $9->limitOffset;
@@ -19330,7 +19363,15 @@ insertSelectOptions(SelectStmt *stmt,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple ORDER BY clauses not allowed"),
parser_errposition(exprLocation((Node *) sortClause))));
+
stmt->sortClause = sortClause;
+ /* Check for ORDER BY ALL marker (SortBy with NULL node) */
+ if (list_length(sortClause) == 1 &&
+ IsA(linitial(sortClause), SortBy) &&
+ ((SortBy *) linitial(sortClause))->node == NULL)
+ {
+ stmt->orderByAll = true;
+ }
}
/* We can handle multiple locking clauses, though */
stmt->lockingClause = list_concat(stmt->lockingClause, lockingClause);
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 33fd2cccae5..53af2afda55 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -191,6 +191,7 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
torder = transformSortClause(pstate,
aggorder,
+ false /* ORDER BY ALL not applicable - aggregate ORDER BY sorts within groups, not query output */,
&tlist,
EXPR_KIND_ORDER_BY,
true /* force SQL99 rules */ );
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 06b65d4a605..1544f628088 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -2793,6 +2793,7 @@ transformGroupClause(ParseState *pstate, List *grouplist, bool groupByAll,
List *
transformSortClause(ParseState *pstate,
List *orderlist,
+ bool orderByAll,
List **targetlist,
ParseExprKind exprKind,
bool useSQL99)
@@ -2800,6 +2801,61 @@ transformSortClause(ParseState *pstate,
List *sortlist = NIL;
ListCell *olitem;
+ /*
+ * Handle ORDER BY ALL expansion.
+ *
+ * The target list has already been transformed and contains the
+ * columns we should sort by. We create a SortBy node for each
+ * non-junk target entry, applying the same sort direction and
+ * NULLS ordering specified in the marker to all columns.
+ */
+ if (orderByAll)
+ {
+ SortBy *sortby;
+ SortBy *all_marker;
+ SortByDir sort_dir;
+ SortByNulls nulls_order;
+
+ /*
+ * orderlist should contain a single SortBy marker with node=NULL
+ * that specifies the sort direction and nulls ordering to apply
+ * to all columns.
+ */
+ Assert(list_length(orderlist) == 1);
+ all_marker = (SortBy *) linitial(orderlist);
+ Assert(IsA(all_marker, SortBy) && all_marker->node == NULL);
+
+ /* Extract sort direction and nulls ordering from the marker */
+ sort_dir = all_marker->sortby_dir;
+ nulls_order = all_marker->sortby_nulls;
+
+ /* Iterate over targets, adding all non-junk columns to sort clause */
+ foreach_ptr(TargetEntry, tle, *targetlist)
+ {
+ /*
+ * Ignore junk TLEs. These are system columns (like ctid, xmin)
+ * or internal working columns that shouldn't be part of the
+ * user-visible ordering.
+ */
+ if (tle->resjunk)
+ continue;
+
+ /* Create a SortBy for this target entry with specified direction */
+ sortby = makeNode(SortBy);
+
+ sortby->node = (Node *) tle->expr;
+ sortby->sortby_dir = sort_dir;
+ sortby->sortby_nulls = nulls_order;
+ sortby->useOp = NIL;
+ sortby->location = exprLocation((Node *) tle->expr);
+
+ sortlist = addTargetToSortList(pstate, tle,
+ sortlist, *targetlist, sortby);
+ }
+
+ return sortlist;
+ }
+
foreach(olitem, orderlist)
{
SortBy *sortby = (SortBy *) lfirst(olitem);
@@ -2875,6 +2931,7 @@ transformWindowDefinitions(ParseState *pstate,
*/
orderClause = transformSortClause(pstate,
windef->orderClause,
+ false /* not applicable for window ORDER BY */ ,
targetlist,
EXPR_KIND_WINDOW_ORDER,
true /* force SQL99 rules */ );
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f16f1535785..44bbbd9420c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5942,8 +5942,68 @@ get_select_query_def(Query *query, deparse_context *context)
{
appendContextKeyword(context, " ORDER BY ",
-PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
- get_rule_orderby(query->sortClause, query->targetList,
- force_colno, context);
+ if (query->orderByAll)
+ {
+ SortGroupClause *srt;
+ TargetEntry *tle;
+ Node *sortexpr;
+ Oid sortcoltype;
+ TypeCacheEntry *typentry;
+
+ appendStringInfoString(buf, "ALL");
+
+ /*
+ * Extract sort direction and NULLS ordering from the first
+ * SortGroupClause. After transformation, sortClause contains
+ * SortGroupClause nodes (one per column). All columns share the
+ * same intended direction (ASC/DESC) and NULLS ordering, though
+ * the actual sort operators differ by column type. We examine the
+ * first column to determine what modifiers to output.
+ */
+ Assert(query->sortClause != NIL);
+ srt = (SortGroupClause *) linitial(query->sortClause);
+
+ /*
+ * Get the sort expression to determine its type without
+ * appending anything to the output buffer.
+ */
+ tle = get_sortgroupref_tle(srt->tleSortGroupRef, query->targetList);
+ sortexpr = (Node *) tle->expr;
+ sortcoltype = exprType(sortexpr);
+
+ /* See whether operator is default < or > for datatype */
+ typentry = lookup_type_cache(sortcoltype,
+ TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
+
+ if (srt->sortop == typentry->lt_opr)
+ {
+ /* ASC is default, so emit nothing for it */
+ if (srt->nulls_first)
+ appendStringInfoString(buf, " NULLS FIRST");
+ }
+ else if (srt->sortop == typentry->gt_opr)
+ {
+ appendStringInfoString(buf, " DESC");
+ /* DESC defaults to NULLS FIRST */
+ if (!srt->nulls_first)
+ appendStringInfoString(buf, " NULLS LAST");
+ }
+ else
+ {
+ appendStringInfo(buf, " USING %s",
+ generate_operator_name(srt->sortop,
+ sortcoltype,
+ sortcoltype));
+ /* be specific to eliminate ambiguity */
+ if (srt->nulls_first)
+ appendStringInfoString(buf, " NULLS FIRST");
+ else
+ appendStringInfoString(buf, " NULLS LAST");
+ }
+ }
+ else
+ get_rule_orderby(query->sortClause, query->targetList,
+ force_colno, context);
}
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4ee092206b0..867aac0158d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -226,6 +226,7 @@ typedef struct Query
List *distinctClause; /* a list of SortGroupClause's */
List *sortClause; /* a list of SortGroupClause's */
+ bool orderByAll; /* was ORDER BY ALL used? */
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
@@ -2242,6 +2243,7 @@ typedef struct SelectStmt
* SelectStmts.
*/
List *sortClause; /* sort clause (a list of SortBy's) */
+ bool orderByAll; /* Is this ORDER BY ALL? */
Node *limitOffset; /* # of result tuples to skip */
Node *limitCount; /* # of result tuples to return */
LimitOption limitOption; /* limit type */
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index fe234611007..5adc9aaf327 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -31,6 +31,7 @@ extern List *transformGroupClause(ParseState *pstate, List *grouplist,
List **targetlist, List *sortClause,
ParseExprKind exprKind, bool useSQL99);
extern List *transformSortClause(ParseState *pstate, List *orderlist,
+ bool orderByAll,
List **targetlist, ParseExprKind exprKind,
bool useSQL99);
--
2.43.0
[application/octet-stream] 0002-Add-documentation-and-tests-for-ORDER-BY-ALL.patch (36.3K, 4-0002-Add-documentation-and-tests-for-ORDER-BY-ALL.patch)
download | inline diff:
From 228372b8d05ed32240a32f9cacacffda74cbd348 Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Tue, 24 Mar 2026 11:34:50 +0530
Subject: [PATCH 2/2] Add documentation and tests for ORDER BY ALL
- Documentation: Added comprehensive documentation in queries.sgml and
reference pages (select.sgml, select_into.sgml)
- Tests: Added comprehensive regression tests covering regular SQL usage,
PL/pgSQL contexts (FOR loops, cursors, functions), deparsing of view
definitions, and negative test cases
---
doc/src/sgml/queries.sgml | 16 +
doc/src/sgml/ref/select.sgml | 15 +-
doc/src/sgml/ref/select_into.sgml | 2 +-
src/pl/plpgsql/src/expected/plpgsql_misc.out | 153 +++++
src/pl/plpgsql/src/sql/plpgsql_misc.sql | 121 ++++
src/test/regress/expected/order_by_all.out | 614 +++++++++++++++++++
src/test/regress/parallel_schedule | 1 +
src/test/regress/sql/order_by_all.sql | 247 ++++++++
8 files changed, 1166 insertions(+), 3 deletions(-)
create mode 100644 src/test/regress/expected/order_by_all.out
create mode 100644 src/test/regress/sql/order_by_all.sql
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 4b522213171..71dff217f05 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1911,6 +1911,22 @@ SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
column to match some other table column's name.
</para>
+ <para>
+ <productname>PostgreSQL</productname> also supports the syntax
+ <literal>ORDER BY ALL</literal>, which is equivalent to explicitly
+ writing all select-list columns. The sort direction (ascending or
+ descending) and null ordering can optionally be specified; these apply
+ to all columns. For example:
+<programlisting>
+SELECT a, b, c FROM table1 ORDER BY ALL;
+SELECT a, b, c FROM table1 ORDER BY ALL DESC;
+SELECT a, b, c FROM table1 ORDER BY ALL DESC NULLS LAST;
+</programlisting>
+ The first example is equivalent to <literal>ORDER BY a, b, c</literal>.
+ The second is equivalent to <literal>ORDER BY a DESC, b DESC, c DESC</literal>.
+ This syntax can simplify ad-hoc exploration of data.
+ </para>
+
<para>
<literal>ORDER BY</literal> can be applied to the result of a
<literal>UNION</literal>, <literal>INTERSECT</literal>, or <literal>EXCEPT</literal>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index ca5dd14d627..1355ba42d09 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -41,7 +41,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ HAVING <replaceable class="parameter">condition</replaceable> ]
[ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
- [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ ORDER BY { ALL [ ASC | DESC ] [ NULLS { FIRST | LAST } ] | <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] } ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
@@ -1395,7 +1395,7 @@ SELECT DISTINCT ON (location) location, time, report
<para>
The optional <literal>ORDER BY</literal> clause has this general form:
<synopsis>
-ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
+ORDER BY { ALL [ ASC | DESC ] [ NULLS { FIRST | LAST } ] | <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] }
</synopsis>
The <literal>ORDER BY</literal> clause causes the result rows to
be sorted according to the specified expression(s). If two rows are
@@ -1405,6 +1405,17 @@ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC |
an implementation-dependent order.
</para>
+ <para>
+ The form <literal>ORDER BY ALL</literal> with no explicit
+ <replaceable class="parameter">expression</replaceable> provided is
+ equivalent to writing <literal>ORDER BY</literal> with all columns of
+ the <command>SELECT</command> output list. The sort direction
+ (ascending or descending) and null ordering can optionally be specified
+ and apply to all columns. For example, <literal>ORDER BY ALL DESC</literal>
+ is equivalent to listing all output columns with <literal>DESC</literal>
+ after each.
+ </para>
+
<para>
Each <replaceable class="parameter">expression</replaceable> can be the
name or ordinal number of an output column
diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml
index cbf865ff838..2e06b9da4e3 100644
--- a/doc/src/sgml/ref/select_into.sgml
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -31,7 +31,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ HAVING <replaceable class="parameter">condition</replaceable> ]
[ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
- [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ ORDER BY { ALL [ ASC | DESC ] [ NULLS { FIRST | LAST } ] | <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] } ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
diff --git a/src/pl/plpgsql/src/expected/plpgsql_misc.out b/src/pl/plpgsql/src/expected/plpgsql_misc.out
index ffb377f5f54..29748846439 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_misc.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_misc.out
@@ -101,3 +101,156 @@ begin
end $$;
NOTICE: r.x = 1
NOTICE: r."foreach" = 2
+-- Test ORDER BY ALL in PL/pgSQL contexts
+-- This tests the PLpgSQL_Expr grammar rule changes for ORDER BY ALL support
+-- Create test table for ORDER BY ALL tests
+create table plpgsql_order_test (
+ a int,
+ b text,
+ c float
+);
+insert into plpgsql_order_test values
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5);
+-- Test ORDER BY ALL in FOR loop
+do $$
+declare
+ r record;
+begin
+ raise notice 'FOR loop with ORDER BY ALL:';
+ for r in select * from plpgsql_order_test order by all
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+NOTICE: FOR loop with ORDER BY ALL:
+NOTICE: a=1, b=bar, c=2.5
+NOTICE: a=1, b=qux, c=1.5
+NOTICE: a=2, b=baz, c=1.5
+NOTICE: a=3, b=foo, c=1.5
+-- Test ORDER BY ALL DESC in FOR loop
+do $$
+declare
+ r record;
+begin
+ raise notice 'FOR loop with ORDER BY ALL DESC:';
+ for r in select * from plpgsql_order_test order by all desc
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+NOTICE: FOR loop with ORDER BY ALL DESC:
+NOTICE: a=3, b=foo, c=1.5
+NOTICE: a=2, b=baz, c=1.5
+NOTICE: a=1, b=qux, c=1.5
+NOTICE: a=1, b=bar, c=2.5
+-- Test ORDER BY ALL with cursor
+do $$
+declare
+ cur cursor for select * from plpgsql_order_test order by all;
+ r record;
+begin
+ raise notice 'Cursor with ORDER BY ALL:';
+ open cur;
+ loop
+ fetch cur into r;
+ exit when not found;
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+ close cur;
+end $$;
+NOTICE: Cursor with ORDER BY ALL:
+NOTICE: a=1, b=bar, c=2.5
+NOTICE: a=1, b=qux, c=1.5
+NOTICE: a=2, b=baz, c=1.5
+NOTICE: a=3, b=foo, c=1.5
+-- Test ORDER BY ALL ASC NULLS FIRST in cursor
+do $$
+declare
+ cur cursor for select * from plpgsql_order_test order by all asc nulls first;
+ r record;
+begin
+ raise notice 'Cursor with ORDER BY ALL ASC NULLS FIRST:';
+ open cur;
+ loop
+ fetch cur into r;
+ exit when not found;
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+ close cur;
+end $$;
+NOTICE: Cursor with ORDER BY ALL ASC NULLS FIRST:
+NOTICE: a=1, b=bar, c=2.5
+NOTICE: a=1, b=qux, c=1.5
+NOTICE: a=2, b=baz, c=1.5
+NOTICE: a=3, b=foo, c=1.5
+-- Test ORDER BY ALL in function with RETURN QUERY
+create function test_order_by_all_return()
+returns table(a int, b text, c float) as $$
+begin
+ return query select * from plpgsql_order_test order by all;
+end;
+$$ language plpgsql;
+select * from test_order_by_all_return();
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+-- Test ORDER BY ALL DESC in function with RETURN QUERY
+create function test_order_by_all_desc_return()
+returns table(a int, b text, c float) as $$
+begin
+ return query select * from plpgsql_order_test order by all desc;
+end;
+$$ language plpgsql;
+select * from test_order_by_all_desc_return();
+ a | b | c
+---+-----+-----
+ 3 | foo | 1.5
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(4 rows)
+
+-- Test ORDER BY ALL with subset of columns
+do $$
+declare
+ r record;
+begin
+ raise notice 'ORDER BY ALL with column subset:';
+ for r in select a, b from plpgsql_order_test order by all
+ loop
+ raise notice 'a=%, b=%', r.a, r.b;
+ end loop;
+end $$;
+NOTICE: ORDER BY ALL with column subset:
+NOTICE: a=1, b=bar
+NOTICE: a=1, b=qux
+NOTICE: a=2, b=baz
+NOTICE: a=3, b=foo
+-- Test ORDER BY ALL NULLS LAST
+do $$
+declare
+ r record;
+begin
+ raise notice 'ORDER BY ALL NULLS LAST:';
+ for r in select * from plpgsql_order_test order by all nulls last
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+NOTICE: ORDER BY ALL NULLS LAST:
+NOTICE: a=1, b=bar, c=2.5
+NOTICE: a=1, b=qux, c=1.5
+NOTICE: a=2, b=baz, c=1.5
+NOTICE: a=3, b=foo, c=1.5
+-- Clean up
+drop function test_order_by_all_return();
+drop function test_order_by_all_desc_return();
+drop table plpgsql_order_test;
diff --git a/src/pl/plpgsql/src/sql/plpgsql_misc.sql b/src/pl/plpgsql/src/sql/plpgsql_misc.sql
index 0bc39fcf325..bfc7103146b 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_misc.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_misc.sql
@@ -66,3 +66,124 @@ begin
raise notice 'r.x = %', r.x;
raise notice 'r."foreach" = %', r."foreach"; -- ok
end $$;
+
+-- Test ORDER BY ALL in PL/pgSQL contexts
+-- This tests the PLpgSQL_Expr grammar rule changes for ORDER BY ALL support
+
+-- Create test table for ORDER BY ALL tests
+create table plpgsql_order_test (
+ a int,
+ b text,
+ c float
+);
+
+insert into plpgsql_order_test values
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5);
+
+-- Test ORDER BY ALL in FOR loop
+do $$
+declare
+ r record;
+begin
+ raise notice 'FOR loop with ORDER BY ALL:';
+ for r in select * from plpgsql_order_test order by all
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+
+-- Test ORDER BY ALL DESC in FOR loop
+do $$
+declare
+ r record;
+begin
+ raise notice 'FOR loop with ORDER BY ALL DESC:';
+ for r in select * from plpgsql_order_test order by all desc
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+
+-- Test ORDER BY ALL with cursor
+do $$
+declare
+ cur cursor for select * from plpgsql_order_test order by all;
+ r record;
+begin
+ raise notice 'Cursor with ORDER BY ALL:';
+ open cur;
+ loop
+ fetch cur into r;
+ exit when not found;
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+ close cur;
+end $$;
+
+-- Test ORDER BY ALL ASC NULLS FIRST in cursor
+do $$
+declare
+ cur cursor for select * from plpgsql_order_test order by all asc nulls first;
+ r record;
+begin
+ raise notice 'Cursor with ORDER BY ALL ASC NULLS FIRST:';
+ open cur;
+ loop
+ fetch cur into r;
+ exit when not found;
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+ close cur;
+end $$;
+
+-- Test ORDER BY ALL in function with RETURN QUERY
+create function test_order_by_all_return()
+returns table(a int, b text, c float) as $$
+begin
+ return query select * from plpgsql_order_test order by all;
+end;
+$$ language plpgsql;
+
+select * from test_order_by_all_return();
+
+-- Test ORDER BY ALL DESC in function with RETURN QUERY
+create function test_order_by_all_desc_return()
+returns table(a int, b text, c float) as $$
+begin
+ return query select * from plpgsql_order_test order by all desc;
+end;
+$$ language plpgsql;
+
+select * from test_order_by_all_desc_return();
+
+-- Test ORDER BY ALL with subset of columns
+do $$
+declare
+ r record;
+begin
+ raise notice 'ORDER BY ALL with column subset:';
+ for r in select a, b from plpgsql_order_test order by all
+ loop
+ raise notice 'a=%, b=%', r.a, r.b;
+ end loop;
+end $$;
+
+-- Test ORDER BY ALL NULLS LAST
+do $$
+declare
+ r record;
+begin
+ raise notice 'ORDER BY ALL NULLS LAST:';
+ for r in select * from plpgsql_order_test order by all nulls last
+ loop
+ raise notice 'a=%, b=%, c=%', r.a, r.b, r.c;
+ end loop;
+end $$;
+
+-- Clean up
+drop function test_order_by_all_return();
+drop function test_order_by_all_desc_return();
+drop table plpgsql_order_test;
diff --git a/src/test/regress/expected/order_by_all.out b/src/test/regress/expected/order_by_all.out
new file mode 100644
index 00000000000..840af89b16f
--- /dev/null
+++ b/src/test/regress/expected/order_by_all.out
@@ -0,0 +1,614 @@
+--
+-- Test ORDER BY ALL
+--
+CREATE TEMP TABLE test_order (
+ a int,
+ b text,
+ c float
+);
+INSERT INTO test_order VALUES
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5);
+-- Basic ORDER BY ALL test (default ASC)
+SELECT * FROM test_order ORDER BY ALL;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+-- ORDER BY ALL ASC (explicit)
+SELECT * FROM test_order ORDER BY ALL ASC;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+-- ORDER BY ALL DESC
+SELECT * FROM test_order ORDER BY ALL DESC;
+ a | b | c
+---+-----+-----
+ 3 | foo | 1.5
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(4 rows)
+
+-- ORDER BY ALL with NULLS FIRST
+SELECT * FROM test_order ORDER BY ALL NULLS FIRST;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+-- ORDER BY ALL DESC NULLS LAST
+SELECT * FROM test_order ORDER BY ALL DESC NULLS LAST;
+ a | b | c
+---+-----+-----
+ 3 | foo | 1.5
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(4 rows)
+
+-- ORDER BY ALL with specific columns in SELECT
+SELECT a, b FROM test_order ORDER BY ALL;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- ORDER BY ALL with expressions
+SELECT a, b, a + c AS sum FROM test_order ORDER BY ALL;
+ a | b | sum
+---+-----+-----
+ 1 | bar | 3.5
+ 1 | qux | 2.5
+ 2 | baz | 3.5
+ 3 | foo | 4.5
+(4 rows)
+
+-- ORDER BY ALL with aggregates (should only order by non-aggregated columns)
+SELECT a, COUNT(*) FROM test_order GROUP BY a ORDER BY ALL;
+ a | count
+---+-------
+ 1 | 2
+ 2 | 1
+ 3 | 1
+(3 rows)
+
+-- ORDER BY ALL with WHERE clause
+SELECT * FROM test_order WHERE a > 1 ORDER BY ALL;
+ a | b | c
+---+-----+-----
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(2 rows)
+
+-- Verify that ORDER BY ALL is equivalent to listing all columns
+SELECT * FROM test_order ORDER BY ALL;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+SELECT * FROM test_order ORDER BY a, b, c;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+-- ORDER BY ALL with LIMIT
+SELECT * FROM test_order ORDER BY ALL LIMIT 2;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+(2 rows)
+
+-- Test deparsing of ORDER BY ALL (ruleutils.c)
+-- Verify that ORDER BY ALL with modifiers is correctly preserved in view definitions
+-- View with ORDER BY ALL (default)
+CREATE VIEW view_order_all AS
+ SELECT * FROM test_order ORDER BY ALL;
+NOTICE: view "view_order_all" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all'::regclass, true);
+ pg_get_viewdef
+--------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order+
+ ORDER BY ALL;
+(1 row)
+
+-- View with ORDER BY ALL ASC
+CREATE VIEW view_order_all_asc AS
+ SELECT * FROM test_order ORDER BY ALL ASC;
+NOTICE: view "view_order_all_asc" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_asc'::regclass, true);
+ pg_get_viewdef
+--------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order+
+ ORDER BY ALL;
+(1 row)
+
+-- View with ORDER BY ALL DESC
+CREATE VIEW view_order_all_desc AS
+ SELECT * FROM test_order ORDER BY ALL DESC;
+NOTICE: view "view_order_all_desc" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_desc'::regclass, true);
+ pg_get_viewdef
+----------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order +
+ ORDER BY ALL DESC;
+(1 row)
+
+-- View with ORDER BY ALL NULLS FIRST
+CREATE VIEW view_order_all_nulls_first AS
+ SELECT * FROM test_order ORDER BY ALL NULLS FIRST;
+NOTICE: view "view_order_all_nulls_first" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_nulls_first'::regclass, true);
+ pg_get_viewdef
+-----------------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order +
+ ORDER BY ALL NULLS FIRST;
+(1 row)
+
+-- View with ORDER BY ALL NULLS LAST
+CREATE VIEW view_order_all_nulls_last AS
+ SELECT * FROM test_order ORDER BY ALL NULLS LAST;
+NOTICE: view "view_order_all_nulls_last" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_nulls_last'::regclass, true);
+ pg_get_viewdef
+--------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order+
+ ORDER BY ALL;
+(1 row)
+
+-- View with ORDER BY ALL DESC NULLS FIRST
+CREATE VIEW view_order_all_desc_nulls_first AS
+ SELECT * FROM test_order ORDER BY ALL DESC NULLS FIRST;
+NOTICE: view "view_order_all_desc_nulls_first" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_desc_nulls_first'::regclass, true);
+ pg_get_viewdef
+----------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order +
+ ORDER BY ALL DESC;
+(1 row)
+
+-- View with ORDER BY ALL ASC NULLS LAST
+CREATE VIEW view_order_all_asc_nulls_last AS
+ SELECT * FROM test_order ORDER BY ALL ASC NULLS LAST;
+NOTICE: view "view_order_all_asc_nulls_last" will be a temporary view
+DETAIL: It depends on temporary table test_order.
+SELECT pg_get_viewdef('view_order_all_asc_nulls_last'::regclass, true);
+ pg_get_viewdef
+--------------------
+ SELECT a, +
+ b, +
+ c +
+ FROM test_order+
+ ORDER BY ALL;
+(1 row)
+
+-- Verify the views actually work
+SELECT * FROM view_order_all;
+ a | b | c
+---+-----+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 3 | foo | 1.5
+(4 rows)
+
+SELECT * FROM view_order_all_desc;
+ a | b | c
+---+-----+-----
+ 3 | foo | 1.5
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(4 rows)
+
+-- Clean up views
+DROP VIEW view_order_all;
+DROP VIEW view_order_all_asc;
+DROP VIEW view_order_all_desc;
+DROP VIEW view_order_all_nulls_first;
+DROP VIEW view_order_all_nulls_last;
+DROP VIEW view_order_all_desc_nulls_first;
+DROP VIEW view_order_all_asc_nulls_last;
+-- Test with NULL values
+CREATE TEMP TABLE test_order_nulls (
+ a int,
+ b text,
+ c float
+);
+INSERT INTO test_order_nulls VALUES
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5),
+ (NULL, 'null_a', 3.0),
+ (2, NULL, 2.0),
+ (3, 'foo', NULL);
+-- ORDER BY ALL with NULLs (default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL;
+ a | b | c
+---+--------+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 2 | | 2
+ 3 | foo | 1.5
+ 3 | foo |
+ | null_a | 3
+(7 rows)
+
+-- ORDER BY ALL ASC (explicit) with NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL ASC;
+ a | b | c
+---+--------+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 2 | | 2
+ 3 | foo | 1.5
+ 3 | foo |
+ | null_a | 3
+(7 rows)
+
+-- ORDER BY ALL DESC with NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL DESC;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 3 | foo |
+ 3 | foo | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(7 rows)
+
+-- ORDER BY ALL NULLS FIRST (with default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL NULLS FIRST;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 3 | foo |
+ 3 | foo | 1.5
+(7 rows)
+
+-- ORDER BY ALL NULLS LAST (with default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL NULLS LAST;
+ a | b | c
+---+--------+-----
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | baz | 1.5
+ 2 | | 2
+ 3 | foo | 1.5
+ 3 | foo |
+ | null_a | 3
+(7 rows)
+
+-- ORDER BY ALL ASC NULLS FIRST
+SELECT * FROM test_order_nulls ORDER BY ALL ASC NULLS FIRST;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 1 | bar | 2.5
+ 1 | qux | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 3 | foo |
+ 3 | foo | 1.5
+(7 rows)
+
+-- ORDER BY ALL DESC NULLS LAST
+SELECT * FROM test_order_nulls ORDER BY ALL DESC NULLS LAST;
+ a | b | c
+---+--------+-----
+ 3 | foo | 1.5
+ 3 | foo |
+ 2 | baz | 1.5
+ 2 | | 2
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+ | null_a | 3
+(7 rows)
+
+-- ORDER BY ALL DESC NULLS FIRST
+SELECT * FROM test_order_nulls ORDER BY ALL DESC NULLS FIRST;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 3 | foo |
+ 3 | foo | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(7 rows)
+
+-- Verify ORDER BY ALL DESC is equivalent to listing all columns DESC
+SELECT * FROM test_order_nulls ORDER BY ALL DESC;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 3 | foo |
+ 3 | foo | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(7 rows)
+
+SELECT * FROM test_order_nulls ORDER BY a DESC, b DESC, c DESC;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 3 | foo |
+ 3 | foo | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(7 rows)
+
+-- Test with WHERE clause and NULLs
+SELECT * FROM test_order_nulls WHERE a IS NOT NULL ORDER BY ALL DESC;
+ a | b | c
+---+-----+-----
+ 3 | foo |
+ 3 | foo | 1.5
+ 2 | | 2
+ 2 | baz | 1.5
+ 1 | qux | 1.5
+ 1 | bar | 2.5
+(6 rows)
+
+-- Test with LIMIT and NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL DESC LIMIT 3;
+ a | b | c
+---+--------+-----
+ | null_a | 3
+ 3 | foo |
+ 3 | foo | 1.5
+(3 rows)
+
+-- Test with subset of columns and NULLs
+SELECT a, b FROM test_order_nulls ORDER BY ALL DESC;
+ a | b
+---+--------
+ | null_a
+ 3 | foo
+ 3 | foo
+ 2 |
+ 2 | baz
+ 1 | qux
+ 1 | bar
+(7 rows)
+
+-- Clean up
+DROP TABLE test_order_nulls;
+-- Negative tests: invalid ORDER BY ALL syntax
+-- ORDER BY ALL cannot be mixed with explicit column specifications
+SELECT * FROM test_order ORDER BY ALL, a;
+ERROR: syntax error at or near ","
+LINE 1: SELECT * FROM test_order ORDER BY ALL, a;
+ ^
+-- ORDER BY ALL cannot use USING operator
+SELECT * FROM test_order ORDER BY ALL USING <;
+ERROR: syntax error at or near "USING"
+LINE 1: SELECT * FROM test_order ORDER BY ALL USING <;
+ ^
+-- ORDER BY ALL with multiple ALL keywords (should fail)
+SELECT * FROM test_order ORDER BY ALL, ALL;
+ERROR: syntax error at or near ","
+LINE 1: SELECT * FROM test_order ORDER BY ALL, ALL;
+ ^
+-- ORDER BY ALL with set operations (UNION)
+SELECT a, b FROM test_order UNION SELECT a, b FROM test_order ORDER BY ALL;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- ORDER BY ALL with column number reference throw an error
+SELECT a, b FROM test_order ORDER BY ALL, 1;
+ERROR: syntax error at or near ","
+LINE 1: SELECT a, b FROM test_order ORDER BY ALL, 1;
+ ^
+-- Additional test coverage for edge cases
+-- Test ORDER BY ALL in subquery
+SELECT * FROM (SELECT a, b FROM test_order ORDER BY ALL) sq;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- Test ORDER BY ALL with nested subquery
+SELECT * FROM (
+ SELECT a, b FROM (
+ SELECT * FROM test_order ORDER BY ALL DESC
+ ) sub1 ORDER BY ALL
+) sub2;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- Test ORDER BY ALL with DISTINCT
+SELECT DISTINCT a, b FROM test_order ORDER BY ALL;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- Test ORDER BY ALL with JOIN
+CREATE TEMP TABLE test_order2 (
+ x int,
+ y text
+);
+INSERT INTO test_order2 VALUES (1, 'alpha'), (2, 'beta'), (3, 'gamma');
+SELECT t1.a, t1.b, t2.x, t2.y
+FROM test_order t1
+JOIN test_order2 t2 ON t1.a = t2.x
+ORDER BY ALL;
+ a | b | x | y
+---+-----+---+-------
+ 1 | bar | 1 | alpha
+ 1 | qux | 1 | alpha
+ 2 | baz | 2 | beta
+ 3 | foo | 3 | gamma
+(4 rows)
+
+DROP TABLE test_order2;
+-- Test ORDER BY ALL with CTE
+WITH cte AS (
+ SELECT a, b FROM test_order WHERE a > 1
+)
+SELECT * FROM cte ORDER BY ALL;
+ a | b
+---+-----
+ 2 | baz
+ 3 | foo
+(2 rows)
+
+-- Test ORDER BY ALL with window function (ORDER BY ALL in outer query)
+SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) as rn
+FROM test_order
+ORDER BY ALL;
+ a | b | rn
+---+-----+----
+ 1 | bar | 1
+ 1 | qux | 2
+ 2 | baz | 1
+ 3 | foo | 1
+(4 rows)
+
+-- Test ORDER BY ALL with INTERSECT (should fail like UNION)
+SELECT a, b FROM test_order INTERSECT SELECT a, b FROM test_order ORDER BY ALL;
+ a | b
+---+-----
+ 1 | bar
+ 1 | qux
+ 2 | baz
+ 3 | foo
+(4 rows)
+
+-- Test ORDER BY ALL with EXCEPT (should fail like UNION)
+SELECT a, b FROM test_order EXCEPT SELECT a, b FROM test_order ORDER BY ALL;
+ a | b
+---+---
+(0 rows)
+
+-- Test ORDER BY ALL with VALUES
+SELECT * FROM (VALUES (3, 'foo'), (1, 'bar'), (2, 'baz')) AS t(x, y) ORDER BY ALL;
+ x | y
+---+-----
+ 1 | bar
+ 2 | baz
+ 3 | foo
+(3 rows)
+
+-- Test ORDER BY ALL with only computed columns (no junk)
+SELECT a + 1 AS col1, b || '_test' AS col2 FROM test_order ORDER BY ALL;
+ col1 | col2
+------+----------
+ 2 | bar_test
+ 2 | qux_test
+ 3 | baz_test
+ 4 | foo_test
+(4 rows)
+
+-- Test behavior when all selected columns would be junk (using ctid/system columns)
+-- This should still work as system columns aren't marked as junk in target list when explicitly selected
+SELECT ctid FROM test_order ORDER BY ALL;
+ ctid
+-------
+ (0,1)
+ (0,2)
+ (0,3)
+ (0,4)
+(4 rows)
+
+-- Test ORDER BY ALL with UNION ALL in subquery (ORDER BY on outer query should work)
+SELECT * FROM (
+ SELECT a, b FROM test_order
+ UNION ALL
+ SELECT a, b FROM test_order
+) sub ORDER BY ALL;
+ a | b
+---+-----
+ 1 | bar
+ 1 | bar
+ 1 | qux
+ 1 | qux
+ 2 | baz
+ 2 | baz
+ 3 | foo
+ 3 | foo
+(8 rows)
+
+-- Clean up
+DROP TABLE test_order;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 549e9b2d7be..5caee1ac899 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -62,6 +62,7 @@ test: sanity_check
# join depends on create_misc
# ----------
test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update delete namespace prepared_xacts
+test: order_by_all
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/order_by_all.sql b/src/test/regress/sql/order_by_all.sql
new file mode 100644
index 00000000000..9b40831a0a9
--- /dev/null
+++ b/src/test/regress/sql/order_by_all.sql
@@ -0,0 +1,247 @@
+--
+-- Test ORDER BY ALL
+--
+
+CREATE TEMP TABLE test_order (
+ a int,
+ b text,
+ c float
+);
+
+INSERT INTO test_order VALUES
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5);
+
+-- Basic ORDER BY ALL test (default ASC)
+SELECT * FROM test_order ORDER BY ALL;
+
+-- ORDER BY ALL ASC (explicit)
+SELECT * FROM test_order ORDER BY ALL ASC;
+
+-- ORDER BY ALL DESC
+SELECT * FROM test_order ORDER BY ALL DESC;
+
+-- ORDER BY ALL with NULLS FIRST
+SELECT * FROM test_order ORDER BY ALL NULLS FIRST;
+
+-- ORDER BY ALL DESC NULLS LAST
+SELECT * FROM test_order ORDER BY ALL DESC NULLS LAST;
+
+-- ORDER BY ALL with specific columns in SELECT
+SELECT a, b FROM test_order ORDER BY ALL;
+
+-- ORDER BY ALL with expressions
+SELECT a, b, a + c AS sum FROM test_order ORDER BY ALL;
+
+-- ORDER BY ALL with aggregates (should only order by non-aggregated columns)
+SELECT a, COUNT(*) FROM test_order GROUP BY a ORDER BY ALL;
+
+-- ORDER BY ALL with WHERE clause
+SELECT * FROM test_order WHERE a > 1 ORDER BY ALL;
+
+-- Verify that ORDER BY ALL is equivalent to listing all columns
+SELECT * FROM test_order ORDER BY ALL;
+SELECT * FROM test_order ORDER BY a, b, c;
+
+-- ORDER BY ALL with LIMIT
+SELECT * FROM test_order ORDER BY ALL LIMIT 2;
+
+-- Test deparsing of ORDER BY ALL (ruleutils.c)
+-- Verify that ORDER BY ALL with modifiers is correctly preserved in view definitions
+
+-- View with ORDER BY ALL (default)
+CREATE VIEW view_order_all AS
+ SELECT * FROM test_order ORDER BY ALL;
+
+SELECT pg_get_viewdef('view_order_all'::regclass, true);
+
+-- View with ORDER BY ALL ASC
+CREATE VIEW view_order_all_asc AS
+ SELECT * FROM test_order ORDER BY ALL ASC;
+
+SELECT pg_get_viewdef('view_order_all_asc'::regclass, true);
+
+-- View with ORDER BY ALL DESC
+CREATE VIEW view_order_all_desc AS
+ SELECT * FROM test_order ORDER BY ALL DESC;
+
+SELECT pg_get_viewdef('view_order_all_desc'::regclass, true);
+
+-- View with ORDER BY ALL NULLS FIRST
+CREATE VIEW view_order_all_nulls_first AS
+ SELECT * FROM test_order ORDER BY ALL NULLS FIRST;
+
+SELECT pg_get_viewdef('view_order_all_nulls_first'::regclass, true);
+
+-- View with ORDER BY ALL NULLS LAST
+CREATE VIEW view_order_all_nulls_last AS
+ SELECT * FROM test_order ORDER BY ALL NULLS LAST;
+
+SELECT pg_get_viewdef('view_order_all_nulls_last'::regclass, true);
+
+-- View with ORDER BY ALL DESC NULLS FIRST
+CREATE VIEW view_order_all_desc_nulls_first AS
+ SELECT * FROM test_order ORDER BY ALL DESC NULLS FIRST;
+
+SELECT pg_get_viewdef('view_order_all_desc_nulls_first'::regclass, true);
+
+-- View with ORDER BY ALL ASC NULLS LAST
+CREATE VIEW view_order_all_asc_nulls_last AS
+ SELECT * FROM test_order ORDER BY ALL ASC NULLS LAST;
+
+SELECT pg_get_viewdef('view_order_all_asc_nulls_last'::regclass, true);
+
+-- Verify the views actually work
+SELECT * FROM view_order_all;
+SELECT * FROM view_order_all_desc;
+
+-- Clean up views
+DROP VIEW view_order_all;
+DROP VIEW view_order_all_asc;
+DROP VIEW view_order_all_desc;
+DROP VIEW view_order_all_nulls_first;
+DROP VIEW view_order_all_nulls_last;
+DROP VIEW view_order_all_desc_nulls_first;
+DROP VIEW view_order_all_asc_nulls_last;
+
+-- Test with NULL values
+CREATE TEMP TABLE test_order_nulls (
+ a int,
+ b text,
+ c float
+);
+
+INSERT INTO test_order_nulls VALUES
+ (3, 'foo', 1.5),
+ (1, 'bar', 2.5),
+ (2, 'baz', 1.5),
+ (1, 'qux', 1.5),
+ (NULL, 'null_a', 3.0),
+ (2, NULL, 2.0),
+ (3, 'foo', NULL);
+
+-- ORDER BY ALL with NULLs (default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL;
+
+-- ORDER BY ALL ASC (explicit) with NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL ASC;
+
+-- ORDER BY ALL DESC with NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL DESC;
+
+-- ORDER BY ALL NULLS FIRST (with default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL NULLS FIRST;
+
+-- ORDER BY ALL NULLS LAST (with default ASC)
+SELECT * FROM test_order_nulls ORDER BY ALL NULLS LAST;
+
+-- ORDER BY ALL ASC NULLS FIRST
+SELECT * FROM test_order_nulls ORDER BY ALL ASC NULLS FIRST;
+
+-- ORDER BY ALL DESC NULLS LAST
+SELECT * FROM test_order_nulls ORDER BY ALL DESC NULLS LAST;
+
+-- ORDER BY ALL DESC NULLS FIRST
+SELECT * FROM test_order_nulls ORDER BY ALL DESC NULLS FIRST;
+
+-- Verify ORDER BY ALL DESC is equivalent to listing all columns DESC
+SELECT * FROM test_order_nulls ORDER BY ALL DESC;
+SELECT * FROM test_order_nulls ORDER BY a DESC, b DESC, c DESC;
+
+-- Test with WHERE clause and NULLs
+SELECT * FROM test_order_nulls WHERE a IS NOT NULL ORDER BY ALL DESC;
+
+-- Test with LIMIT and NULLs
+SELECT * FROM test_order_nulls ORDER BY ALL DESC LIMIT 3;
+
+-- Test with subset of columns and NULLs
+SELECT a, b FROM test_order_nulls ORDER BY ALL DESC;
+
+-- Clean up
+DROP TABLE test_order_nulls;
+
+-- Negative tests: invalid ORDER BY ALL syntax
+
+-- ORDER BY ALL cannot be mixed with explicit column specifications
+SELECT * FROM test_order ORDER BY ALL, a;
+
+-- ORDER BY ALL cannot use USING operator
+SELECT * FROM test_order ORDER BY ALL USING <;
+
+-- ORDER BY ALL with multiple ALL keywords (should fail)
+SELECT * FROM test_order ORDER BY ALL, ALL;
+
+-- ORDER BY ALL with set operations (UNION)
+SELECT a, b FROM test_order UNION SELECT a, b FROM test_order ORDER BY ALL;
+
+-- ORDER BY ALL with column number reference throw an error
+SELECT a, b FROM test_order ORDER BY ALL, 1;
+
+-- Additional test coverage for edge cases
+
+-- Test ORDER BY ALL in subquery
+SELECT * FROM (SELECT a, b FROM test_order ORDER BY ALL) sq;
+
+-- Test ORDER BY ALL with nested subquery
+SELECT * FROM (
+ SELECT a, b FROM (
+ SELECT * FROM test_order ORDER BY ALL DESC
+ ) sub1 ORDER BY ALL
+) sub2;
+
+-- Test ORDER BY ALL with DISTINCT
+SELECT DISTINCT a, b FROM test_order ORDER BY ALL;
+
+-- Test ORDER BY ALL with JOIN
+CREATE TEMP TABLE test_order2 (
+ x int,
+ y text
+);
+
+INSERT INTO test_order2 VALUES (1, 'alpha'), (2, 'beta'), (3, 'gamma');
+
+SELECT t1.a, t1.b, t2.x, t2.y
+FROM test_order t1
+JOIN test_order2 t2 ON t1.a = t2.x
+ORDER BY ALL;
+
+DROP TABLE test_order2;
+
+-- Test ORDER BY ALL with CTE
+WITH cte AS (
+ SELECT a, b FROM test_order WHERE a > 1
+)
+SELECT * FROM cte ORDER BY ALL;
+
+-- Test ORDER BY ALL with window function (ORDER BY ALL in outer query)
+SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) as rn
+FROM test_order
+ORDER BY ALL;
+
+-- Test ORDER BY ALL with INTERSECT (should fail like UNION)
+SELECT a, b FROM test_order INTERSECT SELECT a, b FROM test_order ORDER BY ALL;
+
+-- Test ORDER BY ALL with EXCEPT (should fail like UNION)
+SELECT a, b FROM test_order EXCEPT SELECT a, b FROM test_order ORDER BY ALL;
+
+-- Test ORDER BY ALL with VALUES
+SELECT * FROM (VALUES (3, 'foo'), (1, 'bar'), (2, 'baz')) AS t(x, y) ORDER BY ALL;
+
+-- Test ORDER BY ALL with only computed columns (no junk)
+SELECT a + 1 AS col1, b || '_test' AS col2 FROM test_order ORDER BY ALL;
+
+-- Test behavior when all selected columns would be junk (using ctid/system columns)
+-- This should still work as system columns aren't marked as junk in target list when explicitly selected
+SELECT ctid FROM test_order ORDER BY ALL;
+
+-- Test ORDER BY ALL with UNION ALL in subquery (ORDER BY on outer query should work)
+SELECT * FROM (
+ SELECT a, b FROM test_order
+ UNION ALL
+ SELECT a, b FROM test_order
+) sub ORDER BY ALL;
+
+-- Clean up
+DROP TABLE test_order;
--
2.43.0
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: ORDER BY ALL
In-Reply-To: <CAGPqQf2_b6FSsGm2TwtUu0pq5ruQpSYqTo4Y8jYMV4DP2E17Ng@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox