Tom Lane wrote: > Jeff Davis <pgsql@xxxxxxxxxxx> 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.
