Curious behaviour with "order by random()"

From: Erwin Sebastian Andreasen <erwin(at)andreasen(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Curious behaviour with "order by random()"
Date: 2020-06-24 20:33:08
Message-ID: CAFz3e-7QzgVAmi+6zOuBfETQQNpLfYfzi+HJWb9piUBmbHMq0Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2020-06-24 20:35:19 Re: n_distinct off by a factor of 1000
Previous Message Jim Hurne 2020-06-24 19:44:26 Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked