0

Why are the results different A and B in MariaDB?

SELECT 23033848 * 0.1248 * 11407967/ 23423423 AS 'A', 
23033848 * 0.1248 * (11407967/ 23423423) AS 'B'


"A":"1400035.27058379"  

"B":"1400035.27026258"
Mohan Singh
  • 1,142
  • 3
  • 15
  • 30
kimtheho
  • 11
  • 1
  • Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Tim Biegeleisen Nov 08 '19 at 07:52
  • Floating point arithmetic, by definition, is not exact. While the `A` and `B` results are mathematically identical, the two floating point results from your query are not...at least not quite exact. – Tim Biegeleisen Nov 08 '19 at 07:53

1 Answers1

0

If the answer were just "FLOAT is broken", then I would expect A and B to agree to about 16 significant digits, since that is the precision available in DOUBLE.

However, the results agree to only about 10 digits, so something else is going on.

This is what you have (adding parens to clarify):

SELECT (23033848 * 0.1248 *  11407967) / 23423423  AS 'A', 
        23033848 * 0.1248 * (11407967  / 23423423) AS 'B'

Adding some trailing zeros on gives:

mysql> SELECT 23033848.00 * 0.124800000000000000 * 11407967.00/ 23423423.00 AS 'A',
              23033848.00 * 0.1248000000000000 * (11407967.00/ 23423423.00) AS 'B' ;
+------------------------------------+----------------------------------+
| A                                  | B                                |
+------------------------------------+----------------------------------+
| 1400035.27058379113932237828775068 | 1400035.270583791139265109708800 |
+------------------------------------+----------------------------------+

Shows that probably A is closer to "correct".

So, what is B doing "wrong"?

mysql> SELECT 11407967/ 23423423, 11407967./ 23423423., 11407967.00/ 23423423.00;
+--------------------+----------------------+--------------------------+
| 11407967/ 23423423 | 11407967./ 23423423. | 11407967.00/ 23423423.00 |
+--------------------+----------------------+--------------------------+
|             0.4870 |               0.4870 |                 0.487032 |
+--------------------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%div%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| div_precision_increment   | 4     |

So, I'll bet something is going on with switching to DECIMAL and playing with the number of decimal places.

(The rest of this Answer is left as an exercise.

OK, I don't know the rest of the Answer.)

Rick James
  • 135,179
  • 13
  • 127
  • 222