Re: select random order by random

Gregory Stark wrote:
> "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.

I think I agree with the earlier poster. Surely these two queries should
be equivalent?

SELECT random() FROM generate_series(1, 10) ORDER BY random();
SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo;

--
Richard Huxton
Archonet Ltd