| From: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
|---|---|
| To: | Peter Eisentraut <peter(at)eisentraut(dot)org> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: ORDER BY ALL |
| Date: | 2026-11-16 06:14:38 |
| Message-ID: | CAGPqQf1B9FH33fWA_0NcaTwcMhs-p8_KcoOmU=RsANbbdVRC5g@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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(dot)lathia(at)gmail(dot)com>
wrote:
> Hi Peter,
>
> Thanks for your reply.
>
> On Wed, Sep 23, 2026 at 3:00 PM Peter Eisentraut <peter(at)eisentraut(dot)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/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.
>>
>> 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
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Add-ORDER-BY-ALL_v2.patch | application/octet-stream | 45.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fujii Masao | 2026-11-16 06:19:02 | Re: 041_checkpoint_at_promote.pl might fail due to race condition on child kill |
| Previous Message | Sivirilova Marija | 2026-11-16 06:11:32 | Re: [***DKIM violation***] Re: [PATCH] Fix heap-buffer-overflow in PGTYPEStimestamp_defmt_scan() |