[SPARK-24469] Support collations in Spark SQL

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Incomplete
• Affects Version/s: 2.3.0
• Component/s: SQL
• Labels: bulk-closed
• Assignee: Unassigned
• Reporter: Alexander Shkapsky
• Created: 05/Jun/18 17:02
• Updated: 08/Oct/19 05:44
• Resolved: 08/Oct/19 05:44

Description

One of our use cases is to support case-insensitive comparison in operations, including aggregation and text comparison filters.  Another use case is to sort via collator.  Support for collations throughout the query processor appear to be the proper way to support these needs.

Language-based worked arounds (for the aggregation case) are insufficient:

1. SELECT UPPER(text)....GROUP BY UPPER(text)
   introduces invalid values into the output set
2. SELECT MIN(text)...GROUP BY UPPER(text) 
   results in poor performance in our case, in part due to use of sort-based aggregate

Examples of collation support in RDBMS:

• PostgreSQL
• MySQL
• Oracle
• SQL Server
• DB2