str_to_date is not compatible with MySQL

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table t1 (date char(30), format char(30) not null);
insert into t1 values
('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
('10:20:10AM', '%H:%i:%s%p');
select date,format,str_to_date(date, format) as str_to_date from t1;

2. What did you expect to see?
   In Mysql

mysql> create table t1 (date char(30), format char(30) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values
    -> ('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
    -> ('10:20:10AM', '%H:%i:%s%p');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select date,format,str_to_date(date, format) as str_to_date from t1;
+------------------------+----------------------+-------------+
| date                   | format               | str_to_date |
+------------------------+----------------------+-------------+
| 2003-01-02 10:11:12 PM | %Y-%m-%d %H:%i:%S %p | NULL        |
| 10:20:10AM             | %H:%i:%s%p           | NULL        |
+------------------------+----------------------+-------------+
2 rows in set, 4 warnings (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> create table t1 (date char(30), format char(30) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values
    -> ('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
    -> ('10:20:10AM', '%H:%i:%s%p');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select date,format,str_to_date(date, format) as str_to_date from t1;
+------------------------+----------------------+----------------------------+
| date                   | format               | str_to_date                |
+------------------------+----------------------+----------------------------+
| 2003-01-02 10:11:12 PM | %Y-%m-%d %H:%i:%S %p | 2003-01-02 22:11:12.000000 |
| 10:20:10AM             | %H:%i:%s%p           | 0000-00-00 10:20:10.000000 |
+------------------------+----------------------+----------------------------+
2 rows in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-227-g06183393e-dirty
Git Commit Hash: 06183393ed536aa248248b72d4bf9f5010947f66
Git Branch: master
UTC Build Time: 2019-03-15 08:22:53
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+