MySQL Bugs: #63020: Format function in View lost locale

Bug #63020	Format function in View lost locale
Submitted:	30 Oct 2011 16:53	Modified:	5 Jan 2012 17:49
Reporter:	te kri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.15, 5.5.17	OS:	Any (7,x32)
Assigned to:		CPU Architecture:	Any
Tags:	format, VIEW

Description:
when view is created using function FORMAT and FORMAT function use locale option,
definition of view saved into server doesn't contain that locale information, therefore data is formated as US nor as selected locale 

How to repeat:
create  table test2 (bb decimal (10,2));

insert into test2 values (10.32),(10009.2),(12345678.21);

create view test3 as select format(bb,1,'sk_SK') as cc from test2;

this select return data in US format
select * from test3;
+--------------+
| cc           |
+--------------+
| 10.3         |
| 10,009.2     |
| 12,345,678.2 |
+--------------+

this select return data in SK format
select format(bb,1,'sk_SK') as dd from test2;
+--------------+
| dd           |
+--------------+
| 10,3         |
| 10 009,2     |
| 12 345 678,2 |
+--------------+

Thank you for the bug report. verified with 5.5.17 on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create  table test2 (bb decimal (10,2));
Query OK, 0 rows affected (0.26 sec)

mysql> insert into test2 values (10.32),(10009.2),(12345678.21);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create view test3 as select format(bb,1,'sk_SK') as cc from test2;
Query OK, 0 rows affected (0.26 sec)

mysql> select * from test3;
+--------------+
| cc           |
+--------------+
| 10.3         |
| 10,009.2     |
| 12,345,678.2 |
+--------------+
3 rows in set (0.02 sec)

mysql> select format(bb,1,'sk_SK') as dd from test2;
+--------------+
| dd           |
+--------------+
| 10,3         |
| 10 009,2     |
| 12 345 678,2 |
+--------------+
3 rows in set (0.00 sec)

mysql> show create view test3\G
*************************** 1. row ***************************
                View: test3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test3` AS select format(`test2`.`bb`,1) AS `cc` from `test2`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.02 sec)

Noted in 5.5.20, 5.6.5 changelogs.

Locale information for FORMAT() function instances was lost in view
definitions.