dateAdd function bug

[mengxin9014]

Bug Report

1. Minimal reproduce step (Required)

drop table t;
create table t(a char(32));
insert into t values('20129');
select a, DATE_ADD(a, INTERVAL 1 day ) from t ; 

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

+------------+------------------------------+
| a          | DATE_ADD(a, INTERVAL 1 day ) |
+------------+------------------------------+
| 20129      | 2020-12-10                   |
+------------+------------------------------+

3. What did you see instead (Required)

+------------+------------------------------+
| a          | DATE_ADD(a, INTERVAL 1 day ) |
+------------+------------------------------+
| 20129      | 2020-12-10 00:00:00          |
+------------+------------------------------+

4. What is your TiDB version? (Required)

v5.1.1

[aytrack]

The return value depends on the arguments:
DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts).
DATETIME` if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS.
String otherwise.

The returning type is wrong.

[aytrack]

more cases, seems not only the type problem

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = 20201210;
Empty set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast(20201210 as char);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast(20201210 as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

mysql> select DATE_ADD('20129', INTERVAL 1 day )  = 20201210;
+------------------------------------------------+
| DATE_ADD('20129', INTERVAL 1 day )  = 20201210 |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.04 sec)

mysql> select DATE_ADD('201209', INTERVAL 1 day )  = 20201210;
+-------------------------------------------------+
| DATE_ADD('201209', INTERVAL 1 day )  = 20201210 |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
1 row in set (0.04 sec)


mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = '20201210';
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = '2020-12-10';
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.05 sec)



mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast('2020-12-10' as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

mysql> select * from t where DATE_ADD(a, INTERVAL 1 day )  = cast('20201210' as date);
+-------+
| a     |
+-------+
| 20129 |
+-------+
1 row in set (0.04 sec)

[aytrack]

degrade to major because most common usages was correct

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.