[
https://issues.apache.org/jira/browse/FLINK-39402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18071686#comment-18071686
]
Timo Walther commented on FLINK-39402:
--------------------------------------
[~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)
