From: Dian Fay <di@nmfay.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: `order by random()` makes select-list `random()` invocations deterministic
Date: Thu, 29 Feb 2024 21:14:10 -0500
Message-ID: <CZI1U3NKCZDT.31UY6DAFGVXC5@nmfay.com> (raw)
In-Reply-To: <CAKFQuwYo+EV4=o8Uc6wH=Ko+V8hgiqqOK=a9EC5G90HYx9+POw@mail.gmail.com>
References: <CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com>
<CAKFQuwYo+EV4=o8Uc6wH=Ko+V8hgiqqOK=a9EC5G90HYx9+POw@mail.gmail.com>
On Thu Feb 29, 2024 at 12:53 AM EST, David G. Johnston wrote:
> On Wednesday, February 28, 2024, Dian Fay <di@nmfay.com> wrote:
> >
> >
> > 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:
> >
> >
> This seems to be one of those “won’t fix” bugs that stems from the parser
> being a bit too liberal in what it accepts as valid SQL.
>
>
> https://www.postgresql.org/message-id/CAKFQuwZ3-XGfcS%2BCLTAYvPx3ARYjUxv%2B%3DYL8sOicV0nda%3DT5cA%40...
>
> The fact that the gs involving expression is seen differently than the ones
> not involving gs doesn’t surprise me.
>
> David J.
Thanks, that makes sense! Any thoughts on whether it's worth a
cautionary note in the `order by` and/or random function docs since
`order by random()` is a fairly well attested solution and I'm not the
first person to run into this quirk?
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, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: `order by random()` makes select-list `random()` invocations deterministic
In-Reply-To: <CZI1U3NKCZDT.31UY6DAFGVXC5@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