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);
