DATE_ADD function into VARCHAR column returns inconsistent results in fractional portion

[espresso98]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step

SET @@time_zone='+00:00';
SET @@timestamp=1000000000;
DROP TABLE IF EXISTS t1; 
CREATE TABLE t1 (a VARCHAR(32));
INSERT INTO t1 VALUES(DATE_ADD(CAST('2001-01-01 00:00:00' AS DATETIME), INTERVAL 1 SECOND));
INSERT INTO t1 VALUES(DATE_ADD(CAST('2001-01-01 00:00:00' AS DATETIME(6)), INTERVAL 1 SECOND));
INSERT INTO t1 VALUES(DATE_ADD(CAST('2001-01-01 00:00:00' AS DATETIME), INTERVAL 1.1 SECOND));
INSERT INTO t1 VALUES(DATE_ADD(CAST('2001-01-01 00:00:00' AS DATETIME(6)), INTERVAL 1.1 SECOND));
INSERT INTO t1 VALUES(DATE_ADD(CAST('00:00:00' AS TIME), INTERVAL 1.1 SECOND));
SELECT * FROM t1;
DROP TABLE t1;
SET @@timestamp=default;
SET @@time_zone=default;

2. What did you expect to see?

mysql> SELECT * FROM t1;
+----------------------------+
| a                          |
+----------------------------+
| 2001-01-01 00:00:01        |
| 2001-01-01 00:00:01.000000 |
| 2001-01-01 00:00:01.1      |
| 2001-01-01 00:00:01.100000 |
| 00:00:01.1                 |
+----------------------------+

3. What did you see instead

tidb> SELECT * FROM t1;
+----------------------------+
| a                          |
+----------------------------+
| 2001-01-01 00:00:01        |
| 2001-01-01 00:00:01        |
| 2001-01-01 00:00:01.100000 |
| 2001-01-01 00:00:01.100000 |
| 00:00:01                   |
+----------------------------+

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

[zanmato1984]

There are two categories of problems in your reported cases:

1. Precision loss for the fifth row.
2. Precision mismatch between TiDB and MySQL, but not actual loss.

To differentiate these two categories, I'm opening issue #34659 to focus on the precision loss, and keeping this one to track the precision mismatch. Also adjusting the severity accordingly.