Re: select random order by random

On 11/1/07, Lee Keel <lee(dot)keel(at)uai(dot)com> wrote:
> > Dear sirs,
> >
> > I was very surprised when I executed such SQL query (under PostgreSQL
> > 8.2):
> > select random() from generate_series(1, 10) order by random();
> >
> > I thought I would receive ten random numbers in random order. But I
> > received
> > ten random numbers sorted numerically:
> > random
> > -------------------
> > 0.102324520237744
> > 0.17704638838768
> > 0.533014383167028
> > 0.60182224214077
> > 0.644065519794822
> > 0.750732169486582
> > 0.821376844774932
> > 0.88221683120355
> > 0.889879426918924
> > 0.924697323236614
> > (10 rows)
> >
> > I don't understand - why the result is like that? It seems like in each
> > row
> > both random()s were giving the same result. Why is it like that? What
> > caused
> > it?
>
> Would this not have to do with the 'order by' you added to the end of the
> statement? If you remove the order by clause, then it works for me...

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.