[ 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)