public inbox for [email protected]help / color / mirror / Atom feed
ORDER BY ALL 3+ messages / 2 participants [nested] [flat]
* ORDER BY ALL @ 2026-03-24 06:27 Rushabh Lathia <[email protected]> 2026-03-24 08:02 ` Re: ORDER BY ALL Kirill Reshke <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Rushabh Lathia @ 2026-03-24 06:27 UTC (permalink / raw) To: pgsql-hackers 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY ALL 2026-03-24 06:27 ORDER BY ALL Rushabh Lathia <[email protected]> @ 2026-03-24 08:02 ` Kirill Reshke <[email protected]> 2026-03-24 09:50 ` Re: ORDER BY ALL Rushabh Lathia <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Kirill Reshke @ 2026-03-24 08:02 UTC (permalink / raw) To: Rushabh Lathia <[email protected]>; +Cc: pgsql-hackers On Tue, 24 Mar 2026 at 11:27, Rushabh Lathia <[email protected]> wrote: > > 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 > Hi! What about SQL standard compatibility? ef38a4d97 was merged only after the SQL committee accepted GROUP BY ALL, there was discussion a few years before [0] which ended up in nothing because of SQL standard... So I wonder what is perspective of this thread [0] https://www.postgresql.org/message-id/CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG%2Bi3-hXC0XQ%40mail.g... -- Best regards, Kirill Reshke ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY ALL 2026-03-24 06:27 ORDER BY ALL Rushabh Lathia <[email protected]> 2026-03-24 08:02 ` Re: ORDER BY ALL Kirill Reshke <[email protected]> @ 2026-03-24 09:50 ` Rushabh Lathia <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Rushabh Lathia @ 2026-03-24 09:50 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: pgsql-hackers On Tue, Mar 24, 2026 at 1:32 PM Kirill Reshke <[email protected]> wrote: > On Tue, 24 Mar 2026 at 11:27, Rushabh Lathia <[email protected]> > wrote: > > > > 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 > > > > Hi! What about SQL standard compatibility? ef38a4d97 was merged only > after the SQL committee accepted GROUP BY ALL, there was discussion a > few years before [0] which ended up in nothing because of SQL > standard... So I wonder what is perspective of this thread > > > [0] > https://www.postgresql.org/message-id/CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG%2Bi3-hXC0XQ%40mail.g... Thanks a lot for sharing this. I was not aware that ORDER BY ALL is not yet part of SQL Standards (my bad). > > > -- > Best regards, > Kirill Reshke > -- Rushabh Lathia ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-03-24 09:50 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-24 06:27 ORDER BY ALL Rushabh Lathia <[email protected]> 2026-03-24 08:02 ` Kirill Reshke <[email protected]> 2026-03-24 09:50 ` Rushabh Lathia <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox