Postgres Pro
Downloads
Home   >   mailing lists

Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided - Mailing list pgsql-bugs

From Laurenz Albe
Subject Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided
Date
Msg-id 870b19cb98c433e7052fb7b2d2baf83fdd285c0c.camel@cybertec.at
Whole thread Raw
In response to Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided  (Walker Philips <walker.philips@oakworth.com>)
List pgsql-bugs
Tree view 
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

pgsql-bugs by date:

Previous
From: Joan
Date:
Subject: Re: pg_dump needs an option to add the force flag to the drop database
Next
From: David Rowley
Date:
Subject: Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided