Re: select random order by random

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:

> I think that Piotr expected the random() to be evaluated in both
> places separately.
>
> My guess is that it was recognized by the planner as the same function
> and evaluated once per row only.
>
> If you try this:
>
> select random() from generate_series(1, 10) order by random()*1;
>
> then you'll get random ordering.

This does strike me as wrong. random() is marked volatile and the planner
ought not collapse multiple calls into one. Note that it affects other
volatile functions too:

postgres=# select nextval('s') from generate_series(1, 10) order by nextval('s');
nextval
---------
1
2
3
4
5
6
7
8
9
10
(10 rows)

postgres=# select nextval('s') from generate_series(1, 10) order by nextval('s');
nextval
---------
11
12
13
14
15
16
17
18
19
20
(10 rows)

That's certainly not how I remembered it working but I'm not sure I ever
tested it before.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!