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();
What gives? Does using RANDOM() as an ORDER BY somehow turn it into per-row stable rather than volatile?