Re: select random order by random

On Nov 1, 2007, at 18:57, Tom Lane wrote:

> In the usual tradition of SQL99, the spec text is enormously less
> readable than SQL92 was, but I *think* this says nearly the same thing
> as what we do: a plain column reference in ORDER BY is first sought as
> an output column name, and failing that sought as a column name of one
> of the input tables. They are more restrictive than we are but that's
> OK.
>
> For the particular issue at hand here, it seems to me that 18.f.i.2.B
> dictates that a <sort key> matching an output column be treated as a
> reference to the column, not as an independently evaluated expression.
> Admittedly they are not talking about volatile functions per se, but
> I think there's some defense here for the way our parser does it.

But the described behavior (or rather its "obvious" extension to
Postgres) does not seem to match the OP's later example:

> select random() as xxx, random() from generate_series(1, 10) order
by random();

xxx | random
-------------------+--------------------
0.117905601913997 | 0.587338728172397
0.167445760298262 | 0.183822357647038
0.212947336590359 | 0.726537112484936
0.215260865732683 | 0.57848364467662
0.503411483719671 | 0.51932220557673
0.783855747796528 | 0.366456756538924
0.803222402838628 | 0.0357640516179446
0.917076966221015 | 0.918215564414028
0.937211547017662 | 0.146829404470897
0.987405426328725 | 0.308503020232778

Clearly the <sort key> is matched to the first output column, despite
its renaming. Contrast this with

... order by random; // plain column reference

This substantially breaks the principle of least surprise for me.

Caveat - this is on 7.4 (sigh), perhaps more modern versions have
different behavior.

- John D. Burger
MITRE