MySQL Bugs: #87973: POWER() round off

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

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.

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 ;

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

Thanks for the information. Maybe a good thing to update the manual. Since there is no reference to the data types used.