ORDER BY ALL
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:
t53578_10001-Add-ORDER-BY-ALL-with-ASC-DESC-NULLS-support.patchapplication/octet-stream; name=0001-Add-ORDER-BY-ALL-with-ASC-DESC-NULLS-support.patchDownload+182-9
0002-Add-documentation-and-tests-for-ORDER-BY-ALL.patchapplication/octet-stream; name=0002-Add-documentation-and-tests-for-ORDER-BY-ALL.patchDownload+1166-6
On Tue, 24 Mar 2026 at 11:27, Rushabh Lathia <rushabh.lathia@gmail.com> 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/LASTThe 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 orderingPlease 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]/messages/by-id/CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com which ended up in nothing because of SQL
standard... So I wonder what is perspective of this thread
[0]: /messages/by-id/CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com
--
Best regards,
Kirill Reshke
On Tue, Mar 24, 2026 at 1:32 PM Kirill Reshke <reshkekirill@gmail.com>
wrote:
On Tue, 24 Mar 2026 at 11:27, Rushabh Lathia <rushabh.lathia@gmail.com>
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/LASTThe 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 orderingPlease take a look at the attached patch and let me know your thoughts.
Thanks,
Rushabh Lathia
www.EnterpriseDB.comHi! 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]
/messages/by-id/CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com
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
On 24.03.26 09:02, Kirill Reshke wrote:
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
The SQL working group decided not to standardize ORDER BY ALL. Which
means we could implement it without concern that it will be standardized
in a different way.
On 24.03.26 07:27, Rushabh Lathia wrote:
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/LASTThe 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.
I think this is a feature that is worth pursuing. The patch is still a
bit rough.
In the future, combine the patch with the code and the patch with the
test and documentation into one patch.
Find a place to add the regression tests, instead of adding a new file.
There are probably already places where ORDER BY syntax variants are
tested. In any case, it shouldn't be its own parallel group.
The plpgsql_misc fails. Apparently, the expected file is misformatted.
ORDER BY clauses don't only exist in top-level SELECT statements, they
also appear in aggregate and window functions, for example. This patch
doesn't handle this correctly. Consider:
select array_agg(a order by all) from t;
select rank() over (order by all) from t;
select percentile_disc(0.5) within group (order by all) from t;
These should either be rejected cleanly or do something useful.
Currently, they either crash and produce an internal error. (Also add
test cases.)
Hi Peter,
Thanks for your reply.
On Wed, Sep 23, 2026 at 3:00 PM Peter Eisentraut <peter@eisentraut.org>
wrote:
On 24.03.26 07:27, Rushabh Lathia wrote:
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/LASTThe 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.I think this is a feature that is worth pursuing. The patch is still a
bit rough.In the future, combine the patch with the code and the patch with the
test and documentation into one patch.
Okay, I will work on this.
Find a place to add the regression tests, instead of adding a new file.
There are probably already places where ORDER BY syntax variants are
tested. In any case, it shouldn't be its own parallel group.
Noted.
The plpgsql_misc fails. Apparently, the expected file is misformatted.
ORDER BY clauses don't only exist in top-level SELECT statements, they
also appear in aggregate and window functions, for example. This patch
doesn't handle this correctly. Consider:select array_agg(a order by all) from t;
select rank() over (order by all) from t;
select percentile_disc(0.5) within group (order by all) from t;These should either be rejected cleanly or do something useful.
Currently, they either crash and produce an internal error. (Also add
test cases.)
Sure will work on this.
Thanks,
Rushabh Lathia
Hi Peter,
Thanks for the review. Attached is v2, a single patch with the code, tests
and documentation together, as you asked.
1) Generated single patch (which includes code changes, test and doc).
2) Tests in existing files, not their own parallel group. I dropped the
standalone order_by_all test and its parallel_schedule entry.
The tests are now part of existing test files.
3) Fixed plpgsql_misc failure
4) ORDER BY ALL in aggregates, window functions and WITHIN GROUP. You were
right that v1 got this wrong. The marker
was accepted anywhere the grammar allows a sort clause and never
expanded. array_agg(a order by all) and the window
case failed with type with OID 0 does not exist, and the WITHIN GROUP
case crashed the backend. I chose to reject it cleanly,
since sorting inside an aggregate or window has nothing like "the
select list" to refer to. Added test for the same.
5) Patch is generated based on *76c0df7381e5e4d3fba06abddcb51216821c02bc*
commit.
Kindly let me know if anything is still unclear or missing.
Thanks,
On Fri, Nov 6, 2026 at 6:14 PM Rushabh Lathia <rushabh.lathia@gmail.com>
wrote:
Hi Peter,
Thanks for your reply.
On Wed, Sep 23, 2026 at 3:00 PM Peter Eisentraut <peter@eisentraut.org>
wrote:On 24.03.26 07:27, Rushabh Lathia wrote:
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/LASTThe 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.I think this is a feature that is worth pursuing. The patch is still a
bit rough.In the future, combine the patch with the code and the patch with the
test and documentation into one patch.Okay, I will work on this.
Find a place to add the regression tests, instead of adding a new file.
There are probably already places where ORDER BY syntax variants are
tested. In any case, it shouldn't be its own parallel group.Noted.
The plpgsql_misc fails. Apparently, the expected file is misformatted.
ORDER BY clauses don't only exist in top-level SELECT statements, they
also appear in aggregate and window functions, for example. This patch
doesn't handle this correctly. Consider:select array_agg(a order by all) from t;
select rank() over (order by all) from t;
select percentile_disc(0.5) within group (order by all) from t;These should either be rejected cleanly or do something useful.
Currently, they either crash and produce an internal error. (Also add
test cases.)Sure will work on this.
Thanks,
Rushabh Lathia
--
Rushabh Lathia
Hello!
This seems like a good feature, thanks for working on it.
I have two questions:
* ALL is added to the shared sort_clause, wouldn't be a separate select-level nonterminal (used only by the select_no_parens productions and PLpgSQL_Expr) simpler?
* Is Query.orderByAll needed? Isn't the list printed by the existing get_rule_orderby the same?
And some initial testing findings:
1. pg_dump can't restore an empty select list WITH TIES
create table tt(a int); insert into tt values (1),(2);
create view v_ties as select from tt order by all fetch first 1 row with ties;
select pg_get_viewdef('v_ties');
-- SELECT
-- FROM tt
-- FETCH FIRST (1) ROWS WITH TIES;
2. The deparse only looks at the first column's sort operator
create type pair as (x int, y int);
create view v1 as select row(1,2)::pair as p, 1 as n order by all;
create view v2 as select 1 as n, p from (values (row(1,2)::pair), (row(2,1)::pair)) s(p) order by all;
-- later: CREATE OPERATOR CLASS ... DEFAULT FOR TYPE pair USING btree (reversed ops)
select pg_get_viewdef('v1'); -> ORDER BY ALL USING < NULLS LAST; -- syntax error on restore
select pg_get_viewdef('v2'); -> ORDER BY ALL; -- restores, but returns (2,1),(1,2) instead of (1,2),(2,1)
-- same view with "order by n, p": ORDER BY 1::integer, p USING < NULLS LAST
3. errors can lose print error positions
(select 1 order by 1) order by all;
-- ERROR: multiple ORDER BY clauses not allowed
with recursive r(n) as (select 1 union all select n+1 from r where n<3 order by all) select * from r;
-- ERROR: ORDER BY in a recursive query is not implemented
4.
+ /*
+ * 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;
but
select ctid, a from t order by all; -- works, sorts by ctid
select xmin, a from t order by all; -- fails, no operator for xmin
so that comment seems wrong
HI Zsolt,
Thanks for the review.
On Tue, Nov 17, 2026 at 3:20 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:
Hello!
This seems like a good feature, thanks for working on it.
I have two questions:
* ALL is added to the shared sort_clause, wouldn't be a separate
select-level nonterminal (used only by the select_no_parens
productions and PLpgSQL_Expr) simpler?
Yes, a separate select-level nonterminal would be simpler. I went with the
shared sort_clause
because it lets us control the error message. Currently, misuse produces
“ORDER BY ALL is
only allowed in a query’s ORDER BY clause”, which is much clearer than a
generic
“syntax error at or near “all””.
That said, I don’t have a strong opinion here and I’m happy to switch if
that’s what everyone prefers.
* Is Query.orderByAll needed? Isn't the list printed by the existing
get_rule_orderby the same?
Good question. The existing get_rule_orderby would print a correct list,
but I think the flag is worth
keeping because the expanded form loses information in the deparsed output.
It isn’t what the user
wrote: pg_get_viewdef and pg_dump would show ORDER BY a DESC, b DESC
instead of ORDER BY ALL DESC,
which for a wide view gets long and hides the intent that every output
column is sorted. It is also noisier to read,
since set-operation views would show something like ORDER BY 1 DESC NULLS
LAST, 2 DESC NULLS LAST
rather than the much clearer ORDER BY ALL DESC NULLS LAST.
But looking further in the code, I see that currently there are planty of
example where pg_get_viewdef()
would not return the orginal view query defination. Few examples,
are SELECT * is expanded to the
column list at creation time, ORDER BY / GROUP BY ordinals and aliases are
resolved to the expression
or column, etc.
Considering this, I think removing Query.orderByAll will make code much
simpler and clear.
And some initial testing findings:
1. pg_dump can't restore an empty select list WITH TIES
create table tt(a int); insert into tt values (1),(2);
create view v_ties as select from tt order by all fetch first 1 row with
ties;
select pg_get_viewdef('v_ties');
-- SELECT
-- FROM tt
-- FETCH FIRST (1) ROWS WITH TIES;
This is a nice catch. This is problem when there is empty select list, for
the
non-empty select list output is already correct.
With no columns, ORDER BY ALL expands to an empty list, so there is nothing
to print. This needs the flag ( Query.orderByAll) we just removed, with
deparse
emitting ORDER BY ALL when sortClause is non-NIL or orderByAll is set. The
alternative is to reject the combination.
I am still keeping this as open for the opinion.
2. The deparse only looks at the first column's sort operator
create type pair as (x int, y int);
create view v1 as select row(1,2)::pair as p, 1 as n order by all;
create view v2 as select 1 as n, p from (values (row(1,2)::pair),
(row(2,1)::pair)) s(p) order by all;
-- later: CREATE OPERATOR CLASS ... DEFAULT FOR TYPE pair USING btree
(reversed ops)
select pg_get_viewdef('v1'); -> ORDER BY ALL USING < NULLS LAST;
-- syntax error on restore
select pg_get_viewdef('v2'); -> ORDER BY ALL; -- restores, but
returns (2,1),(1,2) instead of (1,2),(2,1)
-- same view with "order by n, p": ORDER BY 1::integer, p USING < NULLS
LAST
This is fixed, with the implementation of removing Query.orderByAll.
3. errors can lose print error positions
(select 1 order by 1) order by all;
-- ERROR: multiple ORDER BY clauses not allowed
with recursive r(n) as (select 1 union all select n+1 from r where n<3
order by all) select * from r;
-- ERROR: ORDER BY in a recursive query is not implemented
Fixed in the latest version.
4.
+ /* + * 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;but
select ctid, a from t order by all; -- works, sorts by ctid
select xmin, a from t order by all; -- fails, no operator for xminso that comment seems wrong
This is fixed, with the implementation of removing Query.orderByAll.
Attaching the v2 patch for further reviews.
Thanks,
Rushabh Lathia
www.EnterpriseDB.com