Re: Strange behavior of the random() function

> 26 сент. 2018 г., в 6:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> написал(а):
>
> 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.

Because random() is volatile function, but «unified» in such way can be only stable expressions. That the «volatile» function behaves like the «stable» function this is obviously is a bug.