Repository: phoenix Updated Branches: refs/heads/4.x-HBase-1.1 13ee5caa4 -> 1ef00e299
PHOENIX-3042 Using functional index expression in where statement for join query fails Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/1ef00e29 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/1ef00e29 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/1ef00e29 Branch: refs/heads/4.x-HBase-1.1 Commit: 1ef00e299774cdff7c14bbc7943b03f344ae2e7f Parents: 13ee5ca Author: Thomas D'Silva <tdsi...@salesforce.com> Authored: Mon Jul 18 17:05:56 2016 -0700 Committer: Thomas D'Silva <tdsi...@salesforce.com> Committed: Mon Jul 18 17:05:56 2016 -0700 ---------------------------------------------------------------------- .../end2end/index/IndexExpressionIT.java | 82 ++++++++++++++++++++ .../phoenix/compile/IndexStatementRewriter.java | 12 ++- .../apache/phoenix/compile/JoinCompiler.java | 17 ++-- .../apache/phoenix/optimize/QueryOptimizer.java | 2 +- .../parse/IndexExpressionParseNodeRewriter.java | 8 +- .../phoenix/compile/QueryCompilerTest.java | 41 +++++++++- 6 files changed, 144 insertions(+), 18 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java index 2e55594..21da43a 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexExpressionIT.java @@ -1411,5 +1411,87 @@ public class IndexExpressionIT extends BaseHBaseManagedTimeIT { conn.close(); } } + + @Test + public void testImmutableTableGlobalIndexExpressionWithJoin() throws Exception { + helpTestIndexExpressionWithJoin(false, false); + } + + @Test + public void testImmutableTableLocalIndexExpressionWithJoin() throws Exception { + helpTestIndexExpressionWithJoin(false, true); + } + + @Test + public void testMutableTableGlobalIndexExpressionWithJoin() throws Exception { + helpTestIndexExpressionWithJoin(true, false); + } + + @Test + public void testMutableTableLocalIndexExpressionWithJoin() throws Exception { + helpTestIndexExpressionWithJoin(true, true); + } + + public void helpTestIndexExpressionWithJoin(boolean mutable, + boolean localIndex) throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + String nameSuffix = "T" + (mutable ? "MUTABLE" : "_IMMUTABLE") + (localIndex ? "_LOCAL" : "_GLOBAL"); + String tableName = "T" + nameSuffix; + String indexName = "IDX" + nameSuffix; + try { + conn.createStatement().execute( + "CREATE TABLE " + + tableName + + "( c_customer_sk varchar primary key, c_first_name varchar, c_last_name varchar )" + + (!mutable ? "IMMUTABLE_ROWS=true" : "")); + String query = "SELECT * FROM " + tableName; + ResultSet rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + conn.createStatement().execute( + "CREATE " + (localIndex ? "LOCAL" : "") + + " INDEX " + indexName + " ON " + tableName + " (c_customer_sk || c_first_name asc)"); + query = "SELECT * FROM " + indexName; + rs = conn.createStatement().executeQuery(query); + assertFalse(rs.next()); + + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?)"); + stmt.setString(1, "1"); + stmt.setString(2, "David"); + stmt.setString(3, "Smith"); + stmt.execute(); + conn.commit(); + + query = "select c.c_customer_sk from " + tableName + " c " + + "left outer join " + tableName + " c2 on c.c_customer_sk = c2.c_customer_sk " + + "where c.c_customer_sk || c.c_first_name = '1David'"; + rs = conn.createStatement().executeQuery("EXPLAIN "+query); + String explainPlan = QueryUtil.getExplainPlan(rs); + if (localIndex) { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1,'1David']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", explainPlan); + } + else { + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexName + " ['1David']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + indexName + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", explainPlan); + } + + rs = conn.createStatement().executeQuery(query); + assertTrue(rs.next()); + assertEquals("1", rs.getString(1)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/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 6f2cbfa..b20dc1e 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 @@ -43,10 +43,12 @@ public class IndexStatementRewriter extends ParseNodeRewriter { private Map<TableRef, TableRef> multiTableRewriteMap; private final ImmutableBytesWritable ptr = new ImmutableBytesWritable(); + private final boolean setTableAlias; - public IndexStatementRewriter(ColumnResolver dataResolver, Map<TableRef, TableRef> multiTableRewriteMap) { + public IndexStatementRewriter(ColumnResolver dataResolver, Map<TableRef, TableRef> multiTableRewriteMap, boolean setTableAlias) { super(dataResolver); this.multiTableRewriteMap = multiTableRewriteMap; + this.setTableAlias = setTableAlias; } /** @@ -58,7 +60,7 @@ public class IndexStatementRewriter extends ParseNodeRewriter { * @throws SQLException */ public static ParseNode translate(ParseNode node, ColumnResolver dataResolver) throws SQLException { - return rewrite(node, new IndexStatementRewriter(dataResolver, null)); + return rewrite(node, new IndexStatementRewriter(dataResolver, null, false)); } /** @@ -83,7 +85,7 @@ public class IndexStatementRewriter extends ParseNodeRewriter { * @throws SQLException */ public static SelectStatement translate(SelectStatement statement, ColumnResolver dataResolver, Map<TableRef, TableRef> multiTableRewriteMap) throws SQLException { - return rewrite(statement, new IndexStatementRewriter(dataResolver, multiTableRewriteMap)); + return rewrite(statement, new IndexStatementRewriter(dataResolver, multiTableRewriteMap, false)); } @Override @@ -142,6 +144,10 @@ public class IndexStatementRewriter extends ParseNodeRewriter { } private TableName getReplacedTableName(TableRef origRef) { + // if the setTableAlias flag is true and the original table has an alias we use that as the table name + if (setTableAlias && origRef.getTableAlias() != null) + return TableName.create(null, origRef.getTableAlias()); + if (multiTableRewriteMap == null) return null; http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java index 6fab728..e8c05ca 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java @@ -51,12 +51,14 @@ import org.apache.phoenix.parse.DerivedTableNode; import org.apache.phoenix.parse.EqualParseNode; import org.apache.phoenix.parse.HintNode; import org.apache.phoenix.parse.HintNode.Hint; +import org.apache.phoenix.parse.IndexExpressionParseNodeRewriter; import org.apache.phoenix.parse.JoinTableNode; import org.apache.phoenix.parse.JoinTableNode.JoinType; import org.apache.phoenix.parse.NamedTableNode; import org.apache.phoenix.parse.OrderByNode; import org.apache.phoenix.parse.ParseNode; import org.apache.phoenix.parse.ParseNodeFactory; +import org.apache.phoenix.parse.ParseNodeRewriter; import org.apache.phoenix.parse.SelectStatement; import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor; import org.apache.phoenix.parse.TableName; @@ -102,7 +104,6 @@ import com.google.common.collect.Sets; public class JoinCompiler { public enum ColumnRefType { - PREFILTER, JOINLOCAL, GENERAL, } @@ -161,10 +162,6 @@ public class JoinCompiler { if (!compiler.columnRefs.containsKey(ref)) compiler.columnRefs.put(ref, ColumnRefType.JOINLOCAL); } - for (ColumnRef ref : prefilterRefVisitor.getColumnRefMap().keySet()) { - if (!compiler.columnRefs.containsKey(ref)) - compiler.columnRefs.put(ref, ColumnRefType.PREFILTER); - } return joinTable; } @@ -740,8 +737,7 @@ public class JoinCompiler { } else { for (Map.Entry<ColumnRef, ColumnRefType> e : columnRefs.entrySet()) { ColumnRef columnRef = e.getKey(); - if (e.getValue() != ColumnRefType.PREFILTER - && columnRef.getTableRef().equals(tableRef) + if (columnRef.getTableRef().equals(tableRef) && (!retainPKColumns || !SchemaUtil.isPKColumn(columnRef.getColumn()))) { if (columnRef instanceof LocalIndexColumnRef) { sourceColumns.add(new LocalIndexDataColumnRef(context, IndexUtil.getIndexColumnName(columnRef.getColumn()))); @@ -1248,7 +1244,12 @@ public class JoinCompiler { } }); - return IndexStatementRewriter.translate(NODE_FACTORY.select(select, newFrom), resolver, replacement); + SelectStatement indexSelect = IndexStatementRewriter.translate(NODE_FACTORY.select(select, newFrom), resolver, replacement); + for ( TableRef indexTableRef : replacement.values()) { + // replace expressions with corresponding matching columns for functional indexes + indexSelect = ParseNodeRewriter.rewrite(indexSelect, new IndexExpressionParseNodeRewriter(indexTableRef.getTable(), indexTableRef.getTableAlias(), statement.getConnection(), indexSelect.getUdfParseNodes())); + } + return indexSelect; } private static SelectStatement getSubqueryForOptimizedPlan(HintNode hintNode, List<ColumnDef> dynamicCols, TableRef tableRef, Map<ColumnRef, ColumnRefType> columnRefs, ParseNode where, List<ParseNode> groupBy, http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/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 6eb6cb0..e4198ee 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 @@ -230,7 +230,7 @@ public class QueryOptimizer { if (PIndexState.ACTIVE.equals(resolver.getTables().get(0).getTable().getIndexState())) { try { // translate nodes that match expressions that are indexed to the associated column parse node - indexSelect = ParseNodeRewriter.rewrite(indexSelect, new IndexExpressionParseNodeRewriter(index, statement.getConnection(), indexSelect.getUdfParseNodes())); + indexSelect = ParseNodeRewriter.rewrite(indexSelect, new IndexExpressionParseNodeRewriter(index, null, statement.getConnection(), indexSelect.getUdfParseNodes())); QueryCompiler compiler = new QueryCompiler(statement, indexSelect, resolver, targetColumns, parallelIteratorFactory, dataPlan.getContext().getSequenceManager(), isProjected); QueryPlan plan = compiler.compile(); http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/phoenix-core/src/main/java/org/apache/phoenix/parse/IndexExpressionParseNodeRewriter.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/IndexExpressionParseNodeRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/IndexExpressionParseNodeRewriter.java index 9f7b2bf..785b602 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/parse/IndexExpressionParseNodeRewriter.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/IndexExpressionParseNodeRewriter.java @@ -37,14 +37,14 @@ public class IndexExpressionParseNodeRewriter extends ParseNodeRewriter { private final Map<ParseNode, ParseNode> indexedParseNodeToColumnParseNodeMap; - public IndexExpressionParseNodeRewriter(PTable index, PhoenixConnection connection, Map<String, UDFParseNode> udfParseNodes) throws SQLException { + public IndexExpressionParseNodeRewriter(PTable index, String alias, PhoenixConnection connection, Map<String, UDFParseNode> udfParseNodes) throws SQLException { indexedParseNodeToColumnParseNodeMap = Maps.newHashMapWithExpectedSize(index.getColumns().size()); - NamedTableNode tableNode = NamedTableNode.create(null, + NamedTableNode tableNode = NamedTableNode.create(alias, TableName.create(index.getParentSchemaName().getString(), index.getParentTableName().getString()), Collections.<ColumnDef> emptyList()); ColumnResolver dataResolver = FromCompiler.getResolver(tableNode, connection, udfParseNodes); StatementContext context = new StatementContext(new PhoenixStatement(connection), dataResolver); - IndexStatementRewriter rewriter = new IndexStatementRewriter(dataResolver, null); + IndexStatementRewriter rewriter = new IndexStatementRewriter(dataResolver, null, true); ExpressionCompiler expressionCompiler = new ExpressionCompiler(context); int indexPosOffset = (index.getBucketNum() == null ? 0 : 1) + (index.isMultiTenant() ? 1 : 0) + (index.getViewIndexId() == null ? 0 : 1); List<PColumn> pkColumns = index.getPKColumns(); @@ -57,7 +57,7 @@ public class IndexExpressionParseNodeRewriter extends ParseNodeRewriter { PDataType expressionDataType = dataExpression.getDataType(); ParseNode indexedParseNode = expressionParseNode.accept(rewriter); PDataType indexColType = IndexUtil.getIndexColumnDataType(dataExpression.isNullable(), expressionDataType); - ParseNode columnParseNode = new ColumnParseNode(null, colName, null); + ParseNode columnParseNode = new ColumnParseNode(alias!=null ? TableName.create(null, alias) : null, colName, null); if ( indexColType != expressionDataType) { columnParseNode = NODE_FACTORY.cast(columnParseNode, expressionDataType, null, null); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/1ef00e29/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java index 133eb37..7aab048 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java @@ -48,10 +48,8 @@ import org.apache.hadoop.hbase.util.Bytes; import org.apache.phoenix.compile.OrderByCompiler.OrderBy; import org.apache.phoenix.coprocessor.BaseScannerRegionObserver; import org.apache.phoenix.exception.SQLExceptionCode; -import org.apache.phoenix.expression.CoerceExpression; import org.apache.phoenix.expression.Expression; import org.apache.phoenix.expression.LiteralExpression; -import org.apache.phoenix.expression.ProjectedColumnExpression; import org.apache.phoenix.expression.aggregator.Aggregator; import org.apache.phoenix.expression.aggregator.CountAggregator; import org.apache.phoenix.expression.aggregator.ServerAggregators; @@ -2348,4 +2346,43 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { conn.close(); } } + + @Test + public void testFuncIndexUsage() throws SQLException { + Connection conn = DriverManager.getConnection(getUrl()); + try { + conn.createStatement().execute("CREATE TABLE t1(k INTEGER PRIMARY KEY,"+ + " col1 VARCHAR, col2 VARCHAR)"); + conn.createStatement().execute("CREATE TABLE t2(k INTEGER PRIMARY KEY," + + " col1 VARCHAR, col2 VARCHAR)"); + conn.createStatement().execute("CREATE TABLE t3(j INTEGER PRIMARY KEY," + + " col3 VARCHAR, col4 VARCHAR)"); + conn.createStatement().execute("CREATE INDEX idx ON t1 (col1 || col2)"); + String query = "SELECT a.k from t1 a where a.col1 || a.col2 = 'foobar'"; + ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+query); + String explainPlan = QueryUtil.getExplainPlan(rs); + assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + + " SERVER FILTER BY FIRST KEY ONLY",explainPlan); + query = "SELECT k,j from t3 b join t1 a ON k = j where a.col1 || a.col2 = 'foobar'"; + rs = conn.createStatement().executeQuery("EXPLAIN "+query); + explainPlan = QueryUtil.getExplainPlan(rs); + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER T3\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " DYNAMIC SERVER FILTER BY B.J IN (\"A.:K\")",explainPlan); + query = "SELECT a.k,b.k from t2 b join t1 a ON a.k = b.k where a.col1 || a.col2 = 'foobar'"; + rs = conn.createStatement().executeQuery("EXPLAIN "+query); + explainPlan = QueryUtil.getExplainPlan(rs); + assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER T2\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " DYNAMIC SERVER FILTER BY B.K IN (\"A.:K\")",explainPlan); + } finally { + conn.close(); + } + } }