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

On Tue, 18 Jul 2023 at 17:38, Walker Philips
<walker(dot)philips(at)oakworth(dot)com> wrote:
> 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.

This is intended and how the SQL standard defines how it's meant to
work. The default frame options for which rows are in frame is RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. ORDER BY, when used in
the window clause just defines which rows are peers of each other. If
you don't have an ORDER BY then all rows in the partition are peers of
each other, that's why you see all rows in the partition aggregated in
the latter of your two queries above. In the first of your queries,
since you've not adjusted the frame options only rows from the start
of the frame (UNBOUNDED PRECEDING) to the current row (CURRENT ROW)
(and the current row's peers, i.e rows with the same value according
to the ORDER BY clause) are in the frame. That's why you see new
values being aggregated as the window advances.

If you want all rows in the partition to be in the frame at once then
you can either leave off the ORDER BY as you've done in the first of
your queries or you can change the frame visibility options to ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. You can also have
an ORDER BY to control the order of aggregation. I think the
following likely will give you what you want:

select right(a.telephone1,3), a.accountid, array_agg(accountid) over
(partition by lower(a.telephone1) order by a.accountid desc ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as
hashed_household from air_spotter.account a;

David