MySQL Bugs: #61938: Invalid result from equation of double data return by stored function

Bug #61938	Invalid result from equation of double data return by stored function
Submitted:	21 Jul 2011 6:57	Modified:	24 Jan 2012 10:27
Reporter:	William Anthony	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	double, FUNCTION, precision, regression

Description:
When doing equation of double variables returned by stored function, the result is wrong.

Tested with 5.0 and 5.1, the result is true.

How to repeat:
select 	x_netprice_calc(80000,4,'R',0,'P',0,'P',0,'P') as base, x_netprice_calc(80000,4,'R',3,'P',0,'P',0,'P') as net2, (x_netprice_calc(80000,4,'R',0,'P',0,'P',0,'P') - x_netprice_calc(80000,4,'R',3,'P',0,'P',0,'P')) as base_minus_net2;

The result of base_minus_net2 should be 2399.88 but it returned 2399.8800000000047

the stored function used for test case

Attachment: x_netprice_calc.sql (text/x-sql), 999 bytes.

Verified with 5.5.14 on 32-bit Windows:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> delimiter $$
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `x_netprice_calc`(
    ->                                                  price double,
    ->                                                  disc1_amt double, disc1_
kode varchar(1),
    ->                                                  disc2_amt double, disc2_
kode varchar(1),
    ->                                                  disc3_amt double, disc3_
kode varchar(1),
    ->                                                  disc4_amt double, disc4_
kode varchar(1)) RETURNS double
    -> BEGIN
    ->  declare baseprice double;
    ->  declare netprice1 double;
    ->  declare netprice2 double;
    ->  declare netprice3 double;
    ->
    ->  set baseprice = price - If(
    ->                          disc1_kode = "P",
    ->                                  ROUND((price * disc1_amt)/100,2),
    ->                                  disc1_amt
    ->                          );
    ->
    ->  set netprice1 = If(
    ->                          disc2_kode = "P",
    ->                                  ROUND((baseprice * disc2_amt)/100,2),
    ->                                  disc2_amt
    ->                          );
    ->
    ->  set netprice2 = If(
    ->                          disc3_kode = "P",
    ->                                  ROUND((baseprice * disc3_amt)/100,2),
    ->                                  disc3_amt
    ->                          );
    ->
    ->  set netprice3 = If(
    ->                          disc4_kode = "P",
    ->                                  ROUND((baseprice * disc4_amt)/100,2),
    ->                                  disc4_amt
    ->                          );
    ->
    ->  return baseprice - netprice1 - netprice2 - netprice3;
    -> END$$
Query OK, 0 rows affected (0.47 sec)

mysql> delimiter ;
mysql> select   x_netprice_calc(80000,4,'R',0,'P',0,'P',0,'P') as base,
    -> x_netprice_calc(80000,4,'R',3,'P',0,'P',0,'P') as net2,
    -> (x_netprice_calc(80000,4,'R',0,'P',0,'P',0,'P') -
    -> x_netprice_calc(80000,4,'R',3,'P',0,'P',0,'P')) as base_minus_net2;
+-------+----------+--------------------+
| base  | net2     | base_minus_net2    |
+-------+----------+--------------------+
| 79996 | 77596.12 | 2399.8800000000047 |
+-------+----------+--------------------+
1 row in set (0.38 sec)

mysql> select 79996 - 77596.12;
+------------------+
| 79996 - 77596.12 |
+------------------+
|          2399.88 |
+------------------+
1 row in set (0.00 sec)

mysql> select '79996' - '77596.12';
+----------------------+
| '79996' - '77596.12' |
+----------------------+
|   2399.8800000000047 |
+----------------------+
1 row in set (0.00 sec)

mysql> select '79996' - 77596.12;
+--------------------+
| '79996' - 77596.12 |
+--------------------+
| 2399.8800000000047 |
+--------------------+
1 row in set (0.00 sec)

So, while function clearly returns double and use double in all the intermediate calculations, looks like when we calculate f(...) - f(...) we interpret result returned by function call as string somewhere, and convert it back to double.

CREATE FUNCTION `x_netprice_calc_2`(
 price double, 
 disc1_amt double, disc1_kode varchar(1), 
 disc2_amt double, disc2_kode varchar(1),
 disc3_amt double, disc3_kode varchar(1),
 disc4_amt double, disc4_kode varchar(1)) RETURNS decimal(9,2)
...
...

select
x_netprice_calc_2(80000,4,'R',0,'P',0,'P',0,'P') as base,
x_netprice_calc_2(80000,4,'R',3,'P',0,'P',0,'P') as net2,
(x_netprice_calc_2(80000,4,'R',0,'P',0,'P',0,'P') -
x_netprice_calc_2(80000,4,'R',3,'P',0,'P',0,'P')) as base_minus_net2;
base	net2	base_minus_net2
79996.00	77596.12	2399.88

f(...) - f(...)  is double minus double, which may yield rounding errors like this.
Either change function definition, or round() the result of the subtraction to the desired precision.

Thanks for the workaround, it's worked. But will it be fixed? Why 5.0 and 5.1 not affected?

See
"Incompatible Change: The server now includes dtoa, a library for conversion..."
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html