Skip to content

COLLATE order not working in window functions #8937

Closed
@tcboles

Description

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

Activity

self-assigned this
on Sep 15, 2023
hawkfish

hawkfish commented on Sep 15, 2023

@hawkfish
Contributor

The planner is not noticing that the sorts are different:

D explain 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
> )
> ;

┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│           WINDOW          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│    array_agg(col) OVER    │
│(PARTITION BY id ORDER...  │
│         NULLS LAST)       │
│    array_agg(col) OVER    │
│(PARTITION BY id ORDER...  │
│         NULLS LAST)       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│            col            │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           UNNEST          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         DUMMY_SCAN        │
└───────────────────────────┘                             
added a commit that references this issue on Oct 26, 2023

Issue duckdb#8937: Window Order Collation

added a commit that references this issue on Oct 26, 2023

Issue duckdb#8937: Window Order Collation

added a commit that references this issue on Oct 26, 2023

Merge pull request #9477 from hawkfish/window-collate

b83f3a0
added 2 commits that reference this issue on Dec 2, 2023

Issue duckdb#8937: Window Order Collation

Issue duckdb#8937: Window Order Collation

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions

    COLLATE order not working in window functions · Issue #8937 · duckdb/duckdb