Bug #13815 left() with fixed point return value for function (or out parameter for sp)

• Submitted: 6 Oct 2005 19:15
• Modified: 20 Apr 2006 16:05
• Reporter: Gleb Paharenko
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.13/BK source 5.0.15
• OS: Any (ALL)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[6 Oct 2005 19:15] Gleb Paharenko

Description:
The value returned by left(), right(),mid(),etc functions are rounded to the wrong values. Reported by Juri Shimon at:
  http://lists.mysql.com/mysql/190046

I agree with him that this is a weird behavior.

How to repeat:
mysql> drop function if exists test;
mysql> drop function if exists test1;
mysql> delimiter //
mysql> create function test () returns numeric(7,2) return left('77.247',5)//
mysql> create function test1 () returns numeric(7,2) return '77.247'//
mysql> delimiter ;
mysql> select test(),test1();
+--------+---------+
| test() | test1() |
+--------+---------+
|  77.00 |   77.25 |
+--------+---------+
1 row in set (0.02 sec)

How to correct:
use global variable as intermediate storage (with side effect)
--------------------
mysql> drop function if exists test;
mysql> delimiter //
mysql> create function test () returns numeric(7,2) return @z:=left('77.247',5)//
mysql> delimiter ;
mysql> select test(),test1();
+--------+---------+
| test() | test1() |
+--------+---------+
|  77.24 |   77.25 |
+--------+---------+
1 row in set (0.00 sec)

Suggested fix:
Fix this

[6 Oct 2005 19:48] MySQL Verification Team

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create function test () returns numeric(7,2) return left('77.247',5)//
Query OK, 0 rows affected (0.03 sec)

mysql> create function test1 () returns numeric(7,2) return '77.247'//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select test(),test1();
+--------+---------+
| test() | test1() |
+--------+---------+
|  77.00 |   77.25 |
+--------+---------+
1 row in set (0.00 sec)

[20 Apr 2006 15:53] Juri Shimon

It seems have been corrected in one of the latest versions. On 5.0.20:

mysql> drop function if exists test;
mysql> drop function if exists test1;
mysql> delimiter //
mysql> create function test () returns numeric(7,2) return left('77.247',5)//
mysql> create function test1 () returns numeric(7,2) return '77.247'//
mysql> delimiter ;
mysql> select test(),test1();
+--------+---------+
| test() | test1() |
+--------+---------+
|  77.24 |   77.25 |
+--------+---------+
1 row in set (0.02 sec)

[20 Apr 2006 16:02] MySQL Verification Team

Verifying again.

[20 Apr 2006 16:05] MySQL Verification Team

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/