Bug #7008 UPPER(MD5()) does not work
Submitted: 4 Dec 2004 16:43 Modified: 5 Dec 2004 0:11
Reporter: Stefan Arentz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1.7-Standard OS:Linux (Debian Sarge)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[4 Dec 2004 16:43] Stefan Arentz
Description:
The UPPER function does not work when it gets the output from the MD5() function. I discovered 
this because my MD5 hashes were suddenly 
incompatible.

mysql> select UPPER(MD5('this does not work'));
+----------------------------------+
| UPPER(MD5('this does not work')) |
+----------------------------------+
| 32118a1070836a152fc8bed5e9eb68e8 |
+----------------------------------+
1 row in set (0.00 sec)

This is with the mysql-standard-4.1.7-pc-linux-i686 download from mysql.com

This is different behaviour than in 4.0.x.

 S.

How to repeat:
To repeat this bug:

select UPPER(MD5('this does not work'));

And look if the output is upper or lowecase.
[4 Dec 2004 19:19] Victoria Reznichenko
This is not a bug. The result of MD5() is a binary string. Binary strings have no collation, so UPPER() doesn't affect them.
[4 Dec 2004 19:36] Stefan Arentz
I understand the behaviour if the result of MD5 is a binary string. However, the documentation 
says

"Calculates an MD5 128-bit checksum for the string. The value is returned as a *string of 32 hex 
digits*, or NULL if the argument was NULL. The return value can, for example, be used as a hash 
key."

And this is valid for MySQL 4.0 and 3.23. So this sudden change in behaviour in 4.1 will break 
applications that depend on the previous familiar workings of MD5(). If there is no intend to keep 
that in sync then the documentation should at least give a hint about this.
[5 Dec 2004 0:11] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I noted that MD5() returns a binary string and
added a cross-reference to the BINARY
operator in http://dev.mysql.com/doc/mysql/en/Cast_Functions.html
that describes case conversion of binary strings.