Fixed ASTERIXDB-1249 and ASTERIXDB-1250: self index-nested-loop join correctly identifies the outer and the inner branch.
The first dataset becomes the outer branch.
The second dataset becomes the inner branch.
The optimizer for index-nested-loop join now only try to use an index from the inner branch.
Change-Id: I0d4291197c2bcfbcdcde998c5952af41960c4ad7
Reviewed-on: https://asterix-gerrit.ics.uci.edu/576
Reviewed-by: Yingyi Bu <buyingyi@gmail.com>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
index 9e8a987..8a78d40 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
@@ -20,7 +20,7 @@
* Description : Equi joins two datasets, Customers and Orders, based on the customer id.
* Given the 'indexnl' hint we expect the join to be transformed
* into an indexed nested-loop join using Customers' primary index.
- * We expect the additional predicates to be put into a select above the
+ * We expect the additional predicates to be put into a select above the
* primary index search.
* Success : Yes
*/
@@ -30,13 +30,13 @@
use dataverse test;
create type AddressType as closed {
- number: int32,
+ number: int32,
street: string,
city: string
}
create type CustomerType as closed {
- cid: int32,
+ cid: int32,
name: string,
age: int32?,
address: AddressType?,
@@ -60,7 +60,7 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-equi-join-multipred.adm";
-for $c in dataset('Customers')
for $o in dataset('Orders')
+for $c in dataset('Customers')
where $c.cid /*+ indexnl */ = $o.cid and $c.name < $o.orderstatus and $c.age < $o.cid
-return {"customer":$c, "order": $o}
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
index 48e7965..aeb6cbb 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
@@ -40,8 +40,8 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-equi-join_01.adm";
-for $x in dataset('test1.DsOne')
for $y in dataset('test1.DsTwo')
+for $x in dataset('test1.DsOne')
where $x.key1 /*+ indexnl */ = $y.key2
return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
index a4b5264..2aa29f4 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
@@ -17,8 +17,8 @@
* under the License.
*/
/*
- * Description : Notice the query hint to use an indexed nested-loops join plan.
- * : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Description : Notice the query hint to use an indexed nested-loops join plan.
+ * : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
* Expected Res : Success
* Date : 29th November 2012
*/
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
index 025b101..622e8e1 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
@@ -28,13 +28,13 @@
use dataverse test;
create type AddressType as closed {
- number: int32,
+ number: int32,
street: string,
city: string
}
create type CustomerType as closed {
- cid: int32,
+ cid: int32,
name: string,
age: int32?,
address: AddressType?,
@@ -58,7 +58,7 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-equi-join_04.adm";
-for $c in dataset('Customers')
for $o in dataset('Orders')
+for $c in dataset('Customers')
where $c.cid /*+ indexnl */ = $o.cid
-return {"customer":$c, "order": $o}
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
index 5ae298c..72b8b87 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
@@ -17,8 +17,8 @@
* under the License.
*/
/*
- * Description : Notice the query hint to use an indexed nested-loops join plan.
- * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Description : Notice the query hint to use an indexed nested-loops join plan.
+ * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
* Expected Res : Success
* Date : 29th November 2012
*/
@@ -40,8 +40,8 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-ge-join_01.adm";
-for $x in dataset('test1.DsOne')
for $y in dataset('test1.DsTwo')
+for $x in dataset('test1.DsOne')
where $x.key1 /*+ indexnl */ >= $y.key2
return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
index f0d1853..05d3f89 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
@@ -17,8 +17,8 @@
* under the License.
*/
/*
- * Description : Notice the query hint to use an indexed nested-loops join plan.
- * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Description : Notice the query hint to use an indexed nested-loops join plan.
+ * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
* Expected Res : Success
* Date : 29th November 2012
*/
@@ -40,8 +40,8 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-gt-join_01.adm";
-for $x in dataset('test1.DsOne')
for $y in dataset('test1.DsTwo')
+for $x in dataset('test1.DsOne')
where $x.key1 /*+ indexnl */ > $y.key2
return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
index a880f16..6353d16 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
@@ -17,8 +17,8 @@
* under the License.
*/
/*
- * Description : Notice the query hint to use an indexed nested-loops join plan.
- * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Description : Notice the query hint to use an indexed nested-loops join plan.
+ * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
* Expected Res : Success
* Date : 29th November 2012
*/
@@ -40,8 +40,8 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-le-join_01.adm";
-for $x in dataset('test1.DsOne')
for $y in dataset('test1.DsTwo')
+for $x in dataset('test1.DsOne')
where $x.key1 /*+ indexnl */ <= $y.key2
return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
index e87265c..a5a964d 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
@@ -17,8 +17,8 @@
* under the License.
*/
/*
- * Description : Notice the query hint to use an indexed nested-loops join plan.
- * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Description : Notice the query hint to use an indexed nested-loops join plan.
+ * : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
* Expected Res : Success
* Date : 29th November 2012
*/
@@ -40,8 +40,8 @@
write output to asterix_nc1:"rttest/btree-index-join_primary-lt-join_01.adm";
-for $x in dataset('test1.DsOne')
for $y in dataset('test1.DsTwo')
+for $x in dataset('test1.DsOne')
where $x.key1 /*+ indexnl */ < $y.key2
return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
index dd4ca77..bffaf9c 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
@@ -18,9 +18,9 @@
*/
/*
* Description : Equi joins two datasets, DBLP and CSX, based on their title.
- * DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ * DBLP has a secondary btree index on title, and given the 'indexnl' hint
* we expect the join to be transformed into an indexed nested-loop join.
- * We expect the additional predicates to be put into a select above the
+ * We expect the additional predicates to be put into a select above the
* primary index search.
* Success : Yes
*/
@@ -30,7 +30,7 @@
use dataverse test;
create type DBLPType as closed {
- id: int32,
+ id: int32,
dblpid: string,
title: string,
authors: string,
@@ -38,7 +38,7 @@
}
create type CSXType as closed {
- id: int32,
+ id: int32,
csxid: string,
title: string,
authors: string,
@@ -53,7 +53,7 @@
write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join-multipred.adm";
-for $a in dataset('DBLP')
for $b in dataset('CSX')
+for $a in dataset('DBLP')
where $a.title /*+ indexnl */ = $b.title and $a.authors < $b.authors and $a.misc > $b.misc
return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql
index 4de7a0b..eb2d407 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql
@@ -18,7 +18,7 @@
*/
/*
* Description : Equi joins two datasets, DBLP and CSX, based on their title.
- * DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ * DBLP has a secondary btree index on title, and given the 'indexnl' hint
* we expect the join to be transformed into an indexed nested-loop join.
* Success : Yes
*/
@@ -28,7 +28,7 @@
use dataverse test;
create type DBLPType as closed {
- id: int32,
+ id: int32,
dblpid: string,
title: string,
authors: string,
@@ -36,7 +36,7 @@
}
create type CSXType as closed {
- id: int32,
+ id: int32,
csxid: string,
title: string,
authors: string,
@@ -51,7 +51,7 @@
write output to asterix_nc1:"rttest/btree-index-join_title-secondary-equi-join_01.adm";
-for $a in dataset('DBLP')
for $b in dataset('CSX')
+for $a in dataset('DBLP')
where $a.title /*+ indexnl */ = $b.title
return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-self-equi-join.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-self-equi-join.aql
new file mode 100644
index 0000000..abfd197
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-self-equi-join.aql
@@ -0,0 +1,65 @@
+/*
+ * 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 : Self-equi joins on a dataset using two fields - countA and countB.
+ * TweetMessages has a secondary btree index on countB, and given the 'indexnl' hint
+ * we expect the join to be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+ screen-name: string,
+ lang: string,
+ friends-count: int64,
+ statuses-count: int64,
+ name: string,
+ followers-count: int64
+}
+
+create type TweetMessageType as closed {
+ tweetid: int64,
+ user: TwitterUserType,
+ sender-location: point,
+ send-time: datetime,
+ referred-topics: {{ string }},
+ message-text: string,
+ countA: int64,
+ countB: int64
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(sender-location) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(message-text) type keyword;
+
+write output to asterix_nc1:"rttest/btree-index-join_self-secondary-equi-join.adm";
+
+for $t1 in dataset('TweetMessages')
+for $t2 in dataset('TweetMessages')
+let $c := $t1.countA + 20
+where $c /* +indexnl */= $t2.countB
+order by $t2.tweetid
+return {"tweetid2": $t2.tweetid, "count2":$t2.countB};