Bug #87737 'addtime' acts inconsistently when the second parameter a 'datetime' value

• Submitted: 12 Sep 2017 12:41
• Modified: 12 Oct 2017 1:14
• Reporter: Chuan Du
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.7.17
• OS: MacOS
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[12 Sep 2017 12:41] Chuan Du

Description:
As is documented in https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_addtime ,
"""
ADDTIME(expr1,expr2)
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime  expression, and expr2 is a time expression. 

"expr2 is a TIME VALUE"
"""

The following SQL do not obey this rule:

mysql> select addtime(cast('2017-01-01 01:01:01' as datetime), cast('2017-01-01 01:01:01' as datetime));
+-------------------------------------------------------------------------------------------+
| addtime(cast('2017-01-01 01:01:01' as datetime), cast('2017-01-01 01:01:01' as datetime)) |
+-------------------------------------------------------------------------------------------+
| 2017-01-01 02:02:02                                                                       |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
mysql> create table t ( a datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values('2017-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> select addtime(a, a) from t;
+---------------+
| addtime(a, a) |
+---------------+
| NULL          |
+---------------+
1 row in set (0.00 sec)

mysql> select addtime(a, cast('2017-01-01 01:01:01' as datetime)) from t;
+-----------------------------------------------------+
| addtime(a, cast('2017-01-01 01:01:01' as datetime)) |
+-----------------------------------------------------+
| 2017-01-01 02:02:02                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
return NULL for queries like: "select addtime(cast('2017-01-01 01:01:01' as datetime), cast('2017-01-01 01:01:01' as datetime));"

[7 Oct 2017 0:30] MySQL Verification Team

Hi,

I do not agree this is a bug, as you wrote yourself manual clearly states that "... and expr2 is a time expression" and datetime is not a time expression.

mysql [localhost] {msandbox} (test) > select addtime(a, a) from t;
+---------------+
| addtime(a, a) |
+---------------+
| NULL          |
+---------------+
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (test) > select addtime(a, time(a)) from t;
+---------------------+
| addtime(a, time(a)) |
+---------------------+
| 2017-01-01 02:02:02 |
+---------------------+
1 row in set (0.00 sec)

so mysql is behaving as expected

kind regards
Bogdan

[12 Oct 2017 1:14] Chuan Du

Hi, Bogdan:

Thanks for your reply. There's still something I think is not right.  The result of expression 'cast('2017-01-01 01:01:01' as datetime)' is of 'DATETIME' type, not 'TIME', so according to the manual, I think 'select addtime(a, cast('2017-01-01 01:01:01' as datetime)) from t;' should return 'NULL', do you agree? 

mysql> select addtime(a, cast('2017-01-01 01:01:01' as datetime)) from t;
+-----------------------------------------------------+
| addtime(a, cast('2017-01-01 01:01:01' as datetime)) |
+-----------------------------------------------------+
| 2017-01-01 02:02:02                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)