The subtime() function returns an incorrect result when using the date type

[apollodafoni]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table lrr_test(`COL1` date);
insert into lrr_test values('2024-11-01');
insert into lrr_test values('2020-11-01');
select col1,subtime(col1,'12:00:01.341300') from lrr_test;

2. What did you expect to see? (Required)

Mysql：
+------------+---------------------------------+
| col1 | subtime(col1,'12:00:01.341300') |
+------------+---------------------------------+
| 2024-11-01 | 2024-10-31 11:59:58.658700 |
| 2020-11-01 | 2020-10-31 11:59:58.658700 |
+------------+---------------------------------+

3. What did you see instead (Required)

+------------+---------------------------------+
| col1 | subtime(col1,'12:00:01.341300') |
+------------+---------------------------------+
| 2024-11-01 | -12:00:01.341300 |
| 2020-11-01 | -12:00:01.341300 |
+------------+---------------------------------+

4. What is your TiDB version? (Required)

Release Version: v8.4.0
Edition: Community
Git Commit Hash: 2511e92
Git Branch: HEAD
UTC Build Time: 2024-10-27 16:16:04
GoVersion: go1.23.2
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[apollodafoni]

/severity major
/impact wrong-result

[windtalker]

Looks like in https://github.com/pingcap/tidb/blob/3c8f2f3517a4529b5162df69724df389a506aea3/pkg/expression/builtin_time.go#L4581-l4582
if the first argument is date type, it will be adjusted to duration type, not sure if it is on purpose or not. But since it's result is different from MySQL, I think we can treat it as a bug.

[apollodafoni]

/severity moderate

[apollodafoni]

/remove-severity major

[xzhangxian1008]

/assign

[apollodafoni]

/label fuzz/comp

[apollodafoni]

/label affects-7.5