pingcap / pingcap/tidb

Cached tables CREATE TABLE semantics are inconsistent

Open
#34,262 1 comment 2 reactions 1 assignee Claimed by @tiancaiamao View on GitHub
severity/minor sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

If this works:
```sql
create table t1 (a int);
alter table t1 cache;
```

Then this should too, but it's a parse error:
```sql
create table t1 (a int) cache;
```

If a table has caching enabled, in `SHOW CREATE TABLE` it shows:
```sql
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /* CACHED ON */
```

If I try to re-create the table with exactly this syntax, it works. But if I drop the comment it doesn't:

```sql
tidb> create table t2 (a int) cached on;
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 30 near "cached on"
```

The comment itself is weird because it is not a version specific or TiDB specific comment, but just a regular comment. It should be something like:

```sql
create table t2 (a int) /*!T cached on */;
create table t2 (a int) /*T![cached] cached on */;
```

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

See above

### 3. What did you see instead (Required)

See above

### 4. What is your TiDB version? (Required)

```sql
tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.0-alpha-286-g28ea194d2-dirty
Edition: Community
Git Commit Hash: 28ea194d2088908f6690ebdb91b477b5a5a41516
Git Branch: master
UTC Build Time: 2022-04-26 15:14:33
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.