Odd behaviour when doing LIKE with wildcards searching for backslash in MySQL

Question

I've encountered a very unusual problem with MySQL, involving backslashes. Basically when I do a wildcard match with LIKE for \n which is in the database as text rather than an actual newline, it will only return a match if I have just a right hand wildcard:

SELECT * 
FROM  `tmptest` 
WHERE  `a` LIKE  '\\\\n%'

Now, if I query like this, it will not return anything:

SELECT *  
FROM `tmptest` 
WHERE `a` LIKE '%\\\\n%'

As you can see from the data I have in the table both queries should be matching. I'm not sure if it's something that I'm missing, or I'm incorrectly escaping the newline but it doesn't make sense for the first query to work and the second not.

Table structure:

CREATE TABLE IF NOT EXISTS `tmptest` (
`a` varchar(22) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample data:

INSERT INTO `tmptest` (`a`) VALUES
('\\n'),
('\\n\\ndfsdfsdfs\\n');

Thanks for taking time to read this.

Answer 1

It works for me with 6 backslashes when using the left side wildcard:

mysql> SELECT * FROM `tmptest` WHERE `a` LIKE '%\\\\\\n%';
+-----------------+
| a               |
+-----------------+
| \n              |
| \n\ndfsdfsdfs\n |
+-----------------+
2 rows in set (0.00 sec)

Using mysqld Ver 5.1.49

---

@Karolis as far as i understand the expression for the LIKE operator should be parsed twice, hence \\\\ turns into \ when used with LIKE.

But how to explain this (using the expression "back\slash"):

SELECT 'back\\slash' LIKE '%back\\\\slash%'; -> TRUE (normal behaviour)

SELECT 'back\\slash' LIKE '%back\\\\\slash%'; -> TRUE (5 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\slash%'; -> TRUE (6 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\slash%'; -> TRUE (7 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\\slash%'; -> FALSE (normal behaviour, I guess..)

Nevertheless if searching only for the "\":

mysql> SELECT 'back\\slash' LIKE '%\\\\%'; --> FALSE (but should work)

mysql> SELECT 'back\\slash' LIKE '%\\\\\%'; --> TRUE (5 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\%'; --> TRUE (6 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\\%'; --> FALSE (7 backslashes)

For this particular question, one could use a different escape character | and bypass the problem altogether (if no | character occurs):

mysql> SELECT 'back\\slash' LIKE '%\\%' ESCAPE '|'; --> TRUE

So maybe some mysql guru out there can explain this. I simply can't. also tested with mysql 5.1.53 on a different machine. Same behaviour was observed. As i started by commenting, its a rather interesting question.