You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by sa...@apache.org on 2016/08/05 21:03:04 UTC
[05/50] [abbrv] phoenix git commit: PHOENIX-3042 Using functional
index expression in where statement for join query fails
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/51214638
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/51214638
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/51214638
Branch: refs/heads/encodecolumns
Commit: 51214638f1fd3d7881addab2fd347d8ef10980cd
Parents: 6416181
Author: Thomas D'Silva <td...@salesforce.com>
Authored: Mon Jul 18 17:00:43 2016 -0700
Committer: Thomas D'Silva <td...@salesforce.com>
Committed: Mon Jul 18 17:00:43 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/51214638/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/51214638/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/51214638/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/51214638/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/51214638/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/51214638/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();
+ }
+ }
}