You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Samarth Jain (JIRA)" <ji...@apache.org> on 2015/09/20 07:11:04 UTC
[jira] [Updated] (PHOENIX-2277) Indexes on tenant views not working
correctly
[ https://issues.apache.org/jira/browse/PHOENIX-2277?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Samarth Jain updated PHOENIX-2277:
----------------------------------
Description:
Using the test it can be verified that the index is being picked for querying. However, the query isn't able to retrieve the row.
{code}
@Test
public void testIndexesOnTenantViews() throws Exception {
String baseTable = "testIndexesOnTenantViews".toUpperCase();
String tenantView = "tenantView".toUpperCase();
String tenantViewIdx = "tenantView_idx".toUpperCase();
long ts = nextTimestamp();
try (Connection conn = getConnection(ts)) {
conn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 CHAR(15) CONSTRAINT PK PRIMARY KEY(TENANT_ID, PK2, PK3)) MULTI_TENANT = true");
}
String tenantId = "tenant1tenant12";
ts = nextTimestamp();
try (Connection conn = getTenantConnection(tenantId, ts)) {
conn.createStatement().execute("CREATE VIEW " + tenantView + " AS SELECT * FROM " + baseTable);
}
ts = nextTimestamp();
try (Connection conn = getTenantConnection(tenantId, ts)) {
conn.createStatement().execute("CREATE INDEX " + tenantViewIdx + " ON " + tenantView + " (PK2, KV2) INCLUDE (KV1)");
}
Date upsertedDate = new Date(5);
try (Connection conn = getConnection(nextTimestamp())) {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + baseTable + " (TENANT_ID, PK2, PK3, KV1, KV2) VALUES (?, ?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setDate(2, upsertedDate);
stmt.setInt(3, 3);
stmt.setString(4, "KV1");
stmt.setString(5, "KV2");
stmt.executeUpdate();
conn.commit();
}
// Verify that data can be queried using tenant view and tenant view index
try (Connection tenantConn = getTenantConnection(tenantId, nextTimestamp())) {
// Query the tenant view
PreparedStatement stmt = tenantConn.prepareStatement("SELECT KV2 FROM " + tenantView + " WHERE PK2 = ? AND PK3 = ?");
stmt.setDate(1, upsertedDate);
stmt.setInt(2, 3);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals("KV2", rs.getString("KV2"));
assertFalse(rs.next());
// Query using the index on the tenantView
stmt = tenantConn.prepareStatement("SELECT KV1 FROM " + tenantView + " WHERE PK2 = ? AND KV2 = ?");
stmt.setDate(1, upsertedDate);
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());
}
}
{code}
was:
Using the test it can be verified that the index is being picked for querying. However, the query isn't able to retrieve the row.
{code}
@Test
public void testIndexesOnTenantViews() throws Exception {
String baseTable = "testIndexesOnTenantViews".toUpperCase();
String tenantView = "tenantView".toUpperCase();
String tenantViewIdx = "tenantView_idx".toUpperCase();
long ts = nextTimestamp();
try (Connection conn = getConnection(ts)) {
conn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 CHAR(15) CONSTRAINT PK PRIMARY KEY(TENANT_ID, PK2 ROW_TIMESTAMP, PK3)) MULTI_TENANT = true");
}
String tenantId = "tenant1tenant12";
ts = nextTimestamp();
try (Connection conn = getTenantConnection(tenantId, ts)) {
conn.createStatement().execute("CREATE VIEW " + tenantView + " AS SELECT * FROM " + baseTable);
}
ts = nextTimestamp();
try (Connection conn = getTenantConnection(tenantId, ts)) {
conn.createStatement().execute("CREATE INDEX " + tenantViewIdx + " ON " + tenantView + " (PK2, KV2) INCLUDE (KV1)");
}
Date upsertedDate = new Date(5);
try (Connection conn = getConnection(nextTimestamp())) {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + baseTable + " (TENANT_ID, PK2, PK3, KV1, KV2) VALUES (?, ?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setDate(2, upsertedDate);
stmt.setInt(3, 3);
stmt.setString(4, "KV1");
stmt.setString(5, "KV2");
stmt.executeUpdate();
conn.commit();
}
// Verify that data can be queried using tenant view and tenant view index
try (Connection tenantConn = getTenantConnection(tenantId, nextTimestamp())) {
// Query the tenant view
PreparedStatement stmt = tenantConn.prepareStatement("SELECT KV2 FROM " + tenantView + " WHERE PK2 = ? AND PK3 = ?");
stmt.setDate(1, upsertedDate);
stmt.setInt(2, 3);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals("KV2", rs.getString("KV2"));
assertFalse(rs.next());
// Query using the index on the tenantView
stmt = tenantConn.prepareStatement("SELECT KV1 FROM " + tenantView + " WHERE PK2 = ? AND KV2 = ?");
stmt.setDate(1, upsertedDate);
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());
}
}
{code}
> Indexes on tenant views not working correctly
> ---------------------------------------------
>
> Key: PHOENIX-2277
> URL: https://issues.apache.org/jira/browse/PHOENIX-2277
> Project: Phoenix
> Issue Type: Bug
> Reporter: Samarth Jain
>
> Using the test it can be verified that the index is being picked for querying. However, the query isn't able to retrieve the row.
> {code}
> @Test
> public void testIndexesOnTenantViews() throws Exception {
> String baseTable = "testIndexesOnTenantViews".toUpperCase();
> String tenantView = "tenantView".toUpperCase();
> String tenantViewIdx = "tenantView_idx".toUpperCase();
>
> long ts = nextTimestamp();
> try (Connection conn = getConnection(ts)) {
> conn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 CHAR(15) CONSTRAINT PK PRIMARY KEY(TENANT_ID, PK2, PK3)) MULTI_TENANT = true");
> }
> String tenantId = "tenant1tenant12";
> ts = nextTimestamp();
> try (Connection conn = getTenantConnection(tenantId, ts)) {
> conn.createStatement().execute("CREATE VIEW " + tenantView + " AS SELECT * FROM " + baseTable);
> }
> ts = nextTimestamp();
> try (Connection conn = getTenantConnection(tenantId, ts)) {
> conn.createStatement().execute("CREATE INDEX " + tenantViewIdx + " ON " + tenantView + " (PK2, KV2) INCLUDE (KV1)");
> }
> Date upsertedDate = new Date(5);
> try (Connection conn = getConnection(nextTimestamp())) {
> PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + baseTable + " (TENANT_ID, PK2, PK3, KV1, KV2) VALUES (?, ?, ?, ?, ?)");
> stmt.setString(1, tenantId);
> stmt.setDate(2, upsertedDate);
> stmt.setInt(3, 3);
> stmt.setString(4, "KV1");
> stmt.setString(5, "KV2");
> stmt.executeUpdate();
> conn.commit();
> }
>
> // Verify that data can be queried using tenant view and tenant view index
> try (Connection tenantConn = getTenantConnection(tenantId, nextTimestamp())) {
> // Query the tenant view
> PreparedStatement stmt = tenantConn.prepareStatement("SELECT KV2 FROM " + tenantView + " WHERE PK2 = ? AND PK3 = ?");
> stmt.setDate(1, upsertedDate);
> stmt.setInt(2, 3);
> ResultSet rs = stmt.executeQuery();
> assertTrue(rs.next());
> assertEquals("KV2", rs.getString("KV2"));
> assertFalse(rs.next());
>
> // Query using the index on the tenantView
> stmt = tenantConn.prepareStatement("SELECT KV1 FROM " + tenantView + " WHERE PK2 = ? AND KV2 = ?");
> stmt.setDate(1, upsertedDate);
> 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());
> }
> }
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)