MySQL Bugs: #7008: UPPER(MD5()) does not work

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

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.

This is not a bug. The result of MD5() is a binary string. Binary strings have no collation, so UPPER() doesn't affect them.

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.

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.