Bug #25284 select CAST(UNHEX('7C40') AS UNSIGNED); fails. Apparently related to #13016.

• Submitted: 26 Dec 2006 14:41
• Modified: 26 Dec 2006 17:05
• Reporter: Gonzalo Javier Larralde
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.26-community
• OS: Windows (Windows XP)
• CPU Architecture: Any
• Tags: data convertion, data types, hex, INTEGER, UNHEX

[26 Dec 2006 14:41] Gonzalo Javier Larralde

Description:
It's impossible to convert a string to an unsinged number using UNHEX, from a stirng variable, or database result. It's apparently related to bug #13016 [http://bugs.mysql.com/bug.php?id=13016] reported by Jason Garber.

ACTUAL RESULT:
--------------

mysql> select CAST(UNHEX('7C40') AS UNSIGNED);
+---------------------------------+
| CAST(UNHEX('7C40') AS UNSIGNED) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '|@' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.26-community-nt |
+---------------------+
1 row in set (0.00 sec)

EXPECTED RESULT:
----------------
mysql> select CAST(UNHEX('7C40') AS UNSIGNED);
+---------------------------------+
| CAST(UNHEX('7C40') AS UNSIGNED) |
+---------------------------------+
|                           29888 |
+---------------------------------+
1 row in set, 0 warning (0.00 sec)

How to repeat:
mysql> select CAST(UNHEX('7C40') AS UNSIGNED);
+---------------------------------+
| CAST(UNHEX('7C40') AS UNSIGNED) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '|@' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

[26 Dec 2006 16:22] Gonzalo Javier Larralde

Workaround:

CREATE FUNCTION hex2dec (var CHAR(13)) RETURNS BIGINT UNSIGNED
BEGIN

   SET @strVar = CAST(UNHEX(var) as CHAR(13));

   SET @i = 1, @res = 0;
   WHILE @i <= LENGTH(@strVar) DO
   
      SET @res = @res + ORD( SUBSTRING(@strVar, LENGTH(@strVar) - @i + 1) ) * POW(2, (@i - 1) * 8) ;

      SET @i = @i + 1;
   END WHILE;

   RETURN @res;

END;

[26 Dec 2006 16:38] Valeriy Kravchuk

Thank you for a problem report. I'vegot the same result as you with 5.0.34-BK. But I do not think it is a bug. Look, UNHEX('7C40') gives '|@' as a result. How that can be converted to integer? This string does not represent any number in any notation. This is different from the bug(s) you refered to.

[26 Dec 2006 17:05] Gonzalo Javier Larralde

Hi Valeriy,

thanks for your response.

You're right, UNHEX returns an String instead of a Number, so I have to redefine what I consider a bug.

UNHEX should returns a HEX value instead of a String value.

Look that:

SELECT UNHEX('7C40') + 1; --returns 1
SELECT x'7C40' + 1; --returns 31809

If UNHEX returns an String you have not an easy way to convert a string which contains an Hexa number, to a Signed/Unsigned. In the other hand, if you returns a native HEX data type, you can use it as a String, like that:

SELECT x'68656C6C6F20776F726C642C207468697320697320612074657374206F662068657820636F6E76657274696F6E';

I'm not sure if you can understand what I wrote because my english sucks, but I will try to get some help to translate it.

Thanks, and happy new year,

Gonzalo.