Implement Spark-compatible `collate` / `collation`

Start by reading Spark's collationExpressions.scala (Collate, Collation, CollationFactory) for the name-normalization table, then look at an existing function in crates/spark/src/function/string/ to copy the ScalarUDFImpl + #[user_doc] layout. The open design question is the carrier for the collation label: return_field_from_args reading/writing Arrow field metadata (e.g. a spark.collation key) is the suggested path. Done means a pass-through collate UDF that validates names, a collation UDF returning a literal, and crates/sqllogictest/test_files/string/collate.slt plus collation.slt covering the listed edge cases.

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,
  _RTRIM variants, 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 as collation(c2[0])).
• Re-collating: ('a' COLLATE UTF8_LCASE) COLLATE UNICODE takes 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.enabled in 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, collation and the COLLATE syntax added. collation() already returns SYSTEM.BUILTIN.-qualified
  names. Trim collations are behind spark.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 icu4j as their version (76.1 in 4.0.4, 77.1 in 4.1.3, 78.3 in 4.2.0). This does not
  change collation() 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
  is primaryExpression collateClause in SqlBaseParser.g4 (AstBuilder.visitCollate). Name parsing and
  normalization are in common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationFactory.java
  (collationNameToId, fullyQualifiedName).
• DataFusion core has no collation concept. Arrow DataType::Utf8 has no collation field, and datafusion/sql does
  not handle COLLATE. The natural carrier is Arrow field metadata (for example a spark.collation key holding the
  normalized name). ScalarUDFImpl::return_field_from_args can read input field metadata and set it on the output.
• collate becomes a pass-through UDF that validates the name and returns the input array unchanged with updated
  output-field metadata. collation reads the argument field's metadata in return_field_from_args /
  simplify and returns a literal (default SYSTEM.BUILTIN.UTF8_BINARY when 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: add crates/sqllogictest/test_files/string/collate.slt and
  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.