pingcap / pingcap/tidb

`TIMESTAMPADD` does not return correct data type when parameter is DateTime type

Open
#39,213 1 comment 0 reactions 0 assignees View on GitHub
affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 affects-6.1 affects-6.2 affects-6.3 affects-6.4 affects-6.5 affects-6.6 affects-7.0 affects-7.1 affects-7.5 affects-8.1 may-affects-4.0 severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

This issue is found during #38003

`TIMESTAMPADD` should return different DataTypes according to the DataType of its parameters.

e.g.,
MySQL behavior:
```sql
mysql> create view v(result) as select timestampadd(second,1.1,cast('1995-01-05 06:32:20.859724' as datetime)) as result;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| result | datetime(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from v;
+-----------------------+
| result |
+-----------------------+
| 1995-01-05 06:32:22.1 |
+-----------------------+
1 row in set (0.00 sec)
```
TiDB behavior:
```sql
mysql> create view v(result) as select timestampadd(second,1.1,cast('1995-01-05 06:32:20.859724' as datetime)) as result;
Query OK, 0 rows affected (0.12 sec)

mysql> desc v;
+--------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+------+---------+-------+
| result | varchar(19) | NO | | NULL | |
+--------+-------------+------+------+---------+-------+
1 row in set (0.01 sec)

mysql> select * from v;
+---------------------+
| result |
+---------------------+
| 1995-01-05 06:32:22 |
+---------------------+
1 row in set (0.00 sec)
```

When using `TIMESTAMPADD`, TiDB always casts the parameter into String, and always returns a String result. This is not efficient and in-compatible with MySQL behavior.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.