Re: Unexpected sort order. - Mailing list pgsql-general

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"?
>
> I would have thought since "order by b" is in the outer sql statement it would have
> been the one the final result gets ordered by.
>
> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10) order by a) as
xorder by b; 
>  a | b
> ---+----
>  0 |  8
>  1 | 10
>  3 |  4
>  4 |  8
>  5 |  1
>  5 |  9
>  6 |  4
>  6 |  5
>  8 |  4
>  9 |  0
> (10 rows)
>
>
> Changing the constant from 10 to 11 in either but not both of the
> places produces results I would have expected; as do many other ways of
> rewriting the query.
>
> Unless I'm missing something, it seems the way I wrote the query creates
> some confusion of which of the two similar expressions with random()
> it's sorting by.

It looks like a planner bug.

Below are two plans; the first fails and the second succeeds. That leads
me to believe it's a planner bug, but what seems strangest to me is that
it does order by a, and not by some new evaluation of (random()*10).

=> explain 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;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=77.33..79.83 rows=1000 width=0)
   Sort Key: ((random() * 10::double precision))::integer
   ->  Function Scan on generate_series  (cost=0.00..27.50 rows=1000
width=0)
(3 rows)

Time: 0.584 ms

=> explain select * from (select (random()*10)::int as a, (random
()*11)::int as b from generate_series(1,10) order by a) as x order by b;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Sort  (cost=139.66..142.16 rows=1000 width=8)
   Sort Key: x.b
   ->  Sort  (cost=77.33..79.83 rows=1000 width=0)
         Sort Key: ((random() * 10::double precision))::integer
         ->  Function Scan on generate_series  (cost=0.00..27.50
rows=1000 width=0)
(5 rows)

You can apparently get the correct behavior on almost any kind of
rewriting of the query, including the mere addition of a "DESC" onto the
end.

However, the query also fails if you nest it as another subselect, like
so:

=> select a,b from (select a,b from (select (random()*10)::int as a,
(random()*10)::int as b from generate_series(1,10) order by a) as x) as
y order by b;

Regards,
    Jeff Davis