Bug #25896 Using a variable as second argument to FORMAT in stored procedure

• Submitted: 27 Jan 2007 23:37
• Modified: 28 Jan 2007 9:15
• Reporter: Justin Stodola
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.28-enterprise-gpl-log
• OS: Linux (Linux)
• CPU Architecture: Any

[27 Jan 2007 23:37] Justin Stodola

Description:
When defining a stored procedure a syntax error is returned when trying to use a variable as the second argument (precision) to FORMAT().  Variables work as expected in the first argument (value).

How to repeat:
The following definition:

DELIMITER |
CREATE FUNCTION fn_test(weight DOUBLE) RETURNS CHAR(255)
BEGIN
DECLARE rv CHAR(255);
DECLARE multiplier NUMERIC(12,4) UNSIGNED;
DECLARE places INTEGER;
SET multiplier = 1.00;
SET places = 1;
SET rv = FORMAT(weight * multiplier, places);
RETURN rv;
END|
DELIMITER ;

gives this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'places);
RETURN rv;
END' at line 8
Replacing "SET rv = FORMAT(weight * multiplier, places);" with "SET rv = FORMAT(weight * multiplier, 1);" gives no error and works correctly.

[28 Jan 2007 9:15] Valeriy Kravchuk

Thank you for a bug report. This is a duplicate of bug #22684. That bug is fixed in 5.1.14. Fix will not be ported back to 5.0.x branch, sorry.