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:
None 
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
Triage: Needs Triage: D2 (Serious)

[13 Jan 2012 12:57] Daniel Walter
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;
[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)