http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java index b906ae2..4357272 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java @@ -54,180 +54,217 @@ import com.google.common.collect.Lists; */ -public class SortOrderIT extends BaseHBaseManagedTimeIT { +public class SortOrderIT extends BaseHBaseManagedTimeTableReuseIT { - private static final String TABLE = "DescColumnSortOrderTest"; - @Test public void noOrder() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY)"; - runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}}); + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (pk VARCHAR NOT NULL PRIMARY KEY)"; + runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}}, + table); } @Test public void noOrderCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid, code))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid, code))"; Object[][] rows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}}; - runQueryTest(ddl, upsert("oid", "code"), rows, rows); + runQueryTest(ddl, upsert("oid", "code"), rows, rows, table); } @Test public void ascOrderInlinePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY ASC)"; - runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}}); + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (pk VARCHAR NOT NULL PRIMARY KEY ASC)"; + runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}}, + table); } @Test public void ascOrderCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid ASC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid ASC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}}; Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, table); } @Test public void descOrderInlinePK() throws Exception { + String table = generateRandomString(); for (String type : new String[]{"CHAR(2)", "VARCHAR"}) { - String ddl = "CREATE TABLE " + TABLE + " (pk ${type} NOT NULL PRIMARY KEY DESC)".replace("${type}", type); - runQueryTest(ddl, "pk", new Object[][]{{"aa"}, {"bb"}, {"cc"}}, new Object[][]{{"cc"}, {"bb"}, {"aa"}}); + String ddl = "CREATE table " + table + " (pk ${type} NOT NULL PRIMARY KEY DESC)".replace("${type}", type); + runQueryTest(ddl, "pk", new Object[][]{{"aa"}, {"bb"}, {"cc"}}, new Object[][]{{"cc"}, {"bb"}, {"aa"}}, + table); } } @Test public void descOrderCompositePK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}}; Object[][] expectedRows = new Object[][]{{"o3", 3}, {"o2", 2}, {"o1", 1}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, table); } @Test public void descOrderCompositePK2() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}}; Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, table); } @Test public void equalityDescInlinePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY DESC)"; - runQueryTest(ddl, upsert("pk"), new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"b"}}, new WhereCondition("pk", "=", "'b'")); + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (pk VARCHAR NOT NULL PRIMARY KEY DESC)"; + runQueryTest(ddl, upsert("pk"), new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"b"}}, new WhereCondition("pk", "=", "'b'"), + table); } @Test public void equalityDescCompositePK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "=", "'o2'")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "=", "'o2'"), + table); } @Test public void equalityDescCompositePK2() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "=", "2")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "=", "2"), + table); } @Test public void inDescCompositePK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "IN", "(2)")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "IN", "(2)"), + table); } @Test public void inDescCompositePK2() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "IN", "('o2')")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "IN", "('o2')"), + table); } @Test public void likeDescCompositePK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"a1", 1}, {"b2", 2}, {"c3", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", 2}}, new WhereCondition("oid", "LIKE", "('b%')")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", 2}}, new WhereCondition("oid", "LIKE", "('b%')"), + table); } @Test public void likeDescCompositePK2() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code CHAR(2) NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code CHAR(2) NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"a1", "11"}, {"b2", "22"}, {"c3", "33"}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", "22"}}, new WhereCondition("code", "LIKE", "('2%')")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", "22"}}, new WhereCondition("code", "LIKE", "('2%')"), + table); } @Test public void greaterThanDescCompositePK3() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}}; Object[][] expectedRows = new Object[][]{{"o1", 2}, {"o1", 1}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("code", "<", "3")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("code", "<", "3"), + table); } @Test public void substrDescCompositePK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(3) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(3) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; Object[][] insertedRows = new Object[][]{{"ao1", 1}, {"bo2", 2}, {"co3", 3}}; Object[][] expectedRows = new Object[][]{{"co3", 3}, {"bo2", 2}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 3, 1)", ">", "'1'")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 3, 1)", ">", "'1'"), + table); } @Test public void substrDescCompositePK2() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(4) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(4) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; Object[][] insertedRows = new Object[][]{{"aaaa", 1}, {"bbbb", 2}, {"cccd", 3}}; Object[][] expectedRows = new Object[][]{{"cccd", 3}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 4, 1)", "=", "'d'")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 4, 1)", "=", "'d'"), + table); } @Test public void substrFixedLengthDescPK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(3) PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(3) PRIMARY KEY DESC)"; Object[][] insertedRows = new Object[][]{{"a"}, {"ab"}}; Object[][] expectedRows = new Object[][]{{"ab"}, {"a"} }; - runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 1, 1)", "=", "'a'")); + runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 1, 1)", "=", "'a'"), + table); } @Test public void substrVarLengthDescPK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid VARCHAR PRIMARY KEY DESC)"; Object[][] insertedRows = new Object[][]{{"a"}, {"ab"}}; Object[][] expectedRows = new Object[][]{{"ab"}, {"a"} }; - runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 1, 1)", "=", "'a'")); + runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 1, 1)", "=", "'a'"), + table); } @Test public void likeVarLengthDescPK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid VARCHAR PRIMARY KEY DESC)"; Object[][] insertedRows = new Object[][]{{"a"}, {"ab"}}; Object[][] expectedRows = new Object[][]{{"ab"}, {"a"} }; - runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("oid", "like", "'a%'")); + runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("oid", "like", "'a%'"), + table); } @Test public void likeFixedLengthDescPK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(3) PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(3) PRIMARY KEY DESC)"; Object[][] insertedRows = new Object[][]{{"a"}, {"ab"}}; Object[][] expectedRows = new Object[][]{{"ab"}, {"a"} }; - runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("oid", "like", "'a%'")); + runQueryTest(ddl, upsert("oid"), insertedRows, expectedRows, new WhereCondition("oid", "like", "'a%'"), + table); } @Test public void decimalRangeDescPK1() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid DECIMAL PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid DECIMAL PRIMARY KEY DESC)"; Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute(ddl); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(4.99)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(4.0)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(5.0)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(5.001)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(5.999)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(6.0)"); - conn.createStatement().execute("UPSERT INTO " + TABLE + " VALUES(6.001)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(4.99)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(4.0)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(5.0)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(5.001)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(5.999)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(6.0)"); + conn.createStatement().execute("UPSERT INTO " + table + " VALUES(6.001)"); conn.commit(); - String query = "SELECT * FROM " + TABLE + " WHERE oid >= 5.0 AND oid < 6.0"; + String query = "SELECT * FROM " + table + " WHERE oid >= 5.0 AND oid < 6.0"; ResultSet rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertTrue(new BigDecimal("5.999").compareTo(rs.getBigDecimal(1)) == 0); @@ -240,193 +277,225 @@ public class SortOrderIT extends BaseHBaseManagedTimeIT { @Test public void lTrimDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{" o1 ", 1}, {" o2", 2}, {" o3", 3}}; Object[][] expectedRows = new Object[][]{{" o2", 2}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LTRIM(oid)", "=", "'o2'")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LTRIM(oid)", "=", "'o2'"), + table); } @Test public void lPadDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"aaaa", 1}, {"bbbb", 2}, {"cccc", 3}}; Object[][] expectedRows = new Object[][]{{"bbbb", 2}}; - runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LPAD(oid, 8, '123')", "=", "'1231bbbb'")); + runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LPAD(oid, 8, '123')", "=", "'1231bbbb'"), + table); } @Test public void countDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))"; Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}}; Object[][] expectedRows = new Object[][]{{3l}}; - runQueryTest(ddl, upsert("oid", "code"), select("COUNT(oid)"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("oid", "code"), select("COUNT(oid)"), insertedRows, expectedRows, + table); } @Test public void sumDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))"; Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}}; Object[][] expectedRows = new Object[][]{{60l, bdec(60.6), 96l}}; - runQueryTest(ddl, upsert("n1", "n2", "n3"), select("SUM(n1), SUM(n2), SUM(n3)"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3"), select("SUM(n1), SUM(n2), SUM(n3)"), insertedRows, expectedRows, + table); } @Test public void avgDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))"; Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}}; Object[][] expectedRows = new Object[][]{{new BigDecimal(bint(2), -1), bdec(20.2), BigDecimal.valueOf(32)}}; - runQueryTest(ddl, upsert("n1", "n2", "n3"), select("AVG(n1), AVG(n2), AVG(n3)"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3"), select("AVG(n1), AVG(n2), AVG(n3)"), insertedRows, expectedRows, + table); } @Test public void minDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))"; Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}}; Object[][] expectedRows = new Object[][]{{10, bdec(10.2), 21l}}; - runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MIN(n1), MIN(n2), MIN(n3)"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MIN(n1), MIN(n2), MIN(n3)"), insertedRows, expectedRows, + table); } @Test public void maxDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))"; Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}}; Object[][] expectedRows = new Object[][]{{30, bdec(30.2), 43l}}; - runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MAX(n1), MAX(n2), MAX(n3)"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MAX(n1), MAX(n2), MAX(n3)"), insertedRows, expectedRows, + table); } @Test public void havingSumDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (name CHAR(1) NOT NULL, code INTEGER NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (name CHAR(1) NOT NULL, code INTEGER NOT NULL " + "constraint pk primary key (name DESC, code DESC))"; Object[][] insertedRows = new Object[][]{{"a", 10}, {"a", 20}, {"b", 100}}; Object[][] expectedRows = new Object[][]{{"a", 30l}}; runQueryTest(ddl, upsert("name", "code"), select("name", "SUM(code)"), insertedRows, expectedRows, - new HavingCondition("name", "SUM(code) = 30")); + new HavingCondition("name", "SUM(code) = 30"), table); } @Test public void queryDescDateWithExplicitOrderBy() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (c1 CHAR(1) NOT NULL, c2 CHAR(1) NOT NULL, d1 DATE NOT NULL, c3 CHAR(1) NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (c1 CHAR(1) NOT NULL, c2 CHAR(1) NOT NULL, d1 DATE NOT NULL, c3 CHAR(1) NOT NULL " + "constraint pk primary key (c1, c2, d1 DESC, c3))"; Object[] row1 = {"1", "2", date(10, 11, 2001), "3"}; Object[] row2 = {"1", "2", date(10, 11, 2003), "3"}; Object[][] insertedRows = new Object[][]{row1, row2}; runQueryTest(ddl, upsert("c1", "c2", "d1", "c3"), select("c1, c2, d1", "c3"), insertedRows, new Object[][]{row2, row1}, - null, null, new OrderBy("d1", OrderBy.Direction.DESC)); + null, null, new OrderBy("d1", OrderBy.Direction.DESC), table); } @Test public void additionOnDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))"; Object[][] insertedRows = new Object[][]{ {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 1, 2001)}}; Object[][] expectedRows = new Object[][]{ {31l, bdec(32.2), 46l, date(3, 5, 2001)}, {21l, bdec(22.2), 35l, date(2, 10, 2001)}, {11l, bdec(12.2), 24l, date(1, 14, 2001)}}; - runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1+1, n2+2, n3+3", "d1+4"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1+1, n2+2, n3+3", "d1+4"), insertedRows, expectedRows, + table); } @Test public void subtractionOnDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))"; Object[][] insertedRows = new Object[][]{ {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 10, 2001)}}; Object[][] expectedRows = new Object[][]{ {29l, bdec(28.2), 40l, date(3, 6, 2001)}, {19l, bdec(18.2), 29l, date(2, 2, 2001)}, {9l, bdec(8.2), 18l, date(1, 6, 2001)}}; - runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1-1, n2-2, n3-3", "d1-4"), insertedRows, expectedRows); + runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1-1, n2-2, n3-3", "d1-4"), insertedRows, expectedRows, + table); } @Test public void lessThanLeadingDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))"; Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2012)}, {3, date(1, 1, 2013)}, {2, date(1, 1, 2011)}}; Object[][] expectedRows = new Object[][]{{1, date(1, 1, 2012)}}; - runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("id", "<", "2")); + runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("id", "<", "2"), + table); } @Test public void lessThanTrailingDescCompositePK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))"; Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2002)}, {3, date(1, 1, 2003)}, {2, date(1, 1, 2001)}}; Object[][] expectedRows = new Object[][]{{2, date(1, 1, 2001)}}; - runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("date", "<", "TO_DATE('02-02-2001','mm-dd-yyyy')")); + runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("date", "<", "TO_DATE('02-02-2001','mm-dd-yyyy')"), + table); } @Test public void descVarLengthPK() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (id VARCHAR PRIMARY KEY DESC)"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (id VARCHAR PRIMARY KEY DESC)"; Object[][] insertedRows = new Object[][]{{"a"}, {"ab"}, {"abc"}}; Object[][] expectedRows = new Object[][]{{"abc"}, {"ab"}, {"a"}}; runQueryTest(ddl, upsert("id"), select("id"), insertedRows, expectedRows, - null, null, new OrderBy("id", OrderBy.Direction.DESC)); + null, null, new OrderBy("id", OrderBy.Direction.DESC), table); } @Test public void descVarLengthAscPKGT() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; Object[][] insertedRows = new Object[][]{{0, null}, {1, "a"}, {2, "b"}, {3, "ba"}, {4, "baa"}, {5, "c"}, {6, "d"}}; Object[][] expectedRows = new Object[][]{{3}, {4}, {5}, {6}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k2", ">", "'b'"), null, null); + new WhereCondition("k2", ">", "'b'"), null, null, table); } @Test public void descVarLengthDescPKGT() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2 desc))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2 desc))"; Object[][] insertedRows = new Object[][]{{0, null}, {1, "a"}, {2, "b"}, {3, "ba"}, {4, "baa"}, {5, "c"}, {6, "d"}}; Object[][] expectedRows = new Object[][]{{3}, {4}, {5}, {6}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k2", ">", "'b'"), null, null); + new WhereCondition("k2", ">", "'b'"), null, null, table); } @Test public void descVarLengthDescPKLTE() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2 desc))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2 desc))"; Object[][] insertedRows = new Object[][]{{0, null}, {1, "a"}, {2, "b"}, {3, "ba"}, {4, "bb"}, {5, "bc"}, {6, "bba"}, {7, "c"}}; Object[][] expectedRows = new Object[][]{{1}, {2}, {3}, {4}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k2", "<=", "'bb'"), null, null); + new WhereCondition("k2", "<=", "'bb'"), null, null, table); } @Test public void descVarLengthAscPKLTE() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 INTEGER NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; Object[][] insertedRows = new Object[][]{{0, null}, {1, "a"}, {2, "b"}, {3, "ba"}, {4, "bb"}, {5, "bc"}, {6, "bba"}, {7, "c"}}; Object[][] expectedRows = new Object[][]{{1}, {2}, {3}, {4}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k2", "<=", "'bb'"), null, null); + new WhereCondition("k2", "<=", "'bb'"), null, null, table); } @Test public void varLengthAscLT() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}}; Object[][] expectedRows = new Object[][]{{"a"}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k1", "<", "'b'"), null, null); + new WhereCondition("k1", "<", "'b'"), null, null, table); } @Test public void varLengthDescLT() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}}; Object[][] expectedRows = new Object[][]{{"a"}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k1", "<", "'b'"), null, null); + new WhereCondition("k1", "<", "'b'"), null, null, table); } @Test public void varLengthDescGT() throws Exception { - String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; + String table = generateRandomString(); + String ddl = "CREATE table " + table + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}, {"ba","a"}}; Object[][] expectedRows = new Object[][]{{"ba"}}; runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, - new WhereCondition("k1", ">", "'b'"), null, null); + new WhereCondition("k1", ">", "'b'"), null, null, table); } @Test @@ -489,35 +558,37 @@ public class SortOrderIT extends BaseHBaseManagedTimeIT { } } - private void runQueryTest(String ddl, String columnName, Object[][] rows, Object[][] expectedRows) throws Exception { - runQueryTest(ddl, new String[]{columnName}, rows, expectedRows, null); + private void runQueryTest(String ddl, String columnName, Object[][] rows, + Object[][] expectedRows, String table) throws Exception { + runQueryTest(ddl, new String[]{columnName}, rows, expectedRows, null, table); } - private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows) throws Exception { - runQueryTest(ddl, columnNames, rows, expectedRows, null); + private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, + Object[][] expectedRows, String table) throws Exception { + runQueryTest(ddl, columnNames, rows, expectedRows, null, table); } - private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows, WhereCondition condition) throws Exception { - runQueryTest(ddl, columnNames, columnNames, rows, expectedRows, condition, null, null); + private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows, WhereCondition condition, + String table) throws Exception { + runQueryTest(ddl, columnNames, columnNames, rows, expectedRows, condition, null, null, + table); } - private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows) throws Exception { - runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, null, null); + private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, + String table) throws Exception { + runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, null, null, table); } - private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, HavingCondition havingCondition) throws Exception { - runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, havingCondition, null); + private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, HavingCondition havingCondition, + String table) throws Exception { + runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, havingCondition, null, + table); } - private void runQueryTest( - String ddl, - String[] columnNames, - String[] projections, - Object[][] rows, Object[][] expectedRows, - WhereCondition whereCondition, - HavingCondition havingCondition, - OrderBy orderBy) + private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, + WhereCondition whereCondition, HavingCondition havingCondition, OrderBy orderBy, + String table) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -531,7 +602,7 @@ public class SortOrderIT extends BaseHBaseManagedTimeIT { String columns = appendColumns(columnNames); String placeholders = appendPlaceholders(columnNames); - String dml = "UPSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders +")"; + String dml = "UPSERT INTO " + table + " (" + columns + ") VALUES(" + placeholders +")"; PreparedStatement stmt = conn.prepareStatement(dml); for (int row = 0; row < rows.length; row++) { @@ -543,7 +614,7 @@ public class SortOrderIT extends BaseHBaseManagedTimeIT { } conn.commit(); - String selectClause = "SELECT " + appendColumns(projections) + " FROM " + TABLE; + String selectClause = "SELECT " + appendColumns(projections) + " FROM " + table; for (WhereCondition whereConditionClause : new WhereCondition[]{whereCondition, WhereCondition.reverse(whereCondition)}) { String query = WhereCondition.appendWhere(whereConditionClause, selectClause);
http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/StatementHintsIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StatementHintsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StatementHintsIT.java index 70246f9..887a30e 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StatementHintsIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StatementHintsIT.java @@ -37,7 +37,9 @@ import org.junit.Test; */ -public class StatementHintsIT extends BaseHBaseManagedTimeIT { +public class StatementHintsIT extends BaseHBaseManagedTimeTableReuseIT { + + private static final String TABLE_NAME = generateRandomString(); private static void initTableValues() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -45,7 +47,7 @@ public class StatementHintsIT extends BaseHBaseManagedTimeIT { conn.setAutoCommit(false); try { - String ddl = "CREATE TABLE test_table" + + String ddl = "CREATE TABLE " + TABLE_NAME + " (a_integer integer not null, \n" + " a_string varchar not null, \n" + " a_id char(3) not null,\n" + @@ -56,7 +58,7 @@ public class StatementHintsIT extends BaseHBaseManagedTimeIT { String query; PreparedStatement stmt; - query = "UPSERT INTO test_table" + query = "UPSERT INTO " + TABLE_NAME + "(a_integer, a_string, a_id, b_string) " + "VALUES(?,?,?,?)"; stmt = conn.prepareStatement(query); @@ -108,7 +110,7 @@ public class StatementHintsIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl(), props); try { initTableValues(); - String query = "SELECT /*+ RANGE_SCAN */ * FROM test_table WHERE a_integer IN (1, 2, 3, 4)"; + String query = "SELECT /*+ RANGE_SCAN */ * FROM " + TABLE_NAME + " WHERE a_integer IN (1, 2, 3, 4)"; PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery(); @@ -145,7 +147,7 @@ public class StatementHintsIT extends BaseHBaseManagedTimeIT { try { initTableValues(); // second slot on the - String query = "SELECT /*+ SKIP_SCAN */ * FROM test_table WHERE a_string = 'abc'"; + String query = "SELECT /*+ SKIP_SCAN */ * FROM " + TABLE_NAME + " WHERE a_string = 'abc'"; PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery(); http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/StoreNullsIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StoreNullsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StoreNullsIT.java index 8efeefc..cbce02e 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StoreNullsIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StoreNullsIT.java @@ -35,6 +35,7 @@ import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.SchemaUtil; import org.junit.After; import org.junit.Before; +import org.junit.BeforeClass; import org.junit.Test; import java.io.IOException; @@ -57,24 +58,26 @@ import static org.junit.Assert.assertTrue; * functionality allows having row-level versioning (similar to how KEEP_DELETED_CELLS works), but * also allows permanently deleting a row. */ -public class StoreNullsIT extends BaseHBaseManagedTimeIT { +public class StoreNullsIT extends BaseHBaseManagedTimeTableReuseIT { private static final Log LOG = LogFactory.getLog(StoreNullsIT.class); + private static final String WITH_NULLS = generateRandomString(); + private static final String WITHOUT_NULLS = generateRandomString(); - private Connection conn; - private Statement stmt; + private static Connection conn; + private static Statement stmt; - @Before - public void setUp() throws SQLException { + @BeforeClass + public static void setUp() throws SQLException { conn = DriverManager.getConnection(getUrl()); conn.setAutoCommit(true); stmt = conn.createStatement(); - stmt.execute("CREATE TABLE with_nulls (" + + stmt.execute("CREATE TABLE " + WITH_NULLS + " (" + "id SMALLINT NOT NULL PRIMARY KEY, " + "name VARCHAR) " + "STORE_NULLS = true, VERSIONS = 1000, KEEP_DELETED_CELLS = false"); - stmt.execute("CREATE TABLE without_nulls (" + + stmt.execute("CREATE TABLE " + WITHOUT_NULLS + " (" + "id SMALLINT NOT NULL PRIMARY KEY, " + "name VARCHAR) " + "VERSIONS = 1000, KEEP_DELETED_CELLS = false"); @@ -88,19 +91,19 @@ public class StoreNullsIT extends BaseHBaseManagedTimeIT { @Test public void testQueryingHistory() throws SQLException, InterruptedException, IOException { - stmt.executeUpdate("UPSERT INTO with_nulls VALUES (1, 'v1')"); - stmt.executeUpdate("UPSERT INTO without_nulls VALUES (1, 'v1')"); + stmt.executeUpdate("UPSERT INTO " + WITH_NULLS + " VALUES (1, 'v1')"); + stmt.executeUpdate("UPSERT INTO " + WITHOUT_NULLS + " VALUES (1, 'v1')"); Thread.sleep(10L); long afterFirstInsert = System.currentTimeMillis(); Thread.sleep(10L); - stmt.executeUpdate("UPSERT INTO with_nulls VALUES (1, null)"); - stmt.executeUpdate("UPSERT INTO without_nulls VALUES (1, null)"); + stmt.executeUpdate("UPSERT INTO " + WITH_NULLS + " VALUES (1, null)"); + stmt.executeUpdate("UPSERT INTO " + WITHOUT_NULLS + " VALUES (1, null)"); Thread.sleep(10L); - doMajorCompaction("with_nulls"); - doMajorCompaction("without_nulls"); + doMajorCompaction(WITH_NULLS); + doMajorCompaction(WITHOUT_NULLS); Properties historicalProps = new Properties(); historicalProps.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, @@ -108,13 +111,14 @@ public class StoreNullsIT extends BaseHBaseManagedTimeIT { Connection historicalConn = DriverManager.getConnection(getUrl(), historicalProps); Statement historicalStmt = historicalConn.createStatement(); - ResultSet rs = historicalStmt.executeQuery("SELECT name FROM with_nulls WHERE id = 1"); + ResultSet rs = historicalStmt.executeQuery( + "SELECT name FROM " + WITH_NULLS + " WHERE id = 1"); assertTrue(rs.next()); assertEquals("v1", rs.getString(1)); rs.close(); // The single null wipes out all history for a field if STORE_NULLS is not enabled - rs = historicalStmt.executeQuery("SELECT name FROM without_nulls WHERE id = 1"); + rs = historicalStmt.executeQuery("SELECT name FROM " + WITHOUT_NULLS + " WHERE id = 1"); assertTrue(rs.next()); assertNull(rs.getString(1)); rs.close(); @@ -126,19 +130,19 @@ public class StoreNullsIT extends BaseHBaseManagedTimeIT { // Row deletes should work in the same way regardless of what STORE_NULLS is set to @Test public void testDeletes() throws SQLException, InterruptedException, IOException { - stmt.executeUpdate("UPSERT INTO with_nulls VALUES (1, 'v1')"); - stmt.executeUpdate("UPSERT INTO without_nulls VALUES (1, 'v1')"); + stmt.executeUpdate("UPSERT INTO " + WITH_NULLS + " VALUES (1, 'v1')"); + stmt.executeUpdate("UPSERT INTO " + WITHOUT_NULLS + " VALUES (1, 'v1')"); Thread.sleep(10L); long afterFirstInsert = System.currentTimeMillis(); Thread.sleep(10L); - stmt.executeUpdate("DELETE FROM with_nulls WHERE id = 1"); - stmt.executeUpdate("DELETE FROM without_nulls WHERE id = 1"); + stmt.executeUpdate("DELETE FROM " + WITH_NULLS + " WHERE id = 1"); + stmt.executeUpdate("DELETE FROM " + WITHOUT_NULLS + " WHERE id = 1"); Thread.sleep(10L); - doMajorCompaction("with_nulls"); - doMajorCompaction("without_nulls"); + doMajorCompaction(WITH_NULLS); + doMajorCompaction(WITHOUT_NULLS); Properties historicalProps = new Properties(); historicalProps.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, @@ -148,11 +152,12 @@ public class StoreNullsIT extends BaseHBaseManagedTimeIT { // The row should be completely gone for both tables now - ResultSet rs = historicalStmt.executeQuery("SELECT name FROM with_nulls WHERE id = 1"); + ResultSet rs = historicalStmt.executeQuery( + "SELECT name FROM " + WITH_NULLS + " WHERE id = 1"); assertFalse(rs.next()); rs.close(); - rs = historicalStmt.executeQuery("SELECT name FROM without_nulls WHERE id = 1"); + rs = historicalStmt.executeQuery("SELECT name FROM " + WITHOUT_NULLS + " WHERE id = 1"); assertFalse(rs.next()); rs.close(); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/StringToArrayFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StringToArrayFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StringToArrayFunctionIT.java index 6e6ac9f..d341646 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/StringToArrayFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/StringToArrayFunctionIT.java @@ -25,14 +25,22 @@ import java.sql.*; import org.apache.phoenix.schema.types.PVarchar; import org.apache.phoenix.schema.types.PhoenixArray; +import org.junit.BeforeClass; import org.junit.Test; -public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { +public class StringToArrayFunctionIT extends BaseHBaseManagedTimeTableReuseIT { - private void initTables(Connection conn) throws Exception { - String ddl = "CREATE TABLE regions (region_name VARCHAR PRIMARY KEY, string1 VARCHAR, string2 CHAR(50), delimiter1 VARCHAR, delimiter2 CHAR(20), nullstring1 VARCHAR, nullstring2 CHAR(20))"; + private static final String TABLE_NAME = generateRandomString(); + + @BeforeClass + public static void initTables() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String ddl = "CREATE TABLE " + TABLE_NAME + + " (region_name VARCHAR PRIMARY KEY, string1 VARCHAR, string2 CHAR(50), delimiter1 VARCHAR, delimiter2 CHAR(20), nullstring1 VARCHAR, nullstring2 CHAR(20))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO regions(region_name, string1, string2, delimiter1, delimiter2, nullstring1, nullstring2) VALUES('SF Bay Area'," + + String dml = "UPSERT INTO " + TABLE_NAME + + "(region_name, string1, string2, delimiter1, delimiter2, nullstring1, nullstring2) VALUES('SF Bay Area'," + + "'a,b,c,d'," + "'1.2.3.4'," + "','," + @@ -48,10 +56,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction1() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string1, delimiter1) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string1, delimiter1) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", "c", "d"}); @@ -63,10 +72,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string1, delimiter1, nullstring1) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string1, delimiter1, nullstring1) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", null, "d"}); @@ -78,10 +88,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction3() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string1, delimiter1, 'a') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string1, delimiter1, 'a') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{null, "b", "c", "d"}); @@ -93,10 +104,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction4() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string1, delimiter1, 'd') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string1, delimiter1, 'd') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", "c", null}); @@ -108,10 +120,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction5() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string2, delimiter2) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string2, delimiter2) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"1", "2", "3", "4"}); @@ -123,10 +136,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction6() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string2, delimiter2, nullstring2) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string2, delimiter2, nullstring2) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"1", "2", null, "4"}); @@ -138,10 +152,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction7() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string2, delimiter2, '1') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string2, delimiter2, '1') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{null, "2", "3", "4"}); @@ -153,10 +168,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction8() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(string2, delimiter2, '4') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(string2, delimiter2, '4') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"1", "2", "3", null}); @@ -168,10 +184,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction9() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(region_name, ' ', '4') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(region_name, ' ', '4') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"SF", "Bay", "Area"}); @@ -183,10 +200,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction10() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY('hello,hello,hello', delimiter1) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY('hello,hello,hello', delimiter1) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"hello", "hello", "hello"}); @@ -198,10 +216,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction11() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY('a,hello,hello,hello,b', ',', 'hello') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY('a,hello,hello,hello,b', ',', 'hello') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", null, null, null, "b"}); @@ -213,10 +232,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunction12() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY('b.a.b', delimiter2, 'b') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY('b.a.b', delimiter2, 'b') FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{null, "a", null}); @@ -228,10 +248,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionWithNestedFunctions1() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT ARRAY_LENGTH(STRING_TO_ARRAY('a, b, c', ', ')) FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT ARRAY_LENGTH(STRING_TO_ARRAY('a, b, c', ', ')) FROM " + TABLE_NAME + + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); @@ -241,10 +262,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionWithNestedFunctions2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], delimiter2), delimiter2, 'b') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], delimiter2), delimiter2, 'b') FROM " + + TABLE_NAME + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", null, "c"}); @@ -256,10 +278,11 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionWithNestedFunctions3() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT STRING_TO_ARRAY(ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], delimiter2), ARRAY_ELEM(ARRAY[',', '.'], 2), 'b') FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT STRING_TO_ARRAY(ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], delimiter2), ARRAY_ELEM(ARRAY[',', '.'], 2), 'b') FROM " + + TABLE_NAME + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", null, "c"}); @@ -272,15 +295,19 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { public void testStringToArrayFunctionWithUpsert1() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE regions (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; + String tableName = generateRandomString(); + String ddl = + "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO regions(region_name,varchars) VALUES('SF Bay Area', STRING_TO_ARRAY('hello, world, :-)', ', '))"; + String dml = "UPSERT INTO " + tableName + + "(region_name,varchars) VALUES('SF Bay Area', STRING_TO_ARRAY('hello, world, :-)', ', '))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT varchars FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT varchars FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"hello", "world", ":-)"}); @@ -293,15 +320,19 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { public void testStringToArrayFunctionWithUpsert2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE regions (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; + String tableName = generateRandomString(); + String ddl = + "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO regions(region_name,varchars) VALUES('SF Bay Area', STRING_TO_ARRAY('a, b, -, c', ', ', '-'))"; + String dml = "UPSERT INTO " + tableName + + "(region_name,varchars) VALUES('SF Bay Area', STRING_TO_ARRAY('a, b, -, c', ', ', '-'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT varchars FROM regions WHERE region_name = 'SF Bay Area'"); + rs = conn.createStatement().executeQuery( + "SELECT varchars FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", null, "c"}); @@ -314,25 +345,32 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { public void testStringToArrayFunctionWithUpsertSelect1() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE source (region_name VARCHAR PRIMARY KEY, varchar VARCHAR)"; + String table1 = generateRandomString(); + String ddl = + "CREATE TABLE " + table1 + " (region_name VARCHAR PRIMARY KEY, varchar VARCHAR)"; conn.createStatement().execute(ddl); - ddl = "CREATE TABLE target (region_name VARCHAR PRIMARY KEY, varchars VARCHAR[])"; + String table2 = generateRandomString(); + ddl = "CREATE TABLE " + table2 + " (region_name VARCHAR PRIMARY KEY, varchars VARCHAR[])"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO source(region_name, varchar) VALUES('SF Bay Area', 'a,b,c,d')"; + String dml = + "UPSERT INTO " + table1 + "(region_name, varchar) VALUES('SF Bay Area', 'a,b,c,d')"; conn.createStatement().execute(dml); - dml = "UPSERT INTO source(region_name, varchar) VALUES('SF Bay Area2', '1,2,3,4')"; + dml = "UPSERT INTO " + table1 + "(region_name, varchar) VALUES('SF Bay Area2', '1,2,3,4')"; conn.createStatement().execute(dml); conn.commit(); - dml = "UPSERT INTO target(region_name, varchars) SELECT region_name, STRING_TO_ARRAY(varchar, ',') FROM source"; + dml = + "UPSERT INTO " + table2 + + "(region_name, varchars) SELECT region_name, STRING_TO_ARRAY(varchar, ',') FROM " + + table1; conn.createStatement().execute(dml); conn.commit(); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT varchars FROM target"); + rs = conn.createStatement().executeQuery("SELECT varchars FROM " + table2); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", "c", "d"}); @@ -350,25 +388,35 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { public void testStringToArrayFunctionWithUpsertSelect2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE source (region_name VARCHAR PRIMARY KEY, varchar VARCHAR)"; + String sourceTable = generateRandomString(); + + String ddl = + "CREATE TABLE " + sourceTable + " (region_name VARCHAR PRIMARY KEY, varchar VARCHAR)"; conn.createStatement().execute(ddl); - ddl = "CREATE TABLE target (region_name VARCHAR PRIMARY KEY, varchars VARCHAR[])"; + String targetTable = generateRandomString(); + ddl = "CREATE TABLE " + targetTable + + " (region_name VARCHAR PRIMARY KEY, varchars VARCHAR[])"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO source(region_name, varchar) VALUES('SF Bay Area', 'a,b,-,c,d')"; + String dml = "UPSERT INTO " + sourceTable + + "(region_name, varchar) VALUES('SF Bay Area', 'a,b,-,c,d')"; conn.createStatement().execute(dml); - dml = "UPSERT INTO source(region_name, varchar) VALUES('SF Bay Area2', '1,2,-,3,4')"; + dml = "UPSERT INTO " + sourceTable + + "(region_name, varchar) VALUES('SF Bay Area2', '1,2,-,3,4')"; conn.createStatement().execute(dml); conn.commit(); - dml = "UPSERT INTO target(region_name, varchars) SELECT region_name, STRING_TO_ARRAY(varchar, ',', '-') FROM source"; + dml = + "UPSERT INTO " + targetTable + + "(region_name, varchars) SELECT region_name, STRING_TO_ARRAY(varchar, ',', '-') FROM " + + sourceTable; conn.createStatement().execute(dml); conn.commit(); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT varchars FROM target"); + rs = conn.createStatement().executeQuery("SELECT varchars FROM " + targetTable); assertTrue(rs.next()); PhoenixArray expected = new PhoenixArray(PVarchar.INSTANCE, new Object[]{"a", "b", null, "c", "d"}); @@ -385,10 +433,10 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionInWhere1() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT region_name FROM regions WHERE ARRAY['a', 'b', 'c', 'd']=STRING_TO_ARRAY(string1, delimiter1)"); + rs = conn.createStatement().executeQuery("SELECT region_name FROM " + TABLE_NAME + + " WHERE ARRAY['a', 'b', 'c', 'd']=STRING_TO_ARRAY(string1, delimiter1)"); assertTrue(rs.next()); assertEquals("SF Bay Area", rs.getString(1)); @@ -398,10 +446,10 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionInWhere2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT region_name FROM regions WHERE 'a'=ANY(STRING_TO_ARRAY(string1, delimiter1))"); + rs = conn.createStatement().executeQuery("SELECT region_name FROM " + TABLE_NAME + + " WHERE 'a'=ANY(STRING_TO_ARRAY(string1, delimiter1))"); assertTrue(rs.next()); assertEquals("SF Bay Area", rs.getString(1)); @@ -411,10 +459,10 @@ public class StringToArrayFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testStringToArrayFunctionInWhere3() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - initTables(conn); ResultSet rs; - rs = conn.createStatement().executeQuery("SELECT region_name FROM regions WHERE 'a'=ALL(STRING_TO_ARRAY('a,a,a,', delimiter1))"); + rs = conn.createStatement().executeQuery("SELECT region_name FROM " + TABLE_NAME + + " WHERE 'a'=ALL(STRING_TO_ARRAY('a,a,a,', delimiter1))"); assertTrue(rs.next()); assertEquals("SF Bay Area", rs.getString(1)); http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java index 324c494..e899eb2 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java @@ -34,22 +34,25 @@ import org.apache.phoenix.exception.SQLExceptionCode; import org.junit.Test; -public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { +public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeTableReuseIT { @Test public void testTimezoneOffset() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST" + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; + String dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; conn.createStatement().execute(dml); - dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))"; + dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( - "SELECT k1, dates, TIMEZONE_OFFSET('Indian/Cocos', dates) FROM TIMEZONE_OFFSET_TEST"); + "SELECT k1, dates, TIMEZONE_OFFSET('Indian/Cocos', dates) FROM " + timezone_offset_test); assertTrue(rs.next()); assertEquals(390, rs.getInt(3)); @@ -61,16 +64,19 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testUnknownTimezone() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST" + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; + String dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; conn.createStatement().execute(dml); conn.commit(); try { ResultSet rs = conn.createStatement().executeQuery( - "SELECT k1, dates, TIMEZONE_OFFSET('Unknown_Timezone', dates) FROM TIMEZONE_OFFSET_TEST"); + "SELECT k1, dates, TIMEZONE_OFFSET('Unknown_Timezone', dates) FROM " + + timezone_offset_test); rs.next(); assertEquals(0, rs.getInt(3)); @@ -83,18 +89,21 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testInRowKeyDSTTimezoneDesc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST " + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " " + "(k1 INTEGER NOT NULL, dates DATE NOT NULL CONSTRAINT pk PRIMARY KEY (k1, dates DESC))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; + String dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))"; conn.createStatement().execute(dml); - dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))"; + dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, TIMEZONE_OFFSET('Europe/Prague', dates)" - + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC"); + + "FROM " + timezone_offset_test + " ORDER BY k1 ASC"); assertTrue(rs.next()); assertEquals(60, rs.getInt(3)); @@ -105,16 +114,17 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testBothParametersNull() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST " + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " " + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, null, null)"; + String dml = "UPSERT INTO " + timezone_offset_test + " (k1, dates, v1) VALUES (2, null, null)"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)" - + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC"); + + "FROM " + timezone_offset_test + " ORDER BY k1 ASC"); assertTrue(rs.next()); rs.getInt(3); @@ -124,16 +134,18 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void timezoneParameterNull() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST " + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " " + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, TO_DATE('2014-06-02 00:00:00'), null)"; + String dml = "UPSERT INTO " + timezone_offset_test + + " (k1, dates, v1) VALUES (2, TO_DATE('2014-06-02 00:00:00'), null)"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)" - + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC"); + + "FROM " + timezone_offset_test + " ORDER BY k1 ASC"); assertTrue(rs.next()); rs.getInt(3); @@ -143,16 +155,18 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void dateParameterNull() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST " + String timezone_offset_test = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test + " " + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, null, 'Asia/Aden')"; + String dml = + "UPSERT INTO " + timezone_offset_test + " (k1, dates, v1) VALUES (2, null, 'Asia/Aden')"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)" - + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC"); + + "FROM " + timezone_offset_test + " ORDER BY k1 ASC"); assertTrue(rs.next()); rs.getInt(3); @@ -162,9 +176,10 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { @Test public void testInsertingRetrivingTimestamp() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String ddl = "CREATE TABLE T (K INTEGER NOT NULL PRIMARY KEY, V TIMESTAMP)"; + String t = generateRandomString(); + String ddl = "CREATE TABLE " + t + " (K INTEGER NOT NULL PRIMARY KEY, V TIMESTAMP)"; conn.createStatement().execute(ddl); - String dml = "UPSERT INTO T VALUES (?, ?)"; + String dml = "UPSERT INTO " + t + " VALUES (?, ?)"; PreparedStatement stmt = conn.prepareStatement(dml); stmt.setInt(1, 1); Calendar cal = Calendar.getInstance(); @@ -173,7 +188,7 @@ public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT { stmt.setTimestamp(2, new Timestamp(time), cal); stmt.executeUpdate(); conn.commit(); - String query = "SELECT V FROM T"; + String query = "SELECT V FROM " + t; ResultSet rs = conn.createStatement().executeQuery(query); rs.next(); assertEquals(new Timestamp(time), rs.getTimestamp(1)); http://git-wip-us.apache.org/repos/asf/phoenix/blob/ea3b8eaf/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java index 4b4f215..a0b3939 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java @@ -37,7 +37,7 @@ import org.apache.phoenix.util.PropertiesUtil; import org.junit.Test; -public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { +public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeTableReuseIT { public UpsertSelectAutoCommitIT() { } @@ -47,14 +47,17 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); - conn.createStatement().execute("CREATE TABLE atable (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" + + String atable = generateRandomString(); + conn.createStatement().execute("CREATE TABLE " + atable + + " (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" + + "CONSTRAINT pk PRIMARY KEY (organization_id, entity_id))"); String tenantId = getOrganizationId(); // Insert all rows at ts PreparedStatement stmt = conn.prepareStatement( - "upsert into " + - "ATABLE(" + + "upsert into " + atable + + "(" + " ORGANIZATION_ID, " + " ENTITY_ID, " + " A_STRING " + @@ -65,7 +68,7 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { stmt.setString(3, A_VALUE); stmt.execute(); - String query = "SELECT entity_id, a_string FROM ATABLE"; + String query = "SELECT entity_id, a_string FROM " + atable; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); @@ -73,12 +76,15 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { assertEquals(ROW1, rs.getString(1)); assertEquals(A_VALUE, rs.getString(2)); assertFalse(rs.next()); - - conn.createStatement().execute("CREATE TABLE atable2 (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" + + + String atable2 = generateRandomString(); + conn.createStatement().execute("CREATE TABLE " + atable2 + + " (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" + + "CONSTRAINT pk PRIMARY KEY (organization_id, entity_id DESC))"); - conn.createStatement().execute("UPSERT INTO atable2 SELECT * FROM ATABLE"); - query = "SELECT entity_id, a_string FROM ATABLE2"; + conn.createStatement().execute("UPSERT INTO " + atable2 + " SELECT * FROM " + atable); + query = "SELECT entity_id, a_string FROM " + atable2; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -92,14 +98,17 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { @Test public void testDynamicUpsertSelect() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); - String cursorDDL = " CREATE TABLE IF NOT EXISTS CURSOR (ORGANIZATION_ID VARCHAR(15) NOT NULL, \n" + String tableName = generateRandomString(); + String cursorDDL = " CREATE TABLE IF NOT EXISTS " + tableName + + " (ORGANIZATION_ID VARCHAR(15) NOT NULL, \n" + "QUERY_ID VARCHAR(15) NOT NULL, \n" + "CURSOR_ORDER UNSIGNED_LONG NOT NULL, \n" + "CONSTRAINT API_HBASE_CURSOR_STORAGE_PK PRIMARY KEY (ORGANIZATION_ID, QUERY_ID, CURSOR_ORDER))\n" + "SALT_BUCKETS = 4"; conn.createStatement().execute(cursorDDL); - - String dataTableDDL = "CREATE TABLE IF NOT EXISTS PLINYTEST" + + + String tableName2 = generateRandomString(); + String dataTableDDL = "CREATE TABLE IF NOT EXISTS " + tableName2 + "(" + "ORGANIZATION_ID CHAR(15) NOT NULL, " + "PLINY_ID CHAR(15) NOT NULL, " + @@ -115,7 +124,7 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { conn.createStatement().execute(dataTableDDL); PreparedStatement stmt = null; - String upsert = "UPSERT INTO PLINYTEST VALUES (?, ?, ?, ?)"; + String upsert = "UPSERT INTO " + tableName2 + " VALUES (?, ?, ?, ?)"; stmt = conn.prepareStatement(upsert); stmt.setString(1, getOrganizationId()); stmt.setString(2, "aaaaaaaaaaaaaaa"); @@ -124,7 +133,10 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { stmt.executeUpdate(); conn.commit(); - String upsertSelect = "UPSERT INTO CURSOR (ORGANIZATION_ID, QUERY_ID, CURSOR_ORDER, PLINY_ID CHAR(15),CREATED_DATE DATE) SELECT ?, ?, ?, PLINY_ID, CREATED_DATE FROM PLINYTEST WHERE ORGANIZATION_ID = ?"; + String upsertSelect = "UPSERT INTO " + tableName + + + " (ORGANIZATION_ID, QUERY_ID, CURSOR_ORDER, PLINY_ID CHAR(15),CREATED_DATE DATE) SELECT ?, ?, ?, PLINY_ID, CREATED_DATE FROM " + + tableName2 + " WHERE ORGANIZATION_ID = ?"; stmt = conn.prepareStatement(upsertSelect); String orgId = getOrganizationId(); stmt.setString(1, orgId); @@ -146,12 +158,16 @@ public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); conn.createStatement().execute("CREATE SEQUENCE keys"); - conn.createStatement().execute("CREATE TABLE foo (pk INTEGER PRIMARY KEY, val INTEGER)"); + String tableName = generateRandomString(); + conn.createStatement().execute( + "CREATE TABLE " + tableName + " (pk INTEGER PRIMARY KEY, val INTEGER)"); - conn.createStatement().execute("UPSERT INTO foo VALUES (NEXT VALUE FOR keys,1)"); + conn.createStatement().execute( + "UPSERT INTO " + tableName + " VALUES (NEXT VALUE FOR keys,1)"); for (int i=0; i<6; i++) { Statement stmt = conn.createStatement(); - int upsertCount = stmt.executeUpdate("UPSERT INTO foo SELECT NEXT VALUE FOR keys, val FROM foo"); + int upsertCount = stmt.executeUpdate( + "UPSERT INTO " + tableName + " SELECT NEXT VALUE FOR keys, val FROM " + tableName); assertEquals((int)Math.pow(2, i), upsertCount); } conn.close();
