>From Peeyush Gupta <[email protected]>: Peeyush Gupta has submitted this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20569?usp=email )
Change subject: [ASTERIXDB-3671][IDX] Incorrect result with array index and index nested loop join ...................................................................... [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-69372 Change-Id: I36be4676b6ae887eb730dcb0e76ece60bc1f3bcd Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20569 Tested-by: Peeyush Gupta <[email protected]> Reviewed-by: Ali Alsuliman <[email protected]> --- M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java A asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query1.sqlpp A asterixdb/asterix-app/src/test/resources/optimizerts/queries/array-index/join-quantified-queries/use-case-5/query2.sqlpp M asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query8.plan M asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/atomic-and-array-queries/query9.plan A asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query1.plan A asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query2.plan A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/array-index/join-quantified-queries/use-case-5/use-case-5.4.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.3.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.4.adm 13 files changed, 492 insertions(+), 84 deletions(-) Approvals: Peeyush Gupta: Verified Ali Alsuliman: Looks good to me, approved Objections: Anon. E. Moose #1000171: Violations found 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 b2b5e7c..3d80ce2 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 @@ -38,6 +38,7 @@ 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; @@ -48,6 +49,7 @@ 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; @@ -338,7 +340,18 @@ // 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 0000000..80b4978 --- /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 0000000..31f8b1d --- /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 9cb42fc..f92f23b 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 @@ -13,61 +13,65 @@ project ([$$D2, $$D1, $$47]) -- STREAM_PROJECT |PARTITIONED| subplan { - aggregate [$$47] <- [non-empty-stream()] + aggregate [$$47] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| - select (and(eq($$66, to-bigint($$67)), eq($$64, to-bigint($$65)))) + select (and(eq($$66, to-bigint($$67)), eq($$64, to-bigint($$65)))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- STREAM_SELECT |LOCAL| - assign [$$66, $$64] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] + assign [$$66, $$64] <- [$$D1I.getField("field2"), $$D1I.getField("field3")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- ASSIGN |LOCAL| - unnest $$D1I <- scan-collection($$63) + unnest $$D1I <- scan-collection($$63) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- UNNEST |LOCAL| - nested tuple source + nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| } -- SUBPLAN |PARTITIONED| - assign [$$63] <- [$$D1.getField("items")] - -- ASSIGN |PARTITIONED| - project ([$$D2, $$67, $$65, $$D1]) - -- STREAM_PROJECT |PARTITIONED| - exchange - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - unnest-map [$$56, $$D1] <- index-search("Dataset1", 0, "TestDataverse", "Dataset1", true, false, 1, $$74, 1, $$74, true, true, true) - -- BTREE_SEARCH |PARTITIONED| + project ([$$D2, $$D1, $$67, $$65, $$63]) + -- STREAM_PROJECT |PARTITIONED| + select (and(eq($$D1.getField("field4"), $$60), eq($$D1.getField("field1"), $$58))) + -- STREAM_SELECT |PARTITIONED| + assign [$$63] <- [$$D1.getField("items")] + -- ASSIGN |PARTITIONED| + project ([$$D2, $$67, $$65, $$60, $$58, $$D1]) + -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$74, $$D2, $$67, $$65]) - -- STREAM_PROJECT |PARTITIONED| + unnest-map [$$56, $$D1] <- index-search("Dataset1", 0, "TestDataverse", "Dataset1", true, false, 1, $$74, 1, $$74, true, true, true) + -- BTREE_SEARCH |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - distinct ([$$74, $$75]) - -- PRE_SORTED_DISTINCT_BY |PARTITIONED| + project ([$$74, $$D2, $$67, $$65, $$60, $$58]) + -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - order (ASC, $$74) (ASC, $$75) - -- STABLE_SORT [$$74(ASC), $$75(ASC)] |PARTITIONED| + distinct ([$$74, $$75]) + -- PRE_SORTED_DISTINCT_BY |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$D2, $$67, $$65, $$74, $$75]) - -- STREAM_PROJECT |PARTITIONED| + order (ASC, $$74) (ASC, $$75) + -- STABLE_SORT [$$74(ASC), $$75(ASC)] |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - unnest-map [$$70, $$71, $$72, $$73, $$74] <- index-search("d1Idx", 0, "TestDataverse", "Dataset1", true, true, 4, $$58, $$68, $$69, $$60, 4, $$58, $$68, $$69, $$60, true, true, true) - -- BTREE_SEARCH |PARTITIONED| + project ([$$D2, $$67, $$65, $$60, $$58, $$74, $$75]) + -- STREAM_PROJECT |PARTITIONED| exchange - -- BROADCAST_EXCHANGE |PARTITIONED| - running-aggregate [$$75] <- [create-query-uid()] - -- RUNNING_AGGREGATE |PARTITIONED| - assign [$$69, $$68] <- [to-bigint($$65), to-bigint($$67)] - -- ASSIGN |PARTITIONED| - assign [$$60, $$58, $$67, $$65] <- [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 []<-[$$55, $$D2] <- TestDataverse.Dataset2 - -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$70, $$71, $$72, $$73, $$74] <- index-search("d1Idx", 0, "TestDataverse", "Dataset1", true, true, 4, $$58, $$68, $$69, $$60, 4, $$58, $$68, $$69, $$60, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- BROADCAST_EXCHANGE |PARTITIONED| + running-aggregate [$$75] <- [create-query-uid()] + -- RUNNING_AGGREGATE |PARTITIONED| + assign [$$69, $$68] <- [to-bigint($$65), to-bigint($$67)] + -- ASSIGN |PARTITIONED| + assign [$$60, $$58, $$67, $$65] <- [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| - empty-tuple-source - -- EMPTY_TUPLE_SOURCE |PARTITIONED| + data-scan []<-[$$55, $$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 e393017..33b94df 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 @@ -13,76 +13,80 @@ project ([$$D2, $$D1, $$68]) -- STREAM_PROJECT |PARTITIONED| subplan { - aggregate [$$68] <- [non-empty-stream()] + aggregate [$$68] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| - select ($$66) + select ($$66) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- STREAM_SELECT |LOCAL| subplan { - aggregate [$$66] <- [non-empty-stream()] + aggregate [$$66] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| - select (and(eq($$92, to-bigint($$93)), eq($$90, to-bigint($$91)), eq($$88, to-bigint($$89)))) + select (and(eq($$92, to-bigint($$93)), eq($$90, to-bigint($$91)), eq($$88, to-bigint($$89)))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- STREAM_SELECT |LOCAL| - assign [$$92, $$90, $$88] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] + assign [$$92, $$90, $$88] <- [$$DII.getField("field2"), $$DII.getField("field3"), $$DII.getField("field3_notindexed")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- ASSIGN |LOCAL| - unnest $$DII <- scan-collection($$87) + unnest $$DII <- scan-collection($$87) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- UNNEST |LOCAL| - nested tuple source + nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } + } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- SUBPLAN |LOCAL| - select (eq($$85, to-bigint($$86))) + select (eq($$85, to-bigint($$86))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- STREAM_SELECT |LOCAL| - assign [$$87, $$85] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] + assign [$$87, $$85] <- [$$DOI.getField("inner_items"), $$DOI.getField("field2_notindexed")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- ASSIGN |LOCAL| - unnest $$DOI <- scan-collection($$84) + unnest $$DOI <- scan-collection($$84) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- UNNEST |LOCAL| - nested tuple source + nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| } -- SUBPLAN |PARTITIONED| - assign [$$84] <- [$$D1.getField("outer_items")] - -- ASSIGN |PARTITIONED| - project ([$$D2, $$93, $$91, $$89, $$86, $$D1]) - -- STREAM_PROJECT |PARTITIONED| - exchange - -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - unnest-map [$$77, $$D1] <- index-search("Dataset1", 0, "TestDataverse", "Dataset1", true, false, 1, $$104, 1, $$104, true, true, true) - -- BTREE_SEARCH |PARTITIONED| + project ([$$D2, $$D1, $$93, $$91, $$89, $$86, $$84]) + -- STREAM_PROJECT |PARTITIONED| + select (and(eq($$D1.getField("field4"), $$81), eq($$D1.getField("field1"), $$79))) + -- STREAM_SELECT |PARTITIONED| + assign [$$84] <- [$$D1.getField("outer_items")] + -- ASSIGN |PARTITIONED| + project ([$$D2, $$93, $$91, $$89, $$86, $$81, $$79, $$D1]) + -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$104, $$D2, $$93, $$91, $$89, $$86]) - -- STREAM_PROJECT |PARTITIONED| + unnest-map [$$77, $$D1] <- index-search("Dataset1", 0, "TestDataverse", "Dataset1", true, false, 1, $$104, 1, $$104, true, true, true) + -- BTREE_SEARCH |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - distinct ([$$104, $$105]) - -- PRE_SORTED_DISTINCT_BY |PARTITIONED| + project ([$$104, $$D2, $$93, $$91, $$89, $$86, $$81, $$79]) + -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - order (ASC, $$104) (ASC, $$105) - -- STABLE_SORT [$$104(ASC), $$105(ASC)] |PARTITIONED| + distinct ([$$104, $$105]) + -- PRE_SORTED_DISTINCT_BY |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$D2, $$93, $$91, $$89, $$86, $$104, $$105]) - -- STREAM_PROJECT |PARTITIONED| + order (ASC, $$104) (ASC, $$105) + -- STABLE_SORT [$$104(ASC), $$105(ASC)] |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - unnest-map [$$100, $$101, $$102, $$103, $$104] <- index-search("d1Idx", 0, "TestDataverse", "Dataset1", true, true, 4, $$79, $$96, $$97, $$81, 4, $$79, $$96, $$97, $$81, true, true, true) - -- BTREE_SEARCH |PARTITIONED| + project ([$$D2, $$93, $$91, $$89, $$86, $$81, $$79, $$104, $$105]) + -- STREAM_PROJECT |PARTITIONED| exchange - -- BROADCAST_EXCHANGE |PARTITIONED| - running-aggregate [$$105] <- [create-query-uid()] - -- RUNNING_AGGREGATE |PARTITIONED| - assign [$$97, $$96] <- [to-bigint($$91), to-bigint($$93)] - -- ASSIGN |PARTITIONED| - assign [$$81, $$79, $$93, $$91, $$89, $$86] <- [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 []<-[$$76, $$D2] <- TestDataverse.Dataset2 - -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$100, $$101, $$102, $$103, $$104] <- index-search("d1Idx", 0, "TestDataverse", "Dataset1", true, true, 4, $$79, $$96, $$97, $$81, 4, $$79, $$96, $$97, $$81, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- BROADCAST_EXCHANGE |PARTITIONED| + running-aggregate [$$105] <- [create-query-uid()] + -- RUNNING_AGGREGATE |PARTITIONED| + assign [$$97, $$96] <- [to-bigint($$91), to-bigint($$93)] + -- ASSIGN |PARTITIONED| + assign [$$81, $$79, $$93, $$91, $$89, $$86] <- [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| - empty-tuple-source - -- EMPTY_TUPLE_SOURCE |PARTITIONED| + data-scan []<-[$$76, $$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 0000000..15418ae --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query1.plan @@ -0,0 +1,81 @@ +distribute result [$$47] +-- DISTRIBUTE_RESULT |UNPARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + limit 10 + -- STREAM_LIMIT |UNPARTITIONED| + exchange + -- RANDOM_MERGE_EXCHANGE |PARTITIONED| + project ([$$47]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$47] <- [{"date": $$54, "id": $$49, "forecast": $$52}] + -- ASSIGN |PARTITIONED| + limit 10 + -- STREAM_LIMIT |PARTITIONED| + project ([$$54, $$49, $$52]) + -- STREAM_PROJECT |PARTITIONED| + select ($$42) + -- STREAM_SELECT |PARTITIONED| + subplan { + aggregate [$$42] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- AGGREGATE |LOCAL| + select (eq($$53, string-concat(ordered-list-constructor($$54, "03")))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_SELECT |LOCAL| + assign [$$53] <- [$$ff.getField("fv")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |LOCAL| + unnest $$ff <- scan-collection($$52) [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 ([$$54, $$49, $$52]) + -- STREAM_PROJECT |PARTITIONED| + select (eq($$D1.getField("x"), $$49)) + -- STREAM_SELECT |PARTITIONED| + assign [$$52] <- [$$D1.getField("forecast")] + -- ASSIGN |PARTITIONED| + project ([$$54, $$49, $$D1]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$50, $$D1] <- index-search("D1", 0, "test", "D1", true, false, 1, $$59, 1, $$59, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$59, $$54, $$49]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + distinct ([$$59, $$60]) + -- PRE_SORTED_DISTINCT_BY |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$59) (ASC, $$60) + -- STABLE_SORT [$$59(ASC), $$60(ASC)] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$54, $$49, $$59, $$60]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$58, $$59] <- index-search("idx_fv", 0, "test", "D1", true, true, 1, $$57, 1, $$57, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- BROADCAST_EXCHANGE |PARTITIONED| + running-aggregate [$$60] <- [create-query-uid()] + -- RUNNING_AGGREGATE |PARTITIONED| + assign [$$57] <- [string-concat(ordered-list-constructor($$54, "03"))] + -- ASSIGN |PARTITIONED| + project ([$$49, $$54]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$54] <- [$$D2.getField("date")] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$49, $$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 0000000..74d4398 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/array-index/join-quantified-queries/use-case-5/query2.plan @@ -0,0 +1,83 @@ +distribute result [$$47] +-- DISTRIBUTE_RESULT |UNPARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + limit 10 + -- STREAM_LIMIT |UNPARTITIONED| + exchange + -- RANDOM_MERGE_EXCHANGE |PARTITIONED| + project ([$$47]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$47] <- [{"date": $$54, "id": $$49, "forecast": $$52}] + -- ASSIGN |PARTITIONED| + limit 10 + -- STREAM_LIMIT |PARTITIONED| + project ([$$54, $$49, $$52]) + -- STREAM_PROJECT |PARTITIONED| + select ($$42) + -- STREAM_SELECT |PARTITIONED| + subplan { + aggregate [$$42] <- [non-empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- AGGREGATE |LOCAL| + select (eq($$53, string-concat(ordered-list-constructor($$54, "03")))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_SELECT |LOCAL| + assign [$$53] <- [$$ff.getField("fv")] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |LOCAL| + unnest $$ff <- scan-collection($$52) [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 ([$$54, $$49, $$52]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$52] <- [$$D1.getField("forecast")] + -- ASSIGN |PARTITIONED| + project ([$$54, $$49, $$D1]) + -- STREAM_PROJECT |PARTITIONED| + select (eq($$50, $$49)) + -- STREAM_SELECT |PARTITIONED| + project ([$$54, $$49, $$50, $$D1]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$50, $$D1] <- index-search("D1", 0, "test", "D1", true, false, 1, $$59, 1, $$59, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$59, $$54, $$49]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + distinct ([$$59, $$60]) + -- PRE_SORTED_DISTINCT_BY |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$59) (ASC, $$60) + -- STABLE_SORT [$$59(ASC), $$60(ASC)] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$54, $$49, $$59, $$60]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$58, $$59] <- index-search("idx_fv", 0, "test", "D1", true, true, 1, $$57, 1, $$57, true, true, true) + -- BTREE_SEARCH |PARTITIONED| + exchange + -- BROADCAST_EXCHANGE |PARTITIONED| + running-aggregate [$$60] <- [create-query-uid()] + -- RUNNING_AGGREGATE |PARTITIONED| + assign [$$57] <- [string-concat(ordered-list-constructor($$54, "03"))] + -- ASSIGN |PARTITIONED| + project ([$$49, $$54]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$54] <- [$$D2.getField("date")] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$49, $$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/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 0000000..6dfa7a9 --- /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 0000000..3440b2f --- /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 0000000..8af320f --- /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 0000000..2e74ab0 --- /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 0000000..e69de29 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/array-index/join-quantified-queries/use-case-5/use-case-5.3.adm 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 0000000..05d1e05 --- /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 -- To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20569?usp=email To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings?usp=email Gerrit-MessageType: merged Gerrit-Project: asterixdb Gerrit-Branch: neo Gerrit-Change-Id: I36be4676b6ae887eb730dcb0e76ece60bc1f3bcd Gerrit-Change-Number: 20569 Gerrit-PatchSet: 2 Gerrit-Owner: Peeyush Gupta <[email protected]> Gerrit-Reviewer: Ali Alsuliman <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Murtadha Hubail <[email protected]> Gerrit-Reviewer: Peeyush Gupta <[email protected]>
