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]

Reply via email to