MySQL Bugs: #80492: Weird results when quoted number is added to a bigint value

Bug #80492	Weird results when quoted number is added to a bigint value
Submitted:	24 Feb 2016 11:37	Modified:	24 Feb 2016 13:47
Reporter:	Roman Shevchenko	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0/5.1/5.5/5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I try to update a bigint value of 18014398509481984 by adding '1' to it, the value doesn't change. 
When I try to update a bigint value of 18014398509481985 by adding '1' to it, the value gets subtracted by one. 

Tried with Mysql 5.7.11-log Windows and 5.5.47-0+deb8u1 through command line client.

How to repeat:
mysql> create table bint(i bigint);
Query OK, 0 rows affected (0.51 sec)

mysql> show create table bint;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| bint  | CREATE TABLE `bint` (
  `i` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> insert into bint values (18014398509481984);
Query OK, 1 row affected (0.03 sec)

mysql> select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.02 sec)

mysql> update bint set i=i+'1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)

mysql> update bint set i=i+1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481985 |
+-------------------+
1 row in set (0.00 sec)

mysql> update bint set i=i+'1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)

Suggested fix:
Expected result would be just addition by 1.

Thank you for the bug report. Same behavior since 5.0:

Your MySQL connection id is 3
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table if exists bint;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 > create table bint(i bigint);
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 > insert into bint values (18014398509481984);
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > update bint set i=i+'1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql 5.0 > select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 Source distribution PULL: 2016-FEB-19

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.7 > use test
Database changed
mysql 5.7 > drop table if exists bint;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > create table bint(i bigint);
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > insert into bint values (18014398509481984);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.7 > update bint set i=i+'1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql 5.7 > select * from bint;
+-------------------+
| i                 |
+-------------------+
| 18014398509481984 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.7 >

This happens because '1' is treated as a float, so the addition is
done to two floats, and then you loose precision.

http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html