andygrove opened a new issue, #298:
URL: https://github.com/apache/datafusion-spark/issues/298

   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
   
   ```sql
   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 `ExpressionDescription`s 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._


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to