phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1639) Enhance function/expression index tests
Date Tue, 17 Feb 2015 10:04:13 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-1639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14324021#comment-14324021
] 

ASF GitHub Bot commented on PHOENIX-1639:
-----------------------------------------

Github user twdsilva commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/36#discussion_r24804592
  
    --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java
---
    @@ -809,58 +832,439 @@ public void helpTestDropIndexedColumn(boolean mutable, boolean
local) throws Exc
     
             Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
             Connection conn = DriverManager.getConnection(getUrl(), props);
    -        conn.setAutoCommit(false);
    -
    -        // make sure that the tables are empty, but reachable
    -        conn.createStatement().execute(
    -          "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)");
    -        query = "SELECT * FROM t" ;
    -        rs = conn.createStatement().executeQuery(query);
    -        assertFalse(rs.next());
    -        String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h");
    -        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX
" + indexName + " ON t (v1 || '_' || v2)");
    +        try {
    +	        conn.setAutoCommit(false);
    +	
    +	        // make sure that the tables are empty, but reachable
    +	        conn.createStatement().execute(
    +	          "CREATE TABLE t (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)");
    +	        query = "SELECT * FROM t" ;
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertFalse(rs.next());
    +	        String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h");
    +	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX
" + indexName + " ON t (v1 || '_' || v2)");
    +	
    +	        query = "SELECT * FROM t";
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertFalse(rs.next());
    +	
    +	        // load some data into the table
    +	        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
    +	        stmt.setString(1, "a");
    +	        stmt.setString(2, "x");
    +	        stmt.setString(3, "1");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        assertIndexExists(conn,true);
    +	        conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1");
    +	        assertIndexExists(conn,false);
    +	
    +	        query = "SELECT * FROM t";
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("1",rs.getString(2));
    +	        assertFalse(rs.next());
    +	
    +	        // load some data into the table
    +	        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
    +	        stmt.setString(1, "a");
    +	        stmt.setString(2, "2");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        query = "SELECT * FROM t";
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("2",rs.getString(2));
    +	        assertFalse(rs.next());
    +        }
    +        finally {
    +        	conn.close();
    +        }
    +    }
    +    
    +    private static void assertIndexExists(Connection conn, boolean exists) throws SQLException
{
    +        ResultSet rs = conn.getMetaData().getIndexInfo(null, null, "T", false, false);
    +        assertEquals(exists, rs.next());
    +    }
    +    
    +    @Test
    +    public void testImmutableIndexDropCoveredColumn() throws Exception {
    +    	helpTestDropCoveredColumn(false, false);
    +    }
    +    
    +    @Test
    +    public void testImmutableLocalIndexDropCoveredColumn() throws Exception {
    +    	helpTestDropCoveredColumn(false, true);
    +    }
    +    
    +    @Test
    +    public void testMutableIndexDropCoveredColumn() throws Exception {
    +    	helpTestDropCoveredColumn(true, false);
    +    }
    +    
    +    @Test
    +    public void testMutableLocalIndexDropCoveredColumn() throws Exception {
    +    	helpTestDropCoveredColumn(true, true);
    +    }
    +    
    +    public void helpTestDropCoveredColumn(boolean mutable, boolean local) throws Exception
{
    +        ResultSet rs;
    +        PreparedStatement stmt;
     
    -        query = "SELECT * FROM t";
    -        rs = conn.createStatement().executeQuery(query);
    -        assertFalse(rs.next());
    +        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +	        conn.setAutoCommit(false);
    +	
    +	        // make sure that the tables are empty, but reachable
    +	        conn.createStatement().execute(
    +	          "CREATE TABLE t"
    +	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)");
    +	        String dataTableQuery = "SELECT * FROM t";
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertFalse(rs.next());
    +	
    +	        String indexName = "it_" + (mutable ? "m" : "im") + "_" + (local ? "l" : "h");
    +	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX
" + indexName + " ON t (k || '_' || v1) include (v2, v3)");
    +	        String indexTableQuery = "SELECT * FROM " + indexName;
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertFalse(rs.next());
    +	
    +	        // load some data into the table
    +	        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?,?)");
    +	        stmt.setString(1, "a");
    +	        stmt.setString(2, "x");
    +	        stmt.setString(3, "1");
    +	        stmt.setString(4, "j");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        assertIndexExists(conn,true);
    +	        conn.createStatement().execute("ALTER TABLE t DROP COLUMN v2");
    +	        assertIndexExists(conn,true);
    +	
    +	        // verify data table rows
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("x",rs.getString(2));
    +	        assertEquals("j",rs.getString(3));
    +	        assertFalse(rs.next());
    +	        
    +	        // verify index table rows
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a_x",rs.getString(1));
    +	        assertEquals("a",rs.getString(2));
    +	        assertEquals("j",rs.getString(3));
    +	        assertFalse(rs.next());
    +	
    +	        // add another row
    +	        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
    +	        stmt.setString(1, "b");
    +	        stmt.setString(2, "y");
    +	        stmt.setString(3, "k");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        // verify data table rows
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("x",rs.getString(2));
    +	        assertEquals("j",rs.getString(3));
    +	        assertTrue(rs.next());
    +	        assertEquals("b",rs.getString(1));
    +	        assertEquals("y",rs.getString(2));
    +	        assertEquals("k",rs.getString(3));
    +	        assertFalse(rs.next());
    +	        
    +	        // verify index table rows
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a_x",rs.getString(1));
    +	        assertEquals("a",rs.getString(2));
    +	        assertEquals("j",rs.getString(3));
    +	        assertTrue(rs.next());
    +	        assertEquals("b_y",rs.getString(1));
    +	        assertEquals("b",rs.getString(2));
    +	        assertEquals("k",rs.getString(3));
    +	        assertFalse(rs.next());
    +        }
    +        finally {
    +        	conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testImmutableIndexAddPKColumnToTable() throws Exception {
    +    	helpTestAddPKColumnToTable(false, false);
    +    }
    +    
    +    @Test
    +    public void testImmutableLocalIndexAddPKColumnToTable() throws Exception {
    +    	helpTestAddPKColumnToTable(false, true);
    +    }
    +    
    +    @Test
    +    public void testMutableIndexAddPKColumnToTable() throws Exception {
    +    	helpTestAddPKColumnToTable(true, false);
    +    }
    +    
    +    @Test
    +    public void testMutableLocalIndexAddPKColumnToTable() throws Exception {
    +    	helpTestAddPKColumnToTable(true, true);
    +    }
    +    
    +    public void helpTestAddPKColumnToTable(boolean mutable, boolean local) throws Exception
{
    +        ResultSet rs;
    +        PreparedStatement stmt;
     
    -        // load some data into the table
    -        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
    -        stmt.setString(1, "a");
    -        stmt.setString(2, "x");
    -        stmt.setString(3, "1");
    -        stmt.execute();
    +        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +	        conn.setAutoCommit(false);
    +	
    +	        // make sure that the tables are empty, but reachable
    +	        conn.createStatement().execute(
    +	          "CREATE TABLE t"
    +	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)");
    +	        String dataTableQuery = "SELECT * FROM t";
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertFalse(rs.next());
    +	
    +	        String indexName = "IT_" + (mutable ? "M" : "IM") + "_" + (local ? "L" : "H");
    +	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX
" + indexName + " ON t (v1 || '_' || v2)");
    +	        String indexTableQuery = "SELECT * FROM " + indexName;
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertFalse(rs.next());
    +	
    +	        // load some data into the table
    +	        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?,?)");
    +	        stmt.setString(1, "a");
    +	        stmt.setString(2, "x");
    +	        stmt.setString(3, "1");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        assertIndexExists(conn,true);
    +	        conn.createStatement().execute("ALTER TABLE t ADD v3 VARCHAR, k2 DECIMAL PRIMARY
KEY");
    +	        rs = conn.getMetaData().getPrimaryKeys("", "", "T");
    +	        assertTrue(rs.next());
    +	        assertEquals("K",rs.getString("COLUMN_NAME"));
    +	        assertEquals(1, rs.getShort("KEY_SEQ"));
    +	        assertTrue(rs.next());
    +	        assertEquals("K2",rs.getString("COLUMN_NAME"));
    +	        assertEquals(2, rs.getShort("KEY_SEQ"));
    +	
    +	        rs = conn.getMetaData().getPrimaryKeys("", "", indexName);
    +	        assertTrue(rs.next());
    +	        assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "(V1 || '_' || V2)",rs.getString("COLUMN_NAME"));
    +	        int offset = local ? 1 : 0;
    +	        assertEquals(offset+1, rs.getShort("KEY_SEQ"));
    +	        assertTrue(rs.next());
    +	        assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "K",rs.getString("COLUMN_NAME"));
    +	        assertEquals(offset+2, rs.getShort("KEY_SEQ"));
    +	        assertTrue(rs.next());
    +	        assertEquals(IndexUtil.INDEX_COLUMN_NAME_SEP + "K2",rs.getString("COLUMN_NAME"));
    +	        assertEquals(offset+3, rs.getShort("KEY_SEQ"));
    +	
    +	        // verify data table rows
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("x",rs.getString(2));
    +	        assertEquals("1",rs.getString(3));
    +	        assertNull(rs.getBigDecimal(4));
    +	        assertFalse(rs.next());
    +	        
    +	        // verify index table rows
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("x_1",rs.getString(1));
    +	        assertEquals("a",rs.getString(2));
    +	        assertNull(rs.getBigDecimal(3));
    +	        assertFalse(rs.next());
    +	
    +	        // load some data into the table
    +	        stmt = conn.prepareStatement("UPSERT INTO t(K,K2,V1,V2) VALUES(?,?,?,?)");
    +	        stmt.setString(1, "b");
    +	        stmt.setBigDecimal(2, BigDecimal.valueOf(2));
    +	        stmt.setString(3, "y");
    +	        stmt.setString(4, "2");
    +	        stmt.execute();
    +	        conn.commit();
    +	
    +	        // verify data table rows
    +	        rs = conn.createStatement().executeQuery(dataTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("a",rs.getString(1));
    +	        assertEquals("x",rs.getString(2));
    +	        assertEquals("1",rs.getString(3));
    +	        assertNull(rs.getString(4));
    +	        assertNull(rs.getBigDecimal(5));
    +	        assertTrue(rs.next());
    +	        assertEquals("b",rs.getString(1));
    +	        assertEquals("y",rs.getString(2));
    +	        assertEquals("2",rs.getString(3));
    +	        assertNull(rs.getString(4));
    +	        assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(5));
    +	        assertFalse(rs.next());
    +	        
    +	        // verify index table rows
    +	        rs = conn.createStatement().executeQuery(indexTableQuery);
    +	        assertTrue(rs.next());
    +	        assertEquals("x_1",rs.getString(1));
    +	        assertEquals("a",rs.getString(2));
    +	        assertNull(rs.getBigDecimal(3));
    +	        assertTrue(rs.next());
    +	        assertEquals("y_2",rs.getString(1));
    +	        assertEquals("b",rs.getString(2));
    +	        assertEquals(BigDecimal.valueOf(2),rs.getBigDecimal(3));
    +	        assertFalse(rs.next());
    +        }
    +        finally {
    +        	conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testUpdatableViewWithIndex() throws Exception {
    +        helpTestUpdatableViewIndex(false);
    +    }
    +    
    +    @Test
    +    public void testUpdatableViewWithLocalIndex() throws Exception {
    +        helpTestUpdatableViewIndex(true);
    +    }
    +       
    +    private void helpTestUpdatableViewIndex(boolean local) throws Exception {
    +    	Connection conn = DriverManager.getConnection(getUrl());
    +    	try {
    +	        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);
    +	        ddl = "CREATE VIEW v AS SELECT * FROM t WHERE k1 = 1";
    +	        conn.createStatement().execute(ddl);
    +	        conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)");
    +	        conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)");
    +	        conn.commit();
    +	        
    +	        ResultSet rs;
    +	        conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX
i1 on v(k1+k2+k3) include (s1, s2)");
    +	        conn.createStatement().execute("UPSERT INTO v(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)");
    +	        conn.commit();
    +	
    +	        String query = "SELECT k1, k2, k3, s1, s2 FROM v WHERE 	k1+k2+k3 = 173.0";
    +	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
    +	        String queryPlan = QueryUtil.getExplainPlan(rs);
    +	        if (local) {
    +	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_T [-32768,173]\n"
+ "CLIENT MERGE SORT",
    +	                    queryPlan);
    +	        } else {
    +	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + Short.MIN_VALUE
+ ",173]", queryPlan);
    +	        }
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertTrue(rs.next());
    +	        assertEquals(1, rs.getInt(1));
    +	        assertEquals(121, rs.getInt(2));
    +	        assertTrue(BigDecimal.valueOf(51.0).compareTo(rs.getBigDecimal(3))==0);
    +	        assertEquals("foo1", rs.getString(4));
    +	        assertEquals("bar1", rs.getString(5));
    +	        assertFalse(rs.next());
    +	
    +	        conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX
i2 on v(s1||'_'||s2)");
    +	        
    +	        query = "SELECT k1, k2, s1||'_'||s2 FROM v WHERE (s1||'_'||s2)='foo2_bar2'";
    +	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
    +	        if (local) {
    +	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE
+ 1)
    +	                    + ",'foo2_bar2']\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" +
"CLIENT MERGE SORT",
    +	                    QueryUtil.getExplainPlan(rs));
    +	        } else {
    +	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + (Short.MIN_VALUE
+ 1) + ",'foo2_bar2']\n"
    +	                    + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
    +	        }
    +	        rs = conn.createStatement().executeQuery(query);
    +	        assertTrue(rs.next());
    +	        assertEquals(1, rs.getInt(1));
    +	        assertEquals(120, rs.getInt(2));
    +	        assertEquals("foo2_bar2", rs.getString(3));
    +	        assertFalse(rs.next());
    +    	}
    +        finally {
    +        	conn.close();
    +        }
    +    }
    +    
    +    @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, s1 VARCHAR,
s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2))";
    +        conn.createStatement().execute(ddl);
    +        conn.createStatement().execute("CREATE INDEX i1 ON t(k2, s2, s3, s1)");
    +        conn.createStatement().execute("CREATE INDEX i2 ON t(k2, s2||'_'||s3, s1, s4)");
    +        
    +        ddl = "CREATE VIEW v AS SELECT * FROM t WHERE s1 = 'foo'";
    +        conn.createStatement().execute(ddl);
    +        conn.createStatement().execute("UPSERT INTO t VALUES(1,1,'foo','abc','cab')");
    +        conn.createStatement().execute("UPSERT INTO t VALUES(2,2,'bar','xyz','zyx')");
             conn.commit();
    -
    -        assertIndexExists(conn,true);
    -        conn.createStatement().execute("ALTER TABLE t DROP COLUMN v1");
    -        assertIndexExists(conn,false);
    -
    -        query = "SELECT * FROM t";
    -        rs = conn.createStatement().executeQuery(query);
    +        
    +        rs = conn.createStatement().executeQuery("SELECT count(*) FROM v");
             assertTrue(rs.next());
    -        assertEquals("a",rs.getString(1));
    -        assertEquals("1",rs.getString(2));
    +        assertEquals(1, rs.getLong(1));
             assertFalse(rs.next());
    -
    -        // load some data into the table
    -        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
    -        stmt.setString(1, "a");
    -        stmt.setString(2, "2");
    -        stmt.execute();
    -        conn.commit();
    -
    -        query = "SELECT * FROM t";
    +        
    +        conn.createStatement().execute("CREATE INDEX vi1 on v(k2)");
    --- End diff --
    
    For some reason the index on the data table (i2) is used only if there is an index on
the view (vi1)


> Enhance function/expression index tests
> ---------------------------------------
>
>                 Key: PHOENIX-1639
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1639
>             Project: Phoenix
>          Issue Type: Test
>            Reporter: Thomas D'Silva
>            Assignee: Thomas D'Silva
>
> Add tests for views, negative scenarios, sort order of expression. 



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

Mime
View raw message