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]