You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@tajo.apache.org by ji...@apache.org on 2015/05/19 04:07:44 UTC
[19/47] tajo git commit: TAJO-1577: Add test cases to verify join
plans. (jihoon)
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin2.result
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin2.result b/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin2.result
deleted file mode 100644
index c83d6d6..0000000
--- a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin2.result
+++ /dev/null
@@ -1,27 +0,0 @@
-n_name,r_name
--------------------------------
-ALGERIA,AFRICA
-ARGENTINA,AMERICA
-BRAZIL,AMERICA
-CANADA,AMERICA
-CHINA,ASIA
-EGYPT,MIDDLE EAST
-ETHIOPIA,AFRICA
-FRANCE,EUROPE
-GERMANY,EUROPE
-INDIA,ASIA
-INDONESIA,ASIA
-IRAN,MIDDLE EAST
-IRAQ,MIDDLE EAST
-JAPAN,ASIA
-JORDAN,MIDDLE EAST
-KENYA,AFRICA
-MOROCCO,AFRICA
-MOZAMBIQUE,AFRICA
-PERU,AMERICA
-ROMANIA,EUROPE
-RUSSIA,EUROPE
-SAUDI ARABIA,MIDDLE EAST
-UNITED KINGDOM,EUROPE
-UNITED STATES,AMERICA
-VIETNAM,ASIA
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin3.result
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin3.result b/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin3.result
deleted file mode 100644
index e559818..0000000
--- a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin3.result
+++ /dev/null
@@ -1,27 +0,0 @@
-n_name,r_name,p1,p2
--------------------------------
-ALGERIA,AFRICA,1,1
-ARGENTINA,AMERICA,2,2
-BRAZIL,AMERICA,3,2
-CANADA,AMERICA,4,2
-CHINA,ASIA,19,3
-EGYPT,MIDDLE EAST,5,5
-ETHIOPIA,AFRICA,6,1
-FRANCE,EUROPE,7,4
-GERMANY,EUROPE,8,4
-INDIA,ASIA,9,3
-INDONESIA,ASIA,10,3
-IRAN,MIDDLE EAST,11,5
-IRAQ,MIDDLE EAST,12,5
-JAPAN,ASIA,13,3
-JORDAN,MIDDLE EAST,14,5
-KENYA,AFRICA,15,1
-MOROCCO,AFRICA,16,1
-MOZAMBIQUE,AFRICA,17,1
-PERU,AMERICA,18,2
-ROMANIA,EUROPE,20,4
-RUSSIA,EUROPE,23,4
-SAUDI ARABIA,MIDDLE EAST,21,5
-UNITED KINGDOM,EUROPE,24,4
-UNITED STATES,AMERICA,25,2
-VIETNAM,ASIA,22,3
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin4.result
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin4.result b/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin4.result
deleted file mode 100644
index 90df873..0000000
--- a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin4.result
+++ /dev/null
@@ -1,27 +0,0 @@
-n_name,r_name,?plus
--------------------------------
-ALGERIA,AFRICA,0
-ARGENTINA,AMERICA,2
-BRAZIL,AMERICA,3
-CANADA,AMERICA,4
-CHINA,ASIA,20
-EGYPT,MIDDLE EAST,8
-ETHIOPIA,AFRICA,5
-FRANCE,EUROPE,9
-GERMANY,EUROPE,10
-INDIA,ASIA,10
-INDONESIA,ASIA,11
-IRAN,MIDDLE EAST,14
-IRAQ,MIDDLE EAST,15
-JAPAN,ASIA,14
-JORDAN,MIDDLE EAST,17
-KENYA,AFRICA,14
-MOROCCO,AFRICA,15
-MOZAMBIQUE,AFRICA,16
-PERU,AMERICA,18
-ROMANIA,EUROPE,22
-RUSSIA,EUROPE,25
-SAUDI ARABIA,MIDDLE EAST,24
-UNITED KINGDOM,EUROPE,26
-UNITED STATES,AMERICA,25
-VIETNAM,ASIA,23
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin5.result
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin5.result b/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin5.result
deleted file mode 100644
index ea3b0ff..0000000
--- a/tajo-core/src/test/resources/results/TestJoinQuery/testWhereClauseJoin5.result
+++ /dev/null
@@ -1,5 +0,0 @@
-s_acctbal,s_name,p_partkey
--------------------------------
-4032.68,Supplier#000000002,1
-4192.4,Supplier#000000003,2
-4641.08,Supplier#000000004,3
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash.plan
new file mode 100644
index 0000000..668ddab
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash.plan
@@ -0,0 +1,98 @@
+explain
+-------------------------------
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000005)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000003
+2: eb_0000000000000_0000_000004
+3: eb_0000000000000_0000_000005
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 4 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: type=Broadcast, tables=default.region
+ 1: type=Broadcast, tables=default.nation
+ 2: type=Broadcast, tables=default.supplier
+
+SORT(11)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 4 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000003
+
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(12) on eb_0000000000000_0000_000003
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash_NoBroadcast.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash_NoBroadcast.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash_NoBroadcast.plan
new file mode 100644
index 0000000..e504af3
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Hash_NoBroadcast.plan
@@ -0,0 +1,153 @@
+explain
+-------------------------------
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000007)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000007
+ |-eb_0000000000000_0000_000006
+ |-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+ |-eb_0000000000000_0000_000002
+ |-eb_0000000000000_0000_000001
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000001
+2: eb_0000000000000_0000_000002
+3: eb_0000000000000_0000_000003
+4: eb_0000000000000_0000_000004
+5: eb_0000000000000_0000_000005
+6: eb_0000000000000_0000_000006
+7: eb_0000000000000_0000_000007
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000001 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000002 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.nation.n_regionkey (INT4), num=32)
+
+SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.nation.n_regionkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+
+JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(12) on eb_0000000000000_0000_000002
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(11) on eb_0000000000000_0000_000001
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+SORT(15)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(14) on eb_0000000000000_0000_000004
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(13) on eb_0000000000000_0000_000003
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000006 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000005
+
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(16) on eb_0000000000000_0000_000005
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000007 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort.plan
new file mode 100644
index 0000000..668ddab
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort.plan
@@ -0,0 +1,98 @@
+explain
+-------------------------------
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000005)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000003
+2: eb_0000000000000_0000_000004
+3: eb_0000000000000_0000_000005
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 4 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: type=Broadcast, tables=default.region
+ 1: type=Broadcast, tables=default.nation
+ 2: type=Broadcast, tables=default.supplier
+
+SORT(11)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 4 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000003
+
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(12) on eb_0000000000000_0000_000003
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort_NoBroadcast.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort_NoBroadcast.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort_NoBroadcast.plan
new file mode 100644
index 0000000..e504af3
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.Sort_NoBroadcast.plan
@@ -0,0 +1,153 @@
+explain
+-------------------------------
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000007)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000007
+ |-eb_0000000000000_0000_000006
+ |-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+ |-eb_0000000000000_0000_000002
+ |-eb_0000000000000_0000_000001
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000001
+2: eb_0000000000000_0000_000002
+3: eb_0000000000000_0000_000003
+4: eb_0000000000000_0000_000004
+5: eb_0000000000000_0000_000005
+6: eb_0000000000000_0000_000006
+7: eb_0000000000000_0000_000007
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000001 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000002 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.nation.n_regionkey (INT4), num=32)
+
+SCAN(1) on default.nation
+ => target list: default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.nation.n_regionkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+
+JOIN(8)(INNER)
+ => Join Cond: default.nation.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.nation.n_nationkey (INT4), default.region.r_name (TEXT)
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(12) on eb_0000000000000_0000_000002
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(2) default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ SCAN(11) on eb_0000000000000_0000_000001
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+SCAN(3) on default.supplier
+ => target list: default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+SORT(15)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(9)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: CASE WHEN default.supplier.s_name (TEXT) IS NULL THEN N/O ELSE default.supplier.s_name (TEXT) END as s1, default.region.r_name (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.nation.n_nationkey (INT4), default.region.r_name (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(14) on eb_0000000000000_0000_000004
+ => out schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ => in schema: {(2) default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4)}
+ SCAN(13) on eb_0000000000000_0000_000003
+ => out schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+ => in schema: {(2) default.nation.n_nationkey (INT4), default.region.r_name (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000006 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000005
+
+SORT(5)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(16) on eb_0000000000000_0000_000005
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000007 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.result
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.result b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.result
new file mode 100644
index 0000000..81b71b1
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen.result
@@ -0,0 +1,27 @@
+r_name,s1
+-------------------------------
+AFRICA,N/O
+AFRICA,N/O
+AFRICA,N/O
+AFRICA,Supplier#000000002
+AFRICA,Supplier#000000004
+AMERICA,N/O
+AMERICA,N/O
+AMERICA,N/O
+AMERICA,N/O
+AMERICA,Supplier#000000003
+ASIA,N/O
+ASIA,N/O
+ASIA,N/O
+ASIA,N/O
+ASIA,N/O
+EUROPE,N/O
+EUROPE,N/O
+EUROPE,N/O
+EUROPE,N/O
+EUROPE,N/O
+MIDDLE EAST,N/O
+MIDDLE EAST,N/O
+MIDDLE EAST,N/O
+MIDDLE EAST,N/O
+MIDDLE EAST,N/O
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash.plan
new file mode 100644
index 0000000..483457a
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash.plan
@@ -0,0 +1,138 @@
+explain
+-------------------------------
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) default.t.n_regionkey (INT4), s1 (TEXT)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000007)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000007
+ |-eb_0000000000000_0000_000006
+ |-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000003
+2: eb_0000000000000_0000_000004
+3: eb_0000000000000_0000_000005
+4: eb_0000000000000_0000_000006
+5: eb_0000000000000_0000_000007
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+
+[Enforcers]
+ 0: type=Broadcast, tables=default.supplier
+
+TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ PROJECTION(4)
+ => Targets: default.nation.n_nationkey (INT4), default.nation.n_name (TEXT), default.nation.n_regionkey (INT4), default.nation.n_comment (TEXT), default.supplier.s_suppkey (INT4), default.supplier.s_name (TEXT), default.supplier.s_address (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_acctbal (FLOAT8), default.supplier.s_comment (TEXT)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+SORT(15)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(14) on eb_0000000000000_0000_000004
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(13) on eb_0000000000000_0000_000003
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000006 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000005
+
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(16) on eb_0000000000000_0000_000005
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000007 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash_NoBroadcast.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash_NoBroadcast.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash_NoBroadcast.plan
new file mode 100644
index 0000000..af9b3a5
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Hash_NoBroadcast.plan
@@ -0,0 +1,165 @@
+explain
+-------------------------------
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) default.t.n_regionkey (INT4), s1 (TEXT)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000007)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000007
+ |-eb_0000000000000_0000_000006
+ |-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+ |-eb_0000000000000_0000_000002
+ |-eb_0000000000000_0000_000001
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000001
+2: eb_0000000000000_0000_000002
+3: eb_0000000000000_0000_000003
+4: eb_0000000000000_0000_000004
+5: eb_0000000000000_0000_000005
+6: eb_0000000000000_0000_000006
+7: eb_0000000000000_0000_000007
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000001 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+
+SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000002 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 1 => 3 (type=HASH_SHUFFLE, key=default.nation.n_nationkey (INT4), num=32)
+[q_0000000000000_0000] 2 => 3 (type=HASH_SHUFFLE, key=default.supplier.s_nationkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+
+TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ PROJECTION(4)
+ => Targets: default.nation.n_nationkey (INT4), default.nation.n_name (TEXT), default.nation.n_regionkey (INT4), default.nation.n_comment (TEXT), default.supplier.s_suppkey (INT4), default.supplier.s_name (TEXT), default.supplier.s_address (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_acctbal (FLOAT8), default.supplier.s_comment (TEXT)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(14) on eb_0000000000000_0000_000002
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(13) on eb_0000000000000_0000_000001
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+SORT(17)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(16) on eb_0000000000000_0000_000004
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(15) on eb_0000000000000_0000_000003
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000006 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000005
+
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(18) on eb_0000000000000_0000_000005
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000007 [TERMINAL]
+=======================================================
http://git-wip-us.apache.org/repos/asf/tajo/blob/4b1b7799/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Sort.plan
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Sort.plan b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Sort.plan
new file mode 100644
index 0000000..483457a
--- /dev/null
+++ b/tajo-core/src/test/resources/results/TestMultipleJoinTypes/testComplexJoinsWithCaseWhen2.Sort.plan
@@ -0,0 +1,138 @@
+explain
+-------------------------------
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) default.t.n_regionkey (INT4), s1 (TEXT)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+explain
+-------------------------------
+-------------------------------------------------------------------------------
+Execution Block Graph (TERMINAL - eb_0000000000000_0000_000007)
+-------------------------------------------------------------------------------
+|-eb_0000000000000_0000_000007
+ |-eb_0000000000000_0000_000006
+ |-eb_0000000000000_0000_000005
+ |-eb_0000000000000_0000_000004
+ |-eb_0000000000000_0000_000003
+-------------------------------------------------------------------------------
+Order of Execution
+-------------------------------------------------------------------------------
+1: eb_0000000000000_0000_000003
+2: eb_0000000000000_0000_000004
+3: eb_0000000000000_0000_000005
+4: eb_0000000000000_0000_000006
+5: eb_0000000000000_0000_000007
+-------------------------------------------------------------------------------
+
+=======================================================
+Block Id: eb_0000000000000_0000_000003 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+
+[Enforcers]
+ 0: type=Broadcast, tables=default.supplier
+
+TABLE_SUBQUERY(5) as default.t
+ => Targets: CASE WHEN default.t.s_name (TEXT) IS NULL THEN N/O ELSE default.t.s_name (TEXT) END as s1, default.t.n_regionkey (INT4)
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(11) default.t.n_comment (TEXT), default.t.n_name (TEXT), default.t.n_nationkey (INT4), default.t.n_regionkey (INT4), default.t.s_acctbal (FLOAT8), default.t.s_address (TEXT), default.t.s_comment (TEXT), default.t.s_name (TEXT), default.t.s_nationkey (INT4), default.t.s_phone (TEXT), default.t.s_suppkey (INT4)}
+ PROJECTION(4)
+ => Targets: default.nation.n_nationkey (INT4), default.nation.n_name (TEXT), default.nation.n_regionkey (INT4), default.nation.n_comment (TEXT), default.supplier.s_suppkey (INT4), default.supplier.s_name (TEXT), default.supplier.s_address (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_acctbal (FLOAT8), default.supplier.s_comment (TEXT)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ JOIN(10)(LEFT_OUTER)
+ => Join Cond: default.supplier.s_nationkey (INT4) = default.nation.n_nationkey (INT4)
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(11) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4), default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(2) on default.supplier
+ => target list: default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)
+ => out schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ => in schema: {(7) default.supplier.s_acctbal (FLOAT8), default.supplier.s_address (TEXT), default.supplier.s_comment (TEXT), default.supplier.s_name (TEXT), default.supplier.s_nationkey (INT4), default.supplier.s_phone (TEXT), default.supplier.s_suppkey (INT4)}
+ SCAN(1) on default.nation
+ => target list: default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)
+ => out schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+ => in schema: {(4) default.nation.n_comment (TEXT), default.nation.n_name (TEXT), default.nation.n_nationkey (INT4), default.nation.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000004 [LEAF]
+=======================================================
+
+[Outgoing]
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+SCAN(0) on default.region
+ => target list: default.region.r_name (TEXT), default.region.r_regionkey (INT4)
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(3) default.region.r_comment (TEXT), default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000005 [INTERMEDIATE]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 3 => 5 (type=HASH_SHUFFLE, key=default.t.n_regionkey (INT4), num=32)
+[q_0000000000000_0000] 4 => 5 (type=HASH_SHUFFLE, key=default.region.r_regionkey (INT4), num=32)
+
+[Outgoing]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+SORT(15)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ JOIN(11)(INNER)
+ => Join Cond: default.t.n_regionkey (INT4) = default.region.r_regionkey (INT4)
+ => target list: default.region.r_name (TEXT), s1 (TEXT)
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(4) default.region.r_name (TEXT), default.region.r_regionkey (INT4), default.t.n_regionkey (INT4), s1 (TEXT)}
+ SCAN(14) on eb_0000000000000_0000_000004
+ => out schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ => in schema: {(2) default.region.r_name (TEXT), default.region.r_regionkey (INT4)}
+ SCAN(13) on eb_0000000000000_0000_000003
+ => out schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+ => in schema: {(2) s1 (TEXT), default.t.n_regionkey (INT4)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000006 [ROOT]
+=======================================================
+
+[Incoming]
+[q_0000000000000_0000] 5 => 6 (type=RANGE_SHUFFLE, key=default.region.r_name (TEXT), s1 (TEXT), num=32)
+
+[Enforcers]
+ 0: sorted input=eb_0000000000000_0000_000005
+
+SORT(7)
+ => Sort Keys: default.region.r_name (TEXT) (asc),s1 (TEXT) (asc)
+ SCAN(16) on eb_0000000000000_0000_000005
+ => out schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+ => in schema: {(2) default.region.r_name (TEXT), s1 (TEXT)}
+
+=======================================================
+Block Id: eb_0000000000000_0000_000007 [TERMINAL]
+=======================================================