[GENERAL] select random order by random

[piotr_sobolewski]

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?

-- 
Piotr Sobolewski
http://www.piotrsobolewski.w.pl


---(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

Re: [GENERAL] select random order by random

[piotr_sobolewski]

On Thursday 01 November 2007 17:08, brian wrote:

> > 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 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?
>
> Your query specifically requested that the result be ordered by the
> column "random" in the result set (the default ordering direction being
> ASC). Your query is semantically identical to:
> SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo ASC;

I also had such theory. But if I do such query:
select x from generate_series(1, 10) as x order by random();
the answer is shuffled in random order.
So why in one case this "random()" is treaded as a column name and in second - 
as function name?

And when I do such query:
select random() as xxx, random() from generate_series(1, 10) order by 
random();
your theory would predict that the answer is ordered by the second column (as 
the first one is renamed to 'xxx'). However in reality the answer is in 
random order.

> I should think that you would get a better result if you dropped the
> ORDER BY clause.

Yes, I know. However, once I made such request just for fun and curiosity, and 
found that I don't know why does it work like that. And since then I think 
about it and try to understand it - if in this case Postgres behaves the way 
I don't understand, I probably don't understand it well at all.

-- 
Piotr Sobolewski
http://www.piotrsobolewski.w.pl

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] select random order by random

[piotr_sobolewski]

On Thursday 01 November 2007 17:16, Scott Marlowe wrote:

> > > I was very surprised when I executed such SQL query (under PostgreSQL
> > > 8.2):
> > > select random() from generate_series(1, 10) order by random();
> > >
> (...)
> My guess is that it was recognized by the planner as the same function
> and evaluated once per row only.

I also had such hypothesis, but I think that query:
select random(), random() from generate_series(1, 10) order by random();
contradicts it.

-- 
Piotr Sobolewski
http://www.piotrsobolewski.w.pl

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/