Bug #35500 view clips result of function dayname()
Submitted: 22 Mar 2008 15:01 Modified: 19 Nov 2009 18:13
Reporter: Roland Volkmann Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.51a / 5.0.56 OS:Windows (XP Prof. German)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: dayname, lc_time_names, VIEW

[22 Mar 2008 15:01] Roland Volkmann
Description:
if you use function dayname() within a view, than the resulting name is clipped to 9 chars if locale is set to german (set @@lc_time_names = 'de_DE'). Database Engine is InnoDB.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect test;
Connection id:    2
Current database: test

mysql> select @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| de_DE           |
+-----------------+
1 row in set (0.00 sec)

mysql> select dayname('2008-01-03');
+-----------------------+
| dayname('2008-01-03') |
+-----------------------+
| Donnerstag            |
+-----------------------+
1 row in set (0.00 sec)

mysql> create view v_test as select dayname('2008-01-03') as "Wochentag";
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_test;
+-----------+
| Wochentag |
+-----------+
| Donnersta |
+-----------+
1 row in set (0.00 sec)

mysql>
[22 Mar 2008 18:07] Valeriy Kravchuk
Thank you for a bug report. Verified just as described. 

Looks like server assumes English always when deciding on maximum column length for the view. Indeed, in English Wednesday is the longest day name, 9 characters...
[19 Nov 2009 17:51] Roland Volkmann
in version 5.1.39 and above this error doesn't exist any more.
[19 Nov 2009 18:13] Valeriy Kravchuk
Indeed, this is NOT repeatable with recent 5.0.x and 5.1.x versions:

77-52-12-228:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.42-debug Source distribution

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

mysql> set @@lc_time_names = 'de_DE';
Query OK, 0 rows affected (0.00 sec)

mysql> select dayname('2008-01-03');
+-----------------------+
| dayname('2008-01-03') |
+-----------------------+
| Donnerstag            |
+-----------------------+
1 row in set (0.00 sec)

mysql> create view v_test as select dayname('2008-01-03') as "Wochentag";
Query OK, 0 rows affected (0.25 sec)

mysql>  select * from v_test;
+------------+
| Wochentag  |
+------------+
| Donnerstag |
+------------+
1 row in set (0.00 sec)

...

77-52-12-228:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88-debug Source distribution

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

mysql> set @@lc_time_names = 'de_DE';
Query OK, 0 rows affected (0.00 sec)

mysql> select dayname('2008-01-03');
+-----------------------+
| dayname('2008-01-03') |
+-----------------------+
| Donnerstag            | 
+-----------------------+
1 row in set (0.00 sec)

mysql> create view v_test as select dayname('2008-01-03') as "Wochentag";
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from v_test;
+------------+
| Wochentag  |
+------------+
| Donnerstag | 
+------------+
1 row in set (0.00 sec)