MySQL Bugs: #48374: FORMAT(X,D) function will not take a variable for D

Bug #48374	FORMAT(X,D) function will not take a variable for D
Submitted:	28 Oct 2009 12:30	Modified:	30 Aug 2010 14:56
Reporter:	John Larsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.0.88	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
FORMAT(X,D) where D is a variable will fail with a Syntax error.
ROUND(X,D) where D is a variable works.

I will test later in a new version of Mysql, but this should be a documented for 5.0 at least.

How to repeat:
For example lets say I have table
SciData
ID, DATA, SignificantDigits
1, 5.123, 2
2, 6,12, 1

SELECT FORMAT(DATA,SignificantDigits) from SciData;
does not work
SELECT ROUND(DATA,SignificantDigits) from SciData;
Does work.

Of course FORMAT(DATA, 2) 
works just fine.

Verified just as described with recent 5.0.88 from bzr:

77-52-222-60:5.0 openxs$ 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
Server version: 5.0.88-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table td(c1 float, c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into td values(1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select format(c1, c2) from td;
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 'c2) from td' at line 1
mysql> select round(c1, c2) from td;
+---------------+
| round(c1, c2) |
+---------------+
|             1 | 
|             2 | 
|             3 | 
+---------------+
3 rows in set (0.00 sec)

mysql> select @a:=c2, format(c1, @a) from td;
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 '@a) from td' at line 1
mysql> set @a:=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select format(c1, @a) from td;
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 '@a) from td' at line 1
mysql> select format(c1, 2) from td;
+---------------+
| format(c1, 2) |
+---------------+
| 1.00          | 
| 2.00          | 
| 3.00          | 
+---------------+
3 rows in set (0.00 sec)

This is 5.0 bug only, ans in 5.1.41 everythign works as expected:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql 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
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table td(c1 float, c2 int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into td values(1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select format(c1, c2) from td;
+----------------+
| format(c1, c2) |
+----------------+
| 1.0            |
| 2.00           |
| 3.000          |
+----------------+
3 rows in set (0.00 sec)

Verified just as described with recent 5.0.88 from bzr:

77-52-222-60:5.0 openxs$ 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
Server version: 5.0.88-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table td(c1 float, c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into td values(1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select format(c1, c2) from td;
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 'c2) from td' at line 1
mysql> select round(c1, c2) from td;
+---------------+
| round(c1, c2) |
+---------------+
|             1 | 
|             2 | 
|             3 | 
+---------------+
3 rows in set (0.00 sec)

mysql> select @a:=c2, format(c1, @a) from td;
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 '@a) from td' at line 1
mysql> set @a:=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select format(c1, @a) from td;
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 '@a) from td' at line 1
mysql> select format(c1, 2) from td;
+---------------+
| format(c1, 2) |
+---------------+
| 1.00          | 
| 2.00          | 
| 3.00          | 
+---------------+
3 rows in set (0.00 sec)

Verified again that this is resolved in 5.1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.1.51-debug-log Source distribution     

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,     
and you are welcome to modify and redistribute it under the GPL v2 license  

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> create table t(a float, b int);
Query OK, 0 rows affected (0.05 sec)  

mysql> insert into t values (1, 1), (2, 2), (3, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select format(a, b) from t;
+--------------+
| format(a, b) |
+--------------+
| 1.0          |
| 2.00         |
| 3.000        |
+--------------+
3 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.51-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql>

This bug was resolved indirectly by the fix for Bug#21114
and WL#3665 Functions call with IGNORE_SPACE,
which was implemented in 5.1.13 in January 2007.

Given that the fix is very extensive, and introduced incompatible changes,
this bug is not likely to be back ported to 5.0.

Changing the status of this report to documenting,
so that the release notes for 5.1 can be updated.

Noted in 5.1.13 changelog.

FORMAT(X,D) did not accept a nonconstant value for D.