Curious behaviour with "order by random()"

Поиск
Список
Период
Сортировка
От Erwin Sebastian Andreasen
Тема Curious behaviour with "order by random()"
Дата
Msg-id CAFz3e-7QzgVAmi+6zOuBfETQQNpLfYfzi+HJWb9piUBmbHMq0Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Curious behaviour with "order by random()"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения
I wanted to generate some test data based on a subset of rows in a table, thus used ORDER BY RANDOM(). I was surprised to see that if RANDOM() is used in ORDER BY it cannot also be used in column names: the same value is returned.

Compare the output of:

select random(), random();

which will return 2 separate random values with:

select random(), random() order by random();

which returns two of the same values (and the same value is also used in order by). While I use 9.6, I got the same results on db fiddle with 13.0: https://www.db-fiddle.com/f/hNofvnT44izEUmyPyEoWh4/0

What gives? Does using RANDOM() as an ORDER BY somehow turn it into per-row stable rather than volatile?

В списке pgsql-general по дате отправления:

Предыдущее
От: "Jim Hurne"
Дата:
Сообщение: Re: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked
Следующее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: n_distinct off by a factor of 1000