MySQL Bugs: #2595: bigint update from string failure

Bug #2595	bigint update from string failure
Submitted:	31 Jan 2004 8:48	Modified:	6 Feb 2004 2:52
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.17-standard	OS:	Linux (Linux on Intel)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
If you run the mySQL commands below to create a table with
a bigint and a char(18) or char(19) columns, insert records
where the hexstrings set the high-order-bit and low-order-bit
of a 64-bit number, then update the table by adding zero to
the hexstring, you will get the same values in each of the
hexint columns:

hexstring       hexint
0x8000000000000000      09223372036854775808
0x8000000000000001      09223372036854775808
0x800000000000000f      09223372036854775808

    Only the first hexint is correct, 0x8000000000000001 should
give 09223372036854775809, etc.

How to repeat:
use test;
create table hextest ( hexstring char(18), hexint bigint unsigned zerofill );
insert into hextest values ('0x8000000000000000', 0);
insert into hextest values ('0x8000000000000001', 0);
insert into hextest values ('0x800000000000000f', 0);
update hextest set hexint = hexstring + 0;
select * from hextest;

two issues here.

first: when you convert a string to a number you always get a floating point number in MySQL. It is documented. As bigint has a higher precision than float, last bits are lost:

mysql> select "922337203685477580"+1;
+------------------------+
| "922337203685477580"+1 |
+------------------------+
|    9.2233720368548e+17 |
+------------------------+
1 row in set (0.00 sec)

mysql> select "922337203685477580"+0;
+------------------------+
| "922337203685477580"+0 |
+------------------------+
|    9.2233720368548e+17 |
+------------------------+
1 row in set (0.00 sec)

second problem is with representing hex numbers as a string.
You can do it, but do not expect them to be converted to numbers automatically. It happened out that in Linux or SGI atof("0x80") works, but not in FreeBSD or Solaris or AIX. So, we are replacing it with our atof() implementation which is faster and works identically on all systems. It does NOT support hexadecimal numbers (it was never the intention, and it need not to according to SQL:2003 standard).

Would it be possible to add a MySQL function to perform conversion from hexadecimal to integer values?

You can do it with CONV() function:

CONV("3AB03", 16, 10)

you need to remove "0x" prefix -

CONV(MID("0x0BEC", 3), 16, 10)