`regexp_match` and `regexp_like` reject an empty flags string

[jayzhan211]

Describe the bug

An empty flags string ('') is rejected by regexp_match and by regexp_like (when the flags argument is not a literal), while regexp_count, regexp_instr and — as of #24987 — regexp_replace all accept it and treat it as "no flags".

The flags string is spliced into the pattern as (?{flags}), so an empty string produces the invalid regex (?):

DataFusion error: Arrow error: Compute error: Regular expression did not compile: Syntax(
regex parse error:
    (?)b..
     ^
error: repetition operator missing expression
)

Postgres accepts an empty flags string for all of these functions and treats it as no flags.

There is a second, related inconsistency inside regexp_like itself: a literal '' works, because derive_operator maps "" to Operator::RegexMatch and the simplifier rewrites the call to a ~ operator before execution (regexplike.rs#L246-L252). A non-literal '' reaches the kernel and errors. So the same value produces different results depending only on whether constant folding applied.

To Reproduce

-- errors
SELECT regexp_match('foobarbaz', 'b..', '');

-- errors (flags is a column, so the simplifier cannot fold it)
SELECT regexp_like('foobarbaz', 'b..', flags) FROM (VALUES ('')) t(flags);

-- works today: literal '' is simplified into the `~` operator
SELECT regexp_like('foobarbaz', 'b..', '');

-- works today
SELECT regexp_count('foobarbaz', 'b..', 1, '');
SELECT regexp_instr('foobarbaz', 'b..', 1, 1, '');
SELECT regexp_replace('foobarbaz', 'b..', 'X', '');  -- fixed by #24987

Expected behavior

'' behaves the same as omitting the flags argument, for every regexp_* function and regardless of whether the argument is a literal:

SELECT regexp_match('foobarbaz', 'b..', '');  -- [bar]
SELECT regexp_like('foobarbaz', 'b..', flags) FROM (VALUES ('')) t(flags);  -- true

Additional context

The functions that already work do so because compile_regex special-cases the empty string:

https://github.com/apache/datafusion/blob/main/datafusion/functions/src/regex/mod.rs#L165-L177

let pattern = match flags {
    None | Some("") => regex.to_string(),
    ...
};

regexp_like and regexp_match do not go through compile_regex. They delegate to the arrow-rs kernels, which build the pattern unconditionally (arrow-string-59.2.0/src/regexp.rs, e.g. lines 97, 186, 218, 477):

let pattern = match flag {
    Some(flag) => format!("(?{flag}){regex}"),
    None => regex.to_string(),
};

Call sites in DataFusion:

• datafusion/functions/src/regex/regexplike.rs:380,384,388 — regexp_is_match_scalar
• datafusion/functions/src/regex/regexplike.rs:444-500 — regexp_is_match
• datafusion/functions/src/regex/regexplike.rs:416 — regexp_like_scalar builds (?{flagz}){pattern} itself
• datafusion/functions/src/regex/regexpmatch.rs:197,240 — regexp::regexp_match

Two possible fixes:

1. Normalize in DataFusion — map an empty flags value to None before calling the kernels (and in regexp_like_scalar). Self-contained, no upstream dependency.
2. Fix upstream in arrow-rs so Some("") is treated as None, and drop the workaround later.

Option 1 seems preferable as the immediate fix, since it also covers regexp_like_scalar, which does its own formatting.

Follow-up to #24987, which fixed the same class of bug in regexp_replace.

[peterxcli]

take