MySQL Bugs: #1208: bigint precision problem

Bug #1208	bigint precision problem
Submitted:	5 Sep 2003 19:58	Modified:	13 Aug 2004 15:49
Reporter:	Kenneth Hausam	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	3.23.53	OS:	Linux (redhat 7.2)
Assigned to:		CPU Architecture:	Any

Description:
Not sure if this has been fixed in a newer version. Couldn't find reference to fix anywhere in bug system. Using MyISAM table. SQL is as follows:

mysql> select SID_HIGH from SID_COUNT where SID_HIGH = 13440289507735259807;
+----------------------+
| SID_HIGH             |
+----------------------+
| 13440289507735259807 |
| 13440289507735259136 |
+----------------------+
2 rows in set (28.42 sec)

Table structure is as follows:

mysql> describe SID_COUNT;
+--------------+---------------------+------+-----+---------------------+-------+
| Field        | Type                | Null | Key | Default             | Extra |
+--------------+---------------------+------+-----+---------------------+-------+
| SID_LOW      | bigint(20) unsigned |      | PRI | 0                   |       |
| SID_HIGH     | bigint(20) unsigned |      | PRI | 0                   |       |
| DATETIME     | datetime            |      |     | 0000-00-00 00:00:00 |       |
| PREFIX       | char(1)             |      |     |                     |       |
| COMPANY_CODE | char(3)             | YES  |     | NULL                |       |
| ZIP          | char(5)             | YES  |     | NULL                |       |
| CREATE_DATE  | datetime            | YES  |     | NULL                |       |
+--------------+---------------------+------+-----+---------------------+-------+
7 rows in set (0.00 sec)

My understanding was that unsigned bigint was a 64-bit number so maximum value would be 18446744073709551616. So my value in query should be OK.

Also having similar (possibly related?) issues inserting bigints into table where last few digits are altered when they should be the same. Using MySQL/J JDBC driver for inserts.

How to repeat:
I can provide more help in repeating if needed. Hoping that this a known bug in the 3.23.x versions and that you can point me to documentation that talks about problem and how it was fixed in later versions.

Ok, this seems to be a bug only in the 3.23 tree:

--- 8< ---

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

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

mysql> create table SID_COUNT (SID_LOW bigint(20) unsigned not null, SID_HIGH bi
gint(20) unsigned , tstamp timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into SID_COUNT values (13440289507735259807,13440289507735259807,n
ull);  
Query OK, 1 row affected (0.00 sec)

mysql> insert into SID_COUNT values (13440289507735259136,13440289507735259136,n
ull);  
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM SID_COUNT WHERE SID_HIGH=13440289507735259807;
+----------------------+----------------------+----------------+
| SID_LOW              | SID_HIGH             | tstamp         |
+----------------------+----------------------+----------------+
| 13440289507735259136 | 13440289507735259136 | 20030906144939 |
| 13440289507735259136 | 13440289507735259136 | 20030906144945 |
+----------------------+----------------------+----------------+
2 rows in set (0.04 sec)

(looks like 13440289507735259136 is the maximum value for 3.23)

--- 8< ---

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

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

mysql> drop table SID_COUNT;
Query OK, 0 rows affected (0.00 sec)

mysql> create table SID_COUNT (SID_LOW bigint(20) unsigned not null, SID_HIGH bigint(20) 
unsigned , tstamp timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into SID_COUNT values (13440289507735259807,13440289507735259807,null);  
Query OK, 1 row affected (0.00 sec)

mysql> insert into SID_COUNT values (13440289507735259136,13440289507735259136,null);  
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM SID_COUNT WHERE SID_HIGH=13440289507735259807;             
+----------------------+----------------------+----------------+
| SID_LOW              | SID_HIGH             | tstamp         |
+----------------------+----------------------+----------------+
| 13440289507735259807 | 13440289507735259807 | 20030906145102 |
+----------------------+----------------------+----------------+
1 row in set (0.00 sec)

mysql> select * FROM SID_COUNT;
+----------------------+----------------------+----------------+
| SID_LOW              | SID_HIGH             | tstamp         |
+----------------------+----------------------+----------------+
| 13440289507735259807 | 13440289507735259807 | 20030906145102 |
| 13440289507735259136 | 13440289507735259136 | 20030906145104 |
+----------------------+----------------------+----------------+
2 rows in set (0.00 sec)

--- 8< ---

We have generally stopped the development on the 3.23 tree and only critical
bugfixes will be applied - this will be left to decide by Monty, our CTO, whether
the current one qualifies as such.

But you should consider upgrading to the current stable, 4.0 tree.

This is a known issue in 3.23.

In 3.23 we used floating point for bigint calculus.

We switched to 64-bit integer calculus in 4.0, which will not be back-ported to 3.23.