[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression

[" EveyWu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] 

 EveyWu edited comment on CALCITE-6180 at 3/10/24 4:29 AM:
-----------------------------------------------------------

Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals.

Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data.
A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. 
{code:java}
  boolean find;
  try {
    find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
  } catch (Exception patternException) {
    find = false;
  }
  if (!find) {
    s = StringEscapeUtils.unescapeJava(s);
    pattern = StringEscapeUtils.unescapeJava(pattern);
    find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
  }
  return find;
{code}
For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time.


was (Author: eveywu):
Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals.

Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data.
A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. 

```
   boolean find;
  try {
    find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
  } catch (Exception patternException) {
    find = false;
  }
  if (!find) {
    s = StringEscapeUtils.unescapeJava(s);
    pattern = StringEscapeUtils.unescapeJava(pattern);
    find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
  }
  return find;
```

For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time.

> Append possibility to escape backslash in LIKE expression
> ---------------------------------------------------------
>
>                 Key: CALCITE-6180
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6180
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.36.0
>            Reporter: Evgeny Stanilovsky
>            Priority: Major
>              Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)