phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Samarth Jain (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2277) Indexes on tenant views not working correctly
Date Fri, 03 Jun 2016 23:53:59 GMT

     [ 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();
        
        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}

  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, 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
>            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)

Mime
View raw message