ClickHouse / ClickHouse/ClickHouse
optimize_rewrite_regexp_functions rewrites replaceRegexpAll to replaceRegexpOne for multi-match $-anchored patterns ((?m) multiline, empty-matching tails) — replacements silently lost
- 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
Assessment
This issue has not been assessed yet.