"Scott Marlowe" <scott.marlowe@xxxxxxxxx> writes: > On 11/1/07, Martijn van Oosterhout <kleptog@xxxxxxxxx> 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 ---------------------------(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
