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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.17-standard OS:Linux (Linux on Intel)
Assigned to: Sergei Golubchik CPU Architecture:Any

[31 Jan 2004 8:48] [ name withheld ]
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;
[4 Feb 2004 1:29] Sergei Golubchik
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).
[4 Feb 2004 13:50] [ name withheld ]
Would it be possible to add a MySQL function to perform conversion from hexadecimal to integer values?
[6 Feb 2004 2:52] Sergei Golubchik
You can do it with CONV() function:

CONV("3AB03", 16, 10)

you need to remove "0x" prefix -

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