# Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior?

``````mysql> select (1/3 + 1/3 + 1/3);
+-------------------+
| (1/3 + 1/3 + 1/3) |
+-------------------+
|            1.0000 |
+-------------------+
1 row in set (0.00 sec)

mysql> select (1/3);
+--------+
| (1/3)  |
+--------+
| 0.3333 |
+--------+
1 row in set (0.00 sec)

mysql> select(0.3333 + 0.3333 + 0.3333);
+----------------------------+
| (0.3333 + 0.3333 + 0.3333) |
+----------------------------+
|                     0.9999 |
+----------------------------+
1 row in set (0.00 sec)
``````

Why does the first sum of the fractions is summed up to 1 while when summing the decimal equivalent we get 0.9999?

• @ypercube:But why in the last sum it is not rounded up?
– Jim
Sep 7, 2014 at 21:33
• Why should be rounded? `0.3+0.3+0.3` makes `0.9`. Why would `0.3333 + 0.3333 + 0.3333` make `1.0000` and not `0.9999`? Sep 7, 2014 at 21:36
• Well, `1/3` is not `0.3333`. That's what my naive math says. Read the link I posted above (there are also several other web sites with info about float point arithmetic.) The main issue is that all floating point numbers (1.0, 0.33333, whatever) are stored in binary arithmetic. The result you see (1.0 or 0.333 or 0.9999) is the result of converting the internal (binary) representation into decimal. Sep 7, 2014 at 21:41
• Look up the floating point representations and do the arithmetic yourself. All floating point numbers are approximate, with the exception of small integers. You have no control over the internal rounding, only over the external rounding for display purposes. Sep 7, 2014 at 23:59

Peter is correct. Here is why:

Surprisingly, you can control the precision for division.

If you try setting div_precision_increment to the max value of 30, mysqld still does the rounding

Here is a demonstration from MySQL 5.5.37 on my laptop (Windows 7)

``````mysql> set global div_precision_increment = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> set div_precision_increment = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> select 1/3 + 1/3 + 1/3;
+----------------------------------+
| 1/3 + 1/3 + 1/3                  |
+----------------------------------+
| 1.000000000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select 1/3;
+----------------------------------+
| 1/3                              |
+----------------------------------+
| 0.333333333333333333333333333333 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select 0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333;
+--------------------------------------------------------------------------------------------------------+
| 0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333 |
+--------------------------------------------------------------------------------------------------------+
|                                                                       0.999999999999999999999999999999 |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
``````

You mentioned in your comment something about the precision being 4 digits. That's because 4 is the default value for div_precision_increment.

This is why the FORMAT() function was made.

Let me print the answer to that sum using `FORMAT(...,29)` and `FORMAT(...,30)`:

``````mysql> select FORMAT(0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333,29);
+-------------------------------------------------------------------------------------------------------------------+
| FORMAT(0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333,29) |
+-------------------------------------------------------------------------------------------------------------------+
| 1.00000000000000000000000000000                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select FORMAT(0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333,30);
+-------------------------------------------------------------------------------------------------------------------+
| FORMAT(0.333333333333333333333333333333 + 0.333333333333333333333333333333 + 0.333333333333333333333333333333,30) |
+-------------------------------------------------------------------------------------------------------------------+
| 0.999999999999999999999999999999                                                                                  |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
``````

The internal rounding becomes your responsibility when you use FORMAT().

# Give it a Try !!!

CAVEAT : The FORMAT() function will inject commas into the whole number part if it is >= 1000. You can simply use `REPLACE(FORMAT(...,..),',','')` to strip commas and only have digits and one decimal point.

# UPDATE 2014-09-14 21:16 EDT

You just said in your comment

+1.Very useful answer. But my core question is why doesn't the select 1/3 + 1/3 + 1/3; give 0.9999? The 1/3 is 0.3333 according to the second select

As I already explained, div_precision_increment is set to 4 and mysqld is controlling the rounding.

To see this for a fact, let's use two more math formulas

1. `select 1/7 + 2/7 + 4/7 sum1;`
2. `select 1/7 + 2/7 + 2/7 sum2;`

Let's look at the answers with the default div_precision_increment of 4:

``````mysql> show variables like 'div_precision_increment';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| div_precision_increment | 4     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select 1/7 + 2/7 + 4/7 sum1;
+--------+
| sum1   |
+--------+
| 1.0000 |
+--------+
1 row in set (0.00 sec)

mysql> select 1/7 + 2/7 + 2/7 sum2;
+--------+
| sum2   |
+--------+
| 0.7143 |
+--------+
1 row in set (0.00 sec)

mysql>
``````

Since `sum2` is `5/7`, the answer would be `0.714285714285714285714285714285714285714285...`

Because div_precision_increment is set to `4`, mysqld did the rounding. It effectively ran `SELECT FORMAT(5/7,4);` thus, rounding `0.714285714285714285714285714285714285714285...` to 4 digits to the right of the decimal point.

Let change the div_precision_increment to 10 and run `sum2` again

``````mysql> set div_precision_increment = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'div_precision_increment';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| div_precision_increment | 10    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select 1/7 + 2/7 + 2/7 sum2;
+--------------+
| sum2         |
+--------------+
| 0.7142857143 |
+--------------+
1 row in set (0.00 sec)

mysql>
``````

Since div_precision_increment is set to `10`, mysqld did the rounding. It effectively ran `SELECT FORMAT(5/7,10);` thus, rounding `0.714285714285714285714285714285714285714285...` to 10 digits to the right of the decimal point.

Let's try 25

``````mysql> set div_precision_increment = 25;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'div_precision_increment';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| div_precision_increment | 25    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select 1/7 + 2/7 + 2/7 sum2;
+-----------------------------+
| sum2                        |
+-----------------------------+
| 0.7142857142857142857142857 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>
``````

Since div_precision_increment is set to `25`, mysqld did the rounding. It effectively ran `SELECT FORMAT(5/7,25);` thus, rounding `0.714285714285714285714285714285714285714285...` to 25 digits to the right of the decimal point.

• +1.Very useful answer. But my core question is why doesn't the `select 1/3 + 1/3 + 1/3;` give `0.9999`? The `1/3` is `0.3333` according to the second select
– Jim
Sep 14, 2014 at 21:19
• Hi Ronaldo.May be I don't understand the answer. My confusion is: Why doesn't the `select 1/3 + 1/3 + 1/3;` give 1.0? Because of the default rounding at 4 digits? Because I was expecting the result not to be 1.0 but 0.9999 because I consider `select 1/3 + 1/3 + 1/3;` exactly the same as `select(0.3333 + 0.3333 + 0.3333);` since `select (1/3);` gives `0.333`. Can you please help me understand this?
– Jim
Sep 15, 2014 at 14:58