[PHOENIX-2277] Indexes on tenant views not working correctly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Labels: SFDC
• Assignee: Samarth Jain
• Reporter: Samarth Jain
• Created: 20/Sep/15 05:04
• Updated: 13/Jun/16 21:41
• Resolved: 13/Jun/16 21:40

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.

@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;
     }