ttl report error for some configuration

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (a int, b timestamp) ttl = b + interval "1" DAY;

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

no error and TTL should run normally.

3. What did you see instead (Required)

The TTL does not run successfully and we can see an error message:

[2024/03/15 17:47:44.757 +08:00] [ERROR] [worker.go:398] ["error occurs when invoking hook OnTimerEvent"] [groupID=ttl] [hookClass=tidb.ttl] [timerID=183245] [timerNamespace=default] [timerKey=/tidb/ttl/physical_table/242/242] [eventID=2933aeb402904e03abf9c8bfa5f3ee20] [requestRetry=0] [error="[types:1292]Incorrect datetime value: '_utf8'1''"] [retryAfter=10s]

4. What is your TiDB version? (Required)

[lcwangchao]

This error is introuded by #51701 because it uses types.ParseDurationValue to parse expire interval.

However for the format b + interval "1" DAY, the TTLInfo.IntervalExprStr will be stored as _utf8'1' not 1.

Then the types.ParseDurationValue returns an error.

And we can see date add expression is more complex than we thought, see below:

TiDB root@127.0.0.1:test> select from_unixtime(0) + interval "1.2" minute_second;
+-------------------------------------------------+
| from_unixtime(0) + interval "1.2" minute_second |
+-------------------------------------------------+
| 1970-01-01 08:01:02                             |
+-------------------------------------------------+
1 row in set
Time: 0.005s
TiDB root@127.0.0.1:test> select from_unixtime(0) + interval "1.2" minute;
+------------------------------------------+
| from_unixtime(0) + interval "1.2" minute |
+------------------------------------------+
| 1970-01-01 08:01:00                      |
+------------------------------------------+
1 row in set
Time: 0.004s
TiDB root@127.0.0.1:test> select from_unixtime(0) + interval "1.9" minute;
+------------------------------------------+
| from_unixtime(0) + interval "1.9" minute |
+------------------------------------------+
| 1970-01-01 08:01:00                      |
+------------------------------------------+
1 row in set
Time: 0.004s
TiDB root@127.0.0.1:test> select from_unixtime(0) + interval 1.9 minute;
+----------------------------------------+
| from_unixtime(0) + interval 1.9 minute |
+----------------------------------------+
| 1970-01-01 08:02:00                    |
+----------------------------------------+
1 row in set
Time: 0.003s

We can see that the string format "1.2" does not stand for 1.2 in number format, it means 1 and 2 . If the unit is minute_second, it means "1 minute and 2 seconds". And if the unit is minute, the second part will be ignored.

Mysql will also round the value to an integer when the input is number.

Passing interval value to types.ParseDurationValue directly is dangerous before we processing the value before it, so I' will revert PR #51701 before we find a better way to solve this issue.