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 <macro...@tencent.com> 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 {