Bug #22237 The max value for unsigned bigint is 0.9E19

• Submitted: 11 Sep 2006 13:39
• Modified: 5 Oct 2006 13:34
• Reporter: Kit Kit
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5
• OS: Windows (Windows XP)
• CPU Architecture: Any

[11 Sep 2006 13:39] Kit Kit

Description:
For unsigned bigint column, the maximum value should be 1.8E19. However, when I insert value more than 0.9E (for example, 1.1E19) in Command Prompt, it returns OK. However, when I check the value, it is 0.9E19. 

How to repeat:
table name = test;
column name = ubig;

ALTER TABLE test ADD COLUMN ubig bigint unsigned;
INSERT INTO test(ubig) VALUES(1.1E19);

SELECT * FROM test;

[5 Oct 2006 13:34] Valeriy Kravchuk

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

I was able to repeat the behaviour described with 5.0.24a on Windows XP, but look at the test with latest 5.0.27-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 1
Server version: 5.0.27-debug Source distribution

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

mysql> create table tbin(ubig bigint unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tbin values(1.1E19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tbin;
+----------------------+
| ubig                 |
+----------------------+
| 11000000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> show create table tbin;
+-------+-----------------------------------------------------------------------
---------------------------------+
| Table | Create Table
                                 |
+-------+-----------------------------------------------------------------------
---------------------------------+
| tbin  | CREATE TABLE `tbin` (
  `ubig` bigint(20) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
---------------------------------+
1 row in set (0.00 sec)

mysql> create table tbig(ubig bigint unsigned) engine=InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into tbig values(1.1E19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbin;
+----------------------+
| ubig                 |
+----------------------+
| 11000000000000000000 |
+----------------------+
1 row in set (0.00 sec)

So, now it works as expected for both MyISAM and InnoDB tables.