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

Поиск
Список
Период
Сортировка
От Walker Philips
Тема Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided
Дата
Msg-id DS0PR22MB4121435FE9369AD4AAA6319EF93BA@DS0PR22MB4121.namprd22.prod.outlook.com
обсуждение исходный текст
Ответы Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided  (Laurenz Albe <laurenz.albe@cybertec.at>)
Re: Bug with aggregate Window Functions when using Order By. Elements dropped if order by provided  (David Rowley <dgrowleyml@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Postgres 14.8
Ubuntu 22.04 Jellyfish

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}

select  right(a.telephone1,3), a.accountid, array_agg(accountid) over (partition by lower(a.telephone1)) as hashed_household

from air_spotter.account a;



889     1C14927F-C28A-E311-A35E-6C3BE5A84FE4      {1C14927F-C28A-E311-A35E-6C3BE5A84FE4,8552B5D9-95ED-E311-96E3-6C3BE5A86DF8}
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.


Logo-1.png
Walker Philips
Director of Data Engineering and Analytics
T:  205.443.4617   C:  205.585.7668  
Commercial and Private Banking | Wealth Management | Advisory Services
Timeless Values for a Modern World
www.oakworth.com


Oakworth Capital Bank will never ask for personal or account information by e-mail. To protect yourself from fraud, never divulge sensitive information, such as passwords, account numbers, credit card numbers or your PIN in response to an e-mail. When in doubt, feel free to contact us at 205.263.4700.

This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication represents the originator's personal views and opinions, which do not necessarily reflect those of Oakworth Capital Bank. If you are not the original recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error, and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you received this email in error, please immediately notify postmaster@oakworth.com.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #18026: compile error / fe-auth.c:807:64: error: 'CHAR_BIT' undeclared (first use in this function)
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #18027: Logical replication taking forever