Regex compile failures from user patterns surface as four different error shapes, mostly as internal-looking ComputeError; validate literal patterns at planning time and report them consistently

[adriangb]

Is your feature request related to a problem or challenge?

A regular expression from user SQL that does not compile produces a different error for each regex function. Some of these errors look like engine faults (Arrow error: Compute error: ...), not like errors in the query. For regexp_count and regexp_instr, the error also drops the diagnosis from the regex crate. Only regexp_like and the ~ operators check a literal pattern before execution.

All output below is from datafusion-cli 55.1.0, built from main at 8bd6629.

CREATE TABLE t (s VARCHAR, p VARCHAR);
INSERT INTO t VALUES ('abc', 'a(b');

1. regexp_like and ~ / ~*: optimizer error at planning (literal pattern), ComputeError at execution (column pattern)

SELECT regexp_like(s, 'a(b') FROM t;   -- same for s ~ 'a(b', s ~* 'a(b', regexp_like(s, 'a(b', 'i')

Optimizer rule 'simplify_expressions' failed
caused by
Invalid regex
caused by
External error: regex parse error:
    a(b
     ^
error: unclosed group

This is DataFusionError::Context("Invalid regex", External(Box<regex_syntax::Error>)) from simplify_regex_expr (source).

SELECT regexp_like(s, p) FROM t;       -- same for s ~ p

Arrow error: Compute error: Regular expression did not compile: Syntax(
regex parse error:
    a(b
     ^
error: unclosed group
)

This is ArrowError::ComputeError from the arrow-rs regexp_is_match / regexp_is_match_scalar kernels (arrow-string 59.3.0, src/regexp.rs), called from regexplike.rs (source). If all arguments are literals and the call is not rewritten to ~, constant folding goes through regexp_like_scalar, which returns a third shape, DataFusionError::Execution (source):

SELECT regexp_like('abc', 'a(b', 'z');

Execution error: Regular expression did not compile: Syntax(
regex parse error:
    (?z)a(b
      ^
error: unrecognized flag
)

2. regexp_match: ComputeError at execution

SELECT regexp_match(s, 'a(b') FROM t;

Arrow error: Compute error: Regular expression did not compile: Syntax(
regex parse error:
    a(b
     ^
error: unclosed group
)

The error comes from the arrow-rs regexp_match kernel (call site). EXPLAIN of this query succeeds, so the literal pattern is not checked at planning.

3. regexp_count / regexp_instr: ComputeError at execution, diagnosis lost

SELECT regexp_count(s, 'a(b') FROM t;  -- same for regexp_instr

Arrow error: Compute error: Regular expression did not compile: a(b

compile_regex maps every regex::Error to a ComputeError that contains only the pattern (source). An invalid flag has the same problem: regexp_count(s, 'a', 1, 'z') returns Regular expression did not compile: (?z)a, with no indication that the flag is the cause.

4. regexp_replace: External(regex::Error) at execution

SELECT regexp_replace(s, 'a(b', 'x') FROM t;

External error: regex parse error:
    a(b
     ^
error: unclosed group

(source)

5. The unsupported g flag is also reported in three different ways

SELECT regexp_like(s, 'a', 'g') FROM t;        -- Error during planning: regexp_like() does not support the "global" option
SELECT regexp_match(s, 'a', 'g') FROM t;       -- Error during planning: regexp_match() does not support the "global" option
SELECT regexp_match(s, 'a', 'gi') FROM t;      -- Arrow error: Compute error: Regular expression did not compile: Syntax( ... (?gi)a ... error: unrecognized flag )
SELECT regexp_count(s, 'a', 1, 'g') FROM t;    -- Arrow error: Compute error: regexp_count()/regexp_instr() does not support the global flag

regexp_like rejects any flags value that contains g. regexp_match rejects only the exact value g (source), so gi reaches the kernel. The "planning" errors for g are raised at execution when the flags come from a column.

Comparison with PostgreSQL

PostgreSQL 16.15 returns one user error for every function in 1–4:

ERROR:  invalid regular expression: parentheses () not balanced

It returns ERROR: invalid regular expression option: "z" for an invalid flag, and <function>() does not support the "global" option for g in regexp_like, regexp_match and regexp_count. All of these have SQLSTATE class 22 (data exception), for example 2201B for the invalid pattern.

Why this matters

• A caller cannot tell an invalid pattern in the query from an engine fault by the error type. Four of the shapes above are ArrowError::ComputeError or External, which are also used for internal failures.
• For regexp_count and regexp_instr, the user does not see why the pattern is invalid.
• A literal pattern is checked at planning only for regexp_like and the ~ operators. The other functions accept the query at planning and fail at execution, after work has started.

Describe the solution you'd like

1. One consistent error for a pattern that does not compile. The regex UDFs own the pattern and the flags, so they should compile the pattern (or map the kernel error) and return DataFusionError::Execution, or DataFusionError::Plan when the pattern is a literal checked at planning. The message should contain the diagnosis from the regex crate, for example regexp_count(): invalid regular expression 'a(b': unclosed group. The error should never be a stringified ArrowError::ComputeError. compile_regex should keep the regex::Error instead of discarding it.
2. Planning-time validation of literal patterns in every regex UDF, not only ~ and regexp_like. A shared helper can compile a literal (pattern, flags) pair the same way the function assembles it at execution ((?{flags}){pattern}, empty flags treated as no flags, g stripped for regexp_replace and rejected for the other functions) and return a plan error with the diagnosis. The g check should be the same in every function (contains('g')).
3. Make the simplify_regex_expr failure a Plan error with the diagnosis, instead of Context("Invalid regex", External(..)).

The existing tests that pin Arrow error: Compute error: Regular expression did not compile: CompiledTooBig(10485760) in regexp_like.slt and regexp_match.slt would change with point 1.

Describe alternatives you've considered

Change the arrow-rs kernels to return a typed error. This would not fix regexp_count, regexp_instr or regexp_replace, which do not use the kernels, and it would not add planning-time validation.

Additional context

An empty flags string was a related problem, fixed by #25046 (#25021). All five functions now accept '' on main.

[adriangb]

#25352 does most of it. If someone is interested in more plan time validation, please open a follow up issue