[jira] [Comment Edited] (FLINK-39402) Fix LIKE quick path ignoring default escape character '\'

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

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

Timo Walther edited comment on FLINK-39402 at 4/7/26 12:23 PM:
---------------------------------------------------------------

[~au_miner] In standard SQL, there is no default escape character for the LIKE clause. I performed some research on this topic:
 - MySQL & PostgreSQL: These systems typically use the backslash as the default escape character in string literals and LIKE clauses out-of-the-box. However, relying on this is generally discouraged because it can be changed via server configurations (like NO_BACKSLASH_ESCAPES in MySQL or standard_conforming_strings in Postgres).
 - SQL Server (T-SQL): Has no default escape character. You must use the ESCAPE clause. Alternatively, SQL Server allows you to enclose wildcards in square brackets to escape them (e.g., LIKE '20[_]%').
 - Oracle & SQLite: Have no default escape character. You must explicitly define one using the ESCAPE clause if you need to escape a wildcard.

Until FLINK-36100, Flink did not support backslashes by default. The documentation for this is also broken ever since and reads like '' is the default escape sequence, leaving the impression that escaping is disabled by default:
!Screenshot 2026-04-07 at 14.15.23.png|width=850,height=83!

Given that we also don't support backslash in our string literals and default escape characters can cause confusion, I would suggest to not support a default escape character.


was (Author: twalthr):
[~au_miner] In standard SQL, there is no default escape character for the LIKE clause. I performed some research on this topic:
 - MySQL & PostgreSQL: These systems typically use the backslash () as the default escape character in string literals and LIKE clauses out-of-the-box. However, relying on this is generally discouraged because it can be changed via server configurations (like NO_BACKSLASH_ESCAPES in MySQL or standard_conforming_strings in Postgres).
 - SQL Server (T-SQL): Has no default escape character. You must use the ESCAPE clause. Alternatively, SQL Server allows you to enclose wildcards in square brackets to escape them (e.g., LIKE '20[_]%').
 - Oracle & SQLite: Have no default escape character. You must explicitly define one using the ESCAPE clause if you need to escape a wildcard.

Until FLINK-36100, Flink did not support backslashes by default. The documentation for this is also broken ever since and reads like '' is the default escape sequence, leaving the impression that escaping is disabled by default:
!Screenshot 2026-04-07 at 14.15.23.png|width=850,height=83!

Given that we also don't support backslash in our string literals and default escape characters can cause confusion, I would suggest to not support a default escape character.

> Fix LIKE quick path ignoring default escape character '\'
> ---------------------------------------------------------
>
>                 Key: FLINK-39402
>                 URL: https://issues.apache.org/jira/browse/FLINK-39402
>             Project: Flink
>          Issue Type: Bug
>          Components: Table SQL / Planner
>    Affects Versions: 2.3.0
>            Reporter: Qilong Wang
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 2.3.0
>
>         Attachments: Screenshot 2026-04-07 at 14.15.23.png
>
>
> The LIKE expression uses \ as the default escape character when no ESCAPE clause is specified(FLINK-36100). However, the quick path for 2-operand LIKE did not apply escape handling at all. Only \_ happened to produce correct results, while all other escape sequences were broken:
> {code:java}
> – \ escapes '' to match a literal '' — correct
> SELECT 'TE_ST' LIKE 'TE_ST'
> – \ escapes % to match a literal % — should be TRUE, but returns FALSE 
> SELECT 'TE%ST' LIKE 'TE\%ST'
> – \ followed by invalid character S — should throw RuntimeException, but returns FALSE
> SELECT 'TEST' LIKE '%E\S%'
> – \ at the end of pattern — should throw RuntimeException, but returns FALSE
> SELECT 'TEST' LIKE '%E-S%\'{code}
> The fix unifies escape handling for both 2-operand (no ESCAPE clause) and 3-operand LIKE expressions, ensuring the default escape character \ is properly applied in the quick path.



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