in phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexCheckerWithRegionMovesIT.java [324:542]
public void testPhoenixRowTimestamp() throws Exception {
try (Connection conn = DriverManager.getConnection(getUrl())) {
String dataTableName = generateUniqueName();
String indexTableName = generateUniqueName();
TABLE_NAMES.add(dataTableName);
TABLE_NAMES.add(indexTableName);
Timestamp initial = new Timestamp(EnvironmentEdgeManager.currentTimeMillis() - 1);
conn.createStatement().execute("create table " + dataTableName +
" (id varchar(10) not null primary key, val1 varchar(10), val2 varchar(10), val3 varchar(10))" +
tableDDLOptions);
conn.createStatement()
.execute("upsert into " + dataTableName + " values ('a', 'ab', 'abc', 'abcd')");
conn.commit();
Timestamp before = new Timestamp(EnvironmentEdgeManager.currentTimeMillis());
// Sleep 1ms to get a different row timestamps
Thread.sleep(1);
conn.createStatement()
.execute("upsert into " + dataTableName + " values ('b', 'bc', 'bcd', 'bcde')");
conn.commit();
Timestamp after = new Timestamp(EnvironmentEdgeManager.currentTimeMillis() + 1);
conn.createStatement().execute("CREATE INDEX " + indexTableName + " on " +
dataTableName + " (val1, PHOENIX_ROW_TIMESTAMP()) " + "include (val2, val3) " +
(async ? "ASYNC" : "") + this.indexDDLOptions);
if (async) {
// Run the index MR job to rebuild the index and verify that index is built correctly
IndexToolIT.runIndexTool(false, null, dataTableName,
indexTableName, null, 0, IndexTool.IndexVerifyType.AFTER);
}
String timeZoneID = Calendar.getInstance().getTimeZone().getID();
// Write a query to get the val2 = 'bc' with a time range query
String query = "SELECT val1, val2, PHOENIX_ROW_TIMESTAMP() from " + dataTableName +
" WHERE val1 = 'bc' AND " +
"PHOENIX_ROW_TIMESTAMP() > TO_DATE('" + before.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "') AND " +
"PHOENIX_ROW_TIMESTAMP() < TO_DATE('" + after.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "')";
// Verify that we will read from the index table
assertExplainPlan(conn, query, dataTableName, indexTableName);
ResultSet rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("bcd", rs.getString(2));
assertTrue(rs.getTimestamp(3).after(before));
assertTrue(rs.getTimestamp(3).before(after));
assertFalse(rs.next());
// Count the number of index rows
rs = conn.createStatement().executeQuery("SELECT COUNT(*) from " + indexTableName);
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
// Add one more row with val2 ='bc' and check this does not change the result of the previous
// query
// Sleep 1ms to get a different row timestamps
Thread.sleep(1);
conn.createStatement()
.execute("upsert into " + dataTableName + " values ('c', 'bc', 'ccc', 'cccc')");
conn.commit();
assertExplainPlan(conn, query, dataTableName, indexTableName);
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("bcd", rs.getString(2));
assertTrue(rs.getTimestamp(3).after(before));
assertTrue(rs.getTimestamp(3).before(after));
assertFalse(rs.next());
// Write a time range query to get the last row with val2 ='bc'
query = "SELECT val1, val2, PHOENIX_ROW_TIMESTAMP() from " + dataTableName +
" WHERE val1 = 'bc' AND " +
"PHOENIX_ROW_TIMESTAMP() > TO_DATE('" + after.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "')";
// Verify that we will read from the index table
assertExplainPlan(conn, query, dataTableName, indexTableName);
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("ccc", rs.getString(2));
assertTrue(rs.getTimestamp(3).after(after));
assertFalse(rs.next());
// Verify that we can execute the same query without using the index
String noIndexQuery =
"SELECT /*+ NO_INDEX */ val1, val2, PHOENIX_ROW_TIMESTAMP() from " +
dataTableName + " WHERE val1 = 'bc' AND " +
"PHOENIX_ROW_TIMESTAMP() > TO_DATE('" + after.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "')";
// Verify that we will read from the data table
rs = conn.createStatement().executeQuery("EXPLAIN " + noIndexQuery);
String explainPlan = QueryUtil.getExplainPlan(rs);
assertTrue(explainPlan.contains("FULL SCAN OVER " + dataTableName));
rs = conn.createStatement().executeQuery(noIndexQuery);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("ccc", rs.getString(2));
assertTrue(rs.getTimestamp(3).after(after));
after = rs.getTimestamp(3);
assertFalse(rs.next());
// Add an unverified index row
IndexRegionObserver.setFailPostIndexUpdatesForTesting(true);
// Sleep 1ms to get a different row timestamps
Thread.sleep(1);
conn.createStatement()
.execute("upsert into " + dataTableName + " values ('d', 'de', 'def', 'defg')");
conn.commit();
IndexRegionObserver.setFailPostIndexUpdatesForTesting(false);
// Make sure that we can repair the unverified row
query = "SELECT val1, val2, PHOENIX_ROW_TIMESTAMP() from " + dataTableName +
" WHERE val1 = 'de'";
// Verify that we will read from the index table
assertExplainPlan(conn, query, dataTableName, indexTableName);
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("de", rs.getString(1));
assertEquals("def", rs.getString(2));
assertTrue(rs.getTimestamp(3).after(after));
assertFalse(rs.next());
// Add a new index where the index row key starts with PHOENIX_ROW_TIMESTAMP()
indexTableName = generateUniqueName();
conn.createStatement().execute("CREATE INDEX " + indexTableName + " on " +
dataTableName + " (PHOENIX_ROW_TIMESTAMP()) " + "include (val1, val2, val3) " +
(async ? "ASYNC" : "") + this.indexDDLOptions);
if (async) {
// Run the index MR job to rebuild the index and verify that index is built correctly
IndexToolIT.runIndexTool(false, null, dataTableName,
indexTableName, null, 0, IndexTool.IndexVerifyType.AFTER);
}
// Add one more row
// Sleep 1ms to get a different row timestamps
Thread.sleep(1);
conn.createStatement()
.execute("upsert into " + dataTableName + " values ('e', 'ae', 'efg', 'efgh')");
conn.commit();
// Write a query to get all the rows in the order of their timestamps
query = "SELECT val1, val2, PHOENIX_ROW_TIMESTAMP() from " + dataTableName +
" WHERE " +
"PHOENIX_ROW_TIMESTAMP() > TO_DATE('" + initial.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "')";
// Verify that we will read from the index table
assertExplainPlan(conn, query, dataTableName, indexTableName);
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("ab", rs.getString(1));
assertEquals("abc", rs.getString(2));
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("bcd", rs.getString(2));
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("ccc", rs.getString(2));
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("de", rs.getString(1));
assertEquals("def", rs.getString(2));
assertTrue(rs.next());
assertEquals("ae", rs.getString(1));
assertEquals("efg", rs.getString(2));
assertFalse(rs.next());
conn.createStatement().execute("DROP INDEX " + indexTableName + " on " +
dataTableName);
// Run the previous test on an uncovered global index
indexTableName = generateUniqueName();
conn.createStatement().execute("CREATE INDEX " + indexTableName + " on " +
dataTableName + " (PHOENIX_ROW_TIMESTAMP())" +
(async ? "ASYNC" : "") + this.indexDDLOptions);
if (async) {
// Run the index MR job to rebuild the index and verify that index is built correctly
IndexToolIT.runIndexTool(false, null, dataTableName,
indexTableName, null, 0, IndexTool.IndexVerifyType.AFTER);
}
// Verify that without hint, the index table is not selected
assertIndexTableNotSelected(conn, dataTableName, indexTableName, query);
// Verify that we will read from the index table with the index hint
query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ " +
"val1, val2, PHOENIX_ROW_TIMESTAMP() from " + dataTableName + " WHERE " +
"PHOENIX_ROW_TIMESTAMP() > TO_DATE('" + initial.toString() +
"','yyyy-MM-dd HH:mm:ss.SSS', '" + timeZoneID + "')";
assertExplainPlan(conn, query, dataTableName, indexTableName);
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("ab", rs.getString(1));
assertEquals("abc", rs.getString(2));
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("bc", rs.getString(1));
assertEquals("bcd", rs.getString(2));
assertTrue(rs.next());
assertEquals("bc", rs.getString(1));
assertEquals("ccc", rs.getString(2));
assertTrue(rs.next());
assertEquals("de", rs.getString(1));
assertEquals("def", rs.getString(2));
assertTrue(rs.next());
moveRegionsOfTable(dataTableName);
moveRegionsOfTable(indexTableName);
assertEquals("ae", rs.getString(1));
assertEquals("efg", rs.getString(2));
assertFalse(rs.next());
}
}