LIKE fails when ESCAPE character is "_" or "%"

From: tdefusco@metatomix.com
To: bugs@lists.mysql.com
Subject: LIKE fails when ESCAPE character is '_' or '%'

Description:
If the ESCAPE character is set for the LIKE operator, and the ESCAPE =
character happens to be one of LIKE's pattern matching characters '%' or =
'_', non-conforming results are returned.


How-To-Repeat:
Create the following table and data:
CREATE TABLE TEST (VAL VARCHAR(255));
INSERT INTO TEST VALUES('PURITY is 100%');
INSERT INTO TEST VALUES('100_DOLLARS');
INSERT INTO TEST VALUES('1001DOLLARS');

For the following query, other databases only return the string "PURITY =
is 100%", but MySQL instead returns all three records:
SELECT * FROM TEST WHERE TEST.VAL LIKE '%100%%' ESCAPE '%'

For the following query, other databases only return the string =
"100_DOLLARS", but MySQL instead returns no records:
SELECT * FROM TEST WHERE TEST.VAL LIKE '100__DOLLARS' ESCAPE '_'

For the following query, other databases only return the string =
"100_DOLLARS", but MySQL instead returns both "100_DOLLARS" and =
"1001DOLLARS":
SELECT * FROM TEST WHERE TEST.VAL LIKE '100__%' ESCAPE '_'


Fix:
The workaround is not to use any of the LIKE pattern match characters as =
the ESCAPE character when performing a LIKE operation in SQL.


Synopsis: Non-conforming results from LIKE operator when ESCAPE =
character is '_' or '%'
Submitter-Id: tdefusco@metatomix.com
Originator: Tony DeFusco
Organization: Metatomix, Inc.
MySQL support: none
Severity: serious
Priority: high
Category: mysqld
Class: sw-bug
Release: mysql-3.23.38

Executable: mysqld-max-nt
Environment: Pentium III 256MB
System: Win2000 Service Pack 2
Compiler: VC++ 6.0
Architecture: i


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12508@lists.mysql.com
To unsubscribe, e-mail

LIKE fails when ESCAPE character is "_" or "%"

Hi!

>>>>> "Tony" == Tony DeFusco writes:

Tony> From: tdefusco@metatomix.com
Tony> To: bugs@lists.mysql.com
Tony> Subject: LIKE fails when ESCAPE character is '_' or '%'

Tony> Description:
Tony> If the ESCAPE character is set for the LIKE operator, and the ESCAPE character happens to be one of LIKE's pattern matching characters '%' or '_', non-conforming results are returned.

Tony> How-To-Repeat:
Tony> Create the following table and data:
Tony> CREATE TABLE TEST (VAL VARCHAR(255));
Tony> INSERT INTO TEST VALUES('PURITY is 100%');
Tony> INSERT INTO TEST VALUES('100_DOLLARS');
Tony> INSERT INTO TEST VALUES('1001DOLLARS');

Tony> For the following query, other databases only return the string "PURITY is 100%", but MySQL instead returns all three records:
Tony> SELECT * FROM TEST WHERE TEST.VAL LIKE '%100%%' ESCAPE '%'



Thanks for the bug report. Unfortunately this isn't that trivial to
fix without risking getting unwanted side effects :(
I have now documented this as a 'known bug'; We will try to fix this
in 4.1.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12529@lists.mysql.com
To unsubscribe, e-mail