MySQL Bugs: #6257: UPPER(MD5('xxx'))

Bug #6257	UPPER(MD5('xxx'))
Submitted:	26 Oct 2004 4:35	Modified:	30 Oct 2004 12:58
Reporter:	Nathan Modrzewski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.6-gamma	OS:	Linux (SuSE Linux 8.1 (2.4.21))
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Performing an "UPPER" on the result of MD5 will not upper case the string.

SELECT UPPER(MD5('hello world'))

will return "5eb63bbbe01eeed093cb22bb8f5acdc3"  (without the double quotes)

In MySQL 4.0.21 the above SQL statement will return "5EB63BBBE01EEED093CB22BB8F5ACDC3"

If I perform the SQL statement:

SELECT UPPER('hello world')

It will return "HELLO WORLD"

How to repeat:
SELECT UPPER(MD5('hello world'))

Suggested fix:
Perform the UPPER function on the result string from the function MD5, don't know if the problem is due to character sets, or if it is an optimization bug.

also aplies to 
select lower(hex('m'));

which returns 6D

seems that upper/lower doesnt work on the string representation of hex numbers.

btw 
select lower(concat(hex('m'),''))
does work

It's not a bug. MD5() returns a binary string:

mysql> select charset(md5('m'));
+-------------------+
| charset(md5('m')) |
+-------------------+
| binary            |
+-------------------+

Binary strings have no collation, so UPPER() and LOWER()
do not apply. However, if you convert the string to a
non-binary string with a non-binary character set, those
functions will work:

mysql> select upper(convert(md5('m') using latin1));
+---------------------------------------+
| upper(convert(md5('m') using latin1)) |
+---------------------------------------+
| 6F8F57715090DA2632453988D9A1501B      |
+---------------------------------------+

See: http://dev.mysql.com/doc/mysql/en/Cast_Functions.html

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It's not a bug. Trying

SELECT CHARSET(MD5("aaa"))

you'll see that charset it binary. For binary charset UPPER and LOWER does not work - binary charset has no concept of "letter" - it's just a sequence of bytes. Cast it to whatever charset you need and then convert to upper or lower case.