Bug #108011 the special character

• Submitted: 28 Jul 2022 6:54
• Modified: 23 Aug 2022 2:19
• Reporter: hui zhu
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.27, 5.7.39, 8.0.30
• OS: CentOS
• CPU Architecture: x86

[28 Jul 2022 6:54] hui zhu

Description:
when the records in the table include the special character "\",will cause the query result incorrect

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 VARCHAR(100) , c1 REAL  NOT NULL, c2 BIGINT ZEROFILL  UNIQUE, PRIMARY KEY(c0, c2))
INSERT INTO t0 VALUES ("", -1804110191, 1310085724);
INSERT INTO t0 VALUES (" s", 1457785441, 860465620);
INSERT INTO t0 VALUES ('{4Nuap', -1755053066, 1138686299);
CREATE UNIQUE INDEX ic1 USING HASH ON t0(c0, c2, c1);
INSERT INTO t0 VALUES ("\\Ed>ZD", -1993329556, 1831716460);
INSERT INTO t0 VALUES ('mrb2*HkcI', 793249193, 0);
SELECT t0.c0 FROM t0 WHERE (NOT (t0.c0 LIKE t0.c0));

As expected，the query result should be no record，but in fact，the query result shows one record，the value is "\\Ed>ZD".

[28 Jul 2022 8:00] MySQL Verification Team

Hello hui zhu,

Thank you for the report and test case.

regards,
Umesh

[2 Aug 2022 9:20] Roy Lyseng

Posted by developer:
 
This is not a bug.
MySQL uses \ as a default escape character in LIKE patterns, thus this character must be duplicated in the pattern to get a match.

[23 Aug 2022 2:19] hui zhu

Hello，Roy Lyseng
Thanks for your reply.