timestampadd may get stuck when subtracting months

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select timestampadd(month,-10,date'2021-09-01');

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

+------------------------------------------+
| timestampadd(month,-10,date'2021-09-01') |
+------------------------------------------+
| 2020-11-01                               |
+------------------------------------------+

3. What did you see instead (Required)

The query is stuck.

goroutine 18009 [runnable]:
time.Date(0xfffffffffff506dd?, 0x3?, 0x19?, 0x1?, 0x0?, 0x0?, 0x0?, 0x953e620?)
        /usr/local/go/src/time/time.go:1467 +0x4f3
time.Time.AddDate({0x0, 0xffffeb5c2c9599b9, 0x953e620}, 0x0, 0x0, 0xffffffffffffffff)
        /usr/local/go/src/time/time.go:946 +0xdb
github.com/pingcap/tidb/pkg/expression.addUnitToTime({0x59a98c3, 0x5}, {0xc003b5d400?, 0x0?, 0x953e620?}, 0x1?)
        /workspace/source/tidb/pkg/expression/builtin_time.go:6197 +0x8db
github.com/pingcap/tidb/pkg/expression.(*builtinTimestampAddSig).evalString(0xc002fdfc30, {0x0?, 0xc0031b3490?})
        /workspace/source/tidb/pkg/expression/builtin_time.go:6235 +0x187
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).EvalString(...)
        /workspace/source/tidb/pkg/expression/scalar_function.go:464
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).Eval(0xc0031b3570, {0x0?, 0x6353540?})
        /workspace/source/tidb/pkg/expression/scalar_function.go:423 +0x2f5
github.com/pingcap/tidb/pkg/expression.foldConstant({0x63e0600?, 0xc0031b3570?})
        /workspace/source/tidb/pkg/expression/constant_fold.go:232 +0xa25
github.com/pingcap/tidb/pkg/expression.FoldConstant({0x63e0600, 0xc0031b3570})
        /workspace/source/tidb/pkg/expression/constant_fold.go:40 +0x25
github.com/pingcap/tidb/pkg/expression.newFunctionImpl({0x63e1f60, 0xc003b5d400}, 0x1, {0xc002f04e18?, 0xc001ad09c8?}, 0xc002fd9d80, 0x52d30c0?, {0xc003288140, 0x3, 0x4})
        /workspace/source/tidb/pkg/expression/scalar_function.go:252 +0x9a5
github.com/pingcap/tidb/pkg/expression.NewFunction(...)
        /workspace/source/tidb/pkg/expression/scalar_function.go:288
github.com/pingcap/tidb/pkg/planner/core.(*expressionRewriter).newFunctionWithInit(0xc002a25b20, {0xc002f04e18?, 0xc002f04e18?}, 0xc?, 0x1c0fac5?, {0xc003288140?, 0x2?, 0x10?})
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:1408 +0xcf
github.com/pingcap/tidb/pkg/planner/core.(*expressionRewriter).newFunction(...)
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:1421
github.com/pingcap/tidb/pkg/planner/core.(*expressionRewriter).funcCallToExpression(0xc002a25b20, 0xc002fd9d40)
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:2142 +0xe97
github.com/pingcap/tidb/pkg/planner/core.(*expressionRewriter).Leave(0xc002a25b20, {0x6375940?, 0xc002fd9d40?})
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:1251 +0x6ca
github.com/pingcap/tidb/pkg/parser/ast.(*FuncCallExpr).Accept(0xc002fd4680?, {0x63453d0, 0xc002a25b20})
        /workspace/source/tidb/pkg/parser/ast/functions.go:584 +0x16f
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).rewriteExprNode(0xc002b5c000, 0xc002a25b20, {0x638c130?, 0xc002fd9d40?}, 0x1)
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:201 +0x119
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).rewriteWithPreprocess(0xc002b5c000, {0x6368d60?, 0xc002fceff0?}, {0x638c130, 0xc002fd9d40}, {0x63b1be0?, 0xc002fd4680?}, 0x0, 0x0, 0x1, ...)
        /workspace/source/tidb/pkg/planner/core/expression_rewriter.go:146 +0x170
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildProjection(0xc002b5c000, {0x6368d60, 0xc002fceff0}, {0x63b1be0?, 0xc002fd4680}, {0xc001570bd0, 0x1, 0x1?}, 0x59a6c8e?, 0x0, ...)
        /workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:1804 +0x606
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildSelect(0xc002b5c000, {0x6368d60, 0xc002fceff0}, 0xc002fd9e60)
        /workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:4597 +0x14a5
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).Build(0xc002b5c000, {0x6368d60?, 0xc002fceff0?}, {0x6375ed0?, 0xc002fd9e60?})
        /workspace/source/tidb/pkg/planner/core/planbuilder.go:859 +0x367
github.com/pingcap/tidb/pkg/planner.buildLogicalPlan({0x6368d60, 0xc002fceff0}, {0x63e1f60, 0xc003b5d400}, {0x6375ed0, 0xc002fd9e60}, 0xc002b5c000)
        /workspace/source/tidb/pkg/planner/optimize.go:576 +0x165
github.com/pingcap/tidb/pkg/planner.optimize({0x6368d60, 0xc002fceff0}, {0x63e1f60?, 0xc003b5d400}, {0x6375ed0?, 0xc002fd9e60?}, {0x63a5c68, 0xc002fcf020})
        /workspace/source/tidb/pkg/planner/optimize.go:494 +0x466
github.com/pingcap/tidb/pkg/planner.Optimize({0x6368d60, 0xc002fceff0}, {0x63e1f60, 0xc003b5d400}, {0x6375ed0, 0xc002fd9e60}, {0x63a5c68, 0xc002fcf020})
        /workspace/source/tidb/pkg/planner/optimize.go:352 +0x18c5
github.com/pingcap/tidb/pkg/executor.(*Compiler).Compile(0xc001ad2918, {0x6368d60?, 0xc002fceff0?}, {0x637d3e8?, 0xc002fd9e60})
        /workspace/source/tidb/pkg/executor/compiler.go:98 +0x44d
github.com/pingcap/tidb/pkg/session.(*session).ExecuteStmt(0xc003b5d400, {0x6368d60?, 0xc002fceff0?}, {0x637d3e8?, 0xc002fd9e60?})
        /workspace/source/tidb/pkg/session/session.go:2221 +0x754
github.com/pingcap/tidb/pkg/server.(*TiDBContext).ExecuteStmt(0xc004602900, {0x6368d60, 0xc002fceff0}, {0x637d3e8?, 0xc002fd9e60})
        /workspace/source/tidb/pkg/server/driver_tidb.go:292 +0xa7
github.com/pingcap/tidb/pkg/server.(*clientConn).handleStmt(0xc0030afba0, {0x6368d98, 0xc002fe3130}, {0x637d3e8, 0xc002fd9e60}, {0x0, 0x0, 0x0}, 0x1)
        /workspace/source/tidb/pkg/server/conn.go:2056 +0x189
github.com/pingcap/tidb/pkg/server.(*clientConn).handleQuery(0xc0030afba0, {0x6368d98, 0xc002fe3130}, {0xc002f04e11, 0x2f})
        /workspace/source/tidb/pkg/server/conn.go:1823 +0x9ac
github.com/pingcap/tidb/pkg/server.(*clientConn).dispatch(0xc0030afba0, {0x6368d60?, 0xc002a13ef0?}, {0xc002f04e10, 0x30, 0x30})
        /workspace/source/tidb/pkg/server/conn.go:1310 +0xf8b
github.com/pingcap/tidb/pkg/server.(*clientConn).Run(0xc0030afba0, {0x6368d60, 0xc002a13ef0})
        /workspace/source/tidb/pkg/server/conn.go:1083 +0x53e
github.com/pingcap/tidb/pkg/server.(*Server).onConn(0xc00333a700?, 0xc0030afba0)
        /workspace/source/tidb/pkg/server/server.go:736 +0x89d
created by github.com/pingcap/tidb/pkg/server.(*Server).startNetworkListener in goroutine 16968
        /workspace/source/tidb/pkg/server/server.go:554 +0x78d

4. What is your TiDB version? (Required)

v7.5.2

[pcqz]

/sig execution

[XuHuaiyu]

reproduced with nightly 6854e60

[2024/07/25 17:14:20.311 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=60.05157036s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]
[2024/07/25 17:15:20.410 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=120.150923773s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]
[2024/07/25 17:16:20.411 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=180.151140731s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]
[2024/07/25 17:17:20.510 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=240.250726487s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]
[2024/07/25 17:18:20.511 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=300.251207451s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]
[2024/07/25 17:19:20.610 +08:00] [WARN] [expensivequery.go:145] [expensive_query] [cost_time=360.350954777s] [conn=2097154] [user=root] [database=test] [txn_start_ts=0] [mem_max="0 Bytes (0 Bytes)"] [sql="select timestampadd(month,-10,date'2021-09-01')"] [session_alias=]

[XuHuaiyu]

[XuHuaiyu]

introduced by https://github.com/pingcap/tidb/pull/53101/files#diff-8afad47f4e56fe04314b81b9eb32c20d408309037f40b04c803ea64a12646d21R6239

[xzhangxian1008]

/type critical

[ti-chi-bot]

@xzhangxian1008: The label(s) type/critical cannot be applied, because the repository doesn't have them.

In response to this:

/type critical

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.

[seiya-annie]

/report customer