Bug #55912 FORMAT with locale set fails for numbers < 1000
Submitted: 11 Aug 2010 14:37 Modified: 23 Sep 2010 22:16
Reporter: Dag Sondre Hansen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.4 OS:FreeBSD (8.0 release )
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: format locale

[11 Aug 2010 14:37] Dag Sondre Hansen
Description:
When formatting a decimal number using the FORMAT function, the decimal point will not be set according to the locale specified  if the value of the given number is less than 1000 (e.g. when no thousand separator will be shown):

mysql> SELECT FORMAT( 123.33, 2, 'no_NO' ) AS WRONG, FORMAT( 1123.33, 2, 'no_NO' ) AS OK ;
+--------+----------+
| WRONG  | OK       |
+--------+----------+
| 123.33 | 1.123,33 | << notice decimal point. they should both be ","
+--------+----------+
1 row in set (0.01 sec)

How to repeat:

mysql> SELECT FORMAT( 123.33, 2, 'no_NO' ) AS WRONG, FORMAT( 1123.33, 2, 'no_NO' ) AS OK ;
+--------+----------+
| WRONG  | OK       |
+--------+----------+
| 123.33 | 1.123,33 | << notice decimal point. they should both be ","
+--------+----------+
1 row in set (0.01 sec)

The same error occurs for locale set to de_DE
[11 Aug 2010 14:54] MySQL Verification Team
Thank you for the bug report.
[18 Aug 2010 14:23] Alexander Barkov
The original report is about DECIMAL numbers,
but the same problem is repeatable with REAL numbers as well:

mysql> select format(12333e-2,2,'no_NO'), format(112333e-2,2,'no_NO');
+----------------------------+-----------------------------+
| format(12333e-2,2,'no_NO') | format(112333e-2,2,'no_NO') |
+----------------------------+-----------------------------+
| 123.33                     | 1.123,33                    |
+----------------------------+-----------------------------+
1 row in set (1.07 sec)
[18 Aug 2010 14:36] Alexander Barkov
Another problem with FORMAT():

mysql> set character_set_connection=utf32; 
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 as select format(123,2);
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysqld: ctype-ucs2.c:2394: my_well_formed_len_utf32: Assertion `(length % 4) == 0' failed.
[19 Aug 2010 10:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116227

3188 Alexander Barkov	2010-08-19
      Bug#55912 FORMAT with locale set fails for numbers < 1000
      
      Problems:
      - dot character was always printed as decimal point
        instead of localized decimal point for short
        numbers without thousands
      - Item_func_format::val_str always returned values in ASCII format,
        regargless of @@character_set_connection, which in case of utf32
        led to crash in debug build, or to incorrect values in release
        build.
      
      Fix:
      - Adding a piece of code to replace dot character to
        localized decimal point in short numbers.
      - Changing parent class for Item_func_format to Item_str_ascii_func,
        because its val_str() implementation is heavily ASCII oriented.
[20 Aug 2010 11:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116367

3193 Alexander Barkov	2010-08-20
      Bug#55912 FORMAT with locale set fails for numbers < 1000
      
      Problems:
      - dot character was always printed as decimal point
        instead of localized decimal point for short
        numbers without thousands
      - Item_func_format::val_str always returned values in ASCII
      format,
        regargless of @@character_set_connection, which in case of utf32
        led to crash in debug build, or to incorrect values in release build.
      
      Fix:
      - Adding a piece of code to replace dot character to
        localized decimal point in short numbers.
      - Changing parent class for Item_func_format to
        Item_str_ascii_func, because its val_str() implementation is heavily ASCII oriented.
[20 Aug 2010 11:46] Alexander Barkov
Pushed into mysql-5.5-bugfixing (5.5.6-m3)
Pushed into mysql-trunk-bugfixing (5.6.1-m4)
Pushed into mysql-next-mr-bugfixing (5.6.99-m5)
[24 Aug 2010 6:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116573
[25 Aug 2010 9:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:30] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[23 Sep 2010 22:16] Paul DuBois
Noted in 5.5.6, 5.6.1 changelogs.

FORMAT() did not respect the decimal point character if the locale
was changed and always returned an ASCII value.