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]