Bug #113949 Backslash escaping inconsistency in LIKE operator
Submitted: 9 Feb 19:55 Modified: 12 Feb 9:15
Reporter: Nathee Jaywaree Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:8.3.0, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: escape backslash

[9 Feb 19:55] Nathee Jaywaree
Description:
Backslash is not being escaped consistently in LIKE operator with NO_BACKSLASH_ESCAPES sql mode enabled in some statements as shown below.
It is unexpected that the same string is accepted in the SELECT statement, but results in an error when used in the CREATE TABLE and CREATE INDEX statement.

How to repeat:
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT 'a' LIKE 'a' ESCAPE '\'; -- returns 1
CREATE TABLE t0 (c0 BOOLEAN DEFAULT('a' LIKE 'a' ESCAPE '\')); -- ERROR 1210 (HY000): Incorrect arguments to ESCAPE
CREATE TABLE t0 (c0 BOOLEAN GENERATED ALWAYS AS ('a' LIKE 'a' ESCAPE '\')); -- Incorrect arguments to ESCAPE
CREATE TABLE t0 (c0 BOOLEAN, CHECK('a' LIKE 'a' ESCAPE '\')); -- Incorrect arguments to ESCAPE

CREATE TABLE t0 (c0 BOOLEAN);
CREATE INDEX i0 on t0 (('a' LIKE 'a' ESCAPE '\')); -- Incorrect arguments to ESCAPE
[12 Feb 9:15] MySQL Verification Team
Hello Nathee Jaywaree,

Thank you for the report and feedback.

regards,
Umesh