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: Bugs System CPU Architecture:Any

[10 Nov 2005 13:39] Eber Duarte
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
[10 Nov 2005 14:04] Valeriy Kravchuk
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
[17 Nov 2005 11:34] Per-Erik Martin
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.)