Cast date error

[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.

select cast(-1 as date);
select cast(-1 as datetime);
select cast(cast(-1 as decimal) as date);
select cast(cast(-1 as decimal) as datetime);
select cast(-0.1 as date);
select cast(-0.1 as datetime);

2. What did you expect to see?
   In Mysql

mysql> select cast(-1 as date);
+------------------+
| cast(-1 as date) |
+------------------+
| NULL             |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(-1 as datetime);
+----------------------+
| cast(-1 as datetime) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(cast(-1 as decimal) as date);
+-----------------------------------+
| cast(cast(-1 as decimal) as date) |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(cast(-1 as decimal) as datetime);
+---------------------------------------+
| cast(cast(-1 as decimal) as datetime) |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(-0.1 as date);
+--------------------+
| cast(-0.1 as date) |
+--------------------+
| NULL               |
+--------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select cast(-0.1 as datetime);
+------------------------+
| cast(-0.1 as datetime) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> select cast(-1 as date);
ERROR 1292 (22007): invalid time format: '-1'
mysql> select cast(-1 as datetime);
ERROR 1292 (22007): invalid time format: '-1'
mysql> select cast(cast(-1 as decimal) as date);
ERROR 1292 (22007): Incorrect datetime value: '-1'
mysql> select cast(cast(-1 as decimal) as datetime);
ERROR 1292 (22007): Incorrect datetime value: '-1'
mysql> select cast(-0.1 as date);
ERROR 1292 (22007): Incorrect datetime value: '-0.1'
mysql> select cast(-0.1 as datetime);
ERROR 1292 (22007): Incorrect datetime value: '-0.1'

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

[erjiaqing]

mysql> select cast("00:00:00" as datetime);select version();
+------------------------------+
| cast("00:00:00" as datetime) |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

mysql> select cast("00:00:00" as datetime);select version();
+------------------------------+
| cast("00:00:00" as datetime) |
+------------------------------+
| 0000-00-00 00:00:00          |
+------------------------------+
1 row in set (0.00 sec)

+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.7.25-TiDB-v3.0.0-beta.1-212-gf17a1155e |
+------------------------------------------+
1 row in set (0.00 sec)