MySQL Bugs: #49530: Arithmetic error when dealing with BIGINT

Bug #49530	Arithmetic error when dealing with BIGINT
Submitted:	8 Dec 2009 12:16	Modified:	12 Oct 2015 8:29
Reporter:	Zasukhin Vasiliy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.26, 5.0.89, 5.1.41, 5.1.43	OS:	Any (XPSP3, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
Arithmetic operations return incorrect results if the number is specified in hexadecimal, but the result should be greater than 0x10000000000000 and use the function HEX (). If the function HEX () is not used, then the error occurs when the result is more 0x20000000000000
If the number in decimal form, the error does not occur.

How to repeat:
SELECT HEX(0xfffffffffffff+1);
10000000000000 - Correctly

SELECT HEX(0xfffffffffffff+2);
10000000000002 - Wrong

SELECT 0x20000000000000+0;
9007199254740992 - Correctly

SELECT 0x20000000000000+1;
9007199254740992 - Wrong

SELECT 0x20000000000000+2;
9007199254740994 - Correctly

SELECT 0x20000000000000+3;
9007199254740996 - Wrong

Verified with 5.1.43 from bzr on Mac OS X:

77-52-7-73:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT HEX(0xfffffffffffff+1);
+------------------------+
| HEX(0xfffffffffffff+1) |
+------------------------+
| 10000000000000         |
+------------------------+
1 row in set (0.02 sec)

mysql> SELECT HEX(0xfffffffffffff+2);
+------------------------+
| HEX(0xfffffffffffff+2) |
+------------------------+
| 10000000000002         |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT 0x20000000000000+0;
+--------------------+
| 0x20000000000000+0 |
+--------------------+
|   9007199254740992 |
+--------------------+
1 row in set (0.00 sec)

mysql> select  0x20000000000000+1;
+--------------------+
| 0x20000000000000+1 |
+--------------------+
|   9007199254740992 |
+--------------------+
1 row in set (0.00 sec)

mysql> select  0x20000000000000+2;
+--------------------+
| 0x20000000000000+2 |
+--------------------+
|   9007199254740994 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 0xfffffffffffff+2;
+-------------------+
| 0xfffffffffffff+2 |
+-------------------+
|  4503599627370497 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 0xfffffffffffff+1;
+-------------------+
| 0xfffffffffffff+1 |
+-------------------+
|  4503599627370496 |
+-------------------+
1 row in set (0.00 sec)

The problem may be related to this fact:

mysql> select  0x20000000000000+0;
Field   1:  `0x20000000000000+0`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     17
Max_length: 16
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+--------------------+
| 0x20000000000000+0 |
+--------------------+
|   9007199254740992 | 
+--------------------+
1 row in set (0.00 sec)

That is, result is considered to be DOUBLE because hex literal 0x200... is considered a string, not an integer number.

http://bugs.mysql.com/bug.php?id=78641
describes the same problems and will be used for tracking.