TL;DR: If I order a window-function partition, then the length of the resulting array changes.

Here’s a minimal example. We first generate some toy data:

select *
from (
  values
    (date('1900-01-01'),1,'a')
   ,(date('1900-01-02'),2,'a')
   ,(date('1900-01-03'),3,'a')
     ) a(date,id,customer)

The resulting data looks like this:

Now the window functions:

with data as (

select *
from (
  values
    (date('1900-01-01'),1,'a')
   ,(date('1900-01-02'),2,'a')
   ,(date('1900-01-03'),3,'a')
     ) a(date,id,customer)

select
  date
 ,id
 ,customer
 ,array_agg(id) over (partition by customer) as ids
 ,array_agg(id) over (partition by customer order by date) as ids_ordered
from
  data

Why does order by date affect the length of the arrays? Is this a bug or expected behaviour?

My deployment: (a slightly modified version of) version 0.8.0 of the Trino chart.