Bug #38050 Capitalization Problem With HEX / UNHEX functions

• Submitted: 11 Jul 2008 15:39
• Modified: 11 Jul 2008 18:36
• Reporter: Shlomo Priymak
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[11 Jul 2008 15:39] Shlomo Priymak

Description:
The difference between md5('a') and hex(unhex(md5('a'))) is only capitalization.
While the md5() function returns the hex in lowercase, the unhex,hex returns the result in uppercase. But in logical terms, hex(unhex(md5('a'))) and md5('a') should be equal.

Is this a bug, or am I missing something?

(I've posted a question at the forum but got no replies:
http://forums.mysql.com/read.php?20,207234,207234#msg-207234 )

How to repeat:
Try the folllowing:
select md5('a');

md5('a')
--------------------------------
0cc175b9c0f1b6a831c399e269772661

select hex(unhex(md5('a')));

hex(unhex(md5('a')))
--------------------------------
0CC175B9C0F1B6A831C399E269772661

select 1 as is_true
from dual
where md5('a')=hex(unhex(md5('a')));

is_true
-------

[11 Jul 2008 18:36] Sveta Smirnova

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

Result of HEX function is hexadecimal representation of its argument. There is no difference of case for hexademical values. So function HEX can return result in upper case. Unfrotunately there is no workaround better than use UPPER or LOWER function when comparing md5 and result of the expression provided.