Bug #8435 Precision math: BIGINT arithmetic isn't always correct

• Submitted: 11 Feb 2005 3:31
• Modified: 8 Jun 2005 10:18
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[11 Feb 2005 3:31] Trudy Pelzer

Description:
When I work with large BIGINT values (i.e. in excess of
9000000000000000000), arithmetic operations don't 
always produce the correct result.

How to repeat:
mysql> create table t1 (col1 bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(9000000000000000000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(9000000000000000005);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(9000000000000000055);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(9000000000000000555);
Query OK, 1 row affected (0.00 sec)

mysql> select col1/2 from t1;
+---------------------------+
| col1/2                    |
+---------------------------+
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
+---------------------------+
6 rows in set (0.00 sec)
-- The last 3 results are incorrect. The result should be:
+------------------------+
| col1/2                 |
+------------------------+
| 4500000000000000000.00 |
| 4500000000000000002.50 |
| 4500000000000000027.50 |
| 4500000000000000277.50 |
+------------------------+
-- NOTE: If you take the same values and multiply by .5
instead, the correct result is returned. So the problem
seems to be strictly with division.

mysql> update t1 set col1=col1/2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from t1;
+---------------------+
| col1                |
+---------------------+
| 4500000000000000000 |
| 4500000000000000000 |
| 4500000000000000000 |
| 4500000000000000000 |
+---------------------+
6 rows in set (0.00 sec)
-- Same problem here, after the UPDATE. The correct result
should be:
+------------------------+
| col1/2                 |
------------------------+
| 4500000000000000000.00 |
| 4500000000000000002.50 |
| 4500000000000000027.50 |
| 4500000000000000277.50 |
+------------------------+

[11 Feb 2005 3:42] Trudy Pelzer

Sorry: I was in error when I was providing the expected results
of the division operation. The correct results should,of course,
be BIGINT values. For:
mysql> select col1/2 from t1;
+---------------------------+
| col1/2                    |
+---------------------------+
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
| 4500000000000000000.00000 |
+---------------------------+
6 rows in set (0.00 sec)
-- The last 3 results are incorrect. The result should be:
+------------------------+
| col1/2                 |
+------------------------+
| 4500000000000000000 |
| 4500000000000000003 |
| 4500000000000000028 |
| 4500000000000000278 |
+------------------------+

And for:
mysql> select * from t1;
+---------------------+
| col1                |
+---------------------+
| 4500000000000000000 |
| 4500000000000000000 |
| 4500000000000000000 |
| 4500000000000000000 |
+---------------------+
6 rows in set (0.00 sec)
-- Same problem here, after the UPDATE. The correct result
should be:
+------------------------+
| col1/2                 |
------------------------+
| 4500000000000000000 |
| 4500000000000000003 |
| 4500000000000000028 |
| 4500000000000000278 |
+------------------------+