MySQL Bugs: #11066: FORMAT Function

Bug #11066	FORMAT Function
Submitted:	3 Jun 2005 9:27	Modified:	26 Jul 2005 19:26
Reporter:	Tom Chantry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Any (any)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
FORMAT Function seems to return incorrect values under certain conditions.

How to repeat:
Using the query 

SELECT d.fund, if(d.size=0,'-',format(d.Size/1000000,0))
FROM data D WHERE date='2005-05-01' ORDER BY d.fund	

gives results like

'Example1', '2,540'
'Example2', '120'
'Example3', '850'

which is correct, if i now use the following query, which is the same as above but linking to another table

SELECT d.Fund,if(d.size=0,'-',format(d.Size/1000000,0))
FROM data d, FundIndex Fi 
WHERE d.date='2005-05-01' and Fi.shortname = d.fund 		
ORDER BY d.fund	

I get incorrect results

'Example1', '2'
'Example2', '1'
'Example3', '8'

But if I execute the same query without the ORDER BY d.fund - it again gives the correct results

Could you please write here definition of tables ?

Data:
'fund', 'varchar(15)', 'NO', 'PRI', '', ''
'date', 'date', 'NO', 'PRI', '0000-00-00', ''
'ror', 'double', 'YES', '', '', ''
'Size', 'double', 'YES', '', '', ''

FundIndex
'shortname', 'varchar(20)', 'NO', 'PRI', '', ''
'fullname', 'varchar(100)', 'NO', 'PRI', '', ''
'Type', 'varchar(4)', 'YES', '', '', ''
'OtherName', 'varchar(50)', 'NO', '', '', ''
'Status', 'int(10) unsigned', 'NO', '', '0', ''
'ProgName', 'varchar(20)', 'NO', '', '', ''

Sorry...but I can't reproduce the bug:

mysql> SELECT d.Fund,if(d.size=0,'-',format(d.Size/1000000,0)) FROM data d, FundIndex Fi WHERE d.date=NOW() and Fi.shortname = d.fund ORDER BY d.fund;
+-----------+-------------------------------------------+
| Fund      | if(d.size=0,'-',format(d.Size/1000000,0)) |
+-----------+-------------------------------------------+
| Example 1 | 3                                         |
| Example 2 | 120                                       |
| Example 3 | 850                                       |
+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

Ok try following,

mysql> create table data (Name char(20),Date_ Date, Figure Double) engine = myis
am;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into myindex values('Example1','Example1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data values ('Example1','2005-05-01',850000000);
Query OK, 1 row affected (0.00 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) fr
om myindex, data where myindex.name1 = data.name order by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 850.00                                               |
+----------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into data values ('Example2','2005-05-01',950000000);
Query OK, 1 row affected (0.00 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) fr
om myindex, data where myindex.name1 = data.name order by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 850.00                                               |
+----------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into myindex values('Example2','Example2');
Query OK, 1 row affected (0.00 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) fr
om myindex, data where myindex.name1 = data.name and data.date_ = '2005-05-01' o
rder by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 8                                                    |
| Example2 | 9                                                    |
+----------+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) fr
om myindex, data where myindex.name1 = data.name  order by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 8                                                    |
| Example2 | 9                                                    |
+----------+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) fr
om myindex, data where myindex.name1 = data.name  ;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 850.00                                               |
| Example2 | 950.00                                               |
+----------+------------------------------------------------------+
2 rows in set (0.00 sec)

I was able reproduce it with 5.0 bk tree.

I used the following CREATE TABLE statement for myindex table, because it's absent in Tom's test case:
create table myindex(name1 varchar(20), name2 varchar(20));

So, the real crux of this problem was that using an 'order by' clause along with the original query caused the results to not display the desired formatting. 

In testing this, it appears that it is now fixed:

mysql> select * from data;
+----------+------------+-----------+
| Name     | Date_      | Figure    |
+----------+------------+-----------+
| Example1 | 2005-05-01 | 850000000 |
| Example2 | 2005-05-01 | 950000000 |
+----------+------------+-----------+
2 rows in set (0.07 sec)

mysql> select * from myindex;
+----------+----------+
| name1    | name2    |
+----------+----------+
| Example1 | Example1 |
| Example2 | Example2 |
+----------+----------+
2 rows in set (0.21 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) from myindex, data where myindex.name1 = data.name and data.date_ = '2005-05-01' order
 by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 850.00                                               |
| Example2 | 950.00                                               |
+----------+------------------------------------------------------+
2 rows in set (0.44 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,2)) from myindex, data where myindex.name1 = data.name  ;                                 
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,2)) |
+----------+------------------------------------------------------+
| Example1 | 850.00                                               |
| Example2 | 950.00                                               |
+----------+------------------------------------------------------+
2 rows in set (0.51 sec)

mysql> select data.name, if(data.figure =0,'-',format(data.figure/1000000,4)) from myindex, data where myindex.name1 = data.name and data.date_ = '2005-05-01' order
 by data.name;
+----------+------------------------------------------------------+
| name     | if(data.figure =0,'-',format(data.figure/1000000,4)) |
+----------+------------------------------------------------------+
| Example1 | 850.0000                                             |
| Example2 | 950.0000                                             |
+----------+------------------------------------------------------+
2 rows in set (0.62 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.11-beta-debug |
+-------------------+
1 row in set (0.05 sec)