Fix for bug #8429 (FORMAT returns incorrect result)

mysql-test/r/func_math.result:
  test result ixed
mysql-test/t/func_math.test:
  test case added
sql/item_strfunc.cc:
  Item_func_format::val_str now handles 'decimal' and 'double' values in
  different way

3 files changed, 52 insertions, 13 deletions

diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index 0798a034c3e..e4889289c18 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -130,3 +130,16 @@ Warnings:
 Note	1003	select degrees(pi()) AS `degrees(pi())`,radians(360) AS `radians(360)`
 select rand(rand);
 ERROR 42S22: Unknown column 'rand' in 'field list'
+create table t1 (col1 int, col2 decimal(60,30));
+insert into t1 values(1,1234567890.12345);
+select format(col2,7) from t1;
+format(col2,7)
+1,234,567,890.1234500
+select format(col2,8) from t1;
+format(col2,8)
+1,234,567,890.12345000
+insert into t1 values(7,1234567890123456.12345);
+select format(col2,6) from t1 where col1=7;
+format(col2,6)
+1,234,567,890,123,456.123450
+drop table t1;
diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test
index 0eac72782a8..b21f38052b6 100644
--- a/mysql-test/t/func_math.test
+++ b/mysql-test/t/func_math.test
@@ -67,3 +67,15 @@ explain extended select degrees(pi()),radians(360);
 
 --error 1054
 select rand(rand);
+
+#
+# Bug #8459 (FORMAT returns incorrect result)
+#
+create table t1 (col1 int, col2 decimal(60,30));
+insert into t1 values(1,1234567890.12345);
+select format(col2,7) from t1;
+select format(col2,8) from t1;
+insert into t1 values(7,1234567890123456.12345);
+select format(col2,6) from t1 where col1=7;
+drop table t1;
+
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index d3327a0e41f..539bed58e66 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1668,22 +1668,36 @@ Item_func_format::Item_func_format(Item *org,int dec) :Item_str_func(org)
 
 String *Item_func_format::val_str(String *str)
 {
-  DBUG_ASSERT(fixed == 1);
-  double nr= args[0]->val_real();
-  uint32 length,str_length,dec;
+  uint32 length, str_length ,dec;
   int diff;
-  if ((null_value=args[0]->null_value))
-    return 0; /* purecov: inspected */
-  nr= my_double_round(nr, decimals, FALSE);
+  DBUG_ASSERT(fixed == 1);
   dec= decimals ? decimals+1 : 0;
-  /* Here default_charset() is right as this is not an automatic conversion */
-  str->set(nr,decimals, default_charset());
-  if (isnan(nr))
-    return str;
-  str_length=str->length();
-  if (nr < 0)
-    str_length--;				// Don't count sign
 
+  if (args[0]->result_type() == DECIMAL_RESULT ||
+      args[0]->result_type() == INT_RESULT)
+  {
+    my_decimal dec_val, rnd_dec, *res;
+    res= args[0]->val_decimal(&dec_val);
+    my_decimal_round(E_DEC_FATAL_ERROR, res, decimals, false, &rnd_dec);
+    my_decimal2string(E_DEC_FATAL_ERROR, &rnd_dec, 0, 0, 0, str);
+    str_length= str->length();
+    if (rnd_dec.sign())
+      str_length--;
+  }
+  else
+  {
+    double nr= args[0]->val_real();
+    if ((null_value=args[0]->null_value))
+      return 0; /* purecov: inspected */
+    nr= my_double_round(nr, decimals, FALSE);
+    /* Here default_charset() is right as this is not an automatic conversion */
+    str->set(nr,decimals, default_charset());
+    if (isnan(nr))
+      return str;
+    str_length=str->length();
+    if (nr < 0)
+      str_length--;				// Don't count sign
+  }
   /* We need this test to handle 'nan' values */
   if (str_length >= dec+4)
   {