[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17804878#comment-17804878
]
Julian Hyde edited comment on CALCITE-6180 at 1/9/24 8:42 PM:
--------------------------------------------------------------
The PR was originally merged to main as commit
[32173211|https://github.com/apache/calcite/commit/321732114993090fa7c87a67b1026d2d4b86b907];
I have reverted in commit
[a86bca8c|https://github.com/apache/calcite/commit/a86bca8c3e8aa134eb0f416e91f699be02b7d20c].
The expression
{noformat}
'a\b' like 'a!\b' escape '!'{noformat}
gives an error in Oracle, in hsqldb, in Calcite 1.36; following the revert,
Calcite now (correctly) gives an error again.
{noformat}
# Oracle
> select case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end from dual;
ORA-01424: missing or illegal character following the escape character
# Hsqldb
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
Error: data exception: invalid escape sequence (state=22025,code=-3458)
# Calcite (as of 1.36), and after revert
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
Invalid escape sequence 'a!\b', 1
# Calcite (before revert)
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
+--------+
| EXPR$0 |
+--------+
| 1 |
+--------+
1 row selected (1.896 seconds)
{noformat}
The fact is that Calcite has different semantics for {{LIKE}} than Postgres,
MySQL. (It's likely that SQLite is similar to Postgres, and Maria is similar to
MySQL. MySQL's {{LIKE}} semantics, due to handling of backslashes in strings,
are very complicated. BigQuery has an {{IS LIKE}} operator but it's even more
different.)
I would welcome a PR that emulates Postgres or MySQL semantics. But the PR must
have a flag so that people can retain current Calcite semantics, and must have
tests for the behavior of the operator in all other supported semantics.
was (Author: julianhyde):
The PR was originally merged to main as commit
[32173211|https://github.com/apache/calcite/commit/321732114993090fa7c87a67b1026d2d4b86b907];
I have reverted in commit
[a86bca8c|https://github.com/apache/calcite/commit/a86bca8c3e8aa134eb0f416e91f699be02b7d20c].
The expression {'a\b' like 'a!\b' escape '!'} is invalid in Oracle, hsqldb, was
invalid in Calcite as of 1.36 and is now invalid again.
{noformat}
# Oracle
> select case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end from dual;
ORA-01424: missing or illegal character following the escape character
# Hsqldb
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
Error: data exception: invalid escape sequence (state=22025,code=-3458)
# Calcite (as of 1.36), and after revert
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
Invalid escape sequence 'a!\b', 1
# Calcite (before revert)
> values case when 'a\b' like 'a!\b' escape '!' then 1 else 0 end;
+--------+
| EXPR$0 |
+--------+
| 1 |
+--------+
1 row selected (1.896 seconds)
{noformat}
The fact is that Calcite has different semantics for {{LIKE}} than Postgres,
MySQL. (It's likely that SQLite is similar to Postgres, and Maria is similar to
MySQL. MySQL's handling of backslashes in strings makes it very complicated.)
I would welcome a PR that emulates Postgres or MySQL semantics. But the PR must
have a flag so that people can retain current Calcite semantics.
> 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
> Assignee: Evgeny Stanilovsky
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> 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)
