phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [19/26] phoenix git commit: PHOENIX-4248 Breakup IndexExpressionIT into several integration tests so as not to create too many tables in one test
Date Fri, 29 Sep 2017 02:31:17 GMT
http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
new file mode 100644
index 0000000..14b569a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
@@ -0,0 +1,775 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.index;
+
+import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY;
+import static org.apache.phoenix.util.TestUtil.INDEX_DATA_SCHEMA;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.execute.CommitException;
+import org.apache.phoenix.query.QueryConstants;
+import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Test;
+
+public class IndexUsageIT extends ParallelStatsDisabledIT {
+
+    /**
+     * Adds a row to the index data table
+     * 
+     * @param i row number
+     */
+    private void insertRow(PreparedStatement stmt, int i) throws SQLException {
+        // insert row
+        stmt.setString(1, "varchar" + String.valueOf(i));
+        stmt.setString(2, "char" + String.valueOf(i));
+        stmt.setInt(3, i);
+        stmt.setLong(4, i);
+        stmt.setBigDecimal(5, new BigDecimal(i*0.5d));
+        Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i - 1) * MILLIS_IN_DAY);
+        stmt.setDate(6, date);
+        stmt.setString(7, "a.varchar" + String.valueOf(i));
+        stmt.setString(8, "a.char" + String.valueOf(i));
+        stmt.setInt(9, i);
+        stmt.setLong(10, i);
+        stmt.setBigDecimal(11, new BigDecimal(i*0.5d));
+        stmt.setDate(12, date);
+        stmt.setString(13, "b.varchar" + String.valueOf(i));
+        stmt.setString(14, "b.char" + String.valueOf(i));
+        stmt.setInt(15, i);
+        stmt.setLong(16, i);
+        stmt.setBigDecimal(17, new BigDecimal(i*0.5d));
+        stmt.setDate(18, date);
+        stmt.executeUpdate();
+    }
+
+    private void createDataTable(Connection conn, String dataTableName, String tableProps) throws SQLException {
+        String tableDDL = "create table " + dataTableName + TestUtil.TEST_TABLE_SCHEMA + tableProps;
+        conn.createStatement().execute(tableDDL);
+    }
+    
+    private void populateDataTable(Connection conn, String dataTable) throws SQLException {
+        String upsert = "UPSERT INTO " + dataTable
+                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+        PreparedStatement stmt1 = conn.prepareStatement(upsert);
+        // insert two rows
+        insertRow(stmt1, 1);
+        insertRow(stmt1, 2);
+        conn.commit();
+    }
+
+    @Test
+    public void testGroupByCountImmutableIndex() throws Exception {
+        helpTestGroupByCount(false, false);
+    }
+
+    @Test
+    public void testGroupByCountImmutableLocalIndex() throws Exception {
+        helpTestGroupByCount(false, true);
+    }
+
+    @Test
+    public void testGroupByCountMutableIndex() throws Exception {
+        helpTestGroupByCount(true, false);
+    }
+
+    @Test
+    public void testGroupByCountMutableLocalIndex() throws Exception {
+        helpTestGroupByCount(true, true);
+    }
+
+    protected void helpTestGroupByCount(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
+        String indexName = generateUniqueName();
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
+            populateDataTable(conn, fullDataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+                    + " (int_col1+int_col2)";
+            conn.createStatement().execute(ddl);
+
+            String groupBySql = "SELECT (int_col1+int_col2), COUNT(*) FROM " + fullDataTableName
+                    + " GROUP BY (int_col1+int_col2)";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + groupBySql);
+            String expectedPlan = "CLIENT PARALLEL 1-WAY "
+                    + (localIndex ? "RANGE SCAN OVER " + fullDataTableName + " [1]"
+                            : "FULL SCAN OVER INDEX_TEST." + indexName)
+                    + "\n    SERVER FILTER BY FIRST KEY ONLY\n    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + B.INT_COL2)\")]" 
+                    + (localIndex ? "\nCLIENT MERGE SORT" : "");
+            assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(groupBySql);
+            assertTrue(rs.next());
+            assertEquals(1, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(1, rs.getInt(2));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testSelectDistinctImmutableIndex() throws Exception {
+        helpTestSelectDistinct(false, false);
+    }
+
+    @Test
+    public void testSelectDistinctImmutableIndexLocal() throws Exception {
+        helpTestSelectDistinct(false, true);
+    }
+
+    @Test
+    public void testSelectDistinctMutableIndex() throws Exception {
+        helpTestSelectDistinct(true, false);
+    }
+
+    @Test
+    public void testSelectDistinctMutableLocalIndex() throws Exception {
+        helpTestSelectDistinct(true, true);
+    }
+
+    protected void helpTestSelectDistinct(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
+        String indexName = generateUniqueName();
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
+            populateDataTable(conn, fullDataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+                    + " (int_col1+1)";
+            conn.createStatement().execute(ddl);
+            String sql = "SELECT distinct int_col1+1 FROM " + fullDataTableName + " where int_col1+1 > 0";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+            String expectedPlan = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+                    + (localIndex ? fullDataTableName + " [1,0] - [1,*]"
+                            : "INDEX_TEST." + indexName + " [0] - [*]")
+                    + "\n    SERVER FILTER BY FIRST KEY ONLY\n    SERVER DISTINCT PREFIX FILTER OVER [TO_BIGINT(\"(A.INT_COL1 + 1)\")]\n    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [TO_BIGINT(\"(A.INT_COL1 + 1)\")]"
+                    + (localIndex ? "\nCLIENT MERGE SORT" : "");
+            assertEquals(expectedPlan, QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(sql);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(3, rs.getInt(1));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testInClauseWithImmutableIndex() throws Exception {
+        helpTestInClauseWithIndex(false, false);
+    }
+
+    @Test
+    public void testInClauseWithImmutableLocalIndex() throws Exception {
+        helpTestInClauseWithIndex(false, true);
+    }
+
+    @Test
+    public void testInClauseWithMutableIndex() throws Exception {
+        helpTestInClauseWithIndex(true, false);
+    }
+
+    @Test
+    public void testInClauseWithMutableLocalIndex() throws Exception {
+        helpTestInClauseWithIndex(true, false);
+    }
+
+    protected void helpTestInClauseWithIndex(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
+        String indexName = generateUniqueName();
+
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
+            populateDataTable(conn, fullDataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+                    + " (int_col1+1)";
+
+            conn.createStatement().execute(ddl);
+            String sql = "SELECT int_col1+1 FROM " + fullDataTableName + " where int_col1+1 IN (2)";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER "
+                    + (localIndex ? fullDataTableName + " [1,2]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
+                            : "INDEX_TEST." + indexName + " [2]\n    SERVER FILTER BY FIRST KEY ONLY"), QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(sql);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testOrderByWithImmutableIndex() throws Exception {
+        helpTestSelectAliasAndOrderByWithIndex(false, false);
+    }
+
+    @Test
+    public void testOrderByWithImmutableLocalIndex() throws Exception {
+        helpTestSelectAliasAndOrderByWithIndex(false, true);
+    }
+
+    @Test
+    public void testOrderByWithMutableIndex() throws Exception {
+        helpTestSelectAliasAndOrderByWithIndex(true, false);
+    }
+
+    @Test
+    public void testOrderByWithMutableLocalIndex() throws Exception {
+        helpTestSelectAliasAndOrderByWithIndex(true, false);
+    }
+
+    protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
+        String indexName = generateUniqueName();
+
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
+            populateDataTable(conn, fullDataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+                    + " (int_col1+1)";
+
+            conn.createStatement().execute(ddl);
+            String sql = "SELECT int_col1+1 AS foo FROM " + fullDataTableName + " ORDER BY foo";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+            assertEquals("CLIENT PARALLEL 1-WAY "
+                    + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
+                            + " [1]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
+                            : "FULL SCAN OVER INDEX_TEST." + indexName + "\n    SERVER FILTER BY FIRST KEY ONLY"),
+                    QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(sql);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(3, rs.getInt(1));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testImmutableIndexWithCaseSensitiveCols() throws Exception {
+        helpTestIndexWithCaseSensitiveCols(false, false);
+    }
+    
+    @Test
+    public void testImmutableLocalIndexWithCaseSensitiveCols() throws Exception {
+        helpTestIndexWithCaseSensitiveCols(false, true);
+    }
+    
+    @Test
+    public void testMutableIndexWithCaseSensitiveCols() throws Exception {
+        helpTestIndexWithCaseSensitiveCols(true, false);
+    }
+    
+    @Test
+    public void testMutableLocalIndexWithCaseSensitiveCols() throws Exception {
+        helpTestIndexWithCaseSensitiveCols(true, true);
+    }
+    
+    protected void helpTestIndexWithCaseSensitiveCols(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.createStatement().execute("CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"cf1\".\"V1\" VARCHAR, \"CF2\".\"v2\" VARCHAR) "+ (mutable ? "IMMUTABLE_ROWS=true" : ""));
+            String query = "SELECT * FROM " + dataTableName;
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (\"cf1\".\"V1\" || '_' || \"CF2\".\"v2\") INCLUDE (\"V1\",\"v2\")";
+            conn.createStatement().execute(ddl);
+            query = "SELECT * FROM " + indexName;
+            rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+
+            PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)");
+            stmt.setString(1,"a");
+            stmt.setString(2, "x");
+            stmt.setString(3, "1");
+            stmt.execute();
+            stmt.setString(1,"b");
+            stmt.setString(2, "y");
+            stmt.setString(3, "2");
+            stmt.execute();
+            conn.commit();
+
+            query = "SELECT (\"V1\" || '_' || \"v2\"), k, \"V1\", \"v2\"  FROM " + dataTableName + " WHERE (\"V1\" || '_' || \"v2\") = 'x_1'";
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if(localIndex){
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'x_1']\n"
+                           + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['x_1']", QueryUtil.getExplainPlan(rs));
+            }
+
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("x_1",rs.getString(1));
+            assertEquals("a",rs.getString(2));
+            assertEquals("x",rs.getString(3));
+            assertEquals("1",rs.getString(4));
+            //TODO figure out why this " " is needed
+            assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\""));
+            assertEquals("a",rs.getString("k"));
+            assertEquals("x",rs.getString("V1"));
+            assertEquals("1",rs.getString("v2"));
+            assertFalse(rs.next());
+
+            query = "SELECT \"V1\", \"V1\" as foo1, (\"V1\" || '_' || \"v2\") as foo, (\"V1\" || '_' || \"v2\") as \"Foo1\", (\"V1\" || '_' || \"v2\") FROM " + dataTableName + " ORDER BY foo";
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if(localIndex){
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1]\nCLIENT MERGE SORT",
+                    QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName, QueryUtil.getExplainPlan(rs));
+            }
+
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("x",rs.getString(1));
+            assertEquals("x",rs.getString("V1"));
+            assertEquals("x",rs.getString(2));
+            assertEquals("x",rs.getString("foo1"));
+            assertEquals("x_1",rs.getString(3));
+            assertEquals("x_1",rs.getString("Foo"));
+            assertEquals("x_1",rs.getString(4));
+            assertEquals("x_1",rs.getString("Foo1"));
+            assertEquals("x_1",rs.getString(5));
+            assertEquals("x_1",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\""));
+            assertTrue(rs.next());
+            assertEquals("y",rs.getString(1));
+            assertEquals("y",rs.getString("V1"));
+            assertEquals("y",rs.getString(2));
+            assertEquals("y",rs.getString("foo1"));
+            assertEquals("y_2",rs.getString(3));
+            assertEquals("y_2",rs.getString("Foo"));
+            assertEquals("y_2",rs.getString(4));
+            assertEquals("y_2",rs.getString("Foo1"));
+            assertEquals("y_2",rs.getString(5));
+            assertEquals("y_2",rs.getString("\"('cf1'.'V1' || '_' || 'CF2'.'v2')\""));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }    
+    
+    @Test
+    public void testSelectColOnlyInDataTableImmutableIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(false, false);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableImmutableLocalIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(false, true);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableMutableIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(true, false);
+    }
+
+    @Test
+    public void testSelectColOnlyInDataTableMutableLocalIndex() throws Exception {
+        helpTestSelectColOnlyInDataTable(true, true);
+    }
+
+    protected void helpTestSelectColOnlyInDataTable(boolean mutable, boolean localIndex) throws Exception {
+        String dataTableName = generateUniqueName();
+        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
+        String indexName = generateUniqueName();
+
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            conn.setAutoCommit(false);
+            createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
+            populateDataTable(conn, fullDataTableName);
+            String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
+                    + " (int_col1+1)";
+
+            conn = DriverManager.getConnection(getUrl(), props);
+            conn.setAutoCommit(false);
+            conn.createStatement().execute(ddl);
+            String sql = "SELECT int_col1+1, int_col2 FROM " + fullDataTableName + " WHERE int_col1+1=2";
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+            assertEquals("CLIENT PARALLEL 1-WAY "
+                    + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
+                            + " [1,2]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT" : "FULL SCAN OVER "
+                            + fullDataTableName + "\n    SERVER FILTER BY (A.INT_COL1 + 1) = 2"),
+                    QueryUtil.getExplainPlan(rs));
+            rs = conn.createStatement().executeQuery(sql);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertEquals(1, rs.getInt(2));
+            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());
+        String dataTableName = generateUniqueName();
+        String indexName1 = generateUniqueName();
+        String viewName = generateUniqueName();
+        String indexName2 = generateUniqueName();
+    	try {
+	        String ddl = "CREATE TABLE " + dataTableName + " (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 " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE k1 = 1";
+	        conn.createStatement().execute(ddl);
+	        conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo0','bar0',50.0)");
+	        conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(121,'foo1','bar1',51.0)");
+	        conn.commit();
+	        
+	        ResultSet rs;
+	        conn.createStatement().execute("CREATE " + (local ? "LOCAL" : "") + " INDEX " + indexName1 + " on " + viewName + "(k1+k2+k3) include (s1, s2)");
+	        conn.createStatement().execute("UPSERT INTO " + viewName + "(k2,s1,s2,k3) VALUES(120,'foo2','bar2',50.0)");
+	        conn.commit();
+	
+	        String query = "SELECT k1, k2, k3, s1, s2 FROM " + viewName + " 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 " + dataTableName + " [1,173]\n" + "CLIENT MERGE SORT",
+	                    queryPlan);
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_" + dataTableName + " [" + 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 " + indexName2 + " on " + viewName + "(s1||'_'||s2)");
+	        
+	        query = "SELECT k1, k2, s1||'_'||s2 FROM " + viewName + " WHERE (s1||'_'||s2)='foo2_bar2'";
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if (local) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [" + (2)
+	                    + ",'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_" + dataTableName + " [" + (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 testViewUsesMutableTableIndex() throws Exception {
+        helpTestViewUsesTableIndex(false);
+    }
+    
+    @Test
+    public void testViewUsesImmutableTableIndex() throws Exception {
+        helpTestViewUsesTableIndex(true);
+    }
+    
+    private void helpTestViewUsesTableIndex(boolean immutable) throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        try 
+        {
+            String dataTableName = generateUniqueName();
+            String indexName1 = generateUniqueName();
+            String viewName = generateUniqueName();
+            String indexName2 = generateUniqueName();
+        	ResultSet rs;
+	        String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, s1 VARCHAR, s2 VARCHAR, s3 VARCHAR, s4 VARCHAR CONSTRAINT pk PRIMARY KEY (k1, k2)) " + (immutable ? "IMMUTABLE_ROWS = true" : "");
+	        conn.createStatement().execute(ddl);
+	        conn.createStatement().execute("CREATE INDEX " + indexName1 + " ON " + dataTableName + "(k2, s2, s3, s1)");
+	        conn.createStatement().execute("CREATE INDEX " + indexName2 + " ON " + dataTableName + "(k2, s2||'_'||s3, s1, s4)");
+	        
+	        ddl = "CREATE VIEW " + viewName + " AS SELECT * FROM " + dataTableName + " WHERE s1 = 'foo'";
+	        conn.createStatement().execute(ddl);
+	        conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1,'foo','abc','cab')");
+	        conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(2,2,'bar','xyz','zyx')");
+	        conn.commit();
+	        
+	        rs = conn.createStatement().executeQuery("SELECT count(*) FROM " + viewName);
+	        assertTrue(rs.next());
+	        assertEquals(1, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        //i2 should be used since it contains s3||'_'||s4 i
+	        String query = "SELECT s2||'_'||s3 FROM " + viewName + " WHERE k2=1 AND (s2||'_'||s3)='abc_cab'";
+	        rs = conn.createStatement(  ).executeQuery("EXPLAIN " + query);
+	        String queryPlan = QueryUtil.getExplainPlan(rs);
+	        assertEquals(
+	                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName2 + " [1,'abc_cab','foo']\n" +
+	                "    SERVER FILTER BY FIRST KEY ONLY", queryPlan);
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("abc_cab", rs.getString(1));
+	        assertFalse(rs.next());
+	        
+	        conn.createStatement().execute("ALTER VIEW " + viewName + " DROP COLUMN s4");
+	        //i2 cannot be used since s4 has been dropped from the view, so i1 will be used 
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        queryPlan = QueryUtil.getExplainPlan(rs);
+	        assertEquals(
+	                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName1 + " [1]\n" +
+	                "    SERVER FILTER BY FIRST KEY ONLY AND ((\"S2\" || '_' || \"S3\") = 'abc_cab' AND \"S1\" = 'foo')", queryPlan);
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("abc_cab", rs.getString(1));
+	        assertFalse(rs.next());    
+        }
+        finally {
+        	conn.close();
+        }
+    }
+    
+	@Test
+	public void testExpressionThrowsException() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+		try {
+			String ddl = "CREATE TABLE " + dataTableName + " (k1 INTEGER PRIMARY KEY, k2 INTEGER)";
+			conn.createStatement().execute(ddl);
+			ddl = "CREATE INDEX " + indexName + " on " + dataTableName + "(k1/k2)";
+			conn.createStatement().execute(ddl);
+			// upsert should succeed
+			conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,1)");
+			conn.commit();
+			// divide by zero should fail
+			conn.createStatement().execute("UPSERT INTO " + dataTableName + " VALUES(1,0)");
+			conn.commit();
+			fail();
+		} catch (CommitException e) {
+		} finally {
+			conn.close();
+		}
+	}
+	
+	@Test
+	public void testImmutableCaseSensitiveFunctionIndex() throws Exception {
+		helpTestCaseSensitiveFunctionIndex(false, false);
+	}
+
+	@Test
+	public void testImmutableLocalCaseSensitiveFunctionIndex() throws Exception {
+		helpTestCaseSensitiveFunctionIndex(false, true);
+	}
+
+	@Test
+	public void testMutableCaseSensitiveFunctionIndex() throws Exception {
+		helpTestCaseSensitiveFunctionIndex(true, false);
+	}
+
+	@Test
+	public void testMutableLocalCaseSensitiveFunctionIndex() throws Exception {
+		helpTestCaseSensitiveFunctionIndex(true, true);
+	}
+
+	protected void helpTestCaseSensitiveFunctionIndex(boolean mutable,
+			boolean localIndex) throws Exception {
+		Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+		Connection conn = DriverManager.getConnection(getUrl(), props);
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+		try {
+			conn.createStatement().execute(
+					"CREATE TABLE " + dataTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) "
+							+ (!mutable ? "IMMUTABLE_ROWS=true" : ""));
+			String query = "SELECT * FROM  " + dataTableName;
+			ResultSet rs = conn.createStatement().executeQuery(query);
+			assertFalse(rs.next());
+			String ddl = "CREATE " + (localIndex ? "LOCAL" : "")
+					+ " INDEX " + indexName + " ON " + dataTableName + " (REGEXP_SUBSTR(v,'id:\\\\w+'))";
+			conn.createStatement().execute(ddl);
+			query = "SELECT * FROM " + indexName;
+			rs = conn.createStatement().executeQuery(query);
+			assertFalse(rs.next());
+
+			PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?)");
+			stmt.setString(1, "k1");
+			stmt.setString(2, "{id:id1}");
+			stmt.execute();
+			stmt.setString(1, "k2");
+			stmt.setString(2, "{id:id2}");
+			stmt.execute();
+			conn.commit();
+			
+			query = "SELECT k FROM " + dataTableName + " WHERE REGEXP_SUBSTR(v,'id:\\\\w+') = 'id:id1'";
+			rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+			if (localIndex) {
+				assertEquals(
+						"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " [1,'id:id1']\n"
+								+ "    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT",
+						QueryUtil.getExplainPlan(rs));
+			} else {
+				assertEquals(
+						"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['id:id1']\n"
+								+ "    SERVER FILTER BY FIRST KEY ONLY",
+						QueryUtil.getExplainPlan(rs));
+			}
+
+			rs = conn.createStatement().executeQuery(query);
+			assertTrue(rs.next());
+			assertEquals("k1", rs.getString(1));
+			assertFalse(rs.next());
+		} finally {
+			conn.close();
+		}
+	}
+
+	@Test
+    public void testImmutableTableGlobalIndexExpressionWithJoin() throws Exception {
+        helpTestIndexExpressionWithJoin(false, false);
+    }
+	
+	@Test
+    public void testImmutableTableLocalIndexExpressionWithJoin() throws Exception {
+        helpTestIndexExpressionWithJoin(false, true);
+    }
+	
+	@Test
+    public void testMutableTableGlobalIndexExpressionWithJoin() throws Exception {
+        helpTestIndexExpressionWithJoin(true, false);
+    }
+	
+	@Test
+    public void testMutableTableLocalIndexExpressionWithJoin() throws Exception {
+	    helpTestIndexExpressionWithJoin(true, true);
+    }
+
+    public void helpTestIndexExpressionWithJoin(boolean mutable,
+            boolean localIndex) throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String nameSuffix = "T" + (mutable ? "MUTABLE" : "_IMMUTABLE") + (localIndex ? "_LOCAL" : "_GLOBAL");
+        String tableName = "T" + nameSuffix;
+        String indexName = "IDX" + nameSuffix;
+        try {
+            conn.createStatement().execute(
+                        "CREATE TABLE "
+                                + tableName
+                                + "( c_customer_sk varchar primary key, c_first_name varchar, c_last_name varchar )"
+                                + (!mutable ? "IMMUTABLE_ROWS=true" : ""));
+            String query = "SELECT * FROM " + tableName;
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+            
+            conn.createStatement().execute(
+                "CREATE " + (localIndex ? "LOCAL" : "")
+                + " INDEX " + indexName + " ON " + tableName + " (c_customer_sk || c_first_name asc)");
+            query = "SELECT * FROM " + indexName;
+            rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+            
+            PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?)");
+            stmt.setString(1, "1");
+            stmt.setString(2, "David");
+            stmt.setString(3, "Smith");
+            stmt.execute();
+            conn.commit();
+            
+            query = "select c.c_customer_sk from  " + tableName + " c "
+                    + "left outer join " + tableName + " c2 on c.c_customer_sk = c2.c_customer_sk "
+                    + "where c.c_customer_sk || c.c_first_name = '1David'";
+            rs = conn.createStatement().executeQuery("EXPLAIN "+query);
+            String explainPlan = QueryUtil.getExplainPlan(rs);
+            if (localIndex) {
+            	assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1,'1David']\n" + 
+                        "    SERVER FILTER BY FIRST KEY ONLY\n" + 
+                        "CLIENT MERGE SORT\n" +
+                        "    PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+                        "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1]\n" + 
+                        "            SERVER FILTER BY FIRST KEY ONLY\n" + 
+                        "        CLIENT MERGE SORT", explainPlan);
+            }
+            else {
+            	assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['1David']\n" + 
+                        "    SERVER FILTER BY FIRST KEY ONLY\n" + 
+                        "    PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+                        "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n" + 
+                        "            SERVER FILTER BY FIRST KEY ONLY", explainPlan);
+            }
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("1", rs.getString(1));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+	}
+
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java
new file mode 100644
index 0000000..659866b
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexWithTableSchemaChangeIT.java
@@ -0,0 +1,375 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.index;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.util.IndexUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+public class IndexWithTableSchemaChangeIT extends ParallelStatsDisabledIT {
+
+    @Test
+    public void testImmutableIndexDropIndexedColumn() throws Exception {
+        helpTestDropIndexedColumn(false, false);
+    }
+    
+    @Test
+    public void testImmutableLocalIndexDropIndexedColumn() throws Exception {
+        helpTestDropIndexedColumn(false, true);
+    }
+    
+    @Test
+    public void testMutableIndexDropIndexedColumn() throws Exception {
+        helpTestDropIndexedColumn(true, false);
+    }
+    
+    @Test
+    public void testMutableLocalIndexDropIndexedColumn() throws Exception {
+        helpTestDropIndexedColumn(true, true);
+    }
+    
+    public void helpTestDropIndexedColumn(boolean mutable, boolean local) throws Exception {
+        String query;
+        ResultSet rs;
+        PreparedStatement stmt;
+
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+
+        try {
+	        conn.setAutoCommit(false);
+	
+	        // make sure that the tables are empty, but reachable
+            conn.createStatement().execute(
+                "CREATE TABLE " + dataTableName
+                        + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+                        + (!mutable ? " IMMUTABLE_ROWS=true" : ""));
+	        query = "SELECT * FROM " + dataTableName ;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (v1 || '_' || v2)");
+	
+	        query = "SELECT * FROM " + dataTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        // load some data into the table
+	        stmt = conn.prepareStatement("UPSERT INTO " + dataTableName + " VALUES(?,?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	
+	        assertIndexExists(conn, dataTableName, true);
+	        conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v1");
+	        assertIndexExists(conn, dataTableName, false);
+	
+	        query = "SELECT * FROM " + dataTableName;
+	        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 " + dataTableName + " VALUES(?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "2");
+	        stmt.execute();
+	        conn.commit();
+	
+	        query = "SELECT * FROM " + dataTableName;
+	        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, String tableName, boolean exists) throws SQLException {
+        ResultSet rs = conn.getMetaData().getIndexInfo(null, null, tableName, 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;
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+
+        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 " + dataTableName
+	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)");
+	        String dataTableQuery = "SELECT * FROM " + dataTableName;
+	        rs = conn.createStatement().executeQuery(dataTableQuery);
+	        assertFalse(rs.next());
+	
+	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (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 " + dataTableName + " VALUES(?,?,?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.setString(4, "j");
+	        stmt.execute();
+	        conn.commit();
+	
+	        assertIndexExists(conn, dataTableName, true);
+	        conn.createStatement().execute("ALTER TABLE " + dataTableName + " DROP COLUMN v2");
+	        assertIndexExists(conn, dataTableName, 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 " + dataTableName + " 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;
+
+        String dataTableName = generateUniqueName();
+        String indexName = generateUniqueName();
+
+        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 "  + dataTableName
+	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)");
+	        String dataTableQuery = "SELECT * FROM " + dataTableName;
+	        rs = conn.createStatement().executeQuery(dataTableQuery);
+	        assertFalse(rs.next());
+	
+	        conn.createStatement().execute("CREATE " + ( local ? "LOCAL" : "") + " INDEX " + indexName + " ON " + dataTableName + " (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 " + dataTableName + " VALUES(?,?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	
+	        assertIndexExists(conn, dataTableName, true);
+	        conn.createStatement().execute("ALTER TABLE " + dataTableName + " ADD v3 VARCHAR, k2 DECIMAL PRIMARY KEY");
+	        rs = conn.getMetaData().getPrimaryKeys("", "", dataTableName);
+	        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 " + dataTableName + "(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();
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
index f2d18d2..7b7c6ad 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableNonTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
 
 import org.junit.runners.Parameterized.Parameters;
 
-public class LocalImmutableNonTxIndexIT extends IndexIT {
+public class LocalImmutableNonTxIndexIT extends BaseIndexIT {
 
     public LocalImmutableNonTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) {
         super(localIndex, mutable, transactional, columnEncoded);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
index 9e6fd5f..5ff6d04 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalImmutableTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
 
 import org.junit.runners.Parameterized.Parameters;
 
-public class LocalImmutableTxIndexIT extends IndexIT {
+public class LocalImmutableTxIndexIT extends BaseIndexIT {
 
     public LocalImmutableTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) {
         super(localIndex, mutable, transactional, columnEncoded);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
index 9785d20..4b9688b 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableNonTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
 
 import org.junit.runners.Parameterized.Parameters;
 
-public class LocalMutableNonTxIndexIT extends IndexIT {
+public class LocalMutableNonTxIndexIT extends BaseIndexIT {
 
     public LocalMutableNonTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) {
         super(localIndex, mutable, transactional, columnEncoded);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/d57385bf/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
index c09a8c0..5f5dd0f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableTxIndexIT.java
@@ -22,7 +22,7 @@ import java.util.Collection;
 
 import org.junit.runners.Parameterized.Parameters;
 
-public class LocalMutableTxIndexIT extends IndexIT {
+public class LocalMutableTxIndexIT extends BaseIndexIT {
 
     public LocalMutableTxIndexIT(boolean localIndex, boolean mutable, boolean transactional, boolean columnEncoded) {
         super(localIndex, mutable, transactional, columnEncoded);


Mime
View raw message