Bug #46147 Problem with LIKE Operator.
Submitted: 13 Jul 2009 13:28 Modified: 14 Jul 2009 7:25
Reporter: sujeet singh Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version:5.1.36-community OS:Linux
Assigned to: CPU Architecture:Any
Tags: Problem with LIKE Operator.

[13 Jul 2009 13:28] sujeet singh
Description:
When I fetch the records from the LIKE operator then the query returns no results but when I fetch records from the equal to (=) operator then the query return results.

Following user name are stored in the database:

(1) `~@#$%'\|/?.><^&%@()-_+-:
(2) (){}[]|\/?"*&^%$#@test`

I am searching the records from the LIKE operator as the following ways:

(1) SELECT * FROM user WHERE user LIKE '%`~@#$%''\\|/?.><^&%@()-_+-:%'
(2) SELECT * FROM user WHERE user LIKE '%(){}[]|\\/?"*&^%$#@test`%'

And I am fetching the records from the equal to (=) operator as the following ways:

(1) SELECT * FROM user WHERE user = '`~@#$%''\\|/?.><^&%@()-_+-:'
(2) SELECT * FROM user WHERE user = '(){}[]|\\/?"*&^%$#@test`'

But when I am fetching the records from the LIKE operator then the query returns no results and when I am fetching records from the equal to operator then the query returns results. Both of the SQL query is shown above.

Please suggest how I will solve this problem.

How to repeat:
When I fetch the records from the LIKE operator then the query returns no results but when I fetch records from the equal to (=) operator then the query return results.

Following user name are stored in the database:

(1) `~@#$%'\|/?.><^&%@()-_+-:
(2) (){}[]|\/?"*&^%$#@test`

I am searching the records from the LIKE operator as the following ways:

(1) SELECT * FROM user WHERE user LIKE '%`~@#$%''\\|/?.><^&%@()-_+-:%'
(2) SELECT * FROM user WHERE user LIKE '%(){}[]|\\/?"*&^%$#@test`%'

And I am fetching the records from the equal to (=) operator as the following ways:

(1) SELECT * FROM user WHERE user = '`~@#$%''\\|/?.><^&%@()-_+-:'
(2) SELECT * FROM user WHERE user = '(){}[]|\\/?"*&^%$#@test`'

But when I am fetching the records from the LIKE operator then the query returns no results and when I am fetching records from the equal to operator then the query returns results. Both of the SQL query is shown above.

Please suggest how I will solve this problem.
[13 Jul 2009 14:17] Miguel Solorzano
Thank you for the bug report. Could you please try latest released version 5.1.36 and comment the results here. Thanks in advance.
[14 Jul 2009 7:12] sujeet singh
I have tried the latest released version 5.1.36. But the problem is still there.

Thank You
Sujeet
[14 Jul 2009 7:25] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read note about backslash at http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html:

Note

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. (Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape.)