phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maryann...@apache.org
Subject [3/3] git commit: PHOENIX-167 Support semi/anti-joins
Date Mon, 06 Oct 2014 17:56:15 GMT
PHOENIX-167 Support semi/anti-joins


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/5effbbca
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/5effbbca
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/5effbbca

Branch: refs/heads/3.0
Commit: 5effbbca0fd904168edc9d5a35aaf847cfb68dc6
Parents: 006bce1
Author: maryannxue <maryannxue@apache.org>
Authored: Mon Oct 6 13:55:56 2014 -0400
Committer: maryannxue <maryannxue@apache.org>
Committed: Mon Oct 6 13:55:56 2014 -0400

----------------------------------------------------------------------
 .../org/apache/phoenix/end2end/HashJoinIT.java  | 338 ++-------
 .../org/apache/phoenix/end2end/SubqueryIT.java  | 750 +++++++++++++++++++
 phoenix-core/src/main/antlr3/PhoenixSQL.g       |   2 +-
 .../phoenix/compile/ExpressionCompiler.java     |  25 +-
 .../apache/phoenix/compile/JoinCompiler.java    | 135 ++--
 .../apache/phoenix/compile/QueryCompiler.java   |  37 +-
 .../phoenix/compile/StatementNormalizer.java    |   3 +-
 .../phoenix/compile/SubqueryRewriter.java       | 401 ++++++++++
 .../apache/phoenix/compile/UpsertCompiler.java  |   5 +
 .../apache/phoenix/compile/WhereOptimizer.java  |  15 +-
 .../coprocessor/HashJoinRegionScanner.java      |  18 +-
 .../phoenix/exception/SQLExceptionCode.java     |   3 +-
 .../apache/phoenix/execute/HashJoinPlan.java    |  44 +-
 .../apache/phoenix/jdbc/PhoenixStatement.java   |   6 +
 .../phoenix/parse/BooleanParseNodeVisitor.java  |  10 +
 .../apache/phoenix/parse/ExistsParseNode.java   |   6 +-
 .../org/apache/phoenix/parse/JoinTableNode.java |  10 +-
 .../apache/phoenix/parse/ParseNodeFactory.java  |  17 +-
 .../apache/phoenix/parse/ParseNodeRewriter.java |  18 +-
 .../apache/phoenix/parse/ParseNodeVisitor.java  |   3 +
 .../StatelessTraverseAllParseNodeVisitor.java   |   9 +
 .../parse/TraverseAllParseNodeVisitor.java      |  39 +
 .../parse/TraverseNoParseNodeVisitor.java       |  10 +
 .../parse/UnsupportedAllParseNodeVisitor.java   |  25 +
 .../phoenix/compile/JoinQueryCompilerTest.java  |  13 +-
 .../java/org/apache/phoenix/query/BaseTest.java |   8 +
 .../java/org/apache/phoenix/util/TestUtil.java  |   3 +
 27 files changed, 1551 insertions(+), 402 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
index 5243a2e..99d601f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
@@ -118,8 +118,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [I.NAME]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
                 /* 
                  * testLeftJoinWithAggregation()
@@ -131,8 +130,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -146,8 +144,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /* 
                  * testRightJoinWithAggregation()
@@ -159,8 +156,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [I.NAME]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
                  * testRightJoinWithAggregation()
@@ -173,8 +169,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinWithWildcard()
@@ -183,8 +178,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY item_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + 
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinPlanWithIndex()
@@ -196,8 +190,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
                 /*
@@ -210,8 +203,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
                 /*
@@ -221,11 +213,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+                "    PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY QUANTITY < 5000\n" +
-                "    BUILD HASH TABLE 1\n" +
+                "    PARALLEL INNER-JOIN TABLE 1\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY item_id IN (O.item_id)",
                 /*
@@ -235,8 +226,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY i1.item_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.item_id)",
@@ -249,8 +239,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.supplier_id)",
                 /*
@@ -262,10 +251,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY order_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
-                "    BUILD HASH TABLE 1\n" +
+                "    PARALLEL INNER-JOIN TABLE 1\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
                 /*
                  * testStarJoin()
@@ -278,11 +266,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    SERVER SORTED BY [O.order_id]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (O.item_id)",
                 /*
@@ -301,16 +287,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
                 "    SERVER SORTED BY [C.customer_id, I.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY order_id != '000000000000003'\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "                    BUILD HASH TABLE 0\n" +
+                "                    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY customer_id IN (O.customer_id)",
                 /* 
@@ -324,8 +307,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [I.NAME]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
                 /* 
                  * testJoinWithSubqueryAndAggregation()
@@ -339,8 +321,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -355,8 +336,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
                 "        CLIENT MERGE SORT",
@@ -372,8 +352,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [O.Q DESC, I.IID]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
                 "        CLIENT MERGE SORT",
@@ -399,16 +378,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
                 "    SERVER SORTED BY [C.CID, QO.INAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY order_id != '000000000000003'\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "                    BUILD HASH TABLE 0\n" +
+                "                    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinWithLimit()
@@ -421,10 +397,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY PageFilter 4\n" +
                 "    SERVER 4 ROW LIMIT\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+                "    PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "    JOIN-SCANNER 4 ROW LIMIT",
                 /*
@@ -436,10 +411,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+                "    PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY supplier_id BETWEEN MIN/MAX OF (I.supplier_id)\n" +
                 "    JOIN-SCANNER 4 ROW LIMIT",
@@ -451,8 +425,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT",
                 /*
@@ -463,8 +436,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
                 "    DYNAMIC SERVER FILTER BY COL0 IN (RHS.COL2)",
@@ -476,8 +448,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
                 "    DYNAMIC SERVER FILTER BY (COL0, COL1) IN ((RHS.COL1, RHS.COL2))",
@@ -498,8 +469,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [I.0:NAME]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -512,8 +482,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.:item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -527,8 +496,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /* 
                  * testRightJoinWithAggregation()
@@ -540,8 +508,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.0:NAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
                  * testRightJoinWithAggregation()
@@ -554,8 +521,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinWithWildcard()
@@ -564,8 +530,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY item_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinPlanWithIndex()
@@ -577,8 +542,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
                 /*
                  * testJoinPlanWithIndex()
@@ -589,8 +553,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *         AND (supp.name = 'S1' OR supp.name = 'S5')
                  */
                 "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
                 /*
                  * testJoinWithSkipMergeOptimization()
@@ -599,11 +562,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+                "    PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY QUANTITY < 5000\n" +
-                "    BUILD HASH TABLE 1\n" +
+                "    PARALLEL INNER-JOIN TABLE 1\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier",
                 /*
                  * testSelfJoin()
@@ -612,8 +574,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY i1.item_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.:item_id)",
@@ -627,8 +588,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [I1.0:NAME, I2.0:NAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
                 /*
                  * testStarJoin()
@@ -639,10 +599,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY order_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
-                "    BUILD HASH TABLE 1\n" +
+                "    PARALLEL INNER-JOIN TABLE 1\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /*
@@ -657,11 +616,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [O.order_id]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer",
                 /*
                  * testSubJoin()
@@ -679,16 +636,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
                 "    SERVER SORTED BY [C.customer_id, I.0:NAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY order_id != '000000000000003'\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "                    BUILD HASH TABLE 0\n" +
+                "                    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY customer_id IN (O.customer_id)",
                 /* 
@@ -702,8 +656,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [I.NAME]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -718,8 +671,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
                 /* 
@@ -734,8 +686,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
                 "        CLIENT MERGE SORT",
@@ -751,8 +702,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "    SERVER SORTED BY [O.Q DESC, I.IID]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
                 "        CLIENT MERGE SORT",
@@ -778,16 +728,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
                 "    SERVER SORTED BY [C.CID, QO.INAME]\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY order_id != '000000000000003'\n" +
-                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "            BUILD HASH TABLE 0\n" +
+                "            PARALLEL INNER-JOIN TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "                    BUILD HASH TABLE 0\n" +
+                "                    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
                  * testJoinWithLimit()
@@ -800,10 +747,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY PageFilter 4\n" +
                 "    SERVER 4 ROW LIMIT\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
-                "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+                "    PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "    JOIN-SCANNER 4 ROW LIMIT",
                 /*
@@ -815,10 +761,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
-                "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+                "    PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "    DYNAMIC SERVER FILTER BY supplier_id BETWEEN MIN/MAX OF (I.0:supplier_id)\n" +
                 "    JOIN-SCANNER 4 ROW LIMIT",
@@ -830,8 +775,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT",
                 /*
@@ -842,8 +786,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
                 "    DYNAMIC SERVER FILTER BY COL0 IN (RHS.COL2)",
@@ -855,8 +798,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-                "    BUILD HASH TABLE 0\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
                 "    DYNAMIC SERVER FILTER BY (COL0, COL1) IN ((RHS.COL1, RHS.COL2))",
@@ -3480,153 +3422,5 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
         }
     }
 
-    @Test
-    public void testNonCorrelatedSubquery() throws Exception {
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        try {
-            String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY \"item_id\"";
-            PreparedStatement statement = conn.prepareStatement(query);
-            ResultSet rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000004");
-            assertEquals(rs.getString(2), "T4");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000005");
-            assertEquals(rs.getString(2), "T5");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "invalid001");
-            assertEquals(rs.getString(2), "INVALID-1");
-
-            assertFalse(rs.next());
-            
-            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY \"item_id\"";
-            statement = conn.prepareStatement(query);
-            rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000006");
-            assertEquals(rs.getString(2), "T6");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "invalid001");
-            assertEquals(rs.getString(2), "INVALID-1");
-
-            assertFalse(rs.next());
-            
-            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
-            statement = conn.prepareStatement(query);
-            rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000001");
-            assertEquals(rs.getString(2), "T1");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000002");
-            assertEquals(rs.getString(2), "T2");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000003");
-            assertEquals(rs.getString(2), "T3");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000004");
-            assertEquals(rs.getString(2), "T4");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000005");
-            assertEquals(rs.getString(2), "T5");
-
-            assertFalse(rs.next());
-            
-            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
-            statement = conn.prepareStatement(query);
-            rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000001");
-            assertEquals(rs.getString(2), "T1");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000002");
-            assertEquals(rs.getString(2), "T2");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000003");
-            assertEquals(rs.getString(2), "T3");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000004");
-            assertEquals(rs.getString(2), "T4");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000005");
-            assertEquals(rs.getString(2), "T5");
-
-            assertFalse(rs.next());
-        } finally {
-            conn.close();
-        }
-    }
-    
-    @Test
-    public void testNonCorrelatedSubqueryWithRowConstructor() throws Exception {
-        String tempItemJoinTable = "TEMP_ITEM_JOIN_TABLE";
-        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        Connection conn = DriverManager.getConnection(getUrl(), props);
-        try {
-            conn.createStatement().execute("CREATE TABLE " + tempItemJoinTable 
-                    + "   (item_id varchar(10) NOT NULL, " 
-                    + "    item_name varchar NOT NULL, "
-                    + "    co_item_id varchar(10), "
-                    + "    co_item_name varchar "
-                    + "   CONSTRAINT pk PRIMARY KEY (item_id, item_name)) " 
-                    + "   SALT_BUCKETS=4");
-            
-            PreparedStatement upsertStmt = conn.prepareStatement(
-                    "upsert into " + tempItemJoinTable + "(item_id, item_name, co_item_id, co_item_name) " + "values (?, ?, ?, ?)");
-            upsertStmt.setString(1, "0000000001");
-            upsertStmt.setString(2, "T1");
-            upsertStmt.setString(3, "0000000002");
-            upsertStmt.setString(4, "T3");
-            upsertStmt.execute();
-            upsertStmt.setString(1, "0000000004");
-            upsertStmt.setString(2, "T4");
-            upsertStmt.setString(3, "0000000003");
-            upsertStmt.setString(4, "T3");
-            upsertStmt.execute();
-            upsertStmt.setString(1, "0000000003");
-            upsertStmt.setString(2, "T4");
-            upsertStmt.setString(3, "0000000005");
-            upsertStmt.setString(4, "T5");
-            upsertStmt.execute();
-            upsertStmt.setString(1, "0000000006");
-            upsertStmt.setString(2, "T6");
-            upsertStmt.setString(3, "0000000001");
-            upsertStmt.setString(4, "T1");
-            upsertStmt.execute();
-            conn.commit();
-            
-            String query = "SELECT * FROM " + tempItemJoinTable + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
-            PreparedStatement statement = conn.prepareStatement(query);
-            ResultSet rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000003");
-            assertEquals(rs.getString(2), "T4");
-            assertEquals(rs.getString(3), "0000000005");
-            assertEquals(rs.getString(4), "T5");
-
-            assertFalse(rs.next());
-            
-            query = "SELECT * FROM " + tempItemJoinTable + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))"
-                    + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))";
-            statement = conn.prepareStatement(query);
-            rs = statement.executeQuery();
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000004");
-            assertEquals(rs.getString(2), "T4");
-            assertEquals(rs.getString(3), "0000000003");
-            assertEquals(rs.getString(4), "T3");
-            assertTrue (rs.next());
-            assertEquals(rs.getString(1), "0000000006");
-            assertEquals(rs.getString(2), "T6");
-            assertEquals(rs.getString(3), "0000000001");
-            assertEquals(rs.getString(4), "T1");
-
-            assertFalse(rs.next());
-        } finally {
-            conn.close();
-        }
-    }
-
 }
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
new file mode 100644
index 0000000..6b0593e
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
@@ -0,0 +1,750 @@
+/*
+ * 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.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SCHEMA;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+import java.util.regex.Pattern;
+
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.Before;
+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 org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+@Category(HBaseManagedTimeTest.class)
+@RunWith(Parameterized.class)
+public class SubqueryIT extends BaseHBaseManagedTimeIT {
+    
+    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+    private String[] indexDDL;
+    private String[] plans;
+    
+    public SubqueryIT(String[] indexDDL, String[] plans) {
+        this.indexDDL = indexDDL;
+        this.plans = plans;
+    }
+    
+    @BeforeClass
+    @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class)
+    public static void doSetup() throws Exception {
+        Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+        // Forces server cache to be used
+        props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2));
+        // Must update config before starting server
+        setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+    }
+    
+    @Before
+    public void initTable() throws Exception {
+        initTableValues();
+        if (indexDDL != null && indexDDL.length > 0) {
+            Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+            Connection conn = DriverManager.getConnection(getUrl(), props);
+            for (String ddl : indexDDL) {
+                try {
+                    conn.createStatement().execute(ddl);
+                } catch (TableAlreadyExistsException e) {
+                }
+            }
+            conn.close();
+        }
+    }
+    
+    @Parameters
+    public static Collection<Object> data() {
+        List<Object> testCases = Lists.newArrayList();
+        testCases.add(new String[][] {
+                {}, {
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER SORTED BY \\[I.NAME\\]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+                "    SKIP-SCAN-JOIN TABLE 1\n" +
+                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " \\['000000000000001'\\] - \\[\\*\\]\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "        CLIENT MERGE SORT\n" +
+                "    DYNAMIC SERVER FILTER BY item_id IN \\(\\$\\d+.\\$\\d+\\)",
+                
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER SORTED BY [I.NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (SKIP MERGE)\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+                "        CLIENT MERGE SORT",
+                
+                "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id, NAME\\]\n" +
+                "        CLIENT MERGE SORT\n" +
+                "            PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "                    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "                CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 1\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id, NAME\\]\n" +
+                "        CLIENT MERGE SORT\n" +
+                "            PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "                    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "                CLIENT MERGE SORT\n" +
+                "            DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF \\(\\$\\d+.\\$\\d+\\)\n" +
+                "    AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
+                
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER SORTED BY [NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL ANTI-JOIN TABLE 0 (SKIP MERGE)\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+                "        CLIENT MERGE SORT",
+                }});
+        testCases.add(new String[][] {
+                {
+                "CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+                "CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+                "CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+                }, {
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+                "    PARALLEL INNER-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
+                "    PARALLEL SEMI-JOIN TABLE 1 \\(SKIP MERGE\\)\n" +
+                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " \\['000000000000001'\\] - \\[\\*\\]\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "        CLIENT MERGE SORT",
+                
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
+                "    SERVER SORTED BY [I.0:NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+                "    PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (SKIP MERGE)\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+                "        CLIENT MERGE SORT",
+                
+                "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+                "            SERVER FILTER BY FIRST KEY ONLY\n" +
+                "            SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+                "        CLIENT MERGE SORT\n" +
+                "            PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "                    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "                CLIENT MERGE SORT\n" +
+                "    PARALLEL LEFT-JOIN TABLE 1\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+                "            SERVER FILTER BY FIRST KEY ONLY\n" +
+                "            SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+                "        CLIENT MERGE SORT\n" +
+                "            PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "                    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+                "                CLIENT MERGE SORT\n" +
+                "    AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
+                
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    PARALLEL ANTI-JOIN TABLE 0 (SKIP MERGE)\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+                "        CLIENT MERGE SORT",
+                }});
+        return testCases;
+    }
+    
+    
+    protected void initTableValues() throws Exception {
+        ensureTableCreated(getUrl(), JOIN_CUSTOMER_TABLE_FULL_NAME);
+        ensureTableCreated(getUrl(), JOIN_ITEM_TABLE_FULL_NAME);
+        ensureTableCreated(getUrl(), JOIN_SUPPLIER_TABLE_FULL_NAME);
+        ensureTableCreated(getUrl(), JOIN_ORDER_TABLE_FULL_NAME);
+        ensureTableCreated(getUrl(), JOIN_COITEM_TABLE_FULL_NAME);
+        
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE my.seq");
+            // Insert into customer table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + JOIN_CUSTOMER_TABLE_FULL_NAME +
+                    "   (\"customer_id\", " +
+                    "    NAME, " +
+                    "    PHONE, " +
+                    "    ADDRESS, " +
+                    "    LOC_ID, " +
+                    "    DATE) " +
+                    "values (?, ?, ?, ?, ?, ?)");
+            stmt.setString(1, "0000000001");
+            stmt.setString(2, "C1");
+            stmt.setString(3, "999-999-1111");
+            stmt.setString(4, "101 XXX Street");
+            stmt.setString(5, "10001");
+            stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+            stmt.execute();
+                
+            stmt.setString(1, "0000000002");
+            stmt.setString(2, "C2");
+            stmt.setString(3, "999-999-2222");
+            stmt.setString(4, "202 XXX Street");
+            stmt.setString(5, null);
+            stmt.setDate(6, new Date(format.parse("2013-11-25 16:45:07").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "0000000003");
+            stmt.setString(2, "C3");
+            stmt.setString(3, "999-999-3333");
+            stmt.setString(4, "303 XXX Street");
+            stmt.setString(5, null);
+            stmt.setDate(6, new Date(format.parse("2013-11-25 10:06:29").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "0000000004");
+            stmt.setString(2, "C4");
+            stmt.setString(3, "999-999-4444");
+            stmt.setString(4, "404 XXX Street");
+            stmt.setString(5, "10004");
+            stmt.setDate(6, new Date(format.parse("2013-11-22 14:22:56").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "0000000005");
+            stmt.setString(2, "C5");
+            stmt.setString(3, "999-999-5555");
+            stmt.setString(4, "505 XXX Street");
+            stmt.setString(5, "10005");
+            stmt.setDate(6, new Date(format.parse("2013-11-27 09:37:50").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "0000000006");
+            stmt.setString(2, "C6");
+            stmt.setString(3, "999-999-6666");
+            stmt.setString(4, "606 XXX Street");
+            stmt.setString(5, "10001");
+            stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+            stmt.execute();
+            
+            // Insert into item table
+            stmt = conn.prepareStatement(
+                    "upsert into " + JOIN_ITEM_TABLE_FULL_NAME +
+                    "   (\"item_id\", " +
+                    "    NAME, " +
+                    "    PRICE, " +
+                    "    DISCOUNT1, " +
+                    "    DISCOUNT2, " +
+                    "    \"supplier_id\", " +
+                    "    DESCRIPTION) " +
+                    "values (?, ?, ?, ?, ?, ?, ?)");
+            stmt.setString(1, "0000000001");
+            stmt.setString(2, "T1");
+            stmt.setInt(3, 100);
+            stmt.setInt(4, 5);
+            stmt.setInt(5, 10);
+            stmt.setString(6, "0000000001");
+            stmt.setString(7, "Item T1");
+            stmt.execute();
+
+            stmt.setString(1, "0000000002");
+            stmt.setString(2, "T2");
+            stmt.setInt(3, 200);
+            stmt.setInt(4, 5);
+            stmt.setInt(5, 8);
+            stmt.setString(6, "0000000001");
+            stmt.setString(7, "Item T2");
+            stmt.execute();
+
+            stmt.setString(1, "0000000003");
+            stmt.setString(2, "T3");
+            stmt.setInt(3, 300);
+            stmt.setInt(4, 8);
+            stmt.setInt(5, 12);
+            stmt.setString(6, "0000000002");
+            stmt.setString(7, "Item T3");
+            stmt.execute();
+
+            stmt.setString(1, "0000000004");
+            stmt.setString(2, "T4");
+            stmt.setInt(3, 400);
+            stmt.setInt(4, 6);
+            stmt.setInt(5, 10);
+            stmt.setString(6, "0000000002");
+            stmt.setString(7, "Item T4");
+            stmt.execute();
+
+            stmt.setString(1, "0000000005");
+            stmt.setString(2, "T5");
+            stmt.setInt(3, 500);
+            stmt.setInt(4, 8);
+            stmt.setInt(5, 15);
+            stmt.setString(6, "0000000005");
+            stmt.setString(7, "Item T5");
+            stmt.execute();
+
+            stmt.setString(1, "0000000006");
+            stmt.setString(2, "T6");
+            stmt.setInt(3, 600);
+            stmt.setInt(4, 8);
+            stmt.setInt(5, 15);
+            stmt.setString(6, "0000000006");
+            stmt.setString(7, "Item T6");
+            stmt.execute();
+            
+            stmt.setString(1, "invalid001");
+            stmt.setString(2, "INVALID-1");
+            stmt.setInt(3, 0);
+            stmt.setInt(4, 0);
+            stmt.setInt(5, 0);
+            stmt.setString(6, "0000000000");
+            stmt.setString(7, "Invalid item for join test");
+            stmt.execute();
+
+            // Insert into supplier table
+            stmt = conn.prepareStatement(
+                    "upsert into " + JOIN_SUPPLIER_TABLE_FULL_NAME +
+                    "   (\"supplier_id\", " +
+                    "    NAME, " +
+                    "    PHONE, " +
+                    "    ADDRESS, " +
+                    "    LOC_ID) " +
+                    "values (?, ?, ?, ?, ?)");
+            stmt.setString(1, "0000000001");
+            stmt.setString(2, "S1");
+            stmt.setString(3, "888-888-1111");
+            stmt.setString(4, "101 YYY Street");
+            stmt.setString(5, "10001");
+            stmt.execute();
+                
+            stmt.setString(1, "0000000002");
+            stmt.setString(2, "S2");
+            stmt.setString(3, "888-888-2222");
+            stmt.setString(4, "202 YYY Street");
+            stmt.setString(5, "10002");
+            stmt.execute();
+
+            stmt.setString(1, "0000000003");
+            stmt.setString(2, "S3");
+            stmt.setString(3, "888-888-3333");
+            stmt.setString(4, "303 YYY Street");
+            stmt.setString(5, null);
+            stmt.execute();
+
+            stmt.setString(1, "0000000004");
+            stmt.setString(2, "S4");
+            stmt.setString(3, "888-888-4444");
+            stmt.setString(4, "404 YYY Street");
+            stmt.setString(5, null);
+            stmt.execute();
+
+            stmt.setString(1, "0000000005");
+            stmt.setString(2, "S5");
+            stmt.setString(3, "888-888-5555");
+            stmt.setString(4, "505 YYY Street");
+            stmt.setString(5, "10005");
+            stmt.execute();
+
+            stmt.setString(1, "0000000006");
+            stmt.setString(2, "S6");
+            stmt.setString(3, "888-888-6666");
+            stmt.setString(4, "606 YYY Street");
+            stmt.setString(5, "10006");
+            stmt.execute();
+
+            // Insert into order table
+            stmt = conn.prepareStatement(
+                    "upsert into " + JOIN_ORDER_TABLE_FULL_NAME +
+                    "   (\"order_id\", " +
+                    "    \"customer_id\", " +
+                    "    \"item_id\", " +
+                    "    PRICE, " +
+                    "    QUANTITY," +
+                    "    DATE) " +
+                    "values (?, ?, ?, ?, ?, ?)");
+            stmt.setString(1, "000000000000001");
+            stmt.setString(2, "0000000004");
+            stmt.setString(3, "0000000001");
+            stmt.setInt(4, 100);
+            stmt.setInt(5, 1000);
+            stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "000000000000002");
+            stmt.setString(2, "0000000003");
+            stmt.setString(3, "0000000006");
+            stmt.setInt(4, 552);
+            stmt.setInt(5, 2000);
+            stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "000000000000003");
+            stmt.setString(2, "0000000002");
+            stmt.setString(3, "0000000002");
+            stmt.setInt(4, 190);
+            stmt.setInt(5, 3000);
+            stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "000000000000004");
+            stmt.setString(2, "0000000004");
+            stmt.setString(3, "0000000006");
+            stmt.setInt(4, 510);
+            stmt.setInt(5, 4000);
+            stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-26 13:26:04").getTime()));
+            stmt.execute();
+
+            stmt.setString(1, "000000000000005");
+            stmt.setString(2, "0000000005");
+            stmt.setString(3, "0000000003");
+            stmt.setInt(4, 264);
+            stmt.setInt(5, 5000);
+            stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));
+            stmt.execute();
+
+            conn.commit();
+
+            // Insert into coitem table
+            stmt = conn.prepareStatement(
+                    "upsert into " + JOIN_COITEM_TABLE_FULL_NAME + 
+                    "   (item_id, " + 
+                    "    item_name, " + 
+                    "    co_item_id, " + 
+                    "    co_item_name) " + 
+                    "values (?, ?, ?, ?)");
+            stmt.setString(1, "0000000001");
+            stmt.setString(2, "T1");
+            stmt.setString(3, "0000000002");
+            stmt.setString(4, "T3");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000004");
+            stmt.setString(2, "T4");
+            stmt.setString(3, "0000000003");
+            stmt.setString(4, "T3");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000003");
+            stmt.setString(2, "T4");
+            stmt.setString(3, "0000000005");
+            stmt.setString(4, "T5");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000006");
+            stmt.setString(2, "T6");
+            stmt.setString(3, "0000000001");
+            stmt.setString(4, "T1");
+            stmt.execute();
+            
+            conn.commit();
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testNonCorrelatedSubquery() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {            
+            String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "invalid001");
+            assertEquals(rs.getString(2), "INVALID-1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "T6");
+
+            assertFalse(rs.next());
+            
+            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000001");
+            assertEquals(rs.getString(2), "T1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000002");
+            assertEquals(rs.getString(2), "T2");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000005");
+            assertEquals(rs.getString(2), "T5");
+
+            assertFalse(rs.next());
+            
+            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000001");
+            assertEquals(rs.getString(2), "T1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000002");
+            assertEquals(rs.getString(2), "T2");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000005");
+            assertEquals(rs.getString(2), "T5");
+
+            assertFalse(rs.next());
+            
+            query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "T4");
+            assertEquals(rs.getString(3), "0000000005");
+            assertEquals(rs.getString(4), "T5");
+
+            assertFalse(rs.next());
+            
+            query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE EXISTS (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000001");
+            assertEquals(rs.getString(2), "T1");
+            assertEquals(rs.getString(3), "0000000002");
+            assertEquals(rs.getString(4), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "T4");
+            assertEquals(rs.getString(3), "0000000005");
+            assertEquals(rs.getString(4), "T5");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertEquals(rs.getString(3), "0000000003");
+            assertEquals(rs.getString(4), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "T6");
+            assertEquals(rs.getString(3), "0000000001");
+            assertEquals(rs.getString(4), "T1");
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testInSubquery() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000001");
+            assertEquals(rs.getString(2), "T1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000002");
+            assertEquals(rs.getString(2), "T2");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "T6");
+
+            assertFalse(rs.next());
+
+            query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "invalid001");
+            assertEquals(rs.getString(2), "INVALID-1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000005");
+            assertEquals(rs.getString(2), "T5");
+
+            assertFalse(rs.next());
+            
+            query = "SELECT i.\"item_id\", s.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000002");
+            assertEquals(rs.getString(2), "S1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "S2");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "S6");
+
+            assertFalse(rs.next());
+            
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            String plan = QueryUtil.getExplainPlan(rs);
+            assertTrue("\"" + plan + "\" does not match \"" + plans[0] + "\"", Pattern.matches(plans[0], plan));
+            
+            query = "SELECT i.\"item_id\", s.name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s LEFT JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY i.name";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000001");
+            assertEquals(rs.getString(2), "S1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000002");
+            assertEquals(rs.getString(2), "S1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000003");
+            assertEquals(rs.getString(2), "S2");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "S6");
+
+            assertFalse(rs.next());
+            
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+           
+            query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))"
+                    + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertEquals(rs.getString(3), "0000000003");
+            assertEquals(rs.getString(4), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "T6");
+            assertEquals(rs.getString(3), "0000000001");
+            assertEquals(rs.getString(4), "T1");
+
+            assertFalse(rs.next());
+            
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            plan = QueryUtil.getExplainPlan(rs);
+            assertTrue("\"" + plan + "\" does not match \"" + plans[2] + "\"", Pattern.matches(plans[2], plan));
+        } finally {
+            conn.close();
+        }
+    }
+    
+    @Test
+    public void testExistsSubquery() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE NOT EXISTS (SELECT 1 FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "invalid001");
+            assertEquals(rs.getString(2), "INVALID-1");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000005");
+            assertEquals(rs.getString(2), "T5");
+
+            assertFalse(rs.next());
+            
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            assertEquals(plans[3], QueryUtil.getExplainPlan(rs));
+            
+            query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " co WHERE EXISTS (SELECT 1 FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE NOT EXISTS (SELECT 1 FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)"
+                    + " OR EXISTS (SELECT 1 FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000004");
+            assertEquals(rs.getString(2), "T4");
+            assertEquals(rs.getString(3), "0000000003");
+            assertEquals(rs.getString(4), "T3");
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), "0000000006");
+            assertEquals(rs.getString(2), "T6");
+            assertEquals(rs.getString(3), "0000000001");
+            assertEquals(rs.getString(4), "T1");
+
+            assertFalse(rs.next());
+            
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            String plan = QueryUtil.getExplainPlan(rs);
+            assertTrue("\"" + plan + "\" does not match \"" + plans[2] + "\"", Pattern.matches(plans[2], plan));
+        } finally {
+            conn.close();
+        }
+    }
+
+}
+

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/antlr3/PhoenixSQL.g
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/antlr3/PhoenixSQL.g b/phoenix-core/src/main/antlr3/PhoenixSQL.g
index 5e3d8aa..67fd1cc 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -716,7 +716,6 @@ boolean_expression returns [ParseNode ret]
                   |  (IS n=NOT? NULL {$ret = factory.isNull(l,n!=null); } )
                   |  ( n=NOT? ((LIKE r=value_expression {$ret = factory.like(l,r,n!=null,LikeType.CASE_SENSITIVE); } )
                       |        (ILIKE r=value_expression {$ret = factory.like(l,r,n!=null,LikeType.CASE_INSENSITIVE); } )
-                      |        (EXISTS LPAREN r=subquery_expression RPAREN {$ret = factory.exists(l,r,n!=null);} )
                       |        (BETWEEN r1=value_expression AND r2=value_expression {$ret = factory.between(l,r1,r2,n!=null); } )
                       |        ((IN ((r=bind_expression {$ret = factory.inList(Arrays.asList(l,r),n!=null);} )
                                 | (LPAREN r=subquery_expression RPAREN {$ret = factory.in(l,r,n!=null);} )
@@ -724,6 +723,7 @@ boolean_expression returns [ParseNode ret]
                                 )))
                       ))
                    |  { $ret = l; } )
+    |   EXISTS LPAREN s=subquery_expression RPAREN {$ret = factory.exists(s,false);}
     ;
 
 bind_expression  returns [BindParseNode ret]

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index 41f6d83..7f4a7a0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -79,6 +79,7 @@ import org.apache.phoenix.parse.CastParseNode;
 import org.apache.phoenix.parse.ColumnParseNode;
 import org.apache.phoenix.parse.ComparisonParseNode;
 import org.apache.phoenix.parse.DivideParseNode;
+import org.apache.phoenix.parse.ExistsParseNode;
 import org.apache.phoenix.parse.FunctionParseNode;
 import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunctionInfo;
 import org.apache.phoenix.parse.LikeParseNode.LikeType;
@@ -1227,29 +1228,15 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
     }
 
     @Override
-    public boolean visitEnter(InParseNode node) throws SQLException {
+    public boolean visitEnter(ExistsParseNode node) throws SQLException {
         return true;
     }
 
     @Override
-    public Expression visitLeave(InParseNode node, List<Expression> l)
-            throws SQLException {
-        Expression firstChild = l.get(0);
-        LiteralExpression secondChild = (LiteralExpression) l.get(1);
-        ImmutableBytesWritable ptr = context.getTempPtr();
-        ParseNode firstChildNode = node.getChildren().get(0);
-        
-        if (firstChildNode instanceof BindParseNode) {
-            context.getBindManager().addParamMetaData((BindParseNode)firstChildNode, firstChild);
-        }
-        
-        List<Expression> children = Lists.<Expression> newArrayList(firstChild);
-        PhoenixArray array = (PhoenixArray) secondChild.getValue();
-        PDataType type = PDataType.fromTypeId(array.getBaseType());
-        for (Object obj : (Object[]) array.getArray()) {
-            children.add(LiteralExpression.newConstant(obj, type));
-        }
-        return wrapGroupByExpression(InListExpression.create(children, node.isNegate(), ptr));
+    public Expression visitLeave(ExistsParseNode node, List<Expression> l) throws SQLException {
+        LiteralExpression child = (LiteralExpression) l.get(0);
+        PhoenixArray array = (PhoenixArray) child.getValue();
+        return LiteralExpression.newConstant(array.getDimensions() > 0 ^ node.isNegate(), PDataType.BOOLEAN);
     }
 
     @Override


Mime
View raw message