Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 28 Feb 2024 23:45:17 -0500
Message-Id: <CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com>
To: <pgsql-bugs@lists.postgresql.org>
Subject: `order by random()` makes select-list `random()` invocations
 deterministic
From: "Dian Fay" <di@nmfay.com>
Archived-At: 
 <https://www.postgresql.org/message-id/CZHAF947QQQO.27MAUK2SVMBXW%40nmfay.com>
Precedence: bulk

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.