phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maryann...@apache.org
Subject [2/2] git commit: PHOENIX-1168 Support non-correlated sub-queries in where clause having a comparison operator with no modifier or a comparison operator modified by ANY, SOME or ALL
Date Thu, 25 Sep 2014 16:38:24 GMT
PHOENIX-1168 Support non-correlated sub-queries in where clause having a comparison operator with no modifier or a comparison operator modified by ANY, SOME or ALL


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

Branch: refs/heads/4.0
Commit: 81477ee8961ea6b99d981159f06ef644fd2f7dc0
Parents: 349d04f
Author: maryannxue <maryannxue@apache.org>
Authored: Thu Sep 25 12:38:00 2014 -0400
Committer: maryannxue <maryannxue@apache.org>
Committed: Thu Sep 25 12:38:00 2014 -0400

----------------------------------------------------------------------
 .../org/apache/phoenix/end2end/HashJoinIT.java  | 322 +++++++++++++-----
 phoenix-core/src/main/antlr3/PhoenixSQL.g       |   4 +-
 .../phoenix/compile/ExpressionCompiler.java     |  37 ++
 .../apache/phoenix/compile/JoinCompiler.java    |   7 +
 .../apache/phoenix/compile/QueryCompiler.java   |  57 ++--
 .../phoenix/compile/StatementContext.java       |  15 +
 .../phoenix/compile/StatementNormalizer.java    |  13 +
 .../apache/phoenix/compile/WhereCompiler.java   |  46 ++-
 .../phoenix/exception/SQLExceptionCode.java     |   1 +
 .../apache/phoenix/execute/BaseQueryPlan.java   |   2 +-
 .../apache/phoenix/execute/HashJoinPlan.java    | 334 +++++++++++++------
 .../expression/ComparisonExpression.java        |   3 +-
 .../apache/phoenix/parse/ParseNodeFactory.java  |  18 +-
 .../apache/phoenix/parse/ParseNodeRewriter.java |  36 +-
 .../apache/phoenix/parse/ParseNodeVisitor.java  |   7 +-
 .../phoenix/parse/SelectStatementRewriter.java  |  13 +
 .../StatelessTraverseAllParseNodeVisitor.java   |   5 +
 .../apache/phoenix/parse/SubqueryParseNode.java |   8 +-
 .../parse/TraverseAllParseNodeVisitor.java      |  10 +
 .../parse/TraverseNoParseNodeVisitor.java       |  15 +
 .../phoenix/compile/JoinQueryCompilerTest.java  |   4 +-
 .../java/org/apache/phoenix/query/BaseTest.java |  22 +-
 22 files changed, 738 insertions(+), 241 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/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 dcd96d3..ceba009 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
@@ -119,7 +119,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
                 /* 
@@ -132,7 +132,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -147,7 +147,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /* 
@@ -160,7 +160,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -174,7 +174,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -184,7 +184,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -197,7 +197,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
@@ -211,7 +211,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
@@ -222,7 +222,7 @@ 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-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY QUANTITY < 5000\n" +
@@ -236,7 +236,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -250,7 +250,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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)",
@@ -263,7 +263,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY order_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
                 "    BUILD HASH TABLE 1\n" +
@@ -279,10 +279,10 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "            PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH 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)",
@@ -302,15 +302,15 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH 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)",
@@ -325,7 +325,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
                 /* 
@@ -340,7 +340,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -356,7 +356,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -373,7 +373,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -400,15 +400,15 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -422,7 +422,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY PageFilter 4\n" +
                 "    SERVER 4 ROW LIMIT\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
@@ -437,7 +437,7 @@ 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-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
                 "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
@@ -452,7 +452,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT",
@@ -464,7 +464,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -477,7 +477,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -499,7 +499,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -513,7 +513,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -528,7 +528,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /* 
@@ -541,7 +541,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -555,7 +555,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -565,7 +565,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -578,7 +578,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
                 /*
@@ -590,7 +590,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
                 /*
@@ -600,7 +600,7 @@ 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-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY QUANTITY < 5000\n" +
@@ -613,7 +613,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -628,7 +628,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
                 /*
@@ -640,7 +640,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY order_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
                 "    BUILD HASH TABLE 1\n" +
@@ -658,10 +658,10 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "            PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer",
                 /*
@@ -680,15 +680,15 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH 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)",
@@ -703,7 +703,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -719,7 +719,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY",
@@ -735,7 +735,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -752,7 +752,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -779,15 +779,15 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -801,7 +801,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY PageFilter 4\n" +
                 "    SERVER 4 ROW LIMIT\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
                 "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
@@ -816,7 +816,7 @@ 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-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
                 "    BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
@@ -831,7 +831,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT",
@@ -843,7 +843,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -856,7 +856,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -878,7 +878,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -893,7 +893,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -909,7 +909,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /* 
@@ -923,7 +923,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -937,7 +937,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
                 /*
@@ -947,7 +947,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -961,7 +961,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
                 "    SERVER FILTER BY FIRST KEY ONLY\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" +
                 "        CLIENT MERGE SORT",
@@ -975,7 +975,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
                 "CLIENT MERGE SORT\n" + 
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" + 
                 "        CLIENT MERGE SORT",
@@ -987,7 +987,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "CLIENT MERGE SORT\n" + 
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
                 "            SERVER FILTER BY QUANTITY < 5000\n" +
@@ -1002,7 +1002,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n"  +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -1018,7 +1018,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -1032,7 +1032,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  *     ORDER BY order_id
                  */
                 "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -1052,10 +1052,10 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-                "            PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME+" [-32768]\n"+
                 "                CLIENT MERGE SORT\n" +
@@ -1076,16 +1076,16 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
                 "                CLIENT MERGE SORT\n" +
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH 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)",
@@ -1100,7 +1100,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n"+
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -1117,7 +1117,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "            SERVER FILTER BY FIRST KEY ONLY\n" +
@@ -1134,7 +1134,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -1151,7 +1151,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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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" +
@@ -1178,16 +1178,16 @@ 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-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH 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-JOIN 1 HASH TABLES:\n" +
+                "            PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "            BUILD HASH TABLE 0\n" +
                 "                CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +  MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "                    SERVER FILTER BY NAME != 'T3'\n" +
                 "                CLIENT MERGE SORT\n" +      
-                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "                    BUILD HASH TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 /*
@@ -1201,7 +1201,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "    SERVER FILTER BY PageFilter 4\n" +
                 "    SERVER 4 ROW LIMIT\n" +
                 "CLIENT 4 ROW LIMIT\n" +
-                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "        CLIENT MERGE SORT\n" +      
@@ -1217,7 +1217,7 @@ 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-JOIN 2 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -1233,7 +1233,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT",
@@ -1245,7 +1245,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -1258,7 +1258,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                  */
                 "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "CLIENT MERGE SORT\n" +
-                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
                 "    BUILD HASH TABLE 0\n" +
                 "        CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
                 "        CLIENT MERGE SORT\n" +
@@ -3883,5 +3883,153 @@ 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/81477ee8/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 8432f6e..893c3de 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -551,7 +551,7 @@ select_expression returns [SelectStatement ret]
     ;
     
 subquery_expression returns [ParseNode ret]
-    :  s=select_expression {$ret = factory.subquery(s);}
+    :  s=select_expression {$ret = factory.subquery(s, false);}
     ;
     
 // Parse a full select expression structure.
@@ -713,7 +713,7 @@ comparison_op returns [CompareOp ret]
 	;
 	
 boolean_expression returns [ParseNode ret]
-    :   l=value_expression ((op=comparison_op (r=value_expression | ((all=ALL | any=ANY) LPAREN r=value_expression RPAREN)) {$ret = all != null ? factory.wrapInAll(op, l, r) : any != null ? factory.wrapInAny(op, l, r) : factory.comparison(op,l,r); } )
+    :   l=value_expression ((op=comparison_op (r=value_expression | (LPAREN r=subquery_expression RPAREN) | ((all=ALL | any=ANY) LPAREN r=value_expression RPAREN)  | ((all=ALL | any=ANY) LPAREN r=subquery_expression RPAREN)) {$ret = all != null ? factory.wrapInAll(op, l, r) : any != null ? factory.wrapInAny(op, l, r) : factory.comparison(op,l,r); } )
                   |  (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); } )

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/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 cb3bc74..1511539 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
@@ -82,6 +82,7 @@ import org.apache.phoenix.parse.FunctionParseNode;
 import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunctionInfo;
 import org.apache.phoenix.parse.LikeParseNode.LikeType;
 import org.apache.phoenix.parse.InListParseNode;
+import org.apache.phoenix.parse.InParseNode;
 import org.apache.phoenix.parse.IsNullParseNode;
 import org.apache.phoenix.parse.LikeParseNode;
 import org.apache.phoenix.parse.LiteralParseNode;
@@ -93,6 +94,7 @@ import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.RowValueConstructorParseNode;
 import org.apache.phoenix.parse.SequenceValueParseNode;
 import org.apache.phoenix.parse.StringConcatParseNode;
+import org.apache.phoenix.parse.SubqueryParseNode;
 import org.apache.phoenix.parse.SubtractParseNode;
 import org.apache.phoenix.parse.UnsupportedAllParseNodeVisitor;
 import org.apache.phoenix.schema.ColumnFamilyNotFoundException;
@@ -107,6 +109,7 @@ import org.apache.phoenix.schema.PDatum;
 import org.apache.phoenix.schema.PTable;
 import org.apache.phoenix.schema.PTable.IndexType;
 import org.apache.phoenix.schema.PTableType;
+import org.apache.phoenix.schema.PhoenixArray;
 import org.apache.phoenix.schema.RowKeyValueAccessor;
 import org.apache.phoenix.schema.SortOrder;
 import org.apache.phoenix.schema.TableRef;
@@ -115,6 +118,8 @@ import org.apache.phoenix.util.ExpressionUtil;
 import org.apache.phoenix.util.IndexUtil;
 import org.apache.phoenix.util.SchemaUtil;
 
+import com.google.common.collect.Lists;
+
 
 public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expression> {
     private boolean isAggregate;
@@ -1239,4 +1244,36 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
     public boolean visitEnter(ArrayConstructorNode node) throws SQLException {
         return true;
     }
+
+    @Override
+    public boolean visitEnter(InParseNode 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));
+    }
+
+    @Override
+    public Expression visit(SubqueryParseNode node) throws SQLException {
+        Object result = context.getSubqueryResult(node.getSelectNode());
+        return LiteralExpression.newConstant(result);
+    }
 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index 81d169c..41f58e0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
@@ -56,6 +56,7 @@ import org.apache.phoenix.parse.FunctionParseNode;
 import org.apache.phoenix.parse.HintNode;
 import org.apache.phoenix.parse.HintNode.Hint;
 import org.apache.phoenix.parse.InListParseNode;
+import org.apache.phoenix.parse.InParseNode;
 import org.apache.phoenix.parse.IsNullParseNode;
 import org.apache.phoenix.parse.JoinTableNode;
 import org.apache.phoenix.parse.JoinTableNode.JoinType;
@@ -833,6 +834,12 @@ public class JoinCompiler {
                 List<Void> l) throws SQLException {
             return leaveBooleanNode(node, l);
         }
+
+        @Override
+        public Void visitLeave(InParseNode node,
+                List<Void> l) throws SQLException {
+            return leaveBooleanNode(node, l);
+        }
         
         @Override
         public Void visitLeave(IsNullParseNode node, List<Void> l) 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
index 6f9d688..1512d82 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
@@ -21,6 +21,7 @@ import java.sql.SQLException;
 import java.sql.SQLFeatureNotSupportedException;
 import java.util.Collections;
 import java.util.List;
+import java.util.Set;
 
 import org.apache.hadoop.hbase.client.Scan;
 import org.apache.hadoop.hbase.util.Pair;
@@ -33,8 +34,9 @@ import org.apache.phoenix.compile.JoinCompiler.ProjectedPTableWrapper;
 import org.apache.phoenix.compile.JoinCompiler.Table;
 import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
 import org.apache.phoenix.execute.AggregatePlan;
-import org.apache.phoenix.execute.BaseQueryPlan;
 import org.apache.phoenix.execute.HashJoinPlan;
+import org.apache.phoenix.execute.HashJoinPlan.HashSubPlan;
+import org.apache.phoenix.execute.HashJoinPlan.WhereClauseSubPlan;
 import org.apache.phoenix.execute.ScanPlan;
 import org.apache.phoenix.expression.Expression;
 import org.apache.phoenix.expression.RowValueConstructorExpression;
@@ -50,6 +52,7 @@ import org.apache.phoenix.parse.JoinTableNode.JoinType;
 import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.SQLParser;
 import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.parse.SubqueryParseNode;
 import org.apache.phoenix.query.QueryConstants;
 import org.apache.phoenix.schema.AmbiguousColumnException;
 import org.apache.phoenix.schema.ColumnNotFoundException;
@@ -181,15 +184,10 @@ public class QueryCompiler {
             int count = joinSpecs.size();
             ImmutableBytesPtr[] joinIds = new ImmutableBytesPtr[count];
             List<Expression>[] joinExpressions = new List[count];
-            List<Expression>[] hashExpressions = new List[count];
-            Expression[] keyRangeLhsExpressions = new Expression[count];
-            Expression[] keyRangeRhsExpressions = new Expression[count];
-            boolean[] hasFilters = new boolean[count];
             JoinType[] joinTypes = new JoinType[count];
             PTable[] tables = new PTable[count];
             int[] fieldPositions = new int[count];
-            QueryPlan[] joinPlans = new QueryPlan[count];
-            TupleProjector[] clientProjectors = new TupleProjector[count];
+            HashSubPlan[] subPlans = new HashSubPlan[count];
             fieldPositions[0] = projectedTable.getTable().getColumns().size() - projectedTable.getTable().getPKColumns().size();
             boolean forceProjection = table.isSubselect();
             boolean needsProject = forceProjection || asSubquery;
@@ -197,9 +195,9 @@ public class QueryCompiler {
                 JoinSpec joinSpec = joinSpecs.get(i);
                 Scan subScan = ScanUtil.newScan(originalScan);
                 StatementContext subContext = new StatementContext(statement, context.getResolver(), subScan, new SequenceManager(statement));
-                joinPlans[i] = compileJoinQuery(subContext, binds, joinSpec.getJoinTable(), true);
+                QueryPlan joinPlan = compileJoinQuery(subContext, binds, joinSpec.getJoinTable(), true);
                 ColumnResolver resolver = subContext.getResolver();
-                clientProjectors[i] = subContext.getClientTupleProjector();
+                TupleProjector clientProjector = subContext.getClientTupleProjector();
                 boolean hasPostReference = joinSpec.getJoinTable().hasPostReference();
                 if (hasPostReference) {
                     PTableWrapper subProjTable = ((JoinedTableColumnResolver) (resolver)).getPTableWrapper();
@@ -216,29 +214,30 @@ public class QueryCompiler {
                 joinIds[i] = new ImmutableBytesPtr(emptyByteArray); // place-holder
                 Pair<List<Expression>, List<Expression>> joinConditions = joinSpec.compileJoinConditions(context, leftResolver, resolver);
                 joinExpressions[i] = joinConditions.getFirst();
-                hashExpressions[i] = joinConditions.getSecond();
-                Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions[i]);
-                keyRangeLhsExpressions[i] = keyRangeExpressions.getFirst();
-                keyRangeRhsExpressions[i] = keyRangeExpressions.getSecond();
-                hasFilters[i] = joinSpec.getJoinTable().hasFilters();
+                List<Expression> hashExpressions = joinConditions.getSecond();
+                Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions);
+                Expression keyRangeLhsExpression = keyRangeExpressions.getFirst();
+                Expression keyRangeRhsExpression = keyRangeExpressions.getSecond();
+                boolean hasFilters = joinSpec.getJoinTable().hasFilters();
                 joinTypes[i] = joinSpec.getType();
                 if (i < count - 1) {
                     fieldPositions[i + 1] = fieldPositions[i] + (tables[i] == null ? 0 : (tables[i].getColumns().size() - tables[i].getPKColumns().size()));
                 }
+                subPlans[i] = new HashSubPlan(i, joinPlan, hashExpressions, keyRangeLhsExpression, keyRangeRhsExpression, clientProjector, hasFilters);
             }
             if (needsProject) {
                 TupleProjector.serializeProjectorIntoScan(context.getScan(), initialProjectedTable.createTupleProjector());
             }
             context.setCurrentTable(tableRef);
             context.setResolver(needsProject ? projectedTable.createColumnResolver() : joinTable.getOriginalResolver());
-            BaseQueryPlan plan = compileSingleQuery(context, query, binds, asSubquery, joinTable.isAllLeftJoin());
+            QueryPlan plan = compileSingleQuery(context, query, binds, asSubquery, joinTable.isAllLeftJoin());
             Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context);
             Integer limit = null;
             if (query.getLimit() != null && !query.isAggregate() && !query.isDistinct() && query.getOrderBy().isEmpty()) {
                 limit = LimitCompiler.compile(context, query);
             }
             HashJoinInfo joinInfo = new HashJoinInfo(projectedTable.getTable(), joinIds, joinExpressions, joinTypes, starJoinVector, tables, fieldPositions, postJoinFilterExpression, limit, forceProjection);
-            return new HashJoinPlan(joinTable.getStatement(), plan, joinInfo, hashExpressions, keyRangeLhsExpressions, keyRangeRhsExpressions, joinPlans, clientProjectors, hasFilters);
+            return HashJoinPlan.create(joinTable.getStatement(), plan, joinInfo, subPlans);
         }
         
         JoinSpec lastJoinSpec = joinSpecs.get(joinSpecs.size() - 1);
@@ -286,7 +285,7 @@ public class QueryCompiler {
             TupleProjector.serializeProjectorIntoScan(context.getScan(), rhsProjTable.createTupleProjector());
             context.setCurrentTable(rhsTableRef);
             context.setResolver(projectedTable.createColumnResolver());
-            BaseQueryPlan rhsPlan = compileSingleQuery(context, rhs, binds, asSubquery, type == JoinType.Right);
+            QueryPlan rhsPlan = compileSingleQuery(context, rhs, binds, asSubquery, type == JoinType.Right);
             Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context);
             Integer limit = null;
             if (rhs.getLimit() != null && !rhs.isAggregate() && !rhs.isDistinct() && rhs.getOrderBy().isEmpty()) {
@@ -294,7 +293,7 @@ public class QueryCompiler {
             }
             HashJoinInfo joinInfo = new HashJoinInfo(projectedTable.getTable(), joinIds, new List[] {joinExpressions}, new JoinType[] {type == JoinType.Inner ? type : JoinType.Left}, new boolean[] {true}, new PTable[] {lhsProjTable.getTable()}, new int[] {fieldPosition}, postJoinFilterExpression, limit, forceProjection);
             Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, rhsTableRef, type, joinExpressions, hashExpressions);
-            return new HashJoinPlan(joinTable.getStatement(), rhsPlan, joinInfo, new List[] {hashExpressions}, new Expression[] {keyRangeExpressions.getFirst()}, new Expression[] {keyRangeExpressions.getSecond()}, new QueryPlan[] {lhsPlan}, new TupleProjector[] {clientProjector}, new boolean[] {lhsJoin.hasFilters()});
+            return HashJoinPlan.create(joinTable.getStatement(), rhsPlan, joinInfo, new HashSubPlan[] {new HashSubPlan(0, lhsPlan, hashExpressions, keyRangeExpressions.getFirst(), keyRangeExpressions.getSecond(), clientProjector, lhsJoin.hasFilters())});
         }
         
         // Do not support queries like "A right join B left join C" with hash-joins.
@@ -336,7 +335,7 @@ public class QueryCompiler {
         return statement.getConnection().getQueryServices().getOptimizer().optimize(statement, plan);
     }
     
-    protected BaseQueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter) throws SQLException{
+    protected QueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter) throws SQLException{
         PhoenixConnection connection = statement.getConnection();
         ColumnResolver resolver = context.getResolver();
         TableRef tableRef = context.getCurrentTable();
@@ -360,7 +359,7 @@ public class QueryCompiler {
         // Don't pass groupBy when building where clause expression, because we do not want to wrap these
         // expressions as group by key expressions since they're pre, not post filtered.
         context.setResolver(FromCompiler.getResolverForQuery(select, connection));
-        WhereCompiler.compile(context, select, viewWhere);
+        Set<SubqueryParseNode> subqueries = WhereCompiler.compile(context, select, viewWhere);
         context.setResolver(resolver); // recover resolver
         OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit); 
         RowProjector projector = ProjectionCompiler.compile(context, select, groupBy, asSubquery ? Collections.<PDatum>emptyList() : targetColumns);
@@ -375,11 +374,21 @@ public class QueryCompiler {
             }
         }
         ParallelIteratorFactory parallelIteratorFactory = asSubquery ? null : this.parallelIteratorFactory;
-        if (select.isAggregate() || select.isDistinct()) {
-            return new AggregatePlan(context, select, tableRef, projector, limit, orderBy, parallelIteratorFactory, groupBy, having);
-        } else {
-            return new ScanPlan(context, select, tableRef, projector, limit, orderBy, parallelIteratorFactory, allowPageFilter);
+        QueryPlan plan = select.isAggregate() || select.isDistinct() ? 
+                  new AggregatePlan(context, select, tableRef, projector, limit, orderBy, parallelIteratorFactory, groupBy, having)
+                : new ScanPlan(context, select, tableRef, projector, limit, orderBy, parallelIteratorFactory, allowPageFilter);
+        if (!subqueries.isEmpty()) {
+            int count = subqueries.size();
+            WhereClauseSubPlan[] subPlans = new WhereClauseSubPlan[count];
+            int i = 0;
+            for (SubqueryParseNode subqueryNode : subqueries) {
+                SelectStatement stmt = subqueryNode.getSelectNode();
+                subPlans[i++] = new WhereClauseSubPlan(compileSubquery(stmt), stmt, subqueryNode.expectSingleRow());
+            }
+            plan = HashJoinPlan.create(select, plan, null, subPlans);
         }
+        
+        return plan;
     }
 }
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementContext.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementContext.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementContext.java
index 1c75527..56e63ae 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementContext.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementContext.java
@@ -32,6 +32,7 @@ import org.apache.hadoop.hbase.util.Pair;
 import org.apache.phoenix.jdbc.PhoenixConnection;
 import org.apache.phoenix.jdbc.PhoenixStatement;
 import org.apache.phoenix.join.TupleProjector;
+import org.apache.phoenix.parse.SelectStatement;
 import org.apache.phoenix.query.KeyRange;
 import org.apache.phoenix.query.QueryConstants;
 import org.apache.phoenix.query.QueryServices;
@@ -78,6 +79,8 @@ public class StatementContext {
     private TupleProjector clientTupleProjector;
     private TimeRange scanTimeRange = null;
     
+    private Map<SelectStatement, Object> subqueryResults;
+    
     public StatementContext(PhoenixStatement statement) {
         this(statement, FromCompiler.EMPTY_TABLE_RESOLVER, new Scan(), new SequenceManager(statement));
     }
@@ -99,6 +102,7 @@ public class StatementContext {
         this.currentTable = resolver != null && !resolver.getTables().isEmpty() ? resolver.getTables().get(0) : null;
         this.whereConditionColumns = new ArrayList<Pair<byte[],byte[]>>();
         this.dataColumns = this.currentTable == null ? Collections.<PColumn, Integer>emptyMap() : Maps.<PColumn, Integer>newLinkedHashMap();
+        this.subqueryResults = Maps.<SelectStatement, Object>newHashMap();
     }
 
     /**
@@ -289,5 +293,16 @@ public class StatementContext {
     public TimeRange getScanTimeRange() {
     	return this.scanTimeRange;
     }
+    
+    public boolean isSubqueryResultAvailable(SelectStatement select) {
+        return subqueryResults.containsKey(select);
+    }
 
+    public Object getSubqueryResult(SelectStatement select) {
+        return subqueryResults.get(select);
+    }
+    
+    public void setSubqueryResult(SelectStatement select, Object result) {
+        subqueryResults.put(select, result);
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
index e29a20a..88aa81c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
@@ -34,6 +34,7 @@ import org.apache.phoenix.parse.NamedTableNode;
 import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.ParseNodeRewriter;
 import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.parse.SubqueryParseNode;
 import org.apache.phoenix.parse.TableName;
 import org.apache.phoenix.parse.TableNode;
 import org.apache.phoenix.parse.TableNodeVisitor;
@@ -150,6 +151,18 @@ public class StatementNormalizer extends ParseNodeRewriter {
              nodes = normNodes;
              node = NODE_FACTORY.comparison(node.getInvertFilterOp(), nodes.get(0), nodes.get(1));
          }
+         // Add limit 2 to direct comparison with sub-query without ANY/SOME/ALL modifiers.
+         ParseNode rhs = nodes.get(1);
+         if (rhs instanceof SubqueryParseNode) {
+             SelectStatement subquery = ((SubqueryParseNode) rhs).getSelectNode();
+             subquery = NODE_FACTORY.select(subquery, NODE_FACTORY.limit(NODE_FACTORY.literal(2)));
+             rhs = NODE_FACTORY.subquery(subquery, true);
+             List<ParseNode> normNodes = Lists.newArrayListWithExpectedSize(2);
+             normNodes.add(nodes.get(0));
+             normNodes.add(rhs);
+             nodes = normNodes;
+             node = NODE_FACTORY.comparison(node.getFilterOp(), nodes.get(0), nodes.get(1));
+         }
          return super.visitLeave(node, nodes);
     }
     

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
index 2e72f43..27a4490 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
@@ -43,10 +43,12 @@ import org.apache.phoenix.parse.FilterableStatement;
 import org.apache.phoenix.parse.HintNode.Hint;
 import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.ParseNodeFactory;
+import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
+import org.apache.phoenix.parse.SubqueryParseNode;
 import org.apache.phoenix.schema.AmbiguousColumnException;
 import org.apache.phoenix.schema.ColumnNotFoundException;
 import org.apache.phoenix.schema.ColumnRef;
-import org.apache.phoenix.schema.LocalIndexDataColumnRef;
 import org.apache.phoenix.schema.PDataType;
 import org.apache.phoenix.schema.PTable;
 import org.apache.phoenix.schema.PTable.IndexType;
@@ -75,7 +77,7 @@ public class WhereCompiler {
     private WhereCompiler() {
     }
 
-    public static Expression compile(StatementContext context, FilterableStatement statement) throws SQLException {
+    public static Set<SubqueryParseNode> compile(StatementContext context, FilterableStatement statement) throws SQLException {
         return compile(context, statement, null);
     }
     
@@ -89,7 +91,7 @@ public class WhereCompiler {
      * @throws ColumnNotFoundException if column name could not be resolved
      * @throws AmbiguousColumnException if an unaliased column name is ambiguous across multiple tables
      */
-    public static Expression compile(StatementContext context, FilterableStatement statement, ParseNode viewWhere) throws SQLException {
+    public static Set<SubqueryParseNode> compile(StatementContext context, FilterableStatement statement, ParseNode viewWhere) throws SQLException {
         return compile(context, statement, viewWhere, Collections.<Expression>emptyList(), false);
     }
     
@@ -103,10 +105,21 @@ public class WhereCompiler {
      * @throws ColumnNotFoundException if column name could not be resolved
      * @throws AmbiguousColumnException if an unaliased column name is ambiguous across multiple tables
      */    
-    public static Expression compile(StatementContext context, FilterableStatement statement, ParseNode viewWhere, List<Expression> dynamicFilters, boolean hashJoinOptimization) throws SQLException {
+    public static Set<SubqueryParseNode> compile(StatementContext context, FilterableStatement statement, ParseNode viewWhere, List<Expression> dynamicFilters, boolean hashJoinOptimization) throws SQLException {
+        ParseNode where = statement.getWhere();
+        Set<SubqueryParseNode> subqueryNodes = Sets.<SubqueryParseNode> newHashSet();
+        SubqueryParseNodeVisitor subqueryVisitor = new SubqueryParseNodeVisitor(context, subqueryNodes);
+        if (where != null) {
+            where.accept(subqueryVisitor);
+        }
+        if (viewWhere != null) {
+            viewWhere.accept(subqueryVisitor);
+        }
+        if (!subqueryNodes.isEmpty())
+            return subqueryNodes;
+        
         Set<Expression> extractedNodes = Sets.<Expression>newHashSet();
         WhereExpressionCompiler whereCompiler = new WhereExpressionCompiler(context);
-        ParseNode where = statement.getWhere();
         Expression expression = where == null ? LiteralExpression.newConstant(true,PDataType.BOOLEAN,true) : where.accept(whereCompiler);
         if (whereCompiler.isAggregate()) {
             throw new SQLExceptionInfo.Builder(SQLExceptionCode.AGGREGATE_IN_WHERE).build().buildException();
@@ -128,7 +141,7 @@ public class WhereCompiler {
         expression = WhereOptimizer.pushKeyExpressionsToScan(context, statement, expression, extractedNodes);
         setScanFilter(context, statement, expression, whereCompiler.disambiguateWithFamily, hashJoinOptimization);
 
-        return expression;
+        return subqueryNodes;
     }
     
     private static class WhereExpressionCompiler extends ExpressionCompiler {
@@ -212,7 +225,6 @@ public class WhereCompiler {
      */
     private static void setScanFilter(StatementContext context, FilterableStatement statement, Expression whereClause, boolean disambiguateWithFamily, boolean hashJoinOptimization) {
         Scan scan = context.getScan();
-        assert scan.getFilter() == null;
 
         if (LiteralExpression.isFalse(whereClause)) {
             context.setScanRanges(ScanRanges.NOTHING);
@@ -259,4 +271,24 @@ public class WhereCompiler {
             ScanUtil.andFilterAtBeginning(scan, scanRanges.getSkipScanFilter());
         }
     }
+    
+    private static class SubqueryParseNodeVisitor extends StatelessTraverseAllParseNodeVisitor {
+        private final StatementContext context;
+        private final Set<SubqueryParseNode> subqueryNodes;
+        
+        SubqueryParseNodeVisitor(StatementContext context, Set<SubqueryParseNode> subqueryNodes) {
+            this.context = context;
+            this.subqueryNodes = subqueryNodes;
+        }
+        
+        @Override
+        public Void visit(SubqueryParseNode node) throws SQLException {
+            SelectStatement select = node.getSelectNode();
+            if (!context.isSubqueryResultAvailable(select)) {
+                this.subqueryNodes.add(node);
+            }
+            return null;                
+        }
+        
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/81477ee8/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index 3d5e302..bbb09dc 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -78,6 +78,7 @@ public enum SQLExceptionCode {
     SERVER_ARITHMETIC_ERROR(212, "22012", "Arithmetic error on server."),
     VALUE_OUTSIDE_RANGE(213,"22003","Value outside range."),
     VALUE_IN_LIST_NOT_CONSTANT(214, "22008", "Values in IN must evaluate to a constant."),
+    SINGLE_ROW_SUBQUERY_RETURNS_MULTIPLE_ROWS(215, "22015", "Single-row subquery returns more than one row."),
     
     /**
      * Constraint Violation (errorcode 03, sqlstate 23)


Mime
View raw message