[ASTERIXDB-2924][IDX] Multiple INLJs are now recognized.
- user mode changes: no
- storage format changes: no
- interface changes: no
WLOG, two datasets that can be optimized with an INLJ using the same
probe are now recognized and transformed into a double INLJ plan.
Change-Id: I1da6174b07963ec611677d7906c7304c46a2a382
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/12424
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
index b2fdb74..5bfdf1d 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
@@ -126,9 +126,9 @@
}
protected void fillSubTreeIndexExprs(OptimizableOperatorSubTree subTree,
- Map<IAccessMethod, AccessMethodAnalysisContext> analyzedAMs, IOptimizationContext context)
- throws AlgebricksException {
- fillSubTreeIndexExprs(subTree, analyzedAMs, context, false);
+ Map<IAccessMethod, AccessMethodAnalysisContext> analyzedAMs, IOptimizationContext context,
+ boolean isJoinLeftBranch) throws AlgebricksException {
+ fillSubTreeIndexExprs(subTree, analyzedAMs, context, isJoinLeftBranch, false);
}
/**
@@ -137,6 +137,7 @@
* @param subTree
* @param analyzedAMs
* @param context
+ * @param isJoinLeftBranch
* @param isArbitraryFormOfSubtree
* if the given subtree is in an arbitrary form that OptimizableSubTree class can't initialize, we try
* to fill the field type of each variable that is used in the optimizable function expressions.
@@ -146,7 +147,7 @@
*/
protected void fillSubTreeIndexExprs(OptimizableOperatorSubTree subTree,
Map<IAccessMethod, AccessMethodAnalysisContext> analyzedAMs, IOptimizationContext context,
- boolean isArbitraryFormOfSubtree) throws AlgebricksException {
+ boolean isJoinLeftBranch, boolean isArbitraryFormOfSubtree) throws AlgebricksException {
Iterator<Map.Entry<IAccessMethod, AccessMethodAnalysisContext>> amIt = analyzedAMs.entrySet().iterator();
// Check applicability of indexes by access method type.
while (amIt.hasNext()) {
@@ -154,10 +155,13 @@
AccessMethodAnalysisContext amCtx = entry.getValue();
// For the current access method type, map variables to applicable
// indexes.
- if (!isArbitraryFormOfSubtree) {
- fillAllIndexExprs(subTree, amCtx, context);
- } else {
+ if (isArbitraryFormOfSubtree) {
fillVarFieldTypeForOptFuncExprs(subTree, amCtx, context);
+ } else {
+ if (isJoinLeftBranch) {
+ fillVarFieldTypeForOptFuncExprs(subTree, amCtx, context);
+ }
+ fillAllIndexExprs(subTree, amCtx, context);
}
}
}
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
index 199f878..71baa5c 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
@@ -330,12 +330,8 @@
if (continueCheck && checkRightSubTreeMetadata) {
// Map variables to the applicable indexes and find the field name and type.
// Then find the applicable indexes for the variables used in the JOIN condition.
- if (checkLeftSubTreeMetadata) {
- fillSubTreeIndexExprs(leftSubTree, analyzedAMs, context);
- } else {
- fillSubTreeIndexExprs(leftSubTree, analyzedAMs, context, true);
- }
- fillSubTreeIndexExprs(rightSubTree, analyzedAMs, context);
+ fillSubTreeIndexExprs(leftSubTree, analyzedAMs, context, true, !checkLeftSubTreeMetadata);
+ fillSubTreeIndexExprs(rightSubTree, analyzedAMs, context, false);
// Prunes the access methods based on the function expression and access methods.
pruneIndexCandidates(analyzedAMs, context, typeEnvironment);
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
index 4199ece..7ed5994 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
@@ -395,7 +395,7 @@
if (continueCheck) {
// Map variables to the applicable indexes and find the field name and type.
// Then find the applicable indexes for the variables used in the SELECT condition.
- fillSubTreeIndexExprs(subTree, analyzedAMs, context);
+ fillSubTreeIndexExprs(subTree, analyzedAMs, context, false);
// Prune the access methods based on the function expression and access methods.
pruneIndexCandidates(analyzedAMs, context, typeEnvironment);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query1.sqlpp
new file mode 100644
index 0000000..6953d43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query1.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * 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.
+ */
+DROP DATAVERSE TestDataverse IF EXISTS;
+CREATE DATAVERSE TestDataverse;
+USE TestDataverse;
+
+CREATE TYPE GenericType AS { _id: bigint, c : bigint };
+CREATE DATASET IndexDatasetA (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET IndexDatasetB (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET ProbeDataset (GenericType)
+PRIMARY KEY _id;
+
+CREATE INDEX indexA
+ON IndexDatasetA (k : int);
+CREATE INDEX indexB
+ON IndexDatasetB (k : int);
+
+-- Query 1, ternary join w/ primary key on probe.
+FROM ProbeDataset P,
+ IndexDatasetA A,
+ IndexDatasetB B
+WHERE P._id /* +indexnl */ = A.k AND
+ P._id /* +indexnl */ = B.k
+SELECT COUNT(*);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query2.sqlpp
new file mode 100644
index 0000000..344fadd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query2.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * 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.
+ */
+DROP DATAVERSE TestDataverse IF EXISTS;
+CREATE DATAVERSE TestDataverse;
+USE TestDataverse;
+
+CREATE TYPE GenericType AS { _id: bigint, c : bigint };
+CREATE DATASET IndexDatasetA (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET IndexDatasetB (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET ProbeDataset (GenericType)
+PRIMARY KEY _id;
+
+CREATE INDEX indexA
+ON IndexDatasetA (k : int);
+CREATE INDEX indexB
+ON IndexDatasetB (k : int);
+
+-- Query 2, ternary join w/ closed field on probe.
+FROM ProbeDataset P,
+ IndexDatasetA A,
+ IndexDatasetB B
+WHERE P.c /* +indexnl */ = A.k AND
+ P.c /* +indexnl */ = B.k
+SELECT COUNT(*);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query3.sqlpp
new file mode 100644
index 0000000..ce0d563
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query3.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * 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.
+ */
+DROP DATAVERSE TestDataverse IF EXISTS;
+CREATE DATAVERSE TestDataverse;
+USE TestDataverse;
+
+CREATE TYPE GenericType AS { _id: bigint, c : bigint };
+CREATE DATASET IndexDatasetA (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET IndexDatasetB (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET IndexDatasetC (GenericType)
+PRIMARY KEY _id;
+CREATE DATASET ProbeDataset (GenericType)
+PRIMARY KEY _id;
+
+CREATE INDEX indexA
+ON IndexDatasetA (k : int);
+CREATE INDEX indexB
+ON IndexDatasetB (k : int);
+CREATE INDEX indexC
+ON IndexDatasetC (k : int);
+
+-- Query 3, quad-nary join w/ closed field on probe.
+FROM ProbeDataset P,
+ IndexDatasetA A,
+ IndexDatasetB B,
+ IndexDatasetC C
+WHERE P.c /* +indexnl */ = A.k AND
+ P.c /* +indexnl */ = B.k AND
+ P.c /* +indexnl */ = C.k
+SELECT COUNT(*);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query1.plan
new file mode 100644
index 0000000..671d511
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query1.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.IndexDatasetB) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$73(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.indexB) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.IndexDatasetA) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$71(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.indexA) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestDataverse.ProbeDataset) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query2.plan
new file mode 100644
index 0000000..c0f794e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query2.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.IndexDatasetB) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$73(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.indexB) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.IndexDatasetA) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$71(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.indexA) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestDataverse.ProbeDataset) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query3.plan
new file mode 100644
index 0000000..b94c26d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query3.plan
@@ -0,0 +1,49 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetC.IndexDatasetC) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$91(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetC.indexC) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.IndexDatasetB) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$89(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetB.indexB) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.IndexDatasetA) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$87(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestDataverse.IndexDatasetA.indexA) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestDataverse.ProbeDataset) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.1.ddl.sqlpp
new file mode 100644
index 0000000..58d2013
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.1.ddl.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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.
+ */
+DROP DATAVERSE TestDataverse IF EXISTS;
+CREATE DATAVERSE TestDataverse;
+USE TestDataverse;
+
+CREATE TYPE GenericType AS { _id: uuid, c: bigint };
+CREATE DATASET IndexDatasetA (GenericType)
+PRIMARY KEY _id AUTOGENERATED;
+CREATE DATASET IndexDatasetB (GenericType)
+PRIMARY KEY _id AUTOGENERATED;
+CREATE DATASET IndexDatasetC (GenericType)
+PRIMARY KEY _id AUTOGENERATED;
+CREATE DATASET ProbeDataset (GenericType)
+PRIMARY KEY _id AUTOGENERATED;
+
+CREATE INDEX indexA
+ON IndexDatasetA (k: int);
+CREATE INDEX indexB
+ON IndexDatasetB (k: int);
+CREATE INDEX indexC
+ON IndexDatasetC (k: int);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.2.update.sqlpp
new file mode 100644
index 0000000..04b3c42
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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.
+ */
+USE TestDataverse;
+
+INSERT INTO ProbeDataset [
+ { "c": 1 },
+ { "c": 2 },
+ { "c": 3 }
+];
+INSERT INTO IndexDatasetA [
+ { "c": 1, "k": 1 },
+ { "c": 2, "k": 2 },
+ { "c": 3, "k": 4 }
+];
+INSERT INTO IndexDatasetB [
+ { "c": 1, "k": 1 },
+ { "c": 2, "k": 2 },
+ { "c": 3, "k": 4 }
+];
+INSERT INTO IndexDatasetC [
+ { "c": 1, "k": 1 },
+ { "c": 2, "k": 2 },
+ { "c": 3, "k": 4 }
+];
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.3.query.sqlpp
new file mode 100644
index 0000000..9e65e5f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.3.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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.
+ */
+USE TestDataverse;
+
+FROM ProbeDataset P,
+ IndexDatasetA A,
+ IndexDatasetB B
+WHERE P.c /* +indexnl */ = A.k AND
+ P.c /* +indexnl */ = B.k
+SELECT P.c
+ORDER BY P.c;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.4.query.sqlpp
new file mode 100644
index 0000000..f59cd69
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-multiple-join/btree-multiple-join.4.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+USE TestDataverse;
+
+FROM ProbeDataset P,
+ IndexDatasetA A,
+ IndexDatasetB B,
+ IndexDatasetC C
+WHERE P.c /* +indexnl */ = A.k AND
+ P.c /* +indexnl */ = B.k AND
+ P.c /* +indexnl */ = C.k
+SELECT P.c
+ORDER BY P.c;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.1.adm
new file mode 100644
index 0000000..683f403
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.1.adm
@@ -0,0 +1,2 @@
+{ "c": 1 }
+{ "c": 2 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.2.adm
new file mode 100644
index 0000000..683f403
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/btree-multiple-join/btree-multiple-join.2.adm
@@ -0,0 +1,2 @@
+{ "c": 1 }
+{ "c": 2 }
\ No newline at end of file