MySQL Bugs: #2231: string column, INDEX+LIKE, don't take the ESCAPE character

Bug #2231	string column, INDEX+LIKE, don't take the ESCAPE character
Submitted:	28 Dec 2003 23:44	Modified:	8 Jan 2004 23:55
Reporter:	Samuel Leo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.17	OS:	Windows (Windows XP)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
LIKE comparison can use index if pattern string didn't started wild character,
the this optimized function didn't take account of the ESCAPE sub-statement. 

This bug is very nasty, because sometimes I can't do double escape,
drop index is unacceptable too.

How to repeat:
CREATE TABLE test (name varchar(10));
INSERT INTO test VALUES ('a');
INSERT INTO test VALUES ('a\\b');
SELECT * FROM test WHERE name LIKE 'a\\%' ESCAPE '#';
SELECT * FROM test WHERE name LIKE 'a\\\\%';
CREATE INDEX name_key ON test(name);
SELECT * FROM test WHERE name LIKE 'a\\%' ESCAPE '#';
SELECT * FROM test WHERE name LIKE 'a\\\\%';

the 3rd SELECT return nothing, but others return the 'a\\b';

Suggested fix:
NONE

Query produces different result depending on wether it uses index or not;

SELECT * FROM test IGNORE KEY(name_key) WHERE name LIKE 'a\\%' ESCAPE '#';
+------+
| name |
+------+
| a\b  |
+------+
1 row in set (0.04 sec)

# This is wrong result as "a\b" does not satisfy LIKE expression.
  

SELECT * FROM test WHERE name LIKE 'a\\%' ESCAPE '#';
Empty set (0.00 sec)
 
# Empty set is correct result

The empty result is wrong. As a literal string, 'a\\%' represent three character 'a\%', because the '\' isn't escape '%', so it should match any string beginning with 'a\'. Without index it does match the 'a\b', the pattern '%a\\%' return 'a\b' too.
The problem is the index search routine didn't count the ESCAPE statement.

I have examined the issue carefully and I have to agree with you.

Thanks for the bug report.

We shall try to fix it ASAP.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html