Re: Unexpected sort order.

Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote:
>>> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10) order by a) as x order by b;
>
>> It looks like a planner bug.
>
> 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.
>
> I suppose we could add a check for whether the sort expression contains
> volatile functions before believing this, but I'm having a hard time
> believing that there are any real-world cases where the check wouldn't
> be a waste of cycles.

Would it be a smaller waste of cycles and still avoid the problem
if the planner blindly kept only the second sort step rather than
the first one when it sees these redundant steps? Or would that
get other cases wrong?

> What's the use-case for sorting by a volatile
> expression in the first place?
>

There was no use-case I had in mind when I reported it.
The order just surprised me so I thought I'd post it here.

If I wanted to make up a possible use case - hmm, perhaps random
sampling - but surely there would be better ways of doing that.
So nope, no real-world use cases I can make up - just a odd
result on a rather weirdly written query. None of my real
applications would care if it's not fixed.