`COLLATION` Support

[alamb]

Is your feature request related to a problem or challenge?

"Collation" generically means how to compare and sort string values.

Soem databases, most notably Postgres, allow you to change the default collation order to control this more carefully to match whatever the user wants rather than what the standard sort order means

Someone asked about this on discord: https://discord.com/channels/885562378132000778/1166447479609376850/1205554368292855868

Here are some details on how this works in Postgres: https://www.postgresql.org/docs/current/collation.html

Describe the solution you'd like

Someone to design and implement COLLATION

This probably looks like a SessionConfig setting to control collation at the session level and possibly some way to define it as part of the table definition

Describe alternatives you've considered

No response

Additional context

This would likely require adding collation support to arrow-rs as well, though I am not 100% sure

[alamb]

cc @gruuya as I believe you mentioned you might also be interested in this feature

[tustvold]

I think a first step would be to identify a mature Rust library for supporting collations, as I suspect this is not something we wish to implement ourselves, much like we use chrono for temporal functionality.

I also wonder if there might be a middle ground where we provide specialised UDFs or similar for manipulating collations, as full native support would be a very substantial undertaking. This would also provide a good story for making this functionality optional

[alamb]

I believe collation is an important feature for postgres compatibility, but it is not as widely used in other databases.

I agree having something that was optional would be ideal

[gruuya]

Yeah, for reference DuckDB also provides optional general collations, using an extension for the ICU project: https://duckdb.org/docs/sql/expressions/collations.html#icu-collations

There's a corresponding Rust crate as well: https://docs.rs/icu/latest/icu/collator/index.html

[andygrove]

A major feature of Spark 4.0.0 is support for string collation

[mbutrovich]

There's a corresponding Rust crate as well: https://docs.rs/icu/latest/icu/collator/index.html

There's also Google's rust_icu.

[alamb]

I think it would be great to sketch out what supporting collation would look like.

As I recall, it is fairly invasive, as it potentially changes all string comparisons (both for ORDER BY as well as for =, <, etc type functions).

The first major question, is where the support would live (upstream in arrow-rs or in DataFusion) 🤔

[mjgarton]

It seems collation support is being discussed for the arrow c++ implementation. I don't know if this implies arrow-rs is a more suitable place than datafusion?

apache/arrow#27875

Datafusion would need some degree of support in any case I suppose, even if it delegates the actual ordering decisions to arrow-rs.

[alamb]

I started a discussion on an API to support customizing behavior like this on

• API to register behavior for Extension Types #18223

I think COLLATION would be a natural additional property for types (as it controlls comparisons and sorting)

[mjgarton]

I notice that support for extension types has been merged. I can't quite see how to get from that to collation support. Is more work needed in extension types first?