Incorrect regex-to-LIKE simplification for literal backslashes

Describe the bug

A regex matching a literal backslash is rewritten to a LIKE pattern with different escaping semantics, producing both false positives and false negatives.

Reproduced in 55.2.0 and 56.0.0-dev (1635ed9).

To Reproduce

Create regex.csv:

id,s,p
1,a\b,a\\b
2,ab,a\\b

Run:

CREATE EXTERNAL TABLE regex_input (
 id BIGINT, s VARCHAR, p VARCHAR
)
STORED AS CSV LOCATION 'regex.csv'
OPTIONS ('has_header' 'true');

SELECT
 id,
 s ~ ('a' || chr(92) || chr(92) || 'b') AS constant_pattern,
 s ~ p AS dynamic_pattern
FROM regex_input
ORDER BY id;

Expected:

id | constant_pattern | dynamic_pattern
1  | true             | true
2  | false            | false

Actual:

id | constant_pattern | dynamic_pattern
1  | false            | true
2  | true             | false

The constant pattern is optimized into LIKE '%a\b%', where the backslash is consumed as a LIKE escape. The dynamic pattern retains the correct regex behavior.

Expected behavior

No response

Additional context

No response

Willingness to contribute

None

take

@jonasHanhan

This is a blocker for our 56 release. @jonasHanhan do you think you will be able to make a PR quickly?

If not, no worries, we can do it too

Thanks for the ping. I've hit a CreatePullRequest permission error on this account (looks like I'm at a cap on open PRs), so I won't be able to open one quickly. Please go ahead and take it — no worries on my side. Happy to review or follow up later if useful.

I did some digging. Here is the PR that introduced it:

perf: Apply logical regexp optimizations to Utf8View and LargeUtf8 inputs #20581

The bad news is that it still happens. The "good" news is that it is not a new regression (it appears to have been introduced in 54). That doesn't mean it shouldn't be fixed but I also don't think we need to treat it as a 56 release blocker.

Version	`s = 'a\b'`	`s = 'ab'`	Status
53.0.0	true	false	correct
53.1.0	true	false	correct
54.0.0	false	true	wrong
54.1.0	false	true	wrong
55.0.0	false	true	wrong
55.1.0	false	true	wrong
main	false	true	wrong

SELECT
  s,
  s ~ ('a' || chr(92) || chr(92) || 'b') AS matches_regex
FROM (VALUES (arrow_cast('a\b', 'Utf8View')), (arrow_cast('ab', 'Utf8View'))) AS t(s)
ORDER BY s;

Correct output (53.1.0):

+-----+---------------+
| s   | matches_regex |
+-----+---------------+
| a\b | true          |
| ab  | false         |
+-----+---------------+

Wrong output (54.0.0 and later, including main):

+-----+---------------+
| s   | matches_regex |
+-----+---------------+
| a\b | false         |
| ab  | true          |
+-----+---------------+

Details

andrewlamb@Andrews-MacBook-Pro-3:~$ ~/Software/datafusion-cli/datafusion-cli-53.1.0
DataFusion CLI v53.1.0
> SELECT
  s,
  s ~ ('a' || chr(92) || chr(92) || 'b') AS matches_regex
FROM (VALUES (arrow_cast('a\b', 'Utf8View')), (arrow_cast('ab', 'Utf8View'))) AS t(s)
ORDER BY s;
+-----+---------------+
| s   | matches_regex |
+-----+---------------+
| a\b | true          |
| ab  | false         |
+-----+---------------+
2 row(s) fetched.
Elapsed 0.002 seconds.

>
\q
andrewlamb@Andrews-MacBook-Pro-3:~$ ~/Software/datafusion-cli/datafusion-cli-54.0.0
DataFusion CLI v54.0.0
> SELECT
  s,
  s ~ ('a' || chr(92) || chr(92) || 'b') AS matches_regex
FROM (VALUES (arrow_cast('a\b', 'Utf8View')), (arrow_cast('ab', 'Utf8View'))) AS t(s)
ORDER BY s;
+-----+---------------+
| s   | matches_regex |
+-----+---------------+
| a\b | false         |
| ab  | true          |
+-----+---------------+
2 row(s) fetched.
Elapsed 0.002 seconds.

@alamb

@alamb Thanks for following up on this! 🙏 Hope we can make progress on a minor version.

Navigation Menu

Repository navigation

Incorrect regex-to-LIKE simplification for literal backslashes #27120

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Incorrect regex-to-LIKE simplification for literal backslashes #27120

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Activity

jonasHanhan commented on Nov 5, 2026

alamb commented on Nov 5, 2026

alamb commented on Nov 5, 2026

jonasHanhan commented on Nov 5, 2026

alamb commented on Nov 6, 2026

lonless9 commented on Nov 8, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions