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]