Bug #74535 Expression Evaluation 9223372036854775807 + 1
Submitted: 24 Oct 2014 3:36 Modified: 27 Oct 2014 12:07
Reporter: Hongyu Qiao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:MySQL-5.6.20 OS:Linux (Ubuntu 12.04)
Assigned to: CPU Architecture:Any
Tags: 9223372036854775807;can't get the result

[24 Oct 2014 3:36] Hongyu Qiao
Description:
I can't get the result of the expression if there is the bigint 9223372036854775807.

mysql> select 9223372036854775807+1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
mysql> select 9223372036854775806+1;
+-----------------------+
| 9223372036854775806+1 |
+-----------------------+
|   9223372036854775807 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select 9223372036854775808+1;
+-----------------------+
| 9223372036854775808+1 |
+-----------------------+
|   9223372036854775809 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select 9223372036854775807*10;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 * 10)'

How to repeat:
mysql> select 9223372036854775807*10;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 * 10)'

Suggested fix:
deal with this number.
[24 Oct 2014 13:10] MySQL Verification Team
Hello!

Thank you for the report.

Thanks,
Umesh
[24 Oct 2014 13:12] MySQL Verification Team
It is assuming signed unless the value is too big, the one ending in 7.
To enable the operation to succeed in this case, convert the value to unsigned:

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select 9223372036854775807+1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
mysql>
mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select 9223372036854775808+1;
+-----------------------+
| 9223372036854775808+1 |
+-----------------------+
|   9223372036854775809 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select 9223372036854775809+1;
+-----------------------+
| 9223372036854775809+1 |
+-----------------------+
|   9223372036854775810 |
+-----------------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW V1 AS SELECT 9223372036854775807 AS A, 9223372036854775807+1 AS B, 9223372036854775808 AS C, 9223372036854775808+1 AS D;
Query OK, 0 rows affected (0.00 sec)

mysql> desc V1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| A     | bigint(19)          | NO   |     | 0       |       |
| B     | bigint(21)          | NO   |     | 0       |       |
| C     | bigint(19) unsigned | NO   |     | 0       |       |
| D     | bigint(20) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from V1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
[27 Oct 2014 12:07] Roy Lyseng
This is not a bug. When parsing integer constants, MySQL determines the correct data type for each, and also uses this data type for type propagation in expressions.

Example:

CREATE TABLE t1 AS
SELECT 9223372036854775807 as sbi1,
       9223372036854775808 as ubi1,
       18446744073709551615 as ubi2,
       18446744073709551616 as dec1;

SHOW CREATE TABLE t1;

CREATE TABLE `t1` (
  `sbi1` bigint(19) NOT NULL DEFAULT '0',
  `ubi1` bigint(19) unsigned NOT NULL DEFAULT '0',
  `ubi2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `dec1` decimal(20,0) NOT NULL DEFAULT '0'
)

These two statements will thus cause an error:

SELECT sbi1+1 FROM t1;
SELECT ubi2+1 FROM t1;

The first statement  will overflow the SIGNED BIGINT data type.
The second statement will overflow the UNSIGNED BIGINT data type.

The workaround is simple: Cast the expression to a DECIMAL type that can accommodate the values, e.g:

SELECT CAST(sbi1 AS DECIMAL(20)) +1 FROM t1;
SELECT CAST(ubi2 AS DECIMAL(20)) +1 FROM t1;