Repository: phoenix
Updated Branches:
  refs/heads/3.0 21cf00c70 -> 2ce227adf


PHOENIX-1560 Join between global index and data table if INDEX hint used


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

Branch: refs/heads/3.0
Commit: 2ce227adff493f646fa023eed084f88c17935f63
Parents: 21cf00c
Author: maryannxue <wei....@intel.com>
Authored: Fri Jan 16 15:10:48 2015 -0500
Committer: maryannxue <wei....@intel.com>
Committed: Fri Jan 16 15:10:48 2015 -0500

----------------------------------------------------------------------
 .../index/GlobalIndexOptimizationIT.java        | 456 +++++++++++++++++++
 phoenix-core/src/main/antlr3/PhoenixSQL.g       |   2 +-
 .../phoenix/compile/ExpressionCompiler.java     |   2 +-
 .../phoenix/compile/IndexStatementRewriter.java |  12 +
 .../phoenix/compile/ProjectionCompiler.java     |  16 +-
 .../apache/phoenix/compile/RowProjector.java    |  22 +-
 .../phoenix/compile/SubqueryRewriter.java       |   2 +-
 .../apache/phoenix/execute/BaseQueryPlan.java   |   4 +
 .../apache/phoenix/execute/HashJoinPlan.java    |   5 +-
 .../phoenix/expression/InListExpression.java    |  11 +-
 .../apache/phoenix/optimize/QueryOptimizer.java | 176 ++++++-
 .../java/org/apache/phoenix/parse/HintNode.java |   8 +
 .../org/apache/phoenix/parse/InParseNode.java   |   8 +-
 .../apache/phoenix/parse/ParseNodeFactory.java  |  10 +-
 .../apache/phoenix/parse/ParseNodeRewriter.java |   2 +-
 .../java/org/apache/phoenix/util/TestUtil.java  |   5 +
 16 files changed, 696 insertions(+), 45 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
new file mode 100644
index 0000000..ee4bdcd
--- /dev/null
+++ 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
@@ -0,0 +1,456 @@
+/*
+ * 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.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Map;
+import java.util.regex.Pattern;
+
+import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
+import org.apache.phoenix.end2end.Shadower;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import com.google.common.collect.Maps;
+
+public class GlobalIndexOptimizationIT extends BaseHBaseManagedTimeIT {
+
+    @BeforeClass 
+    @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class)
+    public static void doSetup() throws Exception {
+        Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+        // Drop the HBase table metadata for this test
+        props.put(QueryServices.DROP_METADATA_ATTRIB, Boolean.toString(true));
+        // Must update config before starting server
+        setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+    }
+
+    private void createBaseTable(String tableName, Integer saltBuckets, String 
splits, boolean multiTenant) throws SQLException {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String ddl = "CREATE TABLE " + tableName + " (t_id VARCHAR NOT 
NULL,\n" +
+                "k1 INTEGER NOT NULL,\n" +
+                "k2 INTEGER NOT NULL,\n" +
+                "k3 INTEGER,\n" +
+                "v1 VARCHAR,\n" +
+                "CONSTRAINT pk PRIMARY KEY (t_id, k1, k2))\n" +
+                (multiTenant ? "MULTI_TENANT = true\n" : "")
+                        + (saltBuckets != null && splits == null ? 
(",salt_buckets=" + saltBuckets) : "" 
+                        + (saltBuckets == null && splits != null ? (" split on 
" + splits) : ""));
+        conn.createStatement().execute(ddl);
+        conn.close();
+    }
+
+    private void createIndex(String indexName, String tableName, String 
columns) throws SQLException {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String ddl = "CREATE INDEX " + indexName + " ON " + tableName + " (" + 
columns + ")";
+        conn.createStatement().execute(ddl);
+        conn.close();
+    }
+    
+    @Test
+    public void testGlobalIndexOptimization() throws Exception {
+        testOptimization(null);
+    }
+    
+    @Test
+    public void testGlobalIndexOptimizationWithSalting() throws Exception {
+        testOptimization(4);
+    }
+    
+    @Test
+    public void testGlobalIndexOptimizationTenantSpecific() throws Exception {
+        testOptimizationTenantSpecific(null);
+    }
+    
+    @Test
+    public void testGlobalIndexOptimizationWithSaltingTenantSpecific() throws 
Exception {
+        testOptimizationTenantSpecific(4);
+    }
+
+    private void testOptimization(Integer saltBuckets) throws Exception {
+        createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, saltBuckets, 
"('e','i','o')", false);
+        Connection conn1 = DriverManager.getConnection(getUrl());
+        try{
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')");
+            conn1.commit();
+            createIndex(TestUtil.DEFAULT_INDEX_TABLE_NAME, 
TestUtil.DEFAULT_DATA_TABLE_NAME, "v1");
+            
+            String query = "SELECT /*+ INDEX(" + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + 
")*/ t_id, k1, k2, k3, V1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where 
v1='a'";
+            ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ 
query);
+            
+            assertTrue(Pattern.matches(
+                        "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                        "    SKIP-SCAN-JOIN TABLE 0\n" +
+                        "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\['a'\\]\n" +
+                        "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                        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 /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ * FROM " + 
TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'";
+            rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+            
+            assertTrue(Pattern.matches(
+                        "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                        "    SKIP-SCAN-JOIN TABLE 0\n" +
+                        "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\['a'\\]\n" +
+                        "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                        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"));
+            assertFalse(rs.next());
+            
+            query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + 
TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + "  where v1<='z' and k3 > 1 order by 
V1,t_id";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertTrue(Pattern.matches(
+                    "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                    "    SERVER FILTER BY K3 > 1\n" +
+                    "    SERVER SORTED BY \\[V1, T_ID\\]\n" +
+                    "CLIENT MERGE SORT\n" +
+                    "    SKIP-SCAN-JOIN TABLE 0\n" +
+                    "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\[\\*\\] - \\['z'\\]\n" +
+                    "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                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("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"));
+            assertFalse(rs.next());
+            
+            query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, V1, k3 from " + 
TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + "  where v1 <='z' group by v1,t_id, k3";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertTrue(Pattern.matches(
+                "CLIENT PARALLEL \\d-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[T_ID, V1, 
K3\\]\n" +
+                "CLIENT MERGE SORT\n" +
+                "    SKIP-SCAN-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " \\[\\*\\] - \\['z'\\]\n" +
+                "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                QueryUtil.getExplainPlan(rs)));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("b", rs.getString("t_id"));;
+            assertEquals(4, rs.getInt("k3"));
+            assertEquals("z", 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("j", rs.getString("t_id"));
+            assertEquals(2, rs.getInt("k3"));
+            assertEquals("a", rs.getString("V1"));
+            assertTrue(rs.next());
+            assertEquals("q", rs.getString("t_id"));
+            assertEquals(1, rs.getInt("k3"));
+            assertEquals("c", rs.getString("V1"));
+            assertFalse(rs.next());
+            
+            query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ v1,sum(k3) from " + 
TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where v1 <='z'  group by v1 order by 
v1";
+            
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            assertTrue(Pattern.matches(
+                "CLIENT PARALLEL \\d-WAY FULL SCAN OVER T\n" +
+                "    SERVER AGGREGATE INTO DISTINCT ROWS BY \\[V1\\]\n" +
+                "CLIENT MERGE SORT\n" +
+                "CLIENT SORTED BY \\[V1\\]\n" +
+                "    SKIP-SCAN-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER I \\[\\*\\] - 
\\['z'\\]\n" +
+                "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                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();
+        }
+    }
+
+    private void testOptimizationTenantSpecific(Integer saltBuckets) throws 
Exception {
+        createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, saltBuckets, 
"('e','i','o')", true);
+        Connection conn1 = DriverManager.getConnection(getUrl() + ';' + 
PhoenixRuntime.TENANT_ID_ATTRIB + "=tid1");
+        try{
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values(1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values(1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values(2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values(3,1,1,'c')");
+            conn1.commit();
+            createIndex(TestUtil.DEFAULT_INDEX_TABLE_NAME, 
TestUtil.DEFAULT_DATA_TABLE_NAME, "v1");
+            
+            String query = "SELECT /*+ INDEX(" + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + 
")*/ k1,k2,k3,v1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'";
+            ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ 
query);
+            
+            assertTrue(Pattern.matches(
+                        "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T 
\\['tid1'\\]\n" +
+                        "    SKIP-SCAN-JOIN TABLE 0\n" +
+                        "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER I 
\\['tid1','a'\\]\n" +
+                        "    DYNAMIC SERVER FILTER BY \\(K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                        QueryUtil.getExplainPlan(rs)));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(1, rs.getInt("k1"));
+            assertEquals(2, rs.getInt("k2"));
+            assertEquals(3, rs.getInt("k3"));
+            assertEquals("a", rs.getString("v1"));
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt("k1"));
+            assertEquals(4, rs.getInt("k2"));
+            assertEquals(2, rs.getInt("k3"));
+            assertEquals("a", rs.getString("v1"));
+            assertFalse(rs.next());
+        } finally {
+            conn1.close();
+        }
+    }
+
+    @Test
+    public void testGlobalIndexOptimizationOnSharedIndex() throws Exception {
+        createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, null, 
"('e','i','o')", false);
+        Connection conn1 = DriverManager.getConnection(getUrl());
+        try{
+            conn1.createStatement().execute("CREATE INDEX i1 ON " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "(k2,k1) INCLUDE (v1)");
+            conn1.createStatement().execute("CREATE VIEW v AS SELECT * FROM t 
WHERE v1 = 'a'");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')");
+            conn1.commit();
+            ResultSet rs = conn1.createStatement().executeQuery("SELECT 
COUNT(*) FROM v");
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertFalse(rs.next());
+            conn1.createStatement().execute("CREATE INDEX vi1 ON v(k1)");
+            
+            String query = "SELECT /*+ INDEX(v vi1)*/ t_id,k1,k2,k3,v1 FROM v 
where k1 IN (1,2) and k2 IN (3,4)";
+            rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+            
+            assertTrue(Pattern.matches(
+                        "CLIENT PARALLEL 1-WAY FULL SCAN OVER T\n" +
+                        "    SERVER FILTER BY V1 = 'a'\n" +
+                        "    SKIP-SCAN-JOIN TABLE 0\n" +
+                        "        CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS 
OVER _IDX_T \\[-32768,1\\] - \\[-32768,2\\]\n" +
+                        "            SERVER FILTER BY K2 IN \\(3,4\\)\n" +
+                        "    DYNAMIC SERVER FILTER BY \\(T_ID, K1, K2\\) IN 
\\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)",
+                        QueryUtil.getExplainPlan(rs)));
+            
+            rs = conn1.createStatement().executeQuery(query);
+            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"));
+            assertFalse(rs.next());
+        } finally {
+            conn1.close();
+        }
+    }
+
+    @Test
+    public void testNoGlobalIndexOptimization() throws Exception {
+        createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, null, 
"('e','i','o')", false);
+        Connection conn1 = DriverManager.getConnection(getUrl());
+        try{
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')");
+            conn1.createStatement().execute("UPSERT INTO " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')");
+            conn1.commit();
+            conn1.createStatement().execute("CREATE INDEX " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " ON " + TestUtil.DEFAULT_DATA_TABLE_NAME + 
"(v1)");
+            
+            // All columns available in index
+            String query = "SELECT /*+ INDEX(" + 
TestUtil.DEFAULT_DATA_TABLE_NAME + " " + TestUtil.DEFAULT_INDEX_TABLE_NAME + 
")*/ t_id, k1, k2, V1 FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME +" where 
v1='a'";
+            ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ 
query);
+            
+            assertEquals(
+                        "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + 
TestUtil.DEFAULT_INDEX_TABLE_NAME + " ['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"));
+            assertTrue(rs.next());
+            assertEquals("j", rs.getString("t_id"));
+            assertEquals(2, rs.getInt("k1"));
+            assertEquals(4, rs.getInt("k2"));
+            assertFalse(rs.next());
+
+            // No INDEX hint specified
+            query = "SELECT t_id, k1, k2, k3, V1 FROM " + 
TestUtil.DEFAULT_DATA_TABLE_NAME +" where v1='a'";
+            rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
+            
+            assertEquals(
+                        "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                        "    SERVER FILTER BY V1 = '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());
+            
+            // No where clause
+            query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + 
TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " order by V1,t_id";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertEquals(
+                        "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\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"));
+            assertFalse(rs.next());
+            
+            // No where clause in index scan
+            query = "SELECT /*+ INDEX(" + TestUtil.DEFAULT_DATA_TABLE_NAME + " 
" + TestUtil.DEFAULT_INDEX_TABLE_NAME + ")*/ t_id, k1, k2, k3, V1 from " + 
TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + "  where k3 > 1 order by V1,t_id";
+            rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
+            
+            assertEquals(
+                        "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + 
TestUtil.DEFAULT_DATA_TABLE_NAME + "\n" +
+                        "    SERVER FILTER BY K3 > 1\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("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"));
+            assertFalse(rs.next());
+        } finally {
+            conn1.close();
+        }
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/antlr3/PhoenixSQL.g
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/antlr3/PhoenixSQL.g 
b/phoenix-core/src/main/antlr3/PhoenixSQL.g
index 95d51b4..b38b672 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -717,7 +717,7 @@ boolean_expression returns [ParseNode ret]
                       |        (ILIKE r=value_expression {$ret = 
factory.like(l,r,n!=null,LikeType.CASE_INSENSITIVE); } )
                       |        (BETWEEN r1=value_expression AND 
r2=value_expression {$ret = factory.between(l,r1,r2,n!=null); } )
                       |        ((IN ((r=bind_expression {$ret = 
factory.inList(Arrays.asList(l,r),n!=null);} )
-                                | (LPAREN r=subquery_expression RPAREN {$ret = 
factory.in(l,r,n!=null);} )
+                                | (LPAREN r=subquery_expression RPAREN {$ret = 
factory.in(l,r,n!=null,false);} )
                                 | (LPAREN v=one_or_more_expressions RPAREN 
{List<ParseNode> il = new ArrayList<ParseNode>(v.size() + 1); il.add(l); 
il.addAll(v); $ret = factory.inList(il,n!=null);})
                                 )))
                       ))

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index 409950c..07f219a 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -124,7 +124,7 @@ public class ExpressionCompiler extends 
UnsupportedAllParseNodeVisitor<Expressio
     private int nodeCount;
     private final boolean resolveViewConstants;
 
-    ExpressionCompiler(StatementContext context) {
+    public ExpressionCompiler(StatementContext context) {
         this(context,GroupBy.EMPTY_GROUP_BY, false);
     }
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
index 5203d50..f5f6d07 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
@@ -50,6 +50,18 @@ public class IndexStatementRewriter extends 
ParseNodeRewriter {
     }
     
     /**
+     * Rewrite the parse node by translating all data table column references 
to
+     * references to the corresponding index column.
+     * @param node the parse node
+     * @param dataResolver the column resolver
+     * @return new parse node or the same one if nothing was rewritten.
+     * @throws SQLException 
+     */
+    public static ParseNode translate(ParseNode node, ColumnResolver 
dataResolver) throws SQLException {
+        return rewrite(node, new IndexStatementRewriter(dataResolver, null));
+    }
+    
+    /**
      * Rewrite the select statement by translating all data table column 
references to
      * references to the corresponding index column.
      * @param statement the select statement

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java
index f23c3ec..a2dc797 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java
@@ -142,11 +142,9 @@ public class ProjectionCompiler {
             if (resolveColumn) {
                 if (tableRef.getTableAlias() != null) {
                     ref = resolver.resolveColumn(null, 
tableRef.getTableAlias(), colName);
-                    colName = 
SchemaUtil.getColumnName(tableRef.getTableAlias(), colName);
                 } else {
                     String schemaName = table.getSchemaName().getString();
                     ref = resolver.resolveColumn(schemaName.length() == 0 ? 
null : schemaName, table.getTableName().getString(), colName);
-                    colName = 
SchemaUtil.getColumnName(table.getName().getString(), colName);
                 }
             }
             Expression expression = ref.newColumnExpression();
@@ -157,7 +155,7 @@ public class ProjectionCompiler {
             }
             projectedExpressions.add(expression);
             boolean isCaseSensitive = 
!SchemaUtil.normalizeIdentifier(colName).equals(colName);
-            projectedColumns.add(new ExpressionProjector(colName, 
table.getName().getString(), expression, isCaseSensitive));
+            projectedColumns.add(new ExpressionProjector(colName, 
tableRef.getTableAlias() == null ? table.getName().getString() : 
tableRef.getTableAlias(), expression, isCaseSensitive));
         }
     }
     
@@ -191,11 +189,9 @@ public class ProjectionCompiler {
             if (resolveColumn) {
                 if (tableRef.getTableAlias() != null) {
                     ref = resolver.resolveColumn(null, 
tableRef.getTableAlias(), indexColName);
-                    colName = 
SchemaUtil.getColumnName(tableRef.getTableAlias(), colName);
                 } else {
                     String schemaName = index.getSchemaName().getString();
                     ref = resolver.resolveColumn(schemaName.length() == 0 ? 
null : schemaName, index.getTableName().getString(), indexColName);
-                    colName = 
SchemaUtil.getColumnName(table.getName().getString(), colName);
                 }
             }
             Expression expression = ref.newColumnExpression();
@@ -204,7 +200,7 @@ public class ProjectionCompiler {
             // appear as a column in an index
             projectedExpressions.add(expression);
             boolean isCaseSensitive = 
!SchemaUtil.normalizeIdentifier(colName).equals(colName);
-            ExpressionProjector projector = new ExpressionProjector(colName, 
table.getName().getString(), expression, isCaseSensitive);
+            ExpressionProjector projector = new ExpressionProjector(colName, 
tableRef.getTableAlias() == null ? table.getName().getString() : 
tableRef.getTableAlias(), expression, isCaseSensitive);
             projectedColumns.add(projector);
         }
     }
@@ -218,8 +214,8 @@ public class ProjectionCompiler {
             projectedExpressions.add(expression);
             String colName = column.getName().toString();
             boolean isCaseSensitive = 
!SchemaUtil.normalizeIdentifier(colName).equals(colName);
-            projectedColumns.add(new ExpressionProjector(colName, 
table.getName()
-                    .getString(), expression, isCaseSensitive));
+            projectedColumns.add(new ExpressionProjector(colName, 
tableRef.getTableAlias() == null ? 
+                    table.getName().getString() : tableRef.getTableAlias(), 
expression, isCaseSensitive));
         }
     }
 
@@ -237,7 +233,7 @@ public class ProjectionCompiler {
             String colName = column.getName().toString();
             boolean isCaseSensitive = 
!SchemaUtil.normalizeIdentifier(colName).equals(colName);
             projectedColumns.add(new ExpressionProjector(colName, 
-                    table.getName().getString(), expression, isCaseSensitive));
+                    tableRef.getTableAlias() == null ? 
table.getName().getString() : tableRef.getTableAlias(), expression, 
isCaseSensitive));
         }
     }
     
@@ -336,7 +332,7 @@ public class ProjectionCompiler {
                 String columnAlias = aliasedNode.getAlias() != null ? 
aliasedNode.getAlias() : 
SchemaUtil.normalizeIdentifier(aliasedNode.getNode().getAlias());
                 boolean isCaseSensitive = aliasedNode.getAlias() != null ? 
aliasedNode.isCaseSensitve() : (columnAlias != null ? 
SchemaUtil.isCaseSensitive(aliasedNode.getNode().getAlias()) : 
selectVisitor.isCaseSensitive);
                 String name = columnAlias == null ? expression.toString() : 
columnAlias;
-                projectedColumns.add(new ExpressionProjector(name, 
table.getName().getString(), expression, isCaseSensitive));
+                projectedColumns.add(new ExpressionProjector(name, 
tableRef.getTableAlias() == null ? table.getName().getString() : 
tableRef.getTableAlias(), expression, isCaseSensitive));
             }
             if(arrayKVFuncs.size() > 0 && arrayKVRefs.size() > 0) {
                 serailizeArrayIndexInformationAndSetInScan(context, 
arrayKVFuncs, arrayKVRefs);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/compile/RowProjector.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/RowProjector.java 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/RowProjector.java
index e6e7d88..ee575f1 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/RowProjector.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/RowProjector.java
@@ -22,9 +22,12 @@ import java.util.Collections;
 import java.util.List;
 import java.util.Map;
 
+import org.apache.phoenix.schema.AmbiguousColumnException;
 import org.apache.phoenix.schema.ColumnNotFoundException;
 import org.apache.phoenix.util.SchemaUtil;
 
+import com.google.common.collect.ArrayListMultimap;
+import com.google.common.collect.ListMultimap;
 import com.google.common.collect.Maps;
 
 
@@ -41,7 +44,7 @@ public class RowProjector {
     public static final RowProjector EMPTY_PROJECTOR = new 
RowProjector(Collections.<ColumnProjector>emptyList(),0, true);
 
     private final List<? extends ColumnProjector> columnProjectors;
-    private final Map<String,Integer> reverseIndex;
+    private final ListMultimap<String,Integer> reverseIndex;
     private final boolean allCaseSensitive;
     private final boolean someCaseSensitive;
     private final int estimatedSize;
@@ -60,7 +63,7 @@ public class RowProjector {
     public RowProjector(List<? extends ColumnProjector> columnProjectors, int 
estimatedRowSize, boolean isProjectEmptyKeyValue) {
         this.columnProjectors = Collections.unmodifiableList(columnProjectors);
         int position = columnProjectors.size();
-        reverseIndex = Maps.newHashMapWithExpectedSize(position);
+        reverseIndex = ArrayListMultimap.<String, Integer>create();
         boolean allCaseSensitive = true;
         boolean someCaseSensitive = false;
         for (--position; position >= 0; position--) {
@@ -68,6 +71,9 @@ public class RowProjector {
             allCaseSensitive &= colProjector.isCaseSensitive();
             someCaseSensitive |= colProjector.isCaseSensitive();
             reverseIndex.put(colProjector.getName(), position);
+            if (!colProjector.getTableName().isEmpty()) {
+                
reverseIndex.put(SchemaUtil.getColumnName(colProjector.getTableName(), 
colProjector.getName()), position);
+            }
         }
         this.allCaseSensitive = allCaseSensitive;
         this.someCaseSensitive = someCaseSensitive;
@@ -87,17 +93,21 @@ public class RowProjector {
         if (!someCaseSensitive) {
             name = SchemaUtil.normalizeIdentifier(name);
         }
-        Integer index = reverseIndex.get(name);
-        if (index == null) {
+        List<Integer> index = reverseIndex.get(name);
+        if (index.isEmpty()) {
             if (!allCaseSensitive && someCaseSensitive) {
                 name = SchemaUtil.normalizeIdentifier(name);
                 index = reverseIndex.get(name);
             }
-            if (index == null) {
+            if (index.isEmpty()) {
                 throw new ColumnNotFoundException(name);
             }
         }
-        return index;
+        if (index.size() > 1) {
+            throw new AmbiguousColumnException(name);
+        }
+        
+        return index.get(0);
     }
     
     public ColumnProjector getColumnProjector(int index) {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
index 01aca00..60067e5 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
@@ -142,7 +142,7 @@ public class SubqueryRewriter extends ParseNodeRewriter {
         SelectStatement subquery = subqueryNode.getSelectNode();
         String rhsTableAlias = ParseNodeFactory.createTempAlias();
         List<AliasedNode> selectNodes = fixAliasedNodes(subquery.getSelect(), 
true);
-        subquery = NODE_FACTORY.select(subquery, true, selectNodes);
+        subquery = NODE_FACTORY.select(subquery, !node.isSubqueryDistinct(), 
selectNodes);
         ParseNode onNode = getJoinConditionNode(l.get(0), selectNodes, 
rhsTableAlias);
         TableNode rhsTable = NODE_FACTORY.derivedTable(rhsTableAlias, 
subquery);
         JoinType joinType = isTopNode ? (node.isNegate() ? JoinType.Anti : 
JoinType.Semi) : JoinType.Left;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
index c729e81..975207a 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
@@ -203,6 +203,10 @@ public abstract class BaseQueryPlan implements QueryPlan {
         
         // Optimize here when getting explain plan, as queries don't get 
optimized until after compilation
         QueryPlan plan = 
context.getConnection().getQueryServices().getOptimizer().optimize(context.getStatement(),
 this);
+        if (!(plan instanceof BaseQueryPlan)) {
+            return plan.getExplainPlan();
+        }
+        
         ResultIterator iterator = plan.iterator();
         List<String> planSteps = Lists.newArrayListWithExpectedSize(5);
         iterator.explain(planSteps);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
index bb29118..8316b73 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
@@ -213,9 +213,6 @@ public class HashJoinPlan extends DelegateQueryPlan {
                 }
             }
             
-            if (minValue.equals(maxValue))
-                return ComparisonExpression.create(CompareOp.EQUAL, 
Lists.newArrayList(lhsExpression, 
LiteralExpression.newConstant(type.toObject(minValue), type)), ptr);
-            
             return AndExpression.create(Lists.newArrayList(
                     ComparisonExpression.create(CompareOp.GREATER_OR_EQUAL, 
Lists.newArrayList(lhsExpression, 
LiteralExpression.newConstant(type.toObject(minValue), type)), ptr), 
                     ComparisonExpression.create(CompareOp.LESS_OR_EQUAL, 
Lists.newArrayList(lhsExpression, 
LiteralExpression.newConstant(type.toObject(maxValue), type)), ptr)));
@@ -226,7 +223,7 @@ public class HashJoinPlan extends DelegateQueryPlan {
             children.add(LiteralExpression.newConstant(type.toObject(value), 
type));
         }
         
-        return InListExpression.create(children, false, ptr);
+        return InListExpression.create(children, false, ptr, false);
     }
     
     private boolean useInClause(boolean hasFilters) {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
 
b/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
index fdb20ff..073570c 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/expression/InListExpression.java
@@ -55,14 +55,21 @@ public class InListExpression extends BaseSingleExpression {
     private int valuesByteLength;
     private int fixedWidth = -1;
     private List<Expression> keyExpressions; // client side only
-
+    
     public static Expression create (List<Expression> children, boolean 
isNegate, ImmutableBytesWritable ptr) throws SQLException {
+        return create(children, isNegate, ptr, true);
+    }
+    
+    public static Expression create (List<Expression> children, boolean 
isNegate, ImmutableBytesWritable ptr, boolean allowShortcut) throws 
SQLException {
         Expression firstChild = children.get(0);
         
         if (firstChild.isStateless() && (!firstChild.evaluate(null, ptr) || 
ptr.getLength() == 0)) {
             return LiteralExpression.newConstant(null, PDataType.BOOLEAN, 
firstChild.getDeterminism());
         }
-        if (children.size() == 2) {
+        // We set allowShortcut to false for child/parent join optimization 
since we 
+        // compare RVC expressions with literal expressions and we want to 
avoid 
+        // RVC-rewrite operation in ComparisonExpression.create().
+        if (allowShortcut && children.size() == 2) {
             return ComparisonExpression.create(isNegate ? CompareOp.NOT_EQUAL 
: CompareOp.EQUAL, children, ptr);
         }
         

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java 
b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index d821e2a..0da988c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
@@ -25,15 +25,24 @@ import java.util.List;
 
 import org.apache.phoenix.compile.ColumnProjector;
 import org.apache.phoenix.compile.ColumnResolver;
+import org.apache.phoenix.compile.ExpressionCompiler;
 import org.apache.phoenix.compile.FromCompiler;
 import org.apache.phoenix.compile.IndexStatementRewriter;
 import org.apache.phoenix.compile.QueryCompiler;
 import org.apache.phoenix.compile.QueryPlan;
 import org.apache.phoenix.compile.SequenceManager;
+import org.apache.phoenix.compile.StatementContext;
+import org.apache.phoenix.compile.StatementNormalizer;
+import org.apache.phoenix.compile.SubqueryRewriter;
 import org.apache.phoenix.iterate.ParallelIteratorFactory;
 import org.apache.phoenix.jdbc.PhoenixStatement;
+import org.apache.phoenix.parse.AliasedNode;
 import org.apache.phoenix.parse.HintNode;
 import org.apache.phoenix.parse.HintNode.Hint;
+import org.apache.phoenix.parse.AndParseNode;
+import org.apache.phoenix.parse.BooleanParseNodeVisitor;
+import org.apache.phoenix.parse.ColumnParseNode;
+import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.ParseNodeFactory;
 import org.apache.phoenix.parse.SelectStatement;
 import org.apache.phoenix.parse.TableNode;
@@ -41,10 +50,12 @@ import org.apache.phoenix.query.QueryServices;
 import org.apache.phoenix.query.QueryServicesOptions;
 import org.apache.phoenix.schema.ColumnNotFoundException;
 import org.apache.phoenix.schema.PColumn;
+import org.apache.phoenix.schema.PDataType;
 import org.apache.phoenix.schema.PDatum;
 import org.apache.phoenix.schema.PIndexState;
 import org.apache.phoenix.schema.PTable;
 import org.apache.phoenix.schema.PTableType;
+import org.apache.phoenix.util.IndexUtil;
 
 import com.google.common.collect.Lists;
 
@@ -136,7 +147,7 @@ public class QueryOptimizer {
         }
         
         for (PTable index : indexes) {
-            QueryPlan plan = addPlan(statement, translatedIndexSelect, index, 
targetColumns, parallelIteratorFactory, dataPlan);
+            QueryPlan plan = addPlan(statement, translatedIndexSelect, index, 
targetColumns, parallelIteratorFactory, dataPlan, false);
             if (plan != null) {
                 // Query can't possibly return anything so just return this 
plan.
                 if (plan.isDegenerate()) {
@@ -186,7 +197,7 @@ public class QueryOptimizer {
                     // Hinted index is applicable, so return it's index
                     PTable index = indexes.get(indexPos);
                     indexes.remove(indexPos);
-                    QueryPlan plan = addPlan(statement, select, index, 
targetColumns, parallelIteratorFactory, dataPlan);
+                    QueryPlan plan = addPlan(statement, select, index, 
targetColumns, parallelIteratorFactory, dataPlan, true);
                     if (plan != null) {
                         return plan;
                     }
@@ -206,7 +217,7 @@ public class QueryOptimizer {
         return -1;
     }
     
-    private static QueryPlan addPlan(PhoenixStatement statement, 
SelectStatement select, PTable index, List<? extends PDatum> targetColumns, 
ParallelIteratorFactory parallelIteratorFactory, QueryPlan dataPlan) throws 
SQLException {
+    private static QueryPlan addPlan(PhoenixStatement statement, 
SelectStatement select, PTable index, List<? extends PDatum> targetColumns, 
ParallelIteratorFactory parallelIteratorFactory, QueryPlan dataPlan, boolean 
isHinted) throws SQLException {
         int nColumns = dataPlan.getProjector().getColumnCount();
         String alias = '"' + dataPlan.getTableRef().getTableAlias() + '"'; // 
double quote in case it's case sensitive
         String schemaName = index.getParentSchemaName().getString();
@@ -214,25 +225,72 @@ public class QueryOptimizer {
 
         String tableName = '"' + index.getTableName().getString() + '"';
         TableNode table = FACTORY.namedTable(alias, FACTORY.table(schemaName, 
tableName));
-        try {
-            SelectStatement indexSelect = FACTORY.select(select, table);
-            ColumnResolver resolver = 
FromCompiler.getResolverForQuery(indexSelect, statement.getConnection());
-            // Check index state of now potentially updated index table to 
make sure it's active
-            if 
(PIndexState.ACTIVE.equals(resolver.getTables().get(0).getTable().getIndexState()))
 {
+        SelectStatement indexSelect = FACTORY.select(select, table);
+        ColumnResolver resolver = 
FromCompiler.getResolverForQuery(indexSelect, statement.getConnection());
+        // Check index state of now potentially updated index table to make 
sure it's active
+        if 
(PIndexState.ACTIVE.equals(resolver.getTables().get(0).getTable().getIndexState()))
 {
+            try {
                 QueryCompiler compiler = new QueryCompiler(statement, 
indexSelect, resolver, targetColumns, parallelIteratorFactory, 
dataPlan.getContext().getSequenceManager());
                 QueryPlan plan = compiler.compile();
                 // Checking number of columns handles the wildcard cases 
correctly, as in that case the index
                 // must contain all columns from the data table to be able to 
be used.
-                if (plan.getTableRef().getTable().getIndexState() == 
PIndexState.ACTIVE && plan.getProjector().getColumnCount() == nColumns) {
-                    return plan;
+                if (plan.getTableRef().getTable().getIndexState() == 
PIndexState.ACTIVE) {
+                    if (plan.getProjector().getColumnCount() == nColumns) {
+                        return plan;
+                    } else {
+                        throw new ColumnNotFoundException("*");
+                    }
+                }
+            } catch (ColumnNotFoundException e) {
+                /* Means that a column is being used that's not in our index.
+                 * Since we currently don't keep stats, we don't know the 
selectivity of the index.
+                 * For now, if this is a hinted plan, we will try rewriting 
the query as a subquery;
+                 * otherwise we just don't use this index (as opposed to 
trying to join back from
+                 * the index table to the data table.
+                 */
+                SelectStatement dataSelect = 
(SelectStatement)dataPlan.getStatement();
+                ParseNode where = dataSelect.getWhere();
+                if (isHinted && where != null) {
+                    StatementContext context = new StatementContext(statement, 
resolver);
+                    WhereConditionRewriter whereRewriter = new 
WhereConditionRewriter(dataPlan.getContext().getResolver(), context);
+                    where = where.accept(whereRewriter);
+                    if (where != null) {
+                        PTable dataTable = dataPlan.getTableRef().getTable();
+                        List<PColumn> pkColumns = dataTable.getPKColumns();
+                        List<AliasedNode> aliasedNodes = 
Lists.<AliasedNode>newArrayListWithExpectedSize(pkColumns.size());
+                        List<ParseNode> nodes = 
Lists.<ParseNode>newArrayListWithExpectedSize(pkColumns.size());
+                        boolean isSalted = dataTable.getBucketNum() != null;
+                        boolean isTenantSpecific = dataTable.isMultiTenant() 
&& statement.getConnection().getTenantId() != null;
+                        int posOffset = (isSalted ? 1 : 0) + (isTenantSpecific 
? 1 : 0);
+                        for (int i = posOffset; i < pkColumns.size(); i++) {
+                            PColumn column = pkColumns.get(i);
+                            String indexColName = 
IndexUtil.getIndexColumnName(column);
+                            ParseNode indexColNode = new ColumnParseNode(null, 
'"' + indexColName + '"', indexColName);
+                            PDataType indexColType = 
IndexUtil.getIndexColumnDataType(column);
+                            PDataType dataColType = column.getDataType();
+                            if (indexColType != dataColType) {
+                                indexColNode = FACTORY.cast(indexColNode, 
dataColType, null, null);
+                            }
+                            aliasedNodes.add(FACTORY.aliasedNode(null, 
indexColNode));
+                            nodes.add(new ColumnParseNode(null, '"' + 
column.getName().getString() + '"'));
+                        }
+                        SelectStatement innerSelect = 
FACTORY.select(indexSelect.getFrom(), indexSelect.getHint(), false, 
aliasedNodes, where, null, null, null, indexSelect.getLimit(), 
indexSelect.getBindCount(), false, indexSelect.hasSequence());
+                        ParseNode outerWhere = FACTORY.in(nodes.size() == 1 ? 
nodes.get(0) : FACTORY.rowValueConstructor(nodes), 
FACTORY.subquery(innerSelect, false), false, true);
+                        ParseNode extractedCondition = 
whereRewriter.getExtractedCondition();
+                        if (extractedCondition != null) {
+                            outerWhere = 
FACTORY.and(Lists.newArrayList(outerWhere, extractedCondition));
+                        }
+                        HintNode hint = 
HintNode.combine(HintNode.subtract(indexSelect.getHint(), new Hint[] 
{Hint.INDEX, Hint.RANGE_SCAN_HASH_JOIN}), FACTORY.hint("NO_INDEX 
SKIP_SCAN_HASH_JOIN"));
+                        SelectStatement query = FACTORY.select(dataSelect, 
hint, outerWhere);
+                        ColumnResolver queryResolver = 
FromCompiler.getResolverForQuery(query, statement.getConnection());
+                        query = SubqueryRewriter.transform(query, 
queryResolver, statement.getConnection());
+                        queryResolver = 
FromCompiler.getResolverForQuery(query, statement.getConnection());
+                        query = StatementNormalizer.normalize(query, 
queryResolver);
+                        QueryPlan plan = new QueryCompiler(statement, query, 
queryResolver).compile();
+                        return plan;
+                    }
                 }
             }
-        } catch (ColumnNotFoundException e) {
-            /* Means that a column is being used that's not in our index.
-             * Since we currently don't keep stats, we don't know the 
selectivity of the index.
-             * For now, we just don't use this index (as opposed to trying to 
join back from
-             * the index table to the data table.
-             */
         }
         return null;
     }
@@ -345,4 +403,90 @@ public class QueryOptimizer {
     }
 
     
+    private static class WhereConditionRewriter extends 
BooleanParseNodeVisitor<ParseNode> {
+        private final ColumnResolver dataResolver;
+        private final ExpressionCompiler expressionCompiler;
+        private List<ParseNode> extractedConditions;
+        
+        public WhereConditionRewriter(ColumnResolver dataResolver, 
StatementContext context) throws SQLException {
+            this.dataResolver = dataResolver;
+            this.expressionCompiler = new ExpressionCompiler(context);
+            this.extractedConditions = Lists.<ParseNode> newArrayList();
+        }
+        
+        public ParseNode getExtractedCondition() {
+            if (this.extractedConditions.isEmpty())
+                return null;
+            
+            if (this.extractedConditions.size() == 1)
+                return this.extractedConditions.get(0);
+            
+            return FACTORY.and(this.extractedConditions);            
+        }
+        
+        @Override
+        public List<ParseNode> newElementList(int size) {
+            return Lists.<ParseNode> newArrayListWithExpectedSize(size);
+        }
+
+        @Override
+        public void addElement(List<ParseNode> l, ParseNode element) {
+            if (element != null) {
+                l.add(element);
+            }
+        }
+
+        @Override
+        public boolean visitEnter(AndParseNode node) throws SQLException {
+            return true;
+        }
+
+        @Override
+        public ParseNode visitLeave(AndParseNode node, List<ParseNode> l)
+                throws SQLException {
+            if (l.equals(node.getChildren()))
+                return node;
+
+            if (l.isEmpty())
+                return null;
+            
+            if (l.size() == 1)
+                return l.get(0);
+            
+            return FACTORY.and(l);
+        }
+
+        @Override
+        protected boolean enterBooleanNode(ParseNode node) throws SQLException 
{
+            return false;
+        }
+
+        @Override
+        protected ParseNode leaveBooleanNode(ParseNode node, List<ParseNode> l)
+                throws SQLException {
+            ParseNode translatedNode = IndexStatementRewriter.translate(node, 
dataResolver);
+            expressionCompiler.reset();
+            try {
+                translatedNode.accept(expressionCompiler);
+            } catch (ColumnNotFoundException e) {
+                extractedConditions.add(node);
+                return null;
+            }
+            
+            return translatedNode;
+        }
+
+        @Override
+        protected boolean enterNonBooleanNode(ParseNode node)
+                throws SQLException {
+            return false;
+        }
+
+        @Override
+        protected ParseNode leaveNonBooleanNode(ParseNode node,
+                List<ParseNode> l) throws SQLException {
+            return node;
+        }
+    }
+    
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java 
b/phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
index 068547f..5ee8016 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
@@ -123,6 +123,14 @@ public class HintNode {
         return new HintNode(hints);
     }
     
+    public static HintNode subtract(HintNode hintNode, Hint[] remove) {
+        Map<Hint,String> hints = new HashMap<Hint,String>(hintNode.hints);
+        for (Hint hint : remove) {
+            hints.remove(hint);
+        }
+        return new HintNode(hints);
+    }
+    
     private HintNode() {
         hints = new HashMap<Hint,String>();
     }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/parse/InParseNode.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/parse/InParseNode.java 
b/phoenix-core/src/main/java/org/apache/phoenix/parse/InParseNode.java
index 151453a..acd71b1 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/InParseNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/InParseNode.java
@@ -32,15 +32,21 @@ import java.util.List;
  */
 public class InParseNode extends BinaryParseNode {
     private final boolean negate;
+    private final boolean isSubqueryDistinct;
 
-    InParseNode(ParseNode l, ParseNode r, boolean negate) {
+    InParseNode(ParseNode l, ParseNode r, boolean negate, boolean 
isSubqueryDistinct) {
         super(l, r);
         this.negate = negate;
+        this.isSubqueryDistinct = isSubqueryDistinct;
     }
     
     public boolean isNegate() {
         return negate;
     }
+    
+    public boolean isSubqueryDistinct() {
+        return isSubqueryDistinct;
+    }
 
     @Override
     public <T> T accept(ParseNodeVisitor<T> visitor) throws SQLException {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java 
b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
index e3a0076..9ab31b1 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
@@ -405,8 +405,8 @@ public class ParseNodeFactory {
         return new ExistsParseNode(child, negate);
     }
 
-    public InParseNode in(ParseNode l, ParseNode r, boolean negate) {
-        return new InParseNode(l, r, negate);
+    public InParseNode in(ParseNode l, ParseNode r, boolean negate, boolean 
isSubqueryDistinct) {
+        return new InParseNode(l, r, negate, isSubqueryDistinct);
     }
 
     public IsNullParseNode isNull(ParseNode child, boolean negate) {
@@ -640,6 +640,12 @@ public class ParseNodeFactory {
                 statement.getBindCount(), statement.isAggregate(), 
statement.hasSequence());
     }
 
+    public SelectStatement select(SelectStatement statement, HintNode hint, 
ParseNode where) {
+        return select(statement.getFrom(), hint, statement.isDistinct(), 
statement.getSelect(), where, statement.getGroupBy(),
+                statement.getHaving(), statement.getOrderBy(), 
statement.getLimit(), statement.getBindCount(), statement.isAggregate(),
+                statement.hasSequence());
+    }
+
     public SelectStatement select(SelectStatement statement, LimitNode limit) {
         return select(statement.getFrom(), statement.getHint(), 
statement.isDistinct(), statement.getSelect(),
                 statement.getWhere(), statement.getGroupBy(), 
statement.getHaving(), statement.getOrderBy(), limit,

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java 
b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
index 27d2aa1..d81e6be 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
@@ -350,7 +350,7 @@ public class ParseNodeRewriter extends 
TraverseAllParseNodeVisitor<ParseNode> {
         ParseNode normNode = leaveCompoundNode(node, nodes, new 
CompoundNodeFactory() {
             @Override
             public ParseNode createNode(List<ParseNode> children) {
-                return NODE_FACTORY.in(children.get(0), children.get(1), 
node.isNegate());
+                return NODE_FACTORY.in(children.get(0), children.get(1), 
node.isNegate(), node.isSubqueryDistinct());
             }
         });
         return normNode;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2ce227ad/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java 
b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
index b832c72..bb02dd1 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
@@ -84,6 +84,11 @@ import com.google.common.collect.Lists;
 public class TestUtil {
     private TestUtil() {
     }
+    public static final String DEFAULT_SCHEMA_NAME = "";
+    public static final String DEFAULT_DATA_TABLE_NAME = "T";
+    public static final String DEFAULT_INDEX_TABLE_NAME = "I";
+    public static final String DEFAULT_DATA_TABLE_FULL_NAME = 
SchemaUtil.getTableName(DEFAULT_SCHEMA_NAME, "T");
+    public static final String DEFAULT_INDEX_TABLE_FULL_NAME = 
SchemaUtil.getTableName(DEFAULT_SCHEMA_NAME, "I");
 
     public static final String CF_NAME = "a";
     public static final byte[] CF = Bytes.toBytes(CF_NAME);

Reply via email to