Bug #43483 get wrong result when putting a code into mysql function.
Submitted: 8 Mar 2009 14:32 Modified: 8 Mar 2009 14:50
Reporter: Steve Pham Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.51b-community-nt OS:Windows
Assigned to: CPU Architecture:Any
Tags: functions

[8 Mar 2009 14:32] Steve Pham
Description:
the same code return different result.
see example below:

1.
mysql> select replace(trim(trailing '.' from trim(trailing '0' from 20.00 ) ),'.',',');
+--------------------------------------------------------------------------+
| replace(trim(trailing '.' from trim(trailing '0' from 20.00 ) ),'.',',') |
+--------------------------------------------------------------------------+
| 20                                                                       |
+--------------------------------------------------------------------------+
1 row in set

2. Now i put "replace(trim(trailing '.' from trim(trailing '0' from 20.00 )" into a function, then get a wrong result

DROP FUNCTION IF EXISTS `prodb`.`convert_price_vn_2`$$

CREATE FUNCTION `prodb`.`convert_price_vn_2`(price float)
	RETURNS varchar(10)
BEGIN
declare retval varchar(20);
return replace(trim(trailing '.' from trim(trailing '0' from price)),'.',',');
END$$

DELIMITER ;

then try 

mysql> select convert_price_vn_2(20.000);
+----------------------------+
| convert_price_vn_2(20.000) |
+----------------------------+
| 2                          |
+----------------------------+
1 row in set

returns 2 while expect 20.

How to repeat:
the same code return different result.
see example below:

1.
mysql> select replace(trim(trailing '.' from trim(trailing '0' from 20.00 ) ),'.',',');
+--------------------------------------------------------------------------+
| replace(trim(trailing '.' from trim(trailing '0' from 20.00 ) ),'.',',') |
+--------------------------------------------------------------------------+
| 20                                                                       |
+--------------------------------------------------------------------------+
1 row in set

2. Now i put "replace(trim(trailing '.' from trim(trailing '0' from 20.00 )" into a function, then get a wrong result

DROP FUNCTION IF EXISTS `prodb`.`convert_price_vn_2`$$

CREATE FUNCTION `prodb`.`convert_price_vn_2`(price float)
	RETURNS varchar(10)
BEGIN
declare retval varchar(20);
return replace(trim(trailing '.' from trim(trailing '0' from price)),'.',',');
END$$

DELIMITER ;

then try 

mysql> select convert_price_vn_2(20.000);
+----------------------------+
| convert_price_vn_2(20.000) |
+----------------------------+
| 2                          |
+----------------------------+
1 row in set

returns 2 while expect 20.

Suggested fix:
no idea to fix.
[8 Mar 2009 14:35] Steve Pham
changed to serious severity.
[8 Mar 2009 14:50] Valeriy Kravchuk
Sorry, but this is NOT a bug. Both 20.000 and 20.00 is considered just as 20 when passed as FLOAT parameter. Redefine your function with VARCHAR parameter to get the desired result:

mysql> CREATE FUNCTION `convert_price_vn_3`(price varchar(20)) RETURNS varchar(10) BEGIN declare retval varchar(20); return replace(trim(trailing '.' from trim(trailing '0' from price)),'.',','); END$$
Query OK, 0 rows affected (0.00 sec)

mysql> select convert_price_vn_3('20.00')$$
+-----------------------------+|
 convert_price_vn_3('20.00') |
+-----------------------------+
| 20                          | 
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert_price_vn_3(20.00)$$
+---------------------------+
| convert_price_vn_3(20.00) |
+---------------------------+
| 20                        | 
+---------------------------+
1 row in set (0.00 sec)

mysql> select convert_price_vn_3(20.000)$$
+----------------------------+
| convert_price_vn_3(20.000) |
+----------------------------+
| 20                         | 
+----------------------------+
1 row in set (0.00 sec)