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

   Part of #177 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   These functions remove characters from the ends of a string:
   
   - `trim(str)` / `ltrim(str)` / `rtrim(str)` remove leading and trailing, 
leading, or trailing ASCII spaces (U+0020
     only, not tabs or newlines). Available since 1.5.0.
   - `trim(trimStr, str)` / `ltrim(trimStr, str)` / `rtrim(trimStr, str)` 
remove any code point that appears in
     `trimStr` and stop at the first code point that does not. `trimStr` is a 
set of characters, not a substring. In the
     SQL function-call form the trim string comes first. The secondary 
constructor `this(trimStr, srcStr)` swaps the
     arguments, so the expression's children are `(srcStr, trimStr)`.
   - SQL syntax: `TRIM([BOTH | LEADING | TRAILING] [trimStr] FROM str)`. With 
no keyword, `BOTH` is used. The parser maps
     `BOTH` to `StringTrim`, `LEADING` to `StringTrimLeft` and `TRAILING` to 
`StringTrimRight`.
   - `btrim(str [, trimStr])` (since 3.2.0) puts the source string first. It is 
a `RuntimeReplaceable` that rewrites to
     `StringTrim(str, trimStr)`.
   
   All arguments are `STRING` (collated strings from 4.0 on). Through 
`ImplicitCastInputTypes`, the analyzer casts
   `BINARY` and other atomic types to `STRING`, so `btrim(encode(...), 
encode(...))` returns `STRING`. The return type is
   the source string's type. The result is NULL if either argument is NULL. The 
functions are deterministic.
   
   ### Examples
   
   ```sql
   SELECT trim('    SparkSQL   '), ltrim('    SparkSQL   '), rtrim('    
SparkSQL   ');
   -- SparkSQL | SparkSQL    |     SparkSQL
   SELECT trim(BOTH FROM '    SparkSQL   ');
   -- SparkSQL
   SELECT trim('SL' FROM 'SSparkSQLS');
   -- parkSQ
   SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
   -- parkSQLS
   SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
   -- SSparkSQ
   SELECT trim(BOTH 'xyz' FROM 'yxTomxx');
   -- Tom
   SELECT trim(LEADING 'xy' FROM 'xyxXxyLAST WORD');
   -- XxyLAST WORD
   SELECT trim(TRAILING 'xy' FROM 'TURNERyxXxy');
   -- TURNERyxX
   SELECT btrim('xyxtrimyyx', 'xy');
   -- trim
   SELECT btrim('SSparkSQLS', 'SL');
   -- parkSQ
   SELECT btrim(encode('yxTomxx', 'utf-8'), encode('xyz', 'utf-8'));   -- 
BINARY in, STRING out
   -- Tom
   SELECT btrim(binary(''), binary('\\000'));
   -- (empty string)
   SELECT btrim(binary('\\000trim\\000'), binary(''));                  -- 
empty trim set: unchanged
   -- \000trim\000
   -- function-call form, trim string first (JDBCV2Suite, name = 'jen'):
   --   trim('j', name) = 'en', ltrim('j', name) = 'en', rtrim('n', name) = 'je'
   ```
   
   (The outputs come from `ExpressionDescription`, `string-functions.sql.out`, 
`postgreSQL/strings.sql.out` and
   `JDBCV2Suite`.)
   
   ### Edge cases to test
   
   Unless noted otherwise, the examples below use Catalyst child order `(str, 
trimStr)`, which is `btrim` order.
   
   - NULL `str` gives NULL. NULL `trimStr` gives NULL even when `str` is not 
NULL (`btrim('a', NULL)` is NULL).
   - Empty `str` returns `''`. Empty `trimStr` returns `str` unchanged. A 
string made only of trim characters returns
     `''` (`btrim('aa', 'a')`, `StringTrimRight(' aaabdefaaaa', 'abf de')` -> 
`''`).
   - The one-argument form trims only U+0020. Tabs, `\n`, NBSP (U+00A0) and 
other Unicode whitespace are kept.
   - Set semantics: order and duplicates in `trimStr` don't matter, e.g. 
`btrim(' aabbtrimccc', 'ab cd')` -> `trim` and
     `btrim('a<a >@>.,>', 'a.,@<>')` -> `' '`.
   - Argument order: function-call `trim`/`ltrim`/`rtrim` take `(trimStr, 
str)`, while `btrim` takes `(str, trimStr)`.
     `TRIM(a, b)` is printed as `TRIM(BOTH a FROM b)`.
   - Matching is on code points, not graphemes, and is byte-exact for 
`UTF8_BINARY` (all from `CollationSupportSuite`):
     - `btrim('i' + U+0307, 'i')` -> U+0307 alone.
     - `btrim('i' + U+0307, 'Δ°')` is unchanged.
     - `btrim('Ο‚xΟ‚', 'Οƒ')` is unchanged.
     - `btrim('𝔸', 'A')` is unchanged.
     - Surrogate-pair characters match as whole code points: `btrim('πŸ˜€πŸ˜†πŸ˜ƒπŸ˜„', 
'πŸ˜€πŸ˜„')` -> `πŸ˜†πŸ˜ƒ` and
       `btrim('aπŸ™ƒbπŸ™ƒc', 'ac')` -> `πŸ™ƒbπŸ™ƒ`.
   - Multi-byte characters: `btrim('θŠ±θŠ±δΈ–η•ŒθŠ±θŠ±', 'θŠ±δΈ–η•Œ')` -> `''` and 
`StringTrimLeft('θŠ±θŠ±δΈ–η•ŒθŠ±θŠ±', '花 δΈ–')` -> `η•ŒθŠ±θŠ±`
     (from `StringExpressionsSuite`).
   - Case-sensitive for `UTF8_BINARY`: `btrim('ASD', 'a')` -> `ASD`.
   - BINARY input: Spark casts binary to string without validating UTF-8, so 
invalid byte sequences can reach the
     kernel. If the Arrow side casts `Binary` to `Utf8` first, that cast fails 
on invalid UTF-8. Decide whether to accept
     `Binary` directly and trim byte-wise per UTF-8 character width, as 
`UTF8String.trimLeft` / `trimRight` do.
   - Test the scalar and column forms of both `str` and `trimStr` (DataFusion 
pre-computes the char set for a scalar
     pattern). Also test `Utf8`, `LargeUtf8` and `Utf8View`.
   - Collations (4.0+): `UTF8_LCASE` and `UNICODE_CI` compare 
case-insensitively:
     - `btrim('asd' COLLATE UTF8_LCASE, 'A')` -> `sd`
     - `btrim('ddsXXXaa' COLLATE UTF8_LCASE, 'ASD')` -> `XXX`
     - `btrim('ẞaaaẞ' COLLATE UTF8_LCASE, 'ß')` -> `aaa`
   
     Case-sensitive, accent-insensitive collations are rejected with 
`DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`, for
     example `TRIM(x COLLATE UNICODE_AI, y COLLATE UNICODE_AI)`. `*_RTRIM` 
collations have their own space handling
     (`CollationAwareUTF8String.binaryTrim*`). A first version can support only 
`UTF8_BINARY`.
   - `spark.sql.ansi.enabled` has no effect. An unsupported trim option is a 
parser error and never reaches the kernel.
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: `StringType` only. Evaluation calls `UTF8String.trim` / 
`trimLeft` / `trimRight`, which have the same
     semantics as 4.x `UTF8_BINARY`.
   - 4.0: dispatches through `CollationSupport.StringTrim` / `StringTrimLeft` / 
`StringTrimRight`. Adds collation
     support (`StringTypeNonCSAICollation(supportsTrimCollation = true)`), and 
the result keeps the source collation.
     `UTF8_BINARY` results are unchanged.
   - 4.1 / 4.2: no behavior change found.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala`
     (`String2TrimExpression`, `StringTrim`, `StringTrimLeft`, 
`StringTrimRight`, `StringTrimBoth`), the parser rule
     `#trim` in 
`sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4` 
and `AstBuilder.visitTrim`,
     
`common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java` 
(`trim`, `trimLeft`, `trimRight`),
     and 
`common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationSupport.java`
 (`StringTrim*`).
   - DataFusion core has `btrim` (alias `trim`), `ltrim` and `rtrim` in 
`datafusion/functions/src/string/`, with the
     shared kernel in `string/common.rs`. They take `(str [, chars])` and use 
`trim_start_matches` /
     `trim_end_matches` on a `&[char]` set, defaulting to `' '`. For valid 
UTF-8 and `UTF8_BINARY`, that is the same
     set-of-code-points behavior as Spark. The differences are:
     - Argument order of the two-argument function-call `trim`/`ltrim`/`rtrim`. 
Spark takes the trim string first, while
       DataFusion takes it second (`btrim` agrees with DataFusion).
     - DataFusion's signatures accept strings only 
(`Coercion::new_exact(logical_string())`), so `BINARY` input is not
       accepted.
     - No collation support.
   - DataFusion's SQL planner (`datafusion/sql/src/expr/mod.rs`, 
`sql_trim_to_expr`) lowers
     `TRIM(LEADING x FROM y)` to `ltrim(y, x)`, and likewise for `rtrim`, 
`btrim` and `trim`, so the source string comes
     first. Comet also serializes Spark's children as `(srcStr, trimStr)`. 
Keeping the kernels in `(srcStr, trimStr)`
     order matches both. Decide (and document) whether the Spark-named 
`trim`/`ltrim`/`rtrim` UDFs also swap for the
     SQL function-call form. Swapping would make the `TRIM(... FROM ...)` 
syntax produce reversed arguments.
   - Reuse the DataFusion kernels where possible. The Spark-specific work is 
`Binary` input, possibly invalid UTF-8, and
     collations.
   - Module: `crates/spark/src/function/string/` (`trim.rs` with the `trim`, 
`ltrim`, `rtrim` and `btrim` UDFs).
   - Tests: add `crates/sqllogictest/test_files/string/trim.slt` (and 
`ltrim`/`rtrim`/`btrim` variants or sections),
     following `elt.slt`. Include both the `TRIM(... FROM ...)` syntax and the 
function-call forms. Document each
     function 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