[ASTERIXDB-2437][COMP] Ensure the index-only plan on a composite index
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Ensure the proper build of an index-only plan on a composite index
where both fields are used in the SELECT condition and only one field
is returned.
Change-Id: Idcc4cbe08323e0c6edb4a01637b2017128da1ab5
Reviewed-on: https://asterix-gerrit.ics.uci.edu/2854
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
index c925b55..10037f0 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
@@ -1196,6 +1196,30 @@
origVarToSIdxUnnestMapOpVarMap.put(tVar, skVarsFromSIdxUnnestMap.get(sIndexIdx));
}
+ // For B-Tree case: if the given secondary key field variable is used only in the select or
+ // join condition, we were not able to catch the mapping between the the SK from the original
+ // data-scan and the SK from the secondary index search since they are different logical variables.
+ // (E.g., we are sending a query on a composite index but returns only one field.)
+ List<LogicalVariable> varsUsedInTopOpButNotAfterwards = new ArrayList<>();
+ copyVarsToAnotherList(uniqueUsedVarsInTopOp, varsUsedInTopOpButNotAfterwards);
+ varsUsedInTopOpButNotAfterwards.removeAll(usedVarsAfterTopOp);
+ if (idxType == IndexType.BTREE) {
+ for (LogicalVariable v : varsUsedInTopOpButNotAfterwards) {
+ int sIndexIdx = chosenIndexFieldNames.indexOf(subTree.getVarsToFieldNameMap().get(v));
+ // For the join-case, the match might not exist.
+ // In this case, we just propagate the variables later.
+ if (sIndexIdx == -1) {
+ continue;
+ }
+ LogicalVariable replacedVar = context.newVar();
+ origPKRecAndSKVarToleftPathMap.put(v, replacedVar);
+ origVarToOutputVarMap.put(skVarsFromSIdxUnnestMap.get(sIndexIdx), v);
+ // Constructs the mapping between the SK from the original data-scan
+ // and the SK from the secondary index search since they are different logical variables.
+ origVarToSIdxUnnestMapOpVarMap.put(v, skVarsFromSIdxUnnestMap.get(sIndexIdx));
+ }
+ }
+
// For R-Tree case: if the given secondary key field variable is used only in the select or join condition,
// we were not able to catch the mapping between the original secondary key field and the newly restored
// secondary key field in the assign operator in the right path.
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp
new file mode 100644
index 0000000..3259a28
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index/btree-sidx-idxonly-10.sqlpp
@@ -0,0 +1,102 @@
+/*
+ * 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.
+ */
+
+/*
+ * Description : Secondary BTree Index index-only selection plan verification test
+ * : The test is intended to verify that the secondary BTree index is used in the optimized query plan.
+ * : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ * : The plan should have two paths after the secondary index-lookup.
+ * : The left path:
+ * ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ * : The right path:
+ * ... -> unnest-map (sidx) -> split -> -> union -> ...
+ * Expected Result : Success
+ *
+*/
+
+drop dataverse twitter if exists;
+create dataverse twitter if not exists;
+use twitter;
+
+create type typeUser if not exists as open {
+ id: int64,
+ name: string,
+ screen_name : string,
+ profile_image_url : string,
+ lang : string,
+ location: string,
+ create_at: date,
+ description: string,
+ followers_count: int32,
+ friends_count: int32,
+ status_count: int64
+};
+
+create type typePlace if not exists as open{
+ country : string,
+ country_code : string,
+ full_name : string,
+ id : string,
+ name : string,
+ place_type : string,
+ bounding_box : rectangle
+};
+
+create type typeGeoTag if not exists as open {
+ stateID: int32,
+ stateName: string,
+ countyID: int32,
+ countyName: string,
+ cityID: int32?,
+ cityName: string?
+};
+
+create type typeTweet if not exists as open {
+ create_at : datetime,
+ id: int64,
+ text: string,
+ in_reply_to_status : int64,
+ in_reply_to_user : int64,
+ favorite_count : int64,
+ coordinate: point?,
+ retweet_count : int64,
+ lang : string,
+ is_retweet: boolean,
+ hashtags : {{ string }} ?,
+ user_mentions : {{ int64 }} ? ,
+ user : typeUser,
+ place : typePlace?,
+ geo_tag: typeGeoTag
+};
+
+create dataset ds_tweet(typeTweet) if not exists primary key id;
+
+create index create_at_status_count_idx on ds_tweet(user.create_at, user.status_count);
+
+select value count(first.create_at) from (
+select t.user.create_at, t.user.status_count, t.id from ds_tweet t
+where
+ t.user.create_at >=
+ date_from_unix_time_in_days(10000) and
+ t.user.create_at <
+ date_from_unix_time_in_days(12000) and
+ t.user.status_count >= 0 and
+ t.user.status_count < 1000000
+) first;
+
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan
new file mode 100644
index 0000000..89e06cc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index/btree-sidx-idxonly-10.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- UNION_ALL |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- SPLIT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- SPLIT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp
new file mode 100644
index 0000000..4530d8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.ddl.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * 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.
+ */
+
+/*
+ * Description : Secondary BTree Index index-only selection plan verification test
+ * : This test is intended to verify that the secondary BTree index is
+ * : used in the optimized query plan.
+ * : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ * : The plan should have two paths after the secondary index-lookup.
+ * : The left path:
+ * ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ * : The right path:
+ * ... -> unnest-map (sidx) -> split -> select (the second condition) -> union -> ...
+ * Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+ id: int64,
+ docid: int64,
+ val1: int64,
+ title: string,
+ point: point,
+ kwds: string,
+ line1: line,
+ line2: line,
+ poly1: polygon,
+ poly2: polygon,
+ rec: rectangle,
+ circle: circle
+};
+
+create dataset MyData(MyRecord)
+ primary key id;
+
+create index btree_index_docid_val1 on MyData(docid,val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp
new file mode 100644
index 0000000..bc705b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp
new file mode 100644
index 0000000..8ccb930
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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;
+
+select element {"pk":o.id, "sk":o.docid}
+from MyData o
+where o.docid < 3 and o.val1 >= 3
+order by o.id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm
new file mode 100644
index 0000000..98ee5d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sidx-composite-idxonly-04/btree-sidx-composite-idxonly-04.1.adm
@@ -0,0 +1,2 @@
+{ "pk": 1, "sk": 1 }
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 9bb995e..18e93e1 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -3377,6 +3377,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="index-selection">
+ <compilation-unit name="btree-sidx-composite-idxonly-04">
+ <output-dir compare="Text">btree-sidx-composite-idxonly-04</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="index-selection">
<compilation-unit name="btree-sidx-idxonly-01">
<output-dir compare="Text">btree-sidx-idxonly-01</output-dir>
</compilation-unit>