@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
protected static void initTableValues(byte[][] splits, long ts) throws Exception {
ensureTableCreated(getUrl(), "LongInKeyTest", splits, ts - 2);
// Insert all rows at ts
String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into " + "LongInKeyTest VALUES(?)");
stmt.setLong(1, 2);
stmt.execute();
conn.close();
}
@Test
public void testIntegerPK() throws Exception {
int[] testNumbers = {
Integer.MIN_VALUE,
Integer.MIN_VALUE + 1,
-2,
-1,
0,
1,
2,
Integer.MAX_VALUE - 1,
Integer.MAX_VALUE
};
ensureTableCreated(getUrl(), "PKIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO PKIntValueTest VALUES(?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setInt(1, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from PKIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk >= "
+ Integer.MIN_VALUE
+ " GROUP BY pk ORDER BY pk ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This case currently fails with an error message:
// "Overflow trying to get next key for [-1, -1, -1, -1]"
select = "SELECT count(*) FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk <= "
+ Integer.MAX_VALUE
+ " GROUP BY pk ORDER BY pk DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This case currently fails since it is not retrieving the negative values.
select = "SELECT count(*) FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk >= "
+ INTEGER_MIN_MINUS_ONE
+ " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This test case fails because it is not retrieving positive values.
select = "SELECT count(*) FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk <= "
+ INTEGER_MAX_PLUS_ONE
+ " GROUP BY pk ORDER BY pk DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
}
@Test
public void testBigIntKV() throws Exception {
// NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of
// bigint is not recognizable in the current version. As a result, we start with
// Long.MIN_VALUE+1 as the smallest value.
long[] testNumbers = {
Long.MIN_VALUE + 1,
Long.MIN_VALUE + 2,
-2L,
-1L,
0L,
1L,
2L,
Long.MAX_VALUE - 1,
Long.MAX_VALUE
};
ensureTableCreated(getUrl(), "KVBigIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO KVBigIntValueTest VALUES(?,?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setLong(1, i);
stmt.setLong(2, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from KVBigIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + (Long.MIN_VALUE + 1);
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVBigIntValueTest WHERE kv >= "
+ (Long.MIN_VALUE + 1)
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getLong(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + Long.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVBigIntValueTest WHERE kv <= "
+ Long.MAX_VALUE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getLong(1));
}
assertFalse(rs.next());
/* NOTE: This section currently fails due to the fact that we cannot parse literal values
that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
before enabling this section of the test.
select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + LONG_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + LONG_MIN_MINUS_ONE +
" GROUP BY kv ORDER BY kv ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + LONG_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + LONG_MAX_PLUS_ONE +
" GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length-1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
*/
}
@Test
public void testIntegerKV() throws Exception {
int[] testNumbers = {
Integer.MIN_VALUE,
Integer.MIN_VALUE + 1,
-2,
-1,
0,
1,
2,
Integer.MAX_VALUE - 1,
Integer.MAX_VALUE
};
ensureTableCreated(getUrl(), "KVIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO KVIntValueTest VALUES(?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setInt(1, i);
stmt.setInt(2, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from KVIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv >= " + Integer.MIN_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv >= "
+ Integer.MIN_VALUE
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv <= " + Integer.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv <= "
+ Integer.MAX_VALUE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv >= " + INTEGER_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv >= "
+ INTEGER_MIN_MINUS_ONE
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv <= " + INTEGER_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv <= "
+ INTEGER_MAX_PLUS_ONE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
}
private long createTable() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), GROUPBYTEST_NAME, null, ts - 2);
return ts;
}