tajo-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jihoon...@apache.org
Subject [44/57] [abbrv] tajo git commit: TAJO-1577: Add test cases to verify join plans. (jihoon)
Date Mon, 25 May 2015 14:36:37 GMT
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinQuery.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinQuery.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinQuery.java
index 7ac25ea..0e42412 100644
--- a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinQuery.java
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinQuery.java
@@ -18,42 +18,48 @@
 
 package org.apache.tajo.engine.query;
 
-import junit.framework.Assert;
-import org.apache.tajo.IntegrationTest;
+import com.google.protobuf.ServiceException;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.hadoop.fs.FileStatus;
+import org.apache.hadoop.fs.FileSystem;
+import org.apache.hadoop.fs.Path;
 import org.apache.tajo.QueryTestCaseBase;
 import org.apache.tajo.TajoConstants;
 import org.apache.tajo.TajoTestingCluster;
+import org.apache.tajo.catalog.Column;
 import org.apache.tajo.catalog.Schema;
-import org.apache.tajo.common.TajoDataTypes.Type;
+import org.apache.tajo.catalog.TableDesc;
+import org.apache.tajo.catalog.TableMeta;
+import org.apache.tajo.common.TajoDataTypes;
 import org.apache.tajo.conf.TajoConf.ConfVars;
-import org.apache.tajo.storage.StorageConstants;
+import org.apache.tajo.datum.Datum;
+import org.apache.tajo.datum.Int4Datum;
+import org.apache.tajo.datum.TextDatum;
+import org.apache.tajo.storage.*;
+import org.apache.tajo.util.FileUtil;
 import org.apache.tajo.util.KeyValueSet;
-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.io.File;
+import java.io.OutputStream;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.List;
 
-import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertTrue;
 
-@Category(IntegrationTest.class)
-@RunWith(Parameterized.class)
 public class TestJoinQuery extends QueryTestCaseBase {
+  private static final Log LOG = LogFactory.getLog(TestJoinQuery.class);
+  private static int reference = 0;
 
-  public TestJoinQuery(String joinOption) {
-    super(TajoConstants.DEFAULT_DATABASE_NAME);
+  public TestJoinQuery(String joinOption) throws Exception {
+    super(TajoConstants.DEFAULT_DATABASE_NAME, joinOption);
 
-    testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_BROADCAST_JOIN_ENABLED.varname,
-        ConfVars.$TEST_BROADCAST_JOIN_ENABLED.defaultVal);
-    testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_BROADCAST_JOIN_THRESHOLD.varname,
-        ConfVars.$DIST_QUERY_BROADCAST_JOIN_THRESHOLD.defaultVal);
+    testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_BROADCAST_JOIN_ENABLED.varname, "true");
+    testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_BROADCAST_JOIN_THRESHOLD.varname, "" + (5 * 1024));
 
     testingCluster.setAllTajoDaemonConfValue(
         ConfVars.$EXECUTOR_HASH_JOIN_SIZE_THRESHOLD.varname,
@@ -97,8 +103,13 @@ public class TestJoinQuery extends QueryTestCaseBase {
     });
   }
 
-  @AfterClass
-  public static void classTearDown() {
+  public static void setup() throws Exception {
+    if (reference++ == 0) {
+      createCommonTables();
+    }
+  }
+
+  public static void classTearDown() throws ServiceException {
     testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_BROADCAST_JOIN_ENABLED.varname,
         ConfVars.$TEST_BROADCAST_JOIN_ENABLED.defaultVal);
     testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_BROADCAST_JOIN_THRESHOLD.varname,
@@ -112,1096 +123,192 @@ public class TestJoinQuery extends QueryTestCaseBase {
         ConfVars.$EXECUTOR_HASH_JOIN_SIZE_THRESHOLD.defaultVal);
     testingCluster.setAllTajoDaemonConfValue(ConfVars.$EXECUTOR_GROUPBY_INMEMORY_HASH_THRESHOLD.varname,
         ConfVars.$EXECUTOR_GROUPBY_INMEMORY_HASH_THRESHOLD.defaultVal);
-  }
-
-  @Test
-  public final void testCrossJoin() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testCrossJoinWithThetaJoinConditionInWhere() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testInnerJoinWithThetaJoinConditionInWhere() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithThetaJoinConditionInWhere() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testRightOuterJoinWithThetaJoinConditionInWhere() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin2() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin3() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin4() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin5() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testWhereClauseJoin6() throws Exception {
-    ResultSet res = executeQuery();
-    System.out.println(resultSetToString(res));
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testTPCHQ2Join() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithMultipleJoinQual1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithMultipleJoinQual2() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithMultipleJoinQual3() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithMultipleJoinQual4() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithMultipleJoinTypes() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoin1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithConstantExpr1() throws Exception {
-    // outer join with constant projections
-    //
-    // select c_custkey, orders.o_orderkey, 'val' as val from customer
-    // left outer join orders on c_custkey = o_orderkey;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithConstantExpr2() throws Exception {
-    // outer join with constant projections
-    //
-    // select c_custkey, o.o_orderkey, 'val' as val from customer left outer join
-    // (select * from orders) o on c_custkey = o.o_orderkey
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithConstantExpr3() throws Exception {
-    // outer join with constant projections
-    //
-    // select a.c_custkey, 123::INT8 as const_val, b.min_name from customer a
-    // left outer join ( select c_custkey, min(c_name) as min_name from customer group by c_custkey) b
-    // on a.c_custkey = b.c_custkey;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithConstantExpr4() throws Exception {
-    // outer join with constant projections
-    //
-    // select
-    //   c_custkey,
-    //   orders.o_orderkey,
-    //   1 as key1
-    // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithConstantExpr5() throws Exception {
-    // outer join with constant projections
-    //
-    // select
-    //   c_custkey,
-    //   orders.o_orderkey,
-    //   1 as key1
-    // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testRightOuterJoin1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testFullOuterJoin1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testJoinCoReferredEvals1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testJoinCoReferredEvalsWithSameExprs1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testJoinCoReferredEvalsWithSameExprs2() throws Exception {
-    // including grouping operator
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testInnerJoinAndCaseWhen() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testComplexJoinsWithCaseWhen() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testComplexJoinsWithCaseWhen2() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testOuterJoinAndCaseWhen1() throws Exception {
-    executeDDL("oj_table1_ddl.sql", "table1");
-    executeDDL("oj_table2_ddl.sql", "table2");
-    try {
-      ResultSet res = executeQuery();
-      assertResultSet(res);
-      cleanupQuery(res);
-    } finally {
-      executeString("DROP TABLE table1");
-      executeString("DROP TABLE table2");
-    }
-  }
-
-  @Test
-  public void testCrossJoinWithAsterisk1() throws Exception {
-    // select region.*, customer.* from region, customer;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-   public void testCrossJoinWithAsterisk2() throws Exception {
-    // select region.*, customer.* from customer, region;
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testCrossJoinWithAsterisk3() throws Exception {
-    // select * from customer, region
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testCrossJoinWithAsterisk4() throws Exception {
-    // select length(r_comment) as len, *, c_custkey*10 from customer, region order by len,r_regionkey,r_name
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testInnerJoinWithEmptyTable() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptyTable1() throws Exception {
-    /*
-    select
-      c_custkey,
-      empty_orders.o_orderkey,
-      empty_orders.o_orderstatus,
-      empty_orders.o_orderdate
-    from
-      customer left outer join empty_orders on c_custkey = o_orderkey
-    order by
-      c_custkey, o_orderkey;
-     */
-
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptyTable2() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptyTable3() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptyTable4() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptyTable5() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testRightOuterJoinWithEmptyTable1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptySubquery1() throws Exception {
-    // Empty Null Supplying table
-    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.INT4);
-    schema.addColumn("name", Type.TEXT);
-    String[] data = new String[]{ "1|table11-1", "2|table11-2", "3|table11-3", "4|table11-4", "5|table11-5" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data, 2);
-
-    data = new String[]{ "1|table11-1", "2|table11-2" };
-    TajoTestingCluster.createTable("table12", schema, tableOptions, data, 2);
-
-    try {
-      testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2");
-
-      ResultSet res = executeString("select a.id, b.id from table11 a " +
-          "left outer join (" +
-          "select table12.id from table12 inner join lineitem on table12.id = lineitem.l_orderkey and table12.id > 10) b " +
-          "on a.id = b.id order by a.id");
-
-      String expected = "id,id\n" +
-          "-------------------------------\n" +
-          "1,null\n" +
-          "2,null\n" +
-          "3,null\n" +
-          "4,null\n" +
-          "5,null\n";
-
-      assertEquals(expected, resultSetToString(res));
-      cleanupQuery(res);
-    } finally {
-      testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname,
-          ConfVars.$TEST_MIN_TASK_NUM.defaultVal);
-      executeString("DROP TABLE table11 PURGE").close();
-      executeString("DROP TABLE table12 PURGE").close();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithEmptySubquery2() throws Exception {
-    //Empty Preserved Row table
-    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.INT4);
-    schema.addColumn("name", Type.TEXT);
-    String[] data = new String[]{ "1|table11-1", "2|table11-2", "3|table11-3", "4|table11-4", "5|table11-5" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data, 2);
-
-    data = new String[]{ "1|table11-1", "2|table11-2" };
-    TajoTestingCluster.createTable("table12", schema, tableOptions, data, 2);
-
-    try {
-      testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2");
-
-      ResultSet res = executeString("select a.id, b.id from " +
-          "(select table12.id, table12.name, lineitem.l_shipdate " +
-          "from table12 inner join lineitem on table12.id = lineitem.l_orderkey and table12.id > 10) a " +
-          "left outer join table11 b " +
-          "on a.id = b.id");
-
-      String expected = "id,id\n" +
-          "-------------------------------\n";
-
-      assertEquals(expected, resultSetToString(res));
-      cleanupQuery(res);
-    } finally {
-      testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname,
-          ConfVars.$TEST_MIN_TASK_NUM.defaultVal);
-      executeString("DROP TABLE table11 PURGE");
-      executeString("DROP TABLE table12 PURGE");
-    }
-  }
-  
-  @Test
-  public final void testFullOuterJoinWithEmptyTable1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testCrossJoinWithEmptyTable1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinOnMultipleDatabases() throws Exception {
-    executeString("CREATE DATABASE JOINS");
-    assertDatabaseExists("joins");
-    executeString("CREATE TABLE JOINS.part_ as SELECT * FROM part");
-    assertTableExists("joins.part_");
-    executeString("CREATE TABLE JOINS.supplier_ as SELECT * FROM supplier");
-    assertTableExists("joins.supplier_");
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-
-    executeString("DROP TABLE JOINS.part_ PURGE");
-    executeString("DROP TABLE JOINS.supplier_ PURGE");
-    executeString("DROP DATABASE JOINS");
-  }
-
-  @Test
-  public final void testJoinWithJson() throws Exception {
-    // select length(r_comment) as len, *, c_custkey*10 from customer, region order by len,r_regionkey,r_name
-    ResultSet res = executeJsonQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinWithJson2() throws Exception {
-    /*
-    select t.n_nationkey, t.n_name, t.n_regionkey, t.n_comment, ps.ps_availqty, s.s_suppkey
-    from (
-      select n_nationkey, n_name, n_regionkey, n_comment
-      from nation n
-      join region r on (n.n_regionkey = r.r_regionkey)
-    ) t
-    join supplier s on (s.s_nationkey = t.n_nationkey)
-    join partsupp ps on (s.s_suppkey = ps.ps_suppkey)
-    where t.n_name in ('ARGENTINA','ETHIOPIA', 'MOROCCO');
-     */
-    ResultSet res = executeJsonQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testJoinOnMultipleDatabasesWithJson() throws Exception {
-    executeString("CREATE DATABASE JOINS");
-    assertDatabaseExists("joins");
-    executeString("CREATE TABLE JOINS.part_ as SELECT * FROM part");
-    assertTableExists("joins.part_");
-    executeString("CREATE TABLE JOINS.supplier_ as SELECT * FROM supplier");
-    assertTableExists("joins.supplier_");
-    ResultSet res = executeJsonQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-
-    executeString("DROP TABLE JOINS.part_ PURGE");
-    executeString("DROP TABLE JOINS.supplier_ PURGE");
-    executeString("DROP DATABASE JOINS");
-  }
-
-  @Test
-  public final void testJoinAsterisk() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithNull1() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithNull2() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinWithNull3() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase1() throws Exception {
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id\n" +
-              "left outer join table13 t3\n" +
-              "on t1.id = t3.id and t2.id = t3.id");
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "1,table11-1,1,null\n" +
-              "2,table11-2,null,null\n" +
-              "3,table11-3,null,null\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase2() throws Exception {
-    // outer -> outer -> inner
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id, t4.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id\n" +
-              "left outer join table13 t3\n" +
-              "on t2.id = t3.id\n" +
-              "inner join table14 t4\n" +
-              "on t2.id = t4.id"
-      );
-
-      String expected =
-          "id,name,id,id,id\n" +
-              "-------------------------------\n" +
-              "1,table11-1,1,null,1\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase2_1() throws Exception {
-    // inner(on predication) -> outer(on predication) -> outer -> where
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id, t4.id\n" +
-              "from table11 t1\n" +
-              "inner join table14 t4\n" +
-              "on t1.id = t4.id and t4.id > 1\n" +
-              "left outer join table13 t3\n" +
-              "on t4.id = t3.id and t3.id = 2\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id \n" +
-              "where t1.id > 1"
-      );
-
-      String expected =
-          "id,name,id,id,id\n" +
-              "-------------------------------\n" +
-              "2,table11-2,null,2,2\n" +
-              "3,table11-3,null,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase3() throws Exception {
-    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
-    // Case J1: Join Predicate on Preserved Row Table
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2 \n" +
-              "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" +
-              "left outer join table13 t3\n" +
-              "on t1.id = t3.id "
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "1,table11-1,1,null\n" +
-              "2,table11-2,null,2\n" +
-              "3,table11-3,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase4() throws Exception {
-    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
-    // Case J2: Join Predicate on Null Supplying Table
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id and t2.id > 1 \n" +
-              "left outer join table13 t3\n" +
-              "on t1.id = t3.id"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "1,table11-1,null,null\n" +
-              "2,table11-2,null,2\n" +
-              "3,table11-3,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase5() throws Exception {
-    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
-    // Case W1: Where Predicate on Preserved Row Table
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id\n" +
-              "left outer join table13 t3\n" +
-              "on t1.id = t3.id\n" +
-              "where t1.name > 'table11-1'"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "2,table11-2,null,2\n" +
-              "3,table11-3,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testLeftOuterJoinPredicationCaseByCase6() throws Exception {
-    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
-    // Case W2: Where Predicate on Null Supplying Table
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "left outer join table12 t2\n" +
-              "on t1.id = t2.id\n" +
-              "left outer join table13 t3\n" +
-              "on t1.id = t3.id\n" +
-              "where t3.id > 2"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "3,table11-3,null,3\n";
-
-      String result = resultSetToString(res);
 
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
+    if (--reference == 0) {
+      dropCommonTables();
     }
   }
 
-  @Test
-  public final void testLeftOuterWithEmptyTable() throws Exception {
-    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
-    // Case W2: Where Predicate on Null Supplying Table
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id\n" +
-              "from table11 t1\n" +
-              "left outer join table15 t2\n" +
-              "on t1.id = t2.id"
-      );
+  protected static void createCommonTables() throws Exception {
+    LOG.info("Create common tables for join tests");
 
-      String expected =
-          "id,name,id\n" +
-              "-------------------------------\n" +
-              "1,table11-1,null\n" +
-              "2,table11-2,null\n" +
-              "3,table11-3,null\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testRightOuterJoinPredicationCaseByCase1() throws Exception {
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "right outer join table12 t2\n" +
-              "on t1.id = t2.id\n" +
-              "right outer join table13 t3\n" +
-              "on t1.id = t3.id and t2.id = t3.id"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "null,null,null,2\n" +
-              "null,null,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testRightOuterJoinPredicationCaseByCase2() throws Exception {
-    // inner -> right
-    // Notice: Join order should be preserved with origin order.
-    // JoinEdge: t1 -> t4, t3 -> t1,t4
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t3.id, t4.id\n" +
-              "from table11 t1\n" +
-              "inner join table14 t4\n" +
-              "on t1.id = t4.id and t4.id > 1\n" +
-              "right outer join table13 t3\n" +
-              "on t4.id = t3.id and t3.id = 2\n" +
-              "where t3.id > 1"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "2,table11-2,2,2\n" +
-              "null,null,3,null\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testRightOuterJoinPredicationCaseByCase3() throws Exception {
-    createOuterJoinTestTable();
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t2.id, t3.id\n" +
-              "from table11 t1\n" +
-              "right outer join table12 t2 \n" +
-              "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" +
-              "right outer join table13 t3\n" +
-              "on t1.id = t3.id "
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "null,null,null,2\n" +
-              "null,null,null,3\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  @Test
-  public final void testFullOuterJoinPredicationCaseByCase1() throws Exception {
-    createOuterJoinTestTable();
-
-    try {
-      ResultSet res = executeString(
-          "select t1.id, t1.name, t3.id, t4.id\n" +
-              "from table11 t1\n" +
-              "full outer join table13 t3\n" +
-              "on t1.id = t3.id\n" +
-              "full outer join table14 t4\n" +
-              "on t3.id = t4.id \n" +
-              "order by t4.id"
-      );
-
-      String expected =
-          "id,name,id,id\n" +
-              "-------------------------------\n" +
-              "null,null,null,1\n" +
-              "2,table11-2,2,2\n" +
-              "3,table11-3,3,3\n" +
-              "null,null,null,4\n" +
-              "1,table11-1,null,null\n";
-
-      String result = resultSetToString(res);
-
-      assertEquals(expected, result);
-    } finally {
-      dropOuterJoinTestTable();
-    }
-  }
-
-  private void createOuterJoinTestTable() throws Exception {
     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.INT4);
-    schema.addColumn("name", Type.TEXT);
-    String[] data = new String[]{ "1|table11-1", "2|table11-2", "3|table11-3" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data);
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
+    String[] data = new String[]{"1|table11-1", "2|table11-2", "3|table11-3", "4|table11-4", "5|table11-5"};
+    TajoTestingCluster.createTable("jointable11", schema, tableOptions, data, 2);
 
     schema = new Schema();
-    schema.addColumn("id", Type.INT4);
-    schema.addColumn("name", Type.TEXT);
-    data = new String[]{ "1|table12-1" };
-    TajoTestingCluster.createTable("table12", schema, tableOptions, data);
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
+    data = new String[]{"1|table12-1", "2|table12-2"};
+    TajoTestingCluster.createTable("jointable12", schema, tableOptions, data, 2);
 
     schema = new Schema();
-    schema.addColumn("id", Type.INT4);
-    schema.addColumn("name", Type.TEXT);
-    data = new String[]{"2|table13-2", "3|table13-3" };
-    TajoTestingCluster.createTable("table13", schema, tableOptions, data);
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
+    data = new String[]{"2|table13-2", "3|table13-3"};
+    TajoTestingCluster.createTable("jointable13", schema, tableOptions, data);
 
     schema = new Schema();
-    schema.addColumn("id", Type.INT4);
-    schema.addColumn("name", Type.TEXT);
-    data = new String[]{"1|table14-1", "2|table14-2", "3|table14-3", "4|table14-4" };
-    TajoTestingCluster.createTable("table14", schema, tableOptions, data);
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
+    data = new String[]{"1|table14-1", "2|table14-2", "3|table14-3", "4|table14-4"};
+    TajoTestingCluster.createTable("jointable14", schema, tableOptions, data);
 
     schema = new Schema();
-    schema.addColumn("id", Type.INT4);
-    schema.addColumn("name", Type.TEXT);
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
     data = new String[]{};
-    TajoTestingCluster.createTable("table15", schema, tableOptions, data);
-  }
-
-  private void dropOuterJoinTestTable() throws Exception {
-    executeString("DROP TABLE table11 PURGE;");
-    executeString("DROP TABLE table12 PURGE;");
-    executeString("DROP TABLE table13 PURGE;");
-    executeString("DROP TABLE table14 PURGE;");
-    executeString("DROP TABLE table15 PURGE;");
-  }
+    TajoTestingCluster.createTable("jointable15", schema, tableOptions, data);
 
-  @Test
-  public void testDifferentTypesJoinCondition() throws Exception {
-    // select * from table20 t3 join table21 t4 on t3.id = t4.id;
-    executeDDL("table1_int8_ddl.sql", "table1", "table20");
-    executeDDL("table1_int4_ddl.sql", "table1", "table21");
-    try {
-      ResultSet res = executeQuery();
-      assertResultSet(res);
-      cleanupQuery(res);
-    } finally {
-      executeString("DROP TABLE table20");
-      executeString("DROP TABLE table21");
+    schema = new Schema();
+    schema.addColumn("id", TajoDataTypes.Type.INT4);
+    schema.addColumn("name", TajoDataTypes.Type.TEXT);
+    data = new String[]{"1000000|a", "1000001|b", "2|c", "3|d", "4|e"};
+    TajoTestingCluster.createTable("jointable1", schema, tableOptions, data, 1);
+
+    data = new String[10000];
+    for (int i = 0; i < data.length; i++) {
+      data[i] = i + "|" + "this is testLeftOuterJoinLeftSideSmallTabletestLeftOuterJoinLeftSideSmallTable" + i;
     }
+    TajoTestingCluster.createTable("jointable_large", schema, tableOptions, data, 2);
+
+    // According to node type(leaf or non-leaf) Broadcast join is determined differently by Repartitioner.
+    // testMultipleBroadcastDataFileWithZeroLength testcase is for the leaf node
+    createMultiFile("nation", 2, new TupleCreator() {
+      public Tuple createTuple(String[] columnDatas) {
+        return new VTuple(new Datum[]{
+            new Int4Datum(Integer.parseInt(columnDatas[0])),
+            new TextDatum(columnDatas[1]),
+            new Int4Datum(Integer.parseInt(columnDatas[2])),
+            new TextDatum(columnDatas[3])
+        });
+      }
+    });
+    addEmptyDataFile("nation_multifile", false);
   }
 
-  @Test
-  public void testComplexJoinCondition1() throws Exception {
-    // select n1.n_nationkey, n1.n_name, n2.n_name  from nation n1 join nation n2 on n1.n_name = upper(n2.n_name);
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
-
-  @Test
-  public void testComplexJoinCondition2() throws Exception {
-    // select n1.n_nationkey, n1.n_name, upper(n2.n_name) name from nation n1 join nation n2
-    // on n1.n_name = upper(n2.n_name);
+  protected static void dropCommonTables() throws ServiceException {
+    LOG.info("Clear common tables for join tests");
 
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
+    client.executeQuery("DROP TABLE IF EXISTS jointable11 PURGE;");
+    client.executeQuery("DROP TABLE IF EXISTS jointable12 PURGE;");
+    client.executeQuery("DROP TABLE IF EXISTS jointable13 PURGE;");
+    client.executeQuery("DROP TABLE IF EXISTS jointable14 PURGE;");
+    client.executeQuery("DROP TABLE IF EXISTS jointable15 PURGE;");
+    client.executeQuery("DROP TABLE IF EXISTS jointable1 PURGE");
+    client.executeQuery("DROP TABLE IF EXISTS jointable_large PURGE");
+    client.executeQuery("DROP TABLE IF EXISTS nation_multifile PURGE");
   }
 
-  @Test
-  public void testComplexJoinCondition3() throws Exception {
-    // select n1.n_nationkey, n1.n_name, n2.n_name from nation n1 join nation n2 on lower(n1.n_name) = lower(n2.n_name);
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
+  interface TupleCreator {
+    Tuple createTuple(String[] columnDatas);
   }
 
-  @Test
-  public void testComplexJoinCondition4() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
+  private static String buildSchemaString(String tableName) throws ServiceException {
+    TableDesc desc = client.getTableDesc(tableName);
+    StringBuffer sb = new StringBuffer();
+    for (Column column : desc.getSchema().getRootColumns()) {
+      sb.append(column.getSimpleName()).append(" ").append(column.getDataType().getType());
+      TajoDataTypes.DataType dataType = column.getDataType();
+      if (dataType.getLength() > 0) {
+        sb.append("(").append(dataType.getLength()).append(")");
+      }
+      sb.append(",");
+    }
+    sb.deleteCharAt(sb.length()-1);
+    return sb.toString();
   }
 
-  @Test
-  public void testComplexJoinCondition5() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
+  private static String buildMultifileDDlString(String tableName) throws ServiceException {
+    String multiTableName = tableName + "_multifile";
+    StringBuilder sb = new StringBuilder("create table ").append(multiTableName).append(" (");
+    sb.append(buildSchemaString(tableName)).append(" )");
+    return sb.toString();
   }
 
-  @Test
-  public void testComplexJoinCondition6() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
+  protected static void createMultiFile(String tableName, int numRowsEachFile, TupleCreator tupleCreator) throws Exception {
+    // make multiple small file
+    String multiTableName = tableName + "_multifile";
+    String sql = buildMultifileDDlString(tableName);
+    client.executeQueryAndGetResult(sql);
 
-  @Test
-  public void testComplexJoinCondition7() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
+    TableDesc table = client.getTableDesc(multiTableName);
+    assertNotNull(table);
 
-  @Test
-  public void testFullOuterJoinWithEmptyIntermediateData() throws Exception {
-    ResultSet res = executeString(
-        "select a.l_orderkey \n" +
-            "from (select * from lineitem where l_orderkey < 0) a\n" +
-            "full outer join (select * from lineitem where l_orderkey < 0) b\n" +
-            "on a.l_orderkey = b.l_orderkey"
-    );
+    TableMeta tableMeta = table.getMeta();
+    Schema schema = table.getLogicalSchema();
 
-    try {
-      String expected =
-          "l_orderkey\n" +
-              "-------------------------------\n";
+    File file = new File("src/test/tpch/" + tableName + ".tbl");
 
-      assertEquals(expected, resultSetToString(res));
-    } finally {
-      cleanupQuery(res);
+    if (!file.exists()) {
+      file = new File(System.getProperty("user.dir") + "/tajo-core/src/test/tpch/" + tableName + ".tbl");
     }
-  }
-
-  @Test
-  public void testJoinWithDifferentShuffleKey() throws Exception {
-    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.INT4);
-    schema.addColumn("name", Type.TEXT);
-
-    List<String> data = new ArrayList<String>();
-
-    int bytes = 0;
-    for (int i = 0; i < 1000000; i++) {
-      String row = i + "|" + i + "name012345678901234567890123456789012345678901234567890";
-      bytes += row.getBytes().length;
-      data.add(row);
-      if (bytes > 2 * 1024 * 1024) {
-        break;
+    String[] rows = FileUtil.readTextFile(file).split("\n");
+
+    assertTrue(rows.length > 0);
+
+    int fileIndex = 0;
+
+    Appender appender = null;
+    for (int i = 0; i < rows.length; i++) {
+      if (i % numRowsEachFile == 0) {
+        if (appender != null) {
+          appender.flush();
+          appender.close();
+        }
+        Path dataPath = new Path(table.getPath().toString(), fileIndex + ".csv");
+        fileIndex++;
+        appender = ((FileStorageManager) TableSpaceManager.getFileStorageManager(conf))
+            .getAppender(tableMeta, schema, dataPath);
+        appender.init();
       }
+      String[] columnDatas = rows[i].split("\\|");
+      Tuple tuple = tupleCreator.createTuple(columnDatas);
+      appender.addTuple(tuple);
     }
-    TajoTestingCluster.createTable("large_table", schema, tableOptions, data.toArray(new String[]{}));
-
-    int originConfValue = conf.getIntVar(ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME);
-    testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname, "1");
-    ResultSet res = executeString(
-       "select count(b.id) " +
-           "from (select id, count(*) as cnt from large_table group by id) a " +
-           "left outer join (select id, count(*) as cnt from large_table where id < 200 group by id) b " +
-           "on a.id = b.id"
-    );
-
-    try {
-      String expected =
-          "?count\n" +
-              "-------------------------------\n" +
-              "200\n";
-
-      assertEquals(expected, resultSetToString(res));
-    } finally {
-      testingCluster.setAllTajoDaemonConfValue(ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname, "" + originConfValue);
-      cleanupQuery(res);
-      executeString("DROP TABLE large_table PURGE").close();
-    }
+    appender.flush();
+    appender.close();
   }
 
-  @Test
-  public final void testJoinFilterOfRowPreservedTable1() throws Exception {
-    // this test is for join filter of a row preserved table.
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
-  }
+  protected static void addEmptyDataFile(String tableName, boolean isPartitioned) throws Exception {
+    TableDesc table = client.getTableDesc(tableName);
 
-  @Test
-  public final void testJoinWithOrPredicates() throws Exception {
-    ResultSet res = executeQuery();
-    assertResultSet(res);
-    cleanupQuery(res);
+    Path path = new Path(table.getPath());
+    FileSystem fs = path.getFileSystem(conf);
+    if (isPartitioned) {
+      List<Path> partitionPathList = getPartitionPathList(fs, path);
+      for (Path eachPath: partitionPathList) {
+        Path dataPath = new Path(eachPath, 0 + "_empty.csv");
+        OutputStream out = fs.create(dataPath);
+        out.close();
+      }
+    } else {
+      Path dataPath = new Path(path, 0 + "_empty.csv");
+      OutputStream out = fs.create(dataPath);
+      out.close();
+    }
   }
 
-  @Test
-  public final void testNaturalJoin() throws Exception {
-    ResultSet res = null;
-    try {
-      res = executeQuery();
-    } catch (Exception e) {
-      Assert.fail();
+  protected static List<Path> getPartitionPathList(FileSystem fs, Path path) throws Exception {
+    FileStatus[] files = fs.listStatus(path);
+    List<Path> paths = new ArrayList<Path>();
+    if (files != null) {
+      for (FileStatus eachFile: files) {
+        if (eachFile.isFile()) {
+          paths.add(path);
+          return paths;
+        } else {
+          paths.addAll(getPartitionPathList(fs, eachFile.getPath()));
+        }
+      }
     }
-    assertResultSet(res);
-    cleanupQuery(res);
+
+    return paths;
   }
 }

http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestMultipleJoinTypes.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestMultipleJoinTypes.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestMultipleJoinTypes.java
new file mode 100644
index 0000000..b772d90
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestMultipleJoinTypes.java
@@ -0,0 +1,105 @@
+/**
+ * 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 com.google.protobuf.ServiceException;
+import org.apache.tajo.IntegrationTest;
+import org.apache.tajo.NamedTest;
+import org.apache.tajo.QueryTestCaseBase;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+@Category(IntegrationTest.class)
+@RunWith(Parameterized.class)
+@NamedTest("TestJoinQuery")
+public class TestMultipleJoinTypes extends TestJoinQuery {
+
+  public TestMultipleJoinTypes(String joinOption) throws Exception {
+    super(joinOption);
+  }
+
+  @BeforeClass
+  public static void setup() throws Exception {
+    TestJoinQuery.setup();
+  }
+
+  @AfterClass
+  public static void classTearDown() throws ServiceException {
+    TestJoinQuery.classTearDown();
+  }
+
+  @Test
+  @QueryTestCaseBase.Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @QueryTestCaseBase.SimpleTest()
+  public final void testJoinWithMultipleJoinTypes() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public void testComplexJoinsWithCaseWhen() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public void testComplexJoinsWithCaseWhen2() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(prepare = {
+      "CREATE TABLE customer_broad_parts (" +
+          "  c_nationkey INT4," +
+          "  c_name    TEXT," +
+          "  c_address    TEXT," +
+          "  c_phone    TEXT," +
+          "  c_acctbal    FLOAT8," +
+          "  c_mktsegment    TEXT," +
+          "  c_comment    TEXT" +
+          ") PARTITION BY COLUMN (c_custkey INT4)",
+      "INSERT OVERWRITE INTO customer_broad_parts" +
+          "  SELECT" +
+          "    c_nationkey," +
+          "    c_name," +
+          "    c_address," +
+          "    c_phone," +
+          "    c_acctbal," +
+          "    c_mktsegment," +
+          "    c_comment," +
+          "    c_custkey" +
+          "  FROM customer"
+  }, cleanup = {
+      "DROP TABLE customer_broad_parts PURGE"
+  }, queries = {
+      @QuerySpec("select a.l_orderkey, b.o_orderkey, c.c_custkey from lineitem a " +
+          "inner join orders b on a.l_orderkey = b.o_orderkey " +
+          "left outer join customer_broad_parts c on a.l_orderkey = c.c_custkey and c.c_custkey < 0")
+  })
+  public final void testInnerAndOuterWithEmpty() throws Exception {
+    runSimpleTests();
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java
new file mode 100644
index 0000000..077b7d2
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinQuery.java
@@ -0,0 +1,462 @@
+/**
+ * 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 com.google.protobuf.ServiceException;
+import org.apache.tajo.IntegrationTest;
+import org.apache.tajo.NamedTest;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+import java.sql.ResultSet;
+
+@Category(IntegrationTest.class)
+@RunWith(Parameterized.class)
+@NamedTest("TestJoinQuery")
+public class TestOuterJoinQuery extends TestJoinQuery {
+
+  public TestOuterJoinQuery(String joinOption) throws Exception {
+    super(joinOption);
+  }
+
+  @BeforeClass
+  public static void setup() throws Exception {
+    TestJoinQuery.setup();
+  }
+
+  @AfterClass
+  public static void classTearDown() throws ServiceException {
+    TestJoinQuery.classTearDown();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithThetaJoinConditionInWhere() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testRightOuterJoinWithThetaJoinConditionInWhere() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoin1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithConstantExpr1() throws Exception {
+    // outer join with constant projections
+    //
+    // select c_custkey, orders.o_orderkey, 'val' as val from customer
+    // left outer join orders on c_custkey = o_orderkey;
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithConstantExpr4() throws Exception {
+    // outer join with constant projections
+    //
+    // select
+    //   c_custkey,
+    //   orders.o_orderkey,
+    //   1 as key1
+    // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1;
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithConstantExpr5() throws Exception {
+    // outer join with constant projections
+    //
+    // select
+    //   c_custkey,
+    //   orders.o_orderkey,
+    //   1 as key1
+    // from customer left outer join orders on c_custkey = o_orderkey and key1 = 1;
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testRightOuterJoin1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testFullOuterJoin1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public void testOuterJoinAndCaseWhen1() throws Exception {
+    executeDDL("oj_table1_ddl.sql", "table1", "testOuterJoinAndCaseWhen1");
+    executeDDL("oj_table2_ddl.sql", "table2", "testOuterJoinAndCaseWhen2");
+    try {
+      runSimpleTests();
+    } finally {
+      executeString("DROP TABLE testOuterJoinAndCaseWhen1");
+      executeString("DROP TABLE testOuterJoinAndCaseWhen2");
+    }
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithEmptyTable1() throws Exception {
+    /*
+    select
+      c_custkey,
+      empty_orders.o_orderkey,
+      empty_orders.o_orderstatus,
+      empty_orders.o_orderdate
+    from
+      customer left outer join empty_orders on c_custkey = o_orderkey
+    order by
+      c_custkey, o_orderkey;
+     */
+
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithEmptyTable2() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithEmptyTable3() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithEmptyTable4() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithEmptyTable5() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testRightOuterJoinWithEmptyTable1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testFullOuterJoinWithEmptyTable1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithNull1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithNull2() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithNull3() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id\n" +
+          "left outer join jointable13 t3\n" +
+          "on t1.id = t3.id and t2.id = t3.id")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id, t4.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id\n" +
+          "left outer join jointable13 t3\n" +
+          "on t2.id = t3.id\n" +
+          "inner join jointable14 t4\n" +
+          "on t2.id = t4.id")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase2() throws Exception {
+    // outer -> outer -> inner
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id, t4.id\n" +
+          "from jointable11 t1\n" +
+          "inner join jointable14 t4\n" +
+          "on t1.id = t4.id and t4.id > 1\n" +
+          "left outer join jointable13 t3\n" +
+          "on t4.id = t3.id and t3.id = 2\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id \n" +
+          "where t1.id > 1")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase2_1() throws Exception {
+    // inner(on predication) -> outer(on predication) -> outer -> where
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2 \n" +
+          "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" +
+          "left outer join jointable13 t3\n" +
+          "on t1.id = t3.id ")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase3() throws Exception {
+    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
+    // Case J1: Join Predicate on Preserved Row Table
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id and t2.id > 1 \n" +
+          "left outer join jointable13 t3\n" +
+          "on t1.id = t3.id")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase4() throws Exception {
+    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
+    // Case J2: Join Predicate on Null Supplying Table
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id\n" +
+          "left outer join jointable13 t3\n" +
+          "on t1.id = t3.id\n" +
+          "where t1.name > 'table11-1'")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase5() throws Exception {
+    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
+    // Case W1: Where Predicate on Preserved Row Table
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable12 t2\n" +
+          "on t1.id = t2.id\n" +
+          "left outer join jointable13 t3\n" +
+          "on t1.id = t3.id\n" +
+          "where t3.id > 2")
+  })
+  public final void testLeftOuterJoinPredicationCaseByCase6() throws Exception {
+    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
+    // Case W2: Where Predicate on Null Supplying Table
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id\n" +
+          "from jointable11 t1\n" +
+          "left outer join jointable15 t2\n" +
+          "on t1.id = t2.id")
+  })
+  public final void testLeftOuterWithEmptyTable() throws Exception {
+    // https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
+    // Case W2: Where Predicate on Null Supplying Table
+    runSimpleTests();
+  }
+
+  // TODO: this test is disabled due to a bug in broadcast join. It will be enabled after TAJO-1553
+//  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "right outer join jointable12 t2\n" +
+          "on t1.id = t2.id\n" +
+          "right outer join jointable13 t3\n" +
+          "on t1.id = t3.id and t2.id = t3.id")
+  })
+  public final void testRightOuterJoinPredicationCaseByCase1() throws Exception {
+    runSimpleTests();
+  }
+
+  // TODO: this test is disabled due to a bug in broadcast join. It will be enabled after TAJO-1553
+//  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t3.id, t4.id\n" +
+          "from jointable11 t1\n" +
+          "inner join jointable14 t4\n" +
+          "on t1.id = t4.id and t4.id > 1\n" +
+          "right outer join jointable13 t3\n" +
+          "on t4.id = t3.id and t3.id = 2\n" +
+          "where t3.id > 1")
+  })
+  public final void testRightOuterJoinPredicationCaseByCase2() throws Exception {
+    // inner -> right
+    // Notice: Join order should be preserved with origin order.
+    // JoinEdge: t1 -> t4, t3 -> t1,t4
+    runSimpleTests();
+  }
+
+  // TODO: this test is disabled due to a bug in broadcast join. It will be enabled after TAJO-1553
+//  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t2.id, t3.id\n" +
+          "from jointable11 t1\n" +
+          "right outer join jointable12 t2 \n" +
+          "on t1.id = t2.id and (concat(t1.name, cast(t2.id as TEXT)) = 'table11-11' or concat(t1.name, cast(t2.id as TEXT)) = 'table11-33')\n" +
+          "right outer join jointable13 t3\n" +
+          "on t1.id = t3.id ")
+  })
+  public final void testRightOuterJoinPredicationCaseByCase3() throws Exception {
+    runSimpleTests();
+  }
+
+  // TODO: this test is disabled due to a bug in broadcast join. It will be enabled after TAJO-1553
+//  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select t1.id, t1.name, t3.id, t4.id\n" +
+          "from jointable11 t1\n" +
+          "full outer join jointable13 t3\n" +
+          "on t1.id = t3.id\n" +
+          "full outer join jointable14 t4\n" +
+          "on t3.id = t4.id \n" +
+          "order by t4.id")
+  })
+  public final void testFullOuterJoinPredicationCaseByCase1() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testJoinFilterOfRowPreservedTable1() throws Exception {
+    // this test is for join filter of a row preserved table.
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoin2() throws Exception {
+    // large, large, small, small
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoin3() throws Exception {
+    // large, large, small, large, small, small
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select a.id, b.name from jointable1 a left outer join jointable_large b on a.id = b.id order by a.id")
+  })
+  public final void testLeftOuterJoinLeftSideSmallTable() throws Exception {
+   runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest
+  public void testMultipleBroadcastDataFileWithZeroLength() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true, sort = true)
+  @SimpleTest
+  public void testMultipleBroadcastDataFileWithZeroLength2() throws Exception {
+    runSimpleTests();
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java
new file mode 100644
index 0000000..71db027
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestOuterJoinWithSubQuery.java
@@ -0,0 +1,142 @@
+/**
+ * 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 com.google.protobuf.ServiceException;
+import org.apache.tajo.IntegrationTest;
+import org.apache.tajo.NamedTest;
+import org.apache.tajo.conf.TajoConf;
+import org.junit.*;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+import static org.junit.Assert.assertEquals;
+
+@Category(IntegrationTest.class)
+@RunWith(Parameterized.class)
+@NamedTest("TestJoinQuery")
+public class TestOuterJoinWithSubQuery extends TestJoinQuery {
+
+  public TestOuterJoinWithSubQuery(String joinOption) throws Exception {
+    super(joinOption);
+  }
+
+  @BeforeClass
+  public static void setup() throws Exception {
+    TestJoinQuery.setup();
+  }
+
+  @AfterClass
+  public static void classTearDown() throws ServiceException {
+    TestJoinQuery.classTearDown();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithConstantExpr2() throws Exception {
+    // outer join with constant projections
+    //
+    // select c_custkey, o.o_orderkey, 'val' as val from customer left outer join
+    // (select * from orders) o on c_custkey = o.o_orderkey
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest()
+  public final void testLeftOuterJoinWithConstantExpr3() throws Exception {
+    // outer join with constant projections
+    //
+    // select a.c_custkey, 123::INT8 as const_val, b.min_name from customer a
+    // left outer join ( select c_custkey, min(c_name) as min_name from customer group by c_custkey) b
+    // on a.c_custkey = b.c_custkey;
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select a.id, b.id from jointable11 a " +
+          "left outer join (" +
+          "select jointable12.id from jointable12 inner join lineitem " +
+          "on jointable12.id = lineitem.l_orderkey and jointable12.id > 10) b " +
+          "on a.id = b.id order by a.id")
+  })
+  public final void testLeftOuterJoinWithEmptySubquery1() throws Exception {
+    try {
+      testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, "2");
+      runSimpleTests();
+    } finally {
+      testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname,
+          TajoConf.ConfVars.$TEST_MIN_TASK_NUM.defaultVal);
+    }
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select a.id, b.id from " +
+          "(select jointable12.id, jointable12.name, lineitem.l_shipdate " +
+          "from jointable12 inner join lineitem on jointable12.id = lineitem.l_orderkey and jointable12.id > 10) a " +
+          "left outer join jointable11 b on a.id = b.id")
+  })
+  public final void testLeftOuterJoinWithEmptySubquery2() throws Exception {
+    //Empty Preserved Row table
+    try {
+      testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname, "2");
+      runSimpleTests();
+    } finally {
+      testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$TEST_MIN_TASK_NUM.varname,
+          TajoConf.ConfVars.$TEST_MIN_TASK_NUM.defaultVal);
+    }
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select a.l_orderkey \n" +
+          "from (select * from lineitem where l_orderkey < 0) a\n" +
+          "full outer join (select * from lineitem where l_orderkey < 0) b\n" +
+          "on a.l_orderkey = b.l_orderkey")
+  })
+  public void testFullOuterJoinWithEmptyIntermediateData() throws Exception {
+    runSimpleTests();
+  }
+
+  @Test
+  @Option(withExplain = true, withExplainGlobal = true, parameterized = true)
+  @SimpleTest(queries = {
+      @QuerySpec("select count(b.id) " +
+          "from (select id, count(*) as cnt from jointable_large group by id) a " +
+          "left outer join (select id, count(*) as cnt from jointable_large where id < 200 group by id) b " +
+          "on a.id = b.id")
+  })
+  public void testJoinWithDifferentShuffleKey() throws Exception {
+    int originConfValue = conf.getIntVar(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME);
+    testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname, "1");
+    try {
+      runSimpleTests();
+    } finally {
+      testingCluster.setAllTajoDaemonConfValue(TajoConf.ConfVars.$DIST_QUERY_JOIN_PARTITION_VOLUME.varname,
+          "" + originConfValue);
+    }
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java
index 002f05d..c0cf4d9 100644
--- a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSelectQuery.java
@@ -19,8 +19,13 @@
 package org.apache.tajo.engine.query;
 
 import com.google.common.collect.Lists;
-import org.apache.tajo.*;
+import org.apache.tajo.IntegrationTest;
+import org.apache.tajo.QueryId;
+import org.apache.tajo.QueryTestCaseBase;
+import org.apache.tajo.SessionVars;
+import org.apache.tajo.TajoConstants;
 import org.apache.tajo.TajoProtos.QueryState;
+import org.apache.tajo.TajoTestingCluster;
 import org.apache.tajo.catalog.CatalogService;
 import org.apache.tajo.catalog.Schema;
 import org.apache.tajo.catalog.TableDesc;
@@ -111,22 +116,13 @@ public class TestSelectQuery extends QueryTestCaseBase {
 
   @Test
   @SimpleTest(queries = {
-      "explain global " +
-          "select l_orderkey, l_partkey from lineitem",
-      "explain global " +
-          "select n1.n_nationkey, n1.n_name, n2.n_name from nation n1 join nation n2 on n1.n_name = upper(n2.n_name) " +
-          "order by n1.n_nationkey;",
-      "explain global " +
-          "select l_linenumber, count(*), count(distinct l_orderkey), sum(distinct l_orderkey) from lineitem " +
-          "group by l_linenumber having sum(distinct l_orderkey) = 6"})
+      @QuerySpec("explain global select l_orderkey, l_partkey from lineitem"),
+      @QuerySpec("explain global select n1.n_nationkey, n1.n_name, n2.n_name from nation n1 join nation n2 " +
+          "on n1.n_name = upper(n2.n_name) order by n1.n_nationkey"),
+      @QuerySpec("explain global select l_linenumber, count(*), count(distinct l_orderkey), sum(distinct l_orderkey) from lineitem " +
+          "group by l_linenumber having sum(distinct l_orderkey) = 6")})
   public final void testExplainSelectPhysical() throws Exception {
-    // Enable this option to fix the shape of the generated plans.
-    testingCluster.getConfiguration().set(ConfVars.$TEST_PLAN_SHAPE_FIX_ENABLED.varname, "true");
-    try {
-      runSimpleTests();
-    } finally {
-      testingCluster.getConfiguration().set(ConfVars.$TEST_PLAN_SHAPE_FIX_ENABLED.varname, "false");
-    }
+    runSimpleTests();
   }
 
   @Test
@@ -501,12 +497,13 @@ public class TestSelectQuery extends QueryTestCaseBase {
     schema.addColumn("id", Type.INT4);
     schema.addColumn("name", Type.TEXT);
     String[] data = new String[]{ "1|table11-1", "2|table11-2", "3|table11-3", "4|table11-4", "5|table11-5" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data, 2);
+    TajoTestingCluster.createTable("testNowInMultipleTasks".toLowerCase(), schema, tableOptions, data, 2);
 
     try {
       testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname, "2");
 
-      ResultSet res = executeString("select concat(substr(to_char(now(),'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) from table11");
+      ResultSet res = executeString("select concat(substr(to_char(now(),'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) " +
+          "from testNowInMultipleTasks");
 
       String nowValue = null;
       int numRecords = 0;
@@ -522,7 +519,8 @@ public class TestSelectQuery extends QueryTestCaseBase {
 
       res.close();
 
-      res = executeString("select concat(substr(to_char(current_timestamp,'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) from table11");
+      res = executeString("select concat(substr(to_char(current_timestamp,'yyyymmddhh24miss'), 1, 14), 'aaa'), sleep(1) " +
+          "from testNowInMultipleTasks");
 
       nowValue = null;
       numRecords = 0;
@@ -538,7 +536,7 @@ public class TestSelectQuery extends QueryTestCaseBase {
     } finally {
       testingCluster.setAllTajoDaemonConfValue(ConfVars.$TEST_MIN_TASK_NUM.varname,
           ConfVars.$TEST_MIN_TASK_NUM.defaultVal);
-      executeString("DROP TABLE table11 PURGE");
+      executeString("DROP TABLE testNowInMultipleTasks PURGE");
     }
   }
 

http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java
index 1aee961..fa77fda 100644
--- a/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/query/TestSortQuery.java
@@ -243,10 +243,10 @@ public class TestSortQuery extends QueryTestCaseBase {
     schema.addColumn("id", Type.INT4);
     schema.addColumn("name", Type.TEXT);
     String[] data = new String[]{ "1|111", "2|\\N", "3|333" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data, 1);
+    TajoTestingCluster.createTable("testSortOnNullColumn2".toLowerCase(), schema, tableOptions, data, 1);
 
     try {
-      ResultSet res = executeString("select * from table11 order by name asc");
+      ResultSet res = executeString("select * from testSortOnNullColumn2 order by name asc");
       String ascExpected = "id,name\n" +
           "-------------------------------\n" +
           "1,111\n" +
@@ -256,7 +256,7 @@ public class TestSortQuery extends QueryTestCaseBase {
       assertEquals(ascExpected, resultSetToString(res));
       res.close();
 
-      res = executeString("select * from table11 order by name desc");
+      res = executeString("select * from testSortOnNullColumn2 order by name desc");
       String descExpected = "id,name\n" +
           "-------------------------------\n" +
           "2,null\n" +
@@ -266,7 +266,7 @@ public class TestSortQuery extends QueryTestCaseBase {
       assertEquals(descExpected, resultSetToString(res));
       res.close();
     } finally {
-      executeString("DROP TABLE table11 PURGE");
+      executeString("DROP TABLE testSortOnNullColumn2 PURGE");
     }
   }
 
@@ -280,10 +280,10 @@ public class TestSortQuery extends QueryTestCaseBase {
     schema.addColumn("id", Type.INT4);
     schema.addColumn("name", Type.TEXT);
     String[] data = new String[]{ "1|111", "2|\\N", "3|333" };
-    TajoTestingCluster.createTable("table11", schema, tableOptions, data, 1);
+    TajoTestingCluster.createTable("testSortOnNullColumn3".toLowerCase(), schema, tableOptions, data, 1);
 
     try {
-      ResultSet res = executeString("select * from table11 order by name null first");
+      ResultSet res = executeString("select * from testSortOnNullColumn3 order by name null first");
       String ascExpected = "id,name\n" +
           "-------------------------------\n" +
           "2,null\n" +
@@ -294,7 +294,7 @@ public class TestSortQuery extends QueryTestCaseBase {
       res.close();
 
     } finally {
-      executeString("DROP TABLE table11 PURGE");
+      executeString("DROP TABLE testSortOnNullColumn3 PURGE");
     }
   }
 


Mime
View raw message