日期解析函数与mysql执行不一致

[troy_wang (Ermazi)]

SELECT STR_TO_DATE(‘200442 Monday’, ‘%X%V %W’);

版本：v4.0.7

tidb无结果
mysql能正常解析

[zhenjiaogao]

这个问题的操作步骤是：
1、MySQL

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
+-----------------------------------------+
| STR_TO_DATE('200442 Monday', '%X%V %W') |
+-----------------------------------------+
| 2004-10-18                              |
+-----------------------------------------+
1 row in set (0.00 sec)

2、TiDB

MySQL [app]> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

MySQL [app]> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v4.0.8 |
+--------------------+
1 row in set (0.00 sec)

MySQL [app]>  SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
+-----------------------------------------+
| STR_TO_DATE('200442 Monday', '%X%V %W') |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL [app]> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

3、从上面的两个执行结果来看 TiDB 和 MySQL 的行为可能存在不一致的情况，如果有新的消息会继续跟帖回复~~

[zhenjiaogao]

该问题后续请关注下面的链接：

https://github.com/pingcap/tidb/pull/21887