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

Question

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?

Answer 1

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.