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 <[email protected]> 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 <[email protected]> Tested-by: Peeyush Gupta <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Peeyush Gupta <[email protected]> --- .../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
