Insert a special datetime with time_zone results in loss of precision

[Defined2014]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a datetime(3));
insert into t values ('2020-01-01 12:00:00.123456 +0600 PST'); # The format is Golang’s default format
insert into t values ('2020-01-01 12:00:00.123456 -0600 PST');
select * from t;

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

Got error during insert or keep result like 2020-01-01 12:00:00.123

MySQL will report error during insert.

3. What did you see instead (Required)

mysql> select * from t;
+-------------------------+
| a                       |
+-------------------------+
| 2020-01-01 12:00:00.123 |
| 2020-01-01 12:00:00.000 |
+-------------------------+
2 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

23953a7

[Defined2014]

Why the results are different in case of + and - is because code below.

tidb/pkg/types/time.go

Line 590 in 23953a7

if unicode.IsPunct(rune(s[i])) {

unicode.IsPunct('+') will return false which is difference between unicode.IsPunct('-').

[SeaRise]

/assign @SeaRise

[SeaRise]

use test;
drop table if exists t;
create table t(a varchar(50));
insert into t values ('2020-01-01 12:00:00.123456 -0600 PST');
select cast(a as datetime(3)) from t;

--tidb
+-------------------------+
| cast(a as datetime(3))  |
+-------------------------+
| 2020-01-01 12:00:00.000 |
+-------------------------+

--mysql
+-------------------------+
| cast(a as datetime(3))  |
+-------------------------+
| 2020-01-01 12:00:00.123 |
+-------------------------+

[SeaRise]

/assign @SeaRise

[SeaRise]

tiflash and tikv also have similar problems.
pingcap/tiflash#8754
tikv/tikv#16492

[SeaRise]

related codes are introduced by #9933 and #37820