From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
cc: David Rowley <dgrowleyml@gmail.com>, Richard Guo <guofenglinux@gmail.com>,
        Ronan Dunklau <ronan.dunklau@aiven.io>,
        Justin Pryzby <pryzby@telsasoft.com>,
        Pavel Luzanov <p.luzanov@postgrespro.ru>,
        pgsql-hackers@lists.postgresql.org,
        Ranier Vilela <ranier.vf@gmail.com>
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
In-reply-to: <CAEZATCVG8SZhxr6JDnPH2ktgDznktcNitZBn9dB7mb20+f4GRw@mail.gmail.com>
References: <3163474.aeNJFYEL58@aivenronan> <CAApHDvowz-gfbuq=8iwHebRcy4VpFuJv52B3jCcXVi+CPFrd1A@mail.gmail.com> <9f61ddbf-2989-1536-b31e-6459370a6baa@postgrespro.ru> <2202180.iZASKD2KPV@aivenlaptop> <CAApHDvpkbvDEDJPNZrUqCsCLVC57ZY8qrPn8nGpQSrn4RYstZw@mail.gmail.com> <CAMbWs4_+5cP+8xjiAc=hds935ZiiG6oepPDeVNovWhQ66DARJw@mail.gmail.com> <CAApHDvqiEBY5_fVv4ZwUtWDPk0pJ6TdoYKFMwhhF_Gdkv-xAkg@mail.gmail.com> <CAApHDvr1Sm+g9hbv4REOVuvQKeDWXcKUAhmbK5K+dfun0s9CvA@mail.gmail.com> <CAApHDvptP-VK+ERLVs6UOAWaCQ7y4bf4_SSfRLCSyKWb-rW5zg@mail.gmail.com> <CAApHDvrsmvoJ1GF18TCxLL1wE=QrHKdam4dy73a5UF_QpNKqgQ@mail.gmail.com> <CAEZATCWETioXs5kY8vT6BVguY41_wD962VDk=u_Nvd7S1UXzuQ@mail.gmail.com> <CAMbWs49sa00xmyw9TfHLYDsHhsud_YEUm3YRkEE4tJrUDqLCgQ@mail.gmail.com> <CAApHDvruKdB5kaj6W9-kaM=zhMtgD2LvU5YDO3kFui6XSZC5Tg@mail.gmail.com> <416296.1673411524@sss.pgh.pa.us> <CAApHDvov5upudo+03svepBJZ2o7DGtU7X+biVX12ni6ojUDn9A@mail.gmail.com> <CAApHDvovqhBKjg6+e85Tda-3neZNnKe-w4rRiFOTbHjdWC99AQ@mail.gmail.c!
 om> <CAMbWs49xNn5uLmQGryTGF-LFPpFvouNSa1Y2BqgU_qq_Om=hSQ@mail.gmail.com> <2909364.1673939119@sss.pgh.pa.us> <CAMbWs49wdTUJ7g7nX-cKDe1EO5LFQPdqUjMkTcNMfBgv5p+jGg@mail.gmail.com> <CAApHDvqKNNhkhoueepYqgqi_a5OkZFC7jL74iFcBj+Jrh1=9+A@mail.gmail.com> <CAEZATCVG8SZhxr6JDnPH2ktgDznktcNitZBn9dB7mb20+f4GRw@mail.gmail.com>
Comments: In-reply-to Dean Rasheed <dean.a.rasheed@gmail.com>
	message dated "Wed, 18 Jan 2023 10:52:24 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3574543.1674056784.1@sss.pgh.pa.us>
Date: Wed, 18 Jan 2023 10:46:24 -0500
Message-ID: <3574544.1674056784@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3574544.1674056784%40sss.pgh.pa.us>
Precedence: bulk

Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I think the behaviour of an ORDER BY in the query can also be pretty
> surprising.

Indeed.  The fundamental question is this: in

> SELECT ARRAY[random(), random(), random()]
> FROM generate_series(1, 3)
> ORDER BY random();

are those four occurrences of random() supposed to refer to the
same value, or not?  This only matters for volatile functions
of course; with stable or immutable functions, textually-equal
subexpressions should have the same value in any given row.

It is very clear what we are supposed to do for

SELECT random() FROM ... ORDER BY 1;

which sadly isn't legal SQL anymore.  It gets fuzzy as soon
as we have

SELECT random() FROM ... ORDER BY random();

You could make an argument either way for those being the
same value or not, but historically we've concluded that
it's more useful to deem them the same value.  Then the
behavior you show is not such a surprising extension,
although it could be argued that such matches should only
extend to identical top-level targetlist entries.

> The trouble is, if we tried to fix that, we'd risk changing some other
> behaviour that users may have come to rely on.

Yeah.  I'm hesitant to try to adjust semantics here;
we're much more likely to get complaints than kudos.

			regards, tom lane