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 x order 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
