phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ramkris...@apache.org
Subject [3/3] git commit: PHOENIX-1015 Support joining back to data table row from local index when query condition involves leading columns in local index (Rajeshbabu)
Date Fri, 04 Jul 2014 10:22:07 GMT
PHOENIX-1015 Support joining back to data table row from local index when
query condition involves leading columns in local index (Rajeshbabu)


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/6e1fcc8d
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/6e1fcc8d
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/6e1fcc8d

Branch: refs/heads/local-index
Commit: 6e1fcc8dc82daa611e52747ead529134ec47e96b
Parents: aa2559f
Author: Ramkrishna <ramkrishna.s.vasudevan@intel.com>
Authored: Fri Jul 4 15:49:41 2014 +0530
Committer: Ramkrishna <ramkrishna.s.vasudevan@intel.com>
Committed: Fri Jul 4 15:49:41 2014 +0530

----------------------------------------------------------------------
 .../apache/phoenix/end2end/AlterTableIT.java    |  11 +-
 .../phoenix/end2end/BaseConnectedQueryIT.java   |   5 +
 .../end2end/BaseTenantSpecificViewIndexIT.java  |  92 ++++--
 .../org/apache/phoenix/end2end/BaseViewIT.java  |  46 ++-
 .../org/apache/phoenix/end2end/DeleteIT.java    |  78 ++++-
 .../org/apache/phoenix/end2end/HashJoinIT.java  | 210 ++++++++++++
 .../org/apache/phoenix/end2end/QueryIT.java     |  56 ++--
 .../apache/phoenix/end2end/SaltedViewIT.java    |   7 +-
 .../end2end/TenantSpecificViewIndexIT.java      |  31 +-
 .../TenantSpecificViewIndexSaltedIT.java        |  10 +
 .../java/org/apache/phoenix/end2end/ViewIT.java |   2 +-
 .../phoenix/end2end/index/ImmutableIndexIT.java |  71 +++-
 .../phoenix/end2end/index/LocalIndexIT.java     | 201 ++++++++++--
 .../phoenix/end2end/index/MutableIndexIT.java   | 326 ++++++++++++++++---
 .../apache/phoenix/compile/DeleteCompiler.java  |   3 +-
 .../phoenix/compile/ExpressionCompiler.java     |  28 +-
 .../apache/phoenix/compile/JoinCompiler.java    |   8 +-
 .../phoenix/compile/StatementContext.java       |  34 +-
 .../apache/phoenix/compile/WhereCompiler.java   |   6 +
 .../coprocessor/BaseScannerRegionObserver.java  |   3 +
 .../GroupedAggregateRegionObserver.java         |  37 ++-
 .../phoenix/coprocessor/ScanRegionObserver.java | 178 +++-------
 .../UngroupedAggregateRegionObserver.java       |  56 +++-
 .../apache/phoenix/execute/BasicQueryPlan.java  | 137 ++++++++
 .../expression/KeyValueColumnExpression.java    |   7 +
 .../expression/ProjectedColumnExpression.java   |  25 +-
 .../apache/phoenix/filter/SkipScanFilter.java   |   2 +-
 .../hbase/index/util/IndexManagementUtil.java   |   4 +-
 .../apache/phoenix/index/IndexMaintainer.java   | 317 ++++++++++++++++--
 .../apache/phoenix/index/PhoenixIndexCodec.java |   4 +-
 .../phoenix/iterate/ParallelIterators.java      |   3 +-
 ...SkipRangeParallelIteratorRegionSplitter.java |   4 +-
 .../org/apache/phoenix/join/ScanProjector.java  |  16 +-
 .../apache/phoenix/optimize/QueryOptimizer.java |  21 +-
 .../apache/phoenix/parse/ParseNodeRewriter.java |   7 +-
 .../query/ConnectionQueryServicesImpl.java      |   4 +-
 .../org/apache/phoenix/schema/ColumnRef.java    |  13 +-
 .../phoenix/schema/LocalIndexDataColumnRef.java |  49 +++
 .../apache/phoenix/schema/MetaDataClient.java   |  43 ++-
 .../org/apache/phoenix/schema/SaltingUtil.java  |  20 +-
 .../org/apache/phoenix/schema/ValueSchema.java  |  20 +-
 .../java/org/apache/phoenix/util/IndexUtil.java | 268 ++++++++++++++-
 .../org/apache/phoenix/util/MetaDataUtil.java   |   7 +
 .../java/org/apache/phoenix/util/ScanUtil.java  |  18 +-
 .../phoenix/index/IndexMaintainerTest.java      |   8 +-
 45 files changed, 2082 insertions(+), 414 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
index 70b3d9a..e673af0 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AlterTableIT.java
@@ -54,9 +54,10 @@ public class AlterTableIT extends BaseHBaseManagedTimeIT {
     public static final String SCHEMA_NAME = "";
     public static final String DATA_TABLE_NAME = "T";
     public static final String INDEX_TABLE_NAME = "I";
+    public static final String LOCAL_INDEX_TABLE_NAME = "LI";
     public static final String DATA_TABLE_FULL_NAME = SchemaUtil.getTableName(SCHEMA_NAME, "T");
     public static final String INDEX_TABLE_FULL_NAME = SchemaUtil.getTableName(SCHEMA_NAME, "I");
-
+    public static final String LOCAL_INDEX_TABLE_FULL_NAME = SchemaUtil.getTableName(SCHEMA_NAME, "LI");
 
     @Test
     public void testAlterTableWithVarBinaryKey() throws Exception {
@@ -208,6 +209,9 @@ public class AlterTableIT extends BaseHBaseManagedTimeIT {
     
         conn.createStatement().execute(
           "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1, v2)");
+        conn.createStatement().execute(
+            "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1, v2)");
+
         query = "SELECT * FROM " + INDEX_TABLE_FULL_NAME;
         rs = conn.createStatement().executeQuery(query);
         assertFalse(rs.next());
@@ -266,9 +270,14 @@ public class AlterTableIT extends BaseHBaseManagedTimeIT {
     
         conn.createStatement().execute(
           "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
+        conn.createStatement().execute(
+            "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
         query = "SELECT * FROM " + INDEX_TABLE_FULL_NAME;
         rs = conn.createStatement().executeQuery(query);
         assertFalse(rs.next());
+        query = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME;
+        rs = conn.createStatement().executeQuery(query);
+        assertFalse(rs.next());
     
         // load some data into the table
         stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?,?)");

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseConnectedQueryIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseConnectedQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseConnectedQueryIT.java
index e6bb65f..7aa7316 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseConnectedQueryIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseConnectedQueryIT.java
@@ -140,6 +140,11 @@ public abstract class BaseConnectedQueryIT extends BaseTest {
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             deletePriorTables(ts, conn);
+            if (ts != HConstants.LATEST_TIMESTAMP) {
+                conn.close();
+                props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+                conn = DriverManager.getConnection(getUrl(), props);
+            }
             deletePriorSequences(ts, conn);
         }
         finally {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseTenantSpecificViewIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseTenantSpecificViewIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseTenantSpecificViewIndexIT.java
index 3b5eb1f..1eb8c38 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseTenantSpecificViewIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseTenantSpecificViewIndexIT.java
@@ -26,6 +26,8 @@ import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.List;
 import java.util.Properties;
 import java.util.Set;
 
@@ -33,6 +35,8 @@ import org.apache.hadoop.hbase.util.Pair;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.QueryUtil;
 
+import com.google.common.collect.Lists;
+
 public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
     
     public static final String TENANT1_ID = "tenant1";
@@ -41,11 +45,15 @@ public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
     protected Set<Pair<String, String>> tenantViewsToDelete = newHashSet();
     
     protected void testUpdatableView(Integer saltBuckets) throws Exception {
+        testUpdatableView(saltBuckets, false);
+    }
+    
+    protected void testUpdatableView(Integer saltBuckets, boolean localIndex) throws Exception {
         createBaseTable("t", saltBuckets);
         Connection conn = createTenantConnection(TENANT1_ID);
         try {
             createAndPopulateTenantView(conn, TENANT1_ID, "t", "");
-            createAndVerifyIndex(conn, saltBuckets, TENANT1_ID, "");
+            createAndVerifyIndex(conn, saltBuckets, TENANT1_ID, "", localIndex);
             verifyViewData(conn, "");
         } finally {
             try { conn.close();} catch (Exception ignored) {}
@@ -53,6 +61,10 @@ public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
     }
     
     protected void testUpdatableViewsWithSameNameDifferentTenants(Integer saltBuckets) throws Exception {
+        testUpdatableViewsWithSameNameDifferentTenants(saltBuckets, false);
+    }
+
+    protected void testUpdatableViewsWithSameNameDifferentTenants(Integer saltBuckets, boolean localIndex) throws Exception {
         createBaseTable("t", saltBuckets);
         Connection conn1 = createTenantConnection(TENANT1_ID);
         Connection conn2 = createTenantConnection(TENANT2_ID);
@@ -64,8 +76,8 @@ public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
             createAndPopulateTenantView(conn1, TENANT1_ID, "t", prefixForTenant1Data);
             createAndPopulateTenantView(conn2, TENANT2_ID, "t", prefixForTenant2Data);
             
-            createAndVerifyIndex(conn1, saltBuckets, TENANT1_ID, prefixForTenant1Data);
-            createAndVerifyIndex(conn2, saltBuckets, TENANT2_ID, prefixForTenant2Data);
+            createAndVerifyIndex(conn1, saltBuckets, TENANT1_ID, prefixForTenant1Data, localIndex);
+            createAndVerifyIndex(conn2, saltBuckets, TENANT2_ID, prefixForTenant2Data, localIndex);
             
             verifyViewData(conn1, prefixForTenant1Data);
             verifyViewData(conn2, prefixForTenant2Data);
@@ -97,15 +109,25 @@ public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
         conn.commit();
     }
     
-    private void createAndVerifyIndex(Connection conn, Integer saltBuckets, String tenantId, String valuePrefix) throws SQLException {
-        conn.createStatement().execute("CREATE INDEX i ON v(v2)");
+    private void createAndVerifyIndex(Connection conn, Integer saltBuckets, String tenantId, String valuePrefix, boolean localIndex) throws SQLException {
+        if(localIndex){
+            conn.createStatement().execute("CREATE LOCAL INDEX i ON v(v2)");
+        } else {
+            conn.createStatement().execute("CREATE INDEX i ON v(v2)");
+        }
         conn.createStatement().execute("UPSERT INTO v(k2,v1,v2) VALUES (-1, 'blah', 'superblah')"); // sanity check that we can upsert after index is there
         conn.commit();
         ResultSet rs = conn.createStatement().executeQuery("EXPLAIN SELECT k1, k2, v2 FROM v WHERE v2='" + valuePrefix + "v2-1'");
-        assertEquals(saltBuckets == null ? 
-                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['" + tenantId + "',-32768,'" + valuePrefix + "v2-1']" :
-                "CLIENT PARALLEL 4-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0,'" + tenantId + "',-32768,'" + valuePrefix + "v2-1'] - [2,'" + tenantId + "',-32768,'" + valuePrefix + "v2-1']\n" + 
-                "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+        if(localIndex){
+            assertEquals(saltBuckets == null ? 
+                    "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_T ['" + tenantId + "',-32768,'" + valuePrefix + "v2-1']" :
+                        "CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T ['" + tenantId + "',-32768,'" + valuePrefix + "v2-1']", QueryUtil.getExplainPlan(rs));
+        } else {
+            assertEquals(saltBuckets == null ? 
+                    "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['" + tenantId + "',-32768,'" + valuePrefix + "v2-1']" :
+                        "CLIENT PARALLEL 4-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0,'" + tenantId + "',-32768,'" + valuePrefix + "v2-1'] - [2,'" + tenantId + "',-32768,'" + valuePrefix + "v2-1']\n" + 
+                        "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
+        }
     }
     
     private Connection createTenantConnection(String tenantId) throws SQLException {
@@ -117,26 +139,38 @@ public class BaseTenantSpecificViewIndexIT extends BaseHBaseManagedTimeIT {
     private void verifyViewData(Connection conn, String valuePrefix) throws SQLException {
         String query = "SELECT k1, k2, v2 FROM v WHERE v2='" + valuePrefix + "v2-1'";
         ResultSet rs = conn.createStatement().executeQuery(query);
-        assertTrue(rs.next());
-        assertEquals(1, rs.getInt(1));
-        assertEquals(1, rs.getInt(2));
-        assertEquals(valuePrefix + "v2-1", rs.getString(3));
-        assertTrue(rs.next());
-        assertEquals(1, rs.getInt(1));
-        assertEquals(3, rs.getInt(2));
-        assertEquals(valuePrefix + "v2-1", rs.getString(3));
-        assertTrue(rs.next());
-        assertEquals(1, rs.getInt(1));
-        assertEquals(5, rs.getInt(2));
-        assertEquals(valuePrefix + "v2-1", rs.getString(3));
-        assertTrue(rs.next());
-        assertEquals(1, rs.getInt(1));
-        assertEquals(7, rs.getInt(2));
-        assertEquals(valuePrefix + "v2-1", rs.getString(3));
-        assertTrue(rs.next());
-        assertEquals(1, rs.getInt(1));
-        assertEquals(9, rs.getInt(2));
-        assertEquals(valuePrefix + "v2-1", rs.getString(3));
+        List<List<Object>> expectedResultsA = Lists.newArrayList(
+            Arrays.<Object>asList(1,1, valuePrefix + "v2-1"),
+            Arrays.<Object>asList(1,3, valuePrefix + "v2-1"),
+            Arrays.<Object>asList(1,5, valuePrefix + "v2-1"),
+            Arrays.<Object>asList(1,7, valuePrefix + "v2-1"),
+            Arrays.<Object>asList(1,9, valuePrefix + "v2-1"));
+        assertValuesEqualsResultSet(rs,expectedResultsA);
         assertFalse(rs.next());
     }
+
+    /**
+     * Asserts that we find the expected values in the result set. We don't know the order, since we don't always
+     * have an order by and we're going through indexes, but we assert that each expected result occurs once as
+     * expected (in any order).
+     */
+    private void assertValuesEqualsResultSet(ResultSet rs, List<List<Object>> expectedResults) throws SQLException {
+        int expectedCount = expectedResults.size();
+        int count = 0;
+        List<List<Object>> actualResults = Lists.newArrayList();
+        List<Object> errorResult = null;
+        while (rs.next() && errorResult == null) {
+            List<Object> result = Lists.newArrayList();
+            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
+                result.add(rs.getObject(i+1));
+            }
+            if (!expectedResults.contains(result)) {
+                errorResult = result;
+            }
+            actualResults.add(result);
+            count++;
+        }
+        assertTrue("Could not find " + errorResult + " in expected results: " + expectedResults + " with actual results: " + actualResults, errorResult == null);
+        assertEquals(count, expectedCount);
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
index 03e8a43..4ff96f6 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseViewIT.java
@@ -45,9 +45,9 @@ public class BaseViewIT extends BaseHBaseManagedTimeIT {
         startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));
     }
 
-    protected void testUpdatableViewWithIndex(Integer saltBuckets) throws Exception {
+    protected void testUpdatableViewWithIndex(Integer saltBuckets, boolean localIndex) throws Exception {
         testUpdatableView(saltBuckets);
-        testUpdatableViewIndex(saltBuckets);
+        testUpdatableViewIndex(saltBuckets, localIndex);
     }
 
     protected void testUpdatableView(Integer saltBuckets) throws Exception {
@@ -97,9 +97,18 @@ public class BaseViewIT extends BaseHBaseManagedTimeIT {
     }
 
     protected void testUpdatableViewIndex(Integer saltBuckets) throws Exception {
+        testUpdatableViewIndex(saltBuckets, false);
+    }
+
+    protected void testUpdatableViewIndex(Integer saltBuckets, boolean localIndex) throws Exception {
         ResultSet rs;
         Connection conn = DriverManager.getConnection(getUrl());
-        conn.createStatement().execute("CREATE INDEX i1 on v(k3) include (s)");
+        if (localIndex) {
+            conn.createStatement().execute("CREATE LOCAL INDEX i1 on v(k3)");
+        } else {
+            conn.createStatement().execute("CREATE INDEX i1 on v(k3) include (s)");
+        }
+        conn.createStatement().execute("UPSERT INTO v(k2,S,k3) VALUES(120,'foo',50.0)");
         String query = "SELECT k1, k2, k3, s FROM v WHERE k3 = 51.0";
         rs = conn.createStatement().executeQuery(query);
         assertTrue(rs.next());
@@ -109,12 +118,21 @@ public class BaseViewIT extends BaseHBaseManagedTimeIT {
         assertEquals("bar", rs.getString(4));
         assertFalse(rs.next());
         rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(saltBuckets == null
-                ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + Short.MIN_VALUE + ",51]"
-                : "CLIENT PARALLEL " + saltBuckets + "-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0," + Short.MIN_VALUE + ",51] - [2," + Short.MIN_VALUE + ",51]\nCLIENT MERGE SORT",
-            QueryUtil.getExplainPlan(rs));
+        if (localIndex) {
+            assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T [-32768,51]",
+                QueryUtil.getExplainPlan(rs));
+        } else {
+            assertEquals(saltBuckets == null
+                    ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + Short.MIN_VALUE + ",51]"
+                            : "CLIENT PARALLEL " + saltBuckets + "-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0," + Short.MIN_VALUE + ",51] - [2," + Short.MIN_VALUE + ",51]\nCLIENT MERGE SORT",
+                            QueryUtil.getExplainPlan(rs));
+        }
 
-        conn.createStatement().execute("CREATE INDEX i2 on v(s)");
+        if (localIndex) {
+            conn.createStatement().execute("CREATE LOCAL INDEX i2 on v(s)");
+        } else {
+            conn.createStatement().execute("CREATE INDEX i2 on v(s)");
+        }
         query = "SELECT k1, k2, s FROM v WHERE s = 'foo'";
         rs = conn.createStatement().executeQuery(query);
         assertTrue(rs.next());
@@ -123,10 +141,14 @@ public class BaseViewIT extends BaseHBaseManagedTimeIT {
         assertEquals("foo", rs.getString(3));
         assertFalse(rs.next());
         rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals(saltBuckets == null
-                ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + (Short.MIN_VALUE+1) + ",'foo']"
-                : "CLIENT PARALLEL " + saltBuckets + "-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0," + (Short.MIN_VALUE+1) + ",'foo'] - [2," + (Short.MIN_VALUE+1) + ",'foo']\nCLIENT MERGE SORT",
-            QueryUtil.getExplainPlan(rs));
+        if (localIndex) {
+            assertEquals("CLIENT PARALLEL 3-WAY RANGE SCAN OVER _LOCAL_IDX_T [" + (Short.MIN_VALUE+1) + ",'foo']",QueryUtil.getExplainPlan(rs));
+        } else {
+            assertEquals(saltBuckets == null
+                    ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T [" + (Short.MIN_VALUE+1) + ",'foo']"
+                            : "CLIENT PARALLEL " + saltBuckets + "-WAY SKIP SCAN ON 3 KEYS OVER _IDX_T [0," + (Short.MIN_VALUE+1) + ",'foo'] - [2," + (Short.MIN_VALUE+1) + ",'foo']\nCLIENT MERGE SORT",
+                            QueryUtil.getExplainPlan(rs));
+        }
     }
 
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
index e606cf1..74b21dd 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DeleteIT.java
@@ -32,6 +32,7 @@ import java.util.Arrays;
 import java.util.Collections;
 import java.util.List;
 
+import org.apache.phoenix.util.MetaDataUtil;
 import org.apache.phoenix.util.QueryUtil;
 import org.junit.Test;
 
@@ -101,14 +102,23 @@ public class DeleteIT extends BaseHBaseManagedTimeIT {
             String explainPlan = QueryUtil.getExplainPlan(rs);
             assertEquals(expectedToBeUsed, explainPlan.contains(" SCAN OVER " + indexName));
    }
-    
+
     private void testDeleteRange(boolean autoCommit, boolean createIndex) throws Exception {
+        testDeleteRange(autoCommit, createIndex, false);
+    }
+
+    private void testDeleteRange(boolean autoCommit, boolean createIndex, boolean local) throws Exception {
         Connection conn = DriverManager.getConnection(getUrl());
         initTableValues(conn);
         
         String indexName = "IDX";
         if (createIndex) {
-            conn.createStatement().execute("CREATE INDEX IF NOT EXISTS idx ON IntIntKeyTest(j)");
+            if (local) {
+                conn.createStatement().execute("CREATE LOCAL INDEX IF NOT EXISTS local_idx ON IntIntKeyTest(j)");
+                indexName = MetaDataUtil.getLocalIndexTableName("INTINTKEYTEST");
+            } else {
+                conn.createStatement().execute("CREATE INDEX IF NOT EXISTS idx ON IntIntKeyTest(j)");
+            }
         }
         
         ResultSet rs;
@@ -171,17 +181,32 @@ public class DeleteIT extends BaseHBaseManagedTimeIT {
     
     @Test
     public void testDeleteRangeNoAutoCommitWithIndex() throws Exception {
-        testDeleteRange(false, true);
+        testDeleteRange(false, true, false);
+    }
+
+    @Test
+    public void testDeleteRangeNoAutoCommitWithLocalIndexIndex() throws Exception {
+        testDeleteRange(false, true, true);
     }
     
     @Test
     public void testDeleteRangeAutoCommitWithIndex() throws Exception {
-        testDeleteRange(true, true);
+        testDeleteRange(true, true, false);
     }
     
     @Test
+    public void testDeleteRangeAutoCommitWithLocalIndex() throws Exception {
+        testDeleteRange(true, true, true);
+    }
+
+    @Test
     public void testDeleteAllFromTableWithIndexAutoCommitSalting() throws SQLException {
-        testDeleteAllFromTableWithIndex(true, true);
+        testDeleteAllFromTableWithIndex(true, true, false);
+    }
+
+    @Test
+    public void testDeleteAllFromTableWithLocalIndexAutoCommitSalting() throws SQLException {
+        testDeleteAllFromTableWithIndex(true, true, true);
     }
     
     @Test
@@ -196,26 +221,40 @@ public class DeleteIT extends BaseHBaseManagedTimeIT {
     
     @Test
     public void testDeleteAllFromTableWithIndexNoAutoCommitSalted() throws SQLException {
-        testDeleteAllFromTableWithIndex(false, true);
+        testDeleteAllFromTableWithIndex(false, true, false);
     }
     
+    @Test
+    public void testDeleteAllFromTableWithLocalIndexNoAutoCommitSalted() throws SQLException {
+        testDeleteAllFromTableWithIndex(false, true, true);
+    }
+
     private void testDeleteAllFromTableWithIndex(boolean autoCommit, boolean isSalted) throws SQLException {
+        testDeleteAllFromTableWithIndex(autoCommit, isSalted, false);
+    }
+
+    private void testDeleteAllFromTableWithIndex(boolean autoCommit, boolean isSalted, boolean localIndex) throws SQLException {
         Connection con = null;
         try {
             con = DriverManager.getConnection(getUrl());
             con.setAutoCommit(autoCommit);
 
             Statement stm = con.createStatement();
-            stm.execute("CREATE TABLE IF NOT EXISTS web_stats (" +
-            		"HOST CHAR(2) NOT NULL," +
-            		"DOMAIN VARCHAR NOT NULL, " +
-            		"FEATURE VARCHAR NOT NULL, " +
-            		"DATE DATE NOT NULL, \n" + 
-            		"USAGE.CORE BIGINT," +
-            		"USAGE.DB BIGINT," +
-            		"STATS.ACTIVE_VISITOR INTEGER " +
-            		"CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE))" + (isSalted ? " SALT_BUCKETS=3" : ""));
-            stm.execute("CREATE INDEX web_stats_idx ON web_stats (CORE,DB,ACTIVE_VISITOR)");
+            String s = "CREATE TABLE IF NOT EXISTS web_stats (" +
+                    "HOST CHAR(2) NOT NULL," +
+                    "DOMAIN VARCHAR NOT NULL, " +
+                    "FEATURE VARCHAR NOT NULL, " +
+                    "DATE DATE NOT NULL, \n" + 
+                    "USAGE.CORE BIGINT," +
+                    "USAGE.DB BIGINT," +
+                    "STATS.ACTIVE_VISITOR INTEGER " +
+                    "CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE))" + (isSalted ? " SALT_BUCKETS=3" : "");
+            stm.execute(s);
+            if (localIndex) {
+                stm.execute("CREATE LOCAL INDEX local_web_stats_idx ON web_stats (CORE,DB,ACTIVE_VISITOR)");
+            } else {
+                stm.execute("CREATE INDEX web_stats_idx ON web_stats (CORE,DB,ACTIVE_VISITOR)");
+            }
             stm.close();
 
             PreparedStatement psInsert = con
@@ -241,8 +280,11 @@ public class DeleteIT extends BaseHBaseManagedTimeIT {
             ResultSet rs = con.createStatement().executeQuery("SELECT /*+ NO_INDEX */ count(*) FROM web_stats");
             assertTrue(rs.next());
             assertEquals(0, rs.getLong(1));
-
-            rs = con.createStatement().executeQuery("SELECT count(*) FROM web_stats_idx");
+            if(localIndex){
+                rs = con.createStatement().executeQuery("SELECT count(*) FROM local_web_stats_idx");
+            } else {
+                rs = con.createStatement().executeQuery("SELECT count(*) FROM web_stats_idx");
+            }
             assertTrue(rs.next());
             assertEquals(0, rs.getLong(1));
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
index 5f02381..3281de9 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
@@ -51,6 +51,7 @@ import java.util.Properties;
 import org.apache.phoenix.exception.SQLExceptionCode;
 import org.apache.phoenix.query.QueryServices;
 import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.MetaDataUtil;
 import org.apache.phoenix.util.QueryUtil;
 import org.apache.phoenix.util.ReadOnlyProps;
 import org.junit.Before;
@@ -516,6 +517,215 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
                 "                    BUILD HASH TABLE 0\n" +
                 "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
                 }});
+        testCases.add(new String[][] {
+                {
+                "CREATE LOCAL INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+                "CREATE LOCAL INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+                "CREATE LOCAL INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+                }, {
+                /* 
+                 * testLeftJoinWithAggregation()
+                 *     SELECT i.name, sum(quantity) FROM joinOrderTable o 
+                 *     LEFT JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     GROUP BY i.name ORDER BY i.name
+                 */     
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY [I.0:NAME]\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +"\n" +
+                "            SERVER FILTER BY FIRST KEY ONLY",
+                /* 
+                 * testLeftJoinWithAggregation()
+                 *     SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o 
+                 *     LEFT JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     GROUP BY i.item_id ORDER BY q DESC"
+                 */     
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.:item_id]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +"\n" +
+                "            SERVER FILTER BY FIRST KEY ONLY",
+                /* 
+                 * testLeftJoinWithAggregation()
+                 *     SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i 
+                 *     LEFT JOIN joinOrderTable o ON o.item_id = i.item_id 
+                 *     GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+                 */     
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+                /* 
+                 * testRightJoinWithAggregation()
+                 *     SELECT i.name, sum(quantity) FROM joinOrderTable o 
+                 *     RIGHT JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     GROUP BY i.name ORDER BY i.name
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME+"\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY [I.0:NAME]\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+                /*
+                 * testRightJoinWithAggregation()
+                 *     SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o 
+                 *     RIGHT JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+                /*
+                 * testJoinWithWildcard()
+                 *     SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp 
+                 *     ON joinItemTable.supplier_id = supp.supplier_id 
+                 *     ORDER BY item_id
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+                /*
+                 * testJoinPlanWithIndex()
+                 *     SELECT item.item_id, item.name, supp.supplier_id, supp.name 
+                 *     FROM joinItemTable item LEFT JOIN joinSupplierTable supp 
+                 *     ON substr(item.name, 2, 1) = substr(supp.name, 2, 1) 
+                 *         AND (supp.name BETWEEN 'S1' AND 'S5') 
+                 *     WHERE item.name BETWEEN 'T1' AND 'T5'
+                 */
+                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']",
+                /*
+                 * testJoinPlanWithIndex()
+                 *     SELECT item.item_id, item.name, supp.supplier_id, supp.name 
+                 *     FROM joinItemTable item INNER JOIN joinSupplierTable supp 
+                 *     ON item.supplier_id = supp.supplier_id 
+                 *     WHERE (item.name = 'T1' OR item.name = 'T5') 
+                 *         AND (supp.name = 'S1' OR supp.name = 'S5')
+                 */
+                "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']",
+                /*
+                 * testJoinWithSkipMergeOptimization()
+                 *     SELECT s.name FROM joinItemTable i 
+                 *     JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000 
+                 *     JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER FILTER BY QUANTITY < 5000\n" +
+                "    BUILD HASH TABLE 1\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+                /*
+                 * testSelfJoin()
+                 *     SELECT i2.item_id, i1.name FROM joinItemTable i1 
+                 *     JOIN joinItemTable i2 ON i1.item_id = i2.item_id 
+                 *     ORDER BY i1.item_id
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +"\n"  +
+                "            SERVER FILTER BY FIRST KEY ONLY",
+                /*
+                 * testSelfJoin()
+                 *     SELECT i1.name, i2.name FROM joinItemTable i1 
+                 *     JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id 
+                 *     ORDER BY i1.name, i2.name
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +"\n"  +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER SORTED BY [I1.0:NAME, I2.0:NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME,
+                /*
+                 * testStarJoin()
+                 *     SELECT order_id, c.name, i.name iname, quantity, o.date 
+                 *     FROM joinOrderTable o 
+                 *     JOIN joinCustomerTable c ON o.customer_id = c.customer_id 
+                 *     JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     ORDER BY order_id
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "    PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
+                "    BUILD HASH TABLE 1\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER FILTER BY FIRST KEY ONLY",
+                /*
+                 * testStarJoin()
+                 *     SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date 
+                 *     FROM joinOrderTable o 
+                 *     JOIN joinCustomerTable c ON o.customer_id = c.customer_id 
+                 *     JOIN joinItemTable i ON o.item_id = i.item_id 
+                 *     ORDER BY order_id
+                 */
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER SORTED BY [O.order_id]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "            BUILD HASH TABLE 0\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME,
+                /*
+                 * testSubJoin()
+                 *     SELECT * FROM joinCustomerTable c 
+                 *     INNER JOIN (joinOrderTable o 
+                 *         INNER JOIN (joinSupplierTable s 
+                 *             RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id)
+                 *         ON o.item_id = i.item_id)
+                 *     ON c.customer_id = o.customer_id
+                 *     WHERE c.customer_id <= '0000000005' 
+                 *         AND order_id != '000000000000003' 
+                 *         AND i.name != 'T3' 
+                 *     ORDER BY c.customer_id, i.name
+                 */
+                "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+                "    SERVER SORTED BY [C.customer_id, I.0:NAME]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "    BUILD HASH TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+                "            SERVER FILTER BY order_id != '000000000000003'\n" +
+                "            PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "            BUILD HASH TABLE 0\n" +
+                "                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +"\n" +
+                "                    SERVER FILTER BY NAME != 'T3'\n" +
+                "                    PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+                "                    BUILD HASH TABLE 0\n" +
+                "                        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+                }});
         return testCases;
     }
     

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java
index fc1c4d5..9e3c93e 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java
@@ -53,12 +53,14 @@ import java.sql.Timestamp;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.HashSet;
+import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 import java.util.Set;
 import java.util.concurrent.atomic.AtomicInteger;
 
+import org.apache.hadoop.hbase.TableName;
 import org.apache.hadoop.hbase.client.HBaseAdmin;
 import org.apache.hadoop.hbase.client.HTable;
 import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
@@ -71,6 +73,7 @@ import org.apache.phoenix.schema.ConstraintViolationException;
 import org.apache.phoenix.schema.PDataType;
 import org.apache.phoenix.schema.SequenceNotFoundException;
 import org.apache.phoenix.util.ByteUtil;
+import org.apache.phoenix.util.MetaDataUtil;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.ReadOnlyProps;
 import org.junit.Before;
@@ -146,6 +149,8 @@ public class QueryIT extends BaseClientManagedTimeIT {
                 + "    B_STRING, " + "    A_DATE)" });
         testCases.add(new String[] { "CREATE INDEX " + ATABLE_INDEX_NAME + " ON aTable (a_integer) INCLUDE ("
                 + "    A_STRING, " + "    B_STRING, " + "    A_DATE)" });
+        testCases.add(new String[] { "CREATE LOCAL INDEX " + ATABLE_INDEX_NAME + " ON aTable (a_integer, a_string) INCLUDE ("
+                + "    B_STRING, " + "    A_DATE)" });
         testCases.add(new String[] { "" });
         return testCases;
     }
@@ -193,21 +198,16 @@ public class QueryIT extends BaseClientManagedTimeIT {
             results.add(result);
         }
         for (int j = 0; j < expectedResultsArray.length; j++) {
-                List<List<Object>> expectedResults = expectedResultsArray[j];
-                Set<List<Object>> expectedResultsSet = Sets.newHashSet(expectedResults);
-                int count = 0;
-                boolean brokeEarly = false;
-                for (List<Object> result : results) {
-                    if (!expectedResultsSet.contains(result)) {
-                        brokeEarly = true;
-                        break;
-                    }
-                    count++;
-                }
-                if (!brokeEarly && count == expectedResults.size()) {
-                    return;
+            List<List<Object>> expectedResults = expectedResultsArray[j];
+            Set<List<Object>> expectedResultsSet = Sets.newHashSet(expectedResults);
+            Iterator<List<Object>> iterator = results.iterator();
+            while (iterator.hasNext()) {
+                if (expectedResultsSet.contains(iterator.next())) {
+                    iterator.remove();
                 }
+            }
         }
+        if (results.isEmpty()) return;
         fail("Unable to find " + results + " in " + Arrays.asList(expectedResultsArray));
     }
     
@@ -2741,8 +2741,8 @@ public class QueryIT extends BaseClientManagedTimeIT {
         String[] answers = new String[]{"00D300000000XHP5bar","a5bar","15bar","5bar","5bar"};
         String[] queries = new String[] { 
         		"SELECT  organization_id || 5 || 'bar' FROM atable limit 1",
-        		"SELECT a_string || 5 || 'bar' FROM atable limit 1",
-        		"SELECT a_integer||5||'bar' FROM atable limit 1",
+        		"SELECT a_string || 5 || 'bar' FROM atable  order by a_string limit 1",
+        		"SELECT a_integer||5||'bar' FROM atable order by a_integer limit 1",
         		"SELECT x_decimal||5||'bar' FROM atable limit 1",
         		"SELECT x_long||5||'bar' FROM atable limit 1"
         };
@@ -2882,20 +2882,22 @@ public class QueryIT extends BaseClientManagedTimeIT {
             assertEquals(E_VALUE, rs.getString(2));
            assertEquals(1, rs.getLong(3));
             assertFalse(rs.next());
-            
-            byte[] tableName = Bytes.toBytes(ATABLE_NAME);
             admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin();
-            HTable htable = (HTable)conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(tableName);
-            htable.clearRegionCache();
-            int nRegions = htable.getRegionLocations().size();
-            admin.split(tableName, ByteUtil.concat(Bytes.toBytes(tenantId), Bytes.toBytes("00A" + Character.valueOf((char)('3' + nextRunCount()))+ ts))); // vary split point with test run
-            int retryCount = 0;
-            do {
-                Thread.sleep(2000);
-                retryCount++;
+            if (admin.tableExists(TableName.valueOf(MetaDataUtil.getLocalIndexTableName("atable")))) {
+                byte[] tableName = Bytes.toBytes(ATABLE_NAME);
+                HTable htable = (HTable)conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(tableName);
+                htable.clearRegionCache();
+                int nRegions = htable.getRegionLocations().size();
+                
+                admin.split(tableName, ByteUtil.concat(Bytes.toBytes(tenantId), Bytes.toBytes("00A" + Character.valueOf((char)('3' + nextRunCount()))+ ts))); // vary split point with test run
+                int retryCount = 0;
+                do {
+                    Thread.sleep(2000);
+                    retryCount++;
                 //htable.clearRegionCache();
-            } while (retryCount < 10 && htable.getRegionLocations().size() == nRegions);
-            assertNotEquals(nRegions,htable.getRegionLocations().size());
+                } while (retryCount < 10 && htable.getRegionLocations().size() == nRegions);
+                assertNotEquals(nRegions,htable.getRegionLocations().size());
+            }
             
             statement.setString(1, tenantId);
             rs = statement.executeQuery();

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
index a6fc562..df7b572 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
@@ -30,6 +30,11 @@ public class SaltedViewIT extends BaseViewIT {
      */
     @Test
     public void testSaltedUpdatableViewWithIndex() throws Exception {
-        testUpdatableViewWithIndex(3);
+        testUpdatableViewWithIndex(3, false);
+    }
+
+    @Test
+    public void testSaltedUpdatableViewWithLocalIndex() throws Exception {
+        testUpdatableViewWithIndex(3, true);
     }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
index 8ade47d..3fd7754 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java
@@ -40,12 +40,31 @@ public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
     }
 
     @Test
+    public void testUpdatableViewLocalIndex() throws Exception {
+        testUpdatableView(null, true);
+    }
+
+    @Test
     public void testUpdatableViewsWithSameNameDifferentTenants() throws Exception {
         testUpdatableViewsWithSameNameDifferentTenants(null);
     }
 
     @Test
+    public void testUpdatableViewsWithSameNameDifferentTenantsWithLocalIndex() throws Exception {
+        testUpdatableViewsWithSameNameDifferentTenants(null, true);
+    }
+
+    @Test
     public void testMultiCFViewIndex() throws Exception {
+        testMultiCFViewIndex(false);
+    }
+
+    @Test
+    public void testMultiCFViewLocalIndex() throws Exception {
+        testMultiCFViewIndex(true);
+    }
+    
+    private void testMultiCFViewIndex(boolean localIndex) throws Exception {
         Connection conn = DriverManager.getConnection(getUrl());
         String ddl = "CREATE TABLE MT_BASE (PK1 VARCHAR not null, PK2 VARCHAR not null, "
                 + "MYCF1.COL1 varchar,MYCF2.COL2 varchar "
@@ -74,7 +93,11 @@ public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
         assertFalse(rs.next());
         conn.createStatement().execute("UPSERT INTO acme VALUES ('e','f','g')");
         conn.commit();
-        conn.createStatement().execute("create index idx_acme on acme (COL1)");
+        if(localIndex){
+            conn.createStatement().execute("create local index idx_acme on acme (COL1)");
+        } else {
+            conn.createStatement().execute("create index idx_acme on acme (COL1)");
+        }
         rs = conn.createStatement().executeQuery("select * from acme");
         assertTrue(rs.next());
         assertEquals("b",rs.getString(1));
@@ -94,7 +117,11 @@ public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
         assertEquals("f",rs.getString(2));
         assertFalse(rs.next());
         rs = conn.createStatement().executeQuery("explain select pk2,col1 from acme where col1='f'");
-        assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_MT_BASE ['a',-32768,'f']",QueryUtil.getExplainPlan(rs));
+        if(localIndex){
+            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_MT_BASE ['a',-32768,'f']",QueryUtil.getExplainPlan(rs));
+        } else {
+            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_MT_BASE ['a',-32768,'f']",QueryUtil.getExplainPlan(rs));
+        }
         
         try {
             // Cannot reference tenant_id column in tenant specific connection

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexSaltedIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexSaltedIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexSaltedIT.java
index e72419a..db692a1 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexSaltedIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexSaltedIT.java
@@ -31,4 +31,14 @@ public class TenantSpecificViewIndexSaltedIT extends BaseTenantSpecificViewIndex
     public void testUpdatableViewsWithSameNameDifferentTenants() throws Exception {
         testUpdatableViewsWithSameNameDifferentTenants(SALT_BUCKETS);
     }
+    
+    @Test
+    public void testUpdatableSaltedViewWithLocalIndex() throws Exception {
+        testUpdatableView(SALT_BUCKETS, true);
+    }
+
+    @Test
+    public void testUpdatableViewsWithSameNameDifferentTenantsWithLocalIndex() throws Exception {
+        testUpdatableViewsWithSameNameDifferentTenants(SALT_BUCKETS, true);
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
index 2dedba1..4776775 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
@@ -93,7 +93,7 @@ public class ViewIT extends BaseViewIT {
 
     @Test
     public void testNonSaltedUpdatableViewWithIndex() throws Exception {
-        testUpdatableViewWithIndex(null);
+        testUpdatableViewWithIndex(null, false);
     }
     
     @Test

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java
index cd3a8fb..bfe0288 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ImmutableIndexIT.java
@@ -110,20 +110,40 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT {
     
     @Test
     public void testIndexWithNullableFixedWithCols() throws Exception {
+        testIndexWithNullableFixedWithCols(false);
+    }
+
+    @Test
+    public void testIndexWithNullableFixedWithColsWithLocalIndex() throws Exception {
+        testIndexWithNullableFixedWithCols(true);
+    }
+
+    private void testIndexWithNullableFixedWithCols(boolean localIndex) throws Exception {
         Properties props = new Properties(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(getUrl(), props);
         conn.setAutoCommit(false);
         ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
         populateTestTable();
-        String ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
-                + " (char_col1 ASC, int_col1 ASC)"
-                + " INCLUDE (long_col1, long_col2)";
+        String ddl = null;
+        if(localIndex){
+            ddl = "CREATE LOCAL INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (char_col1 ASC, int_col1 ASC)"
+                    + " INCLUDE (long_col1, long_col2)";
+        } else {
+            ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (char_col1 ASC, int_col1 ASC)"
+                    + " INCLUDE (long_col1, long_col2)";
+        }
         PreparedStatement stmt = conn.prepareStatement(ddl);
         stmt.execute();
         
         String query = "SELECT char_col1, int_col1 from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE;
         ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
-        assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX", QueryUtil.getExplainPlan(rs));
+        if (localIndex) {
+            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER _LOCAL_IDX_INDEX_TEST.INDEX_DATA_TABLE", QueryUtil.getExplainPlan(rs));
+        } else {
+            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX", QueryUtil.getExplainPlan(rs));
+        }
         
         rs = conn.createStatement().executeQuery(query);
         assertTrue(rs.next());
@@ -188,14 +208,31 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT {
     
     @Test
     public void testDeleteFromAllPKColumnIndex() throws Exception {
+        testDeleteFromAllPKColumnIndex(false);
+    }
+
+    @Test
+    public void testDeleteFromAllPKColumnLocalIndex() throws Exception {
+        testDeleteFromAllPKColumnIndex(true);
+    }
+
+    private void testDeleteFromAllPKColumnIndex(boolean localIndex) throws Exception {
         Properties props = new Properties(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(getUrl(), props);
         conn.setAutoCommit(false);
         ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
         populateTestTable();
-        String ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
-                + " (long_pk, varchar_pk)"
-                + " INCLUDE (long_col1, long_col2)";
+        String ddl = null;
+        if (localIndex) {
+            ddl = "CREATE LOCAL INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (long_pk, varchar_pk)"
+                    + " INCLUDE (long_col1, long_col2)";
+            
+        } else {
+            ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (long_pk, varchar_pk)"
+                    + " INCLUDE (long_col1, long_col2)";
+        }
         PreparedStatement stmt = conn.prepareStatement(ddl);
         stmt.execute();
         
@@ -240,16 +277,30 @@ public class ImmutableIndexIT extends BaseHBaseManagedTimeIT {
         conn.createStatement().execute("DROP INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE);
     }
     
-    
     @Test
     public void testDropIfImmutableKeyValueColumn() throws Exception {
+        testDropIfImmutableKeyValueColumn(false);
+    }
+    
+    @Test
+    public void testDropIfImmutableKeyValueColumnWithLocalIndex() throws Exception {
+        testDropIfImmutableKeyValueColumn(true);
+    }
+
+    private void testDropIfImmutableKeyValueColumn(boolean localIndex) throws Exception {
         Properties props = new Properties(TEST_PROPERTIES);
         Connection conn = DriverManager.getConnection(getUrl(), props);
         conn.setAutoCommit(false);
         ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
         populateTestTable();
-        String ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
-                + " (long_col1)";
+        String ddl = null;
+        if(localIndex) {
+            ddl = "CREATE LOCAL INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (long_col1)";
+        } else {
+            ddl = "CREATE INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
+                    + " (long_col1)";
+        }
         PreparedStatement stmt = conn.prepareStatement(ddl);
         stmt.execute();
         

http://git-wip-us.apache.org/repos/asf/phoenix/blob/6e1fcc8d/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
index c4e935d..2a19791 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/LocalIndexIT.java
@@ -37,9 +37,12 @@ import org.apache.hadoop.hbase.client.Result;
 import org.apache.hadoop.hbase.client.ResultScanner;
 import org.apache.hadoop.hbase.client.Scan;
 import org.apache.hadoop.hbase.util.Pair;
+import org.apache.phoenix.compile.QueryPlan;
+import org.apache.phoenix.coprocessor.BaseScannerRegionObserver;
 import org.apache.phoenix.hbase.index.IndexRegionSplitPolicy;
 import org.apache.phoenix.jdbc.PhoenixConnection;
 import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData;
+import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
 import org.apache.phoenix.query.QueryServices;
 import org.apache.phoenix.schema.PTable;
 import org.apache.phoenix.schema.PTable.IndexType;
@@ -70,6 +73,7 @@ public class LocalIndexIT extends BaseIndexIT {
         String ddl = "CREATE TABLE " + tableName + " (t_id VARCHAR NOT NULL,\n" +
                 "k1 INTEGER NOT NULL,\n" +
                 "k2 INTEGER NOT NULL,\n" +
+                "k3 INTEGER NOT NULL,\n" +
                 "v1 VARCHAR,\n" +
                 "CONSTRAINT pk PRIMARY KEY (t_id, k1, k2))\n"
                         + (saltBuckets != null && splits == null ? (",salt_buckets=" + saltBuckets) : "" 
@@ -84,8 +88,8 @@ public class LocalIndexIT extends BaseIndexIT {
         Connection conn1 = DriverManager.getConnection(getUrl());
         Connection conn2 = DriverManager.getConnection(getUrl());
         conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
-        conn2.createStatement().executeQuery("SELECT * FROM " + DATA_TABLE_FULL_NAME).next();
-        PTable localIndex = conn2.unwrap(PhoenixConnection.class).getMetaDataCache().getTable(new PTableKey(null,INDEX_TABLE_NAME));
+        conn1.createStatement().executeQuery("SELECT * FROM " + DATA_TABLE_FULL_NAME).next();
+        PTable localIndex = conn1.unwrap(PhoenixConnection.class).getMetaDataCache().getTable(new PTableKey(null,INDEX_TABLE_NAME));
         assertEquals(IndexType.LOCAL, localIndex.getIndexType());
         assertNotNull(localIndex.getViewIndexId());
     }
@@ -161,10 +165,10 @@ public class LocalIndexIT extends BaseIndexIT {
         createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
         Connection conn1 = DriverManager.getConnection(getUrl());
         conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,'z')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,'z')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,'a')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,'c')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,4,'z')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,3,'z')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,2,'a')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,1,'c')");
         conn1.commit();
         ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + INDEX_TABLE_NAME);
         assertTrue(rs.next());
@@ -193,10 +197,10 @@ public class LocalIndexIT extends BaseIndexIT {
     public void testBuildIndexWhenUserTableAlreadyHasData() throws Exception {
         createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
         Connection conn1 = DriverManager.getConnection(getUrl());
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,'z')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,'z')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,'a')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,'c')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,4,'z')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,3,'z')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,2,'a')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,1,'c')");
         conn1.commit();
         conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
         ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + INDEX_TABLE_NAME);
@@ -227,10 +231,10 @@ public class LocalIndexIT extends BaseIndexIT {
         createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
         Connection conn1 = DriverManager.getConnection(getUrl());
         try{
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,'z')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,'a')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,'a')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,'c')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,1,'c')");
             conn1.commit();
             conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
             
@@ -257,6 +261,7 @@ public class LocalIndexIT extends BaseIndexIT {
             assertEquals("j", rs.getString("t_id"));
             assertEquals(2, rs.getInt("k1"));
             assertEquals(4, rs.getInt("k2"));
+            assertFalse(rs.next());
             
             query = "SELECT t_id, k1, k2,V1 from " + DATA_TABLE_FULL_NAME + " order by V1,t_id";
             rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
@@ -291,17 +296,145 @@ public class LocalIndexIT extends BaseIndexIT {
         } finally {
             conn1.close();
         }
-        
+    }
+
+    @Test
+    public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
+        createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
+        Connection conn1 = DriverManager.getConnection(getUrl());
+        try{
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,1,'c')");
+            conn1.commit();
+            conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
+            
+            ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + INDEX_TABLE_NAME);
+            assertTrue(rs.next());
+            
+            HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin();
+            int numRegions = admin.getTableRegions(TableName.valueOf(DATA_TABLE_NAME)).size();
+            
+            String query = "SELECT t_id, k1, k2, k3, V1 FROM " + DATA_TABLE_NAME +" where v1='a'";
+            rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+            
+            assertEquals(
+                "CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
+                        + MetaDataUtil.getLocalIndexTableName(DATA_TABLE_NAME) + " [-32768,'a']",
+                        QueryUtil.getExplainPlan(rs));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("f", rs.getString("t_id"));
+            assertEquals(1, rs.getInt("k1"));
+            assertEquals(2, rs.getInt("k2"));
+            assertEquals(3, rs.getInt("k3"));
+            assertTrue(rs.next());
+            assertEquals("j", rs.getString("t_id"));
+            assertEquals(2, rs.getInt("k1"));
+            assertEquals(4, rs.getInt("k2"));
+            assertEquals(2, rs.getInt("k3"));
+            assertFalse(rs.next());
+            
+            query = "SELECT t_id, k1, k2, k3, V1 from " + DATA_TABLE_FULL_NAME + "  where v1<='z' order by V1,t_id";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertEquals(
+                "CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
+                        + MetaDataUtil.getLocalIndexTableName(DATA_TABLE_NAME)+" [-32768,*] - [-32768,'z']\n"
+                        + "    SERVER SORTED BY [V1, T_ID]\n" + "CLIENT MERGE SORT",
+                QueryUtil.getExplainPlan(rs));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("f", rs.getString("t_id"));
+            assertEquals(1, rs.getInt("k1"));
+            assertEquals(2, rs.getInt("k2"));
+            assertEquals(3, rs.getInt("k3"));
+            assertEquals("a", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("j", rs.getString("t_id"));
+            assertEquals(2, rs.getInt("k1"));
+            assertEquals(4, rs.getInt("k2"));
+            assertEquals(2, rs.getInt("k3"));
+            assertEquals("a", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("q", rs.getString("t_id"));
+            assertEquals(3, rs.getInt("k1"));
+            assertEquals(1, rs.getInt("k2"));
+            assertEquals(1, rs.getInt("k3"));
+            assertEquals("c", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("b", rs.getString("t_id"));
+            assertEquals(1, rs.getInt("k1"));
+            assertEquals(2, rs.getInt("k2"));
+            assertEquals(4, rs.getInt("k3"));
+            assertEquals("z", rs.getString("V1"));
+            
+            query = "SELECT t_id, V1, k3 from " + DATA_TABLE_FULL_NAME + "  where v1 <='z' group by v1,t_id, k3";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertEquals(
+                "CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
+                        + MetaDataUtil.getLocalIndexTableName(DATA_TABLE_NAME)+" [-32768,*] - [-32768,'z']\n"
+                        + "    SERVER AGGREGATE INTO DISTINCT ROWS BY [K3, V1, T_ID]\n" + "CLIENT MERGE SORT",
+                QueryUtil.getExplainPlan(rs));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("q", rs.getString("t_id"));
+            assertEquals(1, rs.getInt("k3"));
+            assertEquals("c", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("j", rs.getString("t_id"));
+            assertEquals(2, rs.getInt("k3"));
+            assertEquals("a", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("f", rs.getString("t_id"));
+            assertEquals(3, rs.getInt("k3"));
+            assertEquals("a", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("b", rs.getString("t_id"));
+            assertEquals(4, rs.getInt("k3"));
+            assertEquals("z", rs.getString("V1"));
+            
+            query = "SELECT v1,sum(k3) from " + DATA_TABLE_FULL_NAME + " where v1 <='z'  group by v1 order by v1";
+            PhoenixPreparedStatement statement = conn1.prepareStatement(query).unwrap(PhoenixPreparedStatement.class);
+            QueryPlan plan = statement.compileQuery("EXPLAIN " + query);
+            assertTrue(query, plan.getContext().getScan().getAttribute(BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS) == null);
+            assertTrue(query, plan.getContext().getScan().getAttribute(BaseScannerRegionObserver.UNORDERED_GROUP_BY_EXPRESSIONS) != null);
+            
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            assertEquals(
+                "CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER "
+                        + MetaDataUtil.getLocalIndexTableName(DATA_TABLE_NAME)+" [-32768,*] - [-32768,'z']\n"
+                        + "    SERVER AGGREGATE INTO DISTINCT ROWS BY [V1]\n" + "CLIENT MERGE SORT\n"+ "CLIENT SORTED BY [V1]",
+                QueryUtil.getExplainPlan(rs));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("a", rs.getString(1));
+            assertEquals(5, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("c", rs.getString(1));
+            assertEquals(1, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals("z", rs.getString(1));
+            assertEquals(4, rs.getInt(2));
+       } finally {
+            conn1.close();
+        }
     }
 
     @Test
     public void testIndexPlanSelectionIfBothGlobalAndLocalIndexesHasSameColumnsAndOrder() throws Exception {
         createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
         Connection conn1 = DriverManager.getConnection(getUrl());
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,'z')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,'a')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,'a')");
-        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,'c')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('b',1,2,4,'z')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('f',1,2,3,'a')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('j',2,4,3,'a')");
+        conn1.createStatement().execute("UPSERT INTO "+DATA_TABLE_NAME+" values('q',3,1,1,'c')");
         conn1.commit();
         conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
         conn1.createStatement().execute("CREATE INDEX " + INDEX_TABLE_NAME + "2" + " ON " + DATA_TABLE_NAME + "(v1)");
@@ -316,10 +449,10 @@ public class LocalIndexIT extends BaseIndexIT {
         createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
         Connection conn1 = DriverManager.getConnection(getUrl());
         try {
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,'z')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,'a')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,'a')");
-            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,'c')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,1,'c')");
             conn1.commit();
             conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
             conn1.createStatement().execute("DROP INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME);
@@ -346,4 +479,26 @@ public class LocalIndexIT extends BaseIndexIT {
             conn1.close();
         }
     }
+
+    @Test
+    public void testLocalIndexRowsShouldBeDeletedWhenUserTableRowsDeleted() throws Exception {
+        createBaseTable(DATA_TABLE_NAME, null, "('e','i','o')");
+        Connection conn1 = DriverManager.getConnection(getUrl());
+        try {
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + DATA_TABLE_NAME + " values('q',3,1,1,'c')");
+            conn1.commit();
+            conn1.createStatement().execute("CREATE LOCAL INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_NAME + "(v1)");
+            conn1.createStatement().execute("DELETE FROM " + DATA_TABLE_NAME + " where v1='a'");
+            conn1.commit();
+            conn1 = DriverManager.getConnection(getUrl());
+            ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + INDEX_TABLE_NAME);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+        } finally {
+            conn1.close();
+        }
+    }
 }


Mime
View raw message