@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();
}
@Test
public void testOr() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), KEYONLY_NAME, null, ts);
initTableValues(ts + 1);
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 3));
Connection conn3 = DriverManager.getConnection(getUrl(), props);
analyzeTable(conn3, KEYONLY_NAME);
conn3.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
Connection conn5 = DriverManager.getConnection(getUrl(), props);
String query = "SELECT i1 FROM KEYONLY WHERE i1 < 2 or i1 = 3";
PreparedStatement statement = conn5.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
conn5.close();
}
@Test
public void testKeyOnly() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), KEYONLY_NAME, null, ts);
initTableValues(ts + 1);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
Connection conn3 = DriverManager.getConnection(getUrl(), props);
analyzeTable(conn3, KEYONLY_NAME);
conn3.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 50));
Connection conn5 = DriverManager.getConnection(getUrl(), props);
String query = "SELECT i1, i2 FROM KEYONLY";
PreparedStatement statement = conn5.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertEquals(2, rs.getInt(2));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertEquals(4, rs.getInt(2));
assertFalse(rs.next());
List<KeyRange> splits = getAllSplits(conn5, "KEYONLY");
assertEquals(2, splits.size());
conn5.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60));
Connection conn6 = DriverManager.getConnection(getUrl(), props);
conn6.createStatement().execute("ALTER TABLE KEYONLY ADD s1 varchar");
conn6.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 70));
Connection conn7 = DriverManager.getConnection(getUrl(), props);
PreparedStatement stmt = conn7.prepareStatement("upsert into " + "KEYONLY VALUES (?, ?, ?)");
stmt.setInt(1, 5);
stmt.setInt(2, 6);
stmt.setString(3, "foo");
stmt.execute();
conn7.commit();
conn7.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 80));
Connection conn8 = DriverManager.getConnection(getUrl(), props);
analyzeTable(conn8, KEYONLY_NAME);
conn8.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 90));
Connection conn9 = DriverManager.getConnection(getUrl(), props);
query = "SELECT i1 FROM KEYONLY";
statement = conn9.prepareStatement(query);
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertTrue(rs.next());
assertEquals(5, rs.getInt(1));
assertFalse(rs.next());
query = "SELECT i1,s1 FROM KEYONLY";
statement = conn9.prepareStatement(query);
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertEquals(null, rs.getString(2));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertEquals(null, rs.getString(2));
assertTrue(rs.next());
assertEquals(5, rs.getInt(1));
assertEquals("foo", rs.getString(2));
assertFalse(rs.next());
conn9.close();
}
