Bug #26374 insert/update of unsigned bigint column fails when using variable larger than
Submitted: 14 Feb 2007 17:17 Modified: 5 Dec 2007 18:55
Reporter: Patrick Lin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0 BK. 5.1 BK OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R2 (Low) / E3 (Medium)

[14 Feb 2007 17:17] Patrick Lin
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;
[15 Feb 2007 11:37] Sveta Smirnova
Thank you for the report.

Problem with not inserting data grater than 9223372036854775807 is expected behaviour. See also http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Problem is behaviour is different when one inserts such data into temporary table and MyISAM table. See attached test.
[15 Feb 2007 11:38] Sveta Smirnova
test case

Attachment: bug26288.test (application/octet-stream, text), 650 bytes.