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?
