Bug #81990 LIKE condition with multiple backslashes, must add extra \ at the end

• Submitted: 23 Jun 2016 18:51
• Modified: 24 Dec 2019 13:49
• Reporter: Petras Ramanauskas
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any
• Tags: 5.6.31, 5.7.13

[23 Jun 2016 18:51] Petras Ramanauskas

Description:
I just noticed, that if i have a value in the db field, e.g. '\\\', and I want to find it using SQL with LIKE condition:

SELECT * FROM table WHERE field LIKE '%xxxx%' (wildcards at both sides)

then not only i need to put 3 times '\\\\' into sql, but also one more extra  backslash at the end.

If I don't put that extra backslash at the end, I get no results. If I add it - then I get the results. There should be no extra backslash by the rule, but it doesn't work without it...so it's a bug.

Note: 

-this happens when using utf8mb4_unicode_ci collation (if default collation is used, everything works fine)

-if another escape character is used - no issue

-if wildcards aren't used (or only one of them) - no issue

How to repeat:
Use this sqlfiddle to run LIKE condition with 12 backslashes and with 13 backslashes (12 + one more):

http://sqlfiddle.com/#!9/0e1dad/2

[23 Jun 2016 18:53] Petras Ramanauskas

http://stackoverflow.com/questions/37089966/mysql-like-condition-with-multiple-backslashes...

[23 Jun 2016 20:02] MySQL Verification Team

Please provide the complete test case here (create table, insert data, query actual result and expected one). Also fill the version field. Thanks.

[24 Jun 2016 6:17] Petras Ramanauskas

Version doesn't matter - all of them.

CREATE TABLE `people` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,  `name` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE = InnoDB;

insert into people(id, name) 
values('1','\\\\\\');

select *
from people 
where name like '%\\\\\\\\\\\\%';

NO RESULT.

select *
from people 
where name like '%\\\\\\\\\\\\\%';

YES RESULT.

[25 Jun 2016 9:29] MySQL Verification Team

Thank you for the requested details.
Observed this with 5.6.31/5.7.13.

Thanks,
Umesh

[24 Dec 2019 13:49] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18