Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-2277

Indexes on tenant views not working correctly

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

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

        Attachments

          Activity

            People

            • Assignee:
              samarthjain Samarth Jain
              Reporter:
              samarthjain Samarth Jain
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: