You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by mb...@apache.org on 2025/11/12 14:30:36 UTC
(asterixdb) 07/11: [ASTERIXDB-3671][IDX] Incorrect result with array index and index nested loop join
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
commit 29a2e6a098749a7291fe761759563eba48010200
Author: Peeyush Gupta <pe...@couchbase.com>
AuthorDate: Fri Nov 7 15:10:21 2025 -0800
[ASTERIXDB-3671][IDX] Incorrect result with array index and index nested loop join
- user model changes: no
- storage format changes: no
- interface changes: no
Ext-ref: MB-69202
Change-Id: Ib32ebefe4d7dc32978c98effeb77e1237ee76a3d
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20563
Reviewed-by: Ali Alsuliman <al...@gmail.com>
Tested-by: Peeyush Gupta <pe...@couchbase.com>
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Peeyush Gupta <pe...@couchbase.com>
---
.../rules/am/IntroduceJoinAccessMethodRule.java | 15 ++-
.../use-case-5/query1.sqlpp | 42 +++++++
.../use-case-5/query2.sqlpp | 42 +++++++
.../atomic-and-array-queries/query8.plan | 104 +++++++++---------
.../atomic-and-array-queries/query9.plan | 122 +++++++++++----------
.../join-quantified-queries/use-case-5/query1.plan | 75 +++++++++++++
.../join-quantified-queries/use-case-5/query2.plan | 75 +++++++++++++
.../use-case-5/query1.plan | 116 ++++++++++----------
.../use-case-5/query2.plan | 116 ++++++++++----------
.../atomic-and-array-queries/query8.plan | 104 +++++++++---------
.../atomic-and-array-queries/query9.plan | 122 +++++++++++----------
.../use-case-5/use-case-5.1.ddl.sqlpp | 31 ++++++
.../use-case-5/use-case-5.2.update.sqlpp | 46 ++++++++
.../use-case-5/use-case-5.3.query.sqlpp | 30 +++++
.../use-case-5/use-case-5.4.query.sqlpp | 30 +++++
.../use-case-5/use-case-5.3.adm | 0
.../use-case-5/use-case-5.4.adm | 2 +
17 files changed, 727 insertions(+), 345 deletions(-)
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 b7ae1b0edb..12e32bea7f 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
@@ -39,6 +39,7 @@ 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.AbstractFunctionCallExpression;
import org.apache.hyracks.algebricks.core.algebra.expressions.IAlgebricksConstantValue;
import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
@@ -49,6 +50,7 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogi
import org.apache.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator;
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.util.OperatorManipulationUtil;
import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
@@ -344,7 +346,18 @@ public class IntroduceJoinAccessMethodRule extends AbstractIntroduceAccessMethod
// 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));
+ IAlgebricksConstantValue leftOuterMissingValue =
+ isLeftOuterJoin ? ((LeftOuterJoinOperator) joinOp).getMissingValue() : null;
+ LogicalVariable newMissingNullPlaceHolderVar = null;
+ SelectOperator topSelectOp =
+ isLeftOuterJoin ? new SelectOperator(joinOp.getCondition(), leftOuterMissingValue,
+ newMissingNullPlaceHolderVar) : new SelectOperator(joinOp.getCondition());
+ topSelectOp.setSourceLocation(joinOp.getSourceLocation());
+ topSelectOp.getInputs().add(joinOp.getInputs().get(1));
+ topSelectOp.setExecutionMode(AbstractLogicalOperator.ExecutionMode.LOCAL);
+ context.computeAndSetTypeEnvironmentForOperator(topSelectOp);
+ OperatorManipulationUtil.substituteOpInInput(lastAfterJoinOp, joinOp,
+ new MutableObject<>(topSelectOp));
context.computeAndSetTypeEnvironmentForOperator(lastAfterJoinOp);
return true;
}
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query1.sqlpp
new file mode 100644
index 0000000000..80b49782e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query1.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE testType AS {
+ id: int
+};
+
+CREATE DATASET D1(testType) PRIMARY KEY id;
+CREATE DATASET D2(testType) PRIMARY KEY id;
+
+CREATE INDEX idx_fv ON D1(UNNEST forecast SELECT fv : string) EXCLUDE UNKNOWN KEY;
+
+SELECT
+ D2.date,
+ D2.id,
+ D1.forecast
+FROM D2
+JOIN D1
+ ON D1.x = D2.id
+ WHERE (ANY ff IN D1.forecast SATISFIES ff.fv /*+ indexnl */ = (D2.date || "03") END )
+LIMIT 10;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query2.sqlpp
new file mode 100644
index 0000000000..31f8b1d78a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query2.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+SET `compiler.arrayindex` "true";
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE testType AS {
+ id: int
+};
+
+CREATE DATASET D1(testType) PRIMARY KEY id;
+CREATE DATASET D2(testType) PRIMARY KEY id;
+
+CREATE INDEX idx_fv ON D1(UNNEST forecast SELECT fv : string) EXCLUDE UNKNOWN KEY;
+
+SELECT
+ D2.date,
+ D2.id,
+ D1.forecast
+FROM D2
+JOIN D1
+ ON D1.id = D2.id
+ WHERE (ANY ff IN D1.forecast SATISFIES ff.fv /*+ indexnl */ = (D2.date || "03") END )
+LIMIT 10;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query8.plan
index 431b4f7371..c9b9c54567 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query8.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query8.plan
@@ -9,63 +9,65 @@ distribute result [$$57]
project ([$$D2, $$D1, $$50])
-- STREAM_PROJECT |PARTITIONED|
subplan {
- aggregate [$$50] <- [non-empty-stream()]
+ aggregate [$$50] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select (and(eq($$69, to-bigint($$70)), eq($$67, to-bigint($$68))))
+ select (and(eq($$69, to-bigint($$70)), eq($$67, to-bigint($$68)))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$69, $$67] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] project: [$$70, $$68, $$69, $$67]
+ assign [$$69, $$67] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] project: [$$70, $$68, $$69, $$67] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$D1I <- scan-collection($$66) project: [$$70, $$68, $$D1I]
+ unnest $$D1I <- scan-collection($$66) project: [$$70, $$68, $$D1I] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$70, $$68, $$66])
+ project ([$$70, $$68, $$66]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
- assign [$$66] <- [$$D1.getField("items")]
- -- ASSIGN |PARTITIONED|
- project ([$$D2, $$70, $$68, $$D1])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$59, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$77, 1, $$77, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$77, $$D2, $$70, $$68])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- distinct ([$$77, $$78])
- -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- order (ASC, $$77) (ASC, $$78)
- -- STABLE_SORT [$$77(ASC), $$78(ASC)] |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$D2, $$70, $$68, $$77, $$78])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$73, $$74, $$75, $$76, $$77] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$61, $$71, $$72, $$63, 4, $$61, $$71, $$72, $$63, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
- running-aggregate [$$78] <- [create-query-uid()]
- -- RUNNING_AGGREGATE |PARTITIONED|
- assign [$$72, $$71] <- [to-bigint($$68), to-bigint($$70)]
- -- ASSIGN |PARTITIONED|
- assign [$$63, $$61, $$70, $$68] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3")]
+ select (and(eq($$D1.getField("field4"), $$63), eq($$D1.getField("field1"), $$61))) project: [$$D2, $$D1, $$70, $$68, $$66]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$66] <- [$$D1.getField("items")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2, $$70, $$68, $$63, $$61, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$59, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$77, 1, $$77, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$77, $$D2, $$70, $$68, $$63, $$61])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$77, $$78])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$77) (ASC, $$78)
+ -- STABLE_SORT [$$77(ASC), $$78(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$D2, $$70, $$68, $$63, $$61, $$77, $$78])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$73, $$74, $$75, $$76, $$77] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$61, $$71, $$72, $$63, 4, $$61, $$71, $$72, $$63, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$78] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$72, $$71] <- [to-bigint($$68), to-bigint($$70)]
-- ASSIGN |PARTITIONED|
- project ([$$D2])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$58, $$D2] <- TestDataverse.Dataset2
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ assign [$$63, $$61, $$70, $$68] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$58, $$D2] <- TestDataverse.Dataset2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query9.plan
index 12d38cc193..badead3198 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query9.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query9.plan
@@ -9,82 +9,84 @@ distribute result [$$79]
project ([$$D2, $$D1, $$72])
-- STREAM_PROJECT |PARTITIONED|
subplan {
- aggregate [$$72] <- [non-empty-stream()]
+ aggregate [$$72] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select ($$70)
+ select ($$70) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- project ([$$70])
+ project ([$$70]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
subplan {
- aggregate [$$70] <- [non-empty-stream()]
+ aggregate [$$70] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select (and(eq($$96, to-bigint($$97)), eq($$94, to-bigint($$95)), eq($$92, to-bigint($$93))))
+ select (and(eq($$96, to-bigint($$97)), eq($$94, to-bigint($$95)), eq($$92, to-bigint($$93)))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$96, $$94, $$92] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] project: [$$97, $$95, $$93, $$96, $$94, $$92]
+ assign [$$96, $$94, $$92] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] project: [$$97, $$95, $$93, $$96, $$94, $$92] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$DII <- scan-collection($$91) project: [$$97, $$95, $$93, $$DII]
+ unnest $$DII <- scan-collection($$91) project: [$$97, $$95, $$93, $$DII] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$97, $$95, $$93, $$91])
+ project ([$$97, $$95, $$93, $$91]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
- }
+ } [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- SUBPLAN |LOCAL|
- select (eq($$89, to-bigint($$90))) project: [$$97, $$95, $$93, $$91]
+ select (eq($$89, to-bigint($$90))) project: [$$97, $$95, $$93, $$91] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$91, $$89] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] project: [$$97, $$95, $$93, $$90, $$91, $$89]
+ assign [$$91, $$89] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] project: [$$97, $$95, $$93, $$90, $$91, $$89] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$DOI <- scan-collection($$88) project: [$$97, $$95, $$93, $$90, $$DOI]
+ unnest $$DOI <- scan-collection($$88) project: [$$97, $$95, $$93, $$90, $$DOI] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$97, $$95, $$93, $$90, $$88])
+ project ([$$97, $$95, $$93, $$90, $$88]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
- assign [$$88] <- [$$D1.getField("outer_items")]
- -- ASSIGN |PARTITIONED|
- project ([$$D2, $$97, $$95, $$93, $$90, $$D1])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$81, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$108, 1, $$108, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$108, $$D2, $$97, $$95, $$93, $$90])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- distinct ([$$108, $$109])
- -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- order (ASC, $$108) (ASC, $$109)
- -- STABLE_SORT [$$108(ASC), $$109(ASC)] |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$D2, $$97, $$95, $$93, $$90, $$108, $$109])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$104, $$105, $$106, $$107, $$108] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$83, $$100, $$101, $$85, 4, $$83, $$100, $$101, $$85, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
- running-aggregate [$$109] <- [create-query-uid()]
- -- RUNNING_AGGREGATE |PARTITIONED|
- assign [$$101, $$100] <- [to-bigint($$95), to-bigint($$97)]
- -- ASSIGN |PARTITIONED|
- assign [$$85, $$83, $$97, $$95, $$93, $$90] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3"), $$D2.getField("field3_notindexed"), $$D2.getField("field2_notindexed")]
+ select (and(eq($$D1.getField("field4"), $$85), eq($$D1.getField("field1"), $$83))) project: [$$D2, $$D1, $$97, $$95, $$93, $$90, $$88]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$88] <- [$$D1.getField("outer_items")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2, $$97, $$95, $$93, $$90, $$85, $$83, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$81, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$108, 1, $$108, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$108, $$D2, $$97, $$95, $$93, $$90, $$85, $$83])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$108, $$109])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$108) (ASC, $$109)
+ -- STABLE_SORT [$$108(ASC), $$109(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$D2, $$97, $$95, $$93, $$90, $$85, $$83, $$108, $$109])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$104, $$105, $$106, $$107, $$108] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$83, $$100, $$101, $$85, 4, $$83, $$100, $$101, $$85, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$109] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$101, $$100] <- [to-bigint($$95), to-bigint($$97)]
-- ASSIGN |PARTITIONED|
- project ([$$D2])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$80, $$D2] <- TestDataverse.Dataset2
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ assign [$$85, $$83, $$97, $$95, $$93, $$90] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3"), $$D2.getField("field3_notindexed"), $$D2.getField("field2_notindexed")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$80, $$D2] <- TestDataverse.Dataset2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query1.plan
new file mode 100644
index 0000000000..5ab60ad5c6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query1.plan
@@ -0,0 +1,75 @@
+distribute result [$$50]
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |UNPARTITIONED|
+ exchange
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ assign [$$50] <- [{"date": $$57, "id": $$52, "forecast": $$55}] project: [$$50]
+ -- ASSIGN |PARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |PARTITIONED|
+ select ($$45) project: [$$57, $$52, $$55]
+ -- STREAM_SELECT |PARTITIONED|
+ subplan {
+ aggregate [$$45] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (eq($$56, string-concat(ordered-list-constructor($$57, "03")))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ assign [$$56] <- [$$ff.getField("fv")] project: [$$57, $$56] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |LOCAL|
+ unnest $$ff <- scan-collection($$55) project: [$$57, $$ff] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |LOCAL|
+ project ([$$57, $$55]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- SUBPLAN |PARTITIONED|
+ select (eq($$D1.getField("x"), $$52)) project: [$$57, $$52, $$55]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$55] <- [$$D1.getField("forecast")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$57, $$52, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$53, $$D1] <- index-search("D1", 0, "Default", "test", "D1", true, false, 1, $$62, 1, $$62, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$62, $$57, $$52])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$62, $$63])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$62) (ASC, $$63)
+ -- STABLE_SORT [$$62(ASC), $$63(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$57, $$52, $$62, $$63])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$61, $$62] <- index-search("idx_fv", 0, "Default", "test", "D1", true, true, 1, $$60, 1, $$60, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$63] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$60] <- [string-concat(ordered-list-constructor($$57, "03"))]
+ -- ASSIGN |PARTITIONED|
+ assign [$$57] <- [$$D2.getField("date")] project: [$$52, $$57]
+ -- ASSIGN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$52, $$D2] <- test.D2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query2.plan
new file mode 100644
index 0000000000..6b9660d68f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query2.plan
@@ -0,0 +1,75 @@
+distribute result [$$50]
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |UNPARTITIONED|
+ exchange
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ assign [$$50] <- [{"date": $$57, "id": $$52, "forecast": $$55}] project: [$$50]
+ -- ASSIGN |PARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |PARTITIONED|
+ select ($$45) project: [$$57, $$52, $$55]
+ -- STREAM_SELECT |PARTITIONED|
+ subplan {
+ aggregate [$$45] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (eq($$56, string-concat(ordered-list-constructor($$57, "03")))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ assign [$$56] <- [$$ff.getField("fv")] project: [$$57, $$56] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |LOCAL|
+ unnest $$ff <- scan-collection($$55) project: [$$57, $$ff] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |LOCAL|
+ project ([$$57, $$55]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- SUBPLAN |PARTITIONED|
+ assign [$$55] <- [$$D1.getField("forecast")] project: [$$57, $$52, $$55]
+ -- ASSIGN |PARTITIONED|
+ select (eq($$53, $$52)) project: [$$57, $$52, $$D1]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$57, $$52, $$53, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$53, $$D1] <- index-search("D1", 0, "Default", "test", "D1", true, false, 1, $$62, 1, $$62, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$62, $$57, $$52])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$62, $$63])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$62) (ASC, $$63)
+ -- STABLE_SORT [$$62(ASC), $$63(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$57, $$52, $$62, $$63])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$61, $$62] <- index-search("idx_fv", 0, "Default", "test", "D1", true, true, 1, $$60, 1, $$60, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$63] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$60] <- [string-concat(ordered-list-constructor($$57, "03"))]
+ -- ASSIGN |PARTITIONED|
+ assign [$$57] <- [$$D2.getField("date")] project: [$$52, $$57]
+ -- ASSIGN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$52, $$D2] <- test.D2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query1.plan
index 44e3886609..4c30d35dde 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query1.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query1.plan
@@ -1,4 +1,4 @@
-distribute result [$$44]
+distribute result [$$47]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
@@ -6,64 +6,58 @@ distribute result [$$44]
-- STREAM_LIMIT |UNPARTITIONED|
exchange
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
- project ([$$44])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$44] <- [{"date": $$51, "sid": $$45, "forecast": $$49}]
- -- ASSIGN |PARTITIONED|
- limit 10
- -- STREAM_LIMIT |PARTITIONED|
- project ([$$51, $$45, $$49])
- -- STREAM_PROJECT |PARTITIONED|
- select ($$39)
- -- STREAM_SELECT |PARTITIONED|
- subplan {
- aggregate [$$39] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- AGGREGATE |LOCAL|
- select (eq($$50, "aaaa")) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- STREAM_SELECT |LOCAL|
- assign [$$50] <- [$$ff.getField("fv")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- ASSIGN |LOCAL|
- unnest $$ff <- scan-collection($$49) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- UNNEST |LOCAL|
- nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- NESTED_TUPLE_SOURCE |LOCAL|
- }
- -- SUBPLAN |PARTITIONED|
- project ([$$51, $$45, $$49])
- -- STREAM_PROJECT |PARTITIONED|
+ assign [$$47] <- [{"date": $$54, "sid": $$48, "forecast": $$52}] project: [$$47]
+ -- ASSIGN |PARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |PARTITIONED|
+ select ($$42) project: [$$54, $$48, $$52]
+ -- STREAM_SELECT |PARTITIONED|
+ subplan {
+ aggregate [$$42] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (eq($$53, "aaaa")) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ assign [$$53] <- [$$ff.getField("fv")] project: [$$53] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |LOCAL|
+ unnest $$ff <- scan-collection($$52) project: [$$ff] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |LOCAL|
+ project ([$$52]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- SUBPLAN |PARTITIONED|
+ project ([$$54, $$48, $$52])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$51, $$48))
+ -- HYBRID_HASH_JOIN [$$48][$$51] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- join (eq($$48, $$45))
- -- HYBRID_HASH_JOIN [$$45][$$48] |PARTITIONED|
- exchange
- -- HASH_PARTITION_EXCHANGE [$$45] |PARTITIONED|
- project ([$$51, $$45])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$51, $$45] <- [$$sales.getField("date"), $$sales.getField("sid")]
- -- ASSIGN |PARTITIONED|
- project ([$$sales])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$46, $$sales] <- TestYelp.sales
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
- exchange
- -- HASH_PARTITION_EXCHANGE [$$48] |PARTITIONED|
- project ([$$49, $$48])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$49, $$48] <- [$$temp.getField("forecast"), $$temp.getField("x")]
- -- ASSIGN |PARTITIONED|
- project ([$$temp])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$47, $$temp] <- TestYelp.temp
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
+ -- HASH_PARTITION_EXCHANGE [$$48] |PARTITIONED|
+ assign [$$54, $$48] <- [$$sales.getField("date"), $$sales.getField("sid")] project: [$$54, $$48]
+ -- ASSIGN |PARTITIONED|
+ project ([$$sales])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$49, $$sales] <- TestYelp.sales
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$51] |PARTITIONED|
+ assign [$$52, $$51] <- [$$temp.getField("forecast"), $$temp.getField("x")] project: [$$52, $$51]
+ -- ASSIGN |PARTITIONED|
+ project ([$$temp])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$50, $$temp] <- TestYelp.temp
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query2.plan
index 44e3886609..4c30d35dde 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query2.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/select-quantified-queries/use-case-5/query2.plan
@@ -1,4 +1,4 @@
-distribute result [$$44]
+distribute result [$$47]
-- DISTRIBUTE_RESULT |UNPARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
@@ -6,64 +6,58 @@ distribute result [$$44]
-- STREAM_LIMIT |UNPARTITIONED|
exchange
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
- project ([$$44])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$44] <- [{"date": $$51, "sid": $$45, "forecast": $$49}]
- -- ASSIGN |PARTITIONED|
- limit 10
- -- STREAM_LIMIT |PARTITIONED|
- project ([$$51, $$45, $$49])
- -- STREAM_PROJECT |PARTITIONED|
- select ($$39)
- -- STREAM_SELECT |PARTITIONED|
- subplan {
- aggregate [$$39] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- AGGREGATE |LOCAL|
- select (eq($$50, "aaaa")) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- STREAM_SELECT |LOCAL|
- assign [$$50] <- [$$ff.getField("fv")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- ASSIGN |LOCAL|
- unnest $$ff <- scan-collection($$49) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- UNNEST |LOCAL|
- nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
- -- NESTED_TUPLE_SOURCE |LOCAL|
- }
- -- SUBPLAN |PARTITIONED|
- project ([$$51, $$45, $$49])
- -- STREAM_PROJECT |PARTITIONED|
+ assign [$$47] <- [{"date": $$54, "sid": $$48, "forecast": $$52}] project: [$$47]
+ -- ASSIGN |PARTITIONED|
+ limit 10
+ -- STREAM_LIMIT |PARTITIONED|
+ select ($$42) project: [$$54, $$48, $$52]
+ -- STREAM_SELECT |PARTITIONED|
+ subplan {
+ aggregate [$$42] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (eq($$53, "aaaa")) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ assign [$$53] <- [$$ff.getField("fv")] project: [$$53] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |LOCAL|
+ unnest $$ff <- scan-collection($$52) project: [$$ff] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |LOCAL|
+ project ([$$52]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- SUBPLAN |PARTITIONED|
+ project ([$$54, $$48, $$52])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$51, $$48))
+ -- HYBRID_HASH_JOIN [$$48][$$51] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- join (eq($$48, $$45))
- -- HYBRID_HASH_JOIN [$$45][$$48] |PARTITIONED|
- exchange
- -- HASH_PARTITION_EXCHANGE [$$45] |PARTITIONED|
- project ([$$51, $$45])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$51, $$45] <- [$$sales.getField("date"), $$sales.getField("sid")]
- -- ASSIGN |PARTITIONED|
- project ([$$sales])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$46, $$sales] <- TestYelp.sales
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
- exchange
- -- HASH_PARTITION_EXCHANGE [$$48] |PARTITIONED|
- project ([$$49, $$48])
- -- STREAM_PROJECT |PARTITIONED|
- assign [$$49, $$48] <- [$$temp.getField("forecast"), $$temp.getField("x")]
- -- ASSIGN |PARTITIONED|
- project ([$$temp])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$47, $$temp] <- TestYelp.temp
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
+ -- HASH_PARTITION_EXCHANGE [$$48] |PARTITIONED|
+ assign [$$54, $$48] <- [$$sales.getField("date"), $$sales.getField("sid")] project: [$$54, $$48]
+ -- ASSIGN |PARTITIONED|
+ project ([$$sales])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$49, $$sales] <- TestYelp.sales
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$51] |PARTITIONED|
+ assign [$$52, $$51] <- [$$temp.getField("forecast"), $$temp.getField("x")] project: [$$52, $$51]
+ -- ASSIGN |PARTITIONED|
+ project ([$$temp])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$50, $$temp] <- TestYelp.temp
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query8.plan
index 431b4f7371..c9b9c54567 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query8.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query8.plan
@@ -9,63 +9,65 @@ distribute result [$$57]
project ([$$D2, $$D1, $$50])
-- STREAM_PROJECT |PARTITIONED|
subplan {
- aggregate [$$50] <- [non-empty-stream()]
+ aggregate [$$50] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select (and(eq($$69, to-bigint($$70)), eq($$67, to-bigint($$68))))
+ select (and(eq($$69, to-bigint($$70)), eq($$67, to-bigint($$68)))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$69, $$67] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] project: [$$70, $$68, $$69, $$67]
+ assign [$$69, $$67] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] project: [$$70, $$68, $$69, $$67] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$D1I <- scan-collection($$66) project: [$$70, $$68, $$D1I]
+ unnest $$D1I <- scan-collection($$66) project: [$$70, $$68, $$D1I] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$70, $$68, $$66])
+ project ([$$70, $$68, $$66]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
- assign [$$66] <- [$$D1.getField("items")]
- -- ASSIGN |PARTITIONED|
- project ([$$D2, $$70, $$68, $$D1])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$59, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$77, 1, $$77, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$77, $$D2, $$70, $$68])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- distinct ([$$77, $$78])
- -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- order (ASC, $$77) (ASC, $$78)
- -- STABLE_SORT [$$77(ASC), $$78(ASC)] |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$D2, $$70, $$68, $$77, $$78])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$73, $$74, $$75, $$76, $$77] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$61, $$71, $$72, $$63, 4, $$61, $$71, $$72, $$63, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
- running-aggregate [$$78] <- [create-query-uid()]
- -- RUNNING_AGGREGATE |PARTITIONED|
- assign [$$72, $$71] <- [to-bigint($$68), to-bigint($$70)]
- -- ASSIGN |PARTITIONED|
- assign [$$63, $$61, $$70, $$68] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3")]
+ select (and(eq($$D1.getField("field4"), $$63), eq($$D1.getField("field1"), $$61))) project: [$$D2, $$D1, $$70, $$68, $$66]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$66] <- [$$D1.getField("items")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2, $$70, $$68, $$63, $$61, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$59, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$77, 1, $$77, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$77, $$D2, $$70, $$68, $$63, $$61])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$77, $$78])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$77) (ASC, $$78)
+ -- STABLE_SORT [$$77(ASC), $$78(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$D2, $$70, $$68, $$63, $$61, $$77, $$78])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$73, $$74, $$75, $$76, $$77] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$61, $$71, $$72, $$63, 4, $$61, $$71, $$72, $$63, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$78] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$72, $$71] <- [to-bigint($$68), to-bigint($$70)]
-- ASSIGN |PARTITIONED|
- project ([$$D2])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$58, $$D2] <- TestDataverse.Dataset2
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ assign [$$63, $$61, $$70, $$68] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$58, $$D2] <- TestDataverse.Dataset2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query9.plan
index 12d38cc193..badead3198 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query9.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/array-index/atomic-and-array-queries/query9.plan
@@ -9,82 +9,84 @@ distribute result [$$79]
project ([$$D2, $$D1, $$72])
-- STREAM_PROJECT |PARTITIONED|
subplan {
- aggregate [$$72] <- [non-empty-stream()]
+ aggregate [$$72] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select ($$70)
+ select ($$70) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- project ([$$70])
+ project ([$$70]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
subplan {
- aggregate [$$70] <- [non-empty-stream()]
+ aggregate [$$70] <- [non-empty-stream()] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- AGGREGATE |LOCAL|
- select (and(eq($$96, to-bigint($$97)), eq($$94, to-bigint($$95)), eq($$92, to-bigint($$93))))
+ select (and(eq($$96, to-bigint($$97)), eq($$94, to-bigint($$95)), eq($$92, to-bigint($$93)))) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$96, $$94, $$92] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] project: [$$97, $$95, $$93, $$96, $$94, $$92]
+ assign [$$96, $$94, $$92] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] project: [$$97, $$95, $$93, $$96, $$94, $$92] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$DII <- scan-collection($$91) project: [$$97, $$95, $$93, $$DII]
+ unnest $$DII <- scan-collection($$91) project: [$$97, $$95, $$93, $$DII] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$97, $$95, $$93, $$91])
+ project ([$$97, $$95, $$93, $$91]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
- }
+ } [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- SUBPLAN |LOCAL|
- select (eq($$89, to-bigint($$90))) project: [$$97, $$95, $$93, $$91]
+ select (eq($$89, to-bigint($$90))) project: [$$97, $$95, $$93, $$91] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_SELECT |LOCAL|
- assign [$$91, $$89] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] project: [$$97, $$95, $$93, $$90, $$91, $$89]
+ assign [$$91, $$89] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] project: [$$97, $$95, $$93, $$90, $$91, $$89] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ASSIGN |LOCAL|
- unnest $$DOI <- scan-collection($$88) project: [$$97, $$95, $$93, $$90, $$DOI]
+ unnest $$DOI <- scan-collection($$88) project: [$$97, $$95, $$93, $$90, $$DOI] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- UNNEST |LOCAL|
- project ([$$97, $$95, $$93, $$90, $$88])
+ project ([$$97, $$95, $$93, $$90, $$88]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- STREAM_PROJECT |LOCAL|
- nested tuple source
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
- assign [$$88] <- [$$D1.getField("outer_items")]
- -- ASSIGN |PARTITIONED|
- project ([$$D2, $$97, $$95, $$93, $$90, $$D1])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$81, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$108, 1, $$108, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$108, $$D2, $$97, $$95, $$93, $$90])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- distinct ([$$108, $$109])
- -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- order (ASC, $$108) (ASC, $$109)
- -- STABLE_SORT [$$108(ASC), $$109(ASC)] |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- project ([$$D2, $$97, $$95, $$93, $$90, $$108, $$109])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- unnest-map [$$104, $$105, $$106, $$107, $$108] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$83, $$100, $$101, $$85, 4, $$83, $$100, $$101, $$85, true, true, true)
- -- BTREE_SEARCH |PARTITIONED|
- exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
- running-aggregate [$$109] <- [create-query-uid()]
- -- RUNNING_AGGREGATE |PARTITIONED|
- assign [$$101, $$100] <- [to-bigint($$95), to-bigint($$97)]
- -- ASSIGN |PARTITIONED|
- assign [$$85, $$83, $$97, $$95, $$93, $$90] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3"), $$D2.getField("field3_notindexed"), $$D2.getField("field2_notindexed")]
+ select (and(eq($$D1.getField("field4"), $$85), eq($$D1.getField("field1"), $$83))) project: [$$D2, $$D1, $$97, $$95, $$93, $$90, $$88]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$88] <- [$$D1.getField("outer_items")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2, $$97, $$95, $$93, $$90, $$85, $$83, $$D1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$81, $$D1] <- index-search("Dataset1", 0, "Default", "TestDataverse", "Dataset1", true, false, 1, $$108, 1, $$108, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$108, $$D2, $$97, $$95, $$93, $$90, $$85, $$83])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ distinct ([$$108, $$109])
+ -- PRE_SORTED_DISTINCT_BY |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$108) (ASC, $$109)
+ -- STABLE_SORT [$$108(ASC), $$109(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$D2, $$97, $$95, $$93, $$90, $$85, $$83, $$108, $$109])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ unnest-map [$$104, $$105, $$106, $$107, $$108] <- index-search("d1Idx", 0, "Default", "TestDataverse", "Dataset1", true, true, 4, $$83, $$100, $$101, $$85, 4, $$83, $$100, $$101, $$85, true, true, true)
+ -- BTREE_SEARCH |PARTITIONED|
+ exchange
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ running-aggregate [$$109] <- [create-query-uid()]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$101, $$100] <- [to-bigint($$95), to-bigint($$97)]
-- ASSIGN |PARTITIONED|
- project ([$$D2])
- -- STREAM_PROJECT |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$80, $$D2] <- TestDataverse.Dataset2
- -- DATASOURCE_SCAN |PARTITIONED|
- exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- empty-tuple-source
- -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ assign [$$85, $$83, $$97, $$95, $$93, $$90] <- [to-bigint($$D2.getField("field4")), to-bigint($$D2.getField("field1")), $$D2.getField("field2"), $$D2.getField("field3"), $$D2.getField("field3_notindexed"), $$D2.getField("field2_notindexed")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$D2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$80, $$D2] <- TestDataverse.Dataset2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.1.ddl.sqlpp
new file mode 100644
index 0000000000..6dfa7a9f1c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.1.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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 test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE testType AS {
+ id: int
+};
+
+CREATE DATASET D1(testType) PRIMARY KEY id;
+CREATE DATASET D2(testType) PRIMARY KEY id;
+
+CREATE INDEX idx_fv ON D1(UNNEST forecast SELECT fv : string) EXCLUDE UNKNOWN KEY;
\ 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-5/use-case-5.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.2.update.sqlpp
new file mode 100644
index 0000000000..3440b2f7a2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.2.update.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.
+ */
+
+USE test;
+
+insert into D1 [
+ {
+ "id":1,
+ "forecast": [{
+ "fv":"1003"
+ }]
+ },
+ {
+ "id":2,
+ "forecast": [{
+ "fv":"2003"
+ }]
+ }
+];
+
+insert into D2 [
+ {
+ "id":1,
+ "date":"10"
+ },
+ {
+ "id":2,
+ "date":"20"
+ }
+];
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.3.query.sqlpp
new file mode 100644
index 0000000000..8af320f0d1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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 test;
+
+SELECT
+ D2.date,
+ D2.id,
+ D1.forecast
+FROM D2
+JOIN D1
+ ON D1.x = D2.id
+ WHERE (ANY ff IN D1.forecast SATISFIES ff.fv /*+ indexnl */ = (D2.date || "03") END )
+LIMIT 10;
\ 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-5/use-case-5.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.4.query.sqlpp
new file mode 100644
index 0000000000..2e74ab0e0a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.4.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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 test;
+
+SELECT
+ D2.date,
+ D2.id,
+ D1.forecast
+FROM D2
+JOIN D1
+ ON D1.id = D2.id
+ WHERE (ANY ff IN D1.forecast SATISFIES ff.fv /*+ indexnl */ = (D2.date || "03") END )
+LIMIT 10;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.3.adm
new file mode 100644
index 0000000000..e69de29bb2
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.4.adm
new file mode 100644
index 0000000000..05d1e05f35
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.4.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "date": "10", "forecast": [ { "fv": "1003" } ] }
+{ "id": 2, "date": "20", "forecast": [ { "fv": "2003" } ] }
\ No newline at end of file