Bug #87973 POWER() round off
Submitted: 4 Oct 2017 11:50 Modified: 18 Oct 2017 11:41
Reporter: Dave Hoogendoorn Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.7.19 OS:Windows (10 )
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: POWER()/POW() round off issue

[4 Oct 2017 11:50] Dave Hoogendoorn
Description:
If we use POWER with a base 16 and increase the exponent with a number higher than 13 e.g. POWER(16,14) zero's start to appear at the end of the resulting number. 

example for POWER(16,14)
The result is: 72057594037927940 
While the result should be: 72057594037927936 

example for POWER(16,15)
The result is: 1152921504606847000
While the result should be: 1152921504606846976

.
.
.

example for POWER(16,24)
The result is: 79228162514264340000000000000
While the result should be: 79228162514264337593543950336

The higher the exponent, te more trailing zero's appear. This is not the expected behaviour. I would expect to utilize the maximum bandwidth of the DECIMAL datatype.

How to repeat:
Syntax is:
SELECT POWER(<base>,<exponent>);

With in the MySQL workbench the following queries can be executed:

SELECT POWER(16,14) ;
or
SELECT CAST(POWER(16,14) AS decimal(65,0)); #to get rid of the scientific notation.

The given base and exponent are not the only possibilities to repeat this. As soon as the number gets too large, the problem appears. E.g. POWER(13,16) will show the same issue.

Suggested fix:
There is currently 4/10/2017 no workaround available.

Using CAST to explicitly change the datatype of the base and exponent to a DECIMAL, should overcome this issue. E.g.

SELECT POWER(CAST((<base> AS DECIMAL(<x>)),CAST(<exponent> AS DECIMAL(<x>))) 

However I have already tested this does not work with MySQL. So apparently something has to change in the POWER/POW function.
[4 Oct 2017 13:53] Dave Hoogendoorn
Workaround is available

Created own POWER function that accepts input parameters <base> and <exponent> of datatype DECIMAL(60,0).

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `MY_POW`(base DECIMAL(60,0), exponent DECIMAL(60,0)) RETURNS decimal(60,0)
BEGIN
	DECLARE rslt DECIMAL(60,0);
	DECLARE step TINYINT;
    
    SET rslt = 1;
    SET step = 1;
    
		label1: WHILE step <= exponent DO
			SET rslt = rslt * base;	
            SET step = step + 1;
		END WHILE label1;	

	RETURN rslt;

END$$
DELIMITER ;
[5 Oct 2017 14:17] MySQL Verification Team
Hi,

This is not a bug. POW() function returns DOUBLE. Max DOUBLE precision is 16 digits so that's how many significant digits you get.

mysql [localhost] {msandbox} ((none)) > select POW(16,15);
Field   1:  `POW(16,15)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 20
Decimals:   31
Flags:      BINARY NUM

+----------------------+
| POW(16,15)           |
+----------------------+
| 1.152921504606847e18 |
+----------------------+
1 row in set (0.00 sec)

kind regards
Bogdan
[18 Oct 2017 11:41] Dave Hoogendoorn
Thanks for the information. Maybe a good thing to update the manual. Since there is no reference to the data types used.