This is an automated email from the ASF dual-hosted git repository.
mblow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new a5c6e93 [ASTERIXDB-2830][IDX] Fix INLJ Bug for LOJ Queries w/ Array
Indexes
a5c6e93 is described below
commit a5c6e9339c78cce2d68e6b77730c9ad9fb805a1d
Author: ggalvizo <[email protected]>
AuthorDate: Fri Jun 25 10:45:30 2021 -0700
[ASTERIXDB-2830][IDX] Fix INLJ Bug for LOJ Queries w/ Array Indexes
- user mode changes: no
- storage format changes: no
- interface changes: no
Left outer join queries that utilized an array index for
index-nested-loop join queries would previously incorrectly filter out
"probe" entries that didn't have a corresponding index entry due to the
presence of an INNER-UNNEST on the index entry subtree. All
INNER-UNNESTs on the index subtree are now converted to
LEFT-OUTER-UNNESTs.
Change-Id: I0e6132dcff0a8ad5b6610e86386bb055a07fc97e
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/12043
Integration-Tests: Jenkins <[email protected]>
Tested-by: Jenkins <[email protected]>
Reviewed-by: Dmitry Lychagin <[email protected]>
---
.../optimizer/rules/am/ArrayBTreeAccessMethod.java | 80 ++++++++++++++++++++++
.../join-unnest-queries/use-case-1/query4.sqlpp} | 17 ++---
.../join-unnest-queries/use-case-2/query4.sqlpp} | 17 ++---
.../join-unnest-queries/use-case-3/query5.sqlpp} | 22 +++---
.../join-unnest-queries/use-case-4/query5.sqlpp} | 22 +++---
.../join-unnest-queries/use-case-1/query4.plan | 37 ++++++++++
.../join-unnest-queries/use-case-2/query4.plan | 37 ++++++++++
.../join-unnest-queries/use-case-3/query5.plan | 37 ++++++++++
.../join-unnest-queries/use-case-4/query5.plan | 41 +++++++++++
.../use-case-1/use-case-1.1.ddl.sqlpp | 2 +-
.../use-case-1/use-case-1.8.query.sqlpp | 29 ++++++++
.../use-case-2/use-case-2.1.ddl.sqlpp | 2 +-
.../use-case-2/use-case-2.8.query.sqlpp | 29 ++++++++
.../use-case-3/use-case-3.1.ddl.sqlpp | 2 +-
.../use-case-3/use-case-3.9.query.sqlpp | 29 ++++++++
.../use-case-4/use-case-4.1.ddl.sqlpp | 2 +-
.../use-case-4/use-case-4.9.query.sqlpp | 29 ++++++++
.../with-open-index/with-open-index.1.ddl.sqlpp | 2 +-
.../with-open-index/with-open-index.8.query.sqlpp | 29 ++++++++
.../use-case-1.1.adm | 0
.../use-case-1.2.adm | 0
.../use-case-1.3.adm | 0
.../use-case-1/use-case-1.4.adm | 1 +
.../use-case-2/use-case-2.4.adm | 1 +
.../use-case-3/use-case-3.5.adm | 1 +
.../use-case-4/use-case-4.5.adm | 1 +
.../with-open-index.1.adm | 0
.../with-open-index.2.adm | 0
.../with-open-index.3.adm | 0
.../with-open-index/with-open-index.4.adm | 1 +
30 files changed, 430 insertions(+), 40 deletions(-)
diff --git
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
index 44e4a18..653d15b 100644
---
a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
+++
b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
@@ -25,16 +25,22 @@ import java.util.List;
import org.apache.asterix.common.config.DatasetConfig.IndexType;
import org.apache.asterix.common.exceptions.CompilationException;
import org.apache.asterix.common.exceptions.ErrorCode;
+import org.apache.asterix.metadata.entities.Dataset;
import org.apache.asterix.metadata.entities.Index;
import org.apache.asterix.metadata.utils.ArrayIndexUtil;
import org.apache.asterix.om.types.IAType;
import org.apache.asterix.om.utils.NonTaggedFormatUtil;
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.core.algebra.base.ILogicalExpression;
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.LogicalOperatorTag;
import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import
org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractBinaryJoinOperator;
+import
org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestOperator;
+import
org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
// TODO (GLENN): Refactor the BTreeAccessMethod class and this class to extend
a new "AbstractBTreeAccessMethod" class.
/**
@@ -71,6 +77,80 @@ public class ArrayBTreeAccessMethod extends
BTreeAccessMethod {
}
@Override
+ public boolean applyJoinPlanTransformation(List<Mutable<ILogicalOperator>>
afterJoinRefs,
+ Mutable<ILogicalOperator> joinRef, OptimizableOperatorSubTree
leftSubTree,
+ OptimizableOperatorSubTree rightSubTree, Index chosenIndex,
AccessMethodAnalysisContext analysisCtx,
+ IOptimizationContext context, boolean isLeftOuterJoin, boolean
isLeftOuterJoinWithSpecialGroupBy)
+ throws AlgebricksException {
+ AbstractBinaryJoinOperator joinOp = (AbstractBinaryJoinOperator)
joinRef.getValue();
+ Mutable<ILogicalExpression> conditionRef = joinOp.getCondition();
+ Dataset dataset = analysisCtx.getIndexDatasetMap().get(chosenIndex);
+ OptimizableOperatorSubTree indexSubTree, probeSubTree;
+
+ // We assume that the left subtree is the outer branch and the right
subtree is the inner branch. This
+ // assumption holds true since we only use an index from the right
subtree. The following is just a sanity
+ // check.
+ if (rightSubTree.hasDataSourceScan()
+ &&
dataset.getDatasetName().equals(rightSubTree.getDataset().getDatasetName())) {
+ indexSubTree = rightSubTree;
+ probeSubTree = leftSubTree;
+ } else {
+ return false;
+ }
+
+ LogicalVariable newNullPlaceHolderVar = null;
+ if (isLeftOuterJoin) {
+ // Gets a new null place holder variable that is the first field
variable of the primary key from the
+ // indexSubTree's datasourceScanOp. We need this for all left
outer joins, even those that do not have
+ // a special GroupBy.
+ newNullPlaceHolderVar =
indexSubTree.getDataSourceVariables().get(0);
+
+ // For all INNER-UNNESTs associated with the inner subtree (i.e.
the index subtree) to extract the
+ // secondary keys, transform these UNNESTs to LEFT-OUTER-UNNESTs.
This is to ensure that probe entries w/o
+ // a corresponding secondary key entry are not incorrectly
removed. This will not invalidate our fetched
+ // entries because *all* index entries have a non-empty array.
+ ILogicalOperator workingOp = indexSubTree.getRoot(), rootOp =
indexSubTree.getRoot(), previousOp = null;
+ while (!workingOp.getInputs().isEmpty()) {
+ if (workingOp.getOperatorTag() == LogicalOperatorTag.UNNEST) {
+ UnnestOperator oldUnnest = (UnnestOperator) workingOp;
+ LeftOuterUnnestOperator newUnnest = new
LeftOuterUnnestOperator(oldUnnest.getVariable(),
+ new
MutableObject<>(oldUnnest.getExpressionRef().getValue()));
+ newUnnest.setSourceLocation(oldUnnest.getSourceLocation());
+ newUnnest.getInputs().addAll(oldUnnest.getInputs());
+ newUnnest.setExecutionMode(oldUnnest.getExecutionMode());
+ context.computeAndSetTypeEnvironmentForOperator(newUnnest);
+ if (workingOp.equals(rootOp)) {
+ rootOp = newUnnest;
+ workingOp = newUnnest;
+ } else if (previousOp != null) {
+ previousOp.getInputs().clear();
+ previousOp.getInputs().add(new
MutableObject<>(newUnnest));
+
context.computeAndSetTypeEnvironmentForOperator(previousOp);
+ }
+ }
+ previousOp = workingOp;
+ workingOp = workingOp.getInputs().get(0).getValue();
+ }
+ indexSubTree.setRoot(rootOp);
+ indexSubTree.setRootRef(new MutableObject<>(rootOp));
+ joinOp.getInputs().remove(1);
+ joinOp.getInputs().add(1, new MutableObject<>(rootOp));
+ context.computeAndSetTypeEnvironmentForOperator(joinOp);
+ }
+
+ ILogicalOperator indexSearchOp = createIndexSearchPlan(afterJoinRefs,
joinRef, conditionRef,
+ indexSubTree.getAssignsAndUnnestsRefs(), indexSubTree,
probeSubTree, chosenIndex, analysisCtx, true,
+ isLeftOuterJoin, true, context, newNullPlaceHolderVar);
+ if (indexSearchOp == null) {
+ return false;
+ }
+
+ return AccessMethodUtils.finalizeJoinPlanTransformation(afterJoinRefs,
joinRef, indexSubTree, probeSubTree,
+ analysisCtx, context, isLeftOuterJoin,
isLeftOuterJoinWithSpecialGroupBy, indexSearchOp,
+ newNullPlaceHolderVar, conditionRef, dataset);
+ }
+
+ @Override
public ILogicalOperator
createIndexSearchPlan(List<Mutable<ILogicalOperator>> afterTopOpRefs,
Mutable<ILogicalOperator> topOpRef, Mutable<ILogicalExpression>
conditionRef,
List<Mutable<ILogicalOperator>> assignBeforeTheOpRefs,
OptimizableOperatorSubTree indexSubTree,
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-1/query4.sqlpp
similarity index 71%
copy from
asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
copy to
asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-1/query4.sqlpp
index 39f18a0..6147325 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-1/query4.sqlpp
@@ -16,14 +16,7 @@
* specific language governing permissions and limitations
* under the License.
*/
-
-/*
- * Description: Verify that we are able to correctly perform the following
types of queries using an array->atomic index.
- * 1) Implicit INNER JOIN query, with additional predicate on inner
collection before the unnest.
- * 2) Implicit INNER JOIN query, with additional predicate on the outer
collection.
- * 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
- */
+SET `compiler.arrayindex` "true";
DROP DATAVERSE TestYelp IF EXISTS;
CREATE DATAVERSE TestYelp;
@@ -43,3 +36,11 @@ CREATE TYPE CheckinDatesToMarkersType AS {
CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERATED;
CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY KEY
marker;
+CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST dates);
+
+SELECT COUNT(*)
+FROM YelpCheckinDateMarkers M
+LEFT OUTER JOIN (
+ SELECT VALUE D
+ FROM YelpCheckin C, C.dates D
+) AS CD ON CD /*+ indexnl */ = M.datetime;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-2/query4.sqlpp
similarity index 72%
copy from
asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
copy to
asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-2/query4.sqlpp
index a00ffca..5be3ede 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-2/query4.sqlpp
@@ -16,14 +16,7 @@
* specific language governing permissions and limitations
* under the License.
*/
-
-/*
- * Description: Verify that we are able to correctly perform the following
types of queries using an record->array->atomic index.
- * 1) INNER JOIN query, with additional predicate on inner collection
before the unnest.
- * 2) INNER JOIN query, with additional predicate on the outer collection.
- * 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
- */
+SET `compiler.arrayindex` "true";
DROP DATAVERSE TestYelp IF EXISTS;
CREATE DATAVERSE TestYelp;
@@ -46,3 +39,11 @@ CREATE TYPE CheckinDatesToMarkersType AS {
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);
+
+SELECT COUNT(*)
+FROM YelpCheckinDateMarkers M
+LEFT OUTER JOIN (
+ SELECT VALUE D
+ FROM YelpCheckin C, C.checkin_times.dates D
+) AS CD ON CD /*+ indexnl */ = M.date;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-3/query5.sqlpp
similarity index 70%
copy from
asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
copy to
asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-3/query5.sqlpp
index 39f18a0..0b595d7 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-3/query5.sqlpp
@@ -16,14 +16,7 @@
* specific language governing permissions and limitations
* under the License.
*/
-
-/*
- * Description: Verify that we are able to correctly perform the following
types of queries using an array->atomic index.
- * 1) Implicit INNER JOIN query, with additional predicate on inner
collection before the unnest.
- * 2) Implicit INNER JOIN query, with additional predicate on the outer
collection.
- * 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
- */
+SET `compiler.arrayindex` "true";
DROP DATAVERSE TestYelp IF EXISTS;
CREATE DATAVERSE TestYelp;
@@ -32,7 +25,10 @@ USE TestYelp;
CREATE TYPE CheckinType AS {
checkin_id: uuid,
business_id: string,
- dates: [string]
+ checkin_times: [{
+ date: string,
+ time: string
+ }]
};
CREATE TYPE CheckinDatesToMarkersType AS {
marker: string,
@@ -43,3 +39,11 @@ CREATE TYPE CheckinDatesToMarkersType AS {
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);
+
+SELECT COUNT(*)
+FROM YelpCheckinDateMarkers M
+LEFT OUTER JOIN (
+ SELECT VALUE D.date
+ FROM YelpCheckin C, C.checkin_times D
+) AS CD ON CD /*+ indexnl */ = M.date;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-4/query5.sqlpp
similarity index 71%
copy from
asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
copy to
asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-4/query5.sqlpp
index a00ffca..d5c3295 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-unnest-queries/use-case-4/query5.sqlpp
@@ -16,14 +16,7 @@
* specific language governing permissions and limitations
* under the License.
*/
-
-/*
- * Description: Verify that we are able to correctly perform the following
types of queries using an record->array->atomic index.
- * 1) INNER JOIN query, with additional predicate on inner collection
before the unnest.
- * 2) INNER JOIN query, with additional predicate on the outer collection.
- * 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
- */
+SET `compiler.arrayindex` "true";
DROP DATAVERSE TestYelp IF EXISTS;
CREATE DATAVERSE TestYelp;
@@ -32,10 +25,11 @@ USE TestYelp;
CREATE TYPE CheckinType AS {
checkin_id: uuid,
business_id: string,
- checkin_times: {
+ checkin_times: [{
+ checkin_times_id: int,
dates: [string],
times: [string]
- }
+ }]
};
CREATE TYPE CheckinDatesToMarkersType AS {
marker: string,
@@ -46,3 +40,11 @@ CREATE TYPE CheckinDatesToMarkersType AS {
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);
+
+SELECT COUNT(*)
+FROM YelpCheckinDateMarkers M
+LEFT OUTER JOIN (
+ SELECT VALUE D
+ FROM YelpCheckin C, C.checkin_times CT, CT.dates D
+) AS CD ON CD /*+ indexnl */ = M.date;
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-1/query4.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-1/query4.plan
new file mode 100644
index 0000000..f602149
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-1/query4.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- WINDOW_STREAM |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$70(ASC), $$71(DESC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$70] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- LEFT_OUTER_UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH
(TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- STABLE_SORT [$$79(ASC),
$$70(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|
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-2/query4.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-2/query4.plan
new file mode 100644
index 0000000..44fd353
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-2/query4.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- WINDOW_STREAM |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$71(ASC), $$72(DESC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$71] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- LEFT_OUTER_UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH
(TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- STABLE_SORT [$$81(ASC),
$$71(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|
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-3/query5.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-3/query5.plan
new file mode 100644
index 0000000..0497e66
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-3/query5.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- WINDOW_STREAM |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$72(ASC), $$73(DESC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$72] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- LEFT_OUTER_UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH
(TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- PRE_SORTED_DISTINCT_BY
|PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- STABLE_SORT [$$81(ASC),
$$72(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|
diff --git
a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-4/query5.plan
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-4/query5.plan
new file mode 100644
index 0000000..aac007e
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-unnest-queries/use-case-4/query5.plan
@@ -0,0 +1,41 @@
+-- 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|
+ -- WINDOW_STREAM |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$81(ASC), $$82(DESC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$81] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- LEFT_OUTER_UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- LEFT_OUTER_UNNEST |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- BTREE_SEARCH
(TestYelp.YelpCheckin.YelpCheckin) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ --
PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE
|PARTITIONED|
+ -- STABLE_SORT
[$$91(ASC), $$81(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|
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
index 39f18a0..3e7c0c9 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.1.ddl.sqlpp
@@ -22,7 +22,7 @@
* 1) Implicit INNER JOIN query, with additional predicate on inner
collection before the unnest.
* 2) Implicit INNER JOIN query, with additional predicate on the outer
collection.
* 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
+ * 4) Explicit LEFT OUTER JOIN query using UNNEST subquery, no additional
predicate.
*/
DROP DATAVERSE TestYelp IF EXISTS;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.8.query.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.8.query.sqlpp
new file mode 100644
index 0000000..7bfe568
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-1/use-case-1.8.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
+LEFT OUTER JOIN (
+ FROM YelpCheckin C, C.dates CD
+ SELECT VALUE CD
+) AS D ON D /*+ indexnl */ = M.datetime
+SELECT COUNT(*);
+
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
index a00ffca..4e89d6a 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.1.ddl.sqlpp
@@ -22,7 +22,7 @@
* 1) INNER JOIN query, with additional predicate on inner collection
before the unnest.
* 2) INNER JOIN query, with additional predicate on the outer collection.
* 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
+ * 4) Explicit LEFT OUTER JOIN query using UNNEST subquery, no additional
predicate.
*/
DROP DATAVERSE TestYelp IF EXISTS;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.8.query.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.8.query.sqlpp
new file mode 100644
index 0000000..0414384
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-2/use-case-2.8.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
+LEFT OUTER JOIN (
+ FROM YelpCheckin C, C.checkin_times.dates CD
+ SELECT VALUE CD
+) AS D ON D /*+ indexnl */ = M.datetime
+SELECT COUNT(*);
+
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.1.ddl.sqlpp
index fed0346..d7c49d1 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.1.ddl.sqlpp
@@ -23,7 +23,7 @@
* 2) INNER JOIN query, with additional predicate on inner collection
after the unnest.
* 3) INNER JOIN query, with additional predicate on the outer collection.
* 4) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 5) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
+ * 5) Explicit LEFT OUTER JOIN query using UNNEST subquery, no additional
predicate.
*/
DROP DATAVERSE TestYelp IF EXISTS;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.9.query.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.9.query.sqlpp
new file mode 100644
index 0000000..27eab43
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-3/use-case-3.9.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
+LEFT OUTER JOIN (
+ FROM YelpCheckin C, C.checkin_times D
+ SELECT VALUE D.date
+) AS CD ON CD /* +indexnl */ = M.date
+SELECT COUNT(*);
+
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.1.ddl.sqlpp
index 41d36e9..100cbdd 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.1.ddl.sqlpp
@@ -23,7 +23,7 @@
* 2) INNER JOIN query, with additional predicate on inner collection
before the first unnest and before the final unnest.
* 3) INNER JOIN query, with additional predicate on the outer collection.
* 4) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 5) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
+ * 5) Explicit LEFT OUTER JOIN query using UNNEST subquery, no additional
predicate.
*/
DROP DATAVERSE TestYelp IF EXISTS;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.9.query.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.9.query.sqlpp
new file mode 100644
index 0000000..58ba46a
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/use-case-4/use-case-4.9.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
+LEFT OUTER JOIN (
+ FROM YelpCheckin C, C.checkin_times CT, CT.dates D
+ SELECT VALUE D
+) AS CD ON CD /* +indexnl */ = M.date
+SELECT COUNT(*);
+
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.1.ddl.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.1.ddl.sqlpp
index 039df8c..6a74203 100644
---
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.1.ddl.sqlpp
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.1.ddl.sqlpp
@@ -22,7 +22,7 @@
* 1) Implicit INNER JOIN query, with additional predicate on inner
collection before the unnest.
* 2) Implicit INNER JOIN query, with additional predicate on the outer
collection.
* 3) Explicit INNER JOIN query using UNNEST subquery, no additional
predicate.
- * TODO (GLENN) 4) Explicit LEFT OUTER JOIN query using UNNEST subquery,
no additional predicate.
+ * 4) Explicit LEFT OUTER JOIN query using UNNEST subquery, no additional
predicate.
*/
DROP DATAVERSE TestYelp IF EXISTS;
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.8.query.sqlpp
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.8.query.sqlpp
new file mode 100644
index 0000000..7bfe568
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-unnest-queries/with-open-index/with-open-index.8.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
+LEFT OUTER JOIN (
+ FROM YelpCheckin C, C.dates CD
+ SELECT VALUE CD
+) AS D ON D /*+ indexnl */ = M.datetime
+SELECT COUNT(*);
+
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.1.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.1.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.1.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.1.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.2.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.2.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.2.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.2.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.3.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.3.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/use-case-1.3.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.3.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.4.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.4.adm
new file mode 100644
index 0000000..694c9d6
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/use-case-1.4.adm
@@ -0,0 +1 @@
+{ "$1": 107 }
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-2/use-case-2.4.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-2/use-case-2.4.adm
new file mode 100644
index 0000000..694c9d6
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-2/use-case-2.4.adm
@@ -0,0 +1 @@
+{ "$1": 107 }
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-3/use-case-3.5.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-3/use-case-3.5.adm
new file mode 100644
index 0000000..e593a03
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-3/use-case-3.5.adm
@@ -0,0 +1 @@
+{ "$1": 125 }
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-4/use-case-4.5.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-4/use-case-4.5.adm
new file mode 100644
index 0000000..e593a03
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-4/use-case-4.5.adm
@@ -0,0 +1 @@
+{ "$1": 125 }
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.1.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.1.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.1.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.1.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.2.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.2.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.2.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.2.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.3.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.3.adm
similarity index 100%
rename from
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/use-case-1/with-open-index.3.adm
rename to
asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.3.adm
diff --git
a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.4.adm
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.4.adm
new file mode 100644
index 0000000..694c9d6
--- /dev/null
+++
b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-unnest-queries/with-open-index/with-open-index.4.adm
@@ -0,0 +1 @@
+{ "$1": 107 }
