Improved rule matching for indexed NL joins using primary btree indexes (if hint was given). Fixed a few bugs in the access method rewrite rule. Added tests to guard against regressions.
git-svn-id: https://asterixdb.googlecode.com/svn/branches/asterix_stabilization@855 eaa15691-b419-025a-1212-ee371bd00084
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
index 6efcabd..c7cdd57 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
@@ -66,17 +66,13 @@
protected void fillSubTreeIndexExprs(OptimizableOperatorSubTree subTree,
Map<IAccessMethod, AccessMethodAnalysisContext> analyzedAMs) throws AlgebricksException {
- // The assign may be null if there is only a filter on the primary index key.
- // Match variables from lowest assign which comes directly after the dataset scan.
- List<LogicalVariable> varList = (!subTree.assigns.isEmpty()) ? subTree.assigns.get(subTree.assigns.size() - 1)
- .getVariables() : subTree.dataSourceScan.getVariables();
Iterator<Map.Entry<IAccessMethod, AccessMethodAnalysisContext>> amIt = analyzedAMs.entrySet().iterator();
// Check applicability of indexes by access method type.
while (amIt.hasNext()) {
Map.Entry<IAccessMethod, AccessMethodAnalysisContext> entry = amIt.next();
AccessMethodAnalysisContext amCtx = entry.getValue();
- // For the current access method type, map variables from the assign op to applicable indexes.
- fillAllIndexExprs(varList, subTree, amCtx);
+ // For the current access method type, map variables to applicable indexes.
+ fillAllIndexExprs(subTree, amCtx);
}
}
@@ -156,12 +152,10 @@
// If the access method requires all exprs to be matched but they are not, remove this candidate.
if (!allUsed && accessMethod.matchAllIndexExprs()) {
it.remove();
- return;
}
// A prefix of the index exprs may have been matched.
if (lastFieldMatched < 0 && accessMethod.matchPrefixIndexExprs()) {
it.remove();
- return;
}
}
}
@@ -259,37 +253,43 @@
return true;
}
- protected void fillAllIndexExprs(List<LogicalVariable> varList, OptimizableOperatorSubTree subTree,
+ protected void fillAllIndexExprs(OptimizableOperatorSubTree subTree,
AccessMethodAnalysisContext analysisCtx) throws AlgebricksException {
for (int optFuncExprIndex = 0; optFuncExprIndex < analysisCtx.matchedFuncExprs.size(); optFuncExprIndex++) {
- for (int varIndex = 0; varIndex < varList.size(); varIndex++) {
- LogicalVariable var = varList.get(varIndex);
- IOptimizableFuncExpr optFuncExpr = analysisCtx.matchedFuncExprs.get(optFuncExprIndex);
+ IOptimizableFuncExpr optFuncExpr = analysisCtx.matchedFuncExprs.get(optFuncExprIndex);
+ // Try to match variables from optFuncExpr to assigns.
+ for (int assignIndex = 0; assignIndex < subTree.assigns.size(); assignIndex++) {
+ AssignOperator assignOp = subTree.assigns.get(assignIndex);
+ List<LogicalVariable> varList = assignOp.getVariables();
+ for (int varIndex = 0; varIndex < varList.size(); varIndex++) {
+ LogicalVariable var = varList.get(varIndex);
+ int funcVarIndex = optFuncExpr.findLogicalVar(var);
+ // No matching var in optFuncExpr.
+ if (funcVarIndex == -1) {
+ continue;
+ }
+ // At this point we have matched the optimizable func expr at optFuncExprIndex to an assigned variable.
+ String fieldName = getFieldNameOfFieldAccess(assignOp, subTree.recordType, varIndex);
+ if (fieldName == null) {
+ continue;
+ }
+ // Set the fieldName in the corresponding matched function expression, and remember matching subtree.
+ optFuncExpr.setFieldName(funcVarIndex, fieldName);
+ optFuncExpr.setOptimizableSubTree(funcVarIndex, subTree);
+ fillIndexExprs(fieldName, optFuncExprIndex, subTree.dataset, analysisCtx);
+ }
+ }
+ // Try to match variables from optFuncExpr to datasourcescan if not already matched in assigns.
+ List<LogicalVariable> dsVarList = subTree.dataSourceScan.getVariables();
+ for (int varIndex = 0; varIndex < dsVarList.size(); varIndex++) {
+ LogicalVariable var = dsVarList.get(varIndex);
int funcVarIndex = optFuncExpr.findLogicalVar(var);
// No matching var in optFuncExpr.
if (funcVarIndex == -1) {
continue;
}
- // At this point we have matched the optimizable func expr at optFuncExprIndex to an assigned variable.
- String fieldName = null;
- if (!subTree.assigns.isEmpty()) {
- // Get the fieldName corresponding to the assigned variable at varIndex
- // from the assign operator right above the datasource scan.
- // If the expr at varIndex is not a fieldAccess we get back null.
- fieldName = getFieldNameOfFieldAccess(subTree.assigns.get(subTree.assigns.size() - 1),
- subTree.recordType, varIndex);
- if (fieldName == null) {
- continue;
- }
- } else {
- // We don't have an assign, only a datasource scan.
- // The last var. is the record itself, so skip it.
- if (varIndex >= varList.size() - 1) {
- break;
- }
- // The variable value is one of the partitioning fields.
- fieldName = DatasetUtils.getPartitioningKeys(subTree.dataset).get(varIndex);
- }
+ // The variable value is one of the partitioning fields.
+ String fieldName = DatasetUtils.getPartitioningKeys(subTree.dataset).get(varIndex);
// Set the fieldName in the corresponding matched function expression, and remember matching subtree.
optFuncExpr.setFieldName(funcVarIndex, fieldName);
optFuncExpr.setOptimizableSubTree(funcVarIndex, subTree);
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
index 15c216d..9580d37 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
@@ -100,7 +100,6 @@
if (primaryIndexUnnestOp == null) {
return false;
}
-
Mutable<ILogicalOperator> assignRef = (subTree.assignRefs.isEmpty()) ? null : subTree.assignRefs.get(0);
AssignOperator assign = null;
if (assignRef != null) {
@@ -131,16 +130,8 @@
public boolean applyJoinPlanTransformation(Mutable<ILogicalOperator> joinRef,
OptimizableOperatorSubTree leftSubTree, OptimizableOperatorSubTree rightSubTree, Index chosenIndex,
AccessMethodAnalysisContext analysisCtx, IOptimizationContext context) throws AlgebricksException {
-
AbstractBinaryJoinOperator joinOp = (AbstractBinaryJoinOperator) joinRef.getValue();
Mutable<ILogicalExpression> conditionRef = joinOp.getCondition();
-
- // Skip this rule if there is no index NL hint.
- AbstractFunctionCallExpression conditionFunc = (AbstractFunctionCallExpression) conditionRef.getValue();
- if (!conditionFunc.getAnnotations().containsKey(IndexedNLJoinExpressionAnnotation.INSTANCE)) {
- return false;
- }
-
// Determine if the index is applicable on the left or right side (if both, we arbitrarily prefer the left side).
Dataset dataset = analysisCtx.indexDatasetMap.get(chosenIndex);
// Determine probe and index subtrees based on chosen index.
@@ -154,15 +145,23 @@
indexSubTree = rightSubTree;
probeSubTree = leftSubTree;
}
-
ILogicalOperator primaryIndexUnnestOp = createSecondaryToPrimaryPlan(joinRef, conditionRef, indexSubTree, probeSubTree,
chosenIndex, analysisCtx, true, true, context);
if (primaryIndexUnnestOp == null) {
return false;
}
-
- // TODO: If there are conditions left, add a new select operator on top.
- joinRef.setValue(primaryIndexUnnestOp);
+ // If there are conditions left, add a new select operator on top.
+ indexSubTree.dataSourceScanRef.setValue(primaryIndexUnnestOp);
+ if (conditionRef.getValue() != null) {
+ SelectOperator topSelect = new SelectOperator(conditionRef);
+ topSelect.getInputs().add(indexSubTree.rootRef);
+ topSelect.setExecutionMode(ExecutionMode.LOCAL);
+ context.computeAndSetTypeEnvironmentForOperator(topSelect);
+ // Replace the original join with the new subtree rooted at the select op.
+ joinRef.setValue(topSelect);
+ } else {
+ joinRef.setValue(indexSubTree.rootRef.getValue());
+ }
return true;
}
@@ -485,6 +484,12 @@
@Override
public boolean exprIsOptimizable(Index index, IOptimizableFuncExpr optFuncExpr) {
+ // If we are optimizing a join, check for the indexed nested-loop join hint.
+ if (optFuncExpr.getNumLogicalVars() == 2) {
+ if (!optFuncExpr.getFuncExpr().getAnnotations().containsKey(IndexedNLJoinExpressionAnnotation.INSTANCE)) {
+ return false;
+ }
+ }
// No additional analysis required for BTrees.
return true;
}
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
index d73bdc1..d6f0279 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
@@ -65,6 +65,7 @@
@Override
public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
throws AlgebricksException {
+ clear();
setMetadataDeclarations(context);
// Match operator pattern and initialize optimizable sub trees.
@@ -118,7 +119,7 @@
boolean res = chosenIndex.first.applyJoinPlanTransformation(joinRef, leftSubTree, rightSubTree,
chosenIndex.second, analysisCtx, context);
if (res) {
- OperatorPropertiesUtil.typeOpRec(opRef, context);
+ OperatorPropertiesUtil.typeOpRec(opRef, context);
}
context.addToDontApplySet(this, join);
return res;
@@ -155,4 +156,10 @@
public Map<FunctionIdentifier, List<IAccessMethod>> getAccessMethods() {
return accessMethods;
}
+
+ private void clear() {
+ joinRef = null;
+ join = null;
+ joinCond = null;
+ }
}
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
index 59b11fc..abfcfb3 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
@@ -70,6 +70,7 @@
@Override
public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
throws AlgebricksException {
+ clear();
setMetadataDeclarations(context);
// Match operator pattern and initialize operator members.
@@ -134,4 +135,10 @@
public Map<FunctionIdentifier, List<IAccessMethod>> getAccessMethods() {
return accessMethods;
}
+
+ private void clear() {
+ selectRef = null;
+ select = null;
+ selectCond = null;
+ }
}
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
index 92acd8b..812bb14 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
@@ -114,10 +114,10 @@
}
public void reset() {
- assignRefs.clear();
- assigns.clear();
root = null;
rootRef = null;
+ assignRefs.clear();
+ assigns.clear();
dataSourceScanRef = null;
dataSourceScan = null;
dataset = null;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
new file mode 100644
index 0000000..9b83718
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
@@ -0,0 +1,48 @@
+/*
+ * Description : Equi joins two datasets, Customers and Orders, based on the customer id.
+ * Given the 'indexnl' hint we expect the join to be transformed
+ * into an indexed nested-loop join using Customers' primary index.
+ * We expect the additional predicates to be put into a select above the
+ * primary index search.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+ number: int32,
+ street: string,
+ city: string
+}
+
+create type CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create dataset Customers(CustomerType) partitioned by key cid;
+create dataset Orders(OrderType) partitioned by key oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-multipred.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.cid /*+ indexnl */ = $o.cid and $c.name < $o.orderstatus and $c.age < $o.cid
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
new file mode 100644
index 0000000..bd21ab0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
@@ -0,0 +1,49 @@
+/*
+ * Description : Equi joins two datasets, DBLP and CSX, based on their title.
+ * DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ * we expect the join to be transformed into an indexed nested-loop join.
+ * We expect the additional predicates to be put into a select above the
+ * primary index search.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as closed {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) partitioned by key id;
+
+create dataset CSX(CSXType) partitioned by key id;
+
+load dataset DBLP
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/dblp-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":")) pre-sorted;
+
+load dataset CSX
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/pub-small/csx-small-id.txt"),("format"="delimited-text"),("delimiter"=":"));
+
+create index title_index on DBLP(title);
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multipred.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where $a.title /*+ indexnl */ = $b.title and $a.authors < $b.authors and $a.misc > $b.misc
+return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-multipred.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-multipred.plan
new file mode 100644
index 0000000..8cadace
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-multipred.plan
@@ -0,0 +1,16 @@
+-- SINK_WRITE |PARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_SELECT |LOCAL|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- BTREE_SEARCH |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |LOCAL|
+ -- STABLE_SORT [$$15(ASC)] |LOCAL|
+ -- HASH_PARTITION_EXCHANGE [$$15] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join-multipred.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join-multipred.plan
new file mode 100644
index 0000000..0edd774
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join-multipred.plan
@@ -0,0 +1,19 @@
+-- SINK_WRITE |PARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_SELECT |LOCAL|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |LOCAL|
+ -- STABLE_SORT [$$24(ASC)] |LOCAL|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/scan-delete-rtree-secondary-index.plan b/asterix-app/src/test/resources/optimizerts/results/scan-delete-rtree-secondary-index.plan
index 3961d95..214cf13 100644
--- a/asterix-app/src/test/resources/optimizerts/results/scan-delete-rtree-secondary-index.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/scan-delete-rtree-secondary-index.plan
@@ -2,7 +2,7 @@
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
-- INDEX_INSERT_DELETE |UNPARTITIONED|
-- ONE_TO_ONE_EXCHANGE |LOCAL|
- -- STABLE_SORT [$$24(ASC), $$25(ASC), $$26(ASC), $$27(ASC)] |LOCAL|
+ -- STABLE_SORT [$$27(ASC), $$28(ASC), $$29(ASC), $$30(ASC)] |LOCAL|
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
-- ASSIGN |UNPARTITIONED|
-- ASSIGN |UNPARTITIONED|
@@ -14,8 +14,8 @@
-- ASSIGN |PARTITIONED|
-- STREAM_PROJECT |PARTITIONED|
-- ASSIGN |PARTITIONED|
- -- STREAM_SELECT |PARTITIONED|
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- -- DATASOURCE_SCAN |PARTITIONED|
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterix-app/src/test/resources/runtimets/queries/index-join/btree-primary-equi-join.aql b/asterix-app/src/test/resources/runtimets/queries/index-join/btree-primary-equi-join.aql
new file mode 100644
index 0000000..1015e82
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/index-join/btree-primary-equi-join.aql
@@ -0,0 +1,57 @@
+/*
+ * Description : Equi joins two datasets, Customers and Orders, based on the customer id.
+ * Given the 'indexnl' hint we expect the join to be transformed
+ * into an indexed nested-loop join using Customers' primary index.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as open {
+ number: int32,
+ street: string,
+ city: string
+}
+
+create type CustomerType as closed {
+ cid: int32,
+ name: string,
+ cashBack: int32,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create type OrderType as open {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float,
+ items: [int32]
+}
+
+create dataset Customers(CustomerType) partitioned by key cid;
+create dataset Orders(OrderType) partitioned by key oid;
+
+load dataset Customers
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+load dataset Orders
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/nontagged/orderData.json"),("format"="adm"));
+
+write output to nc1:"rttest/index-join_btree-primary-equi-join.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.cid /*+ indexnl */ = $o.cid
+order by $c.cid, $o.oid
+return {"cid":$c.cid, "oid": $o.oid}
diff --git a/asterix-app/src/test/resources/runtimets/queries/index-join/btree-secondary-equi-join.aql b/asterix-app/src/test/resources/runtimets/queries/index-join/btree-secondary-equi-join.aql
new file mode 100644
index 0000000..d1e9824
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/index-join/btree-secondary-equi-join.aql
@@ -0,0 +1,47 @@
+/*
+ * Description : Equi joins two datasets, DBLP and CSX, based on their title.
+ * DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ * we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as closed {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) partitioned by key id;
+create dataset CSX(CSXType) partitioned by key id;
+
+load dataset DBLP
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/pub-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":"));
+
+load dataset CSX
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/pub-small/csx-small-id.txt"),("format"="delimited-text"),("delimiter"=":"));
+
+create index title_index on DBLP(authors);
+
+write output to nc1:"rttest/index-join_btree-secondary-equi-join.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where $a.authors /*+ indexnl */ = $b.authors
+order by $a.id, $b.id
+return {"aid": $a.id, "bid": $b.id, "authors": $a.authors}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/index-join/btree-primary-equi-join.adm b/asterix-app/src/test/resources/runtimets/results/index-join/btree-primary-equi-join.adm
new file mode 100644
index 0000000..bc2a454
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/index-join/btree-primary-equi-join.adm
@@ -0,0 +1,3 @@
+{ "cid": 5, "oid": 10 }
+{ "cid": 775, "oid": 10 }
+{ "cid": 775, "oid": 1000 }
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/index-join/btree-secondary-equi-join.adm b/asterix-app/src/test/resources/runtimets/results/index-join/btree-secondary-equi-join.adm
new file mode 100644
index 0000000..7dcc1fc
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/index-join/btree-secondary-equi-join.adm
@@ -0,0 +1,5 @@
+{ "aid": 5, "bid": 98, "authors": "Umeshwar Dayal Eric N. Hanson Jennifer Widom" }
+{ "aid": 34, "bid": 57, "authors": "" }
+{ "aid": 54, "bid": 91, "authors": "Lynn Andrea Stein Henry Lieberman David Ungar" }
+{ "aid": 68, "bid": 57, "authors": "" }
+{ "aid": 69, "bid": 57, "authors": "" }
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index aa4e257..b86e4b3 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -1696,6 +1696,16 @@
</test-group>
<test-group name="index-join">
<test-case FilePath="index-join">
+ <compilation-unit name="btree-primary-equi-join">
+ <output-file compare="Text">btree-primary-equi-join.adm</output-file>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="index-join">
+ <compilation-unit name="btree-secondary-equi-join">
+ <output-file compare="Text">btree-secondary-equi-join.adm</output-file>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="index-join">
<compilation-unit name="inverted-index-ngram-edit-distance">
<output-file compare="Text">inverted-index-ngram-edit-distance.adm</output-file>
</compilation-unit>
@@ -1725,6 +1735,11 @@
<output-file compare="Text">inverted-index-word-jaccard.adm</output-file>
</compilation-unit>
</test-case>
+ <test-case FilePath="index-join">
+ <compilation-unit name="rtree-spatial-intersect-point">
+ <output-file compare="Text">rtree-spatial-intersect-point.adm</output-file>
+ </compilation-unit>
+ </test-case>
</test-group>
<test-group name="index-selection">
<test-case FilePath="index-selection">