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

   Part of #177 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `locate(substr, str[, pos])` returns the 1-based position of the first 
occurrence of `substr` in `str`, starting the search at character position 
`pos` (default 1). It returns 0 if there is no match. Available since Spark 
1.5.0.
   
   `position` is an alias registered for the same `StringLocate` expression 
(since 2.3.0), and it takes the same argument order: `position(substr, str[, 
pos])`. The parser also accepts the SQL-standard `POSITION(substr IN str)`, 
which `AstBuilder.visitPosition` turns into `StringLocate(substr, str, 1)`. The 
output column name is `locate(...)` or `position(...)` depending on how the 
function was called.
   
   `substr` and `str` are STRING 
(`StringTypeNonCSAICollation(supportsTrimCollation = true)`) and `pos` is INT, 
all via `ImplicitCastInputTypes`. The return type is INT, and positions are 
counted in characters (UTF-8 code points). NULL handling is unusual:
   
   - the result is nullable only through `substr` and `str`;
   - a NULL `pos` returns **0**, not NULL, even if the other arguments are NULL;
   - `pos < 1` returns 0.
   
   ### Examples
   
   ```sql
   SELECT locate('bar', 'foobarbar');
   -- 4
   SELECT locate('bar', 'foobarbar', 5);
   -- 7
   SELECT POSITION('bar' IN 'foobarbar');
   -- 4
   SELECT position('bar', 'foobarbar', 5);
   -- 7
   SELECT position('cd', 'abcdef');
   -- 3
   SELECT position('xy', 'abcdef');
   -- 0
   SELECT position('bar' in 'foobarbar'), position(null, 'foobarbar'), 
position('aaads', null);
   -- 4   NULL   NULL
   ```
   
   From `StringExpressionsSuite`:
   
   ```text
   locate('aa', 'aaads')              -> 1
   locate('aa', 'aaads', 0)           -> 0
   locate('aa', 'aaads', 1)           -> 1
   locate('aa', 'aaads', 2)           -> 2
   locate('aa', 'aaads', 3)           -> 0
   locate('de', 'aaads', 2)           -> 0
   locate('zz', 'aaads', NULL)        -> 0
   locate(NULL, NULL, NULL)           -> 0
   locate('aa', NULL)                 -> NULL
   locate(NULL, 'aaads')              -> NULL
   ```
   
   ### Edge cases to test
   
   - NULL `pos` returns 0 regardless of the other arguments. Otherwise a NULL 
`substr` or `str` returns NULL. The evaluation order is `pos`, then `substr`, 
then `str`.
   - `pos <= 0` returns 0 (only after the NULL checks on `substr` and `str`).
   - `pos` beyond the end of `str` returns 0 for a non-empty `substr`.
   - Empty `substr` returns 1 for any `pos >= 1`, even beyond the end of `str`, 
because `UTF8String.indexOfEmpty` always returns 0 (see the TODO for 
SPARK-48284). For example, `locate('', 'abc', 10)` returns 1 and `locate('', 
'')` returns 1. This was derived from the code.
   - Multi-byte strings: `pos` and the result count code points (`indexOf` 
walks UTF-8 lead bytes; the match is byte-exact). A surrogate pair counts as 
one character. `'数据砖头'` searched for `'头'` gives 4.
   - Overlapping candidates: `locate('aa', 'aaads', 2)` returns 2.
   - Implicit casts: a non-integer `pos` is cast to INT. A string `pos` that is 
not a number fails with `CAST_INVALID_INPUT` under ANSI mode (the default since 
4.0). Numeric `substr`/`str` are cast to STRING.
   - Literal and column variants for all three arguments, especially a column 
`pos` containing NULLs mixed with valid values.
   - Collations (4.0+):
     - `UTF8_LCASE` uses a lowercase search;
     - ICU collations use `StringSearch`;
     - `*_RTRIM` collations trim trailing spaces from `substr` first;
     - `*_CS_AI` collations are rejected.
   
     Mixing two explicit collations is an analysis error. Supporting 
`UTF8_BINARY` first is acceptable.
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: the same semantics, including `indexOf` returning 0 for an 
empty substring.
   - 4.0: routes through `CollationSupport.StringLocate`; `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`
 (`StringLocate`, with custom `eval`/`doGenCode` for the NULL-`pos` rule). 
Parser: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala`
 (`visitPosition`). Kernel: `UTF8String.indexOf(UTF8String, int)` / 
`indexOfEmpty` in 
`common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java`.
   - DataFusion core has `strpos(str, substr)` with aliases `instr` and 
`position` (`datafusion/functions/src/unicode/strpos.rs`). The planner maps 
`POSITION(x IN y)` to `strpos(y, x)`. Differences from Spark:
     - the argument order is reversed: core's `position(a, b)` searches for `b` 
in `a`, while Spark's `position(a, b)` searches for `a` in `b`;
     - there is no `pos` argument;
     - NULL inputs give NULL (Spark returns 0 for a NULL `pos`);
     - it returns Int64 for `LargeUtf8`.
   
     Core's byte search plus char counting (`find_substring_bytes`, 
`byte_offset_to_char_pos`) can be reused, but the function needs its own 
wrapper. Registering a Spark `position` UDF would replace core's `position` 
alias, so check that `POSITION(x IN y)` still plans correctly (the planner 
calls `strpos` directly).
   - Module: `crates/spark/src/function/string/locate.rs`, registering `locate` 
with alias `position`.
   - Tests: `crates/sqllogictest/test_files/string/locate.slt` and 
`position.slt` exist as placeholders. Add the NULL-`pos`, empty-substring and 
Unicode cases, plus `POSITION(... IN ...)`. 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