[ASTERIXDB-2921][IDX] Support for INLJ w/ quantification.
- user mode changes: no
- storage format changes: no
- interface changes: no
INLJ can now be applied to quantification queries if an array-index
exists. This includes INLJ w/ membership, INLJ w/ existential
quantification, and INLJ w/ universal quantification provided that a
non-emptiness clause is also given.
Change-Id: I834f6c397e4c86a5ede960b924e59727e358fa9d
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/12303
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/AccessMethodUtils.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
index 7528ce0..3e3995e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
@@ -1637,11 +1637,25 @@
// Non-index-only plan case: creates (ORDER)? -> (DISTINCT)? -> UNNEST-MAP(PIDX) and return that unnest-map op.
if (!isIndexOnlyPlan) {
- // If we have a join + an array index, we need add the join source PK to the DISTINCT + ORDER.
- List<LogicalVariable> joinPKVars = Collections.emptyList();
+ // If we have a join + an array index, we need add the join source PK(s) to the DISTINCT + ORDER.
+ List<LogicalVariable> joinPKVars;
if (isArrayIndex && probeSubTree != null) {
- joinPKVars = probeSubTree.getDataSourceVariables().subList(0,
- probeSubTree.getDataSourceVariables().size() - 1);
+ joinPKVars = new ArrayList<>();
+ List<LogicalVariable> liveVars = new ArrayList<>();
+ VariableUtilities.getSubplanLocalLiveVariables(probeSubTree.getRoot(), liveVars);
+
+ for (LogicalVariable liveVar : liveVars) {
+ List<LogicalVariable> keyVars = context.findPrimaryKey(liveVar);
+ if (keyVars != null) {
+ for (LogicalVariable keyVar : keyVars) {
+ if (!joinPKVars.contains(keyVar)) {
+ joinPKVars.add(keyVar);
+ }
+ }
+ }
+ }
+ } else {
+ joinPKVars = Collections.emptyList();
}
return createFinalNonIndexOnlySearchPlan(dataset, inputOp, context, !isArrayIndex && sortPrimaryKeys,
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 33f076d..f74c0c8 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
@@ -26,7 +26,10 @@
import org.apache.asterix.metadata.entities.Dataset;
import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.om.functions.BuiltinFunctions;
+import org.apache.asterix.optimizer.rules.subplan.JoinFromSubplanCreator;
import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.mutable.MutableObject;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
import org.apache.hyracks.algebricks.common.utils.Pair;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
@@ -89,6 +92,7 @@
protected final OptimizableOperatorSubTree rightSubTree = new OptimizableOperatorSubTree();
protected IVariableTypeEnvironment typeEnvironment = null;
protected List<Mutable<ILogicalOperator>> afterJoinRefs = null;
+ private final JoinFromSubplanCreator joinFromSubplanCreator = new JoinFromSubplanCreator();
// Registers access methods.
protected static Map<FunctionIdentifier, List<IAccessMethod>> accessMethods = new HashMap<>();
@@ -98,6 +102,7 @@
registerAccessMethod(BTreeAccessMethod.INSTANCE, accessMethods);
registerAccessMethod(RTreeAccessMethod.INSTANCE, accessMethods);
registerAccessMethod(InvertedIndexAccessMethod.INSTANCE, accessMethods);
+ JoinFromSubplanCreator.addOptimizableFunction(BuiltinFunctions.EQ);
}
/**
@@ -294,6 +299,35 @@
analyzedAMs = new HashMap<>();
}
+ // If there exists a SUBPLAN in our plan, and we are conditioning on a variable,
+ // attempt to rewrite this subplan to allow an array-index AM to be introduced.
+ // If successful, this rewrite will transform into an index-nested-loop-join.
+ // This rewrite is to be used for pushing the UNNESTs and ASSIGNs from the subplan into
+ // the index branch and giving the join a condition for this rule to optimize. *No nodes*
+ // from this rewrite will be used beyond this point.
+ if (continueCheck && context.getPhysicalOptimizationConfig().isArrayIndexEnabled()) {
+ joinFromSubplanCreator.findAfterSubplanSelectOperator(afterJoinRefs);
+ AbstractBinaryJoinOperator joinRewrite = joinFromSubplanCreator.createOperator(joinOp, context);
+ boolean transformationResult = false;
+ if (joinRewrite != null) {
+ Mutable<ILogicalOperator> joinRuleInput = new MutableObject<>(joinRewrite);
+ transformationResult = checkAndApplyJoinTransformation(joinRuleInput, context);
+ }
+
+ // Restore our state, so we can look for more INLJ optimizations if this transformation failed.
+ joinOp = joinFromSubplanCreator.restoreBeforeRewrite(afterJoinRefs, context);
+ joinRef = joinRefFromThisOp;
+
+ if (transformationResult) {
+ // Join rewrite was successful. Connect the after-join operators to the index subtree root before
+ // this rewrite. This also avoids performing the secondary index validation step twice.
+ ILogicalOperator lastAfterJoinOp = afterJoinRefs.get(afterJoinRefs.size() - 1).getValue();
+ OperatorManipulationUtil.substituteOpInInput(lastAfterJoinOp, joinOp, joinOp.getInputs().get(1));
+ context.computeAndSetTypeEnvironmentForOperator(lastAfterJoinOp);
+ return true;
+ }
+ }
+
// Checks the condition of JOIN operator is a function call since only function call can be transformed
// using available indexes. If so, initializes the subtree information that will be used later to decide
// whether the given plan is truly optimizable or not.
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 392c504..faabaef 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
@@ -30,7 +30,7 @@
import org.apache.asterix.common.exceptions.ErrorCode;
import org.apache.asterix.metadata.declared.MetadataProvider;
import org.apache.asterix.metadata.entities.Index;
-import org.apache.asterix.optimizer.rules.util.SelectInSubplanBranchCreator;
+import org.apache.asterix.optimizer.rules.subplan.SelectFromSubplanCreator;
import org.apache.commons.lang3.mutable.Mutable;
import org.apache.commons.lang3.mutable.MutableObject;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
@@ -123,7 +123,7 @@
protected IVariableTypeEnvironment typeEnvironment = null;
protected final OptimizableOperatorSubTree subTree = new OptimizableOperatorSubTree();
protected List<Mutable<ILogicalOperator>> afterSelectRefs = null;
- private final SelectInSubplanBranchCreator selectInSubplanBranchCreator = new SelectInSubplanBranchCreator();
+ private final SelectFromSubplanCreator selectFromSubplanCreator = new SelectFromSubplanCreator();
// Register access methods.
protected static Map<FunctionIdentifier, List<IAccessMethod>> accessMethods = new HashMap<>();
@@ -134,7 +134,7 @@
registerAccessMethod(InvertedIndexAccessMethod.INSTANCE, accessMethods);
registerAccessMethod(ArrayBTreeAccessMethod.INSTANCE, accessMethods);
for (Pair<FunctionIdentifier, Boolean> f : ArrayBTreeAccessMethod.INSTANCE.getOptimizableFunctions()) {
- SelectInSubplanBranchCreator.addOptimizableFunction(f.first);
+ SelectFromSubplanCreator.addOptimizableFunction(f.first);
}
}
@@ -383,15 +383,20 @@
// If successful, this will create a non-index only plan that replaces the subplan's
// DATA-SCAN with a PIDX SEARCH <- DISTINCT <- ORDER <- SIDX SEARCH.
if (continueCheck && context.getPhysicalOptimizationConfig().isArrayIndexEnabled()) {
- SelectOperator selectRewrite = selectInSubplanBranchCreator.createSelect(selectOp, context);
- if (selectRewrite != null
- && checkAndApplyTheSelectTransformation(new MutableObject<>(selectRewrite), context)) {
- return true;
+ SelectOperator selectRewrite = selectFromSubplanCreator.createOperator(selectOp, context);
+ boolean transformationResult = false;
+ if (selectRewrite != null) {
+ Mutable<ILogicalOperator> selectRuleInput = new MutableObject<>(selectRewrite);
+ transformationResult = checkAndApplyTheSelectTransformation(selectRuleInput, context);
+ }
- } else {
- // If this optimization or temp-branch creation was not successful, restore our state.
- selectRef = selectRefFromThisOp;
- selectOp = selectInSubplanBranchCreator.getOriginalSelect();
+ // Restore our state, so we can look for more optimizations if this transformation failed.
+ selectOp = selectFromSubplanCreator.restoreBeforeRewrite(null, null);
+ selectRef = selectRefFromThisOp;
+
+ if (transformationResult) {
+ // Rewrite was successful. Exit early.
+ return true;
}
}
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/SelectInSubplanBranchCreator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/AbstractOperatorFromSubplanCreator.java
similarity index 81%
rename from asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/SelectInSubplanBranchCreator.java
rename to asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/AbstractOperatorFromSubplanCreator.java
index ffe1fd4..9fd14cc 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/SelectInSubplanBranchCreator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/AbstractOperatorFromSubplanCreator.java
@@ -16,11 +16,12 @@
* specific language governing permissions and limitations
* under the License.
*/
-package org.apache.asterix.optimizer.rules.util;
+package org.apache.asterix.optimizer.rules.subplan;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
+import java.util.Set;
import org.apache.asterix.common.exceptions.CompilationException;
import org.apache.asterix.common.exceptions.ErrorCode;
@@ -33,6 +34,7 @@
import org.apache.commons.lang3.mutable.Mutable;
import org.apache.commons.lang3.mutable.MutableObject;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
@@ -51,117 +53,33 @@
import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
import org.apache.hyracks.algebricks.core.algebra.util.OperatorManipulationUtil;
import org.apache.hyracks.api.exceptions.SourceLocation;
-/**
- * For use in writing a "throwaway" branch which removes NTS and subplan operators. The result of this invocation is to
- * be given to the {@code IntroduceSelectAccessMethodRule} to check if an array index can be used.
- * <br>
- * If we are given the pattern (an existential query):
- * <pre>
- * SELECT_1(some variable)
- * SUBPLAN_1 -------------------------------|
- * (parent branch input) AGGREGATE(NON-EMPTY-STREAM)
- * SELECT_2(some predicate)
- * (UNNEST/ASSIGN)*
- * UNNEST(on variable)
- * NESTED-TUPLE-SOURCE
- * </pre>
- * We return the following branch:
- * <pre>
- * SELECT_2(some predicate)
- * (UNNEST/ASSIGN)*
- * UNNEST(on variable)
- * (parent branch input)
- * </pre>
- *
- * If we are given the pattern (a universal query):
- * <pre>
- * SELECT_1(some variable AND array is not empty)
- * SUBPLAN_1 -------------------------------|
- * (parent branch input) AGGREGATE(EMPTY-STREAM)
- * SELECT_2(NOT(IF-MISSING-OR-NULL(some optimizable predicate)))
- * (UNNEST/ASSIGN)*
- * UNNEST(on variable)
- * NESTED-TUPLE-SOURCE
- * </pre>
- * We return the following branch:
- * <pre>
- * SELECT_2(some optimizable predicate) <--- removed the NOT(IF-MISSING-OR-NULL(...))!
- * (UNNEST/ASSIGN)*
- * UNNEST(on variable)
- * (parent branch input)
- * </pre>
- *
- * In the case of nested-subplans, we return a copy of the innermost SELECT followed by all relevant UNNEST/ASSIGNs.
- */
-public class SelectInSubplanBranchCreator {
- private final static List<IAlgebricksConstantValue> zerosAsAsterixConstants =
+abstract public class AbstractOperatorFromSubplanCreator<T> {
+ private final static List<IAlgebricksConstantValue> ZEROS_AS_ASTERIX_CONSTANTS =
Arrays.asList(new IAlgebricksConstantValue[] { new AsterixConstantValue(new AInt64(0)),
new AsterixConstantValue(new AInt32(0)), new AsterixConstantValue(new AInt16((short) 0)),
new AsterixConstantValue(new AInt8((byte) 0)) });
- private final static List<FunctionIdentifier> optimizableFunctions = new ArrayList<>();
+ private Set<FunctionIdentifier> optimizableFunctions;
private IOptimizationContext context;
private SourceLocation sourceLocation;
- private SelectOperator originalSelectRoot;
- /**
- * Add an optimizable function from an access method that can take advantage of this throwaway branch rewrite.
- */
- public static void addOptimizableFunction(FunctionIdentifier functionIdentifier) {
- optimizableFunctions.add(functionIdentifier);
- }
+ abstract public T createOperator(T originalOperatorRef, IOptimizationContext context) throws AlgebricksException;
- /**
- * Create a new branch to match that of the form:
- *
- * <pre>
- * SELECT (...)
- * (UNNEST/ASSIGN)*
- * UNNEST
- * ...
- * </pre>
- *
- * Operators are *created* here, rather than just reconnected from the original branch.
- */
- public SelectOperator createSelect(SelectOperator originalSelect, IOptimizationContext context)
- throws AlgebricksException {
- // Reset our context.
- this.sourceLocation = originalSelect.getSourceLocation();
- this.originalSelectRoot = originalSelect;
+ abstract public T restoreBeforeRewrite(List<Mutable<ILogicalOperator>> afterOperatorRefs,
+ IOptimizationContext context) throws AlgebricksException;
+
+ protected void reset(SourceLocation sourceLocation, IOptimizationContext context,
+ Set<FunctionIdentifier> optimizableFunctions) {
+ this.optimizableFunctions = optimizableFunctions;
+ this.sourceLocation = sourceLocation;
this.context = context;
-
- // We expect a) a SUBPLAN as input to this SELECT, and b) our SELECT to be conditioning on a variable.
- if (!originalSelect.getInputs().get(0).getValue().getOperatorTag().equals(LogicalOperatorTag.SUBPLAN)
- || !originalSelect.getCondition().getValue().getExpressionTag().equals(LogicalExpressionTag.VARIABLE)) {
- return null;
- }
- LogicalVariable originalSelectVar =
- ((VariableReferenceExpression) originalSelect.getCondition().getValue()).getVariableReference();
-
- // Additionally, verify that the subplan does not produce any other variable other than the SELECT var above.
- SubplanOperator subplanOperator = (SubplanOperator) originalSelect.getInputs().get(0).getValue();
- List<LogicalVariable> subplanProducedVars = new ArrayList<>();
- VariableUtilities.getProducedVariables(subplanOperator, subplanProducedVars);
- if (subplanProducedVars.size() != 1 || !subplanProducedVars.get(0).equals(originalSelectVar)) {
- return null;
- }
-
- return traverseSubplanBranch(subplanOperator, null);
}
- /**
- * To undo this process is to return what was passed to us at {@code createSelect} time.
- */
- public SelectOperator getOriginalSelect() {
- return originalSelectRoot;
- }
-
- private SelectOperator traverseSubplanBranch(SubplanOperator subplanOperator, ILogicalOperator parentInput)
- throws AlgebricksException {
+ protected Pair<SelectOperator, UnnestOperator> traverseSubplanBranch(SubplanOperator subplanOperator,
+ ILogicalOperator parentInput) throws AlgebricksException {
// We only expect one plan, and one root.
if (subplanOperator.getNestedPlans().size() > 1
|| subplanOperator.getNestedPlans().get(0).getRoots().size() > 1) {
@@ -177,14 +95,18 @@
workingSubplanRootAsAggregate = (AggregateOperator) workingSubplanRoot;
// Try to find a SELECT that we can optimize (i.e. has a function call).
+ Pair<SelectOperator, UnnestOperator> traversalOutput;
SelectOperator optimizableSelect = null;
for (Mutable<ILogicalOperator> opInput : workingSubplanRoot.getInputs()) {
- ILogicalOperator subplanOrSelect = findSubplanOrOptimizableSelect(opInput.getValue());
+ ILogicalOperator subplanOrSelect = findSubplanOrOptimizableSelect(opInput.getValue(), optimizableFunctions);
if (subplanOrSelect == null) {
return null;
} else if (subplanOrSelect.getOperatorTag().equals(LogicalOperatorTag.SUBPLAN)) {
- optimizableSelect = traverseSubplanBranch((SubplanOperator) subplanOrSelect, opInput.getValue());
+ traversalOutput = traverseSubplanBranch((SubplanOperator) subplanOrSelect, opInput.getValue());
+ if (traversalOutput != null) {
+ optimizableSelect = traversalOutput.first;
+ }
} else {
optimizableSelect = (SelectOperator) subplanOrSelect;
@@ -287,7 +209,7 @@
bottommostNewUnnest.getInputs().addAll(subplanOperator.getInputs());
OperatorManipulationUtil.computeTypeEnvironmentBottomUp(newSelectOperator, context);
- return newSelectOperator;
+ return new Pair<>(newSelectOperator, bottommostNewUnnest);
}
private boolean isUniversalQuantification(AggregateOperator workingSubplanRoot) throws CompilationException {
@@ -355,7 +277,7 @@
return usedVariables.contains(arrayVariable)
&& secondArg.getExpressionTag().equals(LogicalExpressionTag.CONSTANT)
- && zerosAsAsterixConstants.contains(((ConstantExpression) secondArg).getValue());
+ && ZEROS_AS_ASTERIX_CONSTANTS.contains(((ConstantExpression) secondArg).getValue());
}
}
@@ -373,7 +295,7 @@
return usedVariables.contains(arrayVariable)
&& firstArg.getExpressionTag().equals(LogicalExpressionTag.CONSTANT)
- && zerosAsAsterixConstants.contains(((ConstantExpression) firstArg).getValue());
+ && ZEROS_AS_ASTERIX_CONSTANTS.contains(((ConstantExpression) firstArg).getValue());
}
}
@@ -413,7 +335,8 @@
}
}
- private ILogicalOperator findSubplanOrOptimizableSelect(ILogicalOperator operator) {
+ private ILogicalOperator findSubplanOrOptimizableSelect(ILogicalOperator operator,
+ Set<FunctionIdentifier> optimizableFunctions) {
// We are trying to find a SELECT operator with an optimizable function call.
if (operator.getOperatorTag().equals(LogicalOperatorTag.SELECT)) {
SelectOperator selectOperator = (SelectOperator) operator;
@@ -435,11 +358,11 @@
// Inside the NOT(IF-MISSING-OR-NULL(...)) is an optimizable function. Return this.
ScalarFunctionCallExpression ifMissingOrNullExpr = (ScalarFunctionCallExpression) notCondExpr;
ILogicalExpression finalExpr = ifMissingOrNullExpr.getArguments().get(0).getValue();
- if (doesExpressionContainOptimizableFunction(finalExpr)) {
+ if (doesExpressionContainOptimizableFunction(finalExpr, optimizableFunctions)) {
return selectOperator;
}
- } else if (doesExpressionContainOptimizableFunction(selectCondExpr)) {
+ } else if (doesExpressionContainOptimizableFunction(selectCondExpr, optimizableFunctions)) {
// We have an optimizable function. Return this.
return selectOperator;
@@ -455,18 +378,19 @@
return null;
} else {
- return findSubplanOrOptimizableSelect(operator.getInputs().get(0).getValue());
+ return findSubplanOrOptimizableSelect(operator.getInputs().get(0).getValue(), optimizableFunctions);
}
}
- private boolean doesExpressionContainOptimizableFunction(ILogicalExpression inputExpr) {
+ private boolean doesExpressionContainOptimizableFunction(ILogicalExpression inputExpr,
+ Set<FunctionIdentifier> optimizableFunctions) {
if (!inputExpr.getExpressionTag().equals(LogicalExpressionTag.FUNCTION_CALL)) {
return false;
}
// Check if the input expression itself is an optimizable function.
ScalarFunctionCallExpression inputExprAsFunc = (ScalarFunctionCallExpression) inputExpr;
- if (isFunctionOptimizable(inputExprAsFunc)) {
+ if (isFunctionOptimizable(inputExprAsFunc, optimizableFunctions)) {
return true;
}
@@ -476,7 +400,8 @@
for (Mutable<ILogicalExpression> mutableConjunct : conjuncts) {
ILogicalExpression workingConjunct = mutableConjunct.getValue();
if (workingConjunct.getExpressionTag().equals(LogicalExpressionTag.FUNCTION_CALL)
- && (isFunctionOptimizable((ScalarFunctionCallExpression) workingConjunct))) {
+ && (isFunctionOptimizable((ScalarFunctionCallExpression) workingConjunct,
+ optimizableFunctions))) {
return true;
}
}
@@ -485,7 +410,8 @@
return false;
}
- private boolean isFunctionOptimizable(ScalarFunctionCallExpression inputExpr) {
+ private boolean isFunctionOptimizable(ScalarFunctionCallExpression inputExpr,
+ Set<FunctionIdentifier> optimizableFunctions) {
if (inputExpr.getFunctionIdentifier().equals(BuiltinFunctions.GT)) {
// Avoid the GT(LEN(array-field), 0) function.
ILogicalExpression gtExpr = inputExpr.getArguments().get(0).getValue();
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/JoinFromSubplanCreator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/JoinFromSubplanCreator.java
new file mode 100644
index 0000000..0e32703
--- /dev/null
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/JoinFromSubplanCreator.java
@@ -0,0 +1,244 @@
+/*
+ * 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.asterix.optimizer.rules.subplan;
+
+import java.util.ArrayDeque;
+import java.util.ArrayList;
+import java.util.Deque;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.mutable.MutableObject;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractBinaryJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.InnerJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
+import org.apache.hyracks.algebricks.core.algebra.util.OperatorManipulationUtil;
+
+/**
+ * For use in writing a "throwaway" branch which removes NTS and subplan operators. The result of this invocation is to
+ * be given to the {@code IntroduceJoinAccessMethodRule} to check if an array index can be used.
+ * <br>
+ * If we are given the pattern (an existential quantification over a cross product):
+ * <pre>
+ * SELECT_1(some variable)
+ * SUBPLAN_1 -----------------------|
+ * | AGGREGATE(NON-EMPTY-STREAM)
+ * | SELECT_2(some predicate)
+ * | (UNNEST/ASSIGN)*
+ * | UNNEST(on variable)
+ * | NESTED-TUPLE-SOURCE
+ * JOIN(true)
+ * | |----------------- (potential) index branch ...
+ * |----------------- probe branch ...
+ * </pre>
+ * We return the following branch:
+ * <pre>
+ * JOIN(some predicate from SELECT_2)
+ * | |----------------- (UNNEST/ASSIGN)*
+ * | UNNEST(on variable)
+ * | (potential) index branch ...
+ * |----------------- probe branch ...
+ * </pre>
+ *
+ * If we are given the pattern (a universal quantification over a cross product):
+ * <pre>
+ * SELECT_1(some variable AND array is not empty)
+ * SUBPLAN_1 -----------------------|
+ * | AGGREGATE(EMPTY-STREAM)
+ * | SELECT_2(NOT(IF-MISSING-OR-NULL(some optimizable predicate)))
+ * | (UNNEST/ASSIGN)*
+ * | UNNEST(on variable)
+ * | NESTED-TUPLE-SOURCE
+ * JOIN(true)
+ * | |----------------- (potential) index branch ...
+ * |----------------- probe branch ...
+ * </pre>
+ * We return the following branch:
+ * <pre>
+ * JOIN(some optimizable predicate) <--- removed the NOT(IF-MISSING-OR-NULL(...))!
+ * | |----------------- (UNNEST/ASSIGN)*
+ * | UNNEST(on variable)
+ * | (potential) index branch ...
+ * |----------------- probe branch ...
+ * </pre>
+ *
+ * In the case of nested-subplans, we return a copy of the innermost SELECT followed by all relevant UNNEST/ASSIGNs.
+ */
+public class JoinFromSubplanCreator extends AbstractOperatorFromSubplanCreator<AbstractBinaryJoinOperator> {
+ private final static Set<FunctionIdentifier> optimizableFunctions = new HashSet<>();
+ private final Deque<JoinFromSubplanContext> contextStack = new ArrayDeque<>();
+
+ /**
+ * Add an optimizable function from an access method that can take advantage of this throwaway branch.
+ */
+ public static void addOptimizableFunction(FunctionIdentifier functionIdentifier) {
+ optimizableFunctions.add(functionIdentifier);
+ }
+
+ /**
+ * The subplan we want to push to the JOIN operator is located *above/after* the JOIN itself.
+ */
+ public void findAfterSubplanSelectOperator(List<Mutable<ILogicalOperator>> afterJoinRefs)
+ throws AlgebricksException {
+ JoinFromSubplanContext joinContext = new JoinFromSubplanContext();
+ contextStack.push(joinContext);
+
+ // Minimally, we need to have a DISTRIBUTE <- SELECT <- SUBPLAN.
+ if (afterJoinRefs.size() < 3) {
+ return;
+ }
+
+ // We expect a) the operator immediately above to be a SUBPLAN, and b) the next operator above to be a SELECT.
+ Mutable<ILogicalOperator> afterJoinOpRef1 = afterJoinRefs.get(afterJoinRefs.size() - 1);
+ Mutable<ILogicalOperator> afterJoinOpRef2 = afterJoinRefs.get(afterJoinRefs.size() - 2);
+ Mutable<ILogicalOperator> afterJoinOpRef3 = afterJoinRefs.get(afterJoinRefs.size() - 3);
+ ILogicalOperator afterJoinOp1 = afterJoinOpRef1.getValue();
+ ILogicalOperator afterJoinOp2 = afterJoinOpRef2.getValue();
+ ILogicalOperator afterJoinOp3 = afterJoinOpRef3.getValue();
+ if (!afterJoinOp1.getOperatorTag().equals(LogicalOperatorTag.SUBPLAN)
+ || !afterJoinOp2.getOperatorTag().equals(LogicalOperatorTag.SELECT)) {
+ return;
+ }
+
+ // Additionally, verify that our SELECT is conditioning on a variable.
+ joinContext.selectAfterSubplan = (SelectOperator) afterJoinOp2;
+ if (!joinContext.selectAfterSubplan.getCondition().getValue().getExpressionTag()
+ .equals(LogicalExpressionTag.VARIABLE)) {
+ return;
+ }
+
+ // Modify the given after-join operators. We will reconnect these after the join-rule transformation.
+ joinContext.removedAfterJoinOperators = new ArrayList<>();
+ joinContext.removedAfterJoinOperators.add(afterJoinOpRef2);
+ joinContext.removedAfterJoinOperators.add(afterJoinOpRef1);
+ afterJoinRefs.remove(afterJoinOpRef2);
+ afterJoinRefs.remove(afterJoinOpRef1);
+
+ // Connect our inputs here. We will compute the type environment for this copy in {@code createOperator}.
+ joinContext.afterJoinOpForRewrite = OperatorManipulationUtil.deepCopy(afterJoinOp3);
+ joinContext.afterJoinOpForRewrite.getInputs().clear();
+ joinContext.afterJoinOpForRewrite.getInputs().addAll(afterJoinOp3.getInputs());
+ }
+
+ /**
+ * Create a new branch to match that of the form:
+ *
+ * <pre>
+ * JOIN(...)
+ * | |----------------- (UNNEST/ASSIGN)*
+ * | UNNEST
+ * | (potential) index branch ...
+ * |----------------- probe branch ...
+ * </pre>
+ * <p>
+ * Operators are *created* here, rather than just reconnected from the original branch.
+ */
+ @Override
+ public AbstractBinaryJoinOperator createOperator(AbstractBinaryJoinOperator originalOperator,
+ IOptimizationContext context) throws AlgebricksException {
+ // Reset our context.
+ this.reset(originalOperator.getSourceLocation(), context, optimizableFunctions);
+ JoinFromSubplanContext joinContext = contextStack.getFirst();
+ joinContext.originalJoinRoot = originalOperator;
+ if (joinContext.removedAfterJoinOperators == null) {
+ return null;
+ }
+
+ // Verify that the subplan does not produce any other variable other than the SELECT var from its parent.
+ SelectOperator selectAfterSubplan = joinContext.selectAfterSubplan;
+ LogicalVariable originalSelectVar =
+ ((VariableReferenceExpression) selectAfterSubplan.getCondition().getValue()).getVariableReference();
+ SubplanOperator subplanOperator = (SubplanOperator) selectAfterSubplan.getInputs().get(0).getValue();
+ List<LogicalVariable> subplanProducedVars = new ArrayList<>();
+ VariableUtilities.getProducedVariables(subplanOperator, subplanProducedVars);
+ if (subplanProducedVars.size() != 1 || !subplanProducedVars.get(0).equals(originalSelectVar)) {
+ return null;
+ }
+
+ // Traverse our subplan and generate a SELECT branch if applicable.
+ Pair<SelectOperator, UnnestOperator> traversalOutput =
+ traverseSubplanBranch(subplanOperator, originalOperator.getInputs().get(1).getValue());
+ if (traversalOutput == null) {
+ return null;
+ }
+
+ // We have successfully generated a SELECT branch. Create the new JOIN operator.
+ if (originalOperator.getOperatorTag().equals(LogicalOperatorTag.INNERJOIN)) {
+ joinContext.newJoinRoot = new InnerJoinOperator(traversalOutput.first.getCondition());
+
+ } else { // originalOperator.getOperatorTag().equals(LogicalOperatorTag.LEFTOUTERJOIN)
+ joinContext.newJoinRoot = new LeftOuterJoinOperator(traversalOutput.first.getCondition());
+ }
+ joinContext.newJoinRoot.getInputs().add(0, originalOperator.getInputs().get(0));
+
+ // Create the index join branch.
+ traversalOutput.second.getInputs().clear();
+ traversalOutput.second.getInputs().add(originalOperator.getInputs().get(1));
+ context.computeAndSetTypeEnvironmentForOperator(traversalOutput.second);
+ joinContext.newJoinRoot.getInputs().add(1, traversalOutput.first.getInputs().get(0));
+ context.computeAndSetTypeEnvironmentForOperator(joinContext.newJoinRoot);
+
+ // Reconnect our after-join operator to our new join.
+ OperatorManipulationUtil.substituteOpInInput(joinContext.afterJoinOpForRewrite,
+ joinContext.removedAfterJoinOperators.get(0).getValue(), new MutableObject<>(joinContext.newJoinRoot));
+ context.computeAndSetTypeEnvironmentForOperator(joinContext.afterJoinOpForRewrite);
+ return joinContext.newJoinRoot;
+ }
+
+ /**
+ * To undo this process is to return what was passed to us at {@code createOperator} time. If we removed any
+ * after-join references, add them back in the order they were originally given.
+ */
+ @Override
+ public AbstractBinaryJoinOperator restoreBeforeRewrite(List<Mutable<ILogicalOperator>> afterOperatorRefs,
+ IOptimizationContext context) throws AlgebricksException {
+ JoinFromSubplanContext joinContext = contextStack.pop();
+ if (joinContext.removedAfterJoinOperators != null) {
+ afterOperatorRefs.addAll(joinContext.removedAfterJoinOperators);
+ }
+
+ return joinContext.originalJoinRoot;
+ }
+
+ /**
+ * All state associated with a single call of {@code createOperator}.
+ */
+ private static class JoinFromSubplanContext {
+ private List<Mutable<ILogicalOperator>> removedAfterJoinOperators;
+ private AbstractBinaryJoinOperator originalJoinRoot;
+ private AbstractBinaryJoinOperator newJoinRoot;
+ private SelectOperator selectAfterSubplan;
+ private ILogicalOperator afterJoinOpForRewrite;
+ }
+}
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SelectFromSubplanCreator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SelectFromSubplanCreator.java
new file mode 100644
index 0000000..63b5692
--- /dev/null
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/SelectFromSubplanCreator.java
@@ -0,0 +1,146 @@
+/*
+ * 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.asterix.optimizer.rules.subplan;
+
+import java.util.ArrayDeque;
+import java.util.ArrayList;
+import java.util.Deque;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
+
+/**
+ * For use in writing a "throwaway" branch which removes NTS and subplan operators. The result of this invocation is to
+ * be given to the {@code IntroduceSelectAccessMethodRule} to check if an array index can be used.
+ * <br>
+ * If we are given the pattern (an existential quantification query):
+ * <pre>
+ * SELECT_1(some variable)
+ * SUBPLAN_1 -------------------------------|
+ * (parent branch input) AGGREGATE(NON-EMPTY-STREAM)
+ * SELECT_2(some predicate)
+ * (UNNEST/ASSIGN)*
+ * UNNEST(on variable)
+ * NESTED-TUPLE-SOURCE
+ * </pre>
+ * We return the following branch:
+ * <pre>
+ * SELECT_2(some predicate)
+ * (UNNEST/ASSIGN)*
+ * UNNEST(on variable)
+ * (parent branch input)
+ * </pre>
+ *
+ * If we are given the pattern (a universal quantification query):
+ * <pre>
+ * SELECT_1(some variable AND array is not empty)
+ * SUBPLAN_1 -------------------------------|
+ * (parent branch input) AGGREGATE(EMPTY-STREAM)
+ * SELECT_2(NOT(IF-MISSING-OR-NULL(some optimizable predicate)))
+ * (UNNEST/ASSIGN)*
+ * UNNEST(on variable)
+ * NESTED-TUPLE-SOURCE
+ * </pre>
+ * We return the following branch:
+ * <pre>
+ * SELECT_2(some optimizable predicate) <--- removed the NOT(IF-MISSING-OR-NULL(...))!
+ * (UNNEST/ASSIGN)*
+ * UNNEST(on variable)
+ * (parent branch input)
+ * </pre>
+ *
+ * In the case of nested-subplans, we return a copy of the innermost SELECT followed by all relevant UNNEST/ASSIGNs.
+ */
+public class SelectFromSubplanCreator extends AbstractOperatorFromSubplanCreator<SelectOperator> {
+ private final static Set<FunctionIdentifier> optimizableFunctions = new HashSet<>();
+ private final Deque<SelectOperator> selectRootStack = new ArrayDeque<>();
+
+ /**
+ * Add an optimizable function from an access method that can take advantage of this throwaway branch.
+ */
+ public static void addOptimizableFunction(FunctionIdentifier functionIdentifier) {
+ optimizableFunctions.add(functionIdentifier);
+ }
+
+ /**
+ * Create a new branch to match that of the form:
+ *
+ * <pre>
+ * SELECT (...)
+ * (UNNEST/ASSIGN)*
+ * UNNEST
+ * ...
+ * </pre>
+ *
+ * Operators are *created* here, rather than just reconnected from the original branch.
+ */
+ @Override
+ public SelectOperator createOperator(SelectOperator originalOperator, IOptimizationContext context)
+ throws AlgebricksException {
+ // Reset our context.
+ selectRootStack.push(originalOperator);
+ reset(originalOperator.getSourceLocation(), context, optimizableFunctions);
+
+ // We expect a) a SUBPLAN as input to this SELECT, and b) our SELECT to be conditioning on a variable.
+ if (!originalOperator.getInputs().get(0).getValue().getOperatorTag().equals(LogicalOperatorTag.SUBPLAN)
+ || !originalOperator.getCondition().getValue().getExpressionTag()
+ .equals(LogicalExpressionTag.VARIABLE)) {
+ return null;
+ }
+ LogicalVariable originalSelectVar =
+ ((VariableReferenceExpression) originalOperator.getCondition().getValue()).getVariableReference();
+
+ // Additionally, verify that the subplan does not produce any other variable other than the SELECT var above.
+ SubplanOperator subplanOperator = (SubplanOperator) originalOperator.getInputs().get(0).getValue();
+ List<LogicalVariable> subplanProducedVars = new ArrayList<>();
+ VariableUtilities.getProducedVariables(subplanOperator, subplanProducedVars);
+ if (subplanProducedVars.size() != 1 || !subplanProducedVars.get(0).equals(originalSelectVar)) {
+ return null;
+ }
+
+ // Traverse our subplan and generate a SELECT branch if applicable.
+ Pair<SelectOperator, UnnestOperator> traversalOutput = traverseSubplanBranch(subplanOperator, null);
+ return (traversalOutput == null) ? null : traversalOutput.first;
+ }
+
+ /**
+ * To undo this process is to return what was passed to us at {@code createOperator} time. We do not touch the
+ * operators after the SELECT.
+ */
+ @Override
+ public SelectOperator restoreBeforeRewrite(List<Mutable<ILogicalOperator>> afterOperatorRefs,
+ IOptimizationContext context) throws AlgebricksException {
+ return selectRootStack.pop();
+ }
+}
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query1.sqlpp
new file mode 100644
index 0000000..a03397d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query1.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ dates: [string]
+ };
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE M.date /*+ indexnl */ IN C.dates AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query2.sqlpp
new file mode 100644
index 0000000..d3ca985
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query2.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ dates: [string]
+ };
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg" AND
+ ( SOME D IN C.dates
+ SATISFIES M.date /*+ indexnl */ = D )
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query3.sqlpp
new file mode 100644
index 0000000..3b0f70f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query3.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ dates: [string]
+ };
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg" AND
+ LEN(C.dates) > 0 AND
+ ( EVERY D IN C.dates
+ SATISFIES M.date /*+ indexnl */ = D )
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query4.sqlpp
new file mode 100644
index 0000000..5607661
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-1/query4.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ dates: [string]
+ };
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckinA(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinB(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinADates ON YelpCheckinA (UNNEST dates);
+CREATE INDEX IdxYelpCheckinBDates ON YelpCheckinB (UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A, YelpCheckinB B
+WHERE M.date /*+ indexnl */ IN A.dates AND
+ M.date /*+ indexnl */ IN B.dates
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query1.sqlpp
new file mode 100644
index 0000000..d7e8956
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query1.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: {
+ dates: [string],
+ times: [string]
+ }
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times.dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE M.date /*+ indexnl */ IN C.checkin_times.dates AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query2.sqlpp
new file mode 100644
index 0000000..d612e71
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query2.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: {
+ dates: [string],
+ times: [string]
+ }
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times.dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg" AND
+ LEN(C.checkin_times.dates) > 0 AND
+ ( EVERY D IN C.checkin_times.dates
+ SATISFIES M.date /*+ indexnl */ = D )
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query3.sqlpp
new file mode 100644
index 0000000..7270986
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-2/query3.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: {
+ dates: [string],
+ times: [string]
+ }
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+ };
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times.dates);
+
+-- This should NOT be optimized, there is no non-array-emptiness clause.
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg" AND
+ ( EVERY D IN C.checkin_times.dates
+ SATISFIES M.date /*+ indexnl */ = D )
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query1.sqlpp
new file mode 100644
index 0000000..fd3a846
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query1.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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ date: string,
+ time: string
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times SELECT date);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( SOME D IN C.checkin_times
+ SATISFIES D.date /*+ indexnl */ = M.date ) AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query2.sqlpp
new file mode 100644
index 0000000..09b187e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-3/query2.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ date: string,
+ time: string
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times SELECT date);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( EVERY D IN C.checkin_times
+ SATISFIES D.date /*+ indexnl */ = M.date ) AND
+ LEN (C.checkin_times) > 0 AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query1.sqlpp
new file mode 100644
index 0000000..bbafaa9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query1.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ checkin_times_id: int,
+ dates: [string],
+ times: [string]
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( SOME CT IN C.checkin_times
+ SATISFIES ( SOME D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) ) AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query2.sqlpp
new file mode 100644
index 0000000..d7eccc3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query2.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ checkin_times_id: int,
+ dates: [string],
+ times: [string]
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( EVERY CT IN C.checkin_times
+ SATISFIES ( SOME D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) ) AND
+ LEN (C.checkin_times) > 0 AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query3.sqlpp
new file mode 100644
index 0000000..39e2672
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query3.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ checkin_times_id: int,
+ dates: [string],
+ times: [string]
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST checkin_times UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( EVERY CT IN C.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (C.checkin_times) > 0 AND
+ C.business_id = "--Ni3oJ4VOqfOEu7Sj2Vzg"
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query4.sqlpp
new file mode 100644
index 0000000..6f555f9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-4/query4.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ checkin_times_id: int,
+ dates: [string],
+ times: [string]
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckinA(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinB(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDatesA ON YelpCheckinA (UNNEST checkin_times UNNEST dates);
+CREATE INDEX IdxYelpCheckinDatesB ON YelpCheckinB (UNNEST checkin_times UNNEST dates);
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A, YelpCheckinB B
+WHERE ( EVERY CT IN A.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (A.checkin_times) > 0 AND
+ ( EVERY CT IN B.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (B.checkin_times) > 0
+SELECT COUNT(*);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query1.plan
new file mode 100644
index 0000000..7306c4d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query1.plan
@@ -0,0 +1,39 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$61(ASC), $$53(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-1/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query2.plan
new file mode 100644
index 0000000..cb70d4f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query2.plan
@@ -0,0 +1,39 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$69(ASC), $$61(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-1/query3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query3.plan
new file mode 100644
index 0000000..b81bf80
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query3.plan
@@ -0,0 +1,39 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$73(ASC), $$64(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-1/query4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query4.plan
new file mode 100644
index 0000000..39ff34b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-1/query4.plan
@@ -0,0 +1,61 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinB.YelpCheckinB) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$80(ASC), $$68(ASC), $$69(ASC), $$55(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinB.IdxYelpCheckinBDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinA.YelpCheckinA) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$78(ASC), $$68(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinA.IdxYelpCheckinADates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-2/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query1.plan
new file mode 100644
index 0000000..641e620
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query1.plan
@@ -0,0 +1,39 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$63(ASC), $$54(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-2/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query2.plan
new file mode 100644
index 0000000..4fa93b1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query2.plan
@@ -0,0 +1,39 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$76(ASC), $$66(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-2/query3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query3.plan
new file mode 100644
index 0000000..d895e99
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-2/query3.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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- NESTED_LOOP |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckin) |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/array-index/join-quantified-queries/use-case-3/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-3/query1.plan
new file mode 100644
index 0000000..09bcc02
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-3/query1.plan
@@ -0,0 +1,40 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$71(ASC), $$62(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-3/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-3/query2.plan
new file mode 100644
index 0000000..76e50bc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-3/query2.plan
@@ -0,0 +1,40 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$75(ASC), $$65(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-4/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query1.plan
new file mode 100644
index 0000000..22f9aa6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query1.plan
@@ -0,0 +1,47 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- SUBPLAN |LOCAL|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$84(ASC), $$73(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-4/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query2.plan
new file mode 100644
index 0000000..df73479
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query2.plan
@@ -0,0 +1,47 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- SUBPLAN |LOCAL|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$88(ASC), $$76(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-4/query3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query3.plan
new file mode 100644
index 0000000..7331734
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query3.plan
@@ -0,0 +1,47 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- SUBPLAN |LOCAL|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$93(ASC), $$80(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckin.IdxYelpCheckinDates) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-4/query4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query4.plan
new file mode 100644
index 0000000..ad7b9c8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-4/query4.plan
@@ -0,0 +1,79 @@
+-- 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|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- SUBPLAN |LOCAL|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinB.YelpCheckinB) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$144(ASC), $$122(ASC), $$123(ASC), $$97(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinB.IdxYelpCheckinDatesB) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- SUBPLAN |PARTITIONED|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- SUBPLAN |LOCAL|
+ {
+ -- AGGREGATE |LOCAL|
+ -- STREAM_SELECT |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- ASSIGN |LOCAL|
+ -- UNNEST |LOCAL|
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinA.YelpCheckinA) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$142(ASC), $$122(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (TestYelp.YelpCheckinA.IdxYelpCheckinDatesA) |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (TestYelp.YelpCheckinDateMarkers) |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/array-index/join-quantified-queries/use-case-1/use-case-1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.1.ddl.sqlpp
new file mode 100644
index 0000000..29e7dae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.1.ddl.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.
+ */
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ dates: [string]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckinA(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinB(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDatesA ON YelpCheckinA (UNNEST dates);
+CREATE INDEX IdxYelpCheckinDatesB ON YelpCheckinB (UNNEST dates);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.2.update.sqlpp
new file mode 100644
index 0000000..6c84dec
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.2.update.sqlpp
@@ -0,0 +1,115 @@
+/*
+ * 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 TestYelp;
+
+INSERT INTO YelpCheckinA (
+ [
+ {
+ "business_id": "1",
+ "dates": [
+ "2016-04-26 19:49:16",
+ "2016-08-30 18:36:57",
+ "2016-10-15 02:45:18",
+ "2016-11-18 01:54:50",
+ "2017-04-20 18:39:06",
+ "2017-05-03 17:58:02",
+ "2019-03-19 22:04:48"
+ ]
+ },
+ {
+ "business_id": "2",
+ "dates": [
+ "2018-05-25 19:52:07",
+ "2018-09-18 16:09:44",
+ "2019-06-07 17:54:58"
+ ]
+ },
+ {
+ "business_id": "3",
+ "dates": [
+ "2019-06-07 17:54:58"
+ ]
+ }
+ ]
+);
+INSERT INTO YelpCheckinB (
+ [
+ {
+ "business_id": "A",
+ "dates": [
+ "2016-04-26 19:49:16",
+ "2016-08-30 18:36:57",
+ "2016-10-15 02:45:18",
+ "2016-11-18 01:54:50",
+ "2017-04-20 18:39:06",
+ "2017-05-03 17:58:02",
+ "2019-03-19 22:04:48"
+ ]
+ },
+ {
+ "business_id": "B",
+ "dates": [
+ "2018-05-25 19:52:07",
+ "2018-09-18 16:09:44",
+ "2019-06-07 17:54:58"
+ ]
+ },
+ {
+ "business_id": "C",
+ "dates": [
+ "2019-06-07 17:54:58"
+ ]
+ },
+ {
+ "business_id": "D",
+ "dates": [
+ "2015-04-11 13:14:14",
+ "2015-11-21 16:05:56",
+ "2016-05-06 14:10:04",
+ "2017-08-09 15:15:10",
+ "2017-10-21 15:12:56"
+ ]
+ },
+ {
+ "business_id": "E",
+ "dates": [ ]
+ }
+ ]
+);
+INSERT INTO YelpCheckinDateMarkers (
+ [
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "datetime": "2019-06-07 17:54:58", "date": "2016-04-26", "time": "19:49:16" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832e", "datetime": "2016-12-29 01:54:42", "date": "2016-12-29", "time": "01:54:42" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832f", "datetime": "2018-07-22 19:55:31", "date": "2018-07-22", "time": "19:55:31" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688330", "datetime": "2018-09-07 01:42:54", "date": "2018-09-07", "time": "01:42:54" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688331", "datetime": "2019-03-08 03:41:06", "date": "2019-03-08", "time": "03:41:06" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "17:54:58" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a6", "datetime": "2019-02-27 14:03:08", "date": "2019-02-27", "time": "14:03:08" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a7", "datetime": "2015-05-29 16:46:17", "date": "2015-05-29", "time": "16:46:17" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a8", "datetime": "2015-06-01 15:03:53", "date": "2015-06-01", "time": "15:03:53" },
+ { "marker": "BAD_ENTRY_1", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_2", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_3", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_4", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_5", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_6", "datetime": "NA", "date": "NA", "time": "NA" }
+ ]
+);
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.3.query.sqlpp
new file mode 100644
index 0000000..1728e9f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE M.datetime /*+ indexnl */ IN A.dates
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.4.query.sqlpp
new file mode 100644
index 0000000..d420403
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.4.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE ( SOME D IN A.dates
+ SATISFIES M.datetime /*+ indexnl */ = D )
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.5.query.sqlpp
new file mode 100644
index 0000000..2f0ba7d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.5.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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE LEN(A.dates) > 0 AND
+ ( EVERY D IN A.dates
+ SATISFIES M.datetime /*+ indexnl */ = D )
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.6.query.sqlpp
new file mode 100644
index 0000000..495eb72
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-1/use-case-1.6.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A, YelpCheckinB B
+WHERE M.datetime /*+ indexnl */ IN A.dates AND
+ M.datetime /*+ indexnl */ IN B.dates
+SELECT M.marker, A.business_id AS business_id_a, B.business_id AS business_id_b
+ORDER BY M.marker, A.business_id, B.business_id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.1.ddl.sqlpp
new file mode 100644
index 0000000..4af2ca9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.1.ddl.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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 TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: {
+ dates: [string],
+ times: [string]
+ }
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckinA(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDatesA ON YelpCheckinA (UNNEST checkin_times.dates);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.2.update.sqlpp
new file mode 100644
index 0000000..044737d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.2.update.sqlpp
@@ -0,0 +1,93 @@
+/*
+ * 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 TestYelp;
+
+INSERT INTO YelpCheckinA (
+ [
+ {
+ "business_id": "1",
+ "checkin_times": {
+ "dates": [
+ "2016-04-26",
+ "2016-08-30",
+ "2016-10-15",
+ "2016-11-18",
+ "2017-04-20",
+ "2017-05-03",
+ "2019-03-19"
+ ],
+ "times": [
+ "19:49:16",
+ "18:36:57",
+ "02:45:18",
+ "01:54:50",
+ "18:39:06",
+ "17:58:02",
+ "22:04:48"
+ ]
+ }
+ },
+ {
+ "business_id": "2",
+ "checkin_times": {
+ "dates": [
+ "2018-05-25",
+ "2018-09-18",
+ "2019-06-07"
+ ],
+ "times": [
+ "19:52:07",
+ "16:09:44",
+ "21:29:09"
+ ]
+ }
+ },
+ {
+ "business_id": "3",
+ "checkin_times": {
+ "dates": [
+ "2019-06-07"
+ ],
+ "times": [
+ "17:54:58"
+ ]
+ }
+ }
+ ]
+);
+INSERT INTO YelpCheckinDateMarkers (
+ [
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "19:49:16" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832f", "datetime": "2018-07-22 19:55:31", "date": "2018-07-22", "time": "19:55:31" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688330", "datetime": "2018-09-07 01:42:54", "date": "2018-09-07", "time": "01:42:54" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688331", "datetime": "2019-03-08 03:41:06", "date": "2019-03-08", "time": "03:41:06" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "17:54:58" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a6", "datetime": "2019-02-27 14:03:08", "date": "2019-02-27", "time": "14:03:08" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a7", "datetime": "2015-05-29 16:46:17", "date": "2015-05-29", "time": "16:46:17" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a8", "datetime": "2015-06-01 15:03:53", "date": "2015-06-01", "time": "15:03:53" },
+ { "marker": "BAD_ENTRY_1", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_2", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_3", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_4", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_5", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_6", "datetime": "NA", "date": "NA", "time": "NA" }
+ ]
+);
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.3.query.sqlpp
new file mode 100644
index 0000000..d233441
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE M.date /*+ indexnl */ IN A.checkin_times.dates
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.4.query.sqlpp
new file mode 100644
index 0000000..6cd541c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-2/use-case-2.4.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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE LEN(A.checkin_times.dates) > 0 AND
+ ( EVERY D IN A.checkin_times.dates
+ SATISFIES M.date /*+ indexnl */ = D )
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.1.ddl.sqlpp
new file mode 100644
index 0000000..8406e6b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.1.ddl.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.
+ */
+
+DROP DATAVERSE TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ date: string,
+ time: string
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.2.update.sqlpp
new file mode 100644
index 0000000..24efca4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.2.update.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * 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 TestYelp;
+
+INSERT INTO YelpCheckin (
+ [
+ {
+ "business_id": "1",
+ "checkin_times": [
+ {"date": "2016-04-26", "time": "19:49:16"},
+ {"date": "2016-08-30", "time": "18:36:57"},
+ {"date": "2016-10-15", "time": "02:45:18"},
+ {"date": "2016-11-18", "time": "01:54:50"},
+ {"date": "2017-04-20", "time": "18:39:06"},
+ {"date": "2017-05-03", "time": "17:58:02"},
+ {"date": "2019-03-19", "time": "22:04:48"}
+ ]
+ },
+ {
+ "business_id": "2",
+ "checkin_times": [
+ {"date": "2018-05-25", "time": "19:52:07"},
+ {"date": "2018-09-18", "time": "16:09:44"},
+ {"date": "2019-06-07", "time": "21:29:09"}
+ ]
+ },
+ {
+ "business_id": "3",
+ "checkin_times": [
+ {"date": "2019-06-07", "time": "17:54:58"}
+ ]
+ }
+ ]
+);
+INSERT INTO YelpCheckinDateMarkers (
+ [
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "19:49:16" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832f", "datetime": "2018-07-22 19:55:31", "date": "2018-07-22", "time": "19:55:31" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688330", "datetime": "2018-09-07 01:42:54", "date": "2018-09-07", "time": "01:42:54" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688331", "datetime": "2019-03-08 03:41:06", "date": "2019-03-08", "time": "03:41:06" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "17:54:58" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a6", "datetime": "2019-02-27 14:03:08", "date": "2019-02-27", "time": "14:03:08" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a7", "datetime": "2015-05-29 16:46:17", "date": "2015-05-29", "time": "16:46:17" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a8", "datetime": "2015-06-01 15:03:53", "date": "2015-06-01", "time": "15:03:53" },
+ { "marker": "BAD_ENTRY_1", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_2", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_3", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_4", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_5", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_6", "datetime": "NA", "date": "NA", "time": "NA" }
+ ]
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.3.query.sqlpp
new file mode 100644
index 0000000..48f8660
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( SOME D IN C.checkin_times
+ SATISFIES D.date /*+ indexnl */ = M.date )
+SELECT M.marker, C.business_id
+ORDER BY M.marker, C.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.4.query.sqlpp
new file mode 100644
index 0000000..d933cb2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-3/use-case-3.4.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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckin C
+WHERE ( EVERY D IN C.checkin_times
+ SATISFIES D.date /*+ indexnl */ = M.date ) AND
+ LEN (C.checkin_times) > 0
+SELECT M.marker, C.business_id
+ORDER BY M.marker, C.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.1.ddl.sqlpp
new file mode 100644
index 0000000..8f2acb1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.1.ddl.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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 TestYelp IF EXISTS;
+CREATE DATAVERSE TestYelp;
+USE TestYelp;
+
+CREATE TYPE CheckinType AS {
+ checkin_id: uuid,
+ business_id: string,
+ checkin_times: [{
+ checkin_times_id: int,
+ dates: [string],
+ times: [string]
+ }]
+};
+CREATE TYPE CheckinDatesToMarkersType AS {
+ marker: string,
+ datetime: string,
+ date: string,
+ time: string
+};
+
+CREATE DATASET YelpCheckinA(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinB(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
+CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY marker;
+CREATE INDEX IdxYelpCheckinDatesA ON YelpCheckinA (UNNEST checkin_times UNNEST dates);
+CREATE INDEX IdxYelpCheckinDatesB ON YelpCheckinB (UNNEST checkin_times UNNEST dates);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.2.update.sqlpp
new file mode 100644
index 0000000..375b518
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.2.update.sqlpp
@@ -0,0 +1,137 @@
+/*
+ * 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 TestYelp;
+
+INSERT INTO YelpCheckinA (
+ [
+ {
+ "business_id": "1",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2016-04-26", "2016-08-30", "2016-10-15", "2016-11-18"],
+ "times": ["19:49:16", "18:36:57", "02:45:18", "01:54:50"]
+ },
+ {
+ "checkin_times_id": 2,
+ "dates": ["2017-04-20", "2017-05-03"],
+ "times": ["18:39:06", "17:58:02"]
+ },
+ {
+ "checkin_times_id": 3,
+ "dates": ["2019-03-19"],
+ "times": ["22:04:48"]
+ }
+ ]
+ },
+ {
+ "business_id": "2",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2019-06-07", "2018-09-18"],
+ "times": ["19:52:07", "16:09:44"]
+ },
+ {
+ "checkin_times_id": 2,
+ "dates": ["2019-06-07"],
+ "times": ["21:29:09"]
+ }
+ ]
+ },
+ {
+ "business_id": "3",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2019-06-07"],
+ "times": ["17:54:58"]
+ }
+ ]
+ }
+ ]
+);
+INSERT INTO YelpCheckinB (
+ [
+ {
+ "business_id": "A",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2016-04-26", "2016-08-30", "2016-10-15", "2016-11-18"],
+ "times": ["19:49:16", "18:36:57", "02:45:18", "01:54:50"]
+ },
+ {
+ "checkin_times_id": 2,
+ "dates": ["2017-04-20", "2017-05-03"],
+ "times": ["18:39:06", "17:58:02"]
+ },
+ {
+ "checkin_times_id": 3,
+ "dates": ["2019-03-19"],
+ "times": ["22:04:48"]
+ }
+ ]
+ },
+ {
+ "business_id": "B",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2019-06-07", "2018-09-18"],
+ "times": ["19:52:07", "16:09:44"]
+ },
+ {
+ "checkin_times_id": 2,
+ "dates": ["2019-06-07"],
+ "times": ["21:29:09"]
+ }
+ ]
+ },
+ {
+ "business_id": "C",
+ "checkin_times": [
+ {
+ "checkin_times_id": 1,
+ "dates": ["2019-06-07"],
+ "times": ["17:54:58"]
+ }
+ ]
+ }
+ ]
+);
+INSERT INTO YelpCheckinDateMarkers (
+ [
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "19:49:16" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832f", "datetime": "2018-07-22 19:55:31", "date": "2018-07-22", "time": "19:55:31" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688330", "datetime": "2018-09-07 01:42:54", "date": "2018-09-07", "time": "01:42:54" },
+ { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688331", "datetime": "2019-03-08 03:41:06", "date": "2019-03-08", "time": "03:41:06" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "datetime": "2019-06-07 17:54:58", "date": "2019-06-07", "time": "17:54:58" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a6", "datetime": "2019-02-27 14:03:08", "date": "2019-02-27", "time": "14:03:08" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a7", "datetime": "2015-05-29 16:46:17", "date": "2015-05-29", "time": "16:46:17" },
+ { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a8", "datetime": "2015-06-01 15:03:53", "date": "2015-06-01", "time": "15:03:53" },
+ { "marker": "BAD_ENTRY_1", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_2", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_3", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_4", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_5", "datetime": "NA", "date": "NA", "time": "NA" },
+ { "marker": "BAD_ENTRY_6", "datetime": "NA", "date": "NA", "time": "NA" }
+ ]
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.3.query.sqlpp
new file mode 100644
index 0000000..c349477
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE ( SOME CT IN A.checkin_times
+ SATISFIES ( SOME D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) )
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.4.query.sqlpp
new file mode 100644
index 0000000..b5338c8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.4.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE ( EVERY CT IN A.checkin_times
+ SATISFIES ( SOME D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) ) AND
+ LEN (A.checkin_times) > 0
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.5.query.sqlpp
new file mode 100644
index 0000000..70ec139
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.5.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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A
+WHERE ( EVERY CT IN A.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (A.checkin_times) > 0
+SELECT M.marker, A.business_id
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.6.query.sqlpp
new file mode 100644
index 0000000..1522959
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-4/use-case-4.6.query.sqlpp
@@ -0,0 +1,35 @@
+
+/*
+ * 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.
+ */
+SET `compiler.arrayindex` "true";
+USE TestYelp;
+
+FROM YelpCheckinDateMarkers M, YelpCheckinA A, YelpCheckinB B
+WHERE ( EVERY CT IN A.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (A.checkin_times) > 0 AND
+ ( EVERY CT IN B.checkin_times
+ SATISFIES ( EVERY D IN CT.dates
+ SATISFIES D /*+ indexnl */ = M.date ) AND
+ LEN (CT.dates) > 0 ) AND
+ LEN (B.checkin_times) > 0
+SELECT M.marker, A.business_id AS business_id_a, B.business_id AS business_id_b
+ORDER BY M.marker, A.business_id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.1.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.1.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.2.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.2.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.3.adm
new file mode 100644
index 0000000..7533447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.3.adm
@@ -0,0 +1,2 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.4.adm
new file mode 100644
index 0000000..20b63fc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-1/use-case-1.4.adm
@@ -0,0 +1,8 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id_a": "2", "business_id_b": "B" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id_a": "2", "business_id_b": "C" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id_a": "3", "business_id_b": "B" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id_a": "3", "business_id_b": "C" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id_a": "2", "business_id_b": "B" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id_a": "2", "business_id_b": "C" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id_a": "3", "business_id_b": "B" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id_a": "3", "business_id_b": "C" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.1.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.1.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.2.adm
new file mode 100644
index 0000000..7533447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-2/use-case-2.2.adm
@@ -0,0 +1,2 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.1.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.1.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.2.adm
new file mode 100644
index 0000000..7533447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-3/use-case-3.2.adm
@@ -0,0 +1,2 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.1.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.1.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.2.adm
new file mode 100644
index 0000000..e60c921
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.2.adm
@@ -0,0 +1,4 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "2" }
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "2" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.3.adm
new file mode 100644
index 0000000..7533447
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.3.adm
@@ -0,0 +1,2 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id": "3" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id": "3" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.4.adm
new file mode 100644
index 0000000..361e29b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-4/use-case-4.4.adm
@@ -0,0 +1,2 @@
+{ "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "business_id_a": "3", "business_id_b": "C" }
+{ "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "business_id_a": "3", "business_id_b": "C" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 4c821a8..5937278 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -6144,6 +6144,11 @@
<output-dir compare="Text">rtree-spatial-self-intersect-point</output-dir>
</compilation-unit>
</test-case>
+ <test-case FilePath="index-join">
+ <compilation-unit name="btree-multiple-join">
+ <output-dir compare="Text">btree-multiple-join</output-dir>
+ </compilation-unit>
+ </test-case>
</test-group>
<test-group name="index-selection">
<test-case FilePath="index-selection">
@@ -8301,6 +8306,28 @@
</compilation-unit>
</test-case>
</test-group>
+ <test-group name="array-index/join-quantified-queries">
+ <test-case FilePath="array-index/join-quantified-queries">
+ <compilation-unit name="use-case-1">
+ <output-dir compare="Text">use-case-1</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="array-index/join-quantified-queries">
+ <compilation-unit name="use-case-2">
+ <output-dir compare="Text">use-case-2</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="array-index/join-quantified-queries">
+ <compilation-unit name="use-case-3">
+ <output-dir compare="Text">use-case-3</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="array-index/join-quantified-queries">
+ <compilation-unit name="use-case-4">
+ <output-dir compare="Text">use-case-4</output-dir>
+ </compilation-unit>
+ </test-case>
+ </test-group>
<test-group name="array-index/join-unnest-queries">
<test-case FilePath="array-index/join-unnest-queries">
<compilation-unit name="use-case-1">
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
index 0df9851..26be09c 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
@@ -202,6 +202,16 @@
}
}
+ public static void substituteOpInInput(ILogicalOperator workingOp, ILogicalOperator oldOp,
+ Mutable<ILogicalOperator> newOpRef) {
+ for (int i = 0; i < workingOp.getInputs().size(); i++) {
+ if (workingOp.getInputs().get(i).getValue().equals(oldOp)) {
+ workingOp.getInputs().set(i, newOpRef);
+ break;
+ }
+ }
+ }
+
public static ILogicalPlan deepCopy(ILogicalPlan plan, ILogicalOperator dataSource) throws AlgebricksException {
List<Mutable<ILogicalOperator>> roots = plan.getRoots();
List<Mutable<ILogicalOperator>> newRoots = clonePipeline(roots);