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: | |
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
[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.