http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java index 435c649..b5e1c7e 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java @@ -17,17 +17,6 @@ */ 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_DISPLAY_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; @@ -35,71 +24,28 @@ import static org.junit.Assert.assertTrue; import static org.junit.Assert.fail; import java.sql.Connection; -import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -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.MetaDataUtil; 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.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; @RunWith(Parameterized.class) -public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { - - private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - private String[] indexDDL; - private String[] plans; - +public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { + public SubqueryUsingSortMergeJoinIT(String[] indexDDL, String[] plans) { - this.indexDDL = indexDDL; - this.plans = plans; - } - - @BeforeClass - @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class) - public static void doSetup() throws Exception { - Map<String,String> props = Maps.newHashMapWithExpectedSize(3); - // Forces server cache to be used - props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2)); - // Must update config before starting server - setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator())); - } - - @Before - public void initTable() throws Exception { - initTableValues(); - if (indexDDL != null && indexDDL.length > 0) { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - for (String ddl : indexDDL) { - try { - conn.createStatement().execute(ddl); - } catch (TableAlreadyExistsException e) { - } - } - conn.close(); - } + super(indexDDL, plans); } @Parameters @@ -109,52 +55,52 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { {}, { "SORT-MERGE-JOIN (SEMI) TABLES\n" + " SORT-MERGE-JOIN (INNER) TABLES\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" + " SERVER SORTED BY [\"I.supplier_id\"]\n" + " CLIENT MERGE SORT\n" + " AND\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" + " CLIENT SORTED BY [\"I.item_id\"]\n" + "AND (SKIP MERGE)\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " ['000000000000001'] - [*]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + " CLIENT MERGE SORT\n" + "CLIENT SORTED BY [I.NAME]", "SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + " SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + - " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" + " CLIENT MERGE SORT\n" + " AND\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"]\\\n" + " CLIENT MERGE SORT\n" + " CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" + "AND\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\".+.item_id\", .+.NAME\\]\n" + " CLIENT MERGE SORT\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + - " DYNAMIC SERVER FILTER BY \"" + JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id\" IN \\(\\$\\d+.\\$\\d+\\)\n" + + " DYNAMIC SERVER FILTER BY \"" + JOIN_ITEM_TABLE_FULL_NAME + ".item_id\" IN \\(\\$\\d+.\\$\\d+\\)\n" + "CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)", "SORT-MERGE-JOIN \\(SEMI\\) TABLES\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + "\n" + "AND \\(SKIP MERGE\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" + " CLIENT MERGE SORT\n" + " PARALLEL INNER-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " DYNAMIC SERVER FILTER BY \"I.item_id\" IN \\(\"O.item_id\"\\)\n" + @@ -178,21 +124,21 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { " CLIENT MERGE SORT\n" + " CLIENT SORTED BY [\"I.:item_id\"]\n" + "AND (SKIP MERGE)\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " ['000000000000001'] - [*]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + " CLIENT MERGE SORT\n" + "CLIENT SORTED BY [\"I.0:NAME\"]", "SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + " SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + - " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" + " CLIENT MERGE SORT\n" + " AND\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 \\[\".+.:item_id\", \".+.0:NAME\"\\]\n" + " PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" + @@ -201,7 +147,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.:item_id\", \".+.0:NAME\"\\]\n" + " PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + "CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)", @@ -217,9 +163,9 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" + " CLIENT MERGE SORT\n" + " PARALLEL INNER-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " AFTER-JOIN SERVER FILTER BY \\(\"I.0:NAME\" = 'T2' OR O.QUANTITY > \\$\\d+.\\$\\d+\\)", @@ -232,61 +178,61 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { }, { "SORT-MERGE-JOIN (SEMI) TABLES\n" + " SORT-MERGE-JOIN (INNER) TABLES\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + " [1]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " [1]\n" + " SERVER SORTED BY [\"I.0:supplier_id\"]\n" + " CLIENT MERGE SORT\n" + " AND\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " [1]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + " [1]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER SORTED BY [\"S.:supplier_id\"]\n" + " CLIENT MERGE SORT\n" + " CLIENT SORTED BY [\"I.:item_id\"]\n" + "AND (SKIP MERGE)\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " ['000000000000001'] - [*]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + " CLIENT MERGE SORT\n" + "CLIENT SORTED BY [\"I.0:NAME\"]", "SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + " SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" + - " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" + " CLIENT MERGE SORT\n" + " AND\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[1\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " \\[1\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.:item_id\", \".+.0: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" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" + "AND\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[1\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " \\[1\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.:item_id\", \".+.0: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" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " DYNAMIC SERVER FILTER BY \"" + JOIN_SCHEMA + ".idx_item.:item_id\" IN \\(\\$\\d+.\\$\\d+\\)\n" + "CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)", "SORT-MERGE-JOIN \\(SEMI\\) TABLES\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " \\[1\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + " \\[1\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER SORTED BY \\[\"Join.idx_customer.:customer_id\"\\]\n" + " CLIENT MERGE SORT\n" + "AND \\(SKIP MERGE\\)\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[1\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " \\[1\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" + " CLIENT MERGE SORT\n" + " PARALLEL INNER-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" + - " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" + " CLIENT MERGE SORT\n" + " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN \\(\"O.item_id\"\\)\n" + @@ -295,296 +241,12 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { 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 testInSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"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"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -602,7 +264,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"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"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -617,7 +279,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ 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"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -633,9 +295,9 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); rs = conn.createStatement().executeQuery("EXPLAIN " + query); - assertEquals(plans[0], QueryUtil.getExplainPlan(rs)); + assertPlansEqual(plans[0], QueryUtil.getExplainPlan(rs)); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ 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"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s LEFT JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -653,8 +315,8 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * 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 + "))"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + "))" + + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + "))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -672,7 +334,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); String plan = QueryUtil.getExplainPlan(rs); - assertTrue("\"" + plan + "\" does not match \"" + plans[1] + "\"", Pattern.matches(plans[1], plan)); + assertPlansMatch(plans[1], plan); } finally { conn.close(); } @@ -683,7 +345,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"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"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn, 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()); @@ -698,8 +360,8 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * 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)"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " co WHERE EXISTS (SELECT 1 FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn, 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 " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, 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()); @@ -717,7 +379,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); String plan = QueryUtil.getExplainPlan(rs); - assertTrue("\"" + plan + "\" does not match \"" + plans[1] + "\"", Pattern.matches(plans[1], plan)); + assertPlansMatch(plans[1], plan); } finally { conn.close(); } @@ -728,7 +390,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\")"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -746,7 +408,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ name from " + JOIN_CUSTOMER_TABLE_FULL_NAME + " WHERE \"customer_id\" IN (SELECT \"customer_id\" FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i JOIN " + JOIN_ORDER_TABLE_FULL_NAME + " o ON o.\"item_id\" = i.\"item_id\" WHERE i.name = 'T2' OR quantity > (SELECT avg(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\"))"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ name from " + getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME) + " WHERE \"customer_id\" IN (SELECT \"customer_id\" FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o ON o.\"item_id\" = i.\"item_id\" WHERE i.name = 'T2' OR quantity > (SELECT avg(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\"))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -758,9 +420,9 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); String plan = QueryUtil.getExplainPlan(rs); - assertTrue("\"" + plan + "\" does not match \"" + plans[2] + "\"", Pattern.matches(plans[2], plan)); + assertPlansMatch(plans[2], plan); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE quantity = (SELECT quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004')"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -774,7 +436,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE quantity = (SELECT quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003')"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); try { @@ -783,7 +445,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { } catch (SQLException e) { } - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE quantity = (SELECT max(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004' GROUP BY \"order_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -797,7 +459,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE quantity = (SELECT max(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003' GROUP BY \"order_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); try { @@ -815,7 +477,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\")"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -830,7 +492,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -839,7 +501,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -857,7 +519,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { @Test public void testSubqueryWithUpsert() throws Exception { - String tempTable = "UPSERT_SUBQUERY_TABLE"; + String tempTable = generateUniqueName(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); @@ -866,8 +528,8 @@ public class SubqueryUsingSortMergeJoinIT extends BaseHBaseManagedTimeIT { + " (item_id varchar not null primary key, " + " name varchar)"); conn.createStatement().execute("UPSERT /*+ USE_SORT_MERGE_JOIN*/ INTO " + tempTable + "(item_id, name)" - + " SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME - + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")"); + + " SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ")"); String query = "SELECT name FROM " + tempTable + " ORDER BY item_id"; PreparedStatement statement = conn.prepareStatement(query);
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/it/java/org/apache/phoenix/end2end/UserDefinedFunctionsIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UserDefinedFunctionsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UserDefinedFunctionsIT.java index 9f53d12..8b5a591 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UserDefinedFunctionsIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UserDefinedFunctionsIT.java @@ -792,13 +792,159 @@ public class UserDefinedFunctionsIT extends BaseOwnClusterIT { assertFalse(rs.next()); } + private static void initJoinTableValues(Connection conn) throws Exception { + conn.createStatement().execute("create table " + JOIN_ITEM_TABLE_FULL_NAME + + " (\"item_id\" varchar(10) not null primary key, " + + " name varchar, " + + " price integer, " + + " discount1 integer, " + + " discount2 integer, " + + " \"supplier_id\" varchar(10), " + + " description varchar)"); + conn.createStatement().execute("create table " + JOIN_SUPPLIER_TABLE_FULL_NAME + + " (\"supplier_id\" varchar(10) not null primary key, " + + " name varchar, " + + " phone varchar(12), " + + " address varchar, " + + " loc_id varchar(5))"); + PreparedStatement stmt; + conn.createStatement().execute("CREATE SEQUENCE my.seq"); + + // 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(); + + conn.commit(); + } + @Test public void testUdfWithJoin() throws Exception { String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", myreverse8(supp.name) FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp RIGHT JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " item ON myreverse8(item.\"supplier_id\") = myreverse8(supp.\"supplier_id\") ORDER BY \"item_id\""; Connection conn = driver.connect(url, EMPTY_PROPS); - initJoinTableValues(url, null, null); + initJoinTableValues(conn); conn.createStatement().execute( "create function myreverse8(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end.MyReverse' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'"); http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java index 9a2eec1..254046b 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java @@ -227,13 +227,13 @@ public class IndexIT extends ParallelStatsDisabledIT { @Test public void testCreateIndexAfterUpsertStarted() throws Exception { - String tableName = "TBL_" + generateUniqueName(); - String indexName = "IND_" + generateUniqueName(); - String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); - testCreateIndexAfterUpsertStarted(false, fullTableName + "1", fullIndexName + "1"); + testCreateIndexAfterUpsertStarted(false, + SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName()), + SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName())); if (transactional) { - testCreateIndexAfterUpsertStarted(true, fullTableName + "2", fullIndexName + "2"); + testCreateIndexAfterUpsertStarted(true, + SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName()), + SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, generateUniqueName())); } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java index ffaafd8..665af51 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java @@ -41,6 +41,7 @@ import org.apache.phoenix.parse.SelectStatement; import org.apache.phoenix.query.BaseConnectionlessQueryTest; import org.apache.phoenix.util.PropertiesUtil; import org.apache.phoenix.util.QueryUtil; +import org.junit.BeforeClass; import org.junit.Test; /** @@ -48,6 +49,40 @@ import org.junit.Test; */ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest { + @BeforeClass + public static void createJoinTables() throws SQLException { + try (Connection conn = DriverManager.getConnection(getUrl())) { + conn.createStatement().execute("create table " + JOIN_ORDER_TABLE_FULL_NAME + + " (\"order_id\" varchar(15) not null primary key, " + + " \"customer_id\" varchar(10), " + + " \"item_id\" varchar(10), " + + " price integer, " + + " quantity integer, " + + " date timestamp)"); + conn.createStatement().execute("create table " + JOIN_CUSTOMER_TABLE_FULL_NAME + + " (\"customer_id\" varchar(10) not null primary key, " + + " name varchar, " + + " phone varchar(12), " + + " address varchar, " + + " loc_id varchar(5), " + + " date date)"); + conn.createStatement().execute("create table " + JOIN_ITEM_TABLE_FULL_NAME + + " (\"item_id\" varchar(10) not null primary key, " + + " name varchar, " + + " price integer, " + + " discount1 integer, " + + " discount2 integer, " + + " \"supplier_id\" varchar(10), " + + " description varchar)"); + conn.createStatement().execute("create table " + JOIN_SUPPLIER_TABLE_FULL_NAME + + " (\"supplier_id\" varchar(10) not null primary key, " + + " name varchar, " + + " phone varchar(12), " + + " address varchar, " + + " loc_id varchar(5))"); + } + } + @Test public void testExplainPlan() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/test/java/org/apache/phoenix/query/BaseConnectionlessQueryTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseConnectionlessQueryTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseConnectionlessQueryTest.java index 452ea4d..b74cefb 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseConnectionlessQueryTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseConnectionlessQueryTest.java @@ -21,10 +21,6 @@ import static org.apache.phoenix.util.PhoenixRuntime.TENANT_ID_ATTRIB; import static org.apache.phoenix.util.TestUtil.ATABLE_NAME; import static org.apache.phoenix.util.TestUtil.ENTITY_HISTORY_TABLE_NAME; import static org.apache.phoenix.util.TestUtil.FUNKY_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.MULTI_CF_NAME; import static org.apache.phoenix.util.TestUtil.PHOENIX_CONNECTIONLESS_JDBC_URL; import static org.apache.phoenix.util.TestUtil.PTSDB2_NAME; @@ -114,11 +110,9 @@ public class BaseConnectionlessQueryTest extends BaseTest { ensureTableCreated(getUrl(), PTSDB2_NAME); ensureTableCreated(getUrl(), PTSDB3_NAME); ensureTableCreated(getUrl(), MULTI_CF_NAME); - ensureTableCreated(getUrl(), JOIN_ORDER_TABLE_FULL_NAME); - ensureTableCreated(getUrl(), JOIN_CUSTOMER_TABLE_FULL_NAME); - ensureTableCreated(getUrl(), JOIN_ITEM_TABLE_FULL_NAME); - ensureTableCreated(getUrl(), JOIN_SUPPLIER_TABLE_FULL_NAME); ensureTableCreated(getUrl(), TABLE_WITH_ARRAY); + + Properties props = new Properties(); props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(HConstants.LATEST_TIMESTAMP)); PhoenixConnection conn = DriverManager.getConnection(PHOENIX_CONNECTIONLESS_JDBC_URL, props).unwrap(PhoenixConnection.class); http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java index 3de0b77..075828e 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java @@ -44,11 +44,6 @@ import static org.apache.phoenix.util.TestUtil.E_VALUE; import static org.apache.phoenix.util.TestUtil.FUNKY_NAME; import static org.apache.phoenix.util.TestUtil.HBASE_DYNAMIC_COLUMNS; import static org.apache.phoenix.util.TestUtil.HBASE_NATIVE; -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_FULL_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME; -import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME; import static org.apache.phoenix.util.TestUtil.MDTEST_NAME; import static org.apache.phoenix.util.TestUtil.MULTI_CF_NAME; import static org.apache.phoenix.util.TestUtil.PARENTID1; @@ -94,9 +89,7 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Timestamp; import java.sql.Types; -import java.text.SimpleDateFormat; import java.util.Arrays; import java.util.Iterator; import java.util.List; @@ -410,41 +403,6 @@ public abstract class BaseTest { " kv bigint)\n"); builder.put(SUM_DOUBLE_NAME,"create table SumDoubleTest" + " (id varchar not null primary key, d DOUBLE, f FLOAT, ud UNSIGNED_DOUBLE, uf UNSIGNED_FLOAT, i integer, de decimal)"); - builder.put(JOIN_ORDER_TABLE_FULL_NAME, "create table " + JOIN_ORDER_TABLE_FULL_NAME + - " (\"order_id\" varchar(15) not null primary key, " + - " \"customer_id\" varchar(10), " + - " \"item_id\" varchar(10), " + - " price integer, " + - " quantity integer, " + - " date timestamp)"); - builder.put(JOIN_CUSTOMER_TABLE_FULL_NAME, "create table " + JOIN_CUSTOMER_TABLE_FULL_NAME + - " (\"customer_id\" varchar(10) not null primary key, " + - " name varchar, " + - " phone varchar(12), " + - " address varchar, " + - " loc_id varchar(5), " + - " date date)"); - builder.put(JOIN_ITEM_TABLE_FULL_NAME, "create table " + JOIN_ITEM_TABLE_FULL_NAME + - " (\"item_id\" varchar(10) not null primary key, " + - " name varchar, " + - " price integer, " + - " discount1 integer, " + - " discount2 integer, " + - " \"supplier_id\" varchar(10), " + - " description varchar)"); - builder.put(JOIN_SUPPLIER_TABLE_FULL_NAME, "create table " + JOIN_SUPPLIER_TABLE_FULL_NAME + - " (\"supplier_id\" varchar(10) not null primary key, " + - " name varchar, " + - " phone varchar(12), " + - " address varchar, " + - " loc_id varchar(5))"); - builder.put(JOIN_COITEM_TABLE_FULL_NAME, "create table " + JOIN_COITEM_TABLE_FULL_NAME + - " (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"); builder.put(BINARY_NAME,"create table " + BINARY_NAME + " (a_binary BINARY(16) not null, \n" + " b_binary BINARY(16), \n" + @@ -1532,266 +1490,6 @@ public abstract class BaseTest { } } - protected static void initJoinTableValues(String url, byte[][] splits, Long ts) throws Exception { - if (ts == null) { - ensureTableCreated(url, JOIN_CUSTOMER_TABLE_FULL_NAME, JOIN_CUSTOMER_TABLE_FULL_NAME, splits); - ensureTableCreated(url, JOIN_ITEM_TABLE_FULL_NAME, JOIN_ITEM_TABLE_FULL_NAME, splits); - ensureTableCreated(url, JOIN_SUPPLIER_TABLE_FULL_NAME, JOIN_SUPPLIER_TABLE_FULL_NAME, splits); - ensureTableCreated(url, JOIN_ORDER_TABLE_FULL_NAME, JOIN_ORDER_TABLE_FULL_NAME, splits); - } else { - ensureTableCreated(url, JOIN_CUSTOMER_TABLE_FULL_NAME, JOIN_CUSTOMER_TABLE_FULL_NAME, splits, ts - 2); - ensureTableCreated(url, JOIN_ITEM_TABLE_FULL_NAME, JOIN_ITEM_TABLE_FULL_NAME, splits, ts - 2); - ensureTableCreated(url, JOIN_SUPPLIER_TABLE_FULL_NAME, JOIN_SUPPLIER_TABLE_FULL_NAME, splits, ts - 2); - ensureTableCreated(url, JOIN_ORDER_TABLE_FULL_NAME, JOIN_ORDER_TABLE_FULL_NAME, splits, ts - 2); - } - - SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - if (ts != null) { - props.setProperty(CURRENT_SCN_ATTRIB, ts.toString()); - } - Connection conn = DriverManager.getConnection(url, 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(); - } finally { - conn.close(); - } - } - /** * Disable and drop all the tables except SYSTEM.CATALOG and SYSTEM.SEQUENCE */ http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6e703dd/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 987b19a..85e6970 100644 --- a/pom.xml +++ b/pom.xml @@ -13,7 +13,7 @@ <name>The Apache Software License, Version 2.0</name> <url>http://www.apache.org/licenses/LICENSE-2.0.txt</url> <distribution>repo</distribution> - <comments /> + <comments/> </license> </licenses> @@ -117,7 +117,7 @@ <maven.assembly.version>2.5.2</maven.assembly.version> <!-- Plugin options --> - <numForkedUT>3</numForkedUT> + <numForkedUT>6</numForkedUT> <numForkedIT>6</numForkedIT> <!-- Set default encoding so multi-byte tests work correctly on the Mac --> @@ -234,9 +234,10 @@ <runOrder>alphabetical</runOrder> <reuseForks>true</reuseForks> <runOrder>alphabetical</runOrder> - <!--parallel>methods</parallel> - <threadCount>20</threadCount--> - <argLine>-enableassertions -Xmx3072m -XX:MaxPermSize=256m -Djava.security.egd=file:/dev/./urandom "-Djava.library.path=${hadoop.library.path}${path.separator}${java.library.path}" -XX:NewRatio=4 -XX:SurvivorRatio=8 -XX:+UseCompressedOops -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+DisableExplicitGC -XX:+UseCMSInitiatingOccupancyOnly -XX:+CMSClassUnloadingEnabled -XX:+CMSScavengeBeforeRemark -XX:CMSInitiatingOccupancyFraction=68</argLine> + <!--parallel>classesAndMethods</parallel> + <threadCount>20</threadCount> + <enableAssertions>false</enableAssertions--> + <argLine>-Xmx2000m -XX:MaxPermSize=256m -Djava.security.egd=file:/dev/./urandom "-Djava.library.path=${hadoop.library.path}${path.separator}${java.library.path}" -XX:NewRatio=4 -XX:SurvivorRatio=8 -XX:+UseCompressedOops -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+DisableExplicitGC -XX:+UseCMSInitiatingOccupancyOnly -XX:+CMSClassUnloadingEnabled -XX:+CMSScavengeBeforeRemark -XX:CMSInitiatingOccupancyFraction=68</argLine> <redirectTestOutputToFile>${test.output.tofile}</redirectTestOutputToFile> <shutdown>kill</shutdown> <testSourceDirectory>${basedir}/src/it/java</testSourceDirectory> @@ -261,9 +262,10 @@ <runOrder>alphabetical</runOrder> <reuseForks>true</reuseForks> <runOrder>alphabetical</runOrder> - <!--parallel>methods</parallel> - <threadCount>20</threadCount--> - <argLine>-enableassertions -Xmx3072m -XX:MaxPermSize=256m -Djava.security.egd=file:/dev/./urandom "-Djava.library.path=${hadoop.library.path}${path.separator}${java.library.path}" -XX:NewRatio=4 -XX:SurvivorRatio=8 -XX:+UseCompressedOops -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+DisableExplicitGC -XX:+UseCMSInitiatingOccupancyOnly -XX:+CMSClassUnloadingEnabled -XX:+CMSScavengeBeforeRemark -XX:CMSInitiatingOccupancyFraction=68</argLine> + <!--parallel>classesAndMethods</parallel> + <threadCount>20</threadCount> + <enableAssertions>false</enableAssertions--> + <argLine>-Xmx2000m -XX:MaxPermSize=256m -Djava.security.egd=file:/dev/./urandom "-Djava.library.path=${hadoop.library.path}${path.separator}${java.library.path}" -XX:NewRatio=4 -XX:SurvivorRatio=8 -XX:+UseCompressedOops -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+DisableExplicitGC -XX:+UseCMSInitiatingOccupancyOnly -XX:+CMSClassUnloadingEnabled -XX:+CMSScavengeBeforeRemark -XX:CMSInitiatingOccupancyFraction=68</argLine> <redirectTestOutputToFile>${test.output.tofile}</redirectTestOutputToFile> <shutdown>kill</shutdown> <testSourceDirectory>${basedir}/src/it/java</testSourceDirectory>