On Mon, 2006-11-27 at 17:05 -0500, Tom Lane wrote: > Jeff Davis <pgsql@xxxxxxxxxxx> writes: > > On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote: > >> Shouldn't the results of this query shown here been sorted by "b" rather than by "a"? > > >> 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. What's the use-case for sorting by a volatile > expression in the first place? The only use case that I can think of is avoiding surprise during testing. random() and generate_series() are probably used rarely in real applications, as with any other volatile function aside from the sequence functions. However, they're frequently used when developing and testing applications. The only reason I mention this is because it might not always be so obvious when it's a sorting problem. If you do a GROUP BY, and it's grouping by the wrong column, I could see how that could be very confusing[1]. Granted, this is all for hypothetical, contrived testing scenarios. So it's not very compelling if it requires significant work to implement. Regards, Jeff Davis [1] This result certainly doesn't make much sense, although I suppose the query doesn't either: => select sum(a) as aa,b from (select distinct (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10)) x group by b; aa | b ----+---- 1 | 3 1 | 5 2 | 4 7 | 8 7 | 10 8 | 6 8 | 10 9 | 1 9 | 6 9 | 8 (10 rows)
