| Bug #64023 | STR_TO_DATE / DATE_FORMAT combination gives wrong values for non 'en_*' charsets | ||
|---|---|---|---|
| Submitted: | 13 Jan 2012 12:57 | Modified: | 13 Jan 2012 15:02 |
| Reporter: | Daniel Walter | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.1.61, 5.5.16-log | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | charset, date_format, STR_TO_DATE | ||
[13 Jan 2012 15:02]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.61 on Mac OS X also:
macbook-pro: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 10
Server version: 5.1.61-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> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US |
+-----------------+
1 row in set (0.02 sec)
mysql> SELECT DATE_FORMAT('2009-10-02','%e. %M %Y'), STR_TO_DATE(
-> DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y');
+---------------------------------------+--------------------------------------------------------------------+
| DATE_FORMAT('2009-10-02','%e. %M %Y') | STR_TO_DATE(
DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y') |
+---------------------------------------+--------------------------------------------------------------------+
| 2. October 2009 | 2009-10-02 |
+---------------------------------------+--------------------------------------------------------------------+
1 row in set (0.08 sec)
mysql> SET lc_time_names = 'de_DE';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DATE_FORMAT('2009-10-02','%e. %M %Y'), STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y');
+---------------------------------------+--------------------------------------------------------------------+
| DATE_FORMAT('2009-10-02','%e. %M %Y') | STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y') |
+---------------------------------------+--------------------------------------------------------------------+
| 2. Oktober 2009 | NULL |
+---------------------------------------+--------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Error
Code: 1411
Message: Incorrect datetime value: '2. Oktober 2009' for function str_to_date
1 row in set (0.07 sec)
Description: Expected behaviour: mysql> SELECT @@lc_time_names; +-----------------+ | @@lc_time_names | +-----------------+ | en_US | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_FORMAT('2009-10-02','%e. %M %Y'), STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y'); +---------------------------------------+--------------------------------------------------------------------+ | DATE_FORMAT('2009-10-02','%e. %M %Y') | STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y') | +---------------------------------------+--------------------------------------------------------------------+ | 2. October 2009 | 2009-10-02 | +---------------------------------------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW WARNINGS; Empty set (0.00 sec) Buggy behaviour: mysql> SET lc_time_names = 'de_DE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT DATE_FORMAT('2009-10-02','%e. %M %Y'), STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y'); +---------------------------------------+--------------------------------------------------------------------+ | DATE_FORMAT('2009-10-02','%e. %M %Y') | STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y') | +---------------------------------------+--------------------------------------------------------------------+ | 2. Oktober 2009 | NULL | +---------------------------------------+--------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------+ | Warning | 1411 | Incorrect datetime value: '2. Oktober 2009' for function str_to_date | +---------+------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) How to repeat: SET lc_time_names = 'de_DE'; SELECT DATE_FORMAT('2009-10-02','%e. %M %Y'), STR_TO_DATE( DATE_FORMAT('2009-10-02', '%e. %M %Y'), '%e. %M %Y'); SHOW WARNINGS;