Bug #64526 bug when truncate() and format() used together

• Submitted: 2 Mar 2012 17:27
• Modified: 2 Mar 2012 18:52
• Reporter: M Jones
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.10, 5.5.20
• OS: Windows
• CPU Architecture: Any
• Tags: format(), TRUNCATE()

[2 Mar 2012 17:27] M Jones

Description:
There seems to be a bug when number goes over 999 with truncate and format functions used together (I want to round the number and not have commas).

How to repeat:
This works (returns 999.2):
select truncate(format(999.199,1),1) as x;

This fails (returns 1.0):
select truncate(format(1999.199,1),1) as x;

[2 Mar 2012 18:52] Valeriy Kravchuk

This is easy to repeat:

mysql> select truncate(format(1999.199,1),1) as x;
+-----+
| x   |
+-----+
| 1.0 |
+-----+
1 row in set (0.07 sec)

mysql> select truncate(format(999.199,1),1) as x;
+-------+
| x     |
+-------+
| 999.2 |
+-------+
1 row in set (0.00 sec)

But the reason for the problem is obvious, ',' symbol (thousands separator) in string representing formatted number in en_US (default) locale:

mysql> select format(1999.199,1) as x;
+---------+
| x       |
+---------+
| 1,999.2 |
+---------+
1 row in set (0.00 sec)

mysql> select format(999.199,1) as x;
+-------+
| x     |
+-------+
| 999.2 |
+-------+
1 row in set (0.00 sec)

This is all expected, but there is still a bug when server reads "1,999..." string and truncates it to 1 on the first non-numeric character. There is no warning about truncation! Compare to the following:

mysql> select '1,999' + 1;
+-------------+
| '1,999' + 1 |
+-------------+
|           2 |
+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '1,999'
1 row in set (0.00 sec)

This is a real bug demonstrated by your test case, IMHO.

[14 Jan 2014 19:18] Arthur O'Dwyer

This doesn't look like a bug. The way to round a number and not have commas is to use the ROUND() builtin:

  SELECT ROUND(1999.199, 1);

    +--------------------+
    | ROUND(1999.199, 1) |
    +--------------------+
    |             1999.2 |
    +--------------------+

The FORMAT() builtin is not meant for mathematical computations; it's meant to format a number as a character string for output into an English-language report.