Return-Path: X-Original-To: apmail-phoenix-commits-archive@minotaur.apache.org Delivered-To: apmail-phoenix-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id A68D3178A0 for ; Mon, 6 Oct 2014 17:56:13 +0000 (UTC) Received: (qmail 4754 invoked by uid 500); 6 Oct 2014 17:56:13 -0000 Delivered-To: apmail-phoenix-commits-archive@phoenix.apache.org Received: (qmail 4659 invoked by uid 500); 6 Oct 2014 17:56:13 -0000 Mailing-List: contact commits-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list commits@phoenix.apache.org Received: (qmail 4630 invoked by uid 99); 6 Oct 2014 17:56:13 -0000 Received: from tyr.zones.apache.org (HELO tyr.zones.apache.org) (140.211.11.114) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 06 Oct 2014 17:56:13 +0000 Received: by tyr.zones.apache.org (Postfix, from userid 65534) id 39289320CC7; Mon, 6 Oct 2014 17:56:13 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: maryannxue@apache.org To: commits@phoenix.apache.org Date: Mon, 06 Oct 2014 17:56:15 -0000 Message-Id: <313b86b31f3e461dbd41a79701a06aa1@git.apache.org> In-Reply-To: <51ec3cc4346445d98148fc9ac6add807@git.apache.org> References: <51ec3cc4346445d98148fc9ac6add807@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [3/3] git commit: PHOENIX-167 Support semi/anti-joins 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 Authored: Mon Oct 6 13:55:56 2014 -0400 Committer: maryannxue 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 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 data() { + List 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 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 children = Lists. 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 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