This is an automated email from the ASF dual-hosted git repository.
jiajunxie pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 23b7931c3e [CALCITE-6022] Support "CREATE TABLE ... LIKE" DDL in
server module
23b7931c3e is described below
commit 23b7931c3e516bdb6cfedda956213f7fe06c6b24
Author: macroguo <[email protected]>
AuthorDate: Tue Sep 26 14:07:23 2023 +0800
[CALCITE-6022] Support "CREATE TABLE ... LIKE" DDL in server module
---
.../main/java/org/apache/calcite/sql/SqlKind.java | 6 +-
.../apache/calcite/sql/ddl/SqlCreateTableLike.java | 121 ++++++++++++++++
.../org/apache/calcite/sql/ddl/SqlDdlNodes.java | 8 ++
server/src/main/codegen/config.fmpp | 1 +
server/src/main/codegen/includes/parserImpls.ftl | 67 ++++++++-
.../apache/calcite/server/ServerDdlExecutor.java | 99 +++++++++++++
.../org/apache/calcite/test/ServerParserTest.java | 30 ++++
.../java/org/apache/calcite/test/ServerTest.java | 160 +++++++++++++++++++++
server/src/test/resources/sql/table.iq | 137 ++++++++++++++++++
site/_docs/reference.md | 8 ++
10 files changed, 632 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index b2b5556c5e..7688cae915 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -1161,6 +1161,9 @@ public enum SqlKind {
/** {@code CREATE TABLE} DDL statement. */
CREATE_TABLE,
+ /** {@code CREATE TABLE LIKE} DDL statement. */
+ CREATE_TABLE_LIKE,
+
/** {@code ALTER TABLE} DDL statement. */
ALTER_TABLE,
@@ -1281,7 +1284,8 @@ public enum SqlKind {
public static final EnumSet<SqlKind> DDL =
EnumSet.of(COMMIT, ROLLBACK, ALTER_SESSION,
CREATE_SCHEMA, CREATE_FOREIGN_SCHEMA, DROP_SCHEMA,
- CREATE_TABLE, ALTER_TABLE, DROP_TABLE, TRUNCATE_TABLE,
+ CREATE_TABLE, CREATE_TABLE_LIKE,
+ ALTER_TABLE, DROP_TABLE, TRUNCATE_TABLE,
CREATE_FUNCTION, DROP_FUNCTION,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_MATERIALIZED_VIEW, ALTER_MATERIALIZED_VIEW,
diff --git
a/core/src/main/java/org/apache/calcite/sql/ddl/SqlCreateTableLike.java
b/core/src/main/java/org/apache/calcite/sql/ddl/SqlCreateTableLike.java
new file mode 100644
index 0000000000..c291de2fe3
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/ddl/SqlCreateTableLike.java
@@ -0,0 +1,121 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.ddl;
+
+import org.apache.calcite.sql.SqlCreate;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.SqlSpecialOperator;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.Symbolizable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.util.ImmutableNullableList;
+
+import com.google.common.base.Preconditions;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Parse tree for {@code CREATE TABLE LIKE} statement.
+ */
+public class SqlCreateTableLike extends SqlCreate {
+ private static final SqlOperator OPERATOR =
+ new SqlSpecialOperator("CREATE TABLE LIKE", SqlKind.CREATE_TABLE_LIKE);
+
+ /**
+ * The LikeOption specify which additional properties of the original table
to copy.
+ */
+ public enum LikeOption implements Symbolizable {
+ ALL,
+ DEFAULTS,
+ GENERATED
+ }
+
+ public final SqlIdentifier name;
+ public final SqlIdentifier sourceTable;
+ public final SqlNodeList includingOptions;
+ public final SqlNodeList excludingOptions;
+
+
+ public SqlCreateTableLike(SqlParserPos pos, boolean replace, boolean
ifNotExists,
+ SqlIdentifier name, SqlIdentifier sourceTable,
+ SqlNodeList includingOptions, SqlNodeList excludingOptions) {
+ super(OPERATOR, pos, replace, ifNotExists);
+ this.name = name;
+ this.sourceTable = sourceTable;
+ this.includingOptions = includingOptions;
+ this.excludingOptions = excludingOptions;
+
+ // validate like options
+ if (includingOptions.contains(LikeOption.ALL.symbol(SqlParserPos.ZERO))) {
+ Preconditions.checkArgument(
+ includingOptions.size() == 1 && excludingOptions.isEmpty(),
+ "ALL cannot be used with other options");
+ } else if
(excludingOptions.contains(LikeOption.ALL.symbol(SqlParserPos.ZERO))) {
+ Preconditions.checkArgument(
+ excludingOptions.size() == 1 && includingOptions.isEmpty(),
+ "ALL cannot be used with other options");
+ }
+
+ includingOptions.forEach(option -> {
+ Preconditions.checkArgument(
+ !excludingOptions.contains(option),
+ "Cannot include and exclude option %s at same time",
option.toString());
+ });
+ }
+
+ @Override public List<SqlNode> getOperandList() {
+ return ImmutableNullableList.of(name, sourceTable, includingOptions,
excludingOptions);
+ }
+
+ public Set<LikeOption> options() {
+ return includingOptions.stream()
+ .map(c -> ((SqlLiteral) c).symbolValue(LikeOption.class))
+ .collect(Collectors.toSet());
+ }
+
+ @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec)
{
+ writer.keyword("CREATE");
+ writer.keyword("TABLE");
+ if (ifNotExists) {
+ writer.keyword("IF NOT EXISTS");
+ }
+ name.unparse(writer, leftPrec, rightPrec);
+ writer.keyword("LIKE");
+ sourceTable.unparse(writer, leftPrec, rightPrec);
+ for (SqlNode c : new HashSet<>(includingOptions)) {
+ LikeOption likeOption = ((SqlLiteral) c).getValueAs(LikeOption.class);
+ writer.newlineAndIndent();
+ writer.keyword("INCLUDING");
+ writer.keyword(likeOption.name());
+ }
+
+ for (SqlNode c : new HashSet<>(excludingOptions)) {
+ LikeOption likeOption = ((SqlLiteral) c).getValueAs(LikeOption.class);
+ writer.newlineAndIndent();
+ writer.keyword("EXCLUDING");
+ writer.keyword(likeOption.name());
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java
b/core/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java
index acc2fd6d3b..46ed8ed080 100644
--- a/core/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java
+++ b/core/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java
@@ -61,6 +61,14 @@ public class SqlDdlNodes {
query);
}
+ /** Creates a CREATE TABLE LIKE. */
+ public static SqlCreateTableLike createTableLike(SqlParserPos pos, boolean
replace,
+ boolean ifNotExists, SqlIdentifier name, SqlIdentifier sourceTable,
+ SqlNodeList including, SqlNodeList excluding) {
+ return new SqlCreateTableLike(pos, replace, ifNotExists, name,
+ sourceTable, including, excluding);
+ }
+
/** Creates a CREATE VIEW. */
public static SqlCreateView createView(SqlParserPos pos, boolean replace,
SqlIdentifier name, SqlNodeList columnList, SqlNode query) {
diff --git a/server/src/main/codegen/config.fmpp
b/server/src/main/codegen/config.fmpp
index 731d569cb2..e5c3eaf4d2 100644
--- a/server/src/main/codegen/config.fmpp
+++ b/server/src/main/codegen/config.fmpp
@@ -30,6 +30,7 @@ data: {
"org.apache.calcite.sql.SqlCreate"
"org.apache.calcite.sql.SqlDrop"
"org.apache.calcite.sql.SqlTruncate"
+ "org.apache.calcite.sql.ddl.SqlCreateTableLike"
"org.apache.calcite.sql.ddl.SqlDdlNodes"
]
diff --git a/server/src/main/codegen/includes/parserImpls.ftl
b/server/src/main/codegen/includes/parserImpls.ftl
index 00e1f386e9..8449b9d4b6 100644
--- a/server/src/main/codegen/includes/parserImpls.ftl
+++ b/server/src/main/codegen/includes/parserImpls.ftl
@@ -247,14 +247,73 @@ SqlCreate SqlCreateTable(Span s, boolean replace) :
final SqlIdentifier id;
SqlNodeList tableElementList = null;
SqlNode query = null;
+
+ SqlCreate createTableLike = null;
}
{
<TABLE> ifNotExists = IfNotExistsOpt() id = CompoundIdentifier()
- [ tableElementList = TableElementList() ]
- [ <AS> query = OrderedQueryOrExpr(ExprContext.ACCEPT_QUERY) ]
+ (
+ <LIKE> createTableLike = SqlCreateTableLike(s, replace, ifNotExists,
id) {
+ return createTableLike;
+ }
+ |
+ [ tableElementList = TableElementList() ]
+ [ <AS> query = OrderedQueryOrExpr(ExprContext.ACCEPT_QUERY) ]
+ {
+ return SqlDdlNodes.createTable(s.end(this), replace, ifNotExists,
id, tableElementList, query);
+ }
+ )
+}
+
+SqlCreate SqlCreateTableLike(Span s, boolean replace, boolean ifNotExists,
SqlIdentifier id) :
+{
+ final SqlIdentifier sourceTable;
+ final boolean likeOptions;
+ final SqlNodeList including = new SqlNodeList(getPos());
+ final SqlNodeList excluding = new SqlNodeList(getPos());
+}
+{
+ sourceTable = CompoundIdentifier()
+ [ LikeOptions(including, excluding) ]
+ {
+ return SqlDdlNodes.createTableLike(s.end(this), replace, ifNotExists,
id, sourceTable, including, excluding);
+ }
+}
+
+void LikeOptions(SqlNodeList including, SqlNodeList excluding) :
+{
+}
+{
+ LikeOption(including, excluding)
+ (
+ LikeOption(including, excluding)
+ )*
+}
+
+void LikeOption(SqlNodeList includingOptions, SqlNodeList excludingOptions) :
+{
+ boolean including = false;
+ SqlCreateTableLike.LikeOption option;
+}
+{
+ (
+ <INCLUDING> { including = true; }
+ |
+ <EXCLUDING> { including = false; }
+ )
+ (
+ <ALL> { option = SqlCreateTableLike.LikeOption.ALL; }
+ |
+ <DEFAULTS> { option = SqlCreateTableLike.LikeOption.DEFAULTS; }
+ |
+ <GENERATED> { option = SqlCreateTableLike.LikeOption.GENERATED; }
+ )
{
- return SqlDdlNodes.createTable(s.end(this), replace, ifNotExists, id,
- tableElementList, query);
+ if (including) {
+ includingOptions.add(option.symbol(getPos()));
+ } else {
+ excludingOptions.add(option.symbol(getPos()));
+ }
}
}
diff --git
a/server/src/main/java/org/apache/calcite/server/ServerDdlExecutor.java
b/server/src/main/java/org/apache/calcite/server/ServerDdlExecutor.java
index d4dcf61c67..a0808c9f63 100644
--- a/server/src/main/java/org/apache/calcite/server/ServerDdlExecutor.java
+++ b/server/src/main/java/org/apache/calcite/server/ServerDdlExecutor.java
@@ -61,6 +61,7 @@ import org.apache.calcite.sql.ddl.SqlCreateFunction;
import org.apache.calcite.sql.ddl.SqlCreateMaterializedView;
import org.apache.calcite.sql.ddl.SqlCreateSchema;
import org.apache.calcite.sql.ddl.SqlCreateTable;
+import org.apache.calcite.sql.ddl.SqlCreateTableLike;
import org.apache.calcite.sql.ddl.SqlCreateType;
import org.apache.calcite.sql.ddl.SqlCreateView;
import org.apache.calcite.sql.ddl.SqlDropObject;
@@ -102,6 +103,7 @@ import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
+import java.util.Set;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -559,6 +561,58 @@ public class ServerDdlExecutor extends DdlExecutorImpl {
}
}
+ /** Executes a {@code CREATE TABLE LIKE} command. */
+ public void execute(SqlCreateTableLike create,
+ CalcitePrepare.Context context) {
+ final Pair<CalciteSchema, String> pair = schema(context, true,
create.name);
+ if (pair.left.plus().getTable(pair.right) != null) {
+ // Table exists.
+ if (create.ifNotExists) {
+ return;
+ }
+ if (!create.getReplace()) {
+ // They did not specify IF NOT EXISTS, so give error.
+ throw SqlUtil.newContextException(create.name.getParserPosition(),
+ RESOURCE.tableExists(pair.right));
+ }
+ }
+
+ final Pair<CalciteSchema, String> sourceTablePair =
+ schema(context, true, create.sourceTable);
+ final Table table = sourceTablePair.left
+ .getTable(sourceTablePair.right, context.config().caseSensitive())
+ .getTable();
+
+ InitializerExpressionFactory ief =
NullInitializerExpressionFactory.INSTANCE;
+ if (table instanceof Wrapper) {
+ final InitializerExpressionFactory sourceIef =
+ ((Wrapper) table).unwrap(InitializerExpressionFactory.class);
+ if (sourceIef != null) {
+ final Set<SqlCreateTableLike.LikeOption> optionSet = create.options();
+ final boolean includingGenerated =
+ optionSet.contains(SqlCreateTableLike.LikeOption.GENERATED)
+ || optionSet.contains(SqlCreateTableLike.LikeOption.ALL);
+ final boolean includingDefaults =
+ optionSet.contains(SqlCreateTableLike.LikeOption.DEFAULTS)
+ || optionSet.contains(SqlCreateTableLike.LikeOption.ALL);
+
+ // initializes columns based on the source table
InitializerExpressionFactory
+ // and like options.
+ ief =
+ new CopiedTableInitializerExpressionFactory(
+ includingGenerated, includingDefaults, sourceIef);
+ }
+ }
+
+ final JavaTypeFactory typeFactory = context.getTypeFactory();
+ final RelDataType rowType = table.getRowType(typeFactory);
+ // Table does not exist. Create it.
+ pair.left.add(pair.right,
+ new MutableArrayTable(pair.right,
+ RelDataTypeImpl.proto(rowType),
+ RelDataTypeImpl.proto(rowType), ief));
+ }
+
/** Executes a {@code CREATE TYPE} command. */
public void execute(SqlCreateType create,
CalcitePrepare.Context context) {
@@ -606,6 +660,51 @@ public class ServerDdlExecutor extends DdlExecutorImpl {
schemaPlus.add(pair.right, viewTableMacro);
}
+ /**
+ * Initializes columns based on the source {@link
InitializerExpressionFactory}
+ * and like options.
+ */
+ private static class CopiedTableInitializerExpressionFactory
+ extends NullInitializerExpressionFactory {
+
+ private final boolean includingGenerated;
+ private final boolean includingDefaults;
+ private final InitializerExpressionFactory sourceIef;
+
+ CopiedTableInitializerExpressionFactory(
+ boolean includingGenerated,
+ boolean includingDefaults,
+ InitializerExpressionFactory sourceIef) {
+ this.includingGenerated = includingGenerated;
+ this.includingDefaults = includingDefaults;
+ this.sourceIef = sourceIef;
+ }
+
+ @Override public ColumnStrategy generationStrategy(
+ RelOptTable table, int iColumn) {
+ final ColumnStrategy sourceStrategy =
sourceIef.generationStrategy(table, iColumn);
+ if (includingGenerated
+ && (sourceStrategy == ColumnStrategy.STORED
+ || sourceStrategy == ColumnStrategy.VIRTUAL)) {
+ return sourceStrategy;
+ }
+ if (includingDefaults && sourceStrategy == ColumnStrategy.DEFAULT) {
+ return ColumnStrategy.DEFAULT;
+ }
+
+ return super.generationStrategy(table, iColumn);
+ }
+
+ @Override public RexNode newColumnDefaultValue(
+ RelOptTable table, int iColumn, InitializerContext context) {
+ if (includingDefaults || includingGenerated) {
+ return sourceIef.newColumnDefaultValue(table, iColumn, context);
+ } else {
+ return super.newColumnDefaultValue(table, iColumn, context);
+ }
+ }
+ }
+
/** Column definition. */
private static class ColumnDef {
final SqlNode expr;
diff --git a/server/src/test/java/org/apache/calcite/test/ServerParserTest.java
b/server/src/test/java/org/apache/calcite/test/ServerParserTest.java
index d3ed84549a..aeb1c07525 100644
--- a/server/src/test/java/org/apache/calcite/test/ServerParserTest.java
+++ b/server/src/test/java/org/apache/calcite/test/ServerParserTest.java
@@ -181,6 +181,36 @@ class ServerParserTest extends SqlParserTest {
sql(sql).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLike() {
+ final String sql = "create table x like y";
+ final String expected = "CREATE TABLE `X` LIKE `Y`";
+ sql(sql).ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLikeWithOptions() {
+ sql("create table x like y including all")
+ .ok("CREATE TABLE `X` LIKE `Y`\n"
+ + "INCLUDING ALL");
+
+ sql("create table s.x like s.y excluding defaults including generated")
+ .ok("CREATE TABLE `S`.`X` LIKE `S`.`Y`\n"
+ + "INCLUDING GENERATED\n"
+ + "EXCLUDING DEFAULTS");
+
+ sql("create table x like y excluding defaults including all")
+ .fails("ALL cannot be used with other options");
+
+ sql("create table x like y including defaults excluding defaults")
+ .fails("Cannot include and exclude option DEFAULTS at same time");
+
+ }
+
@Test void testCreateView() {
final String sql = "create or replace view v as\n"
+ "select * from (values (1, '2'), (3, '45')) as t (x, y)";
diff --git a/server/src/test/java/org/apache/calcite/test/ServerTest.java
b/server/src/test/java/org/apache/calcite/test/ServerTest.java
index 3c9180970c..d48b1940de 100644
--- a/server/src/test/java/org/apache/calcite/test/ServerTest.java
+++ b/server/src/test/java/org/apache/calcite/test/ServerTest.java
@@ -29,6 +29,7 @@ import org.apache.calcite.sql.ddl.SqlCreateFunction;
import org.apache.calcite.sql.ddl.SqlCreateMaterializedView;
import org.apache.calcite.sql.ddl.SqlCreateSchema;
import org.apache.calcite.sql.ddl.SqlCreateTable;
+import org.apache.calcite.sql.ddl.SqlCreateTableLike;
import org.apache.calcite.sql.ddl.SqlCreateType;
import org.apache.calcite.sql.ddl.SqlCreateView;
import org.apache.calcite.sql.ddl.SqlDropFunction;
@@ -95,6 +96,7 @@ class ServerTest {
executor.execute((SqlNode) o, context);
executor.execute((SqlCreateFunction) o, context);
executor.execute((SqlCreateTable) o, context);
+ executor.execute((SqlCreateTableLike) o, context);
executor.execute((SqlCreateSchema) o, context);
executor.execute((SqlCreateMaterializedView) o, context);
executor.execute((SqlCreateView) o, context);
@@ -277,6 +279,164 @@ class ServerTest {
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLike() throws Exception {
+ try (Connection c = connect();
+ Statement s = c.createStatement()) {
+ s.execute("create table t (i int not null)");
+ s.execute("create table t2 like t");
+ int x = s.executeUpdate("insert into t2 values 1");
+ assertThat(x, is(1));
+ x = s.executeUpdate("insert into t2 values 3");
+ assertThat(x, is(1));
+ try (ResultSet r = s.executeQuery("select sum(i) from t2")) {
+ assertThat(r.next(), is(true));
+ assertThat(r.getInt(1), is(4));
+ assertThat(r.next(), is(false));
+ }
+ }
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLikeWithStoredGeneratedColumn() throws Exception {
+ try (Connection c = connect();
+ Statement s = c.createStatement()) {
+ s.execute("create table t (\n"
+ + " h int not null,\n"
+ + " i int,\n"
+ + " j int as (i + 1) stored,\n"
+ + " k int default -1)\n");
+ s.execute("create table t2 like t including defaults including
generated");
+
+ int x = s.executeUpdate("insert into t2 (h, i) values (3, 4)");
+ assertThat(x, is(1));
+
+ final String sql1 = "explain plan for\n"
+ + "insert into t2 (h, i) values (3, 4)";
+ try (ResultSet r = s.executeQuery(sql1)) {
+ assertThat(r.next(), is(true));
+ final String plan = ""
+ + "EnumerableTableModify(table=[[T2]], operation=[INSERT],
flattened=[false])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1],
expr#3=[+($t1, $t2)], expr#4=[-1], proj#0..1=[{exprs}], J=[$t3], K=[$t4])\n"
+ + " EnumerableValues(tuples=[[{ 3, 4 }]])\n";
+ assertThat(r.getString(1), isLinux(plan));
+ assertThat(r.next(), is(false));
+ }
+
+ try (ResultSet r = s.executeQuery("select * from t2")) {
+ assertThat(r.next(), is(true));
+ assertThat(r.getInt("H"), is(3));
+ assertThat(r.wasNull(), is(false));
+ assertThat(r.getInt("I"), is(4));
+ assertThat(r.getInt("J"), is(5)); // j = i + 1
+ assertThat(r.getInt("K"), is(-1)); // k = -1 (default)
+ assertThat(r.next(), is(false));
+ }
+
+ SQLException e =
+ assertThrows(
+ SQLException.class, () -> s.executeUpdate("insert into t2 values
(3, 4, 5, 6)"));
+ assertThat(e.getMessage(), containsString("Cannot INSERT into generated
column 'J'"));
+ }
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLikeWithVirtualGeneratedColumn() throws Exception {
+ try (Connection c = connect();
+ Statement s = c.createStatement()) {
+ s.execute("create table t (\n"
+ + " h int not null,\n"
+ + " i int,\n"
+ + " j int as (i + 1) virtual)\n");
+ s.execute("create table t2 like t including defaults including
generated");
+
+ int x = s.executeUpdate("insert into t2 (h, i) values (3, 4)");
+ assertThat(x, is(1));
+
+ final String sql1 = "explain plan for\n"
+ + "insert into t (h, i) values (3, 4)";
+ try (ResultSet r = s.executeQuery(sql1)) {
+ final String sql2 = "explain plan for\n"
+ + "insert into t2 (h, i) values (3, 4)";
+ assertThat(r.next(), is(true));
+ final String plan = r.getString(1);
+ assertThat(r.next(), is(false));
+
+ ResultSet r2 = s.executeQuery(sql2);
+ assertThat(r2.next(), is(true));
+ assertEquals(plan, r2.getString(1).replace("T2", "T"));
+ assertThat(r2.next(), is(false));
+ }
+
+ try (ResultSet r = s.executeQuery("select * from t2")) {
+ assertThat(r.next(), is(true));
+ assertThat(r.getInt("H"), is(3));
+ assertThat(r.wasNull(), is(false));
+ assertThat(r.getInt("I"), is(4));
+ assertThat(r.getInt("J"), is(5)); // j = i + 1
+ assertThat(r.next(), is(false));
+ }
+
+ SQLException e =
+ assertThrows(
+ SQLException.class, () -> s.executeUpdate("insert into t2 values
(3, 4, 5)"));
+ assertThat(e.getMessage(), containsString("Cannot INSERT into generated
column 'J'"));
+ }
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6022";>[CALCITE-6022]
+ * Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
+ @Test void testCreateTableLikeWithoutLikeOptions() throws Exception {
+ try (Connection c = connect();
+ Statement s = c.createStatement()) {
+ s.execute("create table t (\n"
+ + " h int not null,\n"
+ + " i int,\n"
+ + " j int as (i + 1) stored,\n"
+ + " k int default -1)");
+ // In table t2, only copy the column and type information from t,
+ // excluding generated expression and default expression
+ s.execute("create table t2 like t");
+
+ int x = s.executeUpdate("insert into t2 (h, i) values (3, 4)");
+ assertThat(x, is(1));
+
+ final String sql1 = "explain plan for\n"
+ + "insert into t2 (h, i) values (3, 4)";
+ try (ResultSet r = s.executeQuery(sql1)) {
+ assertThat(r.next(), is(true));
+ final String plan = ""
+ + "EnumerableTableModify(table=[[T2]], operation=[INSERT],
flattened=[false])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[null:INTEGER],
proj#0..2=[{exprs}], K=[$t2])\n"
+ + " EnumerableValues(tuples=[[{ 3, 4 }]])\n";
+ assertThat(r.getString(1), isLinux(plan));
+ assertThat(r.next(), is(false));
+ }
+
+ try (ResultSet r = s.executeQuery("select * from t2")) {
+ assertThat(r.next(), is(true));
+ assertThat(r.getInt("H"), is(3));
+ assertThat(r.wasNull(), is(false));
+ assertThat(r.getInt("I"), is(4));
+ assertThat(r.getInt("J"), is(0)); // excluding generated column
+ assertThat(r.wasNull(), is(true));
+ assertThat(r.getInt("K"), is(0)); // excluding default column
+ assertThat(r.wasNull(), is(true));
+ assertThat(r.next(), is(false));
+ }
+
+ x = s.executeUpdate("insert into t2 values (3, 4, 5, 6)");
+ assertThat(x, is(1));
+ }
+ }
+
@Test void testTruncateTable() throws Exception {
try (Connection c = connect();
Statement s = c.createStatement()) {
diff --git a/server/src/test/resources/sql/table.iq
b/server/src/test/resources/sql/table.iq
index b696b2cf92..8726b2521d 100755
--- a/server/src/test/resources/sql/table.iq
+++ b/server/src/test/resources/sql/table.iq
@@ -50,6 +50,25 @@ select * from t;
!ok
+create table t2 like t;
+(0 rows modified)
+
+!update
+
+select * from t2;
+I INTEGER(10)
+J INTEGER(10) NOT NULL
+!type
+
+select * from t2;
++---+---+
+| I | J |
++---+---+
++---+---+
+(0 rows)
+
+!ok
+
truncate table t;
(0 rows modified)
@@ -97,6 +116,42 @@ select * from t;
!ok
+drop table t2;
+(0 rows modified)
+
+!update
+
+create table t2 like t including defaults;
+(0 rows modified)
+
+!update
+
+insert into t2 values (1, 2);
+(1 row modified)
+
+!update
+
+insert into t2 (i) values (3);
+(1 row modified)
+
+!update
+
+select * from t2;
++---+---+
+| I | J |
++---+---+
+| 1 | 2 |
+| 3 | 5 |
++---+---+
+(2 rows)
+
+!ok
+
+drop table t2;
+(0 rows modified)
+
+!update
+
drop table t;
(0 rows modified)
@@ -148,6 +203,88 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)],
expr#3=[2], expr#4=[+
EnumerableTableScan(table=[[T]])
!plan
+# Create a table with a STORED column
+create table t2 like t including all;
+(0 rows modified)
+
+!update
+
+insert into t2 values (1, 2, 3);
+Cannot INSERT into generated column 'J'
+!error
+
+insert into t2 (i, j) values (1, 2);
+Cannot INSERT into generated column 'J'
+!error
+
+insert into t2 (i, k) values (1, 3);
+(1 row modified)
+
+!update
+EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
+ EnumerableValues(tuples=[[{ 1, 3 }]])
+!plan
+
+insert into t2 (k, i) values (5, 2);
+(1 row modified)
+
+!update
+EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
+ EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
+ EnumerableValues(tuples=[[{ 5, 2 }]])
+!plan
+
+select * from t2;
++---+---+---+
+| I | J | K |
++---+---+---+
+| 1 | 6 | 3 |
+| 2 | 9 | 5 |
++---+---+---+
+(2 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)], expr#3=[2],
expr#4=[+($t2, $t3)], I=[$t0], J=[$t4], K=[$t1])
+ EnumerableTableScan(table=[[T2]])
+!plan
+
+drop table t2;
+(0 rows modified)
+
+!update
+
+# Create a table excluding virtual columns
+create table t2 like t;
+(0 rows modified)
+
+!update
+
+select * from t2;
+I INTEGER(10)
+J INTEGER(10)
+K INTEGER(10)
+!type
+
+insert into t2 values (1, 2, 3);
+(1 row modified)
+
+!update
+
+select * from t2;
++---+---+---+
+| I | J | K |
++---+---+---+
+| 1 | 2 | 3 |
++---+---+---+
+(1 row)
+
+!ok
+
+drop table t2;
+(0 rows modified)
+
+!update
+
drop table if exists t;
(0 rows modified)
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index ee84aa1ece..b2a2483f88 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -3378,6 +3378,7 @@ ddlStatement:
createSchemaStatement
| createForeignSchemaStatement
| createTableStatement
+ | createTableLikeStatement
| createViewStatement
| createMaterializedViewStatement
| createTypeStatement
@@ -3409,6 +3410,13 @@ createTableStatement:
[ '(' tableElement [, tableElement ]* ')' ]
[ AS query ]
+createTableLikeStatement:
+ CREATE TABLE [ IF NOT EXISTS ] name LIKE sourceTable
+ [ likeOption [, likeOption ]* ]
+
+likeOption:
+ { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | ALL }
+
createTypeStatement:
CREATE [ OR REPLACE ] TYPE name AS
{
