From: | Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> |
---|---|
To: | Chris Browne <cbbrowne(at)acm(dot)org> |
Subject: | Re: select random order by random |
Date: | 2007-11-02 21:49:19 |
Message-ID: | 472B9B5F.9070101@cheapcomplexdevices.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Chris Browne wrote:
> If I replicate your query, with extra columns, AND NAMES, I get the following:
>
> test(at)[local]:5433=# select random() as r1, random() as r2, random() as r3 from generate_series(1,10) order by random();
> r1 | r2 | r3
> --------------------+--------------------+-------------------
> 0.0288224648684263 | 0.904462072532624 | 0.27792159980163
> 0.144174488261342 | 0.406729203648865 | 0.452183415647596
> ...
> It is indeed somewhat curious that the query parser chose to interpret
> that the "order by random()" was referring to column #1.
And even more curiously, IMHO, even specifying
column names isn't enough. Note that this:
li=# select * from (select (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10) order by a) as x order by b;
a | b
---+----
0 | 8
1 | 10
3 | 4
4 | 8
5 | 1
5 | 9
6 | 4
6 | 5
8 | 4
9 | 0
(10 rows)
is sorted by "a" even though the outermost "order by"
clause explicitly said to order by "b".
Seems like it's a known odd behavior ...
http://archives.postgresql.org/pgsql-general/2006-11/msg01523.php
http://archives.postgresql.org/pgsql-general/2006-11/msg01539.php
http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php
From | Date | Subject | |
---|---|---|---|
Next Message | Myshkin LeVine | 2007-11-02 21:52:36 | Problem starting the server with Mac OSX |
Previous Message | Tom Lane | 2007-11-02 21:47:57 | Re: setting for maximum acceptable plan cost? |