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

   Part of #174 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `regexp_substr(str, regexp)` returns the first substring of `str` that 
matches the Java regular expression `regexp`, or NULL if there is no match. It 
is `RuntimeReplaceable`: the analyzer rewrites it to 
`nullif(regexp_extract(str, regexp, 0), '')`, so an empty match also returns 
NULL. Available since 3.4.0.
   
   Both arguments are implicitly cast to STRING. Result type is the type of 
`str` (STRING; collation-aware in 4.0+). NULL if either argument is NULL. There 
are no position / occurrence / flags arguments (unlike some other engines' 
`regexp_substr`).
   
   ### Examples
   
   ```sql
   SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 
'Ste(v|ph)en');
   -- Steven
   SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 
'Jeck');
   -- NULL
   SELECT regexp_substr('1a 2b 14m', '\\d+');
   -- 1
   SELECT regexp_substr('1a 2b 14m', '\\d+(a|b|m)');
   -- 1a
   SELECT regexp_substr('1a 2b 14m', '\\d{2}(a|b|m)');
   -- 14m
   SELECT regexp_substr('1a 2b 14m', '');
   -- NULL
   SELECT regexp_substr('Spark', null);
   -- NULL
   ```
   
   ### Edge cases to test
   
   - NULL `str` or NULL `regexp`, literal and column: NULL.
   - Empty pattern returns NULL (the first match is the empty string at 
position 0, which `nullif` turns into NULL).
   - A pattern whose first match is empty returns NULL even if a later match is 
non-empty: `regexp_substr('abc', 'b*')` matches `''` at position 0, so the 
result is NULL.
   - No match returns NULL (not `''` as `regexp_extract` does).
   - Invalid pattern: `INVALID_PARAMETER_VALUE.PATTERN`. Because of the 
rewrite, the `functionName` in the error is `` `regexp_extract` ``.
   - Column patterns that differ per row.
   - `spark.sql.parser.escapedStringLiterals` (default false) only affects SQL 
literal parsing before the function. In `.slt`, DataFusion keeps backslashes 
literal (`'\d+'`).
   - Java vs Rust divergences to decline or test: lookaround / backreferences / 
atomic / possessive (Java only); ASCII `\d` `\w` `\s` in Java vs Unicode in 
Rust (`regexp_substr('\u0663 1', '\\d')` is `1` in Spark; U+0663 ARABIC-INDIC 
DIGIT THREE); `.` excludes `\r`, `\u0085`, `\u2028`, `\u2029` in Java 
(`regexp_substr('a\rb', 'a.b')` is NULL in Spark); `$` matches before a final 
line terminator in Java; `(?i)` ASCII-only in Java; inline flag letters `U` / 
`u` / `d`; Java-only `\Q..\E`, `\h`, `\R`, `\Z`, octal; Rust-only `(?P<n>)`, 
`\<`, `[[:alpha:]]`. Leftmost-first alternation is the same on both sides 
(`regexp_substr('ab', 'a|ab')` is `a`).
   - Unicode: matches containing multi-byte and supplementary characters are 
returned whole.
   - `UTF8_LCASE` collation (4.0+) matches case-insensitively.
   
   ### Differences across Spark versions
   
   - 4.0: collation-aware inputs; the `nullif` literal is created with `str`'s 
type. Otherwise none found.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/regexpExpressions.scala`,
 `RegExpSubStr` (replacement is `NullIf(RegExpExtract(left, right, 0), "")`).
   - Because of the rewrite, Spark plans never contain `RegExpSubStr`; an 
engine translating Spark plans sees `nullif(regexp_extract(...), '')`. 
`regexp_extract` is not in `datafusion-spark` either; implementing it with the 
same core (group-index checks as in `RegExpExtractBase.checkGroupIndex`) would 
serve both. A direct `regexp_substr` UDF is still useful for SQL users.
   - DataFusion core has no `regexp_substr`. `regexp_match` returns capture 
groups of the first match as a list and uses Rust syntax.
   - Use the shared Java-regex compatibility helper proposed for `rlike` 
(verified subset, decline otherwise, decided at planning time for literal 
patterns).
   - Belongs in `crates/spark/src/function/string/`. Add 
`crates/sqllogictest/test_files/string/regexp_substr.slt` and 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