MySQL Bugs: #12595: Escape character has to be exactly one

Bug #12595	Escape character has to be exactly one
Submitted:	16 Aug 2005 10:07	Modified:	23 Oct 2005 1:50
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.11	OS:	Linux (Linux)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
According to ISO/IEC 9075-2:2003 Part 2 Chapter 8.5 General Rules: 3bI1 the lenght of a given escape character should be exactly one. However, MySQL accepts an escape value with more than one character.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a char(4));

5.0.12-beta-debug-log
[12:03] root@test>SELECT * FROM t1 WHERE a LIKE '*%' ESCAPE '**';
Empty set (0.02 sec)

Suggested fix:
SELECT * FROM t1 WHERE a LIKE '*%' ESCAPE '**' should give an error messages like "invalid escape character"

We are using sql_mode="NO_BACKSLASH_ESCAPE"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28337

<georg> anyone able to review a short fix for bug 12595? It's important fix for SAP
2005-08-16 16:34:51
	
 <ingo> georg, it looks good except of the new empty line with trailing spaces.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28347

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28399

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28431

I reverted the original patch because it broke the sql_mode test, as discovered by Monty.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28684

Can't approve of the patch because of the following reasons:
- MySQL should allow an empty string as the ESCAPE character, which is the same as having no escape character. (I have used this in myself in some applications, so changing this would be a portability problem)
- The above means that the new class variable 'escape_used_in_parsing' is not needed, which basicly invalidates the logic of this patch.
- If SAP has a problem with us supporting an ESCAPE character of zero bytes (not indicated by the bug report), we should create a now mode for them.

While checking this code, I found a bug in the current implementation.  If we have an empty string for ESCAPE, the 'escape' variable in Item_func_like should not be set to 0 but to -1, to ensure we don't get any matches in my_wildcmp() for binary strings

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29590

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31333

Noted in 5.0.16 changelog.