in phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectWithRegionMovesIT.java [1383:1660]
public void testRowTimestampColWithViewsIndexesAndSaltedTables() throws Exception {
String baseTable = generateUniqueName();
String tenantView = generateUniqueName();
String globalView = generateUniqueName();
String baseTableIdx = generateUniqueName();
String tenantViewIdx = generateUniqueName();
TABLE_NAMES.add(baseTable);
TABLE_NAMES.add(baseTableIdx);
TABLE_NAMES.add("_IDX_" + baseTable);
Properties props = new Properties();
props.setProperty(QueryServices.ENABLE_SERVER_SIDE_UPSERT_MUTATIONS,
allowServerSideMutations);
try (Connection conn = DriverManager.getConnection(getUrl(), props);
Statement stmt = conn.createStatement()) {
stmt.execute("CREATE IMMUTABLE TABLE " + baseTable +
" (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, " +
"KV1 VARCHAR, KV2 VARCHAR, KV3 VARCHAR CONSTRAINT PK PRIMARY KEY(TENANT_ID, " +
"PK2 ROW_TIMESTAMP, PK3)) MULTI_TENANT = true, SALT_BUCKETS = 8");
stmt.execute("CREATE INDEX " + baseTableIdx + " ON " +
baseTable + " (PK2, KV3) INCLUDE (KV1)");
stmt.execute("CREATE VIEW " + globalView + " AS SELECT * FROM " +
baseTable + " WHERE KV1 = 'KV1'");
}
String tenantId = "tenant1";
try (Connection conn = getTenantConnection(tenantId);
Statement stmt = conn.createStatement()) {
stmt.execute("CREATE VIEW " + tenantView + " AS SELECT * FROM " +
baseTable);
stmt.execute("CREATE INDEX " + tenantViewIdx + " ON " +
tenantView + " (PK2, KV2) INCLUDE (KV1)");
}
// upsert data into base table without specifying the row timestamp column PK2
long startTime = EnvironmentEdgeManager.currentTimeMillis();
try (Connection conn = DriverManager.getConnection(getUrl(), props);
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + baseTable +
" (TENANT_ID, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?)")) {
// Upsert select in the same table with the row_timestamp column PK2 not specified.
// This will end up creating a new row whose timestamp is the latest timestamp
// (which will be used for the row key too)
stmt.setString(1, tenantId);
stmt.setInt(2, 3);
stmt.setString(3, "KV1");
stmt.setString(4, "KV2");
stmt.setString(5, "KV3");
stmt.executeUpdate();
conn.commit();
}
long endTime = EnvironmentEdgeManager.currentTimeMillis();
// Verify that we can see data when querying through base table, global view and index on
// the base table
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
// Query the base table
try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + baseTable +
" WHERE TENANT_ID = ? AND PK2 >= ? AND PK2 <= ? AND PK3 = ?")) {
stmt.setString(1, tenantId);
stmt.setDate(2, new Date(startTime));
stmt.setDate(3, new Date(endTime));
stmt.setInt(4, 3);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals(tenantId, rs.getString("TENANT_ID"));
assertEquals("KV1", rs.getString("KV1"));
assertEquals("KV2", rs.getString("KV2"));
assertEquals("KV3", rs.getString("KV3"));
assertFalse(rs.next());
}
// Query the globalView
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT /*+ NO_INDEX */ * FROM " + globalView +
" WHERE TENANT_ID = ? AND PK2 >= ? AND PK2 <= ? AND PK3 = ?")) {
stmt.setString(1, tenantId);
stmt.setDate(2, new Date(startTime));
stmt.setDate(3, new Date(endTime));
stmt.setInt(4, 3);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals(tenantId, rs.getString("TENANT_ID"));
assertEquals("KV1", rs.getString("KV1"));
assertEquals("KV2", rs.getString("KV2"));
assertEquals("KV3", rs.getString("KV3"));
assertFalse(rs.next());
}
// Query using the index on base table
try (PreparedStatement stmt = conn.prepareStatement("SELECT KV1 FROM " +
baseTable + " WHERE PK2 >= ? AND PK2 <= ? AND KV3 = ?")) {
stmt.setDate(1, new Date(startTime));
stmt.setDate(2, new Date(endTime));
stmt.setString(3, "KV3");
ResultSet rs = stmt.executeQuery();
QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
assertEquals(plan.getTableRef().getTable().getName().getString(), baseTableIdx);
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV1", rs.getString("KV1"));
assertFalse(rs.next());
}
}
// Verify that data can be queried using tenant view and tenant view index
try (Connection tenantConn = getTenantConnection(tenantId);
PreparedStatement stmt = tenantConn.prepareStatement("SELECT * FROM " +
tenantView + " WHERE PK2 >= ? AND PK2 <= ? AND PK3 = ?")) {
// Query the tenant view
stmt.setDate(1, new Date(startTime));
stmt.setDate(2, new Date(endTime));
stmt.setInt(3, 3);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV1", rs.getString("KV1"));
assertEquals("KV2", rs.getString("KV2"));
assertEquals("KV3", rs.getString("KV3"));
assertFalse(rs.next());
// Query using the index on the tenantView
//TODO: uncomment the code after PHOENIX-2277 is fixed
// stmt = tenantConn.prepareStatement("SELECT KV1 FROM " + tenantView +
// " WHERE PK2 = ? AND KV2 = ?");
// stmt.setDate(1, new Date(upsertedTs));
// stmt.setString(2, "KV2");
// rs = stmt.executeQuery();
// QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
// assertTrue(plan.getTableRef().getTable().getName().getString().equals(tenantViewIdx));
// assertTrue(rs.next());
// assertEquals("KV1", rs.getString("KV1"));
// assertFalse(rs.next());
}
long upsertedTs;
try (Connection tenantConn = getTenantConnection(tenantId)) {
// Upsert into tenant view where the row_timestamp column PK2 is not specified
startTime = EnvironmentEdgeManager.currentTimeMillis();
try (PreparedStatement stmt = tenantConn.prepareStatement("UPSERT INTO " +
tenantView + " (PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?)")) {
stmt.setInt(1, 33);
stmt.setString(2, "KV13");
stmt.setString(3, "KV23");
stmt.setString(4, "KV33");
stmt.executeUpdate();
}
tenantConn.commit();
upsertedTs = endTime = EnvironmentEdgeManager.currentTimeMillis();
// Upsert into tenant view where the row_timestamp column PK2 is specified
try (PreparedStatement stmt = tenantConn.prepareStatement("UPSERT INTO " +
tenantView + " (PK2, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?)")) {
stmt.setDate(1, new Date(upsertedTs));
stmt.setInt(2, 44);
stmt.setString(3, "KV14");
stmt.setString(4, "KV24");
stmt.setString(5, "KV34");
stmt.executeUpdate();
}
tenantConn.commit();
}
// Verify that the data upserted using the tenant view can now be queried using base table
// and the base table index
Date upsertedDate;
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
// Query the base table
try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + baseTable +
" WHERE TENANT_ID = ? AND PK2 >= ? AND PK2 <= ? AND PK3 = ? ")) {
stmt.setString(1, tenantId);
stmt.setDate(2, new Date(startTime));
stmt.setDate(3, new Date(endTime));
stmt.setInt(4, 33);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals(tenantId, rs.getString("TENANT_ID"));
assertEquals("KV13", rs.getString("KV1"));
assertEquals("KV23", rs.getString("KV2"));
assertEquals("KV33", rs.getString("KV3"));
upsertedDate = rs.getDate("PK2");
assertFalse(rs.next());
}
try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + baseTable +
" WHERE TENANT_ID = ? AND PK2 = ? AND PK3 = ? ")) {
stmt.setString(1, tenantId);
stmt.setDate(2, new Date(upsertedTs));
stmt.setInt(3, 44);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals(tenantId, rs.getString("TENANT_ID"));
assertEquals("KV14", rs.getString("KV1"));
assertEquals("KV24", rs.getString("KV2"));
assertEquals("KV34", rs.getString("KV3"));
assertFalse(rs.next());
}
// Query using the index on base table
try (PreparedStatement stmt = conn.prepareStatement("SELECT KV1 FROM " + baseTable +
" WHERE (PK2, KV3) IN ((?, ?), (?, ?)) ORDER BY KV1")) {
stmt.setDate(1, upsertedDate);
stmt.setString(2, "KV33");
stmt.setDate(3, new Date(upsertedTs));
stmt.setString(4, "KV34");
ResultSet rs = stmt.executeQuery();
QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
assertTrue(
plan.getTableRef().getTable().getName().getString().equals(baseTableIdx));
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV13", rs.getString("KV1"));
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV14", rs.getString("KV1"));
assertFalse(rs.next());
}
}
// Verify that the data upserted using the tenant view can now be queried using tenant view
try (Connection tenantConn = getTenantConnection(tenantId);
PreparedStatement stmt = tenantConn.prepareStatement("SELECT * FROM " +
tenantView + " WHERE (PK2, PK3) IN ((?, ?), (?, ?)) ORDER BY KV1")) {
// Query the base table
stmt.setDate(1, upsertedDate);
stmt.setInt(2, 33);
stmt.setDate(3, new Date(upsertedTs));
stmt.setInt(4, 44);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV13", rs.getString("KV1"));
assertTrue(rs.next());
moveRegionsOfTable(baseTable);
moveRegionsOfTable(baseTableIdx);
moveRegionsOfTable("_IDX_" + baseTable);
assertEquals("KV14", rs.getString("KV1"));
assertFalse(rs.next());
//TODO: uncomment the code after PHOENIX-2277 is fixed
// // Query using the index on the tenantView
// stmt = tenantConn.prepareStatement("SELECT KV1 FROM " + tenantView +
// " WHERE (PK2, KV2) IN (?, ?, ?, ?) ORDER BY KV1");
// stmt.setDate(1, new Date(upsertedTs));
// stmt.setString(2, "KV23");
// stmt.setDate(3, new Date(upsertedTs));
// stmt.setString(4, "KV24");
// rs = stmt.executeQuery();
// QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
// assertTrue(plan.getTableRef().getTable().getName().getString().equals(tenantViewIdx));
// assertTrue(rs.next());
// assertEquals("KV13", rs.getString("KV1"));
// assertTrue(rs.next());
// assertEquals("KV14", rs.getString("KV1"));
// assertFalse(rs.next());
}
}