bug in LIKE matching

By my reading, these two queries ought to return the same
result. As you can see, they do not.

mysql> select job,batch,photo,path from picture where batch like "%\%"
escape "|" limit 30;
+-------+---------------+---------------+------------------- -----------------------------------------------------+
| job | batch | photo | path
|
+-------+---------------+---------------+------------------- -----------------------------------------------------+
| 00120 | \fifth grade | DCP_0321 1 11 |
f:/apache/htdocs/supervisor/hires/00120/\fifth grade/DCP_0321 1 11.jpg |
| 00120 | \\fifth grade | DCP_0321 1 1 |
f:/apache/htdocs/supervisor/hires/00120/\\fifth grade/DCP_0321 1 1.jpg |
+-------+---------------+---------------+------------------- -----------------------------------------------------+
2 rows in set (2.50 sec)

mysql> select job,batch,photo,path from picture where batch like "%\\%"
limit 30;
Empty set (2.49 sec)



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: bug in LIKE matching

Dave,

Dave Dyer wrote:
> By my reading, these two queries ought to return the same
> result. As you can see, they do not.

Nope. You have simply ran into backslashes escaping hell.

> mysql> select job,batch,photo,path from picture where batch like "%\%"
> escape "|" limit 30;
> +-------+---------------+---------------+------------------- -----------------------------------------------------+
>
> | job | batch | photo | path
> |
> +-------+---------------+---------------+------------------- -----------------------------------------------------+
>
> | 00120 | \fifth grade | DCP_0321 1 11 |
> f:/apache/htdocs/supervisor/hires/00120/\fifth grade/DCP_0321 1 11.jpg |
> | 00120 | \\fifth grade | DCP_0321 1 1 |
> f:/apache/htdocs/supervisor/hires/00120/\\fifth grade/DCP_0321 1 1.jpg |
> +-------+---------------+---------------+------------------- -----------------------------------------------------+
>
> 2 rows in set (2.50 sec)
>
> mysql> select job,batch,photo,path from picture where batch like "%\\%"
> limit 30;
> Empty set (2.49 sec)

Which is correct. Not that it is easy to see it ....


Take a look at http://www.mysql.com/doc/en/String_comparison_functions.html

6.3.2.1 String Comparison Functions

....

expr LIKE pat [ESCAPE 'escape-char']

....

Note: Because MySQL uses the C escape syntax in strings (for example, '\n'), you
must double any '\' that you use in your LIKE strings. For example, to search for
'\n', specify it as '\\n'. To search for '\', specify it as '\\\\' (the
backslashes are stripped once by the parser and another time when the pattern
match is done, leaving a single backslash to be matched).




You know the solution - whenever you need to match any string containing \ use
another escape character.

For other comparison operators (=, !=, <, >) using \ is safe. With LIKE better
aboid it as you can run into several troubles.

Should I mention that:

mysql> select "ab\cd", "ab\\cd", "ab\\\cd", "ab\\\\cd";
+------+-------+-------+--------+
| abcd | ab\cd | ab\cd | ab\\cd |
+------+-------+-------+--------+
| abcd | ab\cd | ab\cd | ab\\cd |
+------+-------+-------+--------+

which means in your example:
mysql> select job,batch,photo,path from picture where batch like "%\\%"

is actually sent to server as:

mysql> select job,batch,photo,path from picture where batch like "%\%"


Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org