Description:
When using a variable to update/insert into a unsigned bigint column, if the variable is larger than 9223372036854775807
mysql> describe foo;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| bar | bigint(20) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> set @bigint1 = 9223372036854775808;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into foo (bar) values (@bigint1);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'bar' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from foo;
+----+------+
| id | bar |
+----+------+
| 1 | 0 |
+----+------+
1 row in set (0.00 sec)
mysql> update foo set bar = @bigint1 where id = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'bar' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from foo;
+----+------+
| id | bar |
+----+------+
| 1 | 0 |
+----+------+
1 row in set (0.00 sec)
--------------
Updating/inserting with the value 9223372036854775808 directly works fine.
How to repeat:
create temporary table foo ( id int auto_increment primary key, bar bigint unsigned);
set @bigint1 = 9223372036854775808;
insert into foo (bar) values (@bigint1);
show warnings;
select * from foo;
update foo set bar = @bigint1 where id = 1;
show warnings;
select * from foo;