Re: Unexpected sort order.

On Mon, 2006-11-27 at 17:05 -0500, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> 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)
asx 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)