temporary table validates ENGINE (inconsistent with regular table)

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1, t2, t3, t4;
CREATE TABLE t1 (a int) ENGINE=InnoDB; -- works
CREATE TABLE t2 (a int) ENGINE=MyISAM; -- works
CREATE TEMPORARY TABLE t3 (a int) ENGINE=InnoDB; -- fails
CREATE TEMPORARY TABLE t4 (a int) ENGINE=MyISAM; -- fails

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

mysql [localhost:8024] {root} (test) > DROP TABLE IF EXISTS t1, t2, t3, t4;
Query OK, 0 rows affected, 4 warnings (0.00 sec)

mysql [localhost:8024] {root} (test) > CREATE TABLE t1 (a int) ENGINE=InnoDB; -- works
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8024] {root} (test) > CREATE TABLE t2 (a int) ENGINE=MyISAM; -- works
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8024] {root} (test) > CREATE TEMPORARY TABLE t3 (a int) ENGINE=InnoDB; -- fails
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} (test) > CREATE TEMPORARY TABLE t4 (a int) ENGINE=MyISAM; -- fails
Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

mysql> DROP TABLE IF EXISTS t1, t2, t3, t4;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql> CREATE TABLE t1 (a int) ENGINE=InnoDB; -- works
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (a int) ENGINE=MyISAM; -- works
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE t3 (a int) ENGINE=InnoDB; -- fails
ERROR 8200 (HY000): TiDB doesn't support this kind of engine for temporary table
mysql> CREATE TEMPORARY TABLE t4 (a int) ENGINE=MyISAM; -- fails
ERROR 8200 (HY000): TiDB doesn't support this kind of engine for temporary table

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-256-gb0b559f23
Edition: Community
Git Commit Hash: b0b559f2389de5e27d0d2e12d2bfae049186b54b
Git Branch: master
UTC Build Time: 2021-10-01 14:52:33
GoVersion: go1.16.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

/cc @djshow832

[morgo]

I'm not sure this is a major issue; it just seems strange it is inconsistent. I spotted this porting tests.

[aytrack]

Engine type can only be 'memory' or empty for now.

[djshow832]

Since we don't support writing temporary table data to disk, we only support engine=memory and TiDB reports errors when engine is not memory. After we have supported writing to disk, we'll support engine=innodb. This is both for global temporary tables and MySQL temporary tables.
However, @mjonss figures out that users actually don't care about the engine and it's better to support engine=innodb even if it doesn't write to the disk to be compatible with MySQL. Since MySQL doesn't support global temporary tables, we can only support engine=memory.
WDYT?

[morgo]

I agree with @mjonss

As I understand, we don't support the semantics of engine=memory (non transactional; supports btree and heap indexes, stricter limits on column size). Most of the semantics are closer to InnoDB, except that there is a size limit (by memory). But there are already other size limits on max transaction size for regular tables, so it is not so surprising.