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: | |
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
[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;