DRILL-5952: Implement "CREATE TABLE IF NOT EXISTS and CREATE VIEW IF NOT EXISTS"
closes #1033 Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/05d8b3c2 Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/05d8b3c2 Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/05d8b3c2 Branch: refs/heads/master Commit: 05d8b3c2c078c5cf89fcbe05a9a6cf555a21d303 Parents: 27dc0d2 Author: Prasad Nagaraj Subramanya <prasadn...@gmail.com> Authored: Thu Nov 16 23:03:43 2017 -0800 Committer: Arina Ielchiieva <arina.yelchiy...@gmail.com> Committed: Sun Nov 26 15:27:08 2017 +0200 ---------------------------------------------------------------------- .../src/main/codegen/includes/parserImpls.ftl | 25 ++++-- .../drill/exec/planner/sql/SqlConverter.java | 3 + .../sql/handlers/CreateTableHandler.java | 41 ++++++--- .../exec/planner/sql/handlers/ViewHandler.java | 69 ++++++++++----- .../sql/parser/CompoundIdentifierConverter.java | 2 +- .../exec/planner/sql/parser/SqlCreateTable.java | 22 +++-- .../exec/planner/sql/parser/SqlCreateView.java | 37 +++++--- .../org/apache/drill/exec/sql/TestCTAS.java | 61 +++++++++++++ .../org/apache/drill/exec/sql/TestCTTAS.java | 60 +++++++++++++ .../apache/drill/exec/sql/TestViewSupport.java | 90 ++++++++++++++++++++ 10 files changed, 347 insertions(+), 63 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/codegen/includes/parserImpls.ftl ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/codegen/includes/parserImpls.ftl b/exec/java-exec/src/main/codegen/includes/parserImpls.ftl index d9ceed9..67d1501 100644 --- a/exec/java-exec/src/main/codegen/includes/parserImpls.ftl +++ b/exec/java-exec/src/main/codegen/includes/parserImpls.ftl @@ -172,26 +172,34 @@ SqlNodeList ParseRequiredFieldList(String relType) : /** * Parses a create view or replace existing view statement. - * CREATE [OR REPLACE] VIEW view_name [ (field1, field2 ...) ] AS select_statement + * CREATE { [OR REPLACE] VIEW | VIEW [IF NOT EXISTS] | VIEW } view_name [ (field1, field2 ...) ] AS select_statement */ SqlNode SqlCreateOrReplaceView() : { SqlParserPos pos; - boolean replaceView = false; SqlIdentifier viewName; SqlNode query; SqlNodeList fieldList; + String createViewType = "SIMPLE"; } { <CREATE> { pos = getPos(); } - [ <OR> <REPLACE> { replaceView = true; } ] + [ <OR> <REPLACE> { createViewType = "OR_REPLACE"; } ] <VIEW> + [ + <IF> <NOT> <EXISTS> { + if (createViewType == "OR_REPLACE") { + throw new ParseException("Create view statement cannot have both <OR REPLACE> and <IF NOT EXISTS> clause"); + } + createViewType = "IF_NOT_EXISTS"; + } + ] viewName = CompoundIdentifier() fieldList = ParseOptionalFieldList("View") <AS> query = OrderedQueryOrExpr(ExprContext.ACCEPT_QUERY) { - return new SqlCreateView(pos, viewName, fieldList, query, replaceView); + return new SqlCreateView(pos, viewName, fieldList, query, SqlLiteral.createCharString(createViewType, getPos())); } } @@ -215,7 +223,7 @@ SqlNode SqlDropView() : /** * Parses a CTAS or CTTAS statement. - * CREATE [TEMPORARY] TABLE tblname [ (field1, field2, ...) ] AS select_statement. + * CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tblname [ (field1, field2, ...) ] AS select_statement. */ SqlNode SqlCreateTable() : { @@ -225,6 +233,7 @@ SqlNode SqlCreateTable() : SqlNodeList partitionFieldList; SqlNode query; boolean isTemporary = false; + boolean tableNonExistenceCheck = false; } { { @@ -233,6 +242,7 @@ SqlNode SqlCreateTable() : <CREATE> { pos = getPos(); } ( <TEMPORARY> { isTemporary = true; } )? <TABLE> + ( <IF> <NOT> <EXISTS> { tableNonExistenceCheck = true; } )? tblName = CompoundIdentifier() fieldList = ParseOptionalFieldList("Table") ( <PARTITION> <BY> @@ -241,8 +251,9 @@ SqlNode SqlCreateTable() : <AS> query = OrderedQueryOrExpr(ExprContext.ACCEPT_QUERY) { - return new SqlCreateTable(pos,tblName, fieldList, partitionFieldList, query, - SqlLiteral.createBoolean(isTemporary, getPos())); + return new SqlCreateTable(pos, tblName, fieldList, partitionFieldList, query, + SqlLiteral.createBoolean(isTemporary, getPos()), + SqlLiteral.createBoolean(tableNonExistenceCheck, getPos())); } } http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/SqlConverter.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/SqlConverter.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/SqlConverter.java index 0a0e5f6..11b90e2 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/SqlConverter.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/SqlConverter.java @@ -381,6 +381,9 @@ public class SqlConverter { * @return The sql with a ^ character under the error */ static String formatSQLParsingError(String sql, SqlParserPos pos) { + if (pos == null) { + return sql; + } StringBuilder sb = new StringBuilder(); String[] lines = sql.split("\n"); for (int i = 0; i < lines.length; i++) { http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/CreateTableHandler.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/CreateTableHandler.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/CreateTableHandler.java index d232a71..4491bff 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/CreateTableHandler.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/CreateTableHandler.java @@ -40,6 +40,7 @@ import org.apache.drill.common.exceptions.UserException; import org.apache.drill.exec.ExecConstants; import org.apache.drill.exec.physical.PhysicalPlan; import org.apache.drill.exec.physical.base.PhysicalOperator; +import org.apache.drill.exec.planner.sql.DirectPlan; import org.apache.drill.exec.rpc.user.UserSession; import org.apache.drill.exec.store.StorageStrategy; import org.apache.drill.exec.planner.logical.DrillRel; @@ -70,8 +71,8 @@ public class CreateTableHandler extends DefaultSqlHandler { @Override public PhysicalPlan getPlan(SqlNode sqlNode) throws ValidationException, RelConversionException, IOException, ForemanSetupException { - SqlCreateTable sqlCreateTable = unwrap(sqlNode, SqlCreateTable.class); - String originalTableName = sqlCreateTable.getName(); + final SqlCreateTable sqlCreateTable = unwrap(sqlNode, SqlCreateTable.class); + final String originalTableName = sqlCreateTable.getName(); final ConvertedRelNode convertedRelNode = validateAndConvert(sqlCreateTable.getQuery()); final RelDataType validatedRowType = convertedRelNode.getValidatedRowType(); @@ -82,8 +83,14 @@ public class CreateTableHandler extends DefaultSqlHandler { final DrillConfig drillConfig = context.getConfig(); final AbstractSchema drillSchema = resolveSchema(sqlCreateTable, config.getConverter().getDefaultSchema(), drillConfig); + final boolean checkTableNonExistence = sqlCreateTable.checkTableNonExistence(); + final String schemaPath = drillSchema.getFullSchemaName(); - checkDuplicatedObjectExistence(drillSchema, originalTableName, drillConfig, context.getSession()); + // Check table creation possibility + if(!checkTableCreationPossibility(drillSchema, originalTableName, drillConfig, context.getSession(), schemaPath, checkTableNonExistence)) { + return DirectPlan.createDirectPlan(context, false, + String.format("A table or view with given name [%s] already exists in schema [%s]", originalTableName, schemaPath)); + } final RelNode newTblRelNodeWithPCol = SqlHandlerUtil.qualifyPartitionCol(newTblRelNode, sqlCreateTable.getPartitionColumns()); @@ -289,28 +296,36 @@ public class CreateTableHandler extends DefaultSqlHandler { } /** - * Checks if any object (persistent table / temporary table / view) - * with the same name as table to be created exists in indicated schema. + * Validates if table can be created in indicated schema + * Checks if any object (persistent table / temporary table / view) with the same name exists + * or if object with the same name exists but if not exists flag is set. * * @param drillSchema schema where table will be created * @param tableName table name * @param config drill config * @param userSession current user session - * @throws UserException if duplicate is found + * @param schemaPath schema path + * @param checkTableNonExistence whether duplicate check is requested + * @return if duplicate found in indicated schema + * @throws UserException if duplicate found in indicated schema and no duplicate check requested */ - private void checkDuplicatedObjectExistence(AbstractSchema drillSchema, + private boolean checkTableCreationPossibility(AbstractSchema drillSchema, String tableName, DrillConfig config, - UserSession userSession) { - String schemaPath = drillSchema.getFullSchemaName(); + UserSession userSession, + String schemaPath, + boolean checkTableNonExistence) { boolean isTemporaryTable = userSession.isTemporaryTable(drillSchema, config, tableName); if (isTemporaryTable || SqlHandlerUtil.getTableFromSchema(drillSchema, tableName) != null) { - throw UserException - .validationError() - .message("A table or view with given name [%s] already exists in schema [%s]", - tableName, schemaPath) + if (checkTableNonExistence) { + return false; + } else { + throw UserException.validationError() + .message("A table or view with given name [%s] already exists in schema [%s]", tableName, schemaPath) .build(logger); + } } + return true; } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/ViewHandler.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/ViewHandler.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/ViewHandler.java index 495e8b5..e86b90a 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/ViewHandler.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/handlers/ViewHandler.java @@ -1,4 +1,4 @@ -/** +/* * 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 @@ -77,8 +77,13 @@ public abstract class ViewHandler extends DefaultSqlHandler { final View view = new View(newViewName, viewSql, newViewRelNode.getRowType(), SchemaUtilites.getSchemaPathAsList(defaultSchema)); + final String schemaPath = drillSchema.getFullSchemaName(); - validateViewCreationPossibility(drillSchema, createView, context); + // check view creation possibility + if(!checkViewCreationPossibility(drillSchema, createView, context)) { + return DirectPlan + .createDirectPlan(context, false, String.format("A table or view with given name [%s] already exists in schema [%s]", view.getName(), schemaPath)); + } final boolean replaced = drillSchema.createView(view); final String summary = String.format("View '%s' %s successfully in '%s' schema", @@ -90,35 +95,55 @@ public abstract class ViewHandler extends DefaultSqlHandler { /** * Validates if view can be created in indicated schema: * checks if object (persistent / temporary table) with the same name exists - * or if view with the same name exists but replace flag is not set. + * or if view with the same name exists but replace flag is not set + * or if object with the same name exists but if not exists flag is set. * * @param drillSchema schema where views will be created * @param view create view call * @param context query context - * @throws UserException if views can be created in indicated schema + * @return if view can be created in indicated schema + * @throws UserException if view cannot be created in indicated schema and no duplicate check requested */ - private void validateViewCreationPossibility(AbstractSchema drillSchema, SqlCreateView view, QueryContext context) { + private boolean checkViewCreationPossibility(AbstractSchema drillSchema, SqlCreateView view, QueryContext context) { final String schemaPath = drillSchema.getFullSchemaName(); final String viewName = view.getName(); - final Table existingTable = SqlHandlerUtil.getTableFromSchema(drillSchema, viewName); - - if ((existingTable != null && existingTable.getJdbcTableType() != Schema.TableType.VIEW) || - context.getSession().isTemporaryTable(drillSchema, context.getConfig(), viewName)) { - // existing table is not a view - throw UserException - .validationError() - .message("A non-view table with given name [%s] already exists in schema [%s]", viewName, schemaPath) - .build(logger); - } - - if ((existingTable != null && existingTable.getJdbcTableType() == Schema.TableType.VIEW) && !view.getReplace()) { - // existing table is a view and create view has no "REPLACE" clause - throw UserException - .validationError() - .message("A view with given name [%s] already exists in schema [%s]", viewName, schemaPath) - .build(logger); + final Table table = SqlHandlerUtil.getTableFromSchema(drillSchema, viewName); + + final boolean isTable = (table != null && table.getJdbcTableType() != Schema.TableType.VIEW) + || context.getSession().isTemporaryTable(drillSchema, context.getConfig(), viewName); + final boolean isView = (table != null && table.getJdbcTableType() == Schema.TableType.VIEW); + + switch (view.getcreateViewType()) { + case SIMPLE: + if (isTable) { + throw UserException + .validationError() + .message("A non-view table with given name [%s] already exists in schema [%s]", viewName, schemaPath) + .build(logger); + } else if (isView) { + throw UserException + .validationError() + .message("A view with given name [%s] already exists in schema [%s]", viewName, schemaPath) + .build(logger); + } + break; + case OR_REPLACE: + if (isTable) { + throw UserException + .validationError() + .message("A non-view table with given name [%s] already exists in schema [%s]", viewName, schemaPath) + .build(logger); + } + break; + case IF_NOT_EXISTS: + if (isTable || isView) { + return false; + } + break; } + return true; } + } /** Handler for Drop View [If Exists] DDL command. */ http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/CompoundIdentifierConverter.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/CompoundIdentifierConverter.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/CompoundIdentifierConverter.java index db934e2..e9d0dca 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/CompoundIdentifierConverter.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/CompoundIdentifierConverter.java @@ -161,7 +161,7 @@ public class CompoundIdentifierConverter extends SqlShuttle { //SqlNode offset, //SqlNode fetch, rules.put(SqlSelect.class, R(D, E, D, E, E, E, E, E, D, D)); - rules.put(SqlCreateTable.class, R(D, D, D, E, D)); + rules.put(SqlCreateTable.class, R(D, D, D, E, D, D)); rules.put(SqlCreateView.class, R(D, E, E, D)); rules.put(SqlDescribeTable.class, R(D, D, E)); rules.put(SqlDropView.class, R(D, D)); http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateTable.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateTable.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateTable.java index bba60b2..457e78c 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateTable.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateTable.java @@ -20,14 +20,10 @@ package org.apache.drill.exec.planner.sql.parser; import java.util.List; import com.google.common.base.Preconditions; -import org.apache.calcite.tools.Planner; -import org.apache.drill.common.expression.SchemaPath; -import org.apache.drill.exec.ops.QueryContext; import org.apache.drill.exec.planner.sql.handlers.AbstractSqlHandler; import org.apache.drill.exec.planner.sql.handlers.CreateTableHandler; import org.apache.drill.exec.planner.sql.handlers.SqlHandlerConfig; -import org.apache.calcite.plan.hep.HepPlanner; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlKind; @@ -48,13 +44,14 @@ public class SqlCreateTable extends DrillSqlCall { public static final SqlSpecialOperator OPERATOR = new SqlSpecialOperator("CREATE_TABLE", SqlKind.OTHER) { @Override public SqlCall createCall(SqlLiteral functionQualifier, SqlParserPos pos, SqlNode... operands) { - Preconditions.checkArgument(operands.length == 5, "SqlCreateTable.createCall() has to get 5 operands!"); + Preconditions.checkArgument(operands.length == 6, "SqlCreateTable.createCall() has to get 6 operands!"); return new SqlCreateTable(pos, (SqlIdentifier) operands[0], (SqlNodeList) operands[1], (SqlNodeList) operands[2], operands[3], - (SqlLiteral) operands[4]); + (SqlLiteral) operands[4], + (SqlLiteral) operands[5]); } }; @@ -63,19 +60,22 @@ public class SqlCreateTable extends DrillSqlCall { private final SqlNodeList partitionColumns; private final SqlNode query; private final SqlLiteral isTemporary; + private final SqlLiteral tableNonExistenceCheck; public SqlCreateTable(SqlParserPos pos, SqlIdentifier tblName, SqlNodeList fieldList, SqlNodeList partitionColumns, SqlNode query, - SqlLiteral isTemporary) { + SqlLiteral isTemporary, + SqlLiteral tableNonExistenceCheck) { super(pos); this.tblName = tblName; this.fieldList = fieldList; this.partitionColumns = partitionColumns; this.query = query; this.isTemporary = isTemporary; + this.tableNonExistenceCheck = tableNonExistenceCheck; } @Override @@ -91,6 +91,7 @@ public class SqlCreateTable extends DrillSqlCall { ops.add(partitionColumns); ops.add(query); ops.add(isTemporary); + ops.add(tableNonExistenceCheck); return ops; } @@ -101,6 +102,11 @@ public class SqlCreateTable extends DrillSqlCall { writer.keyword("TEMPORARY"); } writer.keyword("TABLE"); + if (tableNonExistenceCheck.booleanValue()) { + writer.keyword("IF"); + writer.keyword("NOT"); + writer.keyword("EXISTS"); + } tblName.unparse(writer, leftPrec, rightPrec); if (fieldList.size() > 0) { SqlHandlerUtil.unparseSqlNodeList(writer, leftPrec, rightPrec, fieldList); @@ -160,4 +166,6 @@ public class SqlCreateTable extends DrillSqlCall { public boolean isTemporary() { return isTemporary.booleanValue(); } + public boolean checkTableNonExistence() { return tableNonExistenceCheck.booleanValue(); } + } http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateView.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateView.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateView.java index cc6118a..e7ee9c9 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateView.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/parser/SqlCreateView.java @@ -23,6 +23,7 @@ import org.apache.drill.exec.planner.sql.handlers.AbstractSqlHandler; import org.apache.drill.exec.planner.sql.handlers.SqlHandlerConfig; import org.apache.drill.exec.planner.sql.handlers.SqlHandlerUtil; import org.apache.drill.exec.planner.sql.handlers.ViewHandler; +import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlKind; @@ -32,7 +33,6 @@ 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.parser.SqlParserPos; import java.util.List; @@ -47,20 +47,19 @@ public class SqlCreateView extends DrillSqlCall { private SqlIdentifier viewName; private SqlNodeList fieldList; private SqlNode query; - private boolean replaceView; + private SqlLiteral createViewType; - public SqlCreateView(SqlParserPos pos, SqlIdentifier viewName, SqlNodeList fieldList, - SqlNode query, SqlLiteral replaceView) { - this(pos, viewName, fieldList, query, replaceView.booleanValue()); + public enum SqlCreateViewType { + SIMPLE, OR_REPLACE, IF_NOT_EXISTS } public SqlCreateView(SqlParserPos pos, SqlIdentifier viewName, SqlNodeList fieldList, - SqlNode query, boolean replaceView) { + SqlNode query, SqlLiteral createViewType) { super(pos); this.viewName = viewName; this.query = query; - this.replaceView = replaceView; this.fieldList = fieldList; + this.createViewType = createViewType; } @Override @@ -74,18 +73,29 @@ public class SqlCreateView extends DrillSqlCall { ops.add(viewName); ops.add(fieldList); ops.add(query); - ops.add(SqlLiteral.createBoolean(replaceView, SqlParserPos.ZERO)); + ops.add(createViewType); return ops; } @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec) { writer.keyword("CREATE"); - if (replaceView) { - writer.keyword("OR"); - writer.keyword("REPLACE"); + switch (SqlCreateViewType.valueOf(createViewType.toValue())) { + case SIMPLE: + writer.keyword("VIEW"); + break; + case OR_REPLACE: + writer.keyword("OR"); + writer.keyword("REPLACE"); + writer.keyword("VIEW"); + break; + case IF_NOT_EXISTS: + writer.keyword("VIEW"); + writer.keyword("IF"); + writer.keyword("NOT"); + writer.keyword("EXISTS"); + break; } - writer.keyword("VIEW"); viewName.unparse(writer, leftPrec, rightPrec); if (fieldList.size() > 0) { SqlHandlerUtil.unparseSqlNodeList(writer, leftPrec, rightPrec, fieldList); @@ -124,6 +134,7 @@ public class SqlCreateView extends DrillSqlCall { } public SqlNode getQuery() { return query; } - public boolean getReplace() { return replaceView; } + + public SqlCreateViewType getcreateViewType() { return SqlCreateViewType.valueOf(createViewType.toValue()); } } http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTAS.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTAS.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTAS.java index 764cef4..2315a03 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTAS.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTAS.java @@ -32,6 +32,7 @@ import org.junit.experimental.categories.Category; import java.util.Map; +import static org.apache.drill.exec.util.StoragePluginTestUtils.DFS_TMP_SCHEMA; import static org.junit.Assert.assertEquals; @Category(SqlTest.class) @@ -254,6 +255,66 @@ public class TestCTAS extends BaseTestQuery { } } + @Test // DRILL-5952 + public void testCreateTableIfNotExistsWhenTableWithSameNameAlreadyExists() throws Exception{ + final String newTblName = "createTableIfNotExistsWhenATableWithSameNameAlreadyExists"; + + try { + String ctasQuery = String.format("CREATE TABLE %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + ctasQuery = + String.format("CREATE TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName); + + testBuilder() + .sqlQuery(ctasQuery) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, String.format("A table or view with given name [%s] already exists in schema [%s]", newTblName, DFS_TMP_SCHEMA)) + .go(); + } finally { + test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName); + } + } + + @Test // DRILL-5952 + public void testCreateTableIfNotExistsWhenViewWithSameNameAlreadyExists() throws Exception{ + final String newTblName = "createTableIfNotExistsWhenAViewWithSameNameAlreadyExists"; + + try { + String ctasQuery = String.format("CREATE VIEW %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + ctasQuery = + String.format("CREATE TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName); + + testBuilder() + .sqlQuery(ctasQuery) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, String.format("A table or view with given name [%s] already exists in schema [%s]", newTblName, DFS_TMP_SCHEMA)) + .go(); + } finally { + test("DROP VIEW IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName); + } + } + + @Test // DRILL-5952 + public void testCreateTableIfNotExistsWhenTableWithSameNameDoesNotExist() throws Exception{ + final String newTblName = "createTableIfNotExistsWhenATableWithSameNameDoesNotExist"; + + try { + String ctasQuery = String.format("CREATE TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + } finally { + test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName); + } + } + private static void ctasErrorTestHelper(final String ctasSql, final String expErrorMsg) throws Exception { final String createTableSql = String.format(ctasSql, "testTableName"); errorMsgTestHelper(createTableSql, expErrorMsg); http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java index bc06af3..e83fa37 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java @@ -383,6 +383,66 @@ public class TestCTTAS extends BaseTestQuery { } } + @Test // DRILL-5952 + public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameAlreadyExists() throws Exception{ + final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameAlreadyExists"; + + try { + String ctasQuery = String.format("CREATE TEMPORARY TABLE %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + ctasQuery = + String.format("CREATE TEMPORARY TABLE IF NOT EXISTS %s AS SELECT * FROM cp.`employee.json`", newTblName); + + testBuilder() + .sqlQuery(ctasQuery) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, String.format("A table or view with given name [%s] already exists in schema [%s]", newTblName, DFS_TMP_SCHEMA)) + .go(); + } finally { + test(String.format("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName)); + } + } + + @Test // DRILL-5952 + public void testCreateTemporaryTableIfNotExistsWhenViewWithSameNameAlreadyExists() throws Exception{ + final String newTblName = "createTemporaryTableIfNotExistsWhenAViewWithSameNameAlreadyExists"; + + try { + String ctasQuery = String.format("CREATE VIEW %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + ctasQuery = + String.format("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName); + + testBuilder() + .sqlQuery(ctasQuery) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, String.format("A table or view with given name [%s] already exists in schema [%s]", newTblName, DFS_TMP_SCHEMA)) + .go(); + } finally { + test(String.format("DROP VIEW IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName)); + } + } + + @Test // DRILL-5952 + public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameDoesNotExist() throws Exception{ + final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameDoesNotExist"; + + try { + String ctasQuery = String.format("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName); + + test(ctasQuery); + + } finally { + test(String.format("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName)); + } + } + @Test public void testManualDropWithoutSchema() throws Exception { String temporaryTableName = "temporary_table_to_drop_without_schema"; http://git-wip-us.apache.org/repos/asf/drill/blob/05d8b3c2/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java index d571c66..6efa5ce 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java @@ -311,6 +311,96 @@ public class TestViewSupport extends TestBaseViewSupport { } } + @Test // DRILL-5952 + public void createViewIfNotExistsWhenTableAlreadyExists() throws Exception { + final String tableName = generateViewName(); + + try { + final String tableDef = "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY `region_id` LIMIT 2"; + + test("CREATE TABLE %s.%s as %s", DFS_TMP_SCHEMA, tableName, tableDef); + + // Try to create the view with same name in same schema with if not exists clause. + final String createViewSql = String.format("CREATE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, tableName, tableDef); + + testBuilder() + .sqlQuery(createViewSql) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, + String.format("A table or view with given name [%s] already exists in schema [%s]", tableName, DFS_TMP_SCHEMA)) + .go(); + + } finally { + FileUtils.deleteQuietly(new File(dirTestWatcher.getDfsTestTmpDir(), tableName)); + } + } + + @Test // DRILL-5952 + public void createViewIfNotExistsWhenViewAlreadyExists() throws Exception { + final String viewName = generateViewName(); + + try { + final String viewDef1 = "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY `region_id` LIMIT 2"; + + // Create the view + createViewHelper(DFS_TMP_SCHEMA, viewName, DFS_TMP_SCHEMA, null, viewDef1); + + // Try to create the view with same name in same schema with if not exists clause. + final String viewDef2 = "SELECT sales_state_province FROM cp.`region.json` ORDER BY `region_id`"; + final String createViewSql = String.format("CREATE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, viewName, viewDef2); + + testBuilder() + .sqlQuery(createViewSql) + .unOrdered() + .baselineColumns("ok", "summary") + .baselineValues(false, + String.format("A table or view with given name [%s] already exists in schema [%s]", viewName, DFS_TMP_SCHEMA)) + .go(); + + // Make sure the view created returns the data expected. + queryViewHelper(String.format("SELECT * FROM %s.`%s` LIMIT 1", DFS_TMP_SCHEMA, viewName), + new String[]{"region_id", "sales_city"}, + ImmutableList.of(new Object[]{0L, "None"}) + ); + } finally { + dropViewHelper(DFS_TMP_SCHEMA, viewName, DFS_TMP_SCHEMA); + } + } + + @Test // DRILL-5952 + public void testCreateViewIfNotExists() throws Exception { + final String viewName = generateViewName(); + + try { + final String viewDef = "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY `region_id` LIMIT 2"; + + final String createViewSql = String.format("CREATE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, viewName, viewDef); + + test(createViewSql); + + // Make sure the view created returns the data expected. + queryViewHelper(String.format("SELECT * FROM %s.`%s` LIMIT 1", DFS_TMP_SCHEMA, viewName), + new String[]{"region_id", "sales_city"}, + ImmutableList.of(new Object[]{0L, "None"}) + ); + } finally { + dropViewHelper(DFS_TMP_SCHEMA, viewName, DFS_TMP_SCHEMA); + } + } + + @Test // DRILL-5952 + public void createViewWithBothOrReplaceAndIfNotExists() throws Exception { + final String viewName = generateViewName(); + + final String viewDef = "SELECT region_id, sales_city FROM cp.`region.json`"; + + // Try to create the view with both <or replace> and <if not exists> clause. + final String createViewSql = String.format("CREATE OR REPLACE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, viewName, viewDef); + + errorMsgTestHelper(createViewSql, "Create view statement cannot have both <OR REPLACE> and <IF NOT EXISTS> clause"); + } + @Test // DRILL-2422 @Category(UnlikelyTest.class) public void createViewWhenATableWithSameNameAlreadyExists() throws Exception {