CREATE TABLE with TTL does not check the TTL 'expression' for composite time units

[mjonss]

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 "one + two" minute_second;

So I think both the issue needs to be fixed, i.e. check TTL 'expression' when create/alter table,
and changing the manual, from 'Expression' to 'Time string' or similar.

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

Failure, saying that "one + two" is not a minute_second expression.

The parser says it only accepts a literal,
while the documentation says Expression: TTL EqOpt TimeColumnName + INTERVAL Expression TimeUnit

3. What did you see instead (Required)

Table created without even a warning:

tidb> create table t (a int, b timestamp) ttl = b + interval "one + two" minute_second;
Query OK, 0 rows affected (0.08 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v7.6.0
Edition: Community
Git Commit Hash: 52794d985ba6325d75a714d4eaa0838d59425eb6
Git Branch: heads/refs/tags/v7.6.0
UTC Build Time: 2024-01-22 14:13:53
GoVersion: go1.21.5
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[lcwangchao]

Seems MySQL allows arbitrary string without reporting any error or warning:

mysql> select from_unixtime(0) + interval"one + two" minute_second;
+------------------------------------------------------+
| from_unixtime(0) + interval"one + two" minute_second |
+------------------------------------------------------+
| 1970-01-01 08:00:00                                  |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

I will update the severity of this issue to minor because TTL has the same behavior with expression.

[kennedy8312]

This seems to be a regression.
mysql> create table t (a int, b timestamp) ttl = b + interval "one + two" minute_second;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 39 near "ttl = b + interval "one + two" minute_second"
mysql> mysql> select tidb_ve;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0
Edition: Community
Git Commit Hash: 55f3b24
Git Branch: heads/refs/tags/v5.4.0
UTC Build Time: 2022-01-25 08:41:03
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[kennedy8312]

The behavior in tidb v5.4.0 seems to be as expected. Here is the result from mysql v8.3.0.

mysql> create table t (a int, b timestamp) ttl = b + interval "one + two" minute_second;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ttl = b + interval "one + two" minute_second' at line 1
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.3.0 |
+-----------+
1 row in set (0.00 sec)

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
The issue started to happen since v6.5.0.
Version v5.4.0 : Success
Version v6.0.0 : Success
Version v6.1.0 : Success
Version v6.2.0 : Success
Version v6.3.0 : Success
Version v6.4.0 : Success
Version v6.5.0 : Failure
Version v6.6.0 : Failure
Version v7.0.0 : Failure
Version v7.1.0 : Failure
Version v7.2.0 : Failure
Version v7.3.0 : Failure
Version v7.4.0 : Failure
Version v7.5.0 : Failure
Version v7.6.0 : Failure
Version v8.0.0 : Failure
Version nightly : Failure

[kennedy8312]

/label affects-8.1

[kennedy8312]

Regression Analysis
PR caused this regression: #39277
first bad commit: [2255898] parser: add syntax for ttl option in ddl (#39277)

[lcwangchao]

kennedy8312

It's not a regression because the first version that support TTL is v6.5.0 and before that, you cannot create any table with TTL options even with the "correct" expression.