MySQL Bugs: #13864: converting strings to large integers loses precision

Bug #13864	converting strings to large integers loses precision
Submitted:	8 Oct 2005 15:19	Modified:	12 Oct 2005 16:23
Reporter:	Kevin Crowston	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.15-BK, 5.0.15-BK	OS:	Linux (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
The manual notes that "If you are using a string in an arithmetic operation, this is converted to a floating-point number." First, this doesn't work for hexadecimal numbers: "0x1" + 1 is 1. 

My main problems is that the conversion to floating point loses precision for large numbers. I couldn't find a way to convert a string directly to an integer. 

My situation: I am given strings such as "232E3F2C3F3E3FDE" that I want to use as a 64 bit unsigned integer. (If I were writing the queries from scratch, I would of course enter 0x232E3F2C3F3E3FDE but in this case, the strings are already as strings.) 

conv(str,16,10) does correctly convert the hex string to decimal (18383815659218730759), but still as a string. Doing any arithmetic operation on the string (e.g., conv(str,16,10)+0) converts the string to a number but apparently via floating point, or at least with a loss of precision (18383815659218728960). 

How to repeat:
SELECT  
cast(0xFF206EF0BFFEFF07 as unsigned integer),
cast(0xFF206EF0BFFEFF07 + 0.0 as unsigned integer),
conv('FF206EF0BFFEFF07',16,10), 
conv('FF206EF0BFFEFF07',16,10) + 0

+----------------------------------------------+----------------------------------------------------+--------------------------------+------------------------------------+
| cast(0xFF206EF0BFFEFF07 as unsigned integer) | cast(0xFF206EF0BFFEFF07 + 0.0 as unsigned integer) | conv('FF206EF0BFFEFF07',16,10) | conv('FF206EF0BFFEFF07',16,10) + 0 |
+----------------------------------------------+----------------------------------------------------+--------------------------------+------------------------------------+
|                         18383815659218730759 |                               18383815659218730760 | 18383815659218730759           |               18383815659218728960 |
+----------------------------------------------+----------------------------------------------------+--------------------------------+------------------------------------+

These should all be the same, but they're not. 

Suggested fix:
Perhaps a function that takes strings and returns integers? Or converting strings that can be integer to integers?

Verified on 4.1.15-BK (ChangeSet@1.2468.1.1, 2005-10-07 03:12:15+03:00, monty@mysql.com):

mysql> SELECT   cast(0xFF206EF0BFFEFF07 as unsigned integer) c1, cast(0xFF206EF0BFFEFF07 + 0.0 as unsigned integer) c2, conv('FF206EF0BFFEFF07',16,10) c3,  conv('FF206EF0BFFEFF07',16,10) + 0 c4;
+----------------------+----------------------+----------------------+----------------------+
| c1                   | c2                   | c3                   | c4            |
+----------------------+----------------------+----------------------+----------------------+
| 18383815659218730759 | 18383815659218730760 | 18383815659218730759 | 18383815659218728960 |
+----------------------+----------------------+----------------------+----------------------+
1 row in set (0,00 sec)

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

And on 5.0.15-rc-BK (ChangeSet@1.2019.1.1, 2005-10-06 22:09:15+03:00, jani@ua141d10.elisa.omakaista.fi):
mysql> SELECT   cast(0xFF206EF0BFFEFF07 as unsigned integer) c1, cast(0xFF206EF0BFFEFF07 + 0.0 as unsigned integer) c2, conv('FF206EF0BFFEFF07',16,10) c3,  conv('FF206EF0BFFEFF07',16,10) + 0 c4;
+----------------------+----------------------+----------------------+----------------------+
| c1                   | c2                   | c3                   | c4            |
+----------------------+----------------------+----------------------+----------------------+
| 18383815659218730759 | 18383815659218730760 | 18383815659218730759 | 18383815659218731008 |
+----------------------+----------------------+----------------------+----------------------+
1 row in set (0,01 sec)

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

And on 5.0.15 value in column c4 looks like a real bug for me. Changed severity accordingly.

This is a normal problem when using float since it relies on the local support of the operation system. You should switch to using a decimal type, since those are guaranteed to store and display correctly.

What would "switch to using a decimal type" look like in the reported case of having a string representing a 64-bit hexidecimal integer that needs to be converted to an large integer without loss of precision? There doesn't seem to be any way to convert a string representing a number to a data type other than a float.

Apologies for getting into that discussion, but I have been through all that. And it is right, this is not a bug see #9840 .  (you also might want to check #8663 )

besides that I thinx 0x7856 is no more supportet as number. your problem results from casting the result of the addition to int, hence the addition is done in float.

this works

cast( conv( 'ABCDEF1234567', 16,10) as unsigned) + 1

you get a string from conv. you cast it to a bigint, and now you to integer aritmetics.

>this works
>cast( conv( 'ABCDEF1234567', 16,10) as unsigned) + 1

It works because 0xABCDEF1234567 is only 52 bits. If you convert a 64-bit hexadecimal integer it loses precision because the string is apparently first converted to float. See the bug report for details.

I read the other two bugs and understand that integer to float has issues that may be platform dependent. 

I originally submitted this as a feature request: some way to convert a string representing a 64-bit integer to a 64-bit integer without going through float and losing precision. Is there a function that takes a string and returns an integer? conv takes a string and returns a string, as does hex. Strings are automatically converted when used as numbers, but only to float. There doesn't seem to be any way to do this.

cast( x as unsigned ) does the exact thing: in 4.1.14

as long as you do your further calculation to the result of the cast (outside) and not inside the cast. 

select conv('FF206EF0BFFEFF07', 16,10) ;
+---------------------------------+
| conv('FF206EF0BFFEFF07', 16,10) |
+---------------------------------+
| 18383815659218730759            |
+---------------------------------+
1 row in set (0.00 sec)

# next one goes wrong, due to float 
select conv('FF206EF0BFFEFF07', 16,10) + 0 ;
+-------------------------------------+
| conv('FF206EF0BFFEFF07', 16,10) + 0 |
+-------------------------------------+
|                18383815659218726912 |
+-------------------------------------+

#first cast then arithmetic op, works fine
select cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 0;
+--------------------------------------------------------+
| cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 0 |
+--------------------------------------------------------+
|                                   18383815659218730759 |
+--------------------------------------------------------+

# also correct
 select cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1;
+--------------------------------------------------------+
| cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1 |
+--------------------------------------------------------+
|                                   18383815659218730760 |
+--------------------------------------------------------+

The documentation only speaks of number to float, for arithmetic operations, not for explicit cast.

but your example
cast(0xFF206EF0BFFEFF07 + 0.0 as unsigned integer)

does the aritmetic op first, so you get a float, add to the float, and cast the float (which by now is allready inaccurate) to bigint. your cast works fne, but with the wrong input

I get oddly different results:

mysql> select conv('FF206EF0BFFEFF07', 16,10) ;
+---------------------------------+
| conv('FF206EF0BFFEFF07', 16,10) |
+---------------------------------+
| 18383815659218730759            |
+---------------------------------+

mysql> select conv('FF206EF0BFFEFF07', 16,10) + 0 ;
+-------------------------------------+
| conv('FF206EF0BFFEFF07', 16,10) + 0 |
+-------------------------------------+
|                18383815659218728960 |
+-------------------------------------+

mysql>  select cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1;
+--------------------------------------------------------+
| cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1 |
+--------------------------------------------------------+
|                                    9223372036854775808 |
+--------------------------------------------------------+

This is on Mac OS X v10.4.2 with the official binary. 

mysql> show variables like 'version%' ;
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| version                 | 4.1.7-standard-log             |
| version_comment         | Official MySQL-standard binary |
| version_compile_machine | powerpc                        |
| version_compile_os      | apple-darwin7.5.0              |
+-------------------------+--------------------------------+

Okay, I updated to the latest release and it works better for me now:

mysql> show variables like 'version%' ;
+-------------------------+----------------------------------------------+
| Variable_name           | Value                                        |
+-------------------------+----------------------------------------------+
| version                 | 4.1.14-max-log                               |
| version_comment         | MySQL Community Edition - Experimental (GPL) |
| version_compile_machine | powerpc                                      |
| version_compile_os      | apple-darwin6.8                              |
+-------------------------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql>  select cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1;
+--------------------------------------------------------+
| cast( conv('FF206EF0BFFEFF07', 16,10) as unsigned) + 1 |
+--------------------------------------------------------+
|                                   18383815659218730760 |
+--------------------------------------------------------+