http://git-wip-us.apache.org/repos/asf/phoenix/blob/d77d2492/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java index 10634b8..0ab171a 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java @@ -28,22 +28,19 @@ import java.sql.SQLException; import java.util.Map; import java.util.regex.Pattern; -import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; +import org.apache.phoenix.end2end.BaseHBaseManagedTimeTableReuseIT; import org.apache.phoenix.end2end.Shadower; import org.apache.phoenix.query.QueryServices; -import org.apache.phoenix.util.PhoenixRuntime; -import org.apache.phoenix.util.QueryUtil; -import org.apache.phoenix.util.ReadOnlyProps; -import org.apache.phoenix.util.TestUtil; +import org.apache.phoenix.util.*; import org.junit.BeforeClass; import org.junit.Test; import com.google.common.collect.Maps; -public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { +public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeTableReuseIT { @BeforeClass - @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class) + @Shadower(classBeingShadowed = BaseHBaseManagedTimeTableReuseIT.class) public static void doSetup() throws Exception { Map<String,String> props = Maps.newHashMapWithExpectedSize(3); // Drop the HBase table metadata for this test @@ -76,44 +73,58 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { @Test public void testGlobalIndexOptimization() throws Exception { - testOptimization(null); + String dataTableName = generateRandomString(); + String indexTableName = generateRandomString(); + String dataTableFullName = SchemaUtil.getTableName("", dataTableName); + testOptimization(dataTableName, dataTableFullName, indexTableName, 4); } @Test public void testGlobalIndexOptimizationWithSalting() throws Exception { - testOptimization(4); + String dataTableName = generateRandomString(); + String indexTableName = generateRandomString(); + String dataTableFullName = SchemaUtil.getTableName("", dataTableName); + testOptimization(dataTableName, dataTableFullName, indexTableName, 4); + } @Test public void testGlobalIndexOptimizationTenantSpecific() throws Exception { - testOptimizationTenantSpecific(null); + String dataTableName = generateRandomString(); + String indexTableName = generateRandomString(); + testOptimizationTenantSpecific(dataTableName, indexTableName, null); } @Test public void testGlobalIndexOptimizationWithSaltingTenantSpecific() throws Exception { - testOptimizationTenantSpecific(4); + String dataTableName = generateRandomString(); + String indexTableName = generateRandomString(); + testOptimizationTenantSpecific(dataTableName, indexTableName, 4); } - private void testOptimization(Integer saltBuckets) throws Exception { - createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, saltBuckets, "('e','i','o')", false); + private void testOptimization(String dataTableName, String dataTableFullName, String indexTableName, Integer saltBuckets) throws Exception { + + createBaseTable(dataTableName, saltBuckets, "('e','i','o')", false); Connection conn1 = DriverManager.getConnection(getUrl()); try{ - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('b',1,2,4,'z')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('f',1,2,3,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('j',2,4,2,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('q',3,1,1,'c')"); conn1.commit(); - createIndex(TestUtil.DEFAULT_INDEX_TABLE_NAME, TestUtil.DEFAULT_DATA_TABLE_NAME, "v1"); + createIndex(indexTableName, dataTableName, "v1"); - String query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ * FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'"; + String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ * FROM " + dataTableName +" where v1='a'"; ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); - + + + String expected = - "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\['a'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\['a'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; String actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -130,15 +141,15 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertEquals(2, rs.getInt("k3")); assertFalse(rs.next()); - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ * FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ * FROM " + dataTableName +" where v1='a'"; rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); expected = - "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\['a'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\['a'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -157,16 +168,16 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertEquals("a", rs.getString("v1")); assertFalse(rs.next()); - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ * FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a' limit 1"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ * FROM " + dataTableName +" where v1='a' limit 1"; rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); expected = - "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" + "CLIENT 1 ROW LIMIT\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\['a'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\['a'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)\n" + + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)\n" + " JOIN-SCANNER 1 ROW LIMIT"; actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -180,18 +191,18 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertEquals("a", rs.getString("v1")); assertFalse(rs.next()); - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where v1<='z' and k3 > 1 order by V1,t_id"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ t_id, k1, k2, k3, V1 from " + dataTableFullName + " where v1<='z' and k3 > 1 order by V1,t_id"; rs = conn1.createStatement().executeQuery("EXPLAIN " + query); expected = - "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + dataTableName + "\n" + " SERVER FILTER BY K3 > 1\n" + - " SERVER SORTED BY \\[T.V1, T.T_ID\\]\n" + + " SERVER SORTED BY \\[" + dataTableName + ".V1, " + dataTableName + ".T_ID\\]\n" + "CLIENT MERGE SORT\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\[\\*\\] - \\['z'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\[\\*\\] - \\['z'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -216,17 +227,17 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertEquals("z", rs.getString("V1")); assertFalse(rs.next()); - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, V1, k3 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where v1 <='z' group by v1,t_id, k3"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ t_id, V1, k3 from " + dataTableFullName + " where v1 <='z' group by v1,t_id, k3"; rs = conn1.createStatement().executeQuery("EXPLAIN " + query); expected = - "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + - " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[T.V1, T.T_ID, T.K3\\]\n" + + "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + dataTableName + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[" + dataTableName + ".V1, " + dataTableName + ".T_ID, " + dataTableName + ".K3\\]\n" + "CLIENT MERGE SORT\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\[\\*\\] - \\['z'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\[\\*\\] - \\['z'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -249,17 +260,17 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertEquals("z", rs.getString("V1")); assertFalse(rs.next()); - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ v1,sum(k3) from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where v1 <='z' group by v1 order by v1"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ v1,sum(k3) from " + dataTableFullName + " where v1 <='z' group by v1 order by v1"; rs = conn1.createStatement().executeQuery("EXPLAIN " + query); expected = - "CLIENT PARALLEL \\d-WAY FULL SCAN OVER T\n" + - " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[T.V1\\]\n" + + "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + dataTableName + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[" + dataTableName + ".V1\\]\n" + "CLIENT MERGE SORT\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER I \\[\\*\\] - \\['z'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\[\\*\\] - \\['z'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.T_ID\", \"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".T_ID\", \"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; actual = QueryUtil.getExplainPlan(rs); assertTrue("Expected:\n" + expected + "\nbut got\n" + actual, Pattern.matches(expected, actual)); @@ -278,26 +289,26 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { } } - private void testOptimizationTenantSpecific(Integer saltBuckets) throws Exception { - createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, saltBuckets, "('e','i','o')", true); + private void testOptimizationTenantSpecific(String dataTableName, String indexTableName, Integer saltBuckets) throws Exception { + createBaseTable(dataTableName, saltBuckets, "('e','i','o')", true); Connection conn1 = DriverManager.getConnection(getUrl() + ';' + PhoenixRuntime.TENANT_ID_ATTRIB + "=tid1"); try{ - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values(1,2,4,'z')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values(1,2,3,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values(2,4,2,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values(3,1,1,'c')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(1,2,4,'z')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(1,2,3,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(2,4,2,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(3,1,1,'c')"); conn1.commit(); - createIndex(TestUtil.DEFAULT_INDEX_TABLE_NAME, TestUtil.DEFAULT_DATA_TABLE_NAME, "v1"); + createIndex(indexTableName, dataTableName, "v1"); - String query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ k1,k2,k3,v1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'"; + String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ k1,k2,k3,v1 FROM " + dataTableName +" where v1='a'"; ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); String actual = QueryUtil.getExplainPlan(rs); - String expected = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T \\['tid1'\\]\n" + + String expected = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " \\['tid1'\\]\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY RANGE SCAN OVER I \\['tid1','a'\\]\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\['tid1','a'\\]\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + - " DYNAMIC SERVER FILTER BY \\(\"T.K1\", \"T.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; + " DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; assertTrue("Expected:\n" + expected + "\ndid not match\n" + actual, Pattern.matches(expected, actual)); rs = conn1.createStatement().executeQuery(query); @@ -319,15 +330,16 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { @Test public void testGlobalIndexOptimizationOnSharedIndex() throws Exception { - createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, null, "('e','i','o')", false); + String dataTableName = generateRandomString(); + createBaseTable(dataTableName, null, "('e','i','o')", false); Connection conn1 = DriverManager.getConnection(getUrl()); try{ - conn1.createStatement().execute("CREATE INDEX i1 ON " + TestUtil.DEFAULT_DATA_TABLE_NAME + "(k2,k1) INCLUDE (v1)"); - conn1.createStatement().execute("CREATE VIEW v AS SELECT * FROM t WHERE v1 = 'a'"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')"); + conn1.createStatement().execute("CREATE INDEX i1 ON " + dataTableName + "(k2,k1) INCLUDE (v1)"); + conn1.createStatement().execute("CREATE VIEW v AS SELECT * FROM " + dataTableName + " WHERE v1 = 'a'"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('b',1,2,4,'z')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('f',1,2,3,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('j',2,4,2,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('q',3,1,1,'c')"); conn1.commit(); ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM v"); assertTrue(rs.next()); @@ -340,10 +352,10 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { String actual = QueryUtil.getExplainPlan(rs); String expected = - "CLIENT PARALLEL 1-WAY FULL SCAN OVER T\n" + + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" + " SERVER FILTER BY V1 = 'a'\n" + " SKIP-SCAN-JOIN TABLE 0\n" + - " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER _IDX_T \\[-32768,1\\] - \\[-32768,2\\]\n" + + " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER _IDX_" + dataTableName + " \\[-32768,1\\] - \\[-32768,2\\]\n" + " SERVER FILTER BY FIRST KEY ONLY AND \"K2\" IN \\(3,4\\)\n" + " DYNAMIC SERVER FILTER BY \\(\"V.T_ID\", \"V.K1\", \"V.K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)"; assertTrue("Expected:\n" + expected + "\ndid not match\n" + actual, Pattern.matches(expected,actual)); @@ -363,22 +375,25 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { @Test public void testNoGlobalIndexOptimization() throws Exception { - createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, null, "('e','i','o')", false); + String dataTableName = generateRandomString(); + String indexTableName = generateRandomString(); + String dataTableFullName = SchemaUtil.getTableName("", dataTableName); + createBaseTable(dataTableName, null, "('e','i','o')", false); Connection conn1 = DriverManager.getConnection(getUrl()); try{ - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')"); - conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('b',1,2,4,'z')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('f',1,2,3,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('j',2,4,2,'a')"); + conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values('q',3,1,1,'c')"); conn1.commit(); - conn1.createStatement().execute("CREATE INDEX " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " ON " + TestUtil.DEFAULT_DATA_TABLE_NAME + "(v1)"); + conn1.createStatement().execute("CREATE INDEX " + indexTableName + " ON " + dataTableName + "(v1)"); // All columns available in index - String query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, V1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'"; + String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ t_id, k1, k2, V1 FROM " + dataTableName +" where v1='a'"; ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " ['a']\n" + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " ['a']\n" + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); @@ -394,11 +409,11 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // No INDEX hint specified - query = "SELECT t_id, k1, k2, k3, V1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'"; + query = "SELECT t_id, k1, k2, k3, V1 FROM " + dataTableName +" where v1='a'"; rs = conn1.createStatement().executeQuery("EXPLAIN "+ query); assertEquals( - "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" + " SERVER FILTER BY V1 = 'a'", QueryUtil.getExplainPlan(rs)); @@ -416,11 +431,11 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // No where clause - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " order by V1,t_id"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ t_id, k1, k2, k3, V1 from " + dataTableFullName + " order by V1,t_id"; rs = conn1.createStatement().executeQuery("EXPLAIN " + query); assertEquals( - "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + dataTableName + "\n" + " SERVER SORTED BY [V1, T_ID]\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); @@ -453,11 +468,11 @@ public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // No where clause in index scan - query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where k3 > 1 order by V1,t_id"; + query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ t_id, k1, k2, k3, V1 from " + dataTableFullName + " where k3 > 1 order by V1,t_id"; rs = conn1.createStatement().executeQuery("EXPLAIN " + query); assertEquals( - "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" + + "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + dataTableName + "\n" + " SERVER FILTER BY K3 > 1\n" + " SERVER SORTED BY [V1, T_ID]\n" + "CLIENT MERGE SORT",
http://git-wip-us.apache.org/repos/asf/phoenix/blob/d77d2492/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java index ced3ac8..0c7a356 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java @@ -48,7 +48,7 @@ import org.apache.hadoop.hbase.coprocessor.RegionCoprocessorEnvironment; import org.apache.hadoop.hbase.coprocessor.SimpleRegionObserver; import org.apache.hadoop.hbase.regionserver.wal.WALEdit; import org.apache.hadoop.hbase.util.Bytes; -import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; +import org.apache.phoenix.end2end.BaseHBaseManagedTimeTableReuseIT; import org.apache.phoenix.end2end.Shadower; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.query.BaseTest; @@ -69,14 +69,12 @@ import com.google.common.collect.Maps; @RunWith(Parameterized.class) -public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { +public class ImmutableIndexIT extends BaseHBaseManagedTimeTableReuseIT { private final boolean localIndex; + private final boolean transactional; private final String tableDDLOptions; - private final String tableName; - private final String indexName; - private final String fullTableName; - private final String fullIndexName; + private volatile boolean stopThreads = false; private static String TABLE_NAME; @@ -85,19 +83,17 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { public ImmutableIndexIT(boolean localIndex, boolean transactional) { this.localIndex = localIndex; + this.transactional = transactional; StringBuilder optionBuilder = new StringBuilder("IMMUTABLE_ROWS=true"); if (transactional) { optionBuilder.append(", TRANSACTIONAL=true"); } this.tableDDLOptions = optionBuilder.toString(); - this.tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + ( transactional ? "_TXN" : ""); - this.indexName = "IDX" + ( transactional ? "_TXN" : ""); - this.fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); - this.fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + } @BeforeClass - @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class) + @Shadower(classBeingShadowed = BaseHBaseManagedTimeTableReuseIT.class) public static void doSetup() throws Exception { Map<String, String> serverProps = Maps.newHashMapWithExpectedSize(1); serverProps.put("hbase.coprocessor.region.classes", CreateIndexRegionObserver.class.getName()); @@ -118,6 +114,10 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { @Ignore public void testDropIfImmutableKeyValueColumn() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + String tableName = "TBL_" + generateRandomString(); + String indexName = "IND_" + generateRandomString(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); try (Connection conn = DriverManager.getConnection(getUrl(), props)) { conn.setAutoCommit(false); String ddl = @@ -157,7 +157,11 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { public void testCreateIndexDuringUpsertSelect() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); props.setProperty(QueryServices.MUTATE_BATCH_SIZE_ATTRIB, Integer.toString(100)); - TABLE_NAME = fullTableName + "_testCreateIndexDuringUpsertSelect"; + String tableName = "TBL_" + generateRandomString(); + String indexName = "IND_" + generateRandomString(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); + TABLE_NAME = fullTableName; String ddl ="CREATE TABLE " + TABLE_NAME + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; INDEX_DDL = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IF NOT EXISTS " + indexName + " ON " + TABLE_NAME + " (long_pk, varchar_pk)" @@ -173,8 +177,8 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { // run the upsert select and also create an index conn.setAutoCommit(true); - String upsertSelect = "UPSERT INTO " + TABLE_NAME + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) " + - "SELECT varchar_pk||'_upsert_select', char_pk, int_pk, long_pk, decimal_pk, date_pk FROM "+ TABLE_NAME; + String upsertSelect = "UPSERT INTO " + TABLE_NAME + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) " + + "SELECT varchar_pk||'_upsert_select', char_pk, int_pk, long_pk, decimal_pk, date_pk FROM "+ TABLE_NAME; conn.createStatement().execute(upsertSelect); ResultSet rs; rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ COUNT(*) FROM " + TABLE_NAME); @@ -241,6 +245,10 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT { @Test public void testCreateIndexWhileUpsertingData() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + String tableName = "TBL_" + generateRandomString(); + String indexName = "IND_" + generateRandomString(); + String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName); + String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName); String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions; String indexDDL = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (long_pk, varchar_pk)" http://git-wip-us.apache.org/repos/asf/phoenix/blob/d77d2492/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java index 21da43a..65f9bac 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java @@ -30,7 +30,7 @@ import java.sql.SQLException; import java.util.Properties; import org.apache.commons.lang.StringUtils; -import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT; +import org.apache.phoenix.end2end.BaseHBaseManagedTimeTableReuseIT; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.execute.CommitException; import org.apache.phoenix.query.QueryConstants; @@ -40,7 +40,7 @@ import org.apache.phoenix.util.PropertiesUtil; import org.apache.phoenix.util.QueryUtil; import org.junit.Test; -public class IndexExpressionIT extends BaseHBaseManagedTimeIT { +public class IndexExpressionIT extends BaseHBaseManagedTimeTableReuseIT { @Test public void testImmutableIndexCreateAndUpdate() throws Exception { @@ -112,18 +112,19 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); // create an expression index String ddl = "CREATE " + (localIndex ? "LOCAL" : "") - + " INDEX IDX ON " + + " INDEX " + indexName + " ON " + fullDataTableName + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(b.char_col2))," + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" @@ -153,7 +154,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + dataTableName + " [1,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT" - : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST.IDX ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", + : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST." + indexName + " ['VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1 ',3,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]", QueryUtil.getExplainPlan(rs)); // verify that the correct results are returned @@ -173,7 +174,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { + fullDataTableName; rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql); assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullDataTableName - + " [1]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX", + + " [1]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST." + indexName, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(indexSelectSql); verifyResult(rs, 1); @@ -195,7 +196,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { verifyResult(rs, 3); verifyResult(rs, 4); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -203,27 +204,31 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { @Test public void testMutableIndexUpdate() throws Exception { - helpTestUpdate(false); + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + helpTestUpdate(dataTableName, fullDataTableName, indexName, false); } @Test public void testMutableLocalIndexUpdate() throws Exception { - helpTestUpdate(true); + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + helpTestUpdate(dataTableName, fullDataTableName, indexName, true); } - protected void helpTestUpdate(boolean localIndex) throws Exception { - String dataTableName = MUTABLE_INDEX_DATA_TABLE; - String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + protected void helpTestUpdate(String dataTableName, String fullDataTableName, String indexName, boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); + populateDataTable(conn, dataTableName, MUTABLE_INDEX_DATA_TABLE); // create an expression index String ddl = "CREATE " + (localIndex ? "LOCAL" : "") - + " INDEX IDX ON " + + " INDEX " + indexName + " ON " + fullDataTableName + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2))," + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )" @@ -269,14 +274,14 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1)); assertEquals(2, rs.getLong(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } } - private void populateDataTable(Connection conn, String dataTable) throws SQLException { - ensureTableCreated(getUrl(), dataTable); + private void populateDataTable(Connection conn, String dataTable, String tableType) throws SQLException { + ensureTableCreated(getUrl(), dataTable, tableType); String upsert = "UPSERT INTO " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTable + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement stmt1 = conn.prepareStatement(upsert); @@ -307,16 +312,17 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestDeleteIndexedExpression(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX"; + String indexName = generateRandomString(); + String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - ensureTableCreated(getUrl(), dataTableName); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + ensureTableCreated(getUrl(), dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (2*long_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); @@ -353,7 +359,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexTableName); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -380,16 +386,17 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestDeleteCoveredCol(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; - String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + "IDX"; + String indexName = generateRandomString(); + String fullIndexTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + indexName; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - ensureTableCreated(getUrl(), dataTableName); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + ensureTableCreated(getUrl(), dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (long_pk, varchar_pk, 1+long_pk, UPPER(varchar_pk) )" + " INCLUDE (long_col1, long_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); @@ -434,7 +441,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals(2L, rs.getLong(3)); assertEquals("VARCHAR1", rs.getString(4)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -461,14 +468,15 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestGroupByCount(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (int_col1+int_col2)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); @@ -478,7 +486,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + groupBySql); String expectedPlan = "CLIENT PARALLEL 1-WAY " + (localIndex ? "RANGE SCAN OVER " + fullDataTableName + " [1]" - : "FULL SCAN OVER INDEX_TEST.IDX") + : "FULL SCAN OVER INDEX_TEST." + indexName) + "\n SERVER FILTER BY FIRST KEY ONLY\n SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + B.INT_COL2)\")]" + (localIndex ? "\nCLIENT MERGE SORT" : ""); assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs)); @@ -488,7 +496,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertTrue(rs.next()); assertEquals(1, rs.getInt(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -515,14 +523,15 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestSelectDistinct(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (int_col1+1)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); @@ -530,7 +539,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql); String expectedPlan = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + (localIndex ? fullDataTableName + " [1,0] - [1,*]" - : "INDEX_TEST.IDX [0] - [*]") + : "INDEX_TEST." + indexName + " [0] - [*]") + "\n SERVER FILTER BY FIRST KEY ONLY\n SERVER DISTINCT PREFIX FILTER OVER [TO_BIGINT(\"(A.INT_COL1 + 1)\")]\n SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + 1)\")]" + (localIndex ? "\nCLIENT MERGE SORT" : ""); assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs)); @@ -540,7 +549,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -567,14 +576,16 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestInClauseWithIndex(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (int_col1+1)"; PreparedStatement stmt = conn.prepareStatement(ddl); @@ -583,12 +594,12 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql); assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + (localIndex ? fullDataTableName + " [1,2]\n SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT" - : "INDEX_TEST.IDX [2]\n SERVER FILTER BY FIRST KEY ONLY"), QueryUtil.getExplainPlan(rs)); + : "INDEX_TEST." + indexName + " [2]\n SERVER FILTER BY FIRST KEY ONLY"), QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(sql); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -615,14 +626,16 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (int_col1+1)"; PreparedStatement stmt = conn.prepareStatement(ddl); @@ -632,7 +645,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("CLIENT PARALLEL 1-WAY " + (localIndex ? "RANGE SCAN OVER " + fullDataTableName + " [1]\n SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT" - : "FULL SCAN OVER INDEX_TEST.IDX\n SERVER FILTER BY FIRST KEY ONLY"), + : "FULL SCAN OVER INDEX_TEST." + indexName + "\n SERVER FILTER BY FIRST KEY ONLY"), QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(sql); assertTrue(rs.next()); @@ -640,7 +653,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -667,21 +680,24 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean localIndex) throws Exception { + String dataTableName = generateRandomString(); + String indexName = generateRandomString(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { - conn.createStatement().execute("CREATE TABLE cs (k VARCHAR NOT NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : "")); - String query = "SELECT * FROM cs"; + conn.createStatement().execute("CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : "")); + String query = "SELECT * FROM " + dataTableName; ResultSet rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX ics ON cs (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")"; + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); - query = "SELECT * FROM ics"; + query = "SELECT * FROM " + indexName; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - stmt = conn.prepareStatement("UPSERT INTO cs VALUES(?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)"); stmt.setString(1,"a"); stmt.setString(2, "x"); stmt.setString(3, "1"); @@ -692,13 +708,13 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { stmt.execute(); conn.commit(); - query = "SELECT (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\" FROM cs WHERE (\"V1\" || '_' || \"v2\") = 'x_1'"; + query = "SELECT (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\" FROM " + dataTableName + " WHERE (\"V1\" || '_' || \"v2\") = 'x_1'"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER CS [1,'x_1']\n" + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'x_1']\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); } else { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER ICS ['x_1']", QueryUtil.getExplainPlan(rs)); + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['x_1']", QueryUtil.getExplainPlan(rs)); } rs = conn.createStatement().executeQuery(query); @@ -714,13 +730,13 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("1",rs.getString("v2")); assertFalse(rs.next()); - query = "SELECT \"V1\", \"V1\" as foo1, (\"V1\" || '_' || \"v2\") as foo, (\"V1\" || '_' || \"v2\") as \"Foo1\", (\"V1\" || '_' || \"v2\") FROM cs ORDER BY foo"; + query = "SELECT \"V1\", \"V1\" as foo1, (\"V1\" || '_' || \"v2\") as foo, (\"V1\" || '_' || \"v2\") as \"Foo1\", (\"V1\" || '_' || \"v2\") FROM " + dataTableName + " ORDER BY foo"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); if(localIndex){ - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER CS [1]\nCLIENT MERGE SORT", + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); } else { - assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER ICS", QueryUtil.getExplainPlan(rs)); + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName, QueryUtil.getExplainPlan(rs)); } rs = conn.createStatement().executeQuery(query); @@ -747,7 +763,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("y_2",rs.getString(5)); assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\"")); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX ICS ON CS"); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + dataTableName); } finally { conn.close(); } @@ -774,14 +790,16 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean localIndex) throws Exception { - String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE; + String dataTableName = generateRandomString(); String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { conn.setAutoCommit(false); - populateDataTable(conn, dataTableName); - String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + fullDataTableName + populateDataTable(conn, dataTableName, mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE); + String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName + " (int_col1+1)"; conn = DriverManager.getConnection(getUrl(), props); @@ -800,7 +818,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals(2, rs.getInt(1)); assertEquals(1, rs.getInt(2)); assertFalse(rs.next()); - conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName); + conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullDataTableName); } finally { conn.close(); } @@ -833,35 +851,39 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + try { conn.setAutoCommit(false); // make sure that the tables are empty, but reachable conn.createStatement().execute( - "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); - query = "SELECT * FROM t" ; + "CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); + query = "SELECT * FROM " + dataTableName ; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h"); - conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (v1 || '_' || v2)"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (v1 || '_' || v2)"); - query = "SELECT * FROM t"; + query = "SELECT * FROM " + dataTableName; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)"); stmt.setString(1, "a"); stmt.setString(2, "x"); stmt.setString(3, "1"); stmt.execute(); conn.commit(); - assertIndexExists(conn,true); - conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1"); - assertIndexExists(conn,false); + assertIndexExists(conn, dataTableName, true); + conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v1"); + assertIndexExists(conn, dataTableName, false); - query = "SELECT * FROM t"; + query = "SELECT * FROM " + dataTableName; rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); @@ -869,13 +891,13 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)"); stmt.setString(1, "a"); stmt.setString(2, "2"); stmt.execute(); conn.commit(); - query = "SELECT * FROM t"; + query = "SELECT * FROM " + dataTableName; rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals("a",rs.getString(1)); @@ -887,8 +909,8 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { } } - private static void assertIndexExists(Connection conn, boolean exists) throws SQLException { - ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", false, false); + private static void assertIndexExists(Connection conn, String tableName, boolean exists) throws SQLException { + ResultSet rs = conn.getMetaData().getIndexInfo(null, null, tableName, false, false); assertEquals(exists, rs.next()); } @@ -915,6 +937,9 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { public void helpTestDropCoveredColumn(boolean mutable, boolean local) throws Exception { ResultSet rs; PreparedStatement stmt; + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); @@ -923,20 +948,19 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { // make sure that the tables are empty, but reachable conn.createStatement().execute( - "CREATE TABLE t" + "CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)"); - String dataTableQuery = "SELECT * FROM t"; + String dataTableQuery = "SELECT * FROM " + dataTableName; rs = conn.createStatement().executeQuery(dataTableQuery); assertFalse(rs.next()); - String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h"); - conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (k || '_' || v1) include (v2, v3)"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (k || '_' || v1) include (v2, v3)"); String indexTableQuery = "SELECT * FROM " + indexName; rs = conn.createStatement().executeQuery(indexTableQuery); assertFalse(rs.next()); // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?,?)"); stmt.setString(1, "a"); stmt.setString(2, "x"); stmt.setString(3, "1"); @@ -944,9 +968,9 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { stmt.execute(); conn.commit(); - assertIndexExists(conn,true); - conn.createStatement().execute("ALTER TABLE t DROP COLUMN v2"); - assertIndexExists(conn,true); + assertIndexExists(conn, dataTableName, true); + conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v2"); + assertIndexExists(conn, dataTableName, true); // verify data table rows rs = conn.createStatement().executeQuery(dataTableQuery); @@ -965,7 +989,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // add another row - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)"); stmt.setString(1, "b"); stmt.setString(2, "y"); stmt.setString(3, "k"); @@ -1025,6 +1049,10 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { ResultSet rs; PreparedStatement stmt; + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName = generateRandomString(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { @@ -1032,29 +1060,28 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { // make sure that the tables are empty, but reachable conn.createStatement().execute( - "CREATE TABLE t" + "CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); - String dataTableQuery = "SELECT * FROM t"; + String dataTableQuery = "SELECT * FROM " + dataTableName; rs = conn.createStatement().executeQuery(dataTableQuery); assertFalse(rs.next()); - String indexName = "IT_" + (mutable ? "M" : "IM") + "_" + (local ? "L" : "H"); - conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON t (v1 || '_' || v2)"); + conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (v1 || '_' || v2)"); String indexTableQuery = "SELECT * FROM " + indexName; rs = conn.createStatement().executeQuery(indexTableQuery); assertFalse(rs.next()); // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)"); stmt.setString(1, "a"); stmt.setString(2, "x"); stmt.setString(3, "1"); stmt.execute(); conn.commit(); - assertIndexExists(conn,true); - conn.createStatement().execute("ALTER TABLE t ADD v3 VARCHAR, k2 DECIMAL PRIMARY KEY"); - rs = conn.getMetaData().getPrimaryKeys("", "", "T"); + assertIndexExists(conn, dataTableName, true); + conn.createStatement().execute("ALTER TABLE " + dataTableName + " ADD v3 VARCHAR, k2 DECIMAL PRIMARY KEY"); + rs = conn.getMetaData().getPrimaryKeys("", "", dataTableName); assertTrue(rs.next()); assertEquals("K",rs.getString("COLUMN_NAME")); assertEquals(1, rs.getShort("KEY_SEQ")); @@ -1092,7 +1119,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertFalse(rs.next()); // load some data into the table - stmt = conn.prepareStatement("UPSERT INTO t(K,K2,V1,V2) VALUES(?,?,?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + "(K,K2,V1,V2) VALUES(?,?,?,?)"); stmt.setString(1, "b"); stmt.setBigDecimal(2, BigDecimal.valueOf(2)); stmt.setString(3, "y"); @@ -1145,28 +1172,33 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { private void helpTestUpdatableViewIndex(boolean local) throws Exception { Connection conn = DriverManager.getConnection(getUrl()); + String dataTableName = generateRandomString(); + String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName; + String indexName1 = generateRandomString(); + String viewName = generateRandomString(); + String indexName2 = generateRandomString(); try { - String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, k3 DECIMAL, s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2, k3))"; + String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, k3 DECIMAL, s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2, k3))"; conn.createStatement().execute(ddl); - ddl = "CREATE VIEW v AS SELECT * FROM t WHERE k1 = 1"; + ddl = "CREATE VIEW " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE k1 = 1"; conn.createStatement().execute(ddl); - conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)"); - conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)"); + conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)"); + conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)"); conn.commit(); ResultSet rs; - conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX i1 on v(k1+k2+k3) include (s1, s2)"); - conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)"); + conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX " + indexName1 + " on " + viewName + "(k1+k2+k3) include (s1, s2)"); + conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)"); conn.commit(); - String query = "SELECT k1, k2, k3, s1, s2 FROM v WHERE k1+k2+k3 = 173.0"; + String query = "SELECT k1, k2, k3, s1, s2 FROM " + viewName + " WHERE k1+k2+k3 = 173.0"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); String queryPlan = QueryUtil.getExplainPlan(rs); if (local) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER T [1,173]\n" + "CLIENT MERGE SORT", + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,173]\n" + "CLIENT MERGE SORT", queryPlan); } else { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + Short.MIN_VALUE + ",173]", queryPlan); + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_" + dataTableName + " [" + Short.MIN_VALUE + ",173]", queryPlan); } rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); @@ -1177,16 +1209,16 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("bar1", rs.getString(5)); assertFalse(rs.next()); - conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX i2 on v(s1||'_'||s2)"); + conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX " + indexName2 + " on " + viewName + "(s1||'_'||s2)"); - query = "SELECT k1, k2, s1||'_'||s2 FROM v WHERE (s1||'_'||s2)='foo2_bar2'"; + query = "SELECT k1, k2, s1||'_'||s2 FROM " + viewName + " WHERE (s1||'_'||s2)='foo2_bar2'"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); if (local) { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER T [" + (2) + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [" + (2) + ",'foo2_bar2']\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); } else { - assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + (Short.MIN_VALUE + 1) + ",'foo2_bar2']\n" + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_" + dataTableName + " [" + (Short.MIN_VALUE + 1) + ",'foo2_bar2']\n" + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); } rs = conn.createStatement().executeQuery(query); @@ -1206,41 +1238,45 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); try { + String dataTableName = generateRandomString(); + String indexName1 = generateRandomString(); + String viewName = generateRandomString(); + String indexName2 = generateRandomString(); ResultSet rs; - String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2))"; + String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2))"; conn.createStatement().execute(ddl); - conn.createStatement().execute("CREATE INDEX i1 ON t(k2, s2, s3, s1)"); - conn.createStatement().execute("CREATE INDEX i2 ON t(k2, s2||'_'||s3, s1, s4)"); + conn.createStatement().execute("CREATE INDEX " + indexName1 + " ON " + dataTableName + "(k2, s2, s3, s1)"); + conn.createStatement().execute("CREATE INDEX " + indexName2 + " ON " + dataTableName + "(k2, s2||'_'||s3, s1, s4)"); - ddl = "CREATE VIEW v AS SELECT * FROM t WHERE s1 = 'foo'"; + ddl = "CREATE VIEW " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE s1 = 'foo'"; conn.createStatement().execute(ddl); - conn.createStatement().execute("UPSERT INTO t VALUES(1,1,'foo','abc','cab')"); - conn.createStatement().execute("UPSERT INTO t VALUES(2,2,'bar','xyz','zyx')"); + conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1,'foo','abc','cab')"); + conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(2,2,'bar','xyz','zyx')"); conn.commit(); - rs = conn.createStatement().executeQuery("SELECT count(*) FROM v"); + rs = conn.createStatement().executeQuery("SELECT count(*) FROM " + viewName); assertTrue(rs.next()); assertEquals(1, rs.getLong(1)); assertFalse(rs.next()); //i2 should be used since it contains s3||'_'||s4 i - String query = "SELECT s2||'_'||s3 FROM v WHERE k2=1 AND (s2||'_'||s3)='abc_cab'"; + String query = "SELECT s2||'_'||s3 FROM " + viewName + " WHERE k2=1 AND (s2||'_'||s3)='abc_cab'"; rs = conn.createStatement( ).executeQuery("EXPLAIN " + query); String queryPlan = QueryUtil.getExplainPlan(rs); assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER I2 [1,'abc_cab','foo']\n" + + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName2 + " [1,'abc_cab','foo']\n" + " SERVER FILTER BY FIRST KEY ONLY", queryPlan); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals("abc_cab", rs.getString(1)); assertFalse(rs.next()); - conn.createStatement().execute("ALTER VIEW v DROP COLUMN s4"); + conn.createStatement().execute("ALTER VIEW " + viewName + " DROP COLUMN s4"); //i2 cannot be used since s4 has been dropped from the view, so i1 will be used rs = conn.createStatement().executeQuery("EXPLAIN " + query); queryPlan = QueryUtil.getExplainPlan(rs); assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER I1 [1]\n" + + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName1 + " [1]\n" + " SERVER FILTER BY FIRST KEY ONLY AND ((\"S2\" || '_' || \"S3\") = 'abc_cab' AND \"S1\" = 'foo')", queryPlan); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); @@ -1254,17 +1290,19 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { @Test public void testExpressionThrowsException() throws Exception { - Connection conn = DriverManager.getConnection(getUrl()); + Connection conn = DriverManager.getConnection(getUrl()); + String dataTableName = generateRandomString(); + String indexName = generateRandomString(); try { - String ddl = "CREATE TABLE t (k1 INTEGER PRIMARY KEY, k2 INTEGER)"; + String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER PRIMARY KEY, k2 INTEGER)"; conn.createStatement().execute(ddl); - ddl = "CREATE INDEX i on t(k1/k2)"; + ddl = "CREATE INDEX " + indexName + " on " + dataTableName + "(k1/k2)"; conn.createStatement().execute(ddl); // upsert should succeed - conn.createStatement().execute("UPSERT INTO T VALUES(1,1)"); + conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1)"); conn.commit(); // divide by zero should fail - conn.createStatement().execute("UPSERT INTO T VALUES(1,0)"); + conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,0)"); conn.commit(); fail(); } catch (CommitException e) { @@ -1297,22 +1335,24 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String dataTableName = generateRandomString(); + String indexName = generateRandomString(); try { conn.createStatement().execute( - "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) " + "CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) " + (mutable ? "IMMUTABLE_ROWS=true" : "")); - String query = "SELECT * FROM t"; + String query = "SELECT * FROM " + dataTableName; ResultSet rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); String ddl = "CREATE " + (localIndex ? "LOCAL" : "") - + " INDEX idx ON t (REGEXP_SUBSTR(v,'id:\\\\w+'))"; + + " INDEX " + indexName + " ON " + dataTableName + " (REGEXP_SUBSTR(v,'id:\\\\w+'))"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); - query = "SELECT * FROM idx"; + query = "SELECT * FROM " + indexName; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)"); + stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)"); stmt.setString(1, "k1"); stmt.setString(2, "{id:id1}"); stmt.execute(); @@ -1321,16 +1361,16 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { stmt.execute(); conn.commit(); - query = "SELECT k FROM t WHERE REGEXP_SUBSTR(v,'id:\\\\w+') = 'id:id1'"; + query = "SELECT k FROM " + dataTableName + " WHERE REGEXP_SUBSTR(v,'id:\\\\w+') = 'id:id1'"; rs = conn.createStatement().executeQuery("EXPLAIN " + query); if (localIndex) { assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T [1,'id:id1']\n" + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'id:id1']\n" + " SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); } else { assertEquals( - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['id:id1']\n" + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['id:id1']\n" + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs)); } @@ -1368,27 +1408,26 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { boolean localIndex) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); - String nameSuffix = "t" + (mutable ? "_mutable" : "_immutable") + (localIndex ? "_local" : "_global"); - String tableName = "t" + nameSuffix; - String indexName = "idx" + nameSuffix; - try { + String dataTableName = generateRandomString(); + String indexName = generateRandomString(); + try { conn.createStatement().execute( - "CREATE TABLE " + tableName + " (" + "CREATE TABLE " + dataTableName + " (" + "pk1 VARCHAR not null, " + "pk2 VARCHAR not null, " + "CONSTRAINT PK PRIMARY KEY (pk1, pk2))" + (!mutable ? "IMMUTABLE_ROWS=true" : "")); - String query = "SELECT * FROM " + tableName; + String query = "SELECT * FROM " + dataTableName; ResultSet rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); conn.createStatement().execute( "CREATE " + (localIndex ? "LOCAL" : "") - + " INDEX " + indexName + " ON " + tableName + " (pk2, pk1)"); + + " INDEX " + indexName + " ON " + dataTableName + " (pk2, pk1)"); query = "SELECT * FROM " + indexName; rs = conn.createStatement().executeQuery(query); assertFalse(rs.next()); - PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?)"); + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)"); stmt.setString(1, "k11"); stmt.setString(2, "k21"); stmt.execute(); @@ -1401,7 +1440,7 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { assertEquals("k11", rs.getString(2)); assertFalse(rs.next()); - query = "SELECT * FROM " + tableName + " WHERE pk2='k21'"; + query = "SELECT * FROM " + dataTableName + " WHERE pk2='k21'"; rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals("k11", rs.getString(1));
