# Copyright 2023 PingCAP, Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

mysql> drop table if exists test.t
mysql> drop table if exists test.t1
mysql> drop table if exists test.t2
mysql> drop table if exists test.t3
mysql> drop table if exists test.t4
mysql> create table if not exists test.t(col_string char(32))
mysql> create table if not exists test.t1(col_string char(32))
mysql> create table if not exists test.t2(col_string char(32))
mysql> create table if not exists test.t3(col_string char(32), col_int int(11))
mysql> create table if not exists test.t4(col_string char(32), col_double double, col_float float, col_decimal decimal(4,2))

mysql> insert into test.t values('20201212121212'), ('2020-12-12 12:12:12'), ('201212121212'), ('20-12-12 12-12-12'), ('2012-12-12 12-12')
mysql> insert into test.t values('20121212129'), ('20-12-12 12-12-9'), ('2012-12-12 12-9'), ('2012121212'), ('20-12-12 12-12'), ('2012-12-12 12'), ('201212129'), ('20-12-12 12-9'), ('20121212'), ('20-12-12 12'), ('2012-12-12')
mysql> insert into test.t values('2012129'), ('20-12-12 9'), ('2012-12-9'), ('201212'), ('20-12-12'), ('20-12-9'), ('2020-12-12 12:12:12..123456'), ('20-12-12.'), ('201212.0')

mysql> insert into test.t1 values('1'), ('12'), ('1212'), ('12-12'), ('2020121212'), ('202012121212')
mysql> insert into test.t1 values('20201312121212'), ('2020-13-12 12:12:12'), ('20201232121212'), ('2020-12-32 12:12:12'), ('20201212241212'), ('2020-12-12 24:12:12'), ('20201212126012'), ('2020-12-12 12:60:12'), ('20201212121260'), ('2020-12-12 12:12:60')
mysql> insert into test.t1 values('201312121212'), ('2013-12-12 12:12'), ('20-13-12 12:12:12'), ('201232121212'), ('2012-32-12 12:12'), ('20-12-32 12:12:12'), ('201212241212'), ('2012-12-24 12:12'), ('20-12-12 24:12:12'), ('201212126012'), ('2012-12-12 60:12'), ('20-12-12 12:60:12'), ('201212121260'), ('2012-12-12 12:60'), ('20-12-12 12:12:60')
mysql> insert into test.t1 values('20131212129'), ('2013-12-12 12-9'), ('20-13-12 12:12:9'), ('20123212129'), ('2012-32-12 12:9'), ('20-12-32 12:12:9'), ('20121260129'), ('2012-12-60 12:9'), ('20-12-12 60:12:9'), ('20121212609'), ('2012-12-12 60:9'), ('20-13-12 12:60:9')
mysql> insert into test.t1 values('2013121212'), ('2013-12-12 12'), ('20-13-12 12:12'), ('2012321212'), ('2012-32-12 12'), ('20-12-32 12:12'), ('2012126012'), ('2012-12-60 12'), ('20-12-12 60:12'), ('2012121260'), ('2012-12-12 60'), ('20-12-12 12:60')
mysql> insert into test.t1 values('201312129'), ('2013-12-12 9'), ('20-13-12 12:9'), ('201232129'), ('2012-32-13 9'), ('20-12-32 12:9'), ('201212609'), ('2012-12-60 9'), ('20-12-12 60:9')
mysql> insert into test.t1 values('20131212'), ('2013-12-12'), ('20-13-12 12'), ('20123212'), ('2012-32-12'), ('20-12-32 12'), ('20121232'), ('2012-12-32'), ('20-12-12 32')
mysql> insert into test.t1 values('2013129'), ('2013-12-9'), ('20-13-12 9'), ('2012329'), ('2012-32-9'), ('20-12-32 9')
mysql> insert into test.t1 values('201312'), ('20-13-12'), ('201232'), ('20-12-32'), ('20139'), ('20-13-9')

mysql> insert into test.t2 values('20201212121212.000001'), ('20201212121212.0001'), ('20201212121212.999999'),('20201212121212.0000001')
mysql> insert into test.t2 values('2020-12-12 12:12:12.000001'), ('2020-12-12 12:12:12.0001'), ('2020-12-12 12:12:12.999999'),('2020-12-12 12:12:12.0000001')
mysql> insert into test.t2 values('20121.000001'), ('20121.121212'), ('20121.999999'),('20121.0000001')
mysql> insert into test.t2 values('201212.000001'), ('2012122.121212'), ('201212.999999'),('201212.0000001')
mysql> insert into test.t2 values('2012121.000001'), ('20121221.121212'), ('2012121.999999'),('2012121.0000001')
mysql> insert into test.t2 values('20121212.000001'), ('201212212.121212'), ('20121212.999999'),('20121212.0000001')
mysql> insert into test.t2 values('201212121.000001'), ('2012122121.121212'), ('201212121.999999'),('201212121.0000001')
mysql> insert into test.t2 values('2012121212.000001'), ('20121221212.121212'), ('2012121212.999999'),('2012121212.0000001')
mysql> insert into test.t2 values('20121212121.000001'), ('20121212121.121212'), ('20121212121.999999'),('20121212121.0000001')
mysql> insert into test.t2 values('201212121212.000001'), ('201212121212.121212'), ('201212121212.999999'),('201212121212.0000001')
mysql> insert into test.t2 values('20121212121212.000001'), ('20121212121212.121212'), ('20121212121212.999999'),('20121212121212.0000001')
mysql> insert into test.t2 values('2012-12-12 12:12:12.000001'), ('2012-12-12 12:12:12.001'), ('2012-12-12 12:12:12.999999'), ('2012-12-12 12:12:12.0000001')
mysql> insert into test.t2 values('2012-12-12 12:12.000001'), ('2012-12-12 12:12.01'), ('2012-12-12 12:12.60'), ('2012-12-12 12:12.121'), ('2012-12-12 12:12.0000001')
mysql> insert into test.t2 values('2012-12-12 12.000001'), ('2012-12-12 12.01'), ('2012-12-12 12.60'), ('2012-12-12 12.121'), ('2012-12-12 12.0000001')
mysql> insert into test.t2 values('2012-12-12.000001'), ('2012-12-12.01'), ('2012-12-12.24'), ('2012-12-12.121'), ('2012-12-12.0000001')
mysql> insert into test.t2 values('2012-12.000001'), ('2012-12.01'), ('2012-12.24'), ('2012-12.121'), ('2012-12.0000001')
mysql> insert into test.t2 values('2012.000001'), ('2012.01'), ('2012.13'), ('2012.121'), ('2012.0000001')

mysql> insert into test.t3 values('20121212121212', 1), ('20121212', 9999), ('20121212121212.123456', 10)

mysql> insert into test.t4 values('20201212121212', 1.4, 1.4, 1.4), ('2020-12-12 12:12:12', 1.6, 1.6, 1.6)

mysql> alter table test.t set tiflash replica 1
mysql> alter table test.t1 set tiflash replica 1
mysql> alter table test.t2 set tiflash replica 1
mysql> alter table test.t3 set tiflash replica 1
mysql> alter table test.t4 set tiflash replica 1;

func> wait_table test t
func> wait_table test t1
func> wait_table test t2
func> wait_table test t3
func> wait_table test t4

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 2914289 day) from test.t;
+-----------------------------+--------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 2914289 day) |
+-----------------------------+--------------------------------------------+
| 20201212121212              | NULL                                       |
| 2020-12-12 12:12:12         | NULL                                       |
| 201212121212                | NULL                                       |
| 20-12-12 12-12-12           | NULL                                       |
| 2012-12-12 12-12            | 9992-01-01 12:12:00                        |
| 20121212129                 | NULL                                       |
| 20-12-12 12-12-9            | NULL                                       |
| 2012-12-12 12-9             | 9992-01-01 12:09:00                        |
| 2012121212                  | NULL                                       |
| 20-12-12 12-12              | NULL                                       |
| 2012-12-12 12               | 9992-01-01 12:00:00                        |
| 201212129                   | NULL                                       |
| 20-12-12 12-9               | NULL                                       |
| 20121212                    | 9992-01-01                                 |
| 20-12-12 12                 | NULL                                       |
| 2012-12-12                  | 9992-01-01                                 |
| 2012129                     | NULL                                       |
| 20-12-12 9                  | NULL                                       |
| 2012-12-9                   | 9991-12-29                                 |
| 201212                      | NULL                                       |
| 20-12-12                    | NULL                                       |
| 20-12-9                     | 9999-12-29                                 |
| 2020-12-12 12:12:12..123456 | NULL                                       |
| 20-12-12.                   | NULL                                       |
| 201212.0                    | NULL                                       |
+-----------------------------+--------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL -735250 day) from test.t;
+-----------------------------+--------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL -735250 day) |
+-----------------------------+--------------------------------------------+
| 20201212121212              | 0007-11-26 12:12:12                        |
| 2020-12-12 12:12:12         | 0007-11-26 12:12:12                        |
| 201212121212                | 0007-11-26 12:12:12                        |
| 20-12-12 12-12-12           | 0007-11-26 12:12:12                        |
| 2012-12-12 12-12            | NULL                                       |
| 20121212129                 | 0007-11-26 12:12:09                        |
| 20-12-12 12-12-9            | 0007-11-26 12:12:09                        |
| 2012-12-12 12-9             | NULL                                       |
| 2012121212                  | 0007-11-26 12:12:00                        |
| 20-12-12 12-12              | 0007-11-26 12:12:00                        |
| 2012-12-12 12               | NULL                                       |
| 201212129                   | 0007-11-26 12:09:00                        |
| 20-12-12 12-9               | 0007-11-26 12:09:00                        |
| 20121212                    | NULL                                       |
| 20-12-12 12                 | 0007-11-26 12:00:00                        |
| 2012-12-12                  | NULL                                       |
| 2012129                     | 0007-11-26 09:00:00                        |
| 20-12-12 9                  | 0007-11-26 09:00:00                        |
| 2012-12-9                   | NULL                                       |
| 201212                      | 0007-11-26                                 |
| 20-12-12                    | 0007-11-26                                 |
| 20-12-9                     | 0007-11-23                                 |
| 2020-12-12 12:12:12..123456 | 0007-11-26 12:12:12.123456                 |
| 20-12-12.                   | 0007-11-26                                 |
| 201212.0                    | 0007-11-26 00:00:00                        |
+-----------------------------+--------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 9999 year) from test.t;
+-----------------------------+------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 9999 year) |
+-----------------------------+------------------------------------------+
| 20201212121212              | NULL                                     |
| 2020-12-12 12:12:12         | NULL                                     |
| 201212121212                | NULL                                     |
| 20-12-12 12-12-12           | NULL                                     |
| 2012-12-12 12-12            | NULL                                     |
| 20121212129                 | NULL                                     |
| 20-12-12 12-12-9            | NULL                                     |
| 2012-12-12 12-9             | NULL                                     |
| 2012121212                  | NULL                                     |
| 20-12-12 12-12              | NULL                                     |
| 2012-12-12 12               | NULL                                     |
| 201212129                   | NULL                                     |
| 20-12-12 12-9               | NULL                                     |
| 20121212                    | NULL                                     |
| 20-12-12 12                 | NULL                                     |
| 2012-12-12                  | NULL                                     |
| 2012129                     | NULL                                     |
| 20-12-12 9                  | NULL                                     |
| 2012-12-9                   | NULL                                     |
| 201212                      | NULL                                     |
| 20-12-12                    | NULL                                     |
| 20-12-9                     | NULL                                     |
| 2020-12-12 12:12:12..123456 | NULL                                     |
| 20-12-12.                   | NULL                                     |
| 201212.0                    | NULL                                     |
+-----------------------------+------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL -2020 year) from test.t;
+-----------------------------+-------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL -2020 year) |
+-----------------------------+-------------------------------------------+
| 20201212121212              | 0000-00-00 12:12:12                       |
| 2020-12-12 12:12:12         | 0000-00-00 12:12:12                       |
| 201212121212                | 0000-00-00 12:12:12                       |
| 20-12-12 12-12-12           | 0000-00-00 12:12:12                       |
| 2012-12-12 12-12            | NULL                                      |
| 20121212129                 | 0000-00-00 12:12:09                       |
| 20-12-12 12-12-9            | 0000-00-00 12:12:09                       |
| 2012-12-12 12-9             | NULL                                      |
| 2012121212                  | 0000-00-00 12:12:00                       |
| 20-12-12 12-12              | 0000-00-00 12:12:00                       |
| 2012-12-12 12               | NULL                                      |
| 201212129                   | 0000-00-00 12:09:00                       |
| 20-12-12 12-9               | 0000-00-00 12:09:00                       |
| 20121212                    | NULL                                      |
| 20-12-12 12                 | 0000-00-00 12:00:00                       |
| 2012-12-12                  | NULL                                      |
| 2012129                     | 0000-00-00 09:00:00                       |
| 20-12-12 9                  | 0000-00-00 09:00:00                       |
| 2012-12-9                   | NULL                                      |
| 201212                      | 0000-00-00                                |
| 20-12-12                    | 0000-00-00                                |
| 20-12-9                     | 0000-00-00                                |
| 2020-12-12 12:12:12..123456 | 0000-00-00 12:12:12.123456                |
| 20-12-12.                   | 0000-00-00                                |
| 201212.0                    | 0000-00-00 00:00:00                       |
+-----------------------------+-------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL -2021 year) from test.t;
+-----------------------------+-------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL -2021 year) |
+-----------------------------+-------------------------------------------+
| 20201212121212              | NULL                                      |
| 2020-12-12 12:12:12         | NULL                                      |
| 201212121212                | NULL                                      |
| 20-12-12 12-12-12           | NULL                                      |
| 2012-12-12 12-12            | NULL                                      |
| 20121212129                 | NULL                                      |
| 20-12-12 12-12-9            | NULL                                      |
| 2012-12-12 12-9             | NULL                                      |
| 2012121212                  | NULL                                      |
| 20-12-12 12-12              | NULL                                      |
| 2012-12-12 12               | NULL                                      |
| 201212129                   | NULL                                      |
| 20-12-12 12-9               | NULL                                      |
| 20121212                    | NULL                                      |
| 20-12-12 12                 | NULL                                      |
| 2012-12-12                  | NULL                                      |
| 2012129                     | NULL                                      |
| 20-12-12 9                  | NULL                                      |
| 2012-12-9                   | NULL                                      |
| 201212                      | NULL                                      |
| 20-12-12                    | NULL                                      |
| 20-12-9                     | NULL                                      |
| 2020-12-12 12:12:12..123456 | NULL                                      |
| 20-12-12.                   | NULL                                      |
| 201212.0                    | NULL                                      |
+-----------------------------+-------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 1 day) from test.t;
+-----------------------------+--------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 1 day) |
+-----------------------------+--------------------------------------+
| 20201212121212              | 2020-12-13 12:12:12                  |
| 2020-12-12 12:12:12         | 2020-12-13 12:12:12                  |
| 201212121212                | 2020-12-13 12:12:12                  |
| 20-12-12 12-12-12           | 2020-12-13 12:12:12                  |
| 2012-12-12 12-12            | 2012-12-13 12:12:00                  |
| 20121212129                 | 2020-12-13 12:12:09                  |
| 20-12-12 12-12-9            | 2020-12-13 12:12:09                  |
| 2012-12-12 12-9             | 2012-12-13 12:09:00                  |
| 2012121212                  | 2020-12-13 12:12:00                  |
| 20-12-12 12-12              | 2020-12-13 12:12:00                  |
| 2012-12-12 12               | 2012-12-13 12:00:00                  |
| 201212129                   | 2020-12-13 12:09:00                  |
| 20-12-12 12-9               | 2020-12-13 12:09:00                  |
| 20121212                    | 2012-12-13                           |
| 20-12-12 12                 | 2020-12-13 12:00:00                  |
| 2012-12-12                  | 2012-12-13                           |
| 2012129                     | 2020-12-13 09:00:00                  |
| 20-12-12 9                  | 2020-12-13 09:00:00                  |
| 2012-12-9                   | 2012-12-10                           |
| 201212                      | 2020-12-13                           |
| 20-12-12                    | 2020-12-13                           |
| 20-12-9                     | 2020-12-10                           |
| 2020-12-12 12:12:12..123456 | 2020-12-13 12:12:12.123456           |
| 20-12-12.                   | 2020-12-13                           |
| 201212.0                    | 2020-12-13 00:00:00                  |
+-----------------------------+--------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 1 day) from test.t1;
+---------------------+--------------------------------------+
| col_string          | DATE_ADD(col_string, INTERVAL 1 day) |
+---------------------+--------------------------------------+
| 1                   | NULL                                 |
| 12                  | NULL                                 |
| 1212                | NULL                                 |
| 12-12               | NULL                                 |
| 2020121212          | NULL                                 |
| 202012121212        | NULL                                 |
| 20201312121212      | NULL                                 |
| 2020-13-12 12:12:12 | NULL                                 |
| 20201232121212      | NULL                                 |
| 2020-12-32 12:12:12 | NULL                                 |
| 20201212241212      | NULL                                 |
| 2020-12-12 24:12:12 | NULL                                 |
| 20201212126012      | NULL                                 |
| 2020-12-12 12:60:12 | NULL                                 |
| 20201212121260      | NULL                                 |
| 2020-12-12 12:12:60 | NULL                                 |
| 201312121212        | NULL                                 |
| 2013-12-12 12:12    | 2013-12-13 12:12:00                  |
| 20-13-12 12:12:12   | NULL                                 |
| 201232121212        | NULL                                 |
| 2012-32-12 12:12    | NULL                                 |
| 20-12-32 12:12:12   | NULL                                 |
| 201212241212        | NULL                                 |
| 2012-12-24 12:12    | 2012-12-25 12:12:00                  |
| 20-12-12 24:12:12   | NULL                                 |
| 201212126012        | NULL                                 |
| 2012-12-12 60:12    | NULL                                 |
| 20-12-12 12:60:12   | NULL                                 |
| 201212121260        | NULL                                 |
| 2012-12-12 12:60    | NULL                                 |
| 20-12-12 12:12:60   | NULL                                 |
| 20131212129         | NULL                                 |
| 2013-12-12 12-9     | 2013-12-13 12:09:00                  |
| 20-13-12 12:12:9    | NULL                                 |
| 20123212129         | NULL                                 |
| 2012-32-12 12:9     | NULL                                 |
| 20-12-32 12:12:9    | NULL                                 |
| 20121260129         | NULL                                 |
| 2012-12-60 12:9     | NULL                                 |
| 20-12-12 60:12:9    | NULL                                 |
| 20121212609         | NULL                                 |
| 2012-12-12 60:9     | NULL                                 |
| 20-13-12 12:60:9    | NULL                                 |
| 2013121212          | NULL                                 |
| 2013-12-12 12       | 2013-12-13 12:00:00                  |
| 20-13-12 12:12      | NULL                                 |
| 2012321212          | NULL                                 |
| 2012-32-12 12       | NULL                                 |
| 20-12-32 12:12      | NULL                                 |
| 2012126012          | NULL                                 |
| 2012-12-60 12       | NULL                                 |
| 20-12-12 60:12      | NULL                                 |
| 2012121260          | NULL                                 |
| 2012-12-12 60       | NULL                                 |
| 20-12-12 12:60      | NULL                                 |
| 201312129           | NULL                                 |
| 2013-12-12 9        | 2013-12-13 09:00:00                  |
| 20-13-12 12:9       | NULL                                 |
| 201232129           | NULL                                 |
| 2012-32-13 9        | NULL                                 |
| 20-12-32 12:9       | NULL                                 |
| 201212609           | NULL                                 |
| 2012-12-60 9        | NULL                                 |
| 20-12-12 60:9       | NULL                                 |
| 20131212            | 2013-12-13                           |
| 2013-12-12          | 2013-12-13                           |
| 20-13-12 12         | NULL                                 |
| 20123212            | NULL                                 |
| 2012-32-12          | NULL                                 |
| 20-12-32 12         | NULL                                 |
| 20121232            | NULL                                 |
| 2012-12-32          | NULL                                 |
| 20-12-12 32         | NULL                                 |
| 2013129             | NULL                                 |
| 2013-12-9           | 2013-12-10                           |
| 20-13-12 9          | NULL                                 |
| 2012329             | NULL                                 |
| 2012-32-9           | NULL                                 |
| 20-12-32 9          | NULL                                 |
| 201312              | NULL                                 |
| 20-13-12            | NULL                                 |
| 201232              | NULL                                 |
| 20-12-32            | NULL                                 |
| 20139               | NULL                                 |
| 20-13-9             | NULL                                 |
+---------------------+--------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 1 day) from test.t2;
+-----------------------------+--------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 1 day) |
+-----------------------------+--------------------------------------+
| 20201212121212.000001       | 2020-12-13 12:12:12.000001           |
| 20201212121212.0001         | 2020-12-13 12:12:12.000100           |
| 20201212121212.999999       | 2020-12-13 12:12:12.999999           |
| 20201212121212.0000001      | 2020-12-13 12:12:12                  |
| 2020-12-12 12:12:12.000001  | 2020-12-13 12:12:12.000001           |
| 2020-12-12 12:12:12.0001    | 2020-12-13 12:12:12.000100           |
| 2020-12-12 12:12:12.999999  | 2020-12-13 12:12:12.999999           |
| 2020-12-12 12:12:12.0000001 | 2020-12-13 12:12:12                  |
| 20121.000001                | 2020-12-02 00:00:01                  |
| 20121.121212                | 2020-12-02 12:12:12                  |
| 20121.999999                | NULL                                 |
| 20121.0000001               | 2020-12-02 00:00:00                  |
| 201212.000001               | 2020-12-13 00:00:01                  |
| 2012122.121212              | 2020-12-13 02:00:00                  |
| 201212.999999               | NULL                                 |
| 201212.0000001              | 2020-12-13 00:00:00                  |
| 2012121.000001              | 2020-12-13 01:00:00                  |
| 20121221.121212             | 2012-12-22 12:12:12                  |
| 2012121.999999              | 2020-12-13 01:00:00                  |
| 2012121.0000001             | 2020-12-13 01:00:00                  |
| 20121212.000001             | 2012-12-13 00:00:01                  |
| 201212212.121212            | 2020-12-13 21:02:12                  |
| 20121212.999999             | NULL                                 |
| 20121212.0000001            | 2012-12-13 00:00:00                  |
| 201212121.000001            | 2020-12-13 12:01:00                  |
| 2012122121.121212           | 2020-12-13 21:21:12                  |
| 201212121.999999            | NULL                                 |
| 201212121.0000001           | 2020-12-13 12:01:00                  |
| 2012121212.000001           | 2020-12-13 12:12:00                  |
| 20121221212.121212          | 2020-12-13 21:21:02.121212           |
| 2012121212.999999           | NULL                                 |
| 2012121212.0000001          | 2020-12-13 12:12:00                  |
| 20121212121.000001          | 2020-12-13 12:12:01.000001           |
| 20121212121.121212          | 2020-12-13 12:12:01.121212           |
| 20121212121.999999          | 2020-12-13 12:12:01.999999           |
| 20121212121.0000001         | 2020-12-13 12:12:01                  |
| 201212121212.000001         | 2020-12-13 12:12:12.000001           |
| 201212121212.121212         | 2020-12-13 12:12:12.121212           |
| 201212121212.999999         | 2020-12-13 12:12:12.999999           |
| 201212121212.0000001        | 2020-12-13 12:12:12                  |
| 20121212121212.000001       | 2012-12-13 12:12:12.000001           |
| 20121212121212.121212       | 2012-12-13 12:12:12.121212           |
| 20121212121212.999999       | 2012-12-13 12:12:12.999999           |
| 20121212121212.0000001      | 2012-12-13 12:12:12                  |
| 2012-12-12 12:12:12.000001  | 2012-12-13 12:12:12.000001           |
| 2012-12-12 12:12:12.001     | 2012-12-13 12:12:12.001000           |
| 2012-12-12 12:12:12.999999  | 2012-12-13 12:12:12.999999           |
| 2012-12-12 12:12:12.0000001 | 2012-12-13 12:12:12                  |
| 2012-12-12 12:12.000001     | 2012-12-13 12:12:01                  |
| 2012-12-12 12:12.01         | 2012-12-13 12:12:01                  |
| 2012-12-12 12:12.60         | NULL                                 |
| 2012-12-12 12:12.121        | NULL                                 |
| 2012-12-12 12:12.0000001    | 2012-12-13 12:12:01                  |
| 2012-12-12 12.000001        | 2012-12-13 12:01:00                  |
| 2012-12-12 12.01            | 2012-12-13 12:01:00                  |
| 2012-12-12 12.60            | NULL                                 |
| 2012-12-12 12.121           | NULL                                 |
| 2012-12-12 12.0000001       | 2012-12-13 12:01:00                  |
| 2012-12-12.000001           | 2012-12-13 01:00:00                  |
| 2012-12-12.01               | 2012-12-13 01:00:00                  |
| 2012-12-12.24               | NULL                                 |
| 2012-12-12.121              | NULL                                 |
| 2012-12-12.0000001          | 2012-12-13 01:00:00                  |
| 2012-12.000001              | 2012-12-02                           |
| 2012-12.01                  | 2012-12-02                           |
| 2012-12.24                  | 2012-12-25                           |
| 2012-12.121                 | NULL                                 |
| 2012-12.0000001             | 2012-12-02                           |
| 2012.000001                 | NULL                                 |
| 2012.01                     | NULL                                 |
| 2012.13                     | NULL                                 |
| 2012.121                    | NULL                                 |
| 2012.0000001                | NULL                                 |
+-----------------------------+--------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 20 day) from test.t;
+-----------------------------+---------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 20 day) |
+-----------------------------+---------------------------------------+
| 20201212121212              | 2021-01-01 12:12:12                   |
| 2020-12-12 12:12:12         | 2021-01-01 12:12:12                   |
| 201212121212                | 2021-01-01 12:12:12                   |
| 20-12-12 12-12-12           | 2021-01-01 12:12:12                   |
| 2012-12-12 12-12            | 2013-01-01 12:12:00                   |
| 20121212129                 | 2021-01-01 12:12:09                   |
| 20-12-12 12-12-9            | 2021-01-01 12:12:09                   |
| 2012-12-12 12-9             | 2013-01-01 12:09:00                   |
| 2012121212                  | 2021-01-01 12:12:00                   |
| 20-12-12 12-12              | 2021-01-01 12:12:00                   |
| 2012-12-12 12               | 2013-01-01 12:00:00                   |
| 201212129                   | 2021-01-01 12:09:00                   |
| 20-12-12 12-9               | 2021-01-01 12:09:00                   |
| 20121212                    | 2013-01-01                            |
| 20-12-12 12                 | 2021-01-01 12:00:00                   |
| 2012-12-12                  | 2013-01-01                            |
| 2012129                     | 2021-01-01 09:00:00                   |
| 20-12-12 9                  | 2021-01-01 09:00:00                   |
| 2012-12-9                   | 2012-12-29                            |
| 201212                      | 2021-01-01                            |
| 20-12-12                    | 2021-01-01                            |
| 20-12-9                     | 2020-12-29                            |
| 2020-12-12 12:12:12..123456 | 2021-01-01 12:12:12.123456            |
| 20-12-12.                   | 2021-01-01                            |
| 201212.0                    | 2021-01-01 00:00:00                   |
+-----------------------------+---------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, ADDDATE(col_string, 20) from test.t;
+-----------------------------+----------------------------+
| col_string                  | ADDDATE(col_string, 20)    |
+-----------------------------+----------------------------+
| 20201212121212              | 2021-01-01 12:12:12        |
| 2020-12-12 12:12:12         | 2021-01-01 12:12:12        |
| 201212121212                | 2021-01-01 12:12:12        |
| 20-12-12 12-12-12           | 2021-01-01 12:12:12        |
| 2012-12-12 12-12            | 2013-01-01 12:12:00        |
| 20121212129                 | 2021-01-01 12:12:09        |
| 20-12-12 12-12-9            | 2021-01-01 12:12:09        |
| 2012-12-12 12-9             | 2013-01-01 12:09:00        |
| 2012121212                  | 2021-01-01 12:12:00        |
| 20-12-12 12-12              | 2021-01-01 12:12:00        |
| 2012-12-12 12               | 2013-01-01 12:00:00        |
| 201212129                   | 2021-01-01 12:09:00        |
| 20-12-12 12-9               | 2021-01-01 12:09:00        |
| 20121212                    | 2013-01-01                 |
| 20-12-12 12                 | 2021-01-01 12:00:00        |
| 2012-12-12                  | 2013-01-01                 |
| 2012129                     | 2021-01-01 09:00:00        |
| 20-12-12 9                  | 2021-01-01 09:00:00        |
| 2012-12-9                   | 2012-12-29                 |
| 201212                      | 2021-01-01                 |
| 20-12-12                    | 2021-01-01                 |
| 20-12-9                     | 2020-12-29                 |
| 2020-12-12 12:12:12..123456 | 2021-01-01 12:12:12.123456 |
| 20-12-12.                   | 2021-01-01                 |
| 201212.0                    | 2021-01-01 00:00:00        |
+-----------------------------+----------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 5 year) from test.t;
+-----------------------------+---------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 5 year) |
+-----------------------------+---------------------------------------+
| 20201212121212              | 2025-12-12 12:12:12                   |
| 2020-12-12 12:12:12         | 2025-12-12 12:12:12                   |
| 201212121212                | 2025-12-12 12:12:12                   |
| 20-12-12 12-12-12           | 2025-12-12 12:12:12                   |
| 2012-12-12 12-12            | 2017-12-12 12:12:00                   |
| 20121212129                 | 2025-12-12 12:12:09                   |
| 20-12-12 12-12-9            | 2025-12-12 12:12:09                   |
| 2012-12-12 12-9             | 2017-12-12 12:09:00                   |
| 2012121212                  | 2025-12-12 12:12:00                   |
| 20-12-12 12-12              | 2025-12-12 12:12:00                   |
| 2012-12-12 12               | 2017-12-12 12:00:00                   |
| 201212129                   | 2025-12-12 12:09:00                   |
| 20-12-12 12-9               | 2025-12-12 12:09:00                   |
| 20121212                    | 2017-12-12                            |
| 20-12-12 12                 | 2025-12-12 12:00:00                   |
| 2012-12-12                  | 2017-12-12                            |
| 2012129                     | 2025-12-12 09:00:00                   |
| 20-12-12 9                  | 2025-12-12 09:00:00                   |
| 2012-12-9                   | 2017-12-09                            |
| 201212                      | 2025-12-12                            |
| 20-12-12                    | 2025-12-12                            |
| 20-12-9                     | 2025-12-09                            |
| 2020-12-12 12:12:12..123456 | 2025-12-12 12:12:12.123456            |
| 20-12-12.                   | 2025-12-12                            |
| 201212.0                    | 2025-12-12 00:00:00                   |
+-----------------------------+---------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 1 month) from test.t;
+-----------------------------+----------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 1 month) |
+-----------------------------+----------------------------------------+
| 20201212121212              | 2021-01-12 12:12:12                    |
| 2020-12-12 12:12:12         | 2021-01-12 12:12:12                    |
| 201212121212                | 2021-01-12 12:12:12                    |
| 20-12-12 12-12-12           | 2021-01-12 12:12:12                    |
| 2012-12-12 12-12            | 2013-01-12 12:12:00                    |
| 20121212129                 | 2021-01-12 12:12:09                    |
| 20-12-12 12-12-9            | 2021-01-12 12:12:09                    |
| 2012-12-12 12-9             | 2013-01-12 12:09:00                    |
| 2012121212                  | 2021-01-12 12:12:00                    |
| 20-12-12 12-12              | 2021-01-12 12:12:00                    |
| 2012-12-12 12               | 2013-01-12 12:00:00                    |
| 201212129                   | 2021-01-12 12:09:00                    |
| 20-12-12 12-9               | 2021-01-12 12:09:00                    |
| 20121212                    | 2013-01-12                             |
| 20-12-12 12                 | 2021-01-12 12:00:00                    |
| 2012-12-12                  | 2013-01-12                             |
| 2012129                     | 2021-01-12 09:00:00                    |
| 20-12-12 9                  | 2021-01-12 09:00:00                    |
| 2012-12-9                   | 2013-01-09                             |
| 201212                      | 2021-01-12                             |
| 20-12-12                    | 2021-01-12                             |
| 20-12-9                     | 2021-01-09                             |
| 2020-12-12 12:12:12..123456 | 2021-01-12 12:12:12.123456             |
| 20-12-12.                   | 2021-01-12                             |
| 201212.0                    | 2021-01-12 00:00:00                    |
+-----------------------------+----------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 12 hour) from test.t;
+-----------------------------+----------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 12 hour) |
+-----------------------------+----------------------------------------+
| 20201212121212              | 2020-12-13 00:12:12                    |
| 2020-12-12 12:12:12         | 2020-12-13 00:12:12                    |
| 201212121212                | 2020-12-13 00:12:12                    |
| 20-12-12 12-12-12           | 2020-12-13 00:12:12                    |
| 2012-12-12 12-12            | 2012-12-13 00:12:00                    |
| 20121212129                 | 2020-12-13 00:12:09                    |
| 20-12-12 12-12-9            | 2020-12-13 00:12:09                    |
| 2012-12-12 12-9             | 2012-12-13 00:09:00                    |
| 2012121212                  | 2020-12-13 00:12:00                    |
| 20-12-12 12-12              | 2020-12-13 00:12:00                    |
| 2012-12-12 12               | 2012-12-13 00:00:00                    |
| 201212129                   | 2020-12-13 00:09:00                    |
| 20-12-12 12-9               | 2020-12-13 00:09:00                    |
| 20121212                    | 2012-12-12 12:00:00                    |
| 20-12-12 12                 | 2020-12-13 00:00:00                    |
| 2012-12-12                  | 2012-12-12 12:00:00                    |
| 2012129                     | 2020-12-12 21:00:00                    |
| 20-12-12 9                  | 2020-12-12 21:00:00                    |
| 2012-12-9                   | 2012-12-09 12:00:00                    |
| 201212                      | 2020-12-12 12:00:00                    |
| 20-12-12                    | 2020-12-12 12:00:00                    |
| 20-12-9                     | 2020-12-09 12:00:00                    |
| 2020-12-12 12:12:12..123456 | 2020-12-13 00:12:12.123456             |
| 20-12-12.                   | 2020-12-12 12:00:00                    |
| 201212.0                    | 2020-12-12 12:00:00                    |
+-----------------------------+----------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 48 minute) from test.t;
+-----------------------------+------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 48 minute) |
+-----------------------------+------------------------------------------+
| 20201212121212              | 2020-12-12 13:00:12                      |
| 2020-12-12 12:12:12         | 2020-12-12 13:00:12                      |
| 201212121212                | 2020-12-12 13:00:12                      |
| 20-12-12 12-12-12           | 2020-12-12 13:00:12                      |
| 2012-12-12 12-12            | 2012-12-12 13:00:00                      |
| 20121212129                 | 2020-12-12 13:00:09                      |
| 20-12-12 12-12-9            | 2020-12-12 13:00:09                      |
| 2012-12-12 12-9             | 2012-12-12 12:57:00                      |
| 2012121212                  | 2020-12-12 13:00:00                      |
| 20-12-12 12-12              | 2020-12-12 13:00:00                      |
| 2012-12-12 12               | 2012-12-12 12:48:00                      |
| 201212129                   | 2020-12-12 12:57:00                      |
| 20-12-12 12-9               | 2020-12-12 12:57:00                      |
| 20121212                    | 2012-12-12 00:48:00                      |
| 20-12-12 12                 | 2020-12-12 12:48:00                      |
| 2012-12-12                  | 2012-12-12 00:48:00                      |
| 2012129                     | 2020-12-12 09:48:00                      |
| 20-12-12 9                  | 2020-12-12 09:48:00                      |
| 2012-12-9                   | 2012-12-09 00:48:00                      |
| 201212                      | 2020-12-12 00:48:00                      |
| 20-12-12                    | 2020-12-12 00:48:00                      |
| 20-12-9                     | 2020-12-09 00:48:00                      |
| 2020-12-12 12:12:12..123456 | 2020-12-12 13:00:12.123456               |
| 20-12-12.                   | 2020-12-12 00:48:00                      |
| 201212.0                    | 2020-12-12 00:48:00                      |
+-----------------------------+------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL 48 second) from test.t;
+-----------------------------+------------------------------------------+
| col_string                  | DATE_ADD(col_string, INTERVAL 48 second) |
+-----------------------------+------------------------------------------+
| 20201212121212              | 2020-12-12 12:13:00                      |
| 2020-12-12 12:12:12         | 2020-12-12 12:13:00                      |
| 201212121212                | 2020-12-12 12:13:00                      |
| 20-12-12 12-12-12           | 2020-12-12 12:13:00                      |
| 2012-12-12 12-12            | 2012-12-12 12:12:48                      |
| 20121212129                 | 2020-12-12 12:12:57                      |
| 20-12-12 12-12-9            | 2020-12-12 12:12:57                      |
| 2012-12-12 12-9             | 2012-12-12 12:09:48                      |
| 2012121212                  | 2020-12-12 12:12:48                      |
| 20-12-12 12-12              | 2020-12-12 12:12:48                      |
| 2012-12-12 12               | 2012-12-12 12:00:48                      |
| 201212129                   | 2020-12-12 12:09:48                      |
| 20-12-12 12-9               | 2020-12-12 12:09:48                      |
| 20121212                    | 2012-12-12 00:00:48                      |
| 20-12-12 12                 | 2020-12-12 12:00:48                      |
| 2012-12-12                  | 2012-12-12 00:00:48                      |
| 2012129                     | 2020-12-12 09:00:48                      |
| 20-12-12 9                  | 2020-12-12 09:00:48                      |
| 2012-12-9                   | 2012-12-09 00:00:48                      |
| 201212                      | 2020-12-12 00:00:48                      |
| 20-12-12                    | 2020-12-12 00:00:48                      |
| 20-12-9                     | 2020-12-09 00:00:48                      |
| 2020-12-12 12:12:12..123456 | 2020-12-12 12:13:00.123456               |
| 20-12-12.                   | 2020-12-12 00:00:48                      |
| 201212.0                    | 2020-12-12 00:00:48                      |
+-----------------------------+------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL col_int day) from test.t3;
+-----------------------+--------------------------------------------+
| col_string            | DATE_ADD(col_string, INTERVAL col_int day) |
+-----------------------+--------------------------------------------+
| 20121212121212        | 2012-12-13 12:12:12                        |
| 20121212              | 2040-04-28                                 |
| 20121212121212.123456 | 2012-12-22 12:12:12.123456                 |
+-----------------------+--------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD('20121212121212.123456', INTERVAL col_int day) from test.t3;
+-----------------------+---------------------------------------------------------+
| col_string            | DATE_ADD('20121212121212.123456', INTERVAL col_int day) |
+-----------------------+---------------------------------------------------------+
| 20121212121212        | 2012-12-13 12:12:12.123456                              |
| 20121212              | 2040-04-28 12:12:12.123456                              |
| 20121212121212.123456 | 2012-12-22 12:12:12.123456                              |
+-----------------------+---------------------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD('20121212121212.123456', INTERVAL 1 day) from test.t3;
+-----------------------+---------------------------------------------------+
| col_string            | DATE_ADD('20121212121212.123456', INTERVAL 1 day) |
+-----------------------+---------------------------------------------------+
| 20121212121212        | 2012-12-13 12:12:12.123456                        |
| 20121212              | 2012-12-13 12:12:12.123456                        |
| 20121212121212.123456 | 2012-12-13 12:12:12.123456                        |
+-----------------------+---------------------------------------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, DATE_ADD(col_string, INTERVAL col_double day) as double_day, DATE_ADD(col_string, INTERVAL col_float day) as float_day, DATE_ADD(col_string, INTERVAL col_decimal day) as decimal_day  from test.t4;
+---------------------+---------------------+---------------------+---------------------+
| col_string          | double_day          | float_day           | decimal_day         |
+---------------------+---------------------+---------------------+---------------------+
| 20201212121212      | 2020-12-13 12:12:12 | 2020-12-13 12:12:12 | 2020-12-13 12:12:12 |
| 2020-12-12 12:12:12 | 2020-12-14 12:12:12 | 2020-12-14 12:12:12 | 2020-12-14 12:12:12 |
+---------------------+---------------------+---------------------+---------------------+

mysql> set tidb_enforce_mpp=1; set @@session.tidb_isolation_read_engines = "tiflash"; select col_string, ADDDATE(col_string, INTERVAL col_double day) as double_day, ADDDATE(col_string, INTERVAL col_float day) as float_day, ADDDATE(col_string, INTERVAL col_decimal day) as decimal_day  from test.t4;
+---------------------+---------------------+---------------------+---------------------+
| col_string          | double_day          | float_day           | decimal_day         |
+---------------------+---------------------+---------------------+---------------------+
| 20201212121212      | 2020-12-13 12:12:12 | 2020-12-13 12:12:12 | 2020-12-13 12:12:12 |
| 2020-12-12 12:12:12 | 2020-12-14 12:12:12 | 2020-12-14 12:12:12 | 2020-12-14 12:12:12 |
+---------------------+---------------------+---------------------+---------------------+