`order by random()` makes select-list `random()` invocations deterministic

From: "Dian Fay" <di(at)nmfay(dot)com>
To: <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: `order by random()` makes select-list `random()` invocations deterministic
Date: 2024-02-29 04:45:17
Message-ID: CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Dunstan 2024-02-29 04:47:42 Re: Feature bug dumpall CREATE ROLE postgres
Previous Message Andrei Lepikhov 2024-02-29 03:36:55 Re: BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker