Bug #28506 format function does not return proper numeric datatypes
Submitted: 18 May 2007 3:45 Modified: 18 May 2007 12:56
Reporter: avi weiss Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: data types, format function

[18 May 2007 3:45] avi weiss
Description:
during formatting process, format function takes correct datatype and creates some form of hybrid that does not allow proper sorting on returned values.

for example, a simple 2 column table "t":

key_id (int), value (float)
1 95.833
2 9.523
3 9.0909
4 87.5

"select value from "t" order by value desc" will yield the correctly ordered list in descending numeric value.

"select format(value,2) as newformat order by newformat desc" will yield the following list:

95.83
9.52
9.10
87.5

clearly, the sort is occurring to some form of char string, then formating is taking place. 

How to repeat:
see above

Suggested fix:
have format actually return valid float type numeric, so sorting will do the right thing.
[18 May 2007 8:17] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Description of FORMAT function at http://dev.mysql.com/doc/refman/5.1/en/string-functions.html says: "Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string." So described behaviour is expected.
[18 May 2007 12:56] avi weiss
yes my bad for working late at night. I read that AFTER I filed it. truncate is what I needed. sorry.