I wrote:
> It looks to me like the planner thinks that order by a and order by b
> are equivalent because the expressions are equal(); hence it discards
> what it thinks is a redundant second sort step.
> ... What's the use-case for sorting by a volatile
> expression in the first place?
It may be worth pointing out that there are related gotchas without
bothering with anything as complicated as a sub-select. Consider
select random() from foo order by 1;
select random() from foo order by random();
Are these the same, or not? If you experiment you'll find out that
Postgres treats them the same --- random() is evaluated only once per
row of foo, and you get output that is sorted. Arguably for the
second case there should be two evaluations of random() per row, and you
should get output that appears randomly ordered (because the sort key
and the output value will be uncorrelated). If you do
select random() from foo order by random()+1;
then you do get two evaluations and random-looking output.
I'd be the first to admit that these various behaviors "just grew"
rather than being intentionally designed; no one has been thinking
about volatility in sort keys. The question remains whether it is
worth expending development effort and planning cycles to have a more
consistent definition. What's the use-case?
regards, tom lane