Bug: Filtering by regex pattern throwing an error

[benHPostHog]

Bug Description

Pattern being tested:
^(?=[A-Za-z0-9().]*[().])[A-Za-z0-9().]+$

Produces the error below.

It matches strings that:
Only contain letters, numbers, parentheses ( ), and periods .
Must include at least one of: (, ), or .

Matching Strings:

abc.def
123(456)
test.data()
A.B
()
.

• The regex pattern follows standard regex syntax
• The pattern includes a positive lookahead and character class, which are common regex features
• The pattern has been validated in standard regex testers

Additional context

From: https://posthoghelp.zendesk.com/agent/tickets/24304

Debug info

Kind: bug

Target area: analytics

Report event: http://go/ticketByUUID/6990df03-3e5d-4e84-8527-391ba22e4f58

Session: https://us.posthog.com/project/sTMFPsFhdP1Ssg/replay/0194b256-bf46-7bf2-b1bc-62b319d9ac2a?t=3650

Exceptions: https://us.posthog.com/project/2/error_tracking?filterGroup=%7B%22type%22%3A%22AND%22%2C%22values%22%3A%5B%7B%22type%22%3A%22AND%22%2C%22values%22%3A%5B%7B%22key%22%3A%22%24session_id%22%2C%22value%22%3A%5B%220194b256-bf46-7bf2-b1bc-62b319d9ac2a%22%5D%2C%22operator%22%3A%22exact%22%2C%22type%22%3A%22event%22%7D%5D%7D%5D%7D

Location: https://us.posthog.com/project/1657/insights/SHMinvGN/edit

Sentry: http://go/sentryUS/1657

Persons-on-events mode for project: person_id_override_properties_on_events

[cosmeoes]

This is the error message:

DB::Exception: OptimizedRegularExpression: cannot compile re2: ^(?=[A-Za-z0-9().]*[().])[A-Za-z0-9().]+$, error: invalid perl operator: (?=. Look at https://github.com/google/re2/wiki/Syntax for reference.

Here is a comment relevant to this bug: ClickHouse/ClickHouse#43757 (comment)

It seems ClickHouse uses re2 which doesn't support positive lookahead functions:

RE2 does not support arbitrary lookahead or lookbehind assertions, nor does it support backreferences

Source: https://swtch.com/~rsc/regexp/regexp3.html#caveats