MySQL Bugs: #14826: Error on Create function with FORMAT

Bug #14826	Error on Create function with FORMAT
Submitted:	10 Nov 2005 13:39	Modified:	17 Nov 2005 11:34
Reporter:	Eber Duarte	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.17-BK, 5.0.15-standard	OS:	Linux (Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
MySQL return a erro when I tried to create a function that encapsulate FORMAT MySQL function.

This works properly:
mysql> CREATE FUNCTION format_money(n DOUBLE, d INTEGER) RETURNS CHAR(255)   RETURN FORMAT(2.3456, 2);

This doesn't work:
mysql> CREATE FUNCTION format_money(n DOUBLE, d INTEGER) RETURNS CHAR(255)   RETURN FORMAT(n, d);

Thanks a lot for help.

Regards.

How to repeat:
mysql> CREATE FUNCTION format_money(n DOUBLE, d INTEGER) RETURNS CHAR(255)   RETURN FORMAT(n, d);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'd)' at line 1

Thank you for a bug report. Verified just as described on 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00...):

[openxs@Fedora 5.0]$ bin/mysql -uroot test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

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

mysql> CREATE FUNCTION format_money(n DOUBLE, d INTEGER) RETURNS CHAR(255)
    -> RETURN FORMAT(2.3456, 2);
Query OK, 0 rows affected (0,06 sec)

mysql> select format_money(2.5, 2);
+----------------------+
| format_money(2.5, 2) |
+----------------------+
| 2.35                 |
+----------------------+
1 row in set (0,02 sec)

mysql> CREATE FUNCTION format_money2(n DOUBLE, d INTEGER) RETURNS CHAR(255)   RETURN FORMAT(n, d);
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 'd)' at line 1

This is actually functioning as designed.
FORMAT(x, d) accepts an expression as its first parameter, but only an explicit integer as its second parameter (not an expression). This works:

CREATE FUNCTION format_money(n DOUBLE) RETURNS CHAR(255)
  RETURN FORMAT(n, 2);

(This entry might be reopened as a feature request. In either case the documentation should be updated, since it's not very clear on at this point.)