Re: ORDER BY ALL

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.

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.)