Skip to content

Regex m option parameter does not enable "Newline sensitive matching" #17056

New issue
New issue
Open
Open
Regex m option parameter does not enable "Newline sensitive matching"#17056
Labels
reproduced

Description

@prmelehan
prmelehan
opened on Apr 10, 2025

What happens?

When using regexp_extract, with the m option as a final argument, the resulting query does not apply the correct, documented behavior.

SELECT regexp_extract(
    'Line 1
     Some text
     Line 2
     More text',
    '^Line.*$',
  0,
  'm');

Will yield an empty string, ''

The DuckDB documentation states that:

'm', 'n', 'p' | Newline sensitive matching

If I provide (?m) as a prefix, then the newline sensitive matching works correctly...

SELECT regexp_extract(
    'Line 1
     Some text
     Line 2
     More text',
    '(?m)^Line.*$');

== 'Line 1'

From what I've gathered, it might be found here:

duckdb/src/function/scalar/string/regexp.cpp

Lines 224 to 321 in e1dcf13

static void RegexExtractFunction(DataChunk &args, ExpressionState &state, Vector &result) {
auto &func_expr = state.expr.Cast<BoundFunctionExpression>();
const auto &info = func_expr.bind_info->Cast<RegexpExtractBindData>();
auto &strings = args.data[0];
auto &patterns = args.data[1];
if (info.constant_pattern) {
auto &lstate = ExecuteFunctionState::GetFunctionState(state)->Cast<RegexLocalState>();
UnaryExecutor::Execute<string_t, string_t>(strings, result, args.size(), [&](string_t input) {
return Extract(input, result, lstate.constant_pattern, info.rewrite);
});
} else {
BinaryExecutor::Execute<string_t, string_t, string_t>(strings, patterns, result, args.size(),
[&](string_t input, string_t pattern) {
RE2 re(CreateStringPiece(pattern), info.options);
return Extract(input, result, re, info.rewrite);
});
}
}
//===--------------------------------------------------------------------===//
// Regexp Extract Struct
//===--------------------------------------------------------------------===//
static void RegexExtractStructFunction(DataChunk &args, ExpressionState &state, Vector &result) {
auto &lstate = ExecuteFunctionState::GetFunctionState(state)->Cast<RegexLocalState>();
const auto count = args.size();
auto &input = args.data[0];
auto &child_entries = StructVector::GetEntries(result);
const auto groupSize = child_entries.size();
// Reference the 'input' StringBuffer, because we won't need to allocate new data
// for the result, all returned strings are substrings of the originals
for (auto &child_entry : child_entries) {
child_entry->SetAuxiliary(input.GetAuxiliary());
}
vector<RE2::Arg> argv(groupSize);
vector<RE2::Arg *> groups(groupSize);
vector<duckdb_re2::StringPiece> ws(groupSize);
for (size_t i = 0; i < groupSize; ++i) {
groups[i] = &argv[i];
argv[i] = &ws[i];
}
if (input.GetVectorType() == VectorType::CONSTANT_VECTOR) {
result.SetVectorType(VectorType::CONSTANT_VECTOR);
if (ConstantVector::IsNull(input)) {
ConstantVector::SetNull(result, true);
} else {
ConstantVector::SetNull(result, false);
auto idata = ConstantVector::GetData<string_t>(input);
auto str = CreateStringPiece(idata[0]);
auto match = duckdb_re2::RE2::PartialMatchN(str, lstate.constant_pattern, groups.data(),
UnsafeNumericCast<int>(groups.size()));
for (size_t col = 0; col < child_entries.size(); ++col) {
auto &child_entry = child_entries[col];
ConstantVector::SetNull(*child_entry, false);
auto &extracted = ws[col];
auto cdata = ConstantVector::GetData<string_t>(*child_entry);
cdata[0] = string_t(extracted.data(), UnsafeNumericCast<uint32_t>(match ? extracted.size() : 0));
}
}
} else {
UnifiedVectorFormat iunified;
input.ToUnifiedFormat(count, iunified);
const auto &ivalidity = iunified.validity;
auto idata = UnifiedVectorFormat::GetData<string_t>(iunified);
// Start with a valid flat vector
result.SetVectorType(VectorType::FLAT_VECTOR);
// Start with valid children
for (size_t col = 0; col < child_entries.size(); ++col) {
auto &child_entry = child_entries[col];
child_entry->SetVectorType(VectorType::FLAT_VECTOR);
}
for (idx_t i = 0; i < count; ++i) {
const auto idx = iunified.sel->get_index(i);
if (ivalidity.RowIsValid(idx)) {
auto str = CreateStringPiece(idata[idx]);
auto match = duckdb_re2::RE2::PartialMatchN(str, lstate.constant_pattern, groups.data(),
UnsafeNumericCast<int>(groups.size()));
for (size_t col = 0; col < child_entries.size(); ++col) {
auto &child_entry = child_entries[col];
auto cdata = FlatVector::GetData<string_t>(*child_entry);
auto &extracted = ws[col];
cdata[i] = string_t(extracted.data(), UnsafeNumericCast<uint32_t>(match ? extracted.size() : 0));
}
} else {
FlatVector::SetNull(result, i, true);
}
}
}
}

To Reproduce

Provide this query:

SELECT regexp_extract(
    'Line 1
     Some text
     Line 2
     More text',
    '^Line.*$',
  0,
  'm');

You will get an empty string ''

OS:

macOS (arm64)

DuckDB Version:

1.2.0

DuckDB Client:

CLI / C++

Hardware:

No response

Full Name:

Ryan Melehan

Affiliation:

Coco Alemana

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

  • Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

  • Yes, I have

Activity

  1. prmelehan
    added
    needs triage
    on Apr 10, 2025
  2. szarnyasg
    added
    reproduced
    on Apr 10, 2025
  3. duckdblabs-bot
    removed
    needs triage
    on Apr 10, 2025

  4. github-actions commented on Apr 11, 2026

    @github-actions
    github-actionsBot
    on Apr 11, 2026 – with GitHub Actions

    This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 30 days.

  5. github-actions
    added
    stale
    on Apr 11, 2026

  6. IfkumRfnl commented on Apr 12, 2026

    @IfkumRfnl
    IfkumRfnl
    on Apr 12, 2026
    Contributor

    I dug into a fix for this issue, seems like the root cause is mapping 'm' only to set_dot_nl(false) here

    duckdb/src/function/scalar/string/regexp/regexp_util.cpp

    Lines 22 to 64 in fc55355

    void ParseRegexOptions(const string &options, duckdb_re2::RE2::Options &result, bool *global_replace) {
    for (idx_t i = 0; i < options.size(); i++) {
    switch (options[i]) {
    case 'c':
    // case-sensitive matching
    result.set_case_sensitive(true);
    break;
    case 'i':
    // case-insensitive matching
    result.set_case_sensitive(false);
    break;
    case 'l':
    // literal matching
    result.set_literal(true);
    break;
    case 'm':
    case 'n':
    case 'p':
    // newline-sensitive matching
    result.set_dot_nl(false);
    break;
    case 's':
    // non-newline-sensitive matching
    result.set_dot_nl(true);
    break;
    case 'g':
    // global replace, only available for regexp_replace
    if (global_replace) {
    *global_replace = true;
    } else {
    throw InvalidInputException("Option 'g' (global replace) is only valid for regexp_replace");
    }
    break;
    case ' ':
    case '\t':
    case '\n':
    // ignore whitespace
    break;
    default:
    throw InvalidInputException("Unrecognized Regex option %c", options[i]);
    }
    }
    }

    That setting controls whether . matches newlines. It does not enable multiline anchor behavior for ^ and $. In RE2’s default Perl-style mode, multiline anchor semantics are not driven by RE2::Options; they are expressed inline via (?m).

    So one solution is to add (?m) to the pattern when the option 'm' is set. But there is one more issue on the optimizer side.

    duckdb/src/optimizer/rule/regex_optimizations.cpp

    Lines 150 to 221 in fc55355

    unique_ptr<Expression> RegexOptimizationRule::Apply(LogicalOperator &op, vector<reference<Expression>> &bindings,
    bool &changes_made, bool is_root) {
    auto &root = bindings[0].get().Cast<BoundFunctionExpression>();
    auto &constant_expr = bindings[2].get().Cast<BoundConstantExpression>();
    D_ASSERT(root.children.size() == 2 || root.children.size() == 3);
    auto regexp_bind_data = root.bind_info.get()->Cast<RegexpMatchesBindData>();
    auto constant_value = ExpressionExecutor::EvaluateScalar(GetContext(), constant_expr);
    D_ASSERT(constant_value.type() == constant_expr.return_type);
    duckdb_re2::RE2::Options parsed_options = regexp_bind_data.options;
    if (constant_expr.value.IsNull()) {
    return make_uniq<BoundConstantExpression>(Value(root.return_type));
    }
    auto patt_str = StringValue::Get(constant_value);
    // the constant_expr is a scalar expression that we have to fold
    if (!constant_expr.IsFoldable()) {
    return nullptr;
    };
    duckdb_re2::RE2 pattern(patt_str, parsed_options);
    if (!pattern.ok()) {
    return nullptr; // this should fail somewhere else
    }
    LikeString like_string;
    // check for a like string. If we can convert it to a like string, the like string
    // optimizer will further optimize suffix and prefix things.
    if (pattern.Regexp()->op() == duckdb_re2::kRegexpLiteralString ||
    pattern.Regexp()->op() == duckdb_re2::kRegexpLiteral) {
    // convert to contains.
    LikeString escaped_like_string = GetLikeStringEscaped(pattern.Regexp(), true);
    if (!escaped_like_string.exists) {
    return nullptr;
    }
    // if regexp had options, remove them so the new Contains Expression can be matched for other optimizers.
    if (root.children.size() == 3) {
    root.children.pop_back();
    D_ASSERT(root.children.size() == 2);
    }
    auto parameter = make_uniq<BoundConstantExpression>(Value(std::move(escaped_like_string.like_string)));
    auto contains = make_uniq<BoundFunctionExpression>(root.return_type, GetStringContains(),
    std::move(root.children), nullptr);
    contains->children[1] = std::move(parameter);
    return std::move(contains);
    } else if (pattern.Regexp()->op() == duckdb_re2::kRegexpConcat) {
    like_string = LikeMatchFromRegex(pattern);
    } else {
    like_string.exists = false;
    }
    if (!like_string.exists) {
    return nullptr;
    }
    // if regexp had options, remove them so the new Like Expression can be matched for other optimizers.
    if (root.children.size() == 3) {
    root.children.pop_back();
    D_ASSERT(root.children.size() == 2);
    }
    auto like_expression =
    make_uniq<BoundFunctionExpression>(root.return_type, LikeFun::GetFunction(), std::move(root.children), nullptr);
    auto parameter = make_uniq<BoundConstantExpression>(Value(std::move(like_string.like_string)));
    like_expression->children[1] = std::move(parameter);
    return std::move(like_expression);
    }

    Here it replaces some regexp_matches calls into contains(...) / LIKE calls (without checking anything special for the 'm' option). There is also a test for this.

    duckdb/test/optimizer/regex_optimizer.test

    Lines 232 to 248 in fc55355

    # Test regexp_matches with flags (like 'm') is properly optimized to contains
    statement ok
    DELETE FROM test;
    statement ok
    INSERT INTO test VALUES ('hello world'), ('test'), ('hello again');
    query II
    explain analyze SELECT s FROM test WHERE regexp_matches(s, 'hello', 'm');
    ----
    analyzed_plan <REGEX>:.*contains\(s, 'hello'\).*
    query I nosort
    SELECT s FROM test WHERE regexp_matches(s, 'hello', 'm');
    ----
    hello world
    hello again

    If I make the optimizer analyze the normalized (?m) pattern directly, it becomes too conservative and stops optimizing safe cases like regexp_matches(s, 'hello', 'm') and if I keep the old optimizer logic unchanged, then anchored patterns like regexp_matches(s, '^Some', 'm') can still be optimized incorrectly.

    I see two reasonable options:

    1. Disable regex rewrites when external multiline mode is present and the pattern uses anchor semantics
    2. Check whether the rewrite is safe and explicitly block patterns where ^ / $ outside char classes make multiline mode semantically relevant.

    @Mytherin what do you think about this? Which direction would be preferable here?

  7. github-actions
    removed
    stale
    on Apr 13, 2026

  8. Mytherin commented on Apr 14, 2026

    @Mytherin
    Mytherin
    on Apr 14, 2026
    Member

    I think this is just a documentation issue, all the 'm' option controls is whether or not the dot matches a newline, i.e.:

    memory D select regexp_matches(chr(10), '.', 's') dot_matches_newline, regexp_matches(chr(10), '.', 'm') dot_does_not_match_newline, regexp_matches(chr(10), '.') as default_matching_behavior;
┌─────────────────────┬────────────────────────────┬───────────────────────────┐
│ dot_matches_newline │ dot_does_not_match_newline │ default_matching_behavior │
│       boolean       │          boolean           │          boolean          │
├─────────────────────┼────────────────────────────┼───────────────────────────┤
│ true                │ false                      │ false                     │
└─────────────────────┴────────────────────────────┴───────────────────────────┘

    It does not enable multi-line mode, which is enabled through (?m). Multi-line mode affects anchors, i.e. it controls whether or not ^/$ match at line boundaries vs matching the start/end of the entire string. Multi-line mode does not affect whether or not dot matches newlines. For example, you can see that 'm' and 's' have different behavior also in multi-line matching mode:

    select regexp_extract(mls, '(?m)^multi.*$', 0, 'm') ml_mode_dot_does_not_match_nl, regexp_extract(mls, '(?m)^multi.*$', 0, 's') ml_mode_dot_matches_nl from (values (concat('multi', chr(10), 'line', chr(10), 'string'))) t(mls);
┌───────────────────────────────┬────────────────────────┐
│ ml_mode_dot_does_not_match_nl │ ml_mode_dot_matches_nl │
│            varchar            │        varchar         │
├───────────────────────────────┼────────────────────────┤
│ multi                         │ multi\nline\nstring    │
└───────────────────────────────┴────────────────────────┘

    I don't see any reason to change any of this functionality or move it around (in backwards incompatible ways). The functionality is available and works as expected - we just need to improve documentation of these features.

  9. Mytherin commented on Apr 14, 2026

    @Mytherin
    Mytherin
    on Apr 14, 2026
    Member

    I think the confusion here stems from the fact that m conflicts with RE2's own flags, which uses (?m) to signal multi-line anchor mode and (?s) to signal "dot matches nl". I suppose since 'm' is the default it also does not offer much utility as a flag. We could consider deprecating it, but I would not silently change the behavior of this existing option at this point.

  10. IfkumRfnl commented on Apr 14, 2026

    @IfkumRfnl
    IfkumRfnl
    on Apr 14, 2026
    Contributor

    Got it, thanks for clarification. I agree that the behavior shouldn't be changed.

  11. marcodelpin
    added 3 commits that reference this issue on Jul 18, 2026

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching

    c04556a

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching

    a0d6f73

    clang-format: apply canonical formatting to fork fix commits (duckdb#…

    70a31f6
  12. marcodelpin
    added 3 commits that reference this issue on Aug 31, 2026

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching

    3ca17ca

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    cbea035

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    061ca3c
  13. marcodelpin
    added 12 commits that reference this issue on Sep 16, 2026

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    b8ab7b3

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    da3a2c7

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    ffe955a

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    95e0e27

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    77de9df

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    4b3bc8b

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    b11be8a

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    fa427ff

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    6fd6e70

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    2e55a2f

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    446c7d6

    Fix regex 'm'/'n'/'p' option not enabling newline-sensitive matching (d…

    ef230ae
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    reproduced

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions