tidb
tidb copied to clipboard
Published
20 hours ago •
pingcap
pingcap
`TIMESTAMPADD` does not return correct number of fractional digit in datetime expression
Bug Report
1. Minimal reproduce step
SET sql_mode='';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (s1 DATETIME(2));
INSERT INTO t1 VALUES (TIMESTAMP '0001-01-01 00:00:00.05');
SELECT TIMESTAMPADD(second, 1, s1) FROM t1;
DROP TABLE t1;
SELECT TIMESTAMPADD(second, 1.1, '2011-05-18 16:17:11.0');
SET sql_mode=default;
2. What did you expect to see?
mysql> SELECT TIMESTAMPADD(second, 1, s1) FROM t1;
+-----------------------------+
| TIMESTAMPADD(second, 1, s1) |
+-----------------------------+
| 0001-01-01 00:00:01.05 |
+-----------------------------+
mysql> SELECT TIMESTAMPADD(second, 1.1, '2011-05-18 16:17:11.0');
+----------------------------------------------------+
| TIMESTAMPADD(second, 1.1, '2011-05-18 16:17:11.0') |
+----------------------------------------------------+
| 2011-05-18 16:17:12.100000 |
+----------------------------------------------------+
3. What did you see instead
tidb> SELECT TIMESTAMPADD(second, 1, s1) FROM t1;
+-----------------------------+
| TIMESTAMPADD(second, 1, s1) |
+-----------------------------+
| 0001-01-01 00:00:01 |
+-----------------------------+
tidb> SELECT TIMESTAMPADD(second, 1.1, '2011-05-18 16:17:11.0');
+----------------------------------------------------+
| TIMESTAMPADD(second, 1.1, '2011-05-18 16:17:11.0') |
+----------------------------------------------------+
| 2011-05-18 16:17:12 |
+----------------------------------------------------+
4. What is your TiDB version?
tidb_version(): Release Version: v5.5.0-alpha-105-gaabd4e04d
Edition: Community
Git Commit Hash: aabd4e04d994eb91663abaa80865daec4cf970a6
Git Branch: master
UTC Build Time: 2022-01-13 05:36:36
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
fsp lost
MySQL [email protected]:test> SELECT TIMESTAMPADD(second, 1, "2021-07-08 16:30:01.0001");
+-----------------------------------------------------+
| TIMESTAMPADD(second, 1, "2021-07-08 16:30:01.0001") |
+-----------------------------------------------------+
| 2021-07-08 16:30:02 |
+-----------------------------------------------------+
