| Bug #37084 | CONV() function does not convert to and from higher bases properly | ||
|---|---|---|---|
| Submitted: | 29 May 2008 22:14 | Modified: | 31 May 2008 18:21 |
| Reporter: | Tim Soderstrom | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.1.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | base, conv, FUNCTION, hex | ||
[31 May 2008 18:21]
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 098f6bcd4621d373cade4e832627b4f6 is greater than FFFFFFFFFFFFFFFF which is maximum allowed value for unsigned integer. So it is just trancated value, not conversion issue. With smaller values conversion works fine: mysql> select conv(18446744073709551614,10,16); +----------------------------------+ | conv(18446744073709551614,10,16) | +----------------------------------+ | FFFFFFFFFFFFFFFE | +----------------------------------+ 1 row in set (0.00 sec) mysql> select conv('FFFFFFFFFFFFFFFE',16,10); +--------------------------------+ | conv('FFFFFFFFFFFFFFFE',16,10) | +--------------------------------+ | 18446744073709551614 | +--------------------------------+ 1 row in set (0.00 sec)
Description: The CONV() function appears to be improperly converting to and from higher based properly. Specifically, converting from, say, hexidecimal to base 24 and back does not produce the same result. The original idea was to use fewer characters to store, in this case, an MD5. A better solution has been found, however, there may be cases where a work-around does not work. Either way, CONV is not properly doing what is was designed to do. How to repeat: mysql> SELECT MD5('test'); +----------------------------------+ | MD5('test') | +----------------------------------+ | 098f6bcd4621d373cade4e832627b4f6 | +----------------------------------+ 1 row in set (0.00 sec) mysql> CONV('098f6bcd4621d373cade4e832627b4f6', 16, 24); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONV('098f6bcd4621d373cade4e832627b4f6', 16, 24)' at line 1 mysql> SELECT CONV('098f6bcd4621d373cade4e832627b4f6', 16, 24); +--------------------------------------------------+ | CONV('098f6bcd4621d373cade4e832627b4f6', 16, 24) | +--------------------------------------------------+ | L12EE5FN0JI1IF | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CONV('L12EE5FN0JI1IF', 24, 16); +--------------------------------+ | CONV('L12EE5FN0JI1IF', 24, 16) | +--------------------------------+ | FFFFFFFFFFFFFFFF | +--------------------------------+ 1 row in set (0.00 sec) Alternatively, you can do it all in one go: mysql> SELECT CONV(CONV(MD5('Test'), 16, 24), 24, 16); +-----------------------------------------+ | CONV(CONV(MD5('Test'), 16, 24), 24, 16) | +-----------------------------------------+ | FFFFFFFFFFFFFFFF | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> Suggested fix: CONV should work in both directions, so that converting a HEX value to a higher ordered base and back produces the original HEX value.