`unix_timestamp` 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.

select unix_timestamp("-1");
select unix_timestamp(cast("-1" as datetime));

2. What did you expect to see?
   In MySQL

mysql> select unix_timestamp("-1");
+----------------------+
| unix_timestamp("-1") |
+----------------------+
|             0.000000 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

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

3. What did you see instead?
   In TiDB

mysql> select unix_timestamp("-1");
+----------------------+
| unix_timestamp("-1") |
+----------------------+
|                 NULL |
+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select unix_timestamp(cast("-1" as datetime));
+----------------------------------------+
| unix_timestamp(cast("-1" as datetime)) |
+----------------------------------------+
|                                   NULL |
+----------------------------------------+
1 row in set, 1 warning (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.1-206-g303f3c904
Git Commit Hash: 303f3c90405260bdc6cfd2d746d37b79d2df785d
Git Branch: master
UTC Build Time: 2019-05-06 05:52:43
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[qw4990]

The reason is that when building unix_timestamp, TiDB uses cast to convert its arguments to date/time explicitly and this cast hides some errors that unix_timestamp needs.
When there is invalid argument, cast will handle this error and return NULL.
And when unix_timestamp get a NULL argument, it returns NULL directly.

Confirming this issue still exists in master:

select unix_timestamp("-1");
select unix_timestamp(cast("-1" as datetime));

..

mysql> select unix_timestamp("-1");
+----------------------+
| unix_timestamp("-1") |
+----------------------+
|                 NULL |
+----------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ChenPeng2013]

select unix_timestamp("2622-05-26 16:36:42.00");
select unix_timestamp("4330-11-01 16:07:37.00");
select unix_timestamp("4960-01-27 06:32:50.00");

tidb
"2622-05-26 16:36:42.00" < "4330-11-01 16:07:37.00"
unix_timestamp("2622-05-26 16:36:42.00") > unix_timestamp("4330-11-01 16:07:37.00")

mysql> select unix_timestamp("2622-05-26 16:36:42.00");
+------------------------------------------+
| unix_timestamp("2622-05-26 16:36:42.00") |
+------------------------------------------+
|                            2140966928.29 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("4330-11-01 16:07:37.00");
+------------------------------------------+
| unix_timestamp("4330-11-01 16:07:37.00") |
+------------------------------------------+
|                             713728162.16 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("4960-01-27 06:32:50.00");
+------------------------------------------+
| unix_timestamp("4960-01-27 06:32:50.00") |
+------------------------------------------+
|                            2123829601.45 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp
The valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0.

mysql> select unix_timestamp("2622-05-26 16:36:42.00");
+------------------------------------------+
| unix_timestamp("2622-05-26 16:36:42.00") |
+------------------------------------------+
|                                     0.00 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("4330-11-01 16:07:37.00");
+------------------------------------------+
| unix_timestamp("4330-11-01 16:07:37.00") |
+------------------------------------------+
|                                     0.00 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("4960-01-27 06:32:50.00");
+------------------------------------------+
| unix_timestamp("4960-01-27 06:32:50.00") |
+------------------------------------------+
|                                     0.00 |
+------------------------------------------+
1 row in set (0.00 sec)

[dveeden]

This looks related to #9861