Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Thu, 29 Feb 2024 21:14:10 -0500
Message-Id: <CZI1U3NKCZDT.31UY6DAFGVXC5@nmfay.com>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: `order by random()` makes select-list `random()` invocations
 deterministic
From: "Dian Fay" <di@nmfay.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
References: <CZHAF947QQQO.27MAUK2SVMBXW@nmfay.com>
 <CAKFQuwYo+EV4=o8Uc6wH=Ko+V8hgiqqOK=a9EC5G90HYx9+POw@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwYo+EV4=o8Uc6wH=Ko+V8hgiqqOK=a9EC5G90HYx9+POw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/CZI1U3NKCZDT.31UY6DAFGVXC5%40nmfay.com>
Precedence: bulk

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 =E2=80=9Cwon=E2=80=99t fix=E2=80=9D bugs th=
at 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%40mail.gmail.com
>
> The fact that the gs involving expression is seen differently than the on=
es
> not involving gs doesn=E2=80=99t 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?