phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [1/2] phoenix git commit: PHOENIX-2414 Parameterize index tests
Date Fri, 13 Nov 2015 08:16:57 GMT
Repository: phoenix
Updated Branches:
  refs/heads/master 461aaa239 -> 9bf95354a


http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java
new file mode 100644
index 0000000..695225f
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexIT.java
@@ -0,0 +1,880 @@
+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 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.sql.Statement;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Properties;
+
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.phoenix.compile.ColumnResolver;
+import org.apache.phoenix.compile.FromCompiler;
+import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.parse.NamedTableNode;
+import org.apache.phoenix.parse.TableName;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.schema.PTable;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Ignore;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+@RunWith(Parameterized.class)
+public class IndexIT extends BaseHBaseManagedTimeIT {
+	
+	private final boolean localIndex;
+	private final String tableDDLOptions;
+	
+	public IndexIT(boolean localIndex, boolean mutable) {
+		this.localIndex = localIndex;
+		StringBuilder optionBuilder = new StringBuilder();
+		if (!mutable) 
+			optionBuilder.append(" IMMUTABLE_ROWS=true ");
+		this.tableDDLOptions = optionBuilder.toString();
+	}
+	
+	@Parameters(name="localIndex = {0} , mutable = {1}")
+    public static Collection<Boolean[]> data() {
+        return Arrays.asList(new Boolean[][] {     
+                 { false, false }, { false, true },
+                 { true, false }, { true, true }
+           });
+    }
+
+	@Test
+    public void testIndexWithNullableFixedWithCols() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName 
+	                    + " (char_col1 ASC, int_col1 ASC)"
+	                    + " INCLUDE (long_col1, long_col2)";
+	        stmt.execute(ddl);
+	        
+	        String query = "SELECT d.char_col1, int_col1 from " + fullTableName + " as d";
+	        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals(
+	                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + tableName + " [-32768]\n" + 
+	                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+	                "CLIENT MERGE SORT",
+	                QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n"
+	                    + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
+	        }
+	        
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("chara", rs.getString(1));
+	        assertEquals("chara", rs.getString("char_col1"));
+	        assertEquals(2, rs.getInt(2));
+	        assertTrue(rs.next());
+	        assertEquals("chara", rs.getString(1));
+	        assertEquals(3, rs.getInt(2));
+	        assertTrue(rs.next());
+	        assertEquals("chara", rs.getString(1));
+	        assertEquals(4, rs.getInt(2));
+	        assertFalse(rs.next());
+	        
+	        conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName);
+	        
+	        query = "SELECT char_col1, int_col1 from " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        
+	        query = "SELECT char_col1, int_col1 from "+indexName;
+	        try{
+	            rs = conn.createStatement().executeQuery(query);
+	            fail();
+	        } catch (SQLException e) {
+	            assertEquals(SQLExceptionCode.TABLE_UNDEFINED.getErrorCode(), e.getErrorCode());
+	        }
+        }
+    }
+    
+    @Test
+    @Ignore("Failing due to zero byte incorrectly being stripped from row key") // FIXME: fixed in master, so remove this ignore tag when merged.
+    public void testDeleteFromAllPKColumnIndex() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName
+	                    + " (long_pk, varchar_pk)"
+	                    + " INCLUDE (long_col1, long_col2)";
+	        stmt.execute(ddl);
+	        
+	        ResultSet rs;
+	        
+	        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName);
+	        assertTrue(rs.next());
+	        assertEquals(3,rs.getInt(1));
+	        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName);
+	        assertTrue(rs.next());
+	        assertEquals(3,rs.getInt(1));
+	        
+	        String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4";
+	        assertEquals(1,conn.createStatement().executeUpdate(dml));
+	        conn.commit();
+	        
+	        String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(1L, rs.getLong(1));
+	        assertTrue(rs.next());
+	        assertEquals(3L, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        query = "SELECT long_pk FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(1L, rs.getLong(1));
+	        assertTrue(rs.next());
+	        assertEquals(3L, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(1L, rs.getLong(1));
+	        assertTrue(rs.next());
+	        assertEquals(3L, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName);
+        }
+    }
+    
+    @Test
+    public void testDeleteFromNonPKColumnIndex() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        // create unique table and index names for each parameterized test
+        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName
+	                    + " (long_col1, long_col2)"
+	                    + " INCLUDE (decimal_col1, decimal_col2)";
+	        stmt.execute(ddl);
+        }
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {    
+	        ResultSet rs;
+	        
+	        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullTableName);
+	        assertTrue(rs.next());
+	        assertEquals(3,rs.getInt(1));
+	        rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM " + fullIndexName);
+	        assertTrue(rs.next());
+	        assertEquals(3,rs.getInt(1));
+	        
+	        String dml = "DELETE from " + fullTableName + " WHERE long_col2 = 4";
+	        assertEquals(1,conn.createStatement().executeUpdate(dml));
+	        conn.commit();
+
+	        // query the data table
+	        String query = "SELECT /*+ NO_INDEX */ long_pk FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(1L, rs.getLong(1));
+	        assertTrue(rs.next());
+	        assertEquals(3L, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        // query the index table
+	        query = "SELECT long_pk FROM " + fullTableName + " ORDER BY long_col1";
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(1L, rs.getLong(1));
+	        assertTrue(rs.next());
+	        assertEquals(3L, rs.getLong(1));
+	        assertFalse(rs.next());
+	        
+	        conn.createStatement().execute("DROP INDEX " + indexName + " ON " + fullTableName);
+        }
+    }
+    
+    @Test
+    public void testGroupByCount() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+    	try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)";
+	        stmt.execute(ddl);
+	        ResultSet rs;
+	        rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " + fullTableName + " GROUP BY int_col2");
+	        assertTrue(rs.next());
+	        assertEquals(1,rs.getInt(2));
+    	}
+    }
+
+    @Test
+    public void testSelectDistinctOnTableWithSecondaryImmutableIndex() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+    	try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col2)";
+            PreparedStatement pstmt = conn.prepareStatement(ddl);
+            pstmt.execute();
+            ResultSet rs = conn.createStatement().executeQuery("SELECT distinct int_col2 FROM " + fullTableName + " where int_col2 > 0");
+            assertTrue(rs.next());
+            assertEquals(3, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(4, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(5, rs.getInt(1));
+            assertFalse(rs.next());
+    	}
+    }
+
+    @Test
+    public void testInClauseWithIndexOnColumnOfUsignedIntType() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+    	try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String ddl ="CREATE TABLE " + fullTableName + BaseTest.TEST_TABLE_SCHEMA + tableDDLOptions;
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+	        BaseTest.populateTestTable(fullTableName);
+	        ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + " (int_col1)";
+            stmt.execute(ddl);
+            ResultSet rs = conn.createStatement().executeQuery("SELECT int_col1 FROM " + fullTableName + " where int_col1 IN (1, 2, 3, 4)");
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(3, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(4, rs.getInt(1));
+            assertFalse(rs.next());
+    	}
+    }
+    
+    @Test
+    public void createIndexOnTableWithSpecifiedDefaultCF() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+            ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        String ddl ="CREATE TABLE " + fullTableName 
+	        		+ " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) DEFAULT_COLUMN_FAMILY='A'" + (!tableDDLOptions.isEmpty() ? "," + tableDDLOptions : "");
+	        Statement stmt = conn.createStatement();
+	        stmt.execute(ddl);
+
+	        query = "SELECT * FROM " + tableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+
+	        String options = localIndex ? "SALT_BUCKETS=10, MULTI_TENANT=true, IMMUTABLE_ROWS=true, DISABLE_WAL=true" : "";
+	        conn.createStatement().execute(
+	                "CREATE INDEX " + indexName + " ON " + fullTableName + " (v1) INCLUDE (v2) " + options);
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        
+	        //check options set correctly on index
+	        TableName indexTableName = TableName.create(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        NamedTableNode indexNode = NamedTableNode.create(null, indexTableName, null);
+	        ColumnResolver resolver = FromCompiler.getResolver(indexNode, conn.unwrap(PhoenixConnection.class));
+	        PTable indexTable = resolver.getTables().get(0).getTable();
+	        // Can't set IMMUTABLE_ROWS, MULTI_TENANT or DEFAULT_COLUMN_FAMILY_NAME on an index
+	        assertNull(indexTable.getDefaultFamilyName());
+	        assertFalse(indexTable.isMultiTenant());
+	        assertFalse(indexTable.isImmutableRows());
+	        if(localIndex) {
+	            assertEquals(10, indexTable.getBucketNum().intValue());
+	            assertTrue(indexTable.isWALDisabled());
+	        }
+        }
+    }
+    
+    @Test
+    public void testIndexWithNullableDateCol() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+            Date date = new Date(System.currentTimeMillis());
+            
+            createMultiCFTestTable(fullTableName, tableDDLOptions);
+            populateMultiCFTestTable(fullTableName, date);
+            String ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (date_col)";
+            PreparedStatement stmt = conn.prepareStatement(ddl);
+            stmt.execute();
+            
+            String query = "SELECT int_pk from " + fullTableName ;
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if (localIndex) {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\n"
+                           + "    SERVER FILTER BY FIRST KEY ONLY\n"
+                           + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n"
+                        + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
+            }
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(1, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(3, rs.getInt(1));
+            assertFalse(rs.next());
+            
+            query = "SELECT date_col from " + fullTableName + " order by date_col" ;
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if (localIndex) {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\n"
+                        + "    SERVER FILTER BY FIRST KEY ONLY\n"
+                        + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n"
+                        + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
+            }
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(date, rs.getDate(1));
+            assertTrue(rs.next());
+            assertEquals(new Date(date.getTime() + TestUtil.MILLIS_IN_DAY), rs.getDate(1));
+            assertTrue(rs.next());
+            assertEquals(new Date(date.getTime() + 2 * TestUtil.MILLIS_IN_DAY), rs.getDate(1));
+            assertFalse(rs.next());
+        } 
+    }
+    
+    @Test
+    public void testSelectAllAndAliasWithIndex() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        
+	        String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) " + tableDDLOptions;
+			conn.createStatement().execute(ddl);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        
+	        ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (v1)";
+	        conn.createStatement().execute(ddl);
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+
+	        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " 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 * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex){
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+	        }
+
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("b",rs.getString(1));
+	        assertEquals("y",rs.getString(2));
+	        assertEquals("2",rs.getString(3));
+	        assertEquals("b",rs.getString("k"));
+	        assertEquals("y",rs.getString("v1"));
+	        assertEquals("2",rs.getString("v2"));
+	        assertTrue(rs.next());
+	        assertEquals("a",rs.getString(1));
+	        assertEquals("x",rs.getString(2));
+	        assertEquals("1",rs.getString(3));
+	        assertEquals("a",rs.getString("k"));
+	        assertEquals("x",rs.getString("v1"));
+	        assertEquals("1",rs.getString("v2"));
+	        assertFalse(rs.next());
+	        
+	        query = "SELECT v1 as foo FROM " + fullTableName + " WHERE v2 = '1' ORDER BY foo";
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex){
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" +fullTableName + " [-32768,~'1']\n" + 
+	                    "    SERVER SORTED BY [\"V1\"]\n" + 
+	                    "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +fullIndexName + " [~'1']\n" + 
+	                    "    SERVER SORTED BY [\"V1\"]\n" + 
+	                    "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+	        }
+
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x",rs.getString(1));
+	        assertEquals("x",rs.getString("foo"));
+	        assertFalse(rs.next());
+        }
+    }
+    
+    @Test
+    public void testSelectCF() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        
+	        String ddl = "CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, a.v1 VARCHAR, a.v2 VARCHAR, b.v1 VARCHAR) " + tableDDLOptions;
+			conn.createStatement().execute(ddl);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        ddl = "CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v2 DESC) INCLUDE (a.v1)";
+	        conn.createStatement().execute(ddl);
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.setString(4, "A");
+	        stmt.execute();
+	        stmt.setString(1,"b");
+	        stmt.setString(2, "y");
+	        stmt.setString(3, "2");
+	        stmt.setString(4, "B");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullTableName, QueryUtil.getExplainPlan(rs));
+	
+	        query = "SELECT a.* FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+	        }
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("y",rs.getString(1));
+	        assertEquals("2",rs.getString(2));
+	        assertEquals("y",rs.getString("v1"));
+	        assertEquals("2",rs.getString("v2"));
+	        assertTrue(rs.next());
+	        assertEquals("x",rs.getString(1));
+	        assertEquals("1",rs.getString(2));
+	        assertEquals("x",rs.getString("v1"));
+	        assertEquals("1",rs.getString("v2"));
+	        assertFalse(rs.next());
+        }
+    }
+    
+    @Test
+    public void testUpsertAfterIndexDrop() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+    
+	        // make sure that the tables are empty, but reachable
+	        conn.createStatement().execute(
+	          "CREATE TABLE " + fullTableName
+	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	    
+	        conn.createStatement().execute(
+	          "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (v1, v2)");
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	    
+	        // load some data into the table
+	        PreparedStatement stmt =
+	            conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        // make sure the index is working as expected
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x", rs.getString(1));
+	        assertEquals("1", rs.getString(2));
+	        assertEquals("a", rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        String ddl = "DROP INDEX " + indexName + " ON " + fullTableName;
+	        stmt = conn.prepareStatement(ddl);
+	        stmt.execute();
+	        
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k, v1) VALUES(?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "y");
+	        stmt.execute();
+	        conn.commit();
+	    
+	        query = "SELECT * FROM " + fullTableName;
+	    
+	        // check that the data table matches as expected
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a", rs.getString(1));
+	        assertEquals("y", rs.getString(2));
+	        assertFalse(rs.next());
+        }
+    }
+    
+    @Test
+    public void testMultipleUpdatesAcrossRegions() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+    
+	        // make sure that the tables are empty, but reachable
+	        conn.createStatement().execute(
+	          "CREATE TABLE " + fullTableName
+	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) "  + HTableDescriptor.MAX_FILESIZE + "=1, " + HTableDescriptor.MEMSTORE_FLUSHSIZE + "=1 " 
+	        		  + (!tableDDLOptions.isEmpty() ? "," + tableDDLOptions : "") + "SPLIT ON ('b')");
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        conn.createStatement().execute(
+	  	          "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (v1, v2)");
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	    
+	        // load some data into the table
+	        PreparedStatement stmt =
+	            conn.prepareStatement("UPSERT INTO " + fullTableName + " 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();
+	        stmt.setString(1, "c");
+	        stmt.setString(2, "z");
+	        stmt.setString(3, "3");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        // make sure the index is working as expected
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x", rs.getString(1));
+	        assertEquals("1", rs.getString(2));
+	        assertEquals("a", rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("y", rs.getString(1));
+	        assertEquals("2", rs.getString(2));
+	        assertEquals("b", rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("z", rs.getString(1));
+	        assertEquals("3", rs.getString(2));
+	        assertEquals("c", rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if (localIndex) {
+	            assertEquals("CLIENT PARALLEL 2-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n"
+	                       + "    SERVER FILTER BY FIRST KEY ONLY\n"
+	                       + "CLIENT MERGE SORT",
+	                QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n"
+	                    + "    SERVER FILTER BY FIRST KEY ONLY",
+	                QueryUtil.getExplainPlan(rs));
+	        }
+	    
+	        // check that the data table matches as expected
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a", rs.getString(1));
+	        assertEquals("x", rs.getString(2));
+	        assertEquals("1", rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("b", rs.getString(1));
+	        assertEquals("y", rs.getString(2));
+	        assertEquals("2", rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("c", rs.getString(1));
+	        assertEquals("z", rs.getString(2));
+	        assertEquals("3", rs.getString(3));
+	        assertFalse(rs.next());
+        }
+    }
+    
+    @Test
+    public void testIndexWithCaseSensitiveCols() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        
+            conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, \"V1\" VARCHAR, \"v2\" VARCHAR)"+tableDDLOptions);
+            query = "SELECT * FROM "+fullTableName;
+            rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+            conn.createStatement().execute(
+  	  	          "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(\"v2\") INCLUDE (\"V1\")");
+            query = "SELECT * FROM "+fullIndexName;
+            rs = conn.createStatement().executeQuery(query);
+            assertFalse(rs.next());
+
+            PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " 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 * FROM " + fullTableName + " WHERE \"v2\" = '1'";
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if(localIndex){
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768,'1']\n"
+                           + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + fullIndexName + " ['1']", QueryUtil.getExplainPlan(rs));
+            }
+
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("a",rs.getString(1));
+            assertEquals("x",rs.getString(2));
+            assertEquals("1",rs.getString(3));
+            assertEquals("a",rs.getString("k"));
+            assertEquals("x",rs.getString("V1"));
+            assertEquals("1",rs.getString("v2"));
+            assertFalse(rs.next());
+
+            query = "SELECT \"V1\", \"V1\" as foo1, \"v2\" as foo, \"v2\" as \"Foo1\", \"v2\" FROM " + fullTableName + " ORDER BY foo";
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if(localIndex){
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\nCLIENT MERGE SORT",
+                    QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER "+fullIndexName, 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("1",rs.getString(3));
+            assertEquals("1",rs.getString("Foo"));
+            assertEquals("1",rs.getString(4));
+            assertEquals("1",rs.getString("Foo1"));
+            assertEquals("1",rs.getString(5));
+            assertEquals("1",rs.getString("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("2",rs.getString(3));
+            assertEquals("2",rs.getString("Foo"));
+            assertEquals("2",rs.getString(4));
+            assertEquals("2",rs.getString("Foo1"));
+            assertEquals("2",rs.getString(5));
+            assertEquals("2",rs.getString("v2"));
+            assertFalse(rs.next());
+        } 
+    }
+
+    @Test
+    public void testInFilterOnIndexedTable() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        
+	        String ddl = "CREATE TABLE " + fullTableName +"  (PK1 CHAR(2) NOT NULL PRIMARY KEY, CF1.COL1 BIGINT) " + tableDDLOptions;
+	        conn.createStatement().execute(ddl);
+	        ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + "(COL1)";
+	        conn.createStatement().execute(ddl);
+	
+	        query = "SELECT COUNT(COL1) FROM " + fullTableName +" WHERE COL1 IN (1,25,50,75,100)"; 
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+        } 
+    }
+
+    @Test
+    public void testIndexWithDecimalCol() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+            Date date = new Date(System.currentTimeMillis());
+            
+            createMultiCFTestTable(fullTableName, tableDDLOptions);
+            populateMultiCFTestTable(fullTableName, date);
+            String ddl = null;
+            ddl = "CREATE " + (localIndex ? "LOCAL " : "") + "INDEX " + indexName + " ON " + fullTableName + " (decimal_pk) INCLUDE (decimal_col1, decimal_col2)";
+            PreparedStatement stmt = conn.prepareStatement(ddl);
+            stmt.execute();
+            
+            query = "SELECT decimal_pk, decimal_col1, decimal_col2 from " + fullTableName ;
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if(localIndex) {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+            }
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(new BigDecimal("1.1"), rs.getBigDecimal(1));
+            assertEquals(new BigDecimal("2.1"), rs.getBigDecimal(2));
+            assertEquals(new BigDecimal("3.1"), rs.getBigDecimal(3));
+            assertTrue(rs.next());
+            assertEquals(new BigDecimal("2.2"), rs.getBigDecimal(1));
+            assertEquals(new BigDecimal("3.2"), rs.getBigDecimal(2));
+            assertEquals(new BigDecimal("4.2"), rs.getBigDecimal(3));
+            assertTrue(rs.next());
+            assertEquals(new BigDecimal("3.3"), rs.getBigDecimal(1));
+            assertEquals(new BigDecimal("4.3"), rs.getBigDecimal(2));
+            assertEquals(new BigDecimal("5.3"), rs.getBigDecimal(3));
+            assertFalse(rs.next());
+        } 
+    }
+    
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java
deleted file mode 100644
index fe17dbc..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalMutableIndexIT.java
+++ /dev/null
@@ -1,26 +0,0 @@
-/*
- * 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;
-
-public class LocalMutableIndexIT extends BaseMutableIndexIT {
-
-    public LocalMutableIndexIT() {
-        super(true);
-    }
-
-}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java
new file mode 100644
index 0000000..b269212
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/MutableIndexIT.java
@@ -0,0 +1,599 @@
+/*
+ * 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.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Properties;
+
+import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.schema.PTableKey;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.primitives.Doubles;
+
+@RunWith(Parameterized.class)
+public class MutableIndexIT extends BaseHBaseManagedTimeIT {
+    
+    protected final boolean localIndex;
+    private final String tableDDLOptions;
+	
+    public MutableIndexIT(boolean localIndex) {
+		this.localIndex = localIndex;
+		StringBuilder optionBuilder = new StringBuilder();
+		this.tableDDLOptions = optionBuilder.toString();
+	}
+	
+	@Parameters(name="localIndex = {0}")
+    public static Collection<Boolean[]> data() {
+        return Arrays.asList(new Boolean[][] {     
+                 { false }, {true}
+           });
+    }
+    
+    @Test
+    public void testCoveredColumnUpdates() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        
+            createMultiCFTestTable(fullTableName, tableDDLOptions);
+            populateMultiCFTestTable(fullTableName);
+            PreparedStatement stmt = conn.prepareStatement("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName 
+            		+ " (char_col1 ASC, int_col1 ASC) INCLUDE (long_col1, long_col2)");
+            stmt.execute();
+            
+            String query = "SELECT char_col1, int_col1, long_col2 from " + fullTableName;
+            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            if (localIndex) {
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+            } else {
+                assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+            }
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(2, rs.getInt(2));
+            assertEquals(3L, rs.getLong(3));
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(3, rs.getInt(2));
+            assertEquals(4L, rs.getLong(3));
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(4, rs.getInt(2));
+            assertEquals(5L, rs.getLong(3));
+            assertFalse(rs.next());
+            
+            stmt = conn.prepareStatement("UPSERT INTO " + fullTableName
+                    + "(varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2) SELECT varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2*2 FROM "
+                    + fullTableName + " WHERE long_col2=?");
+            stmt.setLong(1,4L);
+            assertEquals(1,stmt.executeUpdate());
+            conn.commit();
+
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(2, rs.getInt(2));
+            assertEquals(3L, rs.getLong(3));
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(3, rs.getInt(2));
+            assertEquals(8L, rs.getLong(3));
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(4, rs.getInt(2));
+            assertEquals(5L, rs.getLong(3));
+            assertFalse(rs.next());
+            
+            stmt = conn.prepareStatement("UPSERT INTO " + fullTableName
+                    + "(varchar_pk, char_pk, int_pk, long_pk , decimal_pk, long_col2) SELECT varchar_pk, char_pk, int_pk, long_pk , decimal_pk, null FROM "
+                    + fullTableName + " WHERE long_col2=?");
+            stmt.setLong(1,3L);
+            assertEquals(1,stmt.executeUpdate());
+            conn.commit();
+            
+            rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(2, rs.getInt(2));
+            assertEquals(0, rs.getLong(3));
+            assertTrue(rs.wasNull());
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(3, rs.getInt(2));
+            assertEquals(8L, rs.getLong(3));
+            assertTrue(rs.next());
+            assertEquals("chara", rs.getString(1));
+            assertEquals(4, rs.getInt(2));
+            assertEquals(5L, rs.getLong(3));
+            assertFalse(rs.next());
+            if(localIndex) {
+                query = "SELECT b.* from " + fullTableName + " where int_col1 = 4";
+                rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+                assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName +" [-32768]\n" +
+                		"    SERVER FILTER BY TO_INTEGER(\"INT_COL1\") = 4\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+                rs = conn.createStatement().executeQuery(query);
+                assertTrue(rs.next());
+                assertEquals("varchar_b", rs.getString(1));
+                assertEquals("charb", rs.getString(2));
+                assertEquals(5, rs.getInt(3));
+                assertEquals(5, rs.getLong(4));
+                assertFalse(rs.next());
+                
+            }
+        } 
+    }
+    
+    @Test
+    public void testCoveredColumns() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	        conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        
+	        conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1) INCLUDE (v2)");
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x",rs.getString(1));
+	        assertEquals("a",rs.getString(2));
+	        assertEquals("1",rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2, null);
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x",rs.getString(1));
+	        assertEquals("a",rs.getString(2));
+	        assertNull(rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));            
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+	        }
+	
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a",rs.getString(1));
+	        assertEquals("x",rs.getString(2));
+	        assertNull(rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2,"3");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName + " [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));            
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+	        }
+	        
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a",rs.getString(1));
+	        assertEquals("x",rs.getString(2));
+	        assertEquals("3",rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2,"4");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));            
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName, QueryUtil.getExplainPlan(rs));
+	        }
+	        
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a",rs.getString(1));
+	        assertEquals("x",rs.getString(2));
+	        assertEquals("4",rs.getString(3));
+	        assertFalse(rs.next());
+        }
+    }
+
+    @Test
+    public void testCompoundIndexKey() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+	
+	        // make sure that the tables are empty, but reachable
+	        conn.createStatement().execute("CREATE TABLE " + fullTableName + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	        conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1, v2)");
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        // load some data into the table
+	        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x",rs.getString(1));
+	        assertEquals("1",rs.getString(2));
+	        assertEquals("a",rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)");
+	        stmt.setString(1,"a");
+	        stmt.setString(2, "y");
+	        stmt.setString(3, null);
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("y",rs.getString(1));
+	        assertNull(rs.getString(2));
+	        assertEquals("a",rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if (localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n"
+	                    + "    SERVER FILTER BY FIRST KEY ONLY\n"
+	                    + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n"
+	                       + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
+	        }
+	        //make sure the data table looks like what we expect
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a",rs.getString(1));
+	        assertEquals("y",rs.getString(2));
+	        assertNull(rs.getString(3));
+	        assertFalse(rs.next());
+	        
+	        // Upsert new row with null leading index column
+	        stmt.setString(1,"b");
+	        stmt.setString(2, null);
+	        stmt.setString(3, "3");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals(null,rs.getString(1));
+	        assertEquals("3",rs.getString(2));
+	        assertEquals("b",rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("y",rs.getString(1));
+	        assertNull(rs.getString(2));
+	        assertEquals("a",rs.getString(3));
+	        assertFalse(rs.next());
+	
+	        // Update row with null leading index column to have a value
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?)");
+	        stmt.setString(1,"b");
+	        stmt.setString(2, "z");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("y",rs.getString(1));
+	        assertNull(rs.getString(2));
+	        assertEquals("a",rs.getString(3));
+	        assertTrue(rs.next());
+	        assertEquals("z",rs.getString(1));
+	        assertEquals("3",rs.getString(2));
+	        assertEquals("b",rs.getString(3));
+	        assertFalse(rs.next());
+        }
+
+    }
+    
+    /**
+     * There was a case where if there were multiple updates to a single row in the same batch, the
+     * index wouldn't be updated correctly as each element of the batch was evaluated with the state
+     * previous to the batch, rather than with the rest of the batch. This meant you could do a put
+     * and a delete on a row in the same batch and the index result would contain the current + put
+     * and current + delete, but not current + put + delete.
+     * @throws Exception on failure
+     */
+    @Test
+    public void testMultipleUpdatesToSingleRow() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        String query;
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+    
+	        // make sure that the tables are empty, but reachable
+	        conn.createStatement().execute(
+	          "CREATE TABLE " + fullTableName
+	              + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)" + tableDDLOptions);
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        conn.createStatement().execute("CREATE " + (localIndex ? " LOCAL " : "") + " INDEX " + indexName + " ON " + fullTableName + " (v1, v2)");
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	    
+	        // load some data into the table
+	        PreparedStatement stmt =
+	            conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "x");
+	        stmt.setString(3, "1");
+	        stmt.execute();
+	        conn.commit();
+	        
+	        // make sure the index is working as expected
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("x", rs.getString(1));
+	        assertEquals("1", rs.getString(2));
+	        assertEquals("a", rs.getString(3));
+	        assertFalse(rs.next());
+	      
+	        // do multiple updates to the same row, in the same batch
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k, v1) VALUES(?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, "y");
+	        stmt.execute();
+	        stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + "(k,v2) VALUES(?,?)");
+	        stmt.setString(1, "a");
+	        stmt.setString(2, null);
+	        stmt.execute();
+	        conn.commit();
+	    
+	        query = "SELECT * FROM " + fullIndexName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("y", rs.getString(1));
+	        assertNull(rs.getString(2));
+	        assertEquals("a", rs.getString(3));
+	        assertFalse(rs.next());
+	    
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+	        if(localIndex) {
+	            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_" + fullTableName+" [-32768]\n"
+	                    + "    SERVER FILTER BY FIRST KEY ONLY\n"
+	                    + "CLIENT MERGE SORT",
+	                QueryUtil.getExplainPlan(rs));
+	        } else {
+	            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + fullIndexName + "\n"
+	                    + "    SERVER FILTER BY FIRST KEY ONLY",
+	                QueryUtil.getExplainPlan(rs));
+	        }
+	    
+	        // check that the data table matches as expected
+	        rs = conn.createStatement().executeQuery(query);
+	        assertTrue(rs.next());
+	        assertEquals("a", rs.getString(1));
+	        assertEquals("y", rs.getString(2));
+	        assertNull(rs.getString(3));
+	        assertFalse(rs.next());
+        }
+    }
+    
+    @Test
+    public void testUpsertingNullForIndexedColumns() throws Exception {
+    	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        ResultSet rs;
+	        // create unique table and index names for each parameterized test
+	        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+	        String indexName = "IDX"  + "_" + System.currentTimeMillis();
+	        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+	        String fullIndexeName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, indexName);
+    		Statement stmt = conn.createStatement();
+    		stmt.execute("CREATE TABLE " + fullTableName + "(v1 VARCHAR PRIMARY KEY, v2 DOUBLE, v3 VARCHAR) "+tableDDLOptions);
+    		stmt.execute("CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullTableName + "  (v2) INCLUDE(v3)");
+    		
+    		//create a row with value null for indexed column v2
+    		stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', null, 'abc')");
+    		conn.commit();
+    		
+    		//assert values in index table 
+    		rs = stmt.executeQuery("select * from " + fullIndexeName);
+    		assertTrue(rs.next());
+    		assertEquals(0, Doubles.compare(0, rs.getDouble(1)));
+    		assertTrue(rs.wasNull());
+    		assertEquals("cc1", rs.getString(2));
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    		
+    		//assert values in data table
+    		rs = stmt.executeQuery("select v1, v2, v3 from " + fullTableName);
+    		assertTrue(rs.next());
+    		assertEquals("cc1", rs.getString(1));
+    		assertEquals(0, Doubles.compare(0, rs.getDouble(2)));
+    		assertTrue(rs.wasNull());
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    		
+    		//update the previously null value for indexed column v2 to a non-null value 1.23
+    		stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', 1.23, 'abc')");
+    		conn.commit();
+    		
+    		//assert values in data table
+    		rs = stmt.executeQuery("select /*+ NO_INDEX */ v1, v2, v3 from " + fullTableName);
+    		assertTrue(rs.next());
+    		assertEquals("cc1", rs.getString(1));
+    		assertEquals(0, Doubles.compare(1.23, rs.getDouble(2)));
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    		
+    		//assert values in index table 
+    		rs = stmt.executeQuery("select * from " + fullIndexeName);
+    		assertTrue(rs.next());
+    		assertEquals(0, Doubles.compare(1.23, rs.getDouble(1)));
+    		assertEquals("cc1", rs.getString(2));
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    		
+    		//update the value for indexed column v2 back to null
+    		stmt.executeUpdate("upsert into " + fullTableName + " values('cc1', null, 'abc')");
+    		conn.commit();
+    		
+    		//assert values in index table 
+    		rs = stmt.executeQuery("select * from " + fullIndexeName);
+    		assertTrue(rs.next());
+    		assertEquals(0, Doubles.compare(0, rs.getDouble(1)));
+    		assertTrue(rs.wasNull());
+    		assertEquals("cc1", rs.getString(2));
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    		
+    		//assert values in data table
+    		rs = stmt.executeQuery("select v1, v2, v3 from " + fullTableName);
+    		assertTrue(rs.next());
+    		assertEquals("cc1", rs.getString(1));
+    		assertEquals(0, Doubles.compare(0, rs.getDouble(2)));
+    		assertEquals("abc", rs.getString(3));
+    		assertFalse(rs.next());
+    	} 
+    }
+    
+	
+    private void assertImmutableRows(Connection conn, String fullTableName, boolean expectedValue) throws SQLException {
+        PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+        assertEquals(expectedValue, pconn.getTable(new PTableKey(pconn.getTenantId(), fullTableName)).isImmutableRows());
+    }
+    
+    @Test
+    public void testAlterTableWithImmutability() throws Exception {
+        String query;
+        ResultSet rs;
+        String tableName = TestUtil.DEFAULT_DATA_TABLE_NAME + "_" + System.currentTimeMillis();
+        String fullTableName = SchemaUtil.getTableName(TestUtil.DEFAULT_SCHEMA_NAME, tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+	        conn.setAutoCommit(false);
+	        conn.createStatement().execute(
+	            "CREATE TABLE " + fullTableName +" (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) " + tableDDLOptions);
+	        
+	        query = "SELECT * FROM " + fullTableName;
+	        rs = conn.createStatement().executeQuery(query);
+	        assertFalse(rs.next());
+	
+	        assertImmutableRows(conn,fullTableName, false);
+	        conn.createStatement().execute("ALTER TABLE " + fullTableName +" SET IMMUTABLE_ROWS=true");
+	        assertImmutableRows(conn,fullTableName, true);
+	        
+	        
+	        conn.createStatement().execute("ALTER TABLE " + fullTableName +" SET immutable_rows=false");
+	        assertImmutableRows(conn,fullTableName, false);
+        }
+    }
+
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bf95354/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
index 5265afc..1f9b20c 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
@@ -136,6 +136,7 @@ import org.apache.phoenix.schema.PTableType;
 import org.apache.phoenix.schema.TableAlreadyExistsException;
 import org.apache.phoenix.schema.TableNotFoundException;
 import org.apache.phoenix.util.ConfigUtil;
+import org.apache.phoenix.util.DateUtil;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.PropertiesUtil;
 import org.apache.phoenix.util.QueryUtil;
@@ -164,6 +165,26 @@ import com.google.common.collect.Sets;
  *
  */
 public abstract class BaseTest {
+    protected static final String TEST_TABLE_SCHEMA = "(" +
+            "   varchar_pk VARCHAR NOT NULL, " +
+            "   char_pk CHAR(6) NOT NULL, " +
+            "   int_pk INTEGER NOT NULL, "+ 
+            "   long_pk BIGINT NOT NULL, " +
+            "   decimal_pk DECIMAL(31, 10) NOT NULL, " +
+            "   date_pk DATE NOT NULL, " +
+            "   a.varchar_col1 VARCHAR, " +
+            "   a.char_col1 CHAR(10), " +
+            "   a.int_col1 INTEGER, " +
+            "   a.long_col1 BIGINT, " +
+            "   a.decimal_col1 DECIMAL(31, 10), " +
+            "   a.date1 DATE, " +
+            "   b.varchar_col2 VARCHAR, " +
+            "   b.char_col2 CHAR(10), " +
+            "   b.int_col2 INTEGER, " +
+            "   b.long_col2 BIGINT, " +
+            "   b.decimal_col2 DECIMAL(31, 10), " +
+            "   b.date2 DATE " +
+            "   CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk, date_pk)) ";
     private static final Map<String,String> tableDDLMap;
     private static final Logger logger = LoggerFactory.getLogger(BaseTest.class);
 
@@ -1714,7 +1735,81 @@ public abstract class BaseTest {
         return utility;
     }
 
-    protected static void createMultiCFTestTable(String tableName) throws SQLException {
+    // Populate the test table with data.
+    public static void populateTestTable(String fullTableName) throws SQLException {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+            String upsert = "UPSERT INTO " + fullTableName
+                    + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+            PreparedStatement stmt = conn.prepareStatement(upsert);
+            stmt.setString(1, "varchar1");
+            stmt.setString(2, "char1");
+            stmt.setInt(3, 1);
+            stmt.setLong(4, 1L);
+            stmt.setBigDecimal(5, new BigDecimal(1.0));
+            Date date = DateUtil.parseDate("2015-01-01 00:00:00");
+            stmt.setDate(6, date);
+            stmt.setString(7, "varchar_a");
+            stmt.setString(8, "chara");
+            stmt.setInt(9, 2);
+            stmt.setLong(10, 2L);
+            stmt.setBigDecimal(11, new BigDecimal(2.0));
+            stmt.setDate(12, date);
+            stmt.setString(13, "varchar_b");
+            stmt.setString(14, "charb");
+            stmt.setInt(15, 3);
+            stmt.setLong(16, 3L);
+            stmt.setBigDecimal(17, new BigDecimal(3.0));
+            stmt.setDate(18, date);
+            stmt.executeUpdate();
+            
+            stmt.setString(1, "varchar2");
+            stmt.setString(2, "char2");
+            stmt.setInt(3, 2);
+            stmt.setLong(4, 2L);
+            stmt.setBigDecimal(5, new BigDecimal(2.0));
+            date = DateUtil.parseDate("2015-01-02 00:00:00");
+            stmt.setDate(6, date);
+            stmt.setString(7, "varchar_a");
+            stmt.setString(8, "chara");
+            stmt.setInt(9, 3);
+            stmt.setLong(10, 3L);
+            stmt.setBigDecimal(11, new BigDecimal(3.0));
+            stmt.setDate(12, date);
+            stmt.setString(13, "varchar_b");
+            stmt.setString(14, "charb");
+            stmt.setInt(15, 4);
+            stmt.setLong(16, 4L);
+            stmt.setBigDecimal(17, new BigDecimal(4.0));
+            stmt.setDate(18, date);
+            stmt.executeUpdate();
+            
+            stmt.setString(1, "varchar3");
+            stmt.setString(2, "char3");
+            stmt.setInt(3, 3);
+            stmt.setLong(4, 3L);
+            stmt.setBigDecimal(5, new BigDecimal(3.0));
+            date = DateUtil.parseDate("2015-01-03 00:00:00");
+            stmt.setDate(6, date);
+            stmt.setString(7, "varchar_a");
+            stmt.setString(8, "chara");
+            stmt.setInt(9, 4);
+            stmt.setLong(10, 4L);
+            stmt.setBigDecimal(11, new BigDecimal(4.0));
+            stmt.setDate(12, date);
+            stmt.setString(13, "varchar_b");
+            stmt.setString(14, "charb");
+            stmt.setInt(15, 5);
+            stmt.setLong(16, 5L);
+            stmt.setBigDecimal(17, new BigDecimal(5.0));
+            stmt.setDate(18, date);
+            stmt.executeUpdate();
+            
+            conn.commit();
+        }
+    }
+
+    protected static void createMultiCFTestTable(String tableName, String options) throws SQLException {
         String ddl = "create table if not exists " + tableName + "(" +
                 "   varchar_pk VARCHAR NOT NULL, " +
                 "   char_pk CHAR(5) NOT NULL, " +
@@ -1732,13 +1827,14 @@ public abstract class BaseTest {
                 "   b.long_col2 BIGINT, " +
                 "   b.decimal_col2 DECIMAL, " +
                 "   b.date_col DATE " + 
-                "   CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk))";
+                "   CONSTRAINT pk PRIMARY KEY (varchar_pk, char_pk, int_pk, long_pk DESC, decimal_pk)) "
+                + (options!=null? options : "");
             Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
             Connection conn = DriverManager.getConnection(getUrl(), props);
             conn.createStatement().execute(ddl);
             conn.close();
     }
-    
+        
     // Populate the test table with data.
     protected static void populateMultiCFTestTable(String tableName) throws SQLException {
         populateMultiCFTestTable(tableName, null);


Mime
View raw message