From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Walker Philips <walker.philips@oakworth.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided
Date: Tue, 18 Jul 2023 12:10:49 +0200
Message-ID: <870b19cb98c433e7052fb7b2d2baf83fdd285c0c.camel@cybertec.at> (raw)
In-Reply-To: <DS0PR22MB4121435FE9369AD4AAA6319EF93BA@DS0PR22MB4121.namprd22.prod.outlook.com>
References: <7eb5a6a9-fd0f-463e-acca-b7622b6820b0.3d353b87-a551-460b-bc24-1d08800ce948.daff3c6d-a26a-4345-b8c7-6e15d34ecd9c@emailsignatures365.codetwo.com>
<7eb5a6a9-fd0f-463e-acca-b7622b6820b0.3bd2cb9b-57e9-4cfe-882f-740b5c103819.14d1b346-d657-42e6-b43b-1b0454fa0c4a@emailsignatures365.codetwo.com>
<DS0PR22MB4121435FE9369AD4AAA6319EF93BA@DS0PR22MB4121.namprd22.prod.outlook.com>
On Mon, 2023-07-17 at 21:41 +0000, Walker Philips wrote:
> Applying an order by clause changes the contents of an array_agg (and string_agg) when performing a window function.
>
> select right(a.telephone1,3),a.accountid,array_agg(accountid)over (partition by lower(a.telephone1)order by a.accountid desc)as hashed_household
> from air_spotter.account a;
>
> generates the results:
> 889 1C14927F-C28A-E311-A35E-6C3BE5A84FE4 {1C14927F-C28A-E311-A35E-6C3BE5A84FE4}
> 889 8552B5D9-95ED-E311-96E3-6C3BE5A86DF8 {1C14927F-C28A-E311-A35E-6C3BE5A84FE4,8552B5D9-95ED-E311-96E3-6C3BE5A86DF8}
> [NULL] B25520B1-A08B-E011-8DC7-1CC1DEE8AA5F {B25520B1-A08B-E011-8DC7-1CC1DEE8AA5F}
>
> Expected results would be the same total content, but the content being sorted as specified by the order by. Specifically
> {8552B5D9-95ED-E311-96E3-6C3BE5A86DF8,1C14927F-C28A-E311-A35E-6C3BE5A84FE4} for both telephones ending in 889.
I don't get it: '8552B5D9-95ED-E311-96E3-6C3BE5A86DF8' is greater than '1C14927F-C28A-E311-A35E-6C3BE5A84FE4'.
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, walker.philips@oakworth.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided
In-Reply-To: <870b19cb98c433e7052fb7b2d2baf83fdd285c0c.camel@cybertec.at>
* 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