ClickHouse / ClickHouse/ClickHouse

optimize_rewrite_regexp_functions rewrites replaceRegexpAll to replaceRegexpOne for multi-match $-anchored patterns ((?m) multiline, empty-matching tails) — replacements silently lost

Open
#116,921 0 comments 0 reactions 2 assignees Claimed by @KochetovNicolai View on GitHub
bug comp-query-analyzer
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
20h 33m
Merged PRs (30d)
501

Description

**Describe what's wrong**

`optimize_rewrite_regexp_functions` (ON by default since 25.8) rewrites `replaceRegexpAll` — and its alias `regexp_replace` — into `replaceRegexpOne` whenever the pattern starts with `^` or ends with an unescaped `$` and has no top-level alternatives (`src/Analyzer/Passes/RegexpFunctionRewritePass.cpp`, `handleReplaceRegexpAll`). The rewrite assumes such a pattern can match at most once, so replacing "all" and "one" are the same. That assumption is wrong in two cases, and both silently drop replacements at pure default settings:

1. An inline multiline flag: with `(?m)`, `$` matches at every line end, so a `$`-terminated pattern can match on every line. After the rewrite only the first line's occurrence is replaced.
2. A trailing subpattern that can also match the empty string (for example `o*$`): global replacement replaces both the non-empty match and the final empty match at the end of the string, while the rewritten `replaceRegexpOne` performs only the first replacement.

With the setting OFF the results agree with re2 global-replace semantics and with the documented contract of `replaceRegexpAll` ("replaces all occurrences"). Only the analyzer path is affected: with `enable_analyzer = 0` both arms return the correct result.

**Does it reproduce on the most recent release?**

Yes. Verified on 26.4.1.1 and on a 26.9 development build; `handleReplaceRegexpAll` in `src/Analyzer/Passes/RegexpFunctionRewritePass.cpp` is identical on current master. The setting has defaulted to ON since 25.8.

**How to reproduce**

```sql
SELECT
replaceRegexpAll(h, '(?m)a$', 'Z') AS multiline,
regexp_replace(h, '(?m)a$', 'Z') AS multiline_alias,
replaceRegexpAll(h2, 'o*$', 'Z') AS empty_tail
FROM (SELECT 'a\nb\na' AS h, 'foo' AS h2)
FORMAT Vertical;

-- At default settings (optimize_rewrite_regexp_functions = 1):
-- multiline: 'Z\nb\na' (only the first line-end match replaced)
-- multiline_alias: 'Z\nb\na'
-- empty_tail: 'fZ'
--
-- SETTINGS optimize_rewrite_regexp_functions = 0:
-- multiline: 'Z\nb\nZ'
-- multiline_alias: 'Z\nb\nZ'
-- empty_tail: 'fZZ'
```

The haystack has to come from a column (here a subquery alias); with a fully constant argument the expression is folded before the divergence is observable.

`EXPLAIN QUERY TREE` confirms the mechanism: with the setting ON the plan contains `function_name: replaceRegexpOne` with the original `(?m)a$` pattern.

**Expected behavior**

`replaceRegexpAll` replaces every match regardless of the optimization setting: `Z\nb\nZ` for the multiline columns and `fZZ` for the empty-tail column.

**Error message and/or stacktrace**

No error — the results are silently wrong.

**Additional context**

The guard in `handleReplaceRegexpAll` checks only `regexp.front() == '^'`, an unescaped trailing `$`, and the absence of alternatives via `OptimizedRegularExpression::analyze`. Neither check rejects an inline `(?m)` (which makes `$` multi-match), nor a `$`-anchored pattern whose tail can match empty (for which a global replace performs two replacements at the end of the string). A pattern like `(?m)^a` happens not to be rewritten only because its first character is `(` rather than `^`, so the caret side of the rewrite is currently reached only by patterns without inline flags — the `$` side fires regardless of where the flag appears.

A related unsound rewrite in the same pass for `extract` is reported separately: #116922.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.