pederbe opened a new issue, #26924:
URL: https://github.com/apache/datafusion/issues/26924
### Describe the bug
When a regex pattern contains an escaped backslash, `col ~ pattern` can
match rows that do not contain a backslash. `regexp_like(col, pattern)` is
affected too, because it simplifies to `~`.
The expression simplifier rewrites simple literal regex patterns to `LIKE`.
It copies the literal text into the `LIKE` pattern and refuses only `%` and
`_`. A backslash is copied unchanged, but `\` is the default escape character
of `LIKE`. So the regex `\\Kitchen` (a literal backslash followed by `Kitchen`)
becomes `LIKE '%\Kitchen%'`, which means `%Kitchen%`.
This was first reported downstream in influxdata/influxdb#27087.
### To Reproduce
In `datafusion-cli` (tested on `main` at d75fd7b86):
```sql
CREATE TABLE t(room VARCHAR) AS VALUES ('Kitchen'), ('/Kitchen'),
('\Kitchen');
SELECT room, room ~ '\\Kitchen' AS matches FROM t;
+----------+---------+
| room | matches |
+----------+---------+
| Kitchen | true |
| /Kitchen | true |
| \Kitchen | true |
+----------+---------+
EXPLAIN SELECT room FROM t WHERE room ~ '\\Kitchen';
-- FilterExec predicate: room LIKE %\Kitchen%
```
`SELECT room FROM t WHERE regexp_like(room, '\\Kitchen')` returns the same
three rows.
Other rewrites have the same problem. For example, `room ~ '^\\Kit'` becomes
`LIKE '\Kit%'`, and `room ~* '\\kitchen'` becomes `ILIKE '%\kitchen%'`.
### Expected behavior
Only `\Kitchen` matches, as it would without the rewrite.
### Additional context
The rewrite is in `datafusion/optimizer/src/simplify_expressions/regex.rs`
(`is_safe_for_like`). #22727 fixed a different problem in the same rewrite.
### Willingness to contribute
I plan to contribute a fix for this bug independently
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
