Repository navigation
Description
Part of #274 and apache/datafusion-comet#7300.
What the function does
expr COLLATE name (SQL syntax) and collate(expr, collationName) (function form) mark a string expression with
a collation. The value is not changed: Collate.eval returns the child's value. Only the result type changes, from
StringType(childCollationId) to StringType(newCollationId). expr must be a STRING of any collation
(including trim collations). In the function form collationName must be a foldable, non-NULL STRING. It is parsed
as a multipart identifier, so system.builtin.UNICODE is accepted. Names are case-insensitive (uNiCoDE_rtRIm_cI
works). The syntax form takes an identifier, not a string literal. Collate is foldable when its child is, and
COLLATE gives an explicit collation strength in type coercion (see the collation-support issue).
collation(expr) returns the fully qualified name of expr's collation as a STRING:
SYSTEM.BUILTIN.<NORMALIZED_NAME>. It is RuntimeReplaceable and is replaced by a literal at analysis time, so
the value of expr is never evaluated and a NULL string value still returns the name. The input must be a STRING.
Both are in the string_funcs group, since 4.0.0. Spark 3.x has no collations.
Examples
SELECT collation('Spark SQL' COLLATE UTF8_LCASE);
-- SYSTEM.BUILTIN.UTF8_LCASE
SELECT collation('Spark SQL');
-- SYSTEM.BUILTIN.UTF8_BINARY
SELECT 'aaa' COLLATE unicode_ci;
-- aaa (result type STRING COLLATE UNICODE_CI)
SELECT collate('aaa', 'utf8_lcase_rtrim');
-- aaa (result type STRING COLLATE UTF8_LCASE_RTRIM)
SELECT collation('aaa' COLLATE unicode_ci_rtrim);
-- SYSTEM.BUILTIN.UNICODE_CI_RTRIM
SELECT collation('a' COLLATE UNICODE_CI_AI);
-- SYSTEM.BUILTIN.UNICODE_CI_AI
SELECT collation(cast('a' AS STRING COLLATE utf8_lcase));
-- SYSTEM.BUILTIN.UTF8_LCASE
SELECT 'a' COLLATE unicode_ci = 'A';
-- true
SELECT 'aaa' COLLATE UTF8_BS;
-- error: COLLATION_INVALID_NAME (collationName = UTF8_BS, proposals = UTF8_LCASE), sqlState 42704
SELECT collate('abc', 123);
-- error: UNEXPECTED_INPUT_TYPE (paramIndex first, requiredType "STRING"), sqlState 42K09
SELECT collate(1, 'UTF8_BINARY');
-- error: DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE
SELECT collate('abc', cast(null as string));
-- error: DATATYPE_MISMATCH.UNEXPECTED_NULL (exprName `collation`)
SELECT collate('aaa');
-- error: WRONG_NUM_ARGS.WITHOUT_SUGGESTION (expectedNum 2, actualNum 1)These come from the ExpressionDescriptions and CollationSuite.
Edge cases to test
- Pass-through: the output values are byte-identical to the input for every collation, including RTRIM collations
(trailing spaces are kept in the value; only comparisons ignore them). - Literal and column input, NULL input (stays NULL), empty string, multi-byte UTF-8.
- Every valid name form:
UTF8_BINARY,UTF8_LCASE,UNICODE,UNICODE_CI,UNICODE_AI,UNICODE_CI_AI,
_RTRIMvariants, ICU locales (en,en_CI,de,sv,tr_CI,en_USA_CI_AI_RTRIM), mixed case, and the
system.builtin.prefix.collation()must return the normalized upper-case name. - Non-foldable collation name in
collate(expr, col):NON_FOLDABLE_ARGUMENT. collation()of a column whose type is collated (table column,CAST(... AS STRING COLLATE ...), nested struct
field or array element such ascollation(c2[0])).- Re-collating:
('a' COLLATE UTF8_LCASE) COLLATE UNICODEtakes the outer collation. - Two different explicit collations meeting in one expression fail with
COLLATION_MISMATCH.EXPLICIT, for example
elt(1, utf8_binary COLLATE utf8_binary, utf8_lcase COLLATE utf8_lcase)(collations-basic.sql.out). - Trim collations need
spark.sql.collation.trim.enabledin 4.0 (default false outside tests). In 4.1 the config
defaults to true, and 4.2 removes it. - Deterministic.
Differences across Spark versions
- 3.4 / 3.5: not available.
- 4.0:
collate,collationand theCOLLATEsyntax added.collation()already returnsSYSTEM.BUILTIN.-qualified
names. Trim collations are behindspark.sql.collation.trim.enabled. - 4.1: trim collations enabled by default. 4.2: the trim config is gone; no other change to these two functions.
- ICU locale collations report
icu4jas their version (76.1 in 4.0.4, 77.1 in 4.1.3, 78.3 in 4.2.0). This does not
changecollation()output, but it does affect comparisons.
Implementation notes
- Spark:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/collationExpressions.scala
(CollateExpressionBuilder,Collate,UnresolvedCollation,ResolvedCollation,Collation). The parser rule
isprimaryExpression collateClauseinSqlBaseParser.g4(AstBuilder.visitCollate). Name parsing and
normalization are incommon/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationFactory.java
(collationNameToId,fullyQualifiedName). - DataFusion core has no collation concept. Arrow
DataType::Utf8has no collation field, anddatafusion/sqldoes
not handleCOLLATE. The natural carrier is Arrow field metadata (for example aspark.collationkey holding the
normalized name).ScalarUDFImpl::return_field_from_argscan read input field metadata and set it on the output. collatebecomes a pass-through UDF that validates the name and returns the input array unchanged with updated
output-field metadata.collationreads the argument field's metadata inreturn_field_from_args/
simplifyand returns a literal (defaultSYSTEM.BUILTIN.UTF8_BINARYwhen no metadata is present).- Name validation and normalization (case, specifier order,
system.builtin.prefix,COLLATION_INVALID_NAME)
should be a shared helper, since the collation-support issue needs the same table. - Without the collation-support work these two functions only carry the label. Comparisons and string functions
will ignore it until that lands. - Module:
crates/spark/src/function/string/. Tests: addcrates/sqllogictest/test_files/string/collate.sltand
collation.slt(function form only, unless the SQL parser is extended); document with#[user_doc].
This issue was generated by an LLM (Claude) from the Apache Spark source code and tests. Examples come from Spark's ExpressionDescription examples, test suites and .sql.out golden files, but please verify them against Spark before relying on them.
Activity