andygrove opened a new issue, #205:
URL: https://github.com/apache/datafusion-spark/issues/205
Part of #177 and apache/datafusion-comet#7300.
### What the function does
`substring_index(str, delim, count)` returns the part of `str` before
`count` occurrences of the delimiter `delim`. Available since Spark 1.5.0.
- If `count > 0`, it returns everything to the left of the `count`-th
delimiter, counting from the left.
- If `count < 0`, it returns everything to the right of the `|count|`-th
delimiter, counting from the right.
- If there are fewer than `|count|` delimiters, it returns `str` unchanged.
- If `count = 0` or `delim` is empty, it returns `''`.
The match is case-sensitive and byte-exact under `UTF8_BINARY`. The search
moves forward one byte at a time, so delimiter matches **may overlap**.
`str` and `delim` are STRING
(`StringTypeNonCSAICollation(supportsTrimCollation = true)`) and `count` is
INT, all via `ImplicitCastInputTypes`. The return type is `str`'s type. It is
null-intolerant: NULL in any argument returns NULL.
### Examples
```sql
SELECT substring_index('www.apache.org', '.', 2);
-- www.apache
```
From `UTF8StringSuite` / `StringExpressionsSuite`:
```text
substring_index('www.apache.org', '.', 3) -> www.apache.org
substring_index('www.apache.org', '.', 1) -> www
substring_index('www.apache.org', '.', 0) -> ''
substring_index('www.apache.org', '.', -1) -> org
substring_index('www.apache.org', '.', -2) -> apache.org
substring_index('www.apache.org', '.', -3) -> www.apache.org
substring_index('', '.', 1) -> ''
substring_index('', '.', -2) -> ''
substring_index('www.apache.org', '', 1) -> ''
substring_index('www.apache.org', '#', 2) -> www.apache.org
substring_index('www||apache||org', '||', 2) -> www||apache
substring_index('www||apache||org', '||', -2) -> apache||org
substring_index('大千世界大千世界', '千', 2) -> 大千世界大
substring_index('||||||', '|||', 3) -> || (overlapping
matches)
substring_index('||||||', '|||', -4) -> |||
substring_index(NULL, '.', -2) -> NULL
```
### Edge cases to test
- NULL `str`, `delim` or `count` returns NULL.
- `count = 0` returns `''`. An empty `delim` returns `''` for any `count`,
including a negative one.
- The delimiter is found exactly `|count|` times and ends at the start or
end of `str`. `substring_index('.a', '.', 1)` gives `''` and
`substring_index('a.', '.', -1)` gives `''` (Spark returns `EMPTY_UTF8` when
`idx == 0` or `idx + delim.numBytes == numBytes`).
- Fewer than `|count|` delimiters returns the whole `str`.
- **Overlapping delimiters**: Spark's `find`/`rfind` advance by 1 byte after
each match, so `'||||||'` with `'|||'` has 4 matches (at offsets 0, 1, 2, 3).
Non-overlapping search (Rust `match_indices`, `memmem` with `start += pos +
len`) finds only 2 and returns `'||||||'` for `count = 3`. Spark returns `'||'`.
- Multi-byte strings and delimiters. Matching is on bytes, and a valid UTF-8
delimiter can only match at character boundaries.
- `count` extremes: `Int.MinValue`, `Int.MaxValue`. `count` is INT, so a
BIGINT argument is implicitly cast. A string `count` that is not a number fails
with `CAST_INVALID_INPUT` under ANSI mode (the default since 4.0).
- Literal and column variants for each argument.
- Collations (4.0+):
- `UTF8_LCASE` matches the delimiter case-insensitively
(`lowercaseSubStringIndex`);
- ICU collations use `StringSearch`;
- `*_RTRIM` collations trim trailing spaces from `delim` first;
- `*_CS_AI` collations are rejected.
Supporting `UTF8_BINARY` first is acceptable.
### Differences across Spark versions
- 3.4 / 3.5: as above.
- 4.0: routes through `CollationSupport.SubstringIndex`; `UTF8_BINARY` is
unchanged.
- 4.1 / 4.2: none found.
### Implementation notes
- Spark source:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala`
(`SubstringIndex`). Kernel: `UTF8String.subStringIndex`, `find`, `rfind` in
`common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java`.
Collation variants: `CollationAwareUTF8String.subStringIndex` /
`lowercaseSubStringIndex`.
- DataFusion core has `substr_index` with alias `substring_index`
(`datafusion/functions/src/unicode/substrindex.rs`). It matches Spark for
`count = 0`, an empty delimiter, a missing delimiter and single-byte
delimiters. It differs in that:
- multi-byte delimiters are searched **without overlap** (`match_indices`
/ `rmatch_indices` / `memmem` with a full-length advance), so results differ
when the delimiter can overlap itself (`'||||||'`, `'|||'`);
- `count` is Int64, while Spark uses INT.
The Spark version needs a byte-wise overlapping search for multi-byte
delimiters. The single-byte `memchr` fast path can be kept.
- Module: `crates/spark/src/function/string/substring_index.rs`.
- Tests: `crates/sqllogictest/test_files/string/substring_index.slt` exists
as a placeholder. Add the overlap, boundary and Unicode cases above. 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]