>From Peeyush Gupta <[email protected]>:
Peeyush Gupta has uploaded this change for review. (
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
---
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(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb
refs/changes/69/20569/1
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: newchange
Gerrit-Project: asterixdb
Gerrit-Branch: neo
Gerrit-Change-Id: I36be4676b6ae887eb730dcb0e76ece60bc1f3bcd
Gerrit-Change-Number: 20569
Gerrit-PatchSet: 1
Gerrit-Owner: Peeyush Gupta <[email protected]>
