From: Dian Fay <di@nmfay.com>
To: pgsql-bugs@lists.postgresql.org
Subject: `order by random()` makes select-list `random()` invocations deterministic
Date: Wed, 28 Feb 2024 23:45:17 -0500
Message-ID: <CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com> (raw)
In Postgres 16.1, running the following query:
```
select
gs,
gs + random() * 100 - 50 as gs2,
random() * 100 - 50 as r1,
random() * 100 - 50 as r2,
random() * 100 - 50 as r3
from generate_series(0, 10) as gs
order by random();
```
Every `random()` invocation in the select list uses a single consistent
value within each returned row. Remove the `order by random()` and
values become randomized as expected, but it gets a bit stranger:
- order by any of the `rN` values and `gs2` uses a different random
value, but all the `rN` values are identical;
- order by `gs2` and all random values are distinct again.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: di@nmfay.com, pgsql-bugs@lists.postgresql.org
Subject: Re: `order by random()` makes select-list `random()` invocations deterministic
In-Reply-To: <CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox