This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new b8c3003 IGNITE-14555 Create table from query result - Fixes #9119.
b8c3003 is described below
commit b8c300313a9d3b36c2139cacb8255c684e5ee1e4
Author: Aleksey Plekhanov <[email protected]>
AuthorDate: Tue Sep 28 09:38:13 2021 +0300
IGNITE-14555 Create table from query result - Fixes #9119.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
.../src/main/codegen/includes/parserImpls.ftl | 39 ++++--
.../query/calcite/exec/ExecutionServiceImpl.java | 50 +++++---
.../calcite/prepare/ddl/CreateTableCommand.java | 19 ++-
.../prepare/ddl/DdlSqlToCommandConverter.java | 135 +++++++++++++++------
.../query/calcite/sql/IgniteSqlCreateTable.java | 25 +++-
.../integration/AbstractDdlIntegrationTest.java | 19 ++-
.../integration/TableDdlIntegrationTest.java | 93 ++++++++++++++
.../query/calcite/sql/SqlDdlParserTest.java | 32 +++++
.../aggregates/test_group_by_many_groups.test_slow | 5 +-
.../test_group_by_many_groups.test_slow_ignore | 15 ---
.../sql/aggregate/aggregates/test_perfect_ht.test | 15 +--
.../aggregates/test_perfect_ht.test_ignore | 1 -
.../test/sql/types/decimal/decimal_aggregates.test | 22 ++++
.../types/decimal/decimal_aggregates.test_ignored | 91 --------------
14 files changed, 361 insertions(+), 200 deletions(-)
diff --git a/modules/calcite/src/main/codegen/includes/parserImpls.ftl
b/modules/calcite/src/main/codegen/includes/parserImpls.ftl
index 62c80ca..725aac0 100644
--- a/modules/calcite/src/main/codegen/includes/parserImpls.ftl
+++ b/modules/calcite/src/main/codegen/includes/parserImpls.ftl
@@ -24,19 +24,23 @@ boolean IfNotExistsOpt() :
{ return false; }
}
-SqlNodeList CreateTableOptionList() :
+SqlNodeList WithCreateTableOptionList() :
{
List<SqlNode> list = new ArrayList<SqlNode>();
- final Span s = Span.of();
+ final Span s;
}
{
- CreateTableOption(list)
- (
- <COMMA> { s.add(this); } CreateTableOption(list)
- )*
- {
- return new SqlNodeList(list, s.end(this));
- }
+ [
+ <WITH> { s = span(); }
+ CreateTableOption(list)
+ (
+ <COMMA> { s.add(this); } CreateTableOption(list)
+ )*
+ {
+ return new SqlNodeList(list, s.end(this));
+ }
+ ]
+ { return null; }
}
SqlLiteral CreateTableOptionKey() :
@@ -148,19 +152,28 @@ SqlCreate SqlCreateTable(Span s, boolean replace) :
final SqlIdentifier id;
final SqlNodeList columnList;
final SqlNodeList optionList;
+ final SqlNode query;
}
{
<TABLE>
ifNotExists = IfNotExistsOpt()
id = CompoundIdentifier()
- columnList = TableElementList()
(
- <WITH> { s.add(this); } optionList = CreateTableOptionList()
+ LOOKAHEAD(3)
+ columnList = TableElementList()
+ optionList = WithCreateTableOptionList()
+ { query = null; }
|
- { optionList = null; }
+ (
+ columnList = ParenthesizedSimpleIdentifierList()
+ |
+ { columnList = null; }
+ )
+ optionList = WithCreateTableOptionList()
+ <AS> { s.add(this); } query =
OrderedQueryOrExpr(ExprContext.ACCEPT_QUERY)
)
{
- return new IgniteSqlCreateTable(s.end(this), ifNotExists, id,
columnList, optionList);
+ return new IgniteSqlCreateTable(s.end(this), ifNotExists, id,
columnList, query, optionList);
}
}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionServiceImpl.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionServiceImpl.java
index ed3bce2..46e7678 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionServiceImpl.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/ExecutionServiceImpl.java
@@ -27,15 +27,16 @@ import java.util.Objects;
import java.util.Set;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
-
import org.apache.calcite.plan.Context;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.runtime.CalciteContextException;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlDdl;
import org.apache.calcite.sql.SqlExplain;
import org.apache.calcite.sql.SqlExplainLevel;
+import org.apache.calcite.sql.SqlInsert;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -95,6 +96,7 @@ import
org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCach
import
org.apache.ignite.internal.processors.query.calcite.prepare.QueryTemplate;
import org.apache.ignite.internal.processors.query.calcite.prepare.Splitter;
import
org.apache.ignite.internal.processors.query.calcite.prepare.ValidationResult;
+import
org.apache.ignite.internal.processors.query.calcite.prepare.ddl.CreateTableCommand;
import
org.apache.ignite.internal.processors.query.calcite.prepare.ddl.DdlSqlToCommandConverter;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteRel;
import org.apache.ignite.internal.processors.query.calcite.schema.SchemaHolder;
@@ -534,8 +536,8 @@ public class ExecutionServiceImpl<Row> extends
AbstractService implements Execut
"querySql=\"" + ctx.query() + "\"]",
IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
}
}
- catch (ValidationException e) {
- throw new IgniteSQLException("Failed to validate query.",
IgniteQueryErrorCode.PARSING, e);
+ catch (ValidationException | CalciteContextException e) {
+ throw new IgniteSQLException("Failed to validate query: " +
e.getMessage(), IgniteQueryErrorCode.PARSING, e);
}
}
@@ -550,12 +552,8 @@ public class ExecutionServiceImpl<Row> extends
AbstractService implements Execut
IgniteRel igniteRel = optimize(sqlNode, planner, log);
- // Split query plan to query fragments.
- List<Fragment> fragments = new Splitter().go(igniteRel);
-
- QueryTemplate template = new QueryTemplate(mappingSvc, fragments);
-
- return new MultiStepQueryPlan(template, queryFieldsMetadata(ctx,
validated.dataType(), validated.origins()));
+ return new MultiStepQueryPlan(queryTemplate(igniteRel),
+ queryFieldsMetadata(ctx, validated.dataType(),
validated.origins()));
}
/** */
@@ -568,12 +566,8 @@ public class ExecutionServiceImpl<Row> extends
AbstractService implements Execut
// Convert to Relational operators graph
IgniteRel igniteRel = optimize(sqlNode, planner, log);
- // Split query plan to query fragments.
- List<Fragment> fragments = new Splitter().go(igniteRel);
-
- QueryTemplate template = new QueryTemplate(mappingSvc, fragments);
-
- return new MultiStepDmlPlan(template, queryFieldsMetadata(ctx,
igniteRel.getRowType(), null));
+ return new MultiStepDmlPlan(queryTemplate(igniteRel),
+ queryFieldsMetadata(ctx, igniteRel.getRowType(), null));
}
/** */
@@ -601,6 +595,14 @@ public class ExecutionServiceImpl<Row> extends
AbstractService implements Execut
}
/** */
+ private QueryTemplate queryTemplate(IgniteRel rel) {
+ // Split query plan to query fragments.
+ List<Fragment> fragments = new Splitter().go(rel);
+
+ return new QueryTemplate(mappingSvc, fragments);
+ }
+
+ /** */
private FieldsMetadata explainFieldsMetadata(PlanningContext ctx) {
IgniteTypeFactory factory = ctx.typeFactory();
RelDataType planStrDataType =
@@ -659,7 +661,23 @@ public class ExecutionServiceImpl<Row> extends
AbstractService implements Execut
", err=" + e.getMessage() + ']', e);
}
- return H2Utils.zeroCursor();
+ if (plan.command() instanceof CreateTableCommand &&
((CreateTableCommand)plan.command()).insertStatement() != null) {
+ SqlInsert insertStmt =
((CreateTableCommand)plan.command()).insertStatement();
+
+ try {
+ // Create new planning context containing created table in the
schema.
+ PlanningContext dmlCtx = createContext(pctx,
pctx.schemaName(), pctx.query(), pctx.parameters());
+
+ QueryPlan dmlPlan = prepareDml(insertStmt, dmlCtx);
+
+ return executePlan(qryId, dmlCtx, dmlPlan);
+ }
+ catch (ValidationException e) {
+ throw new IgniteSQLException("Failed to validate query.",
IgniteQueryErrorCode.PARSING, e);
+ }
+ }
+ else
+ return H2Utils.zeroCursor();
}
/** */
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/CreateTableCommand.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/CreateTableCommand.java
index f474f49..12436c5 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/CreateTableCommand.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/CreateTableCommand.java
@@ -18,7 +18,7 @@
package org.apache.ignite.internal.processors.query.calcite.prepare.ddl;
import java.util.List;
-
+import org.apache.calcite.sql.SqlInsert;
import org.apache.ignite.cache.CacheAtomicityMode;
import org.apache.ignite.cache.CacheWriteSynchronizationMode;
import org.jetbrains.annotations.Nullable;
@@ -78,6 +78,9 @@ public class CreateTableCommand implements DdlCommand {
/** Encrypted flag. */
private boolean encrypted;
+ /** Insert statement for AS SELECT query. */
+ private SqlInsert insertStmt;
+
/**
* @return Cache name upon which new cache configuration for this table
must be based.
*/
@@ -301,4 +304,18 @@ public class CreateTableCommand implements DdlCommand {
public void encrypted(boolean encrypted) {
this.encrypted = encrypted;
}
+
+ /**
+ * @return Data insert statement for CREATE AS SELECT command.
+ */
+ public SqlInsert insertStatement() {
+ return insertStmt;
+ }
+
+ /**
+ * @param insertStmt Data insert statement for CREATE AS SELECT command.
+ */
+ public void insertStatement(SqlInsert insertStmt) {
+ this.insertStmt = insertStmt;
+ }
}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/DdlSqlToCommandConverter.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/DdlSqlToCommandConverter.java
index 9d1f981..f728750 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/DdlSqlToCommandConverter.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/ddl/DdlSqlToCommandConverter.java
@@ -29,8 +29,10 @@ import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.sql.SqlDdl;
import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlInsert;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
@@ -45,6 +47,7 @@ import
org.apache.ignite.internal.processors.query.IgniteSQLException;
import org.apache.ignite.internal.processors.query.QueryUtils;
import
org.apache.ignite.internal.processors.query.calcite.prepare.IgnitePlanner;
import
org.apache.ignite.internal.processors.query.calcite.prepare.PlanningContext;
+import
org.apache.ignite.internal.processors.query.calcite.prepare.ValidationResult;
import
org.apache.ignite.internal.processors.query.calcite.sql.IgniteSqlAlterTableAddColumn;
import
org.apache.ignite.internal.processors.query.calcite.sql.IgniteSqlAlterTableDropColumn;
import
org.apache.ignite.internal.processors.query.calcite.sql.IgniteSqlCreateTable;
@@ -150,8 +153,11 @@ public class DdlSqlToCommandConverter {
private CreateTableCommand convertCreateTable(IgniteSqlCreateTable
createTblNode, PlanningContext ctx) {
CreateTableCommand createTblCmd = new CreateTableCommand();
- createTblCmd.schemaName(deriveSchemaName(createTblNode.name(), ctx));
- createTblCmd.tableName(deriveObjectName(createTblNode.name(), ctx,
"tableName"));
+ String schemaName = deriveSchemaName(createTblNode.name(), ctx);
+ String tableName = deriveObjectName(createTblNode.name(), ctx,
"tableName");
+
+ createTblCmd.schemaName(schemaName);
+ createTblCmd.tableName(tableName);
createTblCmd.ifNotExists(createTblNode.ifNotExists());
createTblCmd.templateName(QueryUtils.TEMPLATE_PARTITIONED);
@@ -163,56 +169,109 @@ public class DdlSqlToCommandConverter {
}
}
- List<SqlColumnDeclaration> colDeclarations =
createTblNode.columnList().getList().stream()
- .filter(SqlColumnDeclaration.class::isInstance)
- .map(SqlColumnDeclaration.class::cast)
- .collect(Collectors.toList());
-
IgnitePlanner planner = ctx.planner();
- List<ColumnDefinition> cols = new ArrayList<>();
+ if (createTblNode.query() == null) {
+ List<SqlColumnDeclaration> colDeclarations =
createTblNode.columnList().getList().stream()
+ .filter(SqlColumnDeclaration.class::isInstance)
+ .map(SqlColumnDeclaration.class::cast)
+ .collect(Collectors.toList());
+
+ List<ColumnDefinition> cols = new ArrayList<>();
+
+ for (SqlColumnDeclaration col : colDeclarations) {
+ if (!col.name.isSimple())
+ throw new IgniteSQLException("Unexpected value of
columnName [" +
+ "expected a simple identifier, but was " + col.name +
"; " +
+ "querySql=\"" + ctx.query() + "\"]",
IgniteQueryErrorCode.PARSING);
+
+ String name = col.name.getSimple();
+ RelDataType type = planner.convert(col.dataType);
+
+ Object dflt = null;
+ if (col.expression != null)
+ dflt = ((SqlLiteral)col.expression).getValue();
+
+ cols.add(new ColumnDefinition(name, type, dflt));
+ }
+
+ createTblCmd.columns(cols);
+
+ List<SqlKeyConstraint> pkConstraints =
createTblNode.columnList().getList().stream()
+ .filter(SqlKeyConstraint.class::isInstance)
+ .map(SqlKeyConstraint.class::cast)
+ .collect(Collectors.toList());
- for (SqlColumnDeclaration col : colDeclarations) {
- if (!col.name.isSimple())
- throw new IgniteSQLException("Unexpected value of columnName
[" +
- "expected a simple identifier, but was " + col.name + "; "
+
+ if (pkConstraints.size() > 1)
+ throw new IgniteSQLException("Unexpected amount of primary key
constraints [" +
+ "expected at most one, but was " + pkConstraints.size() +
"; " +
"querySql=\"" + ctx.query() + "\"]",
IgniteQueryErrorCode.PARSING);
- String name = col.name.getSimple();
- RelDataType type = planner.convert(col.dataType);
+ if (!F.isEmpty(pkConstraints)) {
+ Set<String> dedupSet = new HashSet<>();
- Object dflt = null;
- if (col.expression != null)
- dflt = ((SqlLiteral)col.expression).getValue();
+ List<String> pkCols = pkConstraints.stream()
+ .map(pk -> pk.getOperandList().get(1))
+ .map(SqlNodeList.class::cast)
+ .flatMap(l -> l.getList().stream())
+ .map(SqlIdentifier.class::cast)
+ .map(SqlIdentifier::getSimple)
+ .filter(dedupSet::add)
+ .collect(Collectors.toList());
- cols.add(new ColumnDefinition(name, type, dflt));
+ createTblCmd.primaryKeyColumns(pkCols);
+ }
}
+ else { // CREATE AS SELECT.
+ ValidationResult res =
planner.validateAndGetTypeMetadata(createTblNode.query());
- createTblCmd.columns(cols);
+ // Create INSERT node on top of AS SELECT node.
+ SqlInsert sqlInsert = new SqlInsert(
+ createTblNode.query().getParserPosition(),
+ SqlNodeList.EMPTY,
+ createTblNode.name(),
+ res.sqlNode(),
+ null
+ );
- List<SqlKeyConstraint> pkConstraints =
createTblNode.columnList().getList().stream()
- .filter(SqlKeyConstraint.class::isInstance)
- .map(SqlKeyConstraint.class::cast)
- .collect(Collectors.toList());
+ createTblCmd.insertStatement(sqlInsert);
- if (pkConstraints.size() > 1)
- throw new IgniteSQLException("Unexpected amount of primary key
constraints [" +
- "expected at most one, but was " + pkConstraints.size() + "; "
+
- "querySql=\"" + ctx.query() + "\"]",
IgniteQueryErrorCode.PARSING);
+ List<RelDataTypeField> fields = res.dataType().getFieldList();
+ List<ColumnDefinition> cols = new ArrayList<>(fields.size());
- if (!F.isEmpty(pkConstraints)) {
- Set<String> dedupSet = new HashSet<>();
+ if (createTblNode.columnList() != null) {
+ // Derive column names from the CREATE TABLE clause and column
types from the query.
+ List<SqlIdentifier> colNames =
createTblNode.columnList().getList().stream()
+ .map(SqlIdentifier.class::cast)
+ .collect(Collectors.toList());
- List<String> pkCols = pkConstraints.stream()
- .map(pk -> pk.getOperandList().get(1))
- .map(SqlNodeList.class::cast)
- .flatMap(l -> l.getList().stream())
- .map(SqlIdentifier.class::cast)
- .map(SqlIdentifier::getSimple)
- .filter(dedupSet::add)
- .collect(Collectors.toList());
+ if (fields.size() != colNames.size()) {
+ throw new IgniteSQLException("Number of columns must match
number of query columns",
+ IgniteQueryErrorCode.PARSING);
+ }
+
+ for (int i = 0; i < colNames.size(); i++) {
+ SqlIdentifier colName = colNames.get(i);
+
+ assert colName.isSimple();
+
+ RelDataType type = fields.get(i).getType();
+
+ cols.add(new ColumnDefinition(colName.getSimple(), type,
null));
+ }
+ }
+ else {
+ // Derive column names and column types from the query.
+ for (RelDataTypeField field : fields)
+ cols.add(new ColumnDefinition(field.getName(),
field.getType(), null));
+ }
+
+ createTblCmd.columns(cols);
+ }
- createTblCmd.primaryKeyColumns(pkCols);
+ if (createTblCmd.columns() == null) {
+ throw new IgniteSQLException("Column list or query should be
specified for CREATE TABLE command",
+ IgniteQueryErrorCode.PARSING);
}
return createTblCmd;
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlCreateTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlCreateTable.java
index e6a28f4..bda05e6 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlCreateTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlCreateTable.java
@@ -18,7 +18,6 @@ package
org.apache.ignite.internal.processors.query.calcite.sql;
import java.util.List;
import java.util.Objects;
-
import org.apache.calcite.sql.SqlCreate;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlKind;
@@ -42,6 +41,9 @@ public class IgniteSqlCreateTable extends SqlCreate {
private final @Nullable SqlNodeList columnList;
/** */
+ private final @Nullable SqlNode qry;
+
+ /** */
private final @Nullable SqlNodeList createOptionList;
/** */
@@ -49,18 +51,19 @@ public class IgniteSqlCreateTable extends SqlCreate {
new SqlSpecialOperator("CREATE TABLE", SqlKind.CREATE_TABLE);
/** Creates a SqlCreateTable. */
- protected IgniteSqlCreateTable(SqlParserPos pos, boolean ifNotExists,
- SqlIdentifier name, @Nullable SqlNodeList columnList, @Nullable
SqlNodeList createOptionList) {
+ protected IgniteSqlCreateTable(SqlParserPos pos, boolean ifNotExists,
SqlIdentifier name,
+ @Nullable SqlNodeList columnList, @Nullable SqlNode qry, @Nullable
SqlNodeList createOptionList) {
super(OPERATOR, pos, false, ifNotExists);
this.name = Objects.requireNonNull(name, "name");
this.columnList = columnList;
+ this.qry = qry;
this.createOptionList = createOptionList;
}
/** {@inheritDoc} */
@SuppressWarnings("nullness")
@Override public List<SqlNode> getOperandList() {
- return ImmutableNullableList.of(name, columnList, createOptionList);
+ return ImmutableNullableList.of(name, columnList, qry,
createOptionList);
}
/** {@inheritDoc} */
@@ -71,6 +74,7 @@ public class IgniteSqlCreateTable extends SqlCreate {
writer.keyword("IF NOT EXISTS");
name.unparse(writer, leftPrec, rightPrec);
+
if (columnList != null) {
SqlWriter.Frame frame = writer.startList("(", ")");
for (SqlNode c : columnList) {
@@ -85,6 +89,12 @@ public class IgniteSqlCreateTable extends SqlCreate {
createOptionList.unparse(writer, 0, 0);
}
+
+ if (qry != null) {
+ writer.keyword("AS");
+ writer.newlineAndIndent();
+ qry.unparse(writer, 0, 0);
+ }
}
/**
@@ -102,6 +112,13 @@ public class IgniteSqlCreateTable extends SqlCreate {
}
/**
+ * @return Query of "CREATE TABLE AS query" statement.
+ */
+ public SqlNode query() {
+ return qry;
+ }
+
+ /**
* @return List of the specified options to create table with.
*/
public SqlNodeList createOptionList() {
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractDdlIntegrationTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractDdlIntegrationTest.java
index f851b67..c89bbab 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractDdlIntegrationTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractDdlIntegrationTest.java
@@ -47,7 +47,7 @@ public class AbstractDdlIntegrationTest extends
GridCommonAbstractTest {
/** {@inheritDoc} */
@Override protected void beforeTestsStarted() throws Exception {
- startGrids(1);
+ startGrids(2);
client = startClientGrid(CLIENT_NODE_NAME);
@@ -55,6 +55,15 @@ public class AbstractDdlIntegrationTest extends
GridCommonAbstractTest {
}
/** {@inheritDoc} */
+ @Override protected void afterTestsStopped() throws Exception {
+ super.afterTestsStopped();
+
+ stopAllGrids();
+
+ cleanPersistenceDir();
+ }
+
+ /** {@inheritDoc} */
@Override protected IgniteConfiguration getConfiguration(String
igniteInstanceName) throws Exception {
return super.getConfiguration(igniteInstanceName)
.setSqlConfiguration(
@@ -80,13 +89,13 @@ public class AbstractDdlIntegrationTest extends
GridCommonAbstractTest {
}
/** */
- protected List<List<?>> executeSql(String sql) {
- return executeSql(client, sql);
+ protected List<List<?>> executeSql(String sql, Object... params) {
+ return executeSql(client, sql, params);
}
/** */
- protected List<List<?>> executeSql(IgniteEx ignite, String sql) {
- List<FieldsQueryCursor<List<?>>> cur =
queryProcessor(ignite).query(null, "PUBLIC", sql);
+ protected List<List<?>> executeSql(IgniteEx ignite, String sql, Object...
params) {
+ List<FieldsQueryCursor<List<?>>> cur =
queryProcessor(ignite).query(null, "PUBLIC", sql, params);
try (QueryCursor<List<?>> srvCursor = cur.get(0)) {
return srvCursor.getAll();
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/TableDdlIntegrationTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/TableDdlIntegrationTest.java
index ac81cae..d35cf9d 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/TableDdlIntegrationTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/TableDdlIntegrationTest.java
@@ -276,6 +276,99 @@ public class TableDdlIntegrationTest extends
AbstractDdlIntegrationTest {
}
/**
+ * Creates table as select.
+ */
+ @Test
+ public void createTableAsSelectSimpleCase() {
+ executeSql("create table my_table as select 1 as i, 'test' as s");
+ List<List<?>> res = executeSql("select i, s from my_table");
+
+ assertEquals(1, res.size());
+
+ List<?> row = res.get(0);
+
+ assertEquals(1, row.get(0));
+ assertEquals("test", row.get(1));
+ }
+
+ /**
+ * Creates table with specified columns as select.
+ */
+ @Test
+ public void createTableAsSelectWithColumns() {
+ executeSql("create table my_table(i, s) as select 1 as a, 'test' as
b");
+ List<List<?>> res = executeSql("select i, s from my_table");
+
+ assertEquals(1, res.size());
+
+ List<?> row = res.get(0);
+
+ assertEquals(1, row.get(0));
+ assertEquals("test", row.get(1));
+
+ assertThrows("select a from my_table", IgniteSQLException.class,
"Column 'A' not found in any table");
+ }
+
+ /**
+ * Creates table with options as select.
+ */
+ @SuppressWarnings("unchecked")
+ @Test
+ public void createTableAsSelectWithOptions() {
+ executeSql("create table my_table(s, i) with
cache_name=\"CacheWithOpts\", cache_group=\"CacheGroup\" as select '1', 1");
+ List<List<?>> res = executeSql("select * from my_table");
+
+ assertEquals(1, res.size());
+
+ List<?> row = res.get(0);
+
+ assertEquals("1", row.get(0));
+ assertEquals(1, row.get(1));
+
+ IgniteCache<?, ?> cache = client.cache("CacheWithOpts");
+ assertNotNull(cache);
+ assertEquals("CacheGroup",
cache.getConfiguration(CacheConfiguration.class).getGroupName());
+ }
+
+ /**
+ * Creates table as select with dynamic parameters to query.
+ */
+ @Test
+ public void createTableAsSelectWithParameters() {
+ executeSql("create table my_table(s, i) as select cast(? as varchar),
cast(? as int)", "a", 1);
+ List<List<?>> res = executeSql("select * from my_table");
+
+ assertEquals(1, res.size());
+
+ List<?> row = res.get(0);
+
+ assertEquals("a", row.get(0));
+ assertEquals(1, row.get(1));
+ }
+
+ /** */
+ @Test
+ public void createTableAsSelectWrongColumnsCount() {
+ GridTestUtils.assertThrowsAnyCause(log,
+ () -> executeSql("create table my_table(i, s1, s2) as select 1,
'test'"),
+ IgniteSQLException.class, "Number of columns");
+ }
+
+ /**
+ * Creates table as select from another table.
+ */
+ @Test
+ public void createTableAsSelectFromDistributedTable() {
+ executeSql("create table my_table1(i) as select x from
table(system_range(1, 100))");
+
+ assertEquals(100L, executeSql("select count(*) from
my_table1").get(0).get(0));
+
+ executeSql("create table my_table2(i) as select * from my_table1");
+
+ assertEquals(100L, executeSql("select count(*) from
my_table2").get(0).get(0));
+ }
+
+ /**
* Drops a table created in a default schema.
*/
@Test
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/sql/SqlDdlParserTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/sql/SqlDdlParserTest.java
index 0109554..de653e7 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/sql/SqlDdlParserTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/sql/SqlDdlParserTest.java
@@ -248,6 +248,38 @@ public class SqlDdlParserTest extends
GridCommonAbstractTest {
}
/**
+ * Parsing of CREATE TABLE AS SELECT.
+ */
+ @Test
+ public void createTableAsSelect() throws SqlParseException {
+ IgniteSqlCreateTable createTable = parse("create table my_table(id) as
select 1");
+
+ assertEquals(1, createTable.columnList().size());
+ assertNotNull(createTable.query());
+
+ createTable = parse("create table my_table(id, name) as select 1,
'a'");
+
+ assertEquals(2, createTable.columnList().size());
+ assertNotNull(createTable.query());
+
+ createTable = parse("create table my_table as select 1, 'a'");
+
+ assertNull(createTable.columnList());
+ assertNull(createTable.createOptionList());
+ assertNotNull(createTable.query());
+
+ createTable = parse("create table my_table with cache_name=a,
cache_group=b as select 1");
+
+ assertNull(createTable.columnList());
+ assertNotNull(createTable.createOptionList());
+ assertNotNull(createTable.query());
+
+ assertParserThrows("create table my_table(id int) as select 1",
SqlParseException.class);
+ assertParserThrows("create table my_table(id.a) as select 1",
SqlParseException.class);
+ assertParserThrows("create table my_table(id) as select 1 with
cache_name=a", SqlParseException.class);
+ }
+
+ /**
* Create index with list of indexed columns.
*/
@Test
diff --git
a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
index 9b22d09..7563c0a 100644
---
a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
+++
b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
@@ -3,10 +3,7 @@
# group: [aggregates]
statement ok
-CREATE TABLE integers(i INTEGER, j INTEGER);
-
-statement ok
-INSERT INTO integers SELECT x, 1 FROM table(system_range(0, 9999, 1)) UNION
ALL SELECT x, 2 FROM table(system_range(0, 9999, 1));
+CREATE TABLE integers AS SELECT x AS i, 1 AS j FROM table(system_range(0,
9999, 1)) UNION ALL SELECT x AS i, 2 j FROM table(system_range(0, 9999, 1));
query RR
diff --git
a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore
b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore
deleted file mode 100644
index 3d01e7f..0000000
---
a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore
+++ /dev/null
@@ -1,15 +0,0 @@
-# name: test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
-# description: Test GROUP BY with many groups
-# group: [aggregates]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14555
-
-statement ok
-CREATE TABLE integers AS SELECT x AS i, 1 AS j FROM table(system_range(0,
9999, 1)) UNION ALL SELECT x AS i, 2 j FROM table(system_range(0, 9999, 1));
-
-
-query RR
-SELECT SUM(i), SUM(sums) FROM (SELECT i, SUM(j) AS sums FROM integers GROUP BY
i) tbl1
-----
-49995000
-30000
-
diff --git
a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
index aab3c6e..f0580f5 100644
--- a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
@@ -25,10 +25,7 @@ NULL 1 1 1
# many small columns each having only the values 0 and 1
# total possible combinations is 2^10, but there are only 2 groups
statement ok
-create table manycolumns(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e
INTEGER);
-
-statement ok
-insert into manycolumns select x a, x b, x c, x d, x e from
table(system_range(0,1));
+create table manycolumns as select x a, x b, x c, x d, x e from
table(system_range(0,1));
query IIIII
select a, b, c, d, e FROM manycolumns GROUP BY a, b, c, d, e ORDER BY 1
@@ -40,10 +37,7 @@ select a, b, c, d, e FROM manycolumns GROUP BY a, b, c, d, e
ORDER BY 1
# create a table of tinyints [-127, 127] stored as varchar
# by forcing a varchar to tinyint cast we lose statistics
statement ok
-CREATE TABLE tinyints (t TINYINT);
-
-statement ok
-INSERT INTO tinyints SELECT x::TINYINT::VARCHAR AS t FROM
table(system_range(-127, 127));
+CREATE TABLE tinyints AS SELECT x::TINYINT::VARCHAR AS t FROM
table(system_range(-127, 127));
query IIII
SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT
t::TINYINT i FROM tinyints GROUP BY t)
@@ -52,10 +46,7 @@ SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i)
FROM (SELECT t::TINY
# now do the same with a single smallint column
statement ok
-CREATE TABLE smallints (t SMALLINT)
-
-statement ok
-INSERT INTO smallints SELECT x::SMALLINT::VARCHAR AS t FROM
table(system_range(-32767, 32767));
+CREATE TABLE smallints AS SELECT x::SMALLINT::VARCHAR AS t FROM
table(system_range(-32767, 32767));
query IIII
SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT
t::SMALLINT i FROM smallints GROUP BY t)
diff --git
a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignore
b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignore
index 8dba3ba..48b56b9 100644
---
a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignore
+++
b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignore
@@ -1,7 +1,6 @@
# name: test/sql/aggregate/aggregates/test_perfect_ht.test
# description: Test aggregates that can trigger a perfect HT
# group: [aggregates]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14555
# Ignored: https://issues.apache.org/jira/browse/IGNITE-14636
# Ignored: https://issues.apache.org/jira/browse/IGNITE-14730
# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
diff --git a/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test
b/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test
index c6fe9b9..82145c1 100644
--- a/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test
+++ b/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test
@@ -66,3 +66,25 @@ NULL
45672564564938245.1
4567645908450368043562342564564938245.1
+# decimal aggregates over a table
+statement ok
+CREATE TABLE decimals AS SELECT x::DECIMAL(4,1) AS d1, (x * x)::DECIMAL(9,1)
AS d2, (x * x * x)::DECIMAL(18,1) AS d3, (x * x * x * x)::DECIMAL(38,1) AS d4
FROM table(system_range(1, 999));
+
+query IIII
+SELECT SUM(d1)::VARCHAR, SUM(d2)::VARCHAR, SUM(d3)::VARCHAR, SUM(d4)::VARCHAR
FROM decimals;
+----
+499500.0
+332833500.0
+249500250000.0
+199500333333300.0
+
+statement ok
+INSERT INTO decimals VALUES ('0.1', '0.1', '0.1', '0.1'), ('0.2', '0.2',
'0.2', '0.2')
+
+query IIII
+SELECT SUM(d1)::VARCHAR, SUM(d2)::VARCHAR, SUM(d3)::VARCHAR, SUM(d4)::VARCHAR
FROM decimals;
+----
+499500.3
+332833500.3
+249500250000.3
+199500333333300.3
diff --git
a/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test_ignored
b/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test_ignored
deleted file mode 100644
index fca12be..0000000
--- a/modules/calcite/src/test/sql/types/decimal/decimal_aggregates.test_ignored
+++ /dev/null
@@ -1,91 +0,0 @@
-# name: test/sql/types/decimal/decimal_aggregates.test
-# description: Test decimal aggregates
-# group: [decimal]
-# Ignore: https://issues.apache.org/jira/browse/IGNITE-14555
-
-statement ok
-PRAGMA enable_verification
-
-query I
-SELECT typeof(ANY_VALUE('0.1'::DECIMAL(4,1)))
-----
-DECIMAL(4, 1)
-
-# scalar aggregates
-# first
-query IIIII
-SELECT ANY_VALUE(NULL::DECIMAL),
- ANY_VALUE('0.1'::DECIMAL(4,1))::VARCHAR,
- ANY_VALUE('4938245.1'::DECIMAL(9,1))::VARCHAR,
- ANY_VALUE('45672564564938245.1'::DECIMAL(18,1))::VARCHAR,
-
ANY_VALUE('4567645908450368043562342564564938245.1'::DECIMAL(38,1))::VARCHAR
-----
-NULL
-0.1
-4938245.1
-45672564564938245.1
-4567645908450368043562342564564938245.1
-
-# min
-query IIIII
-SELECT MIN(NULL::DECIMAL),
- MIN('0.1'::DECIMAL(4,1))::VARCHAR,
- MIN('4938245.1'::DECIMAL(9,1))::VARCHAR,
- MIN('45672564564938245.1'::DECIMAL(18,1))::VARCHAR,
- MIN('4567645908450368043562342564564938245.1'::DECIMAL(38,1))::VARCHAR
-----
-NULL
-0.1
-4938245.1
-45672564564938245.1
-4567645908450368043562342564564938245.1
-
-query IIIII
-SELECT MAX(NULL::DECIMAL),
- MAX('0.1'::DECIMAL(4,1))::VARCHAR,
- MAX('4938245.1'::DECIMAL(9,1))::VARCHAR,
- MAX('45672564564938245.1'::DECIMAL(18,1))::VARCHAR,
- MAX('4567645908450368043562342564564938245.1'::DECIMAL(38,1))::VARCHAR
-----
-NULL
-0.1
-4938245.1
-45672564564938245.1
-4567645908450368043562342564564938245.1
-
-# sum
-query IIIII
-SELECT SUM(NULL::DECIMAL),
- SUM('0.1'::DECIMAL(4,1))::VARCHAR,
- SUM('4938245.1'::DECIMAL(9,1))::VARCHAR,
- SUM('45672564564938245.1'::DECIMAL(18,1))::VARCHAR,
- SUM('4567645908450368043562342564564938245.1'::DECIMAL(38,1))::VARCHAR
-----
-NULL
-0.1
-4938245.1
-45672564564938245.1
-4567645908450368043562342564564938245.1
-
-# decimal aggregates over a table
-statement ok
-CREATE TABLE decimals AS SELECT i::DECIMAL(4,1) AS d1, (i * i)::DECIMAL(9,1)
AS d2, (i * i * i)::DECIMAL(18,1) AS d3, (i * i * i * i)::DECIMAL(38,1) AS d4
FROM range(1000) tbl(i);
-
-query IIII
-SELECT SUM(d1)::VARCHAR, SUM(d2)::VARCHAR, SUM(d3)::VARCHAR, SUM(d4)::VARCHAR
FROM decimals;
-----
-499500.0
-332833500.0
-249500250000.0
-199500333333300.0
-
-statement ok
-INSERT INTO decimals VALUES ('0.1', '0.1', '0.1', '0.1'), ('0.2', '0.2',
'0.2', '0.2')
-
-query IIII
-SELECT SUM(d1)::VARCHAR, SUM(d2)::VARCHAR, SUM(d3)::VARCHAR, SUM(d4)::VARCHAR
FROM decimals;
-----
-499500.3
-332833500.3
-249500250000.3
-199500333333300.3
