tajo-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hyun...@apache.org
Subject [36/51] [partial] tajo git commit: TAJO-1761: Separate an integration unit test kit into an independent module.
Date Fri, 14 Aug 2015 14:30:15 GMT
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);
+  }
+}


Mime
View raw message