[jira] [Created] (PHOENIX-1669) Views only use parent table indexes if there is an index on the view itself

[Thomas D'Silva (JIRA)]

Thomas D'Silva created PHOENIX-1669:
---------------------------------------

             Summary: Views only use parent table indexes if there is an index 
on the view itself
                 Key: PHOENIX-1669
                 URL: https://issues.apache.org/jira/browse/PHOENIX-1669
             Project: Phoenix
          Issue Type: Bug
            Reporter: Thomas D'Silva


In ViewIT.testViewUsesTableIndex() if you don't create vi1
then the select query does not use the parent table index i1.

{code}
@Test
    public void testViewUsesTableIndex() throws Exception {
        ResultSet rs;
        Connection conn = DriverManager.getConnection(getUrl());
        String ddl = "CREATE TABLE t (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, 
k3 DECIMAL, s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2, k3))";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("CREATE INDEX i1 ON t(k3, k2) 
INCLUDE(s1, s2)");
        conn.createStatement().execute("CREATE INDEX i2 ON t(k3, k2, s2)");
        
        ddl = "CREATE VIEW v AS SELECT * FROM t WHERE s1 = 'foo'";
        conn.createStatement().execute(ddl);
        String[] s1Values = {"foo","bar"};
        for (int i = 0; i < 10; i++) {
            conn.createStatement().execute("UPSERT INTO t VALUES(" + (i % 4) + 
"," + (i+100) + "," + (i > 5 ? 2 : 1) + ",'" + s1Values[i%2] + "','bas')");
        }
        conn.commit();
        
        rs = conn.createStatement().executeQuery("SELECT count(*) FROM v");
        assertTrue(rs.next());
        assertEquals(5, rs.getLong(1));
        assertFalse(rs.next());

        conn.createStatement().execute("CREATE INDEX vi1 on v(k2)");
        
        String query = "SELECT k2 FROM v WHERE k2 IN (100,109) AND k3 IN (1,2) 
AND s2='bas'";
        rs = conn.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals(100, rs.getInt(1));
        assertFalse(rs.next());
        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
        String queryPlan = QueryUtil.getExplainPlan(rs);
        assertEquals(
                "CLIENT PARALLEL 1-WAY SKIP SCAN ON 4 KEYS OVER I1 [1,100] - 
[2,109]\n" + 
                "    SERVER FILTER BY (\"S2\" = 'bas' AND \"S1\" = 'foo')", 
queryPlan);
    }
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)