Re: select random order by random

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:

> On 11/1/07, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>> On Thu, Nov 01, 2007 at 02:22:58PM -0400, Tom Lane wrote:
>> > > SELECT random() FROM generate_series(1, 10) ORDER BY random();
>> > > SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo;
>> >
>> > (BTW, this is not the planner's fault; the collapsing of the two
>> > targetlist entries into one happens in the parser.)
>>
>> Something twigged telling me that in fact the latter expression is not
>> in standard SQL but a (very common) extension. A <sort key> is clearly
>> indicated to be a <value expression> with no indication anywhere that
>> column aliases are allowed here (though that may be in the common rules
>> somewhere).
>
> Well, the standard way I know if is to use column numbers. i.e.:
>
> select random() from generate_series(1,10) order by 1
>
> That I'm pretty sure IS in the standard. Don't see why column aliases
> would be disallowed. It's not like the where clause where the select
> field doesn't exist when it fires. The select field list does exist
> when order by fires, so referring to it makes sense.

Well IIRC the standard requires the sort keys to be columns from the select
list. You can't put any old expression there, only copies of the expressions
used in the select list.

So in the spec "random()" can't really be considered a second call to
random(), it's just a retyped instance of the "random()" in the select list.
That is, it's just a longwinded way of saying "order by 1" (meaning column 1).

So I guess having the parser do this substitution kind of makes sense if
you're thinking about things the way the spec does. It doesn't make much sense
if you're thinking the way Postgres does of having arbitrary expressions there
independent of what's in the select list.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning