@Test
public void testTxnClosedCorrecty() throws Exception {
String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery(selectSql);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
// verify rows can be read even though commit has not been called
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
conn.close();
// wait for any open txns to time out
Thread.sleep(DEFAULT_TXN_TIMEOUT_SECONDS * 1000 + 10000);
assertTrue("There should be no invalid transactions", txManager.getInvalidSize() == 0);
}
}
@Test
public void testInflightDeleteNotSeen() throws Exception {
String selectSQL = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn1 = DriverManager.getConnection(getUrl());
Connection conn2 = DriverManager.getConnection(getUrl())) {
conn1.setAutoCommit(false);
conn2.setAutoCommit(true);
ResultSet rs = conn1.createStatement().executeQuery(selectSQL);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn1.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
conn1.commit();
rs = conn1.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
String delete = "DELETE FROM " + FULL_TABLE_NAME + " WHERE varchar_pk = 'varchar1'";
stmt = conn1.prepareStatement(delete);
int count = stmt.executeUpdate();
assertEquals(1, count);
rs = conn1.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
assertFalse(rs.next());
conn1.commit();
rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
}
}
public void testCurrentDate() throws Exception {
String selectSql = "SELECT current_date() FROM " + FULL_TABLE_NAME;
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery(selectSql);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
conn.commit();
rs = conn.createStatement().executeQuery(selectSql);
assertTrue(rs.next());
Date date1 = rs.getDate(1);
assertFalse(rs.next());
Thread.sleep(1000);
rs = conn.createStatement().executeQuery(selectSql);
assertTrue(rs.next());
Date date2 = rs.getDate(1);
assertFalse(rs.next());
assertTrue(
"current_date() should change while executing multiple statements",
date2.getTime() > date1.getTime());
}
}
@Test
public void testQueryWithLimitAndStats() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), KEYONLY_NAME, null, ts);
initTableValues(ts + 1, 100);
TestUtil.analyzeTable(getUrl(), ts + 10, KEYONLY_NAME);
Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 50));
Connection conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT i1 FROM KEYONLY LIMIT 1";
ResultSet rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
assertFalse(rs.next());
rs = conn.createStatement().executeQuery("EXPLAIN " + query);
assertEquals(
"CLIENT SERIAL 1-WAY FULL SCAN OVER KEYONLY\n"
+ " SERVER FILTER BY FIRST KEY ONLY\n"
+ " SERVER 1 ROW LIMIT\n"
+ "CLIENT 1 ROW LIMIT",
QueryUtil.getExplainPlan(rs));
conn.close();
}
private void testRowConflicts() throws Exception {
try (Connection conn1 = DriverManager.getConnection(getUrl());
Connection conn2 = DriverManager.getConnection(getUrl())) {
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
conn1.setAutoCommit(false);
ResultSet rs = conn1.createStatement().executeQuery(selectSql);
boolean immutableRows =
conn1
.unwrap(PhoenixConnection.class)
.getTable(new PTableKey(null, FULL_TABLE_NAME))
.isImmutableRows();
assertFalse(rs.next());
// upsert row using conn1
String upsertSql =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, a.int_col1) VALUES(?, ?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn1.prepareStatement(upsertSql);
TestUtil.setRowKeyColumns(stmt, 1);
stmt.setInt(7, 10);
stmt.execute();
// upsert row using conn2
upsertSql =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, b.int_col2) VALUES(?, ?, ?, ?, ?, ?, ?)";
stmt = conn2.prepareStatement(upsertSql);
TestUtil.setRowKeyColumns(stmt, 1);
stmt.setInt(7, 11);
stmt.execute();
conn1.commit();
// second commit should fail
try {
conn2.commit();
if (!immutableRows) fail();
} catch (SQLException e) {
if (immutableRows) fail();
assertEquals(
e.getErrorCode(), SQLExceptionCode.TRANSACTION_CONFLICT_EXCEPTION.getErrorCode());
}
}
}
@Test
public void testReadOwnWrites() throws Exception {
String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery(selectSql);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
// verify rows can be read even though commit has not been called
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
conn.commit();
// verify rows can be read after commit
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
}
}
@Test
public void testDelete() throws Exception {
String selectSQL = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn1 = DriverManager.getConnection(getUrl());
Connection conn2 = DriverManager.getConnection(getUrl())) {
conn1.setAutoCommit(false);
ResultSet rs = conn1.createStatement().executeQuery(selectSQL);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn1.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
conn1.commit();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
// verify rows can be read even though commit has not been called
int rowsDeleted = conn1.createStatement().executeUpdate("DELETE FROM " + FULL_TABLE_NAME);
assertEquals(2, rowsDeleted);
// Delete and second upsert not committed yet, so there should be one row.
rs = conn2.createStatement().executeQuery("SELECT count(*) FROM " + FULL_TABLE_NAME);
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
conn1.commit();
// verify rows are deleted after commit
rs = conn1.createStatement().executeQuery(selectSQL);
assertFalse(rs.next());
}
}