[ https://issues.apache.org/jira/browse/PHOENIX-2277?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15315125#comment-15315125 ]
Samarth Jain commented on PHOENIX-2277: --------------------------------------- This is still an issue. > 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 > Assignee: Samarth Jain > Labels: SFDC, verify > Fix For: 4.8.0 > > > 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(); > > try (Connection conn = DriverManager.getConnection(getUrl())) { > 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"; > try (Connection conn = > DriverManager.getConnection(getTenantUrl(tenantId))) { > conn.createStatement().execute("CREATE VIEW " + tenantView + " AS > SELECT * FROM " + baseTable); > conn.createStatement().execute("CREATE INDEX " + tenantViewIdx + > " ON " + tenantView + " (PK2, KV2) INCLUDE (KV1)"); > } > Date upsertedDate = new Date(5); > try (Connection conn = DriverManager.getConnection(getUrl())) { > 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 = > DriverManager.getConnection(getTenantUrl(tenantId))) { > // 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()); > } > } > private String getTenantUrl(String tenantId) { > return getUrl() + ';' + TENANT_ID_ATTRIB + '=' + tenantId; > } > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)