Re: Strange behavior of the random() function

r(dot)zharkov(at)postgrespro(dot)ru writes:
> Can anybody explain me that strange behavior?

It's a squishiness in the SQL language, if you ask me. Consider this
simplified query:

select random() from generate_series(1, 3) order by random();

Would you expect the output of this query to appear ordered, or not?
There's an argument to be made that the two instances of random() ought
to be executed independently for each row, so that the output would
(probably) seem unordered. But practical usage generally expects that
we unify the two textually-identical expressions, so that the behavior
is the same as

select random() from generate_series(1, 3) order by 1;

> select random() as "rnd", random() as "rnd1", random() as "rnd2"
> from generate_series( 1, 3 )
> order by random();
> All values in any row are the same.

Here, we've unified *all* the appearances of the ORDER BY expression in
the SELECT-list. Maybe that's wrong, but it's hard to make a principled
argument why we shouldn't do it. If only one of them should be unified
with the ORDER BY expression, then which one?

> select random() as "rnd", random() as "rnd1", random() as "rnd2"
> from generate_series( 1, 3 )
> order by random() || 'test';

Here, the ORDER BY expression is not a match to any SELECT-list
expression, so they don't get unified with it.

If you'd like more control over this sort of thing, I'd recommend
using a sub-select, ie

select x,y,z from
(select random() as x, random() as y, random() as z
from generate_series(1,3)) ss
order by x;

which makes it clear what your intention is.

There's some interesting related behaviors: compare the outputs of

select random() as x, random() as y, random() as z
from generate_series(1,3) order by 1;
select random() as x, random() as y, random() as z
from generate_series(1,3) order by 1,2;
select random() as x, random() as y, random() as z
from generate_series(1,3) order by 1,2,3;

I could buy the idea that there's a bug involved in that; but again
the question is exactly which textually identical expressions should
get unified and why.

regards, tom lane