Bug #79530 | Power() Function unexpected results when multiplied by decimal | ||
---|---|---|---|
Submitted: | 4 Dec 2015 17:59 | Modified: | 4 Dec 2015 18:31 |
Reporter: | Piotr Kiełtyka | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5 5.6 5.7 | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | Pow(); Power() |
[4 Dec 2015 17:59]
Piotr Kiełtyka
[4 Dec 2015 18:31]
MySQL Verification Team
Thank you for the bug report. Versions affected: 5.5/5.6/5.7: C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.97-Win X64 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.0 > SELECT 0.28 * Power(10,2); +--------------------+ | 0.28 * Power(10,2) | +--------------------+ | 28 | +--------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.74-Win X64 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.1 > SELECT 0.28 * Power(10,2); +--------------------+ | 0.28 * Power(10,2) | +--------------------+ | 28 | +--------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.47 Source distribution PULL: 2015-NOV-07 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 > SELECT 0.28 * Power(10,2); +--------------------+ | 0.28 * Power(10,2) | +--------------------+ | 28.000000000000004 | +--------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.28 Source distribution PULL: 2015-NOV-07 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > SELECT 0.28 * Power(10,2) -> ; +--------------------+ | 0.28 * Power(10,2) | +--------------------+ | 28.000000000000004 | +--------------------+ 1 row in set (0.06 sec) mysql 5.6 > exit Bye C:\dbs>57 C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.11 Source distribution PULL: 2015-NOV-25 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > SELECT 0.28 * Power(10,2); +--------------------+ | 0.28 * Power(10,2) | +--------------------+ | 28.000000000000004 | +--------------------+ 1 row in set (0.00 sec)
[9 Dec 2015 3:04]
Rick James
I'll bet that change was in response to some other rounding/precision bug. Be cautious about fixing this bug. I suspect there was a change in the choice of FLOAT/FIXED as the interpretation of POWER() or multiplication of mismatched types. Suggest you start there.