http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
new file mode 100644
index 0000000..e4e4497
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * 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    : 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 Orders' secondary 
index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type AddressType as open {
+  number: int64,
+  street: string,
+  city: string
+};
+
+create type CustomerType as closed {
+  cid: int64,
+  name: string,
+  cashBack: int64,
+  age: int64?,
+  address: AddressType?,
+  lastorder: {
+    oid: int64,
+    total: float
+  }
+};
+
+create type OrderType as open {
+  oid: int64,
+  cid: int64,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float,
+  items: [int64]
+};
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key oid;
+
+create index CustomerID_idx on Orders(cid);
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
new file mode 100644
index 0000000..ddfd26b
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * 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    : 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 Orders' secondary 
index.
+ * Success        : Yes
+ */
+
+use test;
+
+load dataset Customers
+using localfs
+(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+load dataset Orders
+using localfs
+(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
new file mode 100644
index 0000000..4bf06f0
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-primary-to-secondary-indexonly-plan-equi-join_01/btree-primary-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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    : 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 Orders' secondary 
index.
+ * Success        : Yes
+ */
+
+use test;
+
+select value count(*) from (
+select o.oid, c.cid
+from Customers c, Orders o
+where c.cid /*+ indexnl */ = o.cid) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp
new file mode 100644
index 0000000..bb700a1
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.1.ddl.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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    : Equi joins two datasets, DBLP and CSX, based on their 
authors.
+ *                  DBLP has a secondary btree index on authors, and given the 
'indexnl' hint
+ *                  we expect the join to be transformed into an indexed 
nested-loop join.
+ *                  This is an index-only plan since we are using the
+ *                  secondary key field condition and returning only PK and SK 
fields.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type DBLPType as closed {
+  id: int64,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+};
+
+create type CSXType as closed {
+  id: int64,
+  csxid: string,
+  title: string,
+  authors: string,
+  misc: string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+create dataset CSX(CSXType) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp
new file mode 100644
index 0000000..e082c6e
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 DBLP
+using localfs
+(("path"="asterix_nc1://data/pub-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":"));
+
+load dataset CSX
+using localfs
+(("path"="asterix_nc1://data/pub-small/csx-small-id.txt"),("format"="delimited-text"),("delimiter"=":"),("quote"="\u0000"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp
new file mode 100644
index 0000000..30aa05e
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.3.ddl.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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;
+
+create index authors_index on DBLP(authors);
+create index authors_index on CSX(authors);
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.query.sqlpp
new file mode 100644
index 0000000..f1b2162
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-equi-join_04/btree-secondary-equi-join_04.4.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 a.id as aid, b.id as bid, b.title
+from DBLP a, CSX b
+where a.authors /*+ indexnl */ = b.authors
+order by aid, bid;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
new file mode 100644
index 0000000..c53da0e
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * 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    : 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.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type AddressType as open {
+  number: int64,
+  street: string,
+  city: string
+};
+
+create type CustomerType as closed {
+  cid: int64,
+  name: string,
+  cashBack: int64,
+  age: int64?,
+  address: AddressType?,
+  lastorder: {
+    oid: int64,
+    total: float
+  }
+};
+
+create type OrderType as open {
+  oid: int64,
+  cid: int64,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float,
+  items: [int64]
+};
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key oid;
+
+create index CustomerID_idx on Orders(cid);
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
new file mode 100644
index 0000000..89bc827
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 Customers
+using localfs
+(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+load dataset Orders
+using localfs
+(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
new file mode 100644
index 0000000..494434a
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-primary-equi-join_01/btree-secondary-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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 value count(*) from (
+select o.oid as oid, c.cid as cid
+from Orders o, Customers c
+where o.cid < 800 and o.cid /*+ indexnl */ = c.cid
+) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
new file mode 100644
index 0000000..9042f33
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
@@ -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    : 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 Orders' secondary 
index.
+ *                  Each branch (outer and inner) will be transformed as an 
index-only plan.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type AddressType as open {
+  number: int64,
+  street: string,
+  city: string
+};
+
+create type CustomerType as closed {
+  cid: int64,
+  name: string,
+  cashBack: int64,
+  age: int64?,
+  address: AddressType?,
+  lastorder: {
+    oid: int64,
+    total: float
+  }
+};
+
+create type OrderType as open {
+  oid: int64,
+  cid: int64,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float,
+  items: [int64]
+};
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key oid;
+
+create index CustomerID_idx on Orders(cid);
+create index Cashback_idx on Customers(cashBack);

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
new file mode 100644
index 0000000..dc5951f
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 Customers
+using localfs
+(("path"="asterix_nc1://data/nontagged/customerData2.json"),("format"="adm"));
+
+load dataset Orders
+using localfs
+(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
new file mode 100644
index 0000000..8e9417a
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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 value count(*) from (
+select o.oid as oid, c.cid as cid
+from Orders o, Customers c
+where o.cid < 100000 and o.cid /*+ indexnl */ = c.cashBack
+) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
new file mode 100644
index 0000000..fdc6a6c
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.1.ddl.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * 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    : 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.
+ *                  This is not an index-only plan since a single secondary 
index can't
+ *                  cover the whole search predicate for the outer relation 
even when we exclude the join condition.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type AddressType as open {
+  number: int64,
+  street: string,
+  city: string
+};
+
+create type CustomerType as closed {
+  cid: int64,
+  name: string,
+  cashBack: int64,
+  age: int64?,
+  address: AddressType?,
+  lastorder: {
+    oid: int64,
+    total: float
+  }
+};
+
+create type OrderType as open {
+  oid: int64,
+  cid: int64,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float,
+  items: [int64]
+};
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key oid;
+
+create index CustomerID_idx on Orders(cid);
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
new file mode 100644
index 0000000..89bc827
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 Customers
+using localfs
+(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+load dataset Orders
+using localfs
+(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
new file mode 100644
index 0000000..b1160bb
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-secondary-non-indexonly-plan-to-primary-equi-join_01.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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 value count(*) from (
+select o.oid as oid, c.cid as cid
+from Orders o, Customers c
+where o.cid < 800 and o.total < 10000 and o.cid /*+ indexnl */ = c.cid
+) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
new file mode 100644
index 0000000..3e1d2cb
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.1.ddl.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * 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    : 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 Orders' secondary 
index.
+ *                  Inner branch will be transformed as an index-only plan.
+ *                  Outer branch cannot be transformed as an index-only plan 
as an index can't cover
+ *                  all search predicates even excluding the join condition.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type AddressType as open {
+  number: int64,
+  street: string,
+  city: string
+};
+
+create type CustomerType as closed {
+  cid: int64,
+  name: string,
+  cashBack: int64,
+  age: int64?,
+  address: AddressType?,
+  lastorder: {
+    oid: int64,
+    total: float
+  }
+};
+
+create type OrderType as open {
+  oid: int64,
+  cid: int64,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float,
+  items: [int64]
+};
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key oid;
+
+create index CustomerID_idx on Orders(cid);
+create index Cashback_idx on Customers(cashBack);

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
new file mode 100644
index 0000000..dc5951f
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 Customers
+using localfs
+(("path"="asterix_nc1://data/nontagged/customerData2.json"),("format"="adm"));
+
+load dataset Orders
+using localfs
+(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
new file mode 100644
index 0000000..63c94d1
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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 value count(*) from (
+select o.oid as oid, c.cid as cid
+from Orders o, Customers c
+where o.cid < 100000 and o.total >= 0 and o.cid /*+ indexnl */ = c.cashBack
+) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp
deleted file mode 100644
index bde7f36..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.1.ddl.sqlpp
+++ /dev/null
@@ -1,48 +0,0 @@
-/*
- * 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    : Joins two datasets on the intersection of their point 
attributes.
- *                  The dataset 'MyData1' has an RTree index, and we expect the
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-drop  dataverse test if exists;
-create  dataverse test;
-
-use test;
-
-
-create type test.MyRecord as
- closed {
-  id : bigint,
-  point : point,
-  kwds : string,
-  line1 : line,
-  line2 : line,
-  poly1 : polygon,
-  poly2 : polygon,
-  rec : rectangle,
-  circle : circle
-};
-
-create  dataset MyData1(MyRecord) primary key id;
-
-create  dataset MyData2(MyRecord) primary key id;
-

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp
deleted file mode 100644
index 21b9b96..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.2.update.sqlpp
+++ /dev/null
@@ -1,32 +0,0 @@
-/*
- * 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    : Joins two datasets on the intersection of their point 
attributes.
- *                  The dataset 'MyData1' has an RTree index, and we expect the
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-use test;
-
-
-load  dataset MyData1 using localfs 
((`path`=`asterix_nc1://data/spatial/spatialData.json`),(`format`=`adm`)) 
pre-sorted;
-
-load  dataset MyData2 using localfs 
((`path`=`asterix_nc1://data/spatial/spatialData.json`),(`format`=`adm`)) 
pre-sorted;
-

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp
deleted file mode 100644
index 01943c7..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.3.ddl.sqlpp
+++ /dev/null
@@ -1,30 +0,0 @@
-/*
- * 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    : Joins two datasets on the intersection of their point 
attributes.
- *                  The dataset 'MyData1' has an RTree index, and we expect the
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-use test;
-
-
-create  index rtree_index  on MyData1 (point) type rtree;
-

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp
deleted file mode 100644
index 0cf19a3..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point/rtree-spatial-intersect-point.4.query.sqlpp
+++ /dev/null
@@ -1,34 +0,0 @@
-/*
- * 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    : Joins two datasets on the intersection of their point 
attributes.
- *                  The dataset 'MyData1' has an RTree index, and we expect the
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-use test;
-
-
-select element {'aid':a.id,'bid':b.id,'apt':a.point,'bp':b.point}
-from  MyData1 as a,
-      MyData2 as b
-where (test.`spatial-intersect`(a.point,b.point) and (a.id != b.id))
-order by a.id,b.id
-;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp
new file mode 100644
index 0000000..bde7f36
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.1.ddl.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The dataset 'MyData1' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : bigint,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle,
+  circle : circle
+};
+
+create  dataset MyData1(MyRecord) primary key id;
+
+create  dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp
new file mode 100644
index 0000000..21b9b96
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.2.update.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The dataset 'MyData1' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use test;
+
+
+load  dataset MyData1 using localfs 
((`path`=`asterix_nc1://data/spatial/spatialData.json`),(`format`=`adm`)) 
pre-sorted;
+
+load  dataset MyData2 using localfs 
((`path`=`asterix_nc1://data/spatial/spatialData.json`),(`format`=`adm`)) 
pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.sqlpp
new file mode 100644
index 0000000..01943c7
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.3.ddl.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.
+ */
+/*
+ * Description    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The dataset 'MyData1' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use test;
+
+
+create  index rtree_index  on MyData1 (point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp
new file mode 100644
index 0000000..0cf19a3
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_01/rtree-spatial-intersect-point_01.4.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The dataset 'MyData1' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use test;
+
+
+select element {'aid':a.id,'bid':b.id,'apt':a.point,'bp':b.point}
+from  MyData1 as a,
+      MyData2 as b
+where (test.`spatial-intersect`(a.point,b.point) and (a.id != b.id))
+order by a.id,b.id
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.sqlpp
new file mode 100644
index 0000000..65cd32e
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.1.ddl.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.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp
new file mode 100644
index 0000000..9116663
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.sqlpp
new file mode 100644
index 0000000..a9f8edc
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.3.ddl.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.
+ */
+
+/*
+ * Description    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use test;
+
+create index rtree_index on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp
new file mode 100644
index 0000000..e43f3d2
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_02/rtree-spatial-intersect-point_02.4.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The inner dataset 'MyData2' has an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+use test;
+
+select element {'aid':a.id,'bid':b.id,'apoint':a.point,'bpoint':b.point}
+from  MyData1 as a,
+      MyData2 as b
+where test.`spatial-intersect`(a.point,b.point)
+order by a.id,b.id
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp
new file mode 100644
index 0000000..e004040
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  Both inner and outer dataset have an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp
new file mode 100644
index 0000000..9116663
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp
new file mode 100644
index 0000000..0595f2d
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.3.ddl.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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;
+
+create index rtree_index1 on MyData1(point) type rtree;
+create index rtree_index2 on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp
new file mode 100644
index 0000000..a241f6c
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_03/rtree-spatial-intersect-point_03.4.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 {'aid':a.id,'bid':b.id,'apoint':a.point,'bpoint':b.point}
+from  MyData1 as a,
+      MyData2 as b
+where test.`spatial-intersect`(a.point,b.point)
+order by a.id,b.id
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp
new file mode 100644
index 0000000..94d2c2d
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  Both inner and outer dataset have an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp
new file mode 100644
index 0000000..9116663
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) 
pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp
new file mode 100644
index 0000000..9506862
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.3.ddl.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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;
+
+create index rtree_index1 on MyData1(poly1) type rtree;
+create index rtree_index2 on MyData2(point) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp
new file mode 100644
index 0000000..b3d9221
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_04/rtree-spatial-intersect-point_04.4.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 {'aid':a.id,'bid':b.id,'apoly1':a.poly1,'bpoint':b.point}
+from  MyData1 as a,
+      MyData2 as b
+where test.`spatial-intersect`(a.poly1,b.point)
+order by a.id,b.id
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp
new file mode 100644
index 0000000..94d2c2d
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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    : Joins two datasets on the intersection of their point 
attributes.
+ *                  Both inner and outer dataset have an RTree index, and we 
expect the
+ *                  join to be transformed into an indexed nested-loop join
+ *                  by using an RTree index from the inner dataset.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData1(MyRecord) primary key id;
+create dataset MyData2(MyRecord) primary key id;
+

Reply via email to