Bug #48273 Strange CEIL behavior in stored function

• Submitted: 23 Oct 2009 19:34
• Modified: 24 Oct 2009 1:01
• Reporter: Mike Drapolyuk
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.67, 5.0.84
• OS: Any
• CPU Architecture: Any
• Tags: CEIL store function

[23 Oct 2009 19:34] Mike Drapolyuk

Description:
CEIL(0.07/0.01)*0.01 returns 0.07 (right), but if i put CEIL into stored function:

DROP FUNCTION IF EXISTS `rt` $$
CREATE FUNCTION `rt`(a DOUBLE, b DOUBLE) RETURNS double
DETERMINISTIC
BEGIN
RETURN CEIL(a/b) * b;
END $$

and call it, i got 0.08

Similarly, this function returns 0.15 for a=0.14 and b=0.01.

How to repeat:
Just execute:

DELIMITER $$

DROP FUNCTION IF EXISTS `rt` $$
CREATE FUNCTION `rt`(a DOUBLE, b DOUBLE) RETURNS double
DETERMINISTIC
BEGIN
RETURN CEIL(a/b) * b;
END $$

DELIMITER ;

SELECT rt(0.07, 0.01), CEIL(0.07/0.01)*0.01;

[23 Oct 2009 20:00] Peter Laursen

Also in 5.1.40 I get the result 

rt(0.07, 0.01)  CEIL(0.07/0.01)*0.01
--------------  --------------------
          0.08                  0.07

[24 Oct 2009 1:01] MySQL Verification Team

Thank you for the bug report. This is issue with the floating-point numbers (double), see below:

mysql> DELIMITER $$
mysql>             
mysql> DROP FUNCTION IF EXISTS `rt` $$
Query OK, 0 rows affected (0.00 sec)  

mysql> CREATE FUNCTION `rt`(a DOUBLE, b DOUBLE) RETURNS double
    -> DETERMINISTIC                                          
    -> BEGIN                                                  
    -> RETURN CEIL(a/b) * b;                                  
    -> END $$                                                 
Query OK, 0 rows affected (0.00 sec)                          

mysql> 
mysql> DELIMITER ;
mysql>            
mysql> SELECT rt(0.07, 0.01), CEIL(0.07/0.01)*0.01;
+----------------+----------------------+          
| rt(0.07, 0.01) | CEIL(0.07/0.01)*0.01 |          
+----------------+----------------------+          
|           0.08 |                 0.07 |
+----------------+----------------------+
1 row in set (0.01 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `rt` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION `rt`(a DECIMAL(6,2), b DECIMAL(6,2)) RETURNS DECIMAL(6,2)
    -> DETERMINISTIC
    -> BEGIN
    -> RETURN CEIL(a/b) * b;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> SELECT rt(0.07, 0.01), CEIL(0.07/0.01)*0.01;
+----------------+----------------------+
| rt(0.07, 0.01) | CEIL(0.07/0.01)*0.01 |
+----------------+----------------------+
|           0.07 |                 0.07 |
+----------------+----------------------+
1 row in set (0.00 sec)

I tested with current source server 5.1.