Bug #5083 Big integer values are inserted as negative into decimal/string columns
Submitted: 18 Aug 2004 7:36 Modified: 26 May 2006 17:01
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Alexey Botchkov CPU Architecture:Any

[18 Aug 2004 7:36] Sergey Petrunya
I'm entering this bug to record progress on fixing the following issue listed in "Open Bugs and Design Deficiencies in MySQL":

When inserting a big integer value (between 2^63 and 2^64-1) into a decimal/string column, it is inserted as a negative value because the number is evaluated in a signed integer context. We plan to fix this in MySQL 4.1.

How to repeat:
create table t1 (a bigint unsigned, b char(50));
insert into r1 values(11,  11);
insert into r1 values(12345678901234567822,  12345678901234567822);
select * from r1;
[20 Aug 2004 20:34] Sergey Petrunya
ChangeSet@1.1990, 2004-08-20 22:34:42+04:00, sergefp@mysql.com
  Fix for bug#5083 
    Change Field::store(longlong) to Field::store_ll(longlong, bool unsigned_val), and implement special handling for conversion of unsigned to string. 
    There is no special unsigned values handling for anything else yet and _maybe_ this still causes some inconsistencies in how signed/unsigned conversions are handled. 
  Also renamed Field::store(double) to Field::store_double(double) to avoid name collisions.
[22 Aug 2004 0:19] Sergey Petrunya
Will work to provide a constistent bigint unsigned values handling, including conversion to/from different types.
[27 Aug 2004 0:31] Sergey Petrunya
ChangeSet@1.2000, 2004-08-27 02:34:46+04:00, sergefp@mysql.com
[1 Sep 2004 15:36] Sergey Petrunya
will be fixed in 5.0
[12 Jan 2005 22:08] Sergey Petrunya
See also BUG#5913, BUG#7670
[21 May 2005 9:12] Sergey Petrunya
Delaying the fix as we currently don't have a set of well-defined rules about how and when are BIGINTs/strings/decimal numbers are converted.
(btw this probem is documented in "Open bugs and design deficiencies" section of user manual).
[14 May 2006 21:43] Hartmut Holzgraefe
This bu seems to be fixed in current 5.0?
[26 May 2006 17:01] MySQL Verification Team
This was already fixed:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table r1 (a bigint unsigned, b char(50));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into r1 values(11,  11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into r1 values(12345678901234567822,  12345678901234567822);
Query OK, 1 row affected (0.00 sec)

mysql> select * from r1;
| a                    | b                    |
|                   11 | 11                   | 
| 12345678901234567822 | 12345678901234567822 | 
2 rows in set (0.00 sec)