MySQL Bugs: #79530: Power() Function unexpected results when multiplied by decimal

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5 5.6 5.7	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	Pow(); Power()

Description:
When doing simple ceiling rounding of decimals an unexpected result occurred

Ceil(0.28 * Power(10,2)) / Power(10,2) gives 0.29 not expected 0.28

Moreover, 0.28 * Power(10,2) --> 28.000000000000004
          0.29 * Power(10,2) --> 28.999999999999996

How to repeat:
SELECT 0.28 * Power(10,2)

Suggested fix:
Workaround:

CEIL(CONVERT(0.28 * Power(10,2),DECIMAL(32,2))) / Power(10,2) --> 0.28

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)

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.