What happens?

When applying a COLLATE to a window function the order uses the default behavior.

To Reproduce

This shows that the order in the array agg does not change when using a window funciton

select
    *,
    array_agg(col) over(partition by id order by col) as lead_col,
    array_agg(col) over(partition by id order by col collate nocase) as lead_col_nocase
from (select unnest(array[1, 1, 1, 1]) as id, unnest(array['A', 'a', 'b', 'B']) as col)

This shows that the collate does change the order outside of the window function

select
    *
from (select unnest(array[1, 1, 1, 1]) as id, unnest(array['A', 'a', 'b', 'B']) as col) order by col collate nocase

OS:

macos

DuckDB Version:

0.8.1

DuckDB Client:

Datagrip JBDC

Full Name:

Thomas Boles

Affiliation:

SaaSWorks Inc.

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have