Re: select random order by random

On Thu, Nov 01, 2007 at 04:49:16PM +0000, Richard Huxton wrote:
> Gregory Stark wrote:
> >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;

If they were pure (in the functional programming sense) then this would
be a correct optimisation. However, if they're marked as volatile then
they should be called independently---they're not pure anymore and
you're calling the code for its side-effects and optimising out the
either call changes the semantics. Try playing around with monads in
Haskell or uniqueness types in Clean, they help to clarify what's going
on when you call a "function" in an impure language.

Sam