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
