Converting `decimal` to `datetime` or `timestamp` when insert 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 (a DATETIME(6));
INSERT INTO t1 VALUES (20010101100000.1234567);
CREATE TABLE t2(a TIMESTAMP);
INSERT INTO t2 VALUES (20010101100000.1234567);

2. What did you expect to see?
   In MySQL

mysql> CREATE TABLE t1 (a DATETIME(6));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (20010101100000.1234567);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE t2(a TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t2 VALUES (20010101100000.1234567);
Query OK, 1 row affected (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> CREATE TABLE t1 (a DATETIME(6));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (20010101100000.1234567);
ERROR 1105 (HY000): cannot convert datum from decimal to type datetime.

mysql> CREATE TABLE t2(a TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t2 VALUES (20010101100000.1234567);
ERROR 1105 (HY000): cannot convert datum from decimal to type timestamp.

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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[Debiancc]

hi @qw4990 ,i am new fish of golang & infrastructure software.
I fixed this issue in my local and tests was passed, but i have a question.
I know tidb is very take care performance, does convert decimal to string and then parse to DateTime is the best way?

[qw4990]

Thanks for your contribution. @Debiancc
Performance is important for TiDB, but correctness and compatibility are also important too.
Maybe we can do it right now, and optimize it later.