Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided

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