MySQL Bugs: #13016: CAST(0xB8 AS UNSINGED INTEGER) fails due to bug #7036 fix

Bug #13016	CAST(0xB8 AS UNSINGED INTEGER) fails due to bug #7036 fix
Submitted:	6 Sep 2005 16:20	Modified:	24 Sep 2005 0:45
Reporter:	Jason Garber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14/5.0 BK	OS:	Linux (Redhat ES 3)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
From Bug #7036:

>Now this function and CAST(string_argument AS SIGNED) also 
>produces warnings for wrong string arguments. (Bug #7036)

The new change violates the documentation on hexadecimal values here:

  http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html

which states:

>In MySQL 4.1 (and in MySQL 4.0 when using the --new option), the default
>type of a hexadecimal value is a string. If you want to ensure that the 
>value is treated as a number, you can use CAST(... AS UNSIGNED):
>
>mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
>       -> 'A', 65     

HOWEVER, when you run this example on 4.1.14, it FAILES, producing this result:

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
+------+------------------------+
| 0x41 | CAST(0x41 AS UNSIGNED) |
+------+------------------------+
| A    |                      0 |
+------+------------------------+
1 row in set, 1 warning (0.00 sec)

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

I believe that this is a very significant bug - it just broke a large part of our application, and corrupted significant amounts of data.

How to repeat:
mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
+------+------------------------+
| 0x41 | CAST(0x41 AS UNSIGNED) |
+------+------------------------+
| A    |                      0 |
+------+------------------------+
1 row in set, 1 warning (0.00 sec)

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

Suggested fix:
I suggest that the functionality is restored as specified in the documentation.  If the currently functionality is correct, then what is a good workaround for converting hex into unsigned integer?

Thanks.

Verified on the latest 4.1.15-BK build also:

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
+------+------------------------+
| 0x41 | CAST(0x41 AS UNSIGNED) |
+------+------------------------+
| A    |                      0 |
+------+------------------------+
1 row in set, 1 warning (0,01 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'A' |
+---------+------+----------------------------------------+
1 row in set (0,00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.15-debug |
+--------------+
1 row in set (0,00 sec)

This test fails on 4.1.9, but passes on 4.1.14.  I'm not sure if it's still an issue or not.  Essentially, it allows the hex syntax to be used to insert a bigint, but then will not allow the hex syntax to be used to select the same value.  I believe it has something to do with being out of the signed range with the second number.

See test script below.

create table x98 (x bigint unsigned not null);

--These two are the SAME value
insert into x98 values (3986218494981934061);
insert into x98 values (0x3751E499EE7D8BED);

--These two are the SAME value
insert into x98 values (11256250144744245389);
insert into x98 values (0x9C363BDE546B888D);

select * from x98;
-- +----------------------+
-- | x                    |
-- +----------------------+
-- |  3986218494981934061 |
-- |  3986218494981934061 |
-- | 11256250144744245389 |
-- | 11256250144744245389 |
-- +----------------------+
-- 4 rows in set (0.00 sec)

select * from x98 where x = 3986218494981934061;
-- +---------------------+
-- | x                   |
-- +---------------------+
-- | 3986218494981934061 |
-- | 3986218494981934061 |
-- +---------------------+
-- 2 rows in set (0.00 sec)

select * from x98 where x = 0x3751E499EE7D8BED ;
-- +---------------------+
-- | x                   |
-- +---------------------+
-- | 3986218494981934061 |
-- | 3986218494981934061 |
-- +---------------------+
-- 2 rows in set (0.01 sec)

select * from x98 where x = 11256250144744245389;
-- +----------------------+
-- | x                    |
-- +----------------------+
-- | 11256250144744245389 |
-- | 11256250144744245389 |
-- +----------------------+
-- 2 rows in set (0.00 sec)

select * from x98 where x = 0x9C363BDE546B888D ;
-- Empty set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30079

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix for bug #7036 also fixes this one. Fix will be in 4.1.15 and 5.0.14