Re: Unexpected sort order.

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