Description:
The FORMAT function should format a number, rounding
it to a specified number of decimal places. When I try
this with a large decimal value, I get an incorrect result
when the number of rounded places either exceeds the
size of the first argument's scale or reaches about 7
places. I also get an incorrect result when the pre-decimal
part of the argument gets to about 16 digits.
How to repeat:
mysql> create table t1 (col1 int, col2 decimal(60,30));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,1234567890.12345);
Query OK, 1 row affected (0.00 sec)
mysql> select format(col2,7) from t1;
+-----------------------+
| format(col2,7) |
+-----------------------+
| 1,234,567,890.1234500 |
+-----------------------+
1 row in set (0.00 sec)
-- This is the correct result.
mysql> select format(col2,8) from t1;
+------------------------+
| format(col2,8) |
+------------------------+
| 1,234,567,890.12345004 |
+------------------------+
1 row in set (0.00 sec)
-- But if I ask for one more post-decimal digit, I get
an incorrect result. The correct result is
1,234,567,890.12345000
mysql> insert into t1 values(2,1234567890.12345678);
Query OK, 1 row affected (0.00 sec)
mysql> select format(col2,6) from t1 where col1=2;
+----------------------+
| format(col2,6) |
+----------------------+
| 1,234,567,890.123457 |
+----------------------+
1 row in set (0.00 sec)
-- This is the correct result.
mysql> select format(col2,7) from t1 where col1=2;
+-----------------------+
| format(col2,7) |
+-----------------------+
| 1,234,567,890.1234567 |
+-----------------------+
1 row in set (0.00 sec)
-- But if I ask for one more post-decimal digit,
I get an incorrect result. The correct result is
1,234,567,890.1234568
mysql> insert into t1 values(7,1234567890123456.12345);
Query OK, 1 row affected (0.00 sec)
mysql> select format(col2,6) from t1 where col1=7;
+------------------------------+
| format(col2,6) |
+------------------------------+
| 1,234,567,890,123,456.000000 |
+------------------------------+
1 row in set (0.00 sec)
-- This result correctly format the pre-decimal digits,
but the post-decimal digits are incorrect. The correct
result is 1,234,567,890,123,456.123450
mysql> insert into t1 values(6,12345678901234567.12345);
Query OK, 1 row affected (0.00 sec)
mysql> select format(col2,6) from t1 where col1=6;
+-------------------------------+
| format(col2,6) |
+-------------------------------+
| 12,345,678,901,234,568.000000 |
+-------------------------------+
1 row in set (0.01 sec)
-- If I add one more digit to the pre-decimal part of the
argument, then even that is no longer correct. The
correct result is 12,345,678,901,234,567.123450
