Insert rounds DATETIME/TIMESTAMP fractions instead of truncating

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (dt datetime, dt6 datetime(6), ts timestamp, ts6 timestamp(6));
insert into t values (now(6), now(6), now(6), now(6));
select sleep(0.5);
insert into t values (now(6), now(6), now(6), now(6));
insert into t values (now(), now(), now(), now());
select sleep(0.5);
insert into t values (now(), now(), now(), now());
select * from t;

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

FSP to be truncated

3. What did you see instead (Required)

FSP was rounded, causing issues like inserting '2022-12-31 23:59:59.501011' into a DATETIME or TIMESTAMP column results in '2023-01-01 00:00:00'.

+---------------------+----------------------------+---------------------+----------------------------+
| dt                  | dt6                        | ts                  | ts6                        |
+---------------------+----------------------------+---------------------+----------------------------+
| 2024-10-01 17:04:39 | 2024-10-01 17:04:38.596766 | 2024-10-01 17:04:39 | 2024-10-01 17:04:38.596766 | <= Rounded UP dt and ts!
| 2024-10-01 17:04:39 | 2024-10-01 17:04:39.106945 | 2024-10-01 17:04:39 | 2024-10-01 17:04:39.106945 |
| 2024-10-01 17:04:39 | 2024-10-01 17:04:39.000000 | 2024-10-01 17:04:39 | 2024-10-01 17:04:39.000000 |
| 2024-10-01 17:04:39 | 2024-10-01 17:04:39.000000 | 2024-10-01 17:04:39 | 2024-10-01 17:04:39.000000 | <= NOW() does truncate!
+---------------------+----------------------------+---------------------+----------------------------+

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v8.3.0
Edition: Community
Git Commit Hash: 1a0c3ac3292fff7742faa0c00a662ccb66ba40db
Git Branch: HEAD
UTC Build Time: 2024-08-20 10:23:00
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[mjonss]

Also reported for MySQL, so at least we are MySQL compatible...

[mjonss]

I think it is as intended :( and accordingly to MySQL Manual even in the SQL standard:
https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

[dveeden]

@mjonss does that mean we can close this issue?

[mjonss]

Yes, closing this, with a reference to #29406.