Function SUBTIME should return NULL with a warning if its argument is an invalid time value.

[SunRunAway]

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 SUBTIME(-32073, 0);
SELECT SUBTIME(0, -32073);

2. What did you expect to see?

In MySQL 5.7 and MySQL 8.0,

mysql> SELECT SUBTIME(-32073, 0); show warnings;
+--------------------+
| SUBTIME(-32073, 0) |
+--------------------+
| NULL               |
+--------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '-32073' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(0, -32073); show warnings;
+--------------------+
| SUBTIME(0, -32073) |
+--------------------+
| NULL               |
+--------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '-32073' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB,

mysql> SELECT SUBTIME(-32073, 0);
ERROR 1292 (22007): Truncated incorrect time value: '-32073'
mysql> SELECT SUBTIME(0, -32073);
ERROR 1292 (22007): Truncated incorrect time value: '-32073'

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

[tcmichael]

Refered to https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html, many of time functions returns NULL when the arguments are invalid, except SUBTIME. Shall we decide which arguments are invalid, will SUBTIME returns NULL

[SunRunAway]

Hi, @tcmichael
TiDB aims to be compatible with MySQL 5.7, so SUBTIME is best to behave like it even if it is undocumented.