I recently encountered some odd behavior with a query both selecting and
sorting by `random()`. When I posted about it on pgsql-bugs ^1, David
Johnston and Tom Lane provided some very detailed explanations as to
what was happening, but weren't sure whether or where information about
it could live comfortably in the docs. I think it's a useful addition;
it's not an everyday occurrence but I'm very much not the first person
to run into it. After a bit of looking, I think I've found a reasonable
location.
This patch revises
https://www.postgresql.org/docs/current/queries-order.html to discuss
sort expressions and options separately, and fits a caveat based on
Tom's suggested language (with an example) into the former section.
There are a few other minor tweaks included here:
- note that `*` is not an expression
- consolidate output column examples
- mention non-column sort expressions
I did write a query demonstrating the `group by` case Tom mentioned, but
expect that one's a lot less common.
1: https://www.postgresql.org/message-id/CZHAF947QQQO.27MAUK2SVMBXW%40nmfay.com