http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestCreateTable.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestCreateTable.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestCreateTable.java new file mode 100644 index 0000000..f34cbce --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestCreateTable.java @@ -0,0 +1,643 @@ +/** + * 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.tajo.engine.query; + +import org.apache.hadoop.fs.FileSystem; +import org.apache.hadoop.fs.Path; +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.QueryTestCaseBase; +import org.apache.tajo.catalog.*; +import org.apache.tajo.catalog.partition.PartitionMethodDesc; +import org.apache.tajo.conf.TajoConf; +import org.apache.tajo.storage.StorageUtil; +import org.apache.tajo.util.KeyValueSet; +import org.junit.Test; +import org.junit.experimental.categories.Category; + +import java.sql.ResultSet; +import java.util.List; + +import static org.junit.Assert.*; + +@Category(IntegrationTest.class) +public class TestCreateTable extends QueryTestCaseBase { + + @Test + public final void testVariousTypes() throws Exception { + List<String> createdNames; + if (testingCluster.isHiveCatalogStoreRunning()) { + createdNames = executeDDL("create_table_various_types_for_hive_catalog.sql", null); + } else { + createdNames = executeDDL("create_table_various_types.sql", null); + } + assertTableExists(createdNames.get(0)); + } + + @Test + public final void testCreateTable1() throws Exception { + List<String> createdNames = executeDDL("table1_ddl.sql", "table1", "table1"); + assertTableExists(createdNames.get(0)); + executeString("DROP TABLE table1"); + } + + @Test + public final void testCreateTable2() throws Exception { + executeString("CREATE DATABASE D1;").close(); + executeString("CREATE DATABASE D2;").close(); + + executeString("CREATE TABLE D1.table1 (age int);").close(); + executeString("CREATE TABLE D1.table2 (age int);").close(); + executeString("CREATE TABLE d2.table3 (age int);").close(); + executeString("CREATE TABLE d2.table4 (age int);").close(); + + assertTableExists("d1.table1"); + assertTableExists("d1.table2"); + assertTableNotExists("d2.table1"); + assertTableNotExists("d2.table2"); + + assertTableExists("d2.table3"); + assertTableExists("d2.table4"); + assertTableNotExists("d1.table3"); + assertTableNotExists("d1.table4"); + + executeString("DROP TABLE D1.table1"); + executeString("DROP TABLE D1.table2"); + executeString("DROP TABLE D2.table3"); + executeString("DROP TABLE D2.table4"); + + assertDatabaseExists("d1"); + assertDatabaseExists("d2"); + executeString("DROP DATABASE D1").close(); + executeString("DROP DATABASE D2").close(); + assertDatabaseNotExists("d1"); + assertDatabaseNotExists("d2"); + } + + private final void assertPathOfCreatedTable(final String databaseName, + final String originalTableName, + final String newTableName, + String createTableStmt) throws Exception { + // create one table + executeString("CREATE DATABASE " + CatalogUtil.denormalizeIdentifier(databaseName)).close(); + getClient().existDatabase(CatalogUtil.denormalizeIdentifier(databaseName)); + final String oldFQTableName = CatalogUtil.buildFQName(databaseName, originalTableName); + + ResultSet res = executeString(createTableStmt); + res.close(); + assertTableExists(oldFQTableName); + TableDesc oldTableDesc = client.getTableDesc(oldFQTableName); + + + // checking the existence of the table directory and validating the path + Path warehouseDir = TajoConf.getWarehouseDir(testingCluster.getConfiguration()); + FileSystem fs = warehouseDir.getFileSystem(testingCluster.getConfiguration()); + assertTrue(fs.exists(new Path(oldTableDesc.getUri()))); + assertEquals(StorageUtil.concatPath(warehouseDir, databaseName, originalTableName), + new Path(oldTableDesc.getUri())); + + // Rename + client.executeQuery("ALTER TABLE " + CatalogUtil.denormalizeIdentifier(oldFQTableName) + + " RENAME to " + CatalogUtil.denormalizeIdentifier(newTableName)); + + // checking the existence of the new table directory and validating the path + final String newFQTableName = CatalogUtil.buildFQName(databaseName, newTableName); + TableDesc newTableDesc = client.getTableDesc(newFQTableName); + assertTrue(fs.exists(new Path(newTableDesc.getUri()))); + assertEquals(StorageUtil.concatPath(warehouseDir, databaseName, newTableName), new Path(newTableDesc.getUri())); + } + + @Test + public final void testCreatedTableViaCTASAndVerifyPath() throws Exception { + assertPathOfCreatedTable("d4", "old_table", "new_mgmt_table", + "CREATE TABLE d4.old_table AS SELECT * FROM default.lineitem;"); + } + + @Test + public final void testCreatedTableJustCreatedAndVerifyPath() throws Exception { + assertPathOfCreatedTable("d5", "old_table", "new_mgmt_table", "CREATE TABLE d5.old_table (age integer);"); + } + + @Test + public final void testCreatedTableWithQuotedIdentifierAndVerifyPath() throws Exception { + if (!testingCluster.isHiveCatalogStoreRunning()) { + assertPathOfCreatedTable("D6", "OldTable", "NewMgmtTable", "CREATE TABLE \"D6\".\"OldTable\" (age integer);"); + } + } + + @Test + public final void testCreateTableIfNotExists() throws Exception { + executeString("CREATE DATABASE D3;").close(); + + assertTableNotExists("d3.table1"); + executeString("CREATE TABLE D3.table1 (age int);").close(); + assertTableExists("d3.table1"); + + executeString("CREATE TABLE IF NOT EXISTS D3.table1 (age int);").close(); + assertTableExists("d3.table1"); + + executeString("DROP TABLE D3.table1"); + } + + @Test + public final void testDropTableIfExists() throws Exception { + executeString("CREATE DATABASE D7;").close(); + + assertTableNotExists("d7.table1"); + executeString("CREATE TABLE d7.table1 (age int);").close(); + assertTableExists("d7.table1"); + + executeString("DROP TABLE d7.table1;").close(); + assertTableNotExists("d7.table1"); + + executeString("DROP TABLE IF EXISTS d7.table1"); + assertTableNotExists("d7.table1"); + + executeString("DROP DATABASE D7;").close(); + } + + @Test + public final void testDelimitedIdentifierWithNonAsciiCharacters() throws Exception { + + if (!testingCluster.isHiveCatalogStoreRunning()) { + ResultSet res = null; + try { + List<String> tableNames = executeDDL("quoted_identifier_non_ascii_ddl.sql", "table1", "\"í ì´ë¸1\""); + assertTableExists(tableNames.get(0)); + + // SELECT "ìì´ë", "í ì¤í¸", "ì«ì" FROM "í ì´ë¸1"; + res = executeFile("quoted_identifier_non_ascii_1.sql"); + assertResultSet(res, "quoted_identifier_non_ascii_1.result"); + } finally { + cleanupQuery(res); + } + + // SELECT "ìì´ë" as "ì§ì§ìì´ë", "í ì¤í¸" as text, "ì«ì" FROM "í ì´ë¸1" as "í ì´ë¸ ë³ëª " + try { + res = executeFile("quoted_identifier_non_ascii_2.sql"); + assertResultSet(res, "quoted_identifier_non_ascii_2.result"); + } finally { + cleanupQuery(res); + } + + // SELECT "ìì´ë" "ì§ì§ìì´ë", char_length("í ì¤í¸") as "길ì´", "ì«ì" * 2 FROM "í ì´ë¸1" "í ì´ë¸ ë³ëª " + try { + res = executeFile("quoted_identifier_non_ascii_3.sql"); + assertResultSet(res, "quoted_identifier_non_ascii_3.result"); + } finally { + cleanupQuery(res); + } + } + } + + @Test + public final void testDelimitedIdentifierWithMixedCharacters() throws Exception { + if (!testingCluster.isHiveCatalogStoreRunning()) { + ResultSet res = null; + + try { + List<String> tableNames = executeDDL("quoted_identifier_mixed_chars_ddl_1.sql", "table1", "\"TABLE1\""); + assertTableExists(tableNames.get(0)); + + tableNames = executeDDL("quoted_identifier_mixed_chars_ddl_1.sql", "table2", "\"tablE1\""); + assertTableExists(tableNames.get(0)); + + // SELECT "aGe", "tExt", "Number" FROM "TABLE1"; + res = executeFile("quoted_identifier_mixed_chars_1.sql"); + assertResultSet(res, "quoted_identifier_mixed_chars_1.result"); + } finally { + cleanupQuery(res); + } + + try { + res = executeFile("quoted_identifier_mixed_chars_2.sql"); + assertResultSet(res, "quoted_identifier_mixed_chars_2.result"); + } finally { + cleanupQuery(res); + } + + try { + res = executeFile("quoted_identifier_mixed_chars_3.sql"); + assertResultSet(res, "quoted_identifier_mixed_chars_3.result"); + } finally { + cleanupQuery(res); + } + } + } + + @Test + public final void testNonreservedKeywordTableNames() throws Exception { + List<String> createdNames = null; + createdNames = executeDDL("table1_ddl.sql", "table1", "filter"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "first"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "format"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "grouping"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "hash"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "index"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "insert"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "last"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "location"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "max"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "min"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "national"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "nullif"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "overwrite"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "precision"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "range"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "regexp"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "rlike"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "set"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "unknown"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "var_pop"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "var_samp"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "varying"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "zone"); + assertTableExists(createdNames.get(0)); + + createdNames = executeDDL("table1_ddl.sql", "table1", "bigint"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "bit"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "blob"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "bool"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "boolean"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "bytea"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "char"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "date"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "decimal"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "double"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "float"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "float4"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "float8"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "inet4"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "int"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "int1"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "int2"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "int4"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "int8"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "integer"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "nchar"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "numeric"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "nvarchar"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "real"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "smallint"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "text"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "time"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "timestamp"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "timestamptz"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "timetz"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "tinyint"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "varbinary"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "varbit"); + assertTableExists(createdNames.get(0)); + createdNames = executeDDL("table1_ddl.sql", "table1", "varchar"); + assertTableExists(createdNames.get(0)); + } + + private boolean isClonedSchema(Schema origSchema, Schema newSchema) { + // Check schema of tables + boolean schemaEqual = + (origSchema.size() == newSchema.size()); + if(schemaEqual == false) { + fail("Number of columns in schema not equal"); + return false; + } + + for(int col = 0; col < origSchema.size(); col++) { + Column colA = origSchema.getColumn(col); + Column colB = newSchema.getColumn(col); + if(colA.getSimpleName().equals(colB.getSimpleName()) == false) { + fail("Column names at index " + col + " do not match"); + return false; + } + if(colA.getDataType().equals(colB.getDataType()) == false) { + fail("Column datatypes at index " + col + " do not match"); + return false; + } + } + return true; + } + + private boolean isClonedTable(String orignalTable, String newTable) throws Exception { + assertTableExists(newTable); + TableDesc origTableDesc = client.getTableDesc(orignalTable); + TableDesc newTableDesc = client.getTableDesc(newTable); + + if(isClonedSchema(origTableDesc.getSchema(), newTableDesc.getSchema()) == false) { + fail("Schema of input tables do not match"); + return false; + } + + // Check partition information + PartitionMethodDesc origPartMethod = origTableDesc.getPartitionMethod(); + PartitionMethodDesc newPartMethod = newTableDesc.getPartitionMethod(); + if(origPartMethod != null) { + if(newPartMethod == null) { + fail("New table does not have partition info"); + return false; + } + if(isClonedSchema(origPartMethod.getExpressionSchema(), + newPartMethod.getExpressionSchema()) == false) { + fail("Partition columns of input tables do not match"); + return false; + } + + if(origPartMethod.getPartitionType().equals(newPartMethod.getPartitionType()) == false) { + fail("Partition type of input tables do not match"); + return false; + } + } + + // Check external flag + if(origTableDesc.isExternal() != newTableDesc.isExternal()) { + fail("External table flag on input tables not equal"); + return false; + } + + if(origTableDesc.getMeta() != null) { + TableMeta origMeta = origTableDesc.getMeta(); + TableMeta newMeta = newTableDesc.getMeta(); + if(origMeta.getStoreType().equals(newMeta.getStoreType()) == false) { + fail("Store type of input tables not equal"); + return false; + } + + KeyValueSet origOptions = origMeta.getOptions(); + KeyValueSet newOptions = newMeta.getOptions(); + if(origOptions.equals(newOptions) == false) { + fail("Meta options of input tables not equal"); + return false; + } + } + return true; + } + + @Test + public final void testCreateExternalTable1FromOnlyPath() throws Exception { + // This test verifies CREATE EXTERNAL TABLE from just a path instead of a full qualified URI. + ResultSet res = null; + try { + res = executeString( + "INSERT INTO LOCATION '/testCreateExternalTable1FromOnlyPath' SELECT * FROM default.lineitem"); + res = executeString( + "CREATE EXTERNAL TABLE table1 (col1 INTEGER) USING TEXT LOCATION '/testCreateExternalTable1FromOnlyPath';"); + } catch (Throwable t) { + if (res != null) { + res.close(); + } + } + } + + @Test + public final void testCreateTableLike1() throws Exception { + // //HiveCatalogStore does not support varchar type in hive-0.12.0 + if (testingCluster.isHiveCatalogStoreRunning()) { + // Basic create table with default database + executeString("CREATE TABLE table1 (c1 int, c2 text);").close(); + executeString("CREATE TABLE table2 LIKE table1"); + String testMsg = "testCreateTableLike1: Basic create table with default db"; + assertTrue(testMsg,isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + // Basic create table with database + executeString("CREATE DATABASE d1").close(); + executeString("CREATE TABLE d1.table1 (c1 int, c2 text);").close(); + executeString("CREATE TABLE d1.table2 LIKE d1.table1"); + testMsg = "testCreateTableLike1: Basic create table with db test failed"; + assertTrue(testMsg, isClonedTable("d1.table1","d1.table2")); + executeString("DROP TABLE d1.table1"); + executeString("DROP TABLE d1.table2"); + + // Table with non-default store type + executeString("CREATE TABLE table1 (c1 int, c2 text) USING rcfile;").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with non-default store type test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + // Table with non-default meta options + executeString("CREATE TABLE table1 (c1 int, c2 text) USING text WITH ('text.delimiter'='|','compression.codec'='org.apache.hadoop.io.compress.DeflateCodec');").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with non-default meta options test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + + // Table with partitions (default partition type) + executeString("CREATE TABLE table1 (c1 int, c2 text) PARTITION BY COLUMN (c3 int, c4 float, c5 text);").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with partitions test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + + // Table with external flag + // Use existing file as input for creating external table + String className = getClass().getSimpleName(); + Path currentDatasetPath = new Path(datasetBasePath, className); + Path filePath = StorageUtil.concatPath(currentDatasetPath, "table1"); + executeString("CREATE EXTERNAL TABLE table3 (c1 int, c2 text) USING rcfile LOCATION '" + filePath.toUri() + "'").close(); + executeString("CREATE TABLE table2 LIKE table3"); + testMsg = "testCreateTableLike1: Table with external table flag test failed"; + assertTrue(testMsg, isClonedTable("table3","table2")); + executeString("DROP TABLE table3"); + executeString("DROP TABLE table2"); + + + // Table created using CTAS + executeString("CREATE TABLE table3 (c1 int, c2 text) PARTITION BY COLUMN (c3 int);").close(); + executeString("CREATE TABLE table4 AS SELECT c1 * c1 as m_c1, c2, c2 as c2_a,c3 from table3;").close(); + executeString("CREATE TABLE table2 LIKE table4"); + testMsg = "testCreateTableLike1: Table using CTAS test failed"; + assertTrue(testMsg, isClonedTable("table4","table2")); + executeString("DROP TABLE table3"); + executeString("DROP TABLE table4"); + executeString("DROP TABLE table2"); + } else { + // Basic create table with default database + executeString("CREATE TABLE table1 (c1 int, c2 varchar);").close(); + executeString("CREATE TABLE table2 LIKE table1"); + String testMsg = "testCreateTableLike1: Basic create table with default db"; + assertTrue(testMsg,isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + // Basic create table with database + executeString("CREATE DATABASE d1").close(); + executeString("CREATE TABLE d1.table1 (c1 int, c2 varchar);").close(); + executeString("CREATE TABLE d1.table2 LIKE d1.table1"); + testMsg = "testCreateTableLike1: Basic create table with db test failed"; + assertTrue(testMsg, isClonedTable("d1.table1","d1.table2")); + executeString("DROP TABLE d1.table1"); + executeString("DROP TABLE d1.table2"); + + // Table with non-default store type + executeString("CREATE TABLE table1 (c1 int, c2 varchar) USING rcfile;").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with non-default store type test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + // Table with non-default meta options + executeString("CREATE TABLE table1 (c1 int, c2 varchar) USING text WITH ('text.delimiter'='|','compression.codec'='org.apache.hadoop.io.compress.DeflateCodec');").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with non-default meta options test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + + // Table with partitions (default partition type) + executeString("CREATE TABLE table1 (c1 int, c2 varchar) PARTITION BY COLUMN (c3 int, c4 float, c5 text);").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table with partitions test failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + + + // Table with external flag + // Use existing file as input for creating external table + String className = getClass().getSimpleName(); + Path currentDatasetPath = new Path(datasetBasePath, className); + Path filePath = StorageUtil.concatPath(currentDatasetPath, "table1"); + executeString("CREATE EXTERNAL TABLE table3 (c1 int, c2 varchar) USING rcfile LOCATION '" + filePath.toUri() + "'").close(); + executeString("CREATE TABLE table2 LIKE table3"); + testMsg = "testCreateTableLike1: Table with external table flag test failed"; + assertTrue(testMsg, isClonedTable("table3","table2")); + executeString("DROP TABLE table3"); + executeString("DROP TABLE table2"); + + + // Table created using CTAS + executeString("CREATE TABLE table3 (c1 int, c2 varchar) PARTITION BY COLUMN (c3 int);").close(); + executeString("CREATE TABLE table4 AS SELECT c1*c1, c2, c2 as c2_a,c3 from table3;").close(); + executeString("CREATE TABLE table2 LIKE table4"); + testMsg = "testCreateTableLike1: Table using CTAS test failed"; + assertTrue(testMsg, isClonedTable("table4","table2")); + executeString("DROP TABLE table3"); + executeString("DROP TABLE table4"); + executeString("DROP TABLE table2"); + + + /* Enable when view is supported + // View + executeString("CREATE TABLE table3 (c1 int, c2 varchar) PARTITION BY COLUMN (c3 int);").close(); + executeString("CREATE VIEW table4(c1,c2,c3) AS SELECT c1*c1, c2, c2,c3 from table3;").close(); + executeString("CREATE TABLE table2 LIKE table4"); + testMsg = "testCreateTableLike1: Table using VIEW test failed"; + assertTrue(testMsg, isClonedTable("table4","table2")); + executeString("DROP TABLE table3"); + executeString("DROP TABLE table4"); + executeString("DROP TABLE table2"); + */ + + /* Enable when partition type other than column is supported + // Table with partitions (range partition) + executeString("CREATE TABLE table1 (c1 int, c2 varchar) PARTITION BY RANGE (c1) ( PARTITION c1 VALUES LESS THAN (2), PARTITION c1 VALUES LESS THAN (5), PARTITION c1 VALUES LESS THAN (MAXVALUE) );").close(); + executeString("CREATE TABLE table2 LIKE table1"); + testMsg = "testCreateTableLike1: Table using non-default partition type failed"; + assertTrue(testMsg, isClonedTable("table1","table2")); + executeString("DROP TABLE table1"); + executeString("DROP TABLE table2"); + */ + } + } + + @Test + public final void testNestedRecord1() throws Exception { + executeString("CREATE DATABASE D9;").close(); + + assertTableNotExists("d9.nested_table"); + executeQuery().close(); + assertTableExists("d9.nested_table"); + + executeString("DROP TABLE D9.nested_table"); + executeString("DROP DATABASE D9").close(); + } + + @Test + public final void testNestedRecord2() throws Exception { + executeString("CREATE DATABASE D9;").close(); + + assertTableNotExists("d9.nested_table2"); + executeQuery(); + assertTableExists("d9.nested_table2"); + + executeString("DROP TABLE D9.nested_table2"); + executeString("DROP DATABASE D9").close(); + } +}
http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestDropTable.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestDropTable.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestDropTable.java new file mode 100644 index 0000000..0020156 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestDropTable.java @@ -0,0 +1,38 @@ +/** + * 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.tajo.engine.query; + +import org.apache.tajo.IntegrationTest; +import org.apache.tajo.QueryTestCaseBase; +import org.junit.Test; +import org.junit.experimental.categories.Category; + +import java.util.List; + +@Category(IntegrationTest.class) +public class TestDropTable extends QueryTestCaseBase { + + @Test + public final void testDropManagedTable() throws Exception { + List<String> createdNames = executeDDL("table1_ddl.sql", "table1.tbl", "abc"); + assertTableExists(createdNames.get(0)); + executeDDL("drop_table_ddl.sql", null); + assertTableNotExists("abc"); + } +} http://git-wip-us.apache.org/repos/asf/tajo/blob/a4106883/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestGroupByQuery.java ---------------------------------------------------------------------- diff --git a/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestGroupByQuery.java b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestGroupByQuery.java new file mode 100644 index 0000000..a5caf38 --- /dev/null +++ b/tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestGroupByQuery.java @@ -0,0 +1,870 @@ +/** + * 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.tajo.engine.query; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.apache.tajo.*; +import org.apache.tajo.catalog.Schema; +import org.apache.tajo.common.TajoDataTypes.Type; +import org.apache.tajo.conf.TajoConf.ConfVars; +import org.apache.tajo.querymaster.Query; +import org.apache.tajo.querymaster.QueryMasterTask; +import org.apache.tajo.querymaster.Stage; +import org.apache.tajo.querymaster.Task; +import org.apache.tajo.storage.StorageConstants; +import org.apache.tajo.util.KeyValueSet; +import org.apache.tajo.util.TUtil; +import org.apache.tajo.worker.TajoWorker; +import org.junit.AfterClass; +import org.junit.Test; +import org.junit.experimental.categories.Category; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; +import org.junit.runners.Parameterized.Parameters; + +import java.sql.ResultSet; +import java.util.*; + +import static org.junit.Assert.*; + +@Category(IntegrationTest.class) +@RunWith(Parameterized.class) +public class TestGroupByQuery extends QueryTestCaseBase { + private static final Log LOG = LogFactory.getLog(TestGroupByQuery.class); + + public TestGroupByQuery(String groupByOption) throws Exception { + super(TajoConstants.DEFAULT_DATABASE_NAME); + + Map<String, String> variables = new HashMap<String, String>(); + if (groupByOption.equals("MultiLevel")) { + variables.put(SessionVars.GROUPBY_MULTI_LEVEL_ENABLED.keyname(), "true"); + } else { + variables.put(SessionVars.GROUPBY_MULTI_LEVEL_ENABLED.keyname(), "false"); + } + client.updateSessionVariables(variables); + } + + @AfterClass + public static void tearDown() throws Exception { + client.unsetSessionVariables(TUtil.newList(SessionVars.GROUPBY_MULTI_LEVEL_ENABLED.keyname())); + } + + @Parameters + public static Collection<Object[]> generateParameters() { + return Arrays.asList(new Object[][]{ + {"MultiLevel"}, + {"No-MultiLevel"}, + }); + } + + @Test + public final void testGroupBy() throws Exception { + // select count(1) as unique_key from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupBy2() throws Exception { + // select count(1) as unique_key from lineitem group by l_linenumber; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupBy3() throws Exception { + // select l_orderkey as gkey from lineitem group by gkey order by gkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupBy4() throws Exception { + // select l_orderkey as gkey, count(1) as unique_key from lineitem group by lineitem.l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupBy5() throws Exception { + // select l_orderkey as gkey, '00' as num from lineitem group by lineitem.l_orderkey order by gkey + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByNested1() throws Exception { + // select l_orderkey + l_partkey as unique_key from lineitem group by l_orderkey + l_partkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByNested2() throws Exception { + // select sum(l_orderkey) + sum(l_partkey) as total from lineitem group by l_orderkey + l_partkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithSameExprs1() throws Exception { + // select sum(l_orderkey) + sum(l_orderkey) as total from lineitem group by l_orderkey + l_partkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithSameExprs2() throws Exception { + // select sum(l_orderkey) as total1, sum(l_orderkey) as total2 from lineitem group by l_orderkey + l_partkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithExpressionKeys1() throws Exception { + // select upper(lower(l_orderkey::text)) as key, count(1) as total from lineitem + // group by key order by upper(lower(l_orderkey::text)), total; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithExpressionKeys2() throws Exception { + // select upper(lower(l_orderkey::text)) as key, count(1) as total from lineitem + // group by upper(lower(l_orderkey::text)) order by upper(l_orderkey::text), total; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithSameConstantKeys1() throws Exception { + // select l_partkey as a, '##' as b, '##' as c, count(*) d from lineitem group by a, b, c order by a; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithConstantKeys1() throws Exception { + // select 123 as key, count(1) as total from lineitem group by key order by key, total; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithConstantKeys2() throws Exception { + // select l_partkey as a, timestamp '2014-07-07 04:28:31.561' as b, '##' as c, count(*) d from lineitem + // group by a, b, c order by l_partkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithConstantKeys3() throws Exception { + // select + // l_partkey as a, + // timestamp '2014-07-07 04:28:31.561' as b, + // '##' as c, + // count(*) d + // from + // lineitem + // group by + // b, c; <- b and c all are constant values. + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithConstantKeys4() throws Exception { + // select + // 'day', + // l_orderkey, + // count(*) as sum + // from + // lineitem + // group by + // 'day', + // l_orderkey + // order by + // 'day', + // l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithConstantKeys5() throws Exception { + // select + // 'day', + // l_orderkey, + // count(*) as sum + // from + // lineitem + // group by + // 'day', + // l_orderkey + // order by + // 'day', + // l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation1() throws Exception { + // select l_orderkey, max(l_orderkey) as maximum, count(distinct l_linenumber) as unique_key from lineitem + // group by l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + /** + * This is an unit test for a combination of aggregation and distinct aggregation functions. + */ + public final void testDistinctAggregation2() throws Exception { + // select l_orderkey, count(*) as cnt, count(distinct l_linenumber) as unique_key from lineitem group by l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation3() throws Exception { + // select count(*), count(distinct l_orderkey), sum(distinct l_orderkey) from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation4() throws Exception { + // select l_linenumber, count(*), count(distinct l_orderkey), sum(distinct l_orderkey) + // from lineitem group by l_linenumber; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation5() throws Exception { + // select sum(distinct l_orderkey), l_linenumber, count(distinct l_orderkey), count(*) as total + // from lineitem group by l_linenumber; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation6() throws Exception { + // select count(distinct l_orderkey) v0, sum(l_orderkey) v1, sum(l_linenumber) v2, count(*) as v4 from lineitem + // group by l_orderkey; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation7() throws Exception { + // select count(*), count(distinct c_nationkey), count(distinct c_mktsegment) from customer + // tpch scale 1000: 15000000 25 5 + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregation8() throws Exception { + /* + select + sum(distinct l_orderkey), + l_linenumber, l_returnflag, l_linestatus, l_shipdate, + count(distinct l_partkey), + sum(l_orderkey) + from + lineitem + group by + l_linenumber, l_returnflag, l_linestatus, l_shipdate; + */ + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregationWithHaving1() throws Exception { + // select l_linenumber, count(*), count(distinct l_orderkey), sum(distinct l_orderkey) from lineitem + // group by l_linenumber having sum(distinct l_orderkey) >= 6; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregationWithUnion1() throws Exception { + // select sum(distinct l_orderkey), l_linenumber, count(distinct l_orderkey), count(*) as total + // from (select * from lineitem union select * from lineitem) group by l_linenumber; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testDistinctAggregationCasebyCase1() throws Exception { + // one groupby, distinct, aggregation + ResultSet res = executeFile("testDistinctAggregation_case1.sql"); + assertResultSet(res, "testDistinctAggregation_case1.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase2() throws Exception { + // one groupby, two distinct, one aggregation + ResultSet res = executeFile("testDistinctAggregation_case2.sql"); + assertResultSet(res, "testDistinctAggregation_case2.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase3() throws Exception { + // one groupby, two distinct, two aggregation(no alias) + ResultSet res = executeFile("testDistinctAggregation_case3.sql"); + assertResultSet(res, "testDistinctAggregation_case3.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase4() throws Exception { + // two groupby, two distinct, two aggregation + ResultSet res = executeFile("testDistinctAggregation_case4.sql"); + assertResultSet(res, "testDistinctAggregation_case4.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase5() throws Exception { + // two groupby, two distinct, two aggregation with stage + ResultSet res = executeFile("testDistinctAggregation_case5.sql"); + assertResultSet(res, "testDistinctAggregation_case5.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase6() throws Exception { + ResultSet res = executeFile("testDistinctAggregation_case6.sql"); + assertResultSet(res, "testDistinctAggregation_case6.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase7() throws Exception { + ResultSet res = executeFile("testDistinctAggregation_case7.sql"); + assertResultSet(res, "testDistinctAggregation_case7.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase8() throws Exception { + ResultSet res = executeFile("testDistinctAggregation_case8.sql"); + assertResultSet(res, "testDistinctAggregation_case8.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase9() throws Exception { + ResultSet res = executeFile("testDistinctAggregation_case9.sql"); + assertResultSet(res, "testDistinctAggregation_case9.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase10() throws Exception { + ResultSet res = executeFile("testDistinctAggregation_case10.sql"); + assertResultSet(res, "testDistinctAggregation_case10.result"); + res.close(); + } + + @Test + public final void testDistinctAggregationCasebyCase11() throws Exception { + ResultSet res; + + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("id", Type.TEXT); + schema.addColumn("code", Type.TEXT); + schema.addColumn("qty", Type.INT4); + schema.addColumn("qty2", Type.FLOAT8); + String[] data = new String[]{"1|a|3|3.0", "1|a|4|4.0", "1|b|5|5.0", "2|a|1|6.0", "2|c|2|7.0", "2|d|3|8.0"}; + TajoTestingCluster.createTable("table10", schema, tableOptions, data); + + res = executeString("select id, count(distinct code), " + + "avg(qty), min(qty), max(qty), sum(qty), " + + "cast(avg(qty2) as INT8), cast(min(qty2) as INT8), cast(max(qty2) as INT8), cast(sum(qty2) as INT8) " + + "from table10 group by id"); + + String expected = "id,?count_4,?avg_5,?min_6,?max_7,?sum_8,?cast_9,?cast_10,?cast_11,?cast_12\n" + + "-------------------------------\n" + + "1,2,4.0,3,5,12,4,3,5,12\n" + + "2,3,2.0,1,3,6,7,6,8,21\n"; + + assertEquals(expected, resultSetToString(res)); + + // multiple distinct with expression + res = executeString( + "select count(distinct code) + count(distinct qty) from table10" + ); + + expected = "?plus_2\n" + + "-------------------------------\n" + + "9\n"; + + assertEquals(expected, resultSetToString(res)); + res.close(); + + res = executeString( + "select id, count(distinct code) + count(distinct qty) from table10 group by id" + ); + + expected = "id,?plus_2\n" + + "-------------------------------\n" + + "1,5\n" + + "2,6\n"; + + assertEquals(expected, resultSetToString(res)); + res.close(); + + executeString("DROP TABLE table10 PURGE").close(); + } + + @Test + public final void testDistinctAggregationCaseByCase3() throws Exception { + // first distinct is smaller than second distinct. + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.TEXT); + schema.addColumn("col2", Type.TEXT); + schema.addColumn("col3", Type.TEXT); + + String[] data = new String[]{ + "a|b-1|\\N", + "a|b-2|\\N", + "a|b-2|\\N", + "a|b-3|\\N", + "a|b-3|\\N", + "a|b-3|\\N" + }; + + TajoTestingCluster.createTable("table10", schema, tableOptions, data); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE table10 PURGE").close(); + } + + @Test + public final void testDistinctAggregationCaseByCase4() throws Exception { + // Reproduction case for TAJO-994 + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.TEXT); + schema.addColumn("col2", Type.TEXT); + + String[] data = new String[]{ + "a|\\N", + "a|\\N|", + "a|\\N|", + "a|\\N|", + "a|\\N|", + "a|\\N|" + }; + + TajoTestingCluster.createTable("testDistinctAggregationCaseByCase4".toLowerCase(), schema, tableOptions, data); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testDistinctAggregationCaseByCase4 PURGE").close(); + } + + @Test + public final void testComplexParameter() throws Exception { + // select sum(l_extendedprice*l_discount) as revenue from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testComplexParameterWithSubQuery() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testComplexParameter2() throws Exception { + // select count(*) + max(l_orderkey) as merged from lineitem; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testHavingWithNamedTarget() throws Exception { + // select l_orderkey, avg(l_partkey) total, sum(l_linenumber) as num from lineitem group by l_orderkey + // having total >= 2 or num = 3 order by l_orderkey, total; + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + @Option(sort = true) + @SimpleTest + public final void testHavingWithAggFunction() throws Exception { + // select l_orderkey, avg(l_partkey) total, sum(l_linenumber) as num from lineitem group by l_orderkey + // having avg(l_partkey) = 2.5 or num = 1; + runSimpleTests(); + } + + @Test + public final void testGroupbyWithJson() throws Exception { + // select l_orderkey, avg(l_partkey) total, sum(l_linenumber) as num from lineitem group by l_orderkey + // having total >= 2 or num = 3; + ResultSet res = executeJsonQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData3() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData4() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData5() throws Exception { + executeString("CREATE TABLE testGroupByWithNullData5 (age INT4, point FLOAT4);").close(); + assertTableExists("testGroupByWithNullData5".toLowerCase()); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testGroupByWithNullData5"); + } + + @Test + public final void testGroupByWithNullData6() throws Exception { + executeString("CREATE TABLE testGroupByWithNullData6 (age INT4, point FLOAT4);").close(); + assertTableExists("testGroupByWithNullData6".toLowerCase()); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testGroupByWithNullData6"); + } + + @Test + public final void testGroupByWithNullData7() throws Exception { + executeString("CREATE TABLE testGroupByWithNullData7 (age INT4, point FLOAT4);").close(); + assertTableExists("testGroupByWithNullData7".toLowerCase()); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testGroupByWithNullData7"); + } + + @Test + public final void testGroupByWithNullData8() throws Exception { + executeString("CREATE TABLE testGroupByWithNullData8 (age INT4, point FLOAT4);").close(); + assertTableExists("testGroupByWithNullData8".toLowerCase()); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testGroupByWithNullData8"); + } + + @Test + public final void testGroupByWithNullData9() throws Exception { + executeString("CREATE TABLE testGroupByWithNullData9 (age INT4, point FLOAT4);").close(); + assertTableExists("testGroupByWithNullData9".toLowerCase()); + + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + + executeString("DROP TABLE testGroupByWithNullData9"); + } + + @Test + public final void testGroupByWithNullData10() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData11() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupByWithNullData12() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testNumShufflePartition() throws Exception { + + Thread.sleep(5000); + KeyValueSet tableOptions = new KeyValueSet(); + tableOptions.set(StorageConstants.TEXT_DELIMITER, StorageConstants.DEFAULT_FIELD_DELIMITER); + tableOptions.set(StorageConstants.TEXT_NULL, "\\\\N"); + + Schema schema = new Schema(); + schema.addColumn("col1", Type.TEXT); + schema.addColumn("col2", Type.TEXT); + + List<String> data = new ArrayList<String>(); + int totalBytes = 0; + Random rand = new Random(System.currentTimeMillis()); + String col1Prefix = "Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1" + + "Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1" + + "Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1Column-1"; + + Set<Integer> uniqKeys = new HashSet<Integer>(); + while(true) { + int col1RandomValue = rand.nextInt(1000000); + uniqKeys.add(col1RandomValue); + String str = (col1Prefix + "-" + col1RandomValue) + "|col2-" + rand.nextInt(1000000); + data.add(str); + + totalBytes += str.getBytes().length; + + if (totalBytes > 3 * 1024 * 1024) { + break; + } + } + TajoTestingCluster.createTable("testnumshufflepartition", schema, tableOptions, data.toArray(new String[]{}), 3); + + try { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_GROUPBY_PARTITION_VOLUME.varname, "2"); + ResultSet res = executeString( + "select col1 \n" + + ",count(distinct col2) as cnt1\n" + + "from testnumshufflepartition \n" + + "group by col1" + ); + + int numRows = 0; + while (res.next()) { + numRows++; + } + assertEquals(uniqKeys.size(), numRows); + + // find last QueryMasterTask + List<QueryMasterTask> qmTasks = new ArrayList<QueryMasterTask>(); + + for(TajoWorker worker: testingCluster.getTajoWorkers()) { + qmTasks.addAll(worker.getWorkerContext().getQueryMaster().getFinishedQueryMasterTasks()); + } + + assertTrue(!qmTasks.isEmpty()); + + Collections.sort(qmTasks, new Comparator<QueryMasterTask>() { + @Override + public int compare(QueryMasterTask o1, QueryMasterTask o2) { + long l1 = o1.getQuerySubmitTime(); + long l2 = o2.getQuerySubmitTime(); + return l1 < l2 ? - 1 : (l1 > l2 ? 1 : 0); + } + }); + + // Getting the number of partitions. It should be 2. + Set<Integer> partitionIds = new HashSet<Integer>(); + + Query query = qmTasks.get(qmTasks.size() - 1).getQuery(); + Collection<Stage> stages = query.getStages(); + assertNotNull(stages); + assertTrue(!stages.isEmpty()); + for (Stage stage : stages) { + if (stage.getId().toStringNoPrefix().endsWith("_000001")) { + for (Task.IntermediateEntry eachInterm: stage.getHashShuffleIntermediateEntries()) { + partitionIds.add(eachInterm.getPartId()); + } + } + } + + assertEquals(2, partitionIds.size()); + executeString("DROP TABLE testnumshufflepartition PURGE").close(); + } finally { + testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_GROUPBY_PARTITION_VOLUME.varname, + ConfVars.$DIST_QUERY_GROUPBY_PARTITION_VOLUME.defaultVal); + } + } + + @Test + public final void testGroupbyWithLimit1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupbyWithLimit2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testGroupbyWithLimit3() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + @Option(sort = true) + @SimpleTest + public final void testGroupbyWithPythonFunc() throws Exception { + runSimpleTests(); + } + + @Test + @Option(sort = true) + @SimpleTest + public final void testGroupbyWithPythonFunc2() throws Exception { + runSimpleTests(); + } + + @Test + public final void testPythonUdaf() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testPythonUdaf2() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + @Option(sort = true) + @SimpleTest + public final void testPythonUdaf3() throws Exception { + runSimpleTests(); + } + + // TODO: this test cannot be executed due to the bug of logical planner (TAJO-1588) +// @Test + public final void testPythonUdafWithHaving() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + @Test + public final void testPythonUdafWithNullData() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + // TODO: this test cannot be executed due to the bug of logical planner (TAJO-1588) +// @Test + public final void testComplexTargetWithPythonUdaf() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } + + // TODO: this test cannot be executed due to the bug of logical planner (TAJO-1588) +// @Test + public final void testDistinctPythonUdafWithUnion1() throws Exception { + ResultSet res = executeQuery(); + assertResultSet(res); + cleanupQuery(res); + } +}
