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

[16 Aug 2005 10:07] Hakan Küçükyılmaz
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"
[16 Aug 2005 10:37] Hakan Küçükyılmaz
We are using sql_mode="NO_BACKSLASH_ESCAPE"
[16 Aug 2005 13:40] Bugs System
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
[16 Aug 2005 14:52] Andrey Hristov
<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.
[16 Aug 2005 18:55] Bugs System
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
[17 Aug 2005 14:21] Bugs System
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
[18 Aug 2005 2:54] Bugs System
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
[18 Aug 2005 2:57] Jim Winstead
I reverted the original patch because it broke the sql_mode test, as discovered by Monty.
[23 Aug 2005 14:34] Bugs System
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
[28 Aug 2005 16:19] Michael Widenius
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
[9 Sep 2005 22:53] Bugs System
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
[22 Oct 2005 2:16] Bugs System
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
[23 Oct 2005 1:50] Paul Dubois
Noted in 5.0.16 changelog.