You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by mb...@apache.org on 2018/03/10 21:50:57 UTC
[4/8] asterixdb git commit: [NO ISSUE][COMP][RT] Rename index-only
test case directory
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp
deleted file mode 100644
index fdc6a6c..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp
+++ /dev/null
@@ -1,66 +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 : 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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01.2.update.sqlpp
deleted file mode 100644
index 89bc827..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.2.update.sqlpp
+++ /dev/null
@@ -1,29 +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.
- */
-
-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01.3.query.sqlpp
deleted file mode 100644
index b1160bb..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-primary-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.3.query.sqlpp
+++ /dev/null
@@ -1,26 +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.
- */
-
-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp
deleted file mode 100644
index 3e1d2cb..0000000
--- 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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp
+++ /dev/null
@@ -1,67 +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 : 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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp
deleted file mode 100644
index dc5951f..0000000
--- 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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp
+++ /dev/null
@@ -1,29 +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.
- */
-
-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-secondary-non-indexonly-plan-to-secondary-indexonly-plan-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp
deleted file mode 100644
index 63c94d1..0000000
--- 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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp
+++ /dev/null
@@ -1,26 +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.
- */
-
-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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-sidx-idxonly-to-pidx-equi-join_01/btree-sidx-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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-sidx-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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-sidx-non-idxonly-to-pidx-equi-join_01/btree-sidx-non-idxonly-to-pidx-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/btree-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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-sidx-non-idxonly-to-sidx-idxonly-equi-join_01/btree-sidx-non-idxonly-to-sidx-idxonly-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/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
new file mode 100644
index 0000000..4920706
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and
+ * another for secondary index in index subtree. For inner branch, this is an index-only plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.2.update.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;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp
new file mode 100644
index 0000000..65d953c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-join-btree-sidx3-idxonly/probe-pidx-join-btree-sidx3-idxonly.3.query.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;
+
+select value count(*) from (
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where t1.countA /*+ indexnl */ = t2.countB
+ )}
+from TweetMessages as t1
+where t1.tweetid < test.bigint('10')) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
deleted file mode 100644
index 4920706..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.1.ddl.sqlpp
+++ /dev/null
@@ -1,56 +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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and
- * another for secondary index in index subtree. For inner branch, this is an index-only plan.
- * Expected Res : Success
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp
deleted file mode 100644
index cb2a156..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.2.update.sqlpp
+++ /dev/null
@@ -1,24 +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.
- */
-
-use test;
-
-load dataset TweetMessages
-using localfs
-(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp
deleted file mode 100644
index 65d953c..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-join-btree-sidx3-idxonly.3.query.sqlpp
+++ /dev/null
@@ -1,29 +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.
- */
-
-use test;
-
-select value count(*) from (
-select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
- select element {'tweetid2':t2.tweetid,'count2':t2.countB}
- from TweetMessages as t2
- where t1.countA /*+ indexnl */ = t2.countB
- )}
-from TweetMessages as t1
-where t1.tweetid < test.bigint('10')) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
new file mode 100644
index 0000000..adffdfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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 : Test that left-outer-join may use an available primary index in the index subtree.
+ * In the probe side, this is an index-only plan since a secondary index-search
+ * will fetch all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.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;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp
new file mode 100644
index 0000000..7d0f895
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+
+select value count(*) from (
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where t1.countA /*+ indexnl */ = t2.tweetid
+ )}
+from TweetMessages as t1
+where t1.countA > 0) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
new file mode 100644
index 0000000..c7685a8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description : Test that left-outer-join may use an available secondary index in the index subtree.
+ * In the probe side, this is an index-only plan since a secondary index-search will
+ * fetch all fields that are required for the plan.
+ * In the inner branch, this is also an index-only plan since a secondary index-search will
+ * fetch all fields that are required for the branch.
+ * All other variables will be propagated from the outer branch.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.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;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp
new file mode 100644
index 0000000..6e2e769
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.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;
+
+select value count(*) from (
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where t1.countA /*+ indexnl */ = t2.countB
+ )}
+from TweetMessages as t1
+where t1.countA > 0) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
deleted file mode 100644
index adffdfd..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.1.ddl.sqlpp
+++ /dev/null
@@ -1,57 +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 : Test that left-outer-join may use an available primary index in the index subtree.
- * In the probe side, this is an index-only plan since a secondary index-search
- * will fetch all fields that are required for the plan.
- * Expected Res : Success
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp
deleted file mode 100644
index cb2a156..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.2.update.sqlpp
+++ /dev/null
@@ -1,24 +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.
- */
-
-use test;
-
-load dataset TweetMessages
-using localfs
-(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.sqlpp
deleted file mode 100644
index 7d0f895..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-idxonly-join-btree-pidx1.3.query.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.
- */
-
-use test;
-
-
-select value count(*) from (
-select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
- select element {'tweetid2':t2.tweetid,'count2':t2.countB}
- from TweetMessages as t2
- where t1.countA /*+ indexnl */ = t2.tweetid
- )}
-from TweetMessages as t1
-where t1.countA > 0) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
deleted file mode 100644
index c7685a8..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.1.ddl.sqlpp
+++ /dev/null
@@ -1,60 +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 : Test that left-outer-join may use an available secondary index in the index subtree.
- * In the probe side, this is an index-only plan since a secondary index-search will
- * fetch all fields that are required for the plan.
- * In the inner branch, this is also an index-only plan since a secondary index-search will
- * fetch all fields that are required for the branch.
- * All other variables will be propagated from the outer branch.
- * Expected Res : Success
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp
deleted file mode 100644
index cb2a156..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.2.update.sqlpp
+++ /dev/null
@@ -1,24 +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.
- */
-
-use test;
-
-load dataset TweetMessages
-using localfs
-(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp
deleted file mode 100644
index 6e2e769..0000000
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-idxonly-join-btree-sidx1-idxonly.3.query.sqlpp
+++ /dev/null
@@ -1,29 +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.
- */
-
-use test;
-
-select value count(*) from (
-select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
- select element {'tweetid2':t2.tweetid,'count2':t2.countB}
- from TweetMessages as t2
- where t1.countA /*+ indexnl */ = t2.countB
- )}
-from TweetMessages as t1
-where t1.countA > 0) a;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/211cbe1b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-idxonly-join-btree-pidx1/probe-sidx-btree-non-idxonly-join-btree-pidx1.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-idxonly-join-btree-pidx1/probe-sidx-btree-non-idxonly-join-btree-pidx1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-idxonly-join-btree-pidx1/probe-sidx-btree-non-idxonly-join-btree-pidx1.1.ddl.sqlpp
new file mode 100644
index 0000000..3e85829
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-idxonly-join-btree-pidx1/probe-sidx-btree-non-idxonly-join-btree-pidx1.1.ddl.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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 : Test that left-outer-join may use an available primary index in the index subtree.
+ * In the probe side, this is a non index-only plan since a secondary index-search can't cover
+ * all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use 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;