MySQL Bugs: #38593: prcision math POWER calculation

Bug #38593	prcision math POWER calculation
Submitted:	6 Aug 2008 7:28	Modified:	8 Aug 2008 12:11
Reporter:	Hans Ginzel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1.25-rc-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	precision math

Description:
Hello,

I have compared calculations of part of the annuity formula in more systems.

MySQL has the worst standard double precision calculation:

MySQL  2.2775836364507		SELECT POWER(1+0.055/12, 180);
R      2.277583772482252	print((1+0.055/12)^180, digits=16);
Matlab 2.27758377248225		(1+0.055/12)^180
Perl   2.27758377248225		print((1+0.055/12)**180);
SAS    2.27758377248089		%put %sysevalf((1+0.055/12)**180);
Excel  2.277583772		(1+0.055/12)^180

bc -l  2.27758377248223174146	(1+0.055/12)^180
scale=50
	2.27758377248223174282405392292378461410806750987912

mysql> SELECT POWER(1+CONVERT(0.055, DECIMAL(60,30))/12,180) AS Inst;
+-----------------+
| Inst            |
+-----------------+
| 2.2775837724823 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT(POWER(1+CONVERT(0.055, DECIMAL(60,30))/12,180), DECIMAL(60,30)) AS Inst;
+----------------------------------+
| Inst                             |
+----------------------------------+
| 2.277583772482252000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

How to repeat:
SELECT POWER(1+0.055/12, 180);	-- 5,5% interest rate, 15 years

Suggested fix:
Be inspired by Perl or R-project source code.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html:

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
...
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places. 

So having result only with 15 decimal places is expected. You can work around this using temporary table:

mysql> create table t1(f1 double(30,20));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(POWER(1+CONVERT(0.055, DECIMAL(60,30))/12,180) );
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
+------------------------+
| f1                     |
+------------------------+
| 2.27758377248223586875 | 
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT POWER(1+CONVERT(0.055, DECIMAL(60,30))/12,180) AS Inst;
+-----------------+
| Inst            |
+-----------------+
| 2.2775837724822 | 
+-----------------+
1 row in set (0.01 sec)

Thank you for your suggestion with temporary table.

But I am still touching bad results for the standard DOUBLE data type (without any  CONVERTing, precision specifying) in compare to other systems that also use standard double precision calculations.

As you cite the documentation "A double-precision floating-point number is accurate to approximately 15 decimal places.". But the result of 
SELECT POWER(1+0.055/12, 180);
is wrong on the 7th decimal place and the result is also wrong comparative to other systems using standard double precision computing.
The default double computing in MySQL is worse than default computing in MS Excel!?! That is a bug.

Another problem is that it is not possible to use the CONVERSION trick for DOUBLE:
SELECT POWER(1+CONVERT(0.055, DOUBLE(30,20))/12, 180);

ERROR 1064 (42000): You have an error in your SQL syntax near 'DOUBLE(30,20))/12, 180)' at line 1

So I request that as a feature.

Or another solution. I request function say PRECISE_COMPUTE(scale, expression) so temporary table wolud not be necessary. Parametr scale would have same meaning as in program bc (standard gnu/linux binary calculator). There should be another possibility to request the precision than inserting into DOUBLE(M,D) column.

Thank you for the feedback.

> As you cite the documentation "A double-precision floating-point number is
> accurate to approximately 15 decimal places.". But the result of 
> SELECT POWER(1+0.055/12, 180); is wrong on the 7th decimal place and the result > is also wrong comparative to other systems using standard double precision computing.

You posted next results:

MySQL  2.2775836364507		SELECT POWER(1+0.055/12, 180);

This happens, because result of 0.055/12 is FLOAT which has exact 7 digits if its size doesn't specified. Compare:

mysql> select 1+0.055/12;
+------------+
| 1+0.055/12 |
+------------+
|  1.0045833 | 
+------------+
1 row in set (0.00 sec)

mysql> select 1+CONVERT(0.055, DECIMAL(60,30))/12;
+-------------------------------------+
| 1+CONVERT(0.055, DECIMAL(60,30))/12 |
+-------------------------------------+
|    1.004583333333333333333333333333 | 
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select power(1.0045833, 180);
+-----------------------+
| power(1.0045833, 180) |
+-----------------------+
|       2.2775701693678 | 
+-----------------------+
1 row in set (0.00 sec)

mysql> select power(1.004583333333333333333333333333, 180);
+----------------------------------------------+
| power(1.004583333333333333333333333333, 180) |
+----------------------------------------------+
|                              2.2775837724822 | 
+----------------------------------------------+
1 row in set (0.00 sec)

But request for extension of CAST function or of new function PRECISE_COMPUTE is valid, so I mark this report as feature request.

Temporary table isn't necessary, it's only affects the default width when you print your floating-point number. You can do the same with FORMAT function:

mysql> SELECT format(POWER(1+55e-3/12,180), 30) as x;
+----------------------------------+
| x                                |
+----------------------------------+
| 2.277583772482252300051186466590 | 
+----------------------------------+
1 row in set (0.00 sec)

Also, note that 1+0.055/12 is NOT treated as float. It's DECIMAL, and as described in the manual the scale of a decimal division depends on div_precision_increment:

mysql> set div_precision_increment=20; select 1+0.055/12;
+---------------------------+
| 1+0.055/12                |
+---------------------------+
| 1.00458333333333333333333 | 
+---------------------------+

You can force a number to be treated a double by using scientific notation - that's why I wrote 55e-3 instead of 0.055 in my first example