exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestCTTAS.java - drill - Git at Google

 /*
  * 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.drill.exec.sql;

 import static org.apache.drill.exec.util.StoragePluginTestUtils.DFS_PLUGIN_NAME;
 import static org.apache.drill.exec.util.StoragePluginTestUtils.DFS_TMP_SCHEMA;
 import static org.hamcrest.CoreMatchers.containsString;

 import java.io.File;
 import java.nio.file.Paths;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Optional;

 import org.apache.drill.categories.SqlTest;
 import org.apache.drill.common.exceptions.UserRemoteException;
 import org.apache.drill.exec.store.StoragePluginRegistry;
 import org.apache.drill.exec.store.dfs.FileSystemConfig;
 import org.apache.drill.exec.store.dfs.WorkspaceConfig;
 import org.apache.drill.common.logical.security.PlainCredentialsProvider;
 import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
 import org.apache.drill.test.BaseTestQuery;
 import org.junit.BeforeClass;
 import org.junit.Rule;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 import org.junit.rules.ExpectedException;

 @Category(SqlTest.class)
 public class TestCTTAS extends BaseTestQuery {

   private static final String temp2_wk = "tmp2";
   private static final String temp2_schema = String.format("%s.%s", DFS_PLUGIN_NAME, temp2_wk);

   @Rule
   public ExpectedException thrown = ExpectedException.none();

   @BeforeClass
   public static void init() throws Exception {
     File tmp2 = dirTestWatcher.makeSubDir(Paths.get("tmp2"));
     StoragePluginRegistry pluginRegistry = getDrillbitContext().getStorage();
     FileSystemConfig pluginConfig = (FileSystemConfig) pluginRegistry.getPlugin(DFS_PLUGIN_NAME).getConfig();

     Map<String, WorkspaceConfig> newWorkspaces = new HashMap<>();
     Optional.ofNullable(pluginConfig.getWorkspaces())
       .ifPresent(newWorkspaces::putAll);
     newWorkspaces.put(temp2_wk, new WorkspaceConfig(tmp2.getAbsolutePath(), true, null, false));

     FileSystemConfig newPluginConfig = new FileSystemConfig(
         pluginConfig.getConnection(),
         pluginConfig.getConfig(),
         newWorkspaces,
         pluginConfig.getFormats(),
         PlainCredentialsProvider.EMPTY_CREDENTIALS_PROVIDER);
     newPluginConfig.setEnabled(pluginConfig.isEnabled());
     pluginRegistry.put(DFS_PLUGIN_NAME, newPluginConfig);
   }

   @Test
   public void testSyntax() throws Exception {
     test("create TEMPORARY table temporary_keyword as select 1 from (values(1))");
     test("create TEMPORARY table %s.temporary_keyword_with_wk as select 1 from (values(1))", DFS_TMP_SCHEMA);
   }

   @Test
   public void testCreateTableWithDifferentStorageFormats() throws Exception {
     List<String> storageFormats = Lists.newArrayList("parquet", "json", "csvh");

     try {
       for (String storageFormat : storageFormats) {
         String temporaryTableName = "temp_" + storageFormat;
         test("alter session set `store.format`='%s'", storageFormat);
         test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

         testBuilder()
             .sqlQuery("select * from %s", temporaryTableName)
             .unOrdered()
             .baselineColumns("c1")
             .baselineValues("A")
             .go();

         testBuilder()
             .sqlQuery("select * from %s", temporaryTableName)
             .unOrdered()
             .sqlBaselineQuery("select * from %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
             .go();
       }
     } finally {
       resetSessionOption("store.format");
     }
   }

   @Test
   public void testTemporaryTablesCaseInsensitivity() throws Exception {
     String temporaryTableName = "tEmP_InSeNSiTiVe";
     List<String> temporaryTableNames = Lists.newArrayList(
         temporaryTableName,
         temporaryTableName.toLowerCase(),
         temporaryTableName.toUpperCase());

     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);
     for (String tableName : temporaryTableNames) {
       testBuilder()
           .sqlQuery("select * from %s", tableName)
           .unOrdered()
           .baselineColumns("c1")
           .baselineValues("A")
           .go();
     }
   }

   @Test
   public void testResolveTemporaryTableWithPartialSchema() throws Exception {
     String temporaryTableName = "temporary_table_with_partial_schema";
     test("use %s", DFS_PLUGIN_NAME);
     test("create temporary table tmp.%s as select 'A' as c1 from (values(1))", temporaryTableName);

     testBuilder()
         .sqlQuery("select * from tmp.%s", temporaryTableName)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("A")
         .go();
   }

   @Test
   public void testPartitionByWithTemporaryTables() throws Exception {
     String temporaryTableName = "temporary_table_with_partitions";
     test("create TEMPORARY table %s partition by (c1) as select * from (" +
         "select 'A' as c1 from (values(1)) union all select 'B' as c1 from (values(1))) t", temporaryTableName);
   }

   @Test
   public void testCreationOutsideOfDefaultTemporaryWorkspace() throws Exception {
     String temporaryTableName = "temporary_table_outside_of_default_workspace";

     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: Temporary tables are not allowed to be created / dropped " +
         "outside of default temporary workspace [%s].", DFS_TMP_SCHEMA));

     test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", temp2_schema, temporaryTableName);
   }

   @Test
   public void testCreateWhenTemporaryTableExistsWithoutSchema() throws Exception {
     String temporaryTableName = "temporary_table_exists_without_schema";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);
   }

   @Test
   public void testCreateWhenTemporaryTableExistsCaseInsensitive() throws Exception {
     String temporaryTableName = "temporary_table_exists_case_insensitive";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName.toUpperCase(), DFS_TMP_SCHEMA);

     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName.toUpperCase());
   }

   @Test
   public void testCreateWhenTemporaryTableExistsWithSchema() throws Exception {
     String temporaryTableName = "temporary_table_exists_with_schema";
     test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);

     expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

     test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
   }

   @Test
   public void testCreateWhenPersistentTableExists() throws Exception {
     String persistentTableName = "persistent_table_exists";
     test("create table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, persistentTableName);

     expectUserRemoteExceptionWithTableExistsMessage(persistentTableName, DFS_TMP_SCHEMA);

     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", persistentTableName);
   }

   @Test
   public void testCreateWhenViewExists() throws Exception {
     String viewName = "view_exists";
     test("create view %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, viewName);

     expectUserRemoteExceptionWithTableExistsMessage(viewName, DFS_TMP_SCHEMA);

     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", viewName);
   }

   @Test
   public void testCreatePersistentTableWhenTemporaryTableExists() throws Exception {
     String temporaryTableName = "temporary_table_exists_before_persistent";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

     test("create table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
   }

   @Test
   public void testCreateViewWhenTemporaryTableExists() throws Exception {
     String temporaryTableName = "temporary_table_exists_before_view";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: A non-view table with given name [%s] already exists in schema [%s]",
       temporaryTableName, DFS_TMP_SCHEMA));

     test("create view %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
   }

   @Test
   public void testSelectWithJoinOnTemporaryTables() throws Exception {
     String temporaryLeftTableName = "temporary_left_table_for_Select_with_join";
     String temporaryRightTableName = "temporary_right_table_for_Select_with_join";
     test("create TEMPORARY table %s as select 'A' as c1, 'B' as c2 from (values(1))", temporaryLeftTableName);
     test("create TEMPORARY table %s as select 'A' as c1, 'C' as c2 from (values(1))", temporaryRightTableName);

     testBuilder()
         .sqlQuery("select t1.c2 col1, t2.c2 col2 from %s t1 join %s t2 on t1.c1 = t2.c1", temporaryLeftTableName, temporaryRightTableName)
         .unOrdered()
         .baselineColumns("col1", "col2")
         .baselineValues("B", "C")
         .go();
   }

   @Test
   public void testTemporaryAndPersistentTablesPriority() throws Exception {
     String name = "temporary_and_persistent_table";
     test("use %s", temp2_schema);
     test("create TEMPORARY table %s as select 'temporary_table' as c1 from (values(1))", name);
     test("create table %s as select 'persistent_table' as c1 from (values(1))", name);

     testBuilder()
         .sqlQuery("select * from %s", name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("temporary_table")
         .go();

     testBuilder()
         .sqlQuery("select * from %s.%s", temp2_schema, name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("persistent_table")
         .go();

     test("drop table %s", name);

     testBuilder()
         .sqlQuery("select * from %s", name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("persistent_table")
         .go();
   }

   @Test
   public void testTemporaryTableAndViewPriority() throws Exception {
     String name = "temporary_table_and_view";
     test("use %s", temp2_schema);
     test("create TEMPORARY table %s as select 'temporary_table' as c1 from (values(1))", name);
     test("create view %s as select 'view' as c1 from (values(1))", name);

     testBuilder()
         .sqlQuery("select * from %s", name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("temporary_table")
         .go();

     testBuilder()
         .sqlQuery("select * from %s.%s", temp2_schema, name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("view")
         .go();

     test("drop table %s", name);

     testBuilder()
         .sqlQuery("select * from %s", name)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("view")
         .go();
   }

   @Test
   public void testTemporaryTablesInViewDefinitions() throws Exception {
     String temporaryTableName = "temporary_table_for_view_definition";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: Temporary tables usage is disallowed. Used temporary table name: [%s]", temporaryTableName));

     test("create view %s.view_with_temp_table as select * from %s", DFS_TMP_SCHEMA, temporaryTableName);
   }

   @Test
   public void testTemporaryTablesInViewExpansionLogic() throws Exception {
     String tableName = "table_for_expansion_logic_test";
     String viewName = "view_for_expansion_logic_test";
     test("use %s", DFS_TMP_SCHEMA);
     test("create table %s as select 'TABLE' as c1 from (values(1))", tableName);
     test("create view %s as select * from %s", viewName, tableName);

     testBuilder()
         .sqlQuery("select * from %s", viewName)
         .unOrdered()
         .baselineColumns("c1")
         .baselineValues("TABLE")
         .go();

     test("drop table %s", tableName);
     test("create temporary table %s as select 'TEMP' as c1 from (values(1))", tableName);

     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: Temporary tables usage is disallowed. Used temporary table name: [%s]", tableName));

     test("select * from %s", viewName);
   }

   @Test // DRILL-5952
   public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameAlreadyExists() throws Exception{
     final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameAlreadyExists";
     test("CREATE TEMPORARY TABLE %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName);

     testBuilder()
       .sqlQuery("CREATE TEMPORARY TABLE IF NOT EXISTS %s AS SELECT * FROM cp.`employee.json`", newTblName)
       .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();

     test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
   }

   @Test // DRILL-5952
   public void testCreateTemporaryTableIfNotExistsWhenViewWithSameNameAlreadyExists() throws Exception{
     final String newTblName = "createTemporaryTableIfNotExistsWhenAViewWithSameNameAlreadyExists";
     test("CREATE VIEW %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName);

     testBuilder()
       .sqlQuery("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName)
       .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();

     test("DROP VIEW IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
   }

   @Test // DRILL-5952
   public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameDoesNotExist() throws Exception{
     final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameDoesNotExist";
     test("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName);
     test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
   }

   @Test
   public void testManualDropWithoutSchema() throws Exception {
     String temporaryTableName = "temporary_table_to_drop_without_schema";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     testBuilder()
         .sqlQuery("drop table %s", temporaryTableName)
         .unOrdered()
         .baselineColumns("ok", "summary")
         .baselineValues(true, String.format("Temporary table [%s] dropped", temporaryTableName))
         .go();
   }

   @Test
   public void testManualDropWithSchema() throws Exception {
     String temporaryTableName = "temporary_table_to_drop_with_schema";
     test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);

     testBuilder()
         .sqlQuery("drop table %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
         .unOrdered()
         .baselineColumns("ok", "summary")
         .baselineValues(true, String.format("Temporary table [%s] dropped", temporaryTableName))
         .go();
   }

   @Test
   public void testDropTemporaryTableAsViewWithoutException() throws Exception {
     String temporaryTableName = "temporary_table_to_drop_like_view_without_exception";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     testBuilder()
         .sqlQuery("drop view if exists %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
         .unOrdered()
         .baselineColumns("ok", "summary")
         .baselineValues(false, String.format("View [%s] not found in schema [%s].",
             temporaryTableName, DFS_TMP_SCHEMA))
         .go();
   }

   @Test
   public void testDropTemporaryTableAsViewWithException() throws Exception {
     String temporaryTableName = "temporary_table_to_drop_like_view_with_exception";
     test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: Unknown view [%s] in schema [%s]", temporaryTableName, DFS_TMP_SCHEMA));

     test("drop view %s.%s", DFS_TMP_SCHEMA, temporaryTableName);
   }

   @Test
   public void testJoinTemporaryWithPersistentTable() throws Exception {
     String temporaryTableName = "temp_tab";
     String persistentTableName = "pers_tab";
     String query = String.format("select * from `%s` a join `%s` b on a.c1 = b.c2",
         persistentTableName, temporaryTableName);

     test("use %s", temp2_schema);
     test("create TEMPORARY table %s as select '12312' as c2", temporaryTableName);
     test("create table %s as select '12312' as c1", persistentTableName);

     testBuilder()
         .sqlQuery(query)
         .unOrdered()
         .baselineColumns("c1", "c2")
         .baselineValues("12312", "12312")
         .go();
   }

   @Test
   public void testTemporaryTableWithAndWithoutLeadingSlashAreTheSame() throws Exception {
     String tablename = "table_with_and_without_slash_create";

     try {
       test("CREATE TEMPORARY TABLE %s AS SELECT * FROM cp.`region.json`", tablename);

       expectUserRemoteExceptionWithMessage(
           String.format("VALIDATION ERROR: A table or view with given name [%s] already exists in schema [%s]",
           tablename, DFS_TMP_SCHEMA));

       test(String.format("CREATE TEMPORARY TABLE `%s` AS SELECT * FROM cp.`employee.json`", "/" + tablename));
     } finally {
       test("DROP TABLE IF EXISTS %s", tablename);
     }
   }

   @Test
   public void testSelectFromTemporaryTableWithAndWithoutLeadingSlash() throws Exception {
     String tableName = "select_from_table_with_and_without_slash";

     try {
       test("CREATE TEMPORARY TABLE %s AS SELECT * FROM cp.`region.json`", tableName);

       String query = "SELECT region_id FROM `%s` LIMIT 1";

       testBuilder()
           .sqlQuery(query, tableName)
           .unOrdered()
           .baselineColumns("region_id")
           .baselineValues(0L)
           .go();

       testBuilder()
           .sqlQuery(query, "/" + tableName)
           .unOrdered()
           .baselineColumns("region_id")
           .baselineValues(0L)
           .go();
     } finally {
       test("DROP TABLE IF EXISTS %s", tableName);
     }
   }

   @Test
   public void testDropTemporaryTableNameStartsWithSlash() throws Exception {
     String tableName = "table_starts_with_slash_drop";

     try {
       test("CREATE TEMPORARY TABLE `%s` AS SELECT 1 FROM cp.`employee.json`", tableName);
       testBuilder()
           .sqlQuery("DROP TABLE `%s`", "/" + tableName)
           .unOrdered()
           .baselineColumns("ok", "summary")
           .baselineValues(true, String.format("Temporary table [%s] dropped", tableName))
           .go();
     } finally {
       test("DROP TABLE IF EXISTS %s", tableName);
     }
   }

   @Test // DRILL-7050
   public void testTemporaryTableInSubQuery() throws Exception {
     test("create temporary table source as (select 1 as id union all select 2 as id)");

     String query =
         "select t1.id as id,\n" +
             "(select count(t2.id)\n" +
             "from source t2 where t2.id = t1.id) as c\n" +
         "from source t1";

     testBuilder()
         .sqlQuery(query)
         .ordered()
         .baselineColumns("id", "c")
         .baselineValues(1, 1L)
         .baselineValues(2, 1L)
         .go();
   }

   private void expectUserRemoteExceptionWithMessage(String message) {
     thrown.expect(UserRemoteException.class);
     thrown.expectMessage(containsString(message));
   }

   private void expectUserRemoteExceptionWithTableExistsMessage(String tableName, String schemaName) {
     expectUserRemoteExceptionWithMessage(String.format(
       "VALIDATION ERROR: A table or view with given name [%s]" +
         " already exists in schema [%s]", tableName, schemaName));
   }

 }
	/*
	* 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.drill.exec.sql;

	import static org.apache.drill.exec.util.StoragePluginTestUtils.DFS_PLUGIN_NAME;
	import static org.apache.drill.exec.util.StoragePluginTestUtils.DFS_TMP_SCHEMA;
	import static org.hamcrest.CoreMatchers.containsString;

	import java.io.File;
	import java.nio.file.Paths;
	import java.util.HashMap;
	import java.util.List;
	import java.util.Map;
	import java.util.Optional;

	import org.apache.drill.categories.SqlTest;
	import org.apache.drill.common.exceptions.UserRemoteException;
	import org.apache.drill.exec.store.StoragePluginRegistry;
	import org.apache.drill.exec.store.dfs.FileSystemConfig;
	import org.apache.drill.exec.store.dfs.WorkspaceConfig;
	import org.apache.drill.common.logical.security.PlainCredentialsProvider;
	import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
	import org.apache.drill.test.BaseTestQuery;
	import org.junit.BeforeClass;
	import org.junit.Rule;
	import org.junit.Test;
	import org.junit.experimental.categories.Category;
	import org.junit.rules.ExpectedException;

	@Category(SqlTest.class)
	public class TestCTTAS extends BaseTestQuery {

	private static final String temp2_wk = "tmp2";
	private static final String temp2_schema = String.format("%s.%s", DFS_PLUGIN_NAME, temp2_wk);

	@Rule
	public ExpectedException thrown = ExpectedException.none();

	@BeforeClass
	public static void init() throws Exception {
	File tmp2 = dirTestWatcher.makeSubDir(Paths.get("tmp2"));
	StoragePluginRegistry pluginRegistry = getDrillbitContext().getStorage();
	FileSystemConfig pluginConfig = (FileSystemConfig) pluginRegistry.getPlugin(DFS_PLUGIN_NAME).getConfig();

	Map<String, WorkspaceConfig> newWorkspaces = new HashMap<>();
	Optional.ofNullable(pluginConfig.getWorkspaces())
	.ifPresent(newWorkspaces::putAll);
	newWorkspaces.put(temp2_wk, new WorkspaceConfig(tmp2.getAbsolutePath(), true, null, false));

	FileSystemConfig newPluginConfig = new FileSystemConfig(
	pluginConfig.getConnection(),
	pluginConfig.getConfig(),
	newWorkspaces,
	pluginConfig.getFormats(),
	PlainCredentialsProvider.EMPTY_CREDENTIALS_PROVIDER);
	newPluginConfig.setEnabled(pluginConfig.isEnabled());
	pluginRegistry.put(DFS_PLUGIN_NAME, newPluginConfig);
	}

	@Test
	public void testSyntax() throws Exception {
	test("create TEMPORARY table temporary_keyword as select 1 from (values(1))");
	test("create TEMPORARY table %s.temporary_keyword_with_wk as select 1 from (values(1))", DFS_TMP_SCHEMA);
	}

	@Test
	public void testCreateTableWithDifferentStorageFormats() throws Exception {
	List<String> storageFormats = Lists.newArrayList("parquet", "json", "csvh");

	try {
	for (String storageFormat : storageFormats) {
	String temporaryTableName = "temp_" + storageFormat;
	test("alter session set `store.format`='%s'", storageFormat);
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	testBuilder()
	.sqlQuery("select * from %s", temporaryTableName)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("A")
	.go();

	testBuilder()
	.sqlQuery("select * from %s", temporaryTableName)
	.unOrdered()
	.sqlBaselineQuery("select * from %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
	.go();
	}
	} finally {
	resetSessionOption("store.format");
	}
	}

	@Test
	public void testTemporaryTablesCaseInsensitivity() throws Exception {
	String temporaryTableName = "tEmP_InSeNSiTiVe";
	List<String> temporaryTableNames = Lists.newArrayList(
	temporaryTableName,
	temporaryTableName.toLowerCase(),
	temporaryTableName.toUpperCase());

	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);
	for (String tableName : temporaryTableNames) {
	testBuilder()
	.sqlQuery("select * from %s", tableName)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("A")
	.go();
	}
	}

	@Test
	public void testResolveTemporaryTableWithPartialSchema() throws Exception {
	String temporaryTableName = "temporary_table_with_partial_schema";
	test("use %s", DFS_PLUGIN_NAME);
	test("create temporary table tmp.%s as select 'A' as c1 from (values(1))", temporaryTableName);

	testBuilder()
	.sqlQuery("select * from tmp.%s", temporaryTableName)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("A")
	.go();
	}

	@Test
	public void testPartitionByWithTemporaryTables() throws Exception {
	String temporaryTableName = "temporary_table_with_partitions";
	test("create TEMPORARY table %s partition by (c1) as select * from (" +
	"select 'A' as c1 from (values(1)) union all select 'B' as c1 from (values(1))) t", temporaryTableName);
	}

	@Test
	public void testCreationOutsideOfDefaultTemporaryWorkspace() throws Exception {
	String temporaryTableName = "temporary_table_outside_of_default_workspace";

	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: Temporary tables are not allowed to be created / dropped " +
	"outside of default temporary workspace [%s].", DFS_TMP_SCHEMA));

	test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", temp2_schema, temporaryTableName);
	}

	@Test
	public void testCreateWhenTemporaryTableExistsWithoutSchema() throws Exception {
	String temporaryTableName = "temporary_table_exists_without_schema";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);
	}

	@Test
	public void testCreateWhenTemporaryTableExistsCaseInsensitive() throws Exception {
	String temporaryTableName = "temporary_table_exists_case_insensitive";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName.toUpperCase(), DFS_TMP_SCHEMA);

	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName.toUpperCase());
	}

	@Test
	public void testCreateWhenTemporaryTableExistsWithSchema() throws Exception {
	String temporaryTableName = "temporary_table_exists_with_schema";
	test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);

	expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

	test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
	}

	@Test
	public void testCreateWhenPersistentTableExists() throws Exception {
	String persistentTableName = "persistent_table_exists";
	test("create table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, persistentTableName);

	expectUserRemoteExceptionWithTableExistsMessage(persistentTableName, DFS_TMP_SCHEMA);

	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", persistentTableName);
	}

	@Test
	public void testCreateWhenViewExists() throws Exception {
	String viewName = "view_exists";
	test("create view %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, viewName);

	expectUserRemoteExceptionWithTableExistsMessage(viewName, DFS_TMP_SCHEMA);

	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", viewName);
	}

	@Test
	public void testCreatePersistentTableWhenTemporaryTableExists() throws Exception {
	String temporaryTableName = "temporary_table_exists_before_persistent";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithTableExistsMessage(temporaryTableName, DFS_TMP_SCHEMA);

	test("create table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
	}

	@Test
	public void testCreateViewWhenTemporaryTableExists() throws Exception {
	String temporaryTableName = "temporary_table_exists_before_view";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: A non-view table with given name [%s] already exists in schema [%s]",
	temporaryTableName, DFS_TMP_SCHEMA));

	test("create view %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);
	}

	@Test
	public void testSelectWithJoinOnTemporaryTables() throws Exception {
	String temporaryLeftTableName = "temporary_left_table_for_Select_with_join";
	String temporaryRightTableName = "temporary_right_table_for_Select_with_join";
	test("create TEMPORARY table %s as select 'A' as c1, 'B' as c2 from (values(1))", temporaryLeftTableName);
	test("create TEMPORARY table %s as select 'A' as c1, 'C' as c2 from (values(1))", temporaryRightTableName);

	testBuilder()
	.sqlQuery("select t1.c2 col1, t2.c2 col2 from %s t1 join %s t2 on t1.c1 = t2.c1", temporaryLeftTableName, temporaryRightTableName)
	.unOrdered()
	.baselineColumns("col1", "col2")
	.baselineValues("B", "C")
	.go();
	}

	@Test
	public void testTemporaryAndPersistentTablesPriority() throws Exception {
	String name = "temporary_and_persistent_table";
	test("use %s", temp2_schema);
	test("create TEMPORARY table %s as select 'temporary_table' as c1 from (values(1))", name);
	test("create table %s as select 'persistent_table' as c1 from (values(1))", name);

	testBuilder()
	.sqlQuery("select * from %s", name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("temporary_table")
	.go();

	testBuilder()
	.sqlQuery("select * from %s.%s", temp2_schema, name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("persistent_table")
	.go();

	test("drop table %s", name);

	testBuilder()
	.sqlQuery("select * from %s", name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("persistent_table")
	.go();
	}

	@Test
	public void testTemporaryTableAndViewPriority() throws Exception {
	String name = "temporary_table_and_view";
	test("use %s", temp2_schema);
	test("create TEMPORARY table %s as select 'temporary_table' as c1 from (values(1))", name);
	test("create view %s as select 'view' as c1 from (values(1))", name);

	testBuilder()
	.sqlQuery("select * from %s", name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("temporary_table")
	.go();

	testBuilder()
	.sqlQuery("select * from %s.%s", temp2_schema, name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("view")
	.go();

	test("drop table %s", name);

	testBuilder()
	.sqlQuery("select * from %s", name)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("view")
	.go();
	}

	@Test
	public void testTemporaryTablesInViewDefinitions() throws Exception {
	String temporaryTableName = "temporary_table_for_view_definition";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: Temporary tables usage is disallowed. Used temporary table name: [%s]", temporaryTableName));

	test("create view %s.view_with_temp_table as select * from %s", DFS_TMP_SCHEMA, temporaryTableName);
	}

	@Test
	public void testTemporaryTablesInViewExpansionLogic() throws Exception {
	String tableName = "table_for_expansion_logic_test";
	String viewName = "view_for_expansion_logic_test";
	test("use %s", DFS_TMP_SCHEMA);
	test("create table %s as select 'TABLE' as c1 from (values(1))", tableName);
	test("create view %s as select * from %s", viewName, tableName);

	testBuilder()
	.sqlQuery("select * from %s", viewName)
	.unOrdered()
	.baselineColumns("c1")
	.baselineValues("TABLE")
	.go();

	test("drop table %s", tableName);
	test("create temporary table %s as select 'TEMP' as c1 from (values(1))", tableName);

	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: Temporary tables usage is disallowed. Used temporary table name: [%s]", tableName));

	test("select * from %s", viewName);
	}

	@Test // DRILL-5952
	public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameAlreadyExists() throws Exception{
	final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameAlreadyExists";
	test("CREATE TEMPORARY TABLE %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName);

	testBuilder()
	.sqlQuery("CREATE TEMPORARY TABLE IF NOT EXISTS %s AS SELECT * FROM cp.`employee.json`", newTblName)
	.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();

	test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
	}

	@Test // DRILL-5952
	public void testCreateTemporaryTableIfNotExistsWhenViewWithSameNameAlreadyExists() throws Exception{
	final String newTblName = "createTemporaryTableIfNotExistsWhenAViewWithSameNameAlreadyExists";
	test("CREATE VIEW %s.%s AS SELECT * from cp.`region.json`", DFS_TMP_SCHEMA, newTblName);

	testBuilder()
	.sqlQuery("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName)
	.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();

	test("DROP VIEW IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
	}

	@Test // DRILL-5952
	public void testCreateTemporaryTableIfNotExistsWhenTableWithSameNameDoesNotExist() throws Exception{
	final String newTblName = "createTemporaryTableIfNotExistsWhenATableWithSameNameDoesNotExist";
	test("CREATE TEMPORARY TABLE IF NOT EXISTS %s.%s AS SELECT * FROM cp.`employee.json`", DFS_TMP_SCHEMA, newTblName);
	test("DROP TABLE IF EXISTS %s.%s", DFS_TMP_SCHEMA, newTblName);
	}

	@Test
	public void testManualDropWithoutSchema() throws Exception {
	String temporaryTableName = "temporary_table_to_drop_without_schema";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	testBuilder()
	.sqlQuery("drop table %s", temporaryTableName)
	.unOrdered()
	.baselineColumns("ok", "summary")
	.baselineValues(true, String.format("Temporary table [%s] dropped", temporaryTableName))
	.go();
	}

	@Test
	public void testManualDropWithSchema() throws Exception {
	String temporaryTableName = "temporary_table_to_drop_with_schema";
	test("create TEMPORARY table %s.%s as select 'A' as c1 from (values(1))", DFS_TMP_SCHEMA, temporaryTableName);

	testBuilder()
	.sqlQuery("drop table %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
	.unOrdered()
	.baselineColumns("ok", "summary")
	.baselineValues(true, String.format("Temporary table [%s] dropped", temporaryTableName))
	.go();
	}

	@Test
	public void testDropTemporaryTableAsViewWithoutException() throws Exception {
	String temporaryTableName = "temporary_table_to_drop_like_view_without_exception";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	testBuilder()
	.sqlQuery("drop view if exists %s.%s", DFS_TMP_SCHEMA, temporaryTableName)
	.unOrdered()
	.baselineColumns("ok", "summary")
	.baselineValues(false, String.format("View [%s] not found in schema [%s].",
	temporaryTableName, DFS_TMP_SCHEMA))
	.go();
	}

	@Test
	public void testDropTemporaryTableAsViewWithException() throws Exception {
	String temporaryTableName = "temporary_table_to_drop_like_view_with_exception";
	test("create TEMPORARY table %s as select 'A' as c1 from (values(1))", temporaryTableName);

	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: Unknown view [%s] in schema [%s]", temporaryTableName, DFS_TMP_SCHEMA));

	test("drop view %s.%s", DFS_TMP_SCHEMA, temporaryTableName);
	}

	@Test
	public void testJoinTemporaryWithPersistentTable() throws Exception {
	String temporaryTableName = "temp_tab";
	String persistentTableName = "pers_tab";
	String query = String.format("select * from `%s` a join `%s` b on a.c1 = b.c2",
	persistentTableName, temporaryTableName);

	test("use %s", temp2_schema);
	test("create TEMPORARY table %s as select '12312' as c2", temporaryTableName);
	test("create table %s as select '12312' as c1", persistentTableName);

	testBuilder()
	.sqlQuery(query)
	.unOrdered()
	.baselineColumns("c1", "c2")
	.baselineValues("12312", "12312")
	.go();
	}

	@Test
	public void testTemporaryTableWithAndWithoutLeadingSlashAreTheSame() throws Exception {
	String tablename = "table_with_and_without_slash_create";

	try {
	test("CREATE TEMPORARY TABLE %s AS SELECT * FROM cp.`region.json`", tablename);

	expectUserRemoteExceptionWithMessage(
	String.format("VALIDATION ERROR: A table or view with given name [%s] already exists in schema [%s]",
	tablename, DFS_TMP_SCHEMA));

	test(String.format("CREATE TEMPORARY TABLE `%s` AS SELECT * FROM cp.`employee.json`", "/" + tablename));
	} finally {
	test("DROP TABLE IF EXISTS %s", tablename);
	}
	}

	@Test
	public void testSelectFromTemporaryTableWithAndWithoutLeadingSlash() throws Exception {
	String tableName = "select_from_table_with_and_without_slash";

	try {
	test("CREATE TEMPORARY TABLE %s AS SELECT * FROM cp.`region.json`", tableName);

	String query = "SELECT region_id FROM `%s` LIMIT 1";

	testBuilder()
	.sqlQuery(query, tableName)
	.unOrdered()
	.baselineColumns("region_id")
	.baselineValues(0L)
	.go();

	testBuilder()
	.sqlQuery(query, "/" + tableName)
	.unOrdered()
	.baselineColumns("region_id")
	.baselineValues(0L)
	.go();
	} finally {
	test("DROP TABLE IF EXISTS %s", tableName);
	}
	}

	@Test
	public void testDropTemporaryTableNameStartsWithSlash() throws Exception {
	String tableName = "table_starts_with_slash_drop";

	try {
	test("CREATE TEMPORARY TABLE `%s` AS SELECT 1 FROM cp.`employee.json`", tableName);
	testBuilder()
	.sqlQuery("DROP TABLE `%s`", "/" + tableName)
	.unOrdered()
	.baselineColumns("ok", "summary")
	.baselineValues(true, String.format("Temporary table [%s] dropped", tableName))
	.go();
	} finally {
	test("DROP TABLE IF EXISTS %s", tableName);
	}
	}

	@Test // DRILL-7050
	public void testTemporaryTableInSubQuery() throws Exception {
	test("create temporary table source as (select 1 as id union all select 2 as id)");

	String query =
	"select t1.id as id,\n" +
	"(select count(t2.id)\n" +
	"from source t2 where t2.id = t1.id) as c\n" +
	"from source t1";

	testBuilder()
	.sqlQuery(query)
	.ordered()
	.baselineColumns("id", "c")
	.baselineValues(1, 1L)
	.baselineValues(2, 1L)
	.go();
	}

	private void expectUserRemoteExceptionWithMessage(String message) {
	thrown.expect(UserRemoteException.class);
	thrown.expectMessage(containsString(message));
	}

	private void expectUserRemoteExceptionWithTableExistsMessage(String tableName, String schemaName) {
	expectUserRemoteExceptionWithMessage(String.format(
	"VALIDATION ERROR: A table or view with given name [%s]" +
	" already exists in schema [%s]", tableName, schemaName));
	}

	}