create table select from not supported.

[winkyao]

MySQL:

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t4 select t1.* from t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t4;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> create table t4 select t1.* from t1;
ERROR 1105 (HY000): line 1 column 22 near " t1.* from t1" (total length 35)

What version of TiDB are you using (tidb-server -V)?

mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 0.9.0
Git Commit Hash: a7aaa64c76b5a1e5bb66caf94b888515cc334cf1
Git Branch: master
UTC Build Time: 2017-10-10 08:44:43 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ref https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html

this case is from mysql-test/alias.test

[bb7133]

I'm working on this issue, hope that it can be fixed soon @winkyao @zz-jason , thanks

[zz-jason]

@bb7133 Thanks for your contribution!

[wwar]

Here is a minimal test case:

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 AS SELECT * FROM t1;

Confirming it is still pending:

mysql> CREATE TABLE t2 AS SELECT * FROM t1;
ERROR 1105 (HY000): 'CREATE TABLE ... SELECT' is not implemented yet
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-183-g57db6cec7
Git Commit Hash: 57db6cec7ffad78e74b6ac6c67a2bfe9a6718d17
Git Branch: master
UTC Build Time: 2020-04-04 08:06:18
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[fintecheando]

Hi,

I got the same error working with Flyway and Java

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 63955
Server version: 5.7.25-TiDB-v4.0.0-rc.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-rc.2
Edition: Community
Git Commit Hash: dfbb1ff
Git Branch: heads/refs/tags/v4.0.0-rc.2
UTC Build Time: 2020-05-15 11:54:25
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

This is the issue:

[log4j] 2020-08-16 23:42:57 [main] WARN org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor - DB: 'CREATE TABLE ... SELECT' is not implemented yet (SQL State: HY000 - Error Code: 1105),
[log4j] 2020-08-16 23:42:57 [main] WARN org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: ,
Migration failed,
-----------------,
SQL State : HY000,
Error Code : 1105,
Message : 'CREATE TABLE ... SELECT' is not implemented yet,
Location : (),
Line : 1,
Statement : CREATE TABLE default.otp_schema_history (,
installed_rank INT NOT NULL,,
version VARCHAR(50),,
description VARCHAR(200) NOT NULL,,
type VARCHAR(20) NOT NULL,,
script VARCHAR(1000) NOT NULL,,
checksum INT,,
installed_by VARCHAR(100) NOT NULL,,
installed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,,
execution_time INT NOT NULL,,
success BOOL NOT NULL,,
CONSTRAINT otp_schema_history_pk PRIMARY KEY (installed_rank),
) ENGINE=InnoDB AS SELECT 1 as "installed_rank", '1' as "version", '<< Flyway Baseline >>' as "description", 'BASELINE' as "type", '<< Flyway Baseline >>' as "script", NULL as "checksum", 'root' as "installed_by", CURRENT_TIMESTAMP as "installed_on", 0 as "execution_time", TRUE as "success",
,
,
[log4j] 2020-08-16 23:42:57 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...,
[log4j] 2020-08-16 23:42:57 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.,

[dveeden]

Isn't this the same as #3839 ?

[dcswinner01]

why not support?

[bb7133]

why not support?

There are always ways to make a workaround just like 'CREATE TABLE LIKE ..." + "INSERT INTO ... SELECT", so the priority of it is low.

[dveeden]

Please note that when using MySQL there is a big restriction on using this: In MySQL 5.7 and MySQL 8.0.20 and before this isn't allowed when GTID is used.

Source: https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-restrictions.html

While I don't see any real problem for implementing this in TiDB I would still recommend people to avoid this. I think it is often better to not combine DDL and DML in a single statement. Also it is often better to more explicitly define a table structure as it may be difficult and/or impossible for the database to use the right data types. For example CREATE TABLE t1 SELECT 1: Would this be a tinyint (bool?), int or bigint? unsigned or signed? a column comment? what should the default be? are nulls allowed?