MySQL Bugs: #68828: search on specific string return no result

Bug #68828	search on specific string return no result
Submitted:	1 Apr 2013 19:42	Modified:	2 Apr 2013 4:59
Reporter:	sebastien Roux	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Storage Engines	Severity:	S2 (Serious)
Version:	5.5.30	OS:	Linux (Debian 2.6.32-5-686)
Assigned to:		CPU Architecture:	Any
Tags:	like, protected value, search

Description:
Try to recover a special string ./\. with operator LIKE and jocker digit %
Ok with strict search
KO with LIKE operator

How to repeat:
CREATE TABLE `mytab` (
  `identifier` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) NOT NULL,
  PRIMARY KEY (`identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO  `zzzz`.`mytab` (
`identifier` ,
`caption`
)
VALUES (
NULL ,  './\\.'
);

select * from mytab;
+------------+---------+
| identifier | caption |
+------------+---------+
|          1 | ./\.    |
+------------+---------+
1 row in set (0.00 sec)
Ok

Try to recover this record using LIKE operator and jocker digit %
mysql> SELECT * 
    -> FROM  `mytab` 
    -> WHERE  `caption` LIKE  '%./\\.%';
Empty set (0.00 sec)

For me, that is an issue or i miss something...

Suggested fix:
Ugly work around :
Insert additional % between /\ will return a row...
That's very weird..

Example :
SELECT * 
FROM  `mytab` 
WHERE  `caption` LIKE  '%./%\\.%'

mysql> SELECT *  FROM  `mytab`  WHERE  `caption` LIKE  '%./%\\.%';
+------------+---------+
| identifier | caption |
+------------+---------+
|          1 | ./\.    |
+------------+---------+
1 row in set (0.00 sec)
OK

See here:
http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html

"Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against."

mysql> SELECT * FROM  `mytab` WHERE  `caption` LIKE  "%./\\\\.%";
+------------+---------+
| identifier | caption |
+------------+---------+
|          1 | ./\.    |
+------------+---------+
1 row in set (0.00 sec)

Ok,
Thanks for your quick answer.
I will do that