wrong result of timestampadd(month,1,date '2024-01-31')

[crazycs520]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The issue if from https://ask.pingcap.com/t/timestampadd-function/419.

select timestampadd(month,1,date '2024-01-31');

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

MySQL result is expected, as following:

> select timestampadd(month,1,date '2024-01-31');
+-----------------------------------------+
| timestampadd(month,1,date '2024-01-31') |
+-----------------------------------------+
| 2024-02-29                              |
+-----------------------------------------+

3. What did you see instead (Required)

> select timestampadd(month,1,date '2024-01-31');
+-----------------------------------------+
| timestampadd(month,1,date '2024-01-31') |
+-----------------------------------------+
| 2024-03-02                              |
+-----------------------------------------+

4. What is your TiDB version? (Required)

***************************[ 1. row ]***************************
tidb_version() | Release Version: v6.6.0-alpha-346-gd0d321f440
Edition: Community
Git Commit Hash: d0d321f440e265e6eb87731ba9fbf3611bf0dca2
Git Branch: master
UTC Build Time: 2023-02-04 00:17:06
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore

[XuHuaiyu]

I think the behavior of tidb is more reasonable. timestampadd(month,1,date '2024-03-30') and timestampadd(month,1,date '2024-03-31') get the same result in MySQL.

mysql>  select timestampadd(month,1,date '2024-03-30');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-30') |
+-----------------------------------------+
| 2024-04-30                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>  select timestampadd(month,1,date '2024-03-31');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-31') |
+-----------------------------------------+
| 2024-04-30                              |
+-----------------------------------------+
1 row in set (0.00 sec)

I reported a bug to MySQL https://bugs.mysql.com/bug.php?id=109941. Waiting for their verification.

[XuHuaiyu]

To fix this behavior, we can make the following modification:

diff --git a/expression/builtin_time.go b/expression/builtin_time.go
index e97f2ef862..be33c81805 100644
--- a/expression/builtin_time.go
+++ b/expression/builtin_time.go
@@ -6180,6 +6180,10 @@ func addUnitToTime(unit string, t time.Time, v float64) (time.Time, bool, error)
                        return tb, true, nil
                }
                tb = t.AddDate(0, int(v), 0)
+               // For corner case: timestampadd(month,1,date '2024-01-31') = "2024-02-29", timestampadd(month,1,date '2024-01-30') = "2024-02-29"
+               for tb.Month() != t.Month()+1 {
+                       tb = tb.AddDate(0, 0, -1)
+               }
        case "QUARTER":
                if !validAddMonth(v*3, t.Year(), int(t.Month())) {
                        return tb, true, nil

[XuHuaiyu]

As this bug report shows, it's a bug of MySQL, verified by the MySQL verification team.

[ti-chi-bot]

@xzhangxian1008: The label(s) may-affects-4.0 cannot be applied. These labels are supported: fuzz/sqlancer, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, ok-to-test, needs-ok-to-test, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1.

In response to this:

/remove-label may-affects-4.0

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[MimeLyc]

/remove-label may-affects-5.4

[ti-chi-bot]

@MimeLyc: These labels are not set on the issue: may-affects-5.4.

In response to this:

/remove-label may-affects-5.4

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[MimeLyc]

/remove-label may-affects-5.3

[ti-chi-bot]

@MimeLyc: The label(s) may-affects-5.3 cannot be applied. These labels are supported: fuzz/sqlancer, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, ok-to-test, needs-ok-to-test, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1.

In response to this:

/remove-label may-affects-5.3

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.