Bug #105489 subtime function seems get wrong result

• Submitted: 8 Nov 2021 3:38
• Modified: 8 Nov 2021 4:38
• Reporter: Shenghui Wu
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.27, 5.7.35
• OS: Any
• CPU Architecture: Any

[8 Nov 2021 3:38] Shenghui Wu

Description:
Subtime function gets different result between literal and data from table.

drop table if exists t;
create table t(a date, b time);
insert into t values("2020-11-08", "00:00:01");
select subtime(a,b) from t;
select subtime(date "2020-11-08", time "00:00:01");

MySQL [test]> select subtime(a,b) from t;
+---------------------+
| subtime(a,b)        |
+---------------------+
| 2020-11-07 23:59:59 |
+---------------------+
1 row in set (0.000 sec)

MySQL [test]> select subtime(date "2020-11-08", time "00:00:01");
+---------------------------------------------+
| subtime(date "2020-11-08", time "00:00:01") |
+---------------------------------------------+
| -00:00:01                                   |
+---------------------------------------------+
1 row in set (0.000 sec)

The two SQL get different result. Seems "2020-11-07 23:59:59" is reasonable.

How to repeat:
See description.

[8 Nov 2021 4:38] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.
Observed that 5.7/8.0 affected.

regards,
Umesh