You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by mh...@apache.org on 2018/12/09 22:57:57 UTC
[4/6] asterixdb git commit: [NO ISSUE][IDX][COMP] Creating an open
index on non-declared fields
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/11.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/11.plan
new file mode 100644
index 0000000..9c6b8d7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/11.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- SORT_MERGE_EXCHANGE [$$27(ASC) ] |PARTITIONED|
+ -- STABLE_SORT [$$27(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$34(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/12.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/12.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/12.plan
new file mode 100644
index 0000000..00280ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/nested-open-index/btree-index/non-enforced-composite-key/12.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- SORT_MERGE_EXCHANGE [$$27(ASC) ] |PARTITIONED|
+ -- STABLE_SORT [$$27(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- STREAM_SELECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- INTERSECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$33(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$37(ASC)] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.1.ddl.sqlpp
new file mode 100644
index 0000000..639fbd8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.1.ddl.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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 : Index join for nested composite non-enforced indexes
+* Expected Res : Success
+*/
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen1(TestOpenType) primary key c_id;
+
+create dataset TestOpen2(TestOpenType) primary key c_id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.10.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.10.query.sqlpp
new file mode 100644
index 0000000..0215367
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.10.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.
+ */
+ /*
+ * Description : Index join because there's a hint and the probe type is known. Promotable numeric type.
+ * Result : Success
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where to_double(t1.nested.c_i8) /*+ indexnl */ = t2.nested.c_i8
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.11.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.11.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.11.ddl.sqlpp
new file mode 100644
index 0000000..548e632
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.11.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.2.update.sqlpp
new file mode 100644
index 0000000..10fa158
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.2.update.sqlpp
@@ -0,0 +1,103 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into TestOpen1 ({
+ "c_id": 1,
+ "nested": {
+ "c_x": 1,
+ "c_s": "hello",
+ "c_i64": 2,
+ "c_i8": 2,
+ "c_d": 2,
+ "extra": 99
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 2,
+ "nested": {
+ "c_x": 2,
+ "c_s": 2,
+ "c_i64": "2",
+ "c_i8": 2.5,
+ "c_d": 3,
+ "extra": 98
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 3,
+ "nested": {
+ "c_x": 3,
+ "c_s": "world",
+ "c_i64": 2,
+ "c_i8": 4,
+ "c_d": 3.125,
+ "extra": 97
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 4,
+ "nested": {
+ "c_x": 4,
+ "c_s": null,
+ "c_i64": null,
+ "c_i8": 500,
+ "c_d": 3.25,
+ "extra": 996
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 5,
+ "nested": {
+ "c_x": 5,
+ "c_s": "hello",
+ "c_i64": 2.25,
+ "c_i8": 10000.25,
+ "c_d": 3.5,
+ "extra": 9
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 6,
+ "nested": {
+ "c_x": 6,
+ "c_s": false,
+ "c_i64": false,
+ "c_i8": 2e100,
+ "c_d": 2e100,
+ "extra": 91
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 7,
+ "nested": {
+ "c_x": 7,
+ "c_s": "world",
+ "c_i64": 3,
+ "extra": 99
+ }
+});
+insert into TestOpen1 ({
+ "c_id": 8,
+ "nested": {
+ "c_x": 8,
+ "extra": 99
+ }
+});
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.3.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.3.update.sqlpp
new file mode 100644
index 0000000..f3d6e8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.3.update.sqlpp
@@ -0,0 +1,103 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into TestOpen2 ({
+ "c_id": 101,
+ "nested": {
+ "c_x": 101,
+ "c_s": "hello",
+ "c_i64": 2,
+ "c_i8": 2,
+ "c_d": 2,
+ "extra": 97
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 102,
+ "nested": {
+ "c_x": 102,
+ "c_s": 2,
+ "c_i64": "2",
+ "c_i8": 2.5,
+ "c_d": 3,
+ "extra": 96
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 103,
+ "nested": {
+ "c_x": 103,
+ "c_s": "world",
+ "c_i64": 2,
+ "c_i8": 4,
+ "c_d": 3.125,
+ "extra": 95
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 104,
+ "nested": {
+ "c_x": 104,
+ "c_s": null,
+ "c_i64": null,
+ "c_i8": 500,
+ "c_d": 3.25,
+ "extra": 94
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 105,
+ "nested": {
+ "c_x": 105,
+ "c_s": "hello",
+ "c_i64": 2.25,
+ "c_i8": 10000.25,
+ "c_d": 3.5,
+ "extra": 93
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 106,
+ "nested": {
+ "c_x": 106,
+ "c_s": false,
+ "c_i64": false,
+ "c_i8": 2e100,
+ "c_d": 2e100,
+ "extra": 91
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 107,
+ "nested": {
+ "c_x": 107,
+ "c_s": "world",
+ "c_i64": 3,
+ "extra": 97
+ }
+});
+insert into TestOpen2 ({
+ "c_id": 108,
+ "nested": {
+ "c_x": 108,
+ "extra": 97
+ }
+});
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.4.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.4.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.4.ddl.sqlpp
new file mode 100644
index 0000000..c8be482
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.4.ddl.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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 idx_t1_s on TestOpen1(nested.c_s:string, nested.extra: int);
+
+create index idx_t1_i64 on TestOpen1(nested.c_i64:int64, nested.extra: int);
+
+create index idx_t1_i8 on TestOpen1(nested.c_i8:int8, nested.extra: int);
+
+create index idx_t1_d on TestOpen1(nested.c_d:double, nested.extra: int);
+
+/********************************************/
+
+create index idx_t2_s on TestOpen2(nested.c_s:string, nested.extra: int);
+
+create index idx_t2_i64 on TestOpen2(nested.c_i64:int64, nested.extra: int);
+
+create index idx_t2_i8 on TestOpen2(nested.c_i8:int8, nested.extra: int);
+
+create index idx_t2_d on TestOpen2(nested.c_d:double, nested.extra: int);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.5.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.5.query.sqlpp
new file mode 100644
index 0000000..1301e74
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.5.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 : No index join because there's no hint, non-enforced nested composite key index
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where t1.nested.c_s = t2.nested.c_s
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.6.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.6.query.sqlpp
new file mode 100644
index 0000000..a91e785
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.6.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 : No index join because the probe type is unknown, non-enforced nested composite key index
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where t1.nested.c_s /*+ indexnl */ = t2.nested.c_s
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.7.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.7.query.sqlpp
new file mode 100644
index 0000000..a3c2a88
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.7.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 : No index join because there's no hint, non-enforced nested composite key index
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where to_string(t1.nested.c_s) = t2.nested.c_s
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.8.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.8.query.sqlpp
new file mode 100644
index 0000000..f8a990e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.8.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 : Index join because there's a hint and the probe type is known (string), non-enforced nested composite
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where to_string(t1.nested.c_s) /*+ indexnl */ = t2.nested.c_s
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.9.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.9.query.sqlpp
new file mode 100644
index 0000000..40f27ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join-2/btree-secondary-non-enforced-equi-join-2.9.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 : Index join because there's a hint and the probe type is known (bigint), non-enforced nested composite
+ */
+
+use test;
+
+select t1.nested.c_x as c1, t2.nested.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where to_bigint(t1.nested.c_i64) /*+ indexnl */ = t2.nested.c_i64
+order by t1.nested.c_x, t2.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.1.ddl.sqlpp
new file mode 100644
index 0000000..2d26551
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.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, DBLP and CSX, based on their authors.
+ * CSX has a nested composite open index, and given the 'indexnl' hint we expect the join to
+ * be transformed into an indexed nested-loop join.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type test.DBLPTypetmp as
+ closed {
+ id : bigint,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+};
+
+create type test.CSXTypetmp as
+ closed {
+ id : bigint,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+};
+
+create type test.DBLPType as {
+ id: int
+};
+
+create type test.CSXType as {
+ id: int
+};
+
+create dataset DBLPtmp(DBLPTypetmp) primary key id;
+
+create dataset CSXtmp(CSXTypetmp) primary key id;
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.2.update.sqlpp
new file mode 100644
index 0000000..b6f719a
Binary files /dev/null and b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.2.update.sqlpp differ
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.3.ddl.sqlpp
new file mode 100644
index 0000000..af7af0d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.3.ddl.sqlpp
@@ -0,0 +1,22 @@
+/*
+ * 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 idx on CSX (nested.authors: string, nested.csxid: string);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.4.query.sqlpp
new file mode 100644
index 0000000..6ec0578
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.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 element {'aid':a.nested.id,'bid':b.nested.id,'authors':a.nested.authors}
+from DBLP as a, CSX as b
+where to_string(a.nested.authors) /*+ indexnl */ = b.nested.authors
+order by a.nested.id, b.nested.id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.5.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.5.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.5.ddl.sqlpp
new file mode 100644
index 0000000..ff41019
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-join/btree-secondary-non-enforced-equi-join/btree-secondary-non-enforced-equi-join.5.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.1.ddl.sqlpp
new file mode 100644
index 0000000..9c7b4e5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.1.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+* Description : Index selection for composite nested non-enforced indexes
+* Expected Res : Success
+*/
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen(TestOpenType) primary key c_id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.10.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.10.query.sqlpp
new file mode 100644
index 0000000..7d4c7da
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.10.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_d >= 3.25
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.11.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.11.query.sqlpp
new file mode 100644
index 0000000..e8ff5b1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.11.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i8 > 499 and t.nested.c_i8 < 99999
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.12.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.12.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.12.ddl.sqlpp
new file mode 100644
index 0000000..07298b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.12.ddl.sqlpp
@@ -0,0 +1,19 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+drop dataverse test if exists;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.2.update.sqlpp
new file mode 100644
index 0000000..c4046a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.2.update.sqlpp
@@ -0,0 +1,103 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into TestOpen ({
+ "c_id": 1,
+ "nested": {
+ "c_x": 1,
+ "c_s": "hello",
+ "c_i64": 2,
+ "c_i8": 2,
+ "c_d": 2,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 2,
+ "nested": {
+ "c_x": 2,
+ "c_s": 2,
+ "c_i64": "2",
+ "c_i8": 2.5,
+ "c_d": 3,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 3,
+ "nested": {
+ "c_x": 3,
+ "c_s": "world",
+ "c_i64": 2,
+ "c_i8": 4,
+ "c_d": 3.125,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 4,
+ "nested": {
+ "c_x": 4,
+ "c_s": null,
+ "c_i64": null,
+ "c_i8": 500,
+ "c_d": 3.25,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 5,
+ "nested": {
+ "c_x": 5,
+ "c_s": "hello",
+ "c_i64": 2.25,
+ "c_i8": 10000.25,
+ "c_d": 3.5,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 6,
+ "nested": {
+ "c_x": 6,
+ "c_s": false,
+ "c_i64": false,
+ "c_i8": 2e100,
+ "c_d": 2e100,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 7,
+ "nested": {
+ "c_x": 7,
+ "c_s": "world",
+ "c_i64": 3,
+ "c_extra": "yes"
+ }
+});
+insert into TestOpen ({
+ "c_id": 8,
+ "nested": {
+ "c_x": 8,
+ "c_extra": "yes"
+ }
+});
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.3.ddl.sqlpp
new file mode 100644
index 0000000..5a3d25a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.3.ddl.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 idx_s on TestOpen(nested.c_s:string, nested.c_extra: string);
+
+create index idx_i64 on TestOpen(nested.c_i64:int64, nested.c_extra: string);
+
+create index idx_i8 on TestOpen(nested.c_i8:int8, nested.c_extra: string);
+
+create index idx_d on TestOpen(nested.c_d:double, nested.c_extra: string);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.4.query.sqlpp
new file mode 100644
index 0000000..2462bda
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_s = 'world'
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.5.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.5.query.sqlpp
new file mode 100644
index 0000000..0b59642
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.5.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i64 = 2
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.6.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.6.query.sqlpp
new file mode 100644
index 0000000..99a596f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.6.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i64 > 2
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.7.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.7.query.sqlpp
new file mode 100644
index 0000000..ce16148
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.7.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i64 > 2.0
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.8.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.8.query.sqlpp
new file mode 100644
index 0000000..c0b4563
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.8.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i8 > 2
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.9.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.9.query.sqlpp
new file mode 100644
index 0000000..c5cc2c1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-01/non-enforced-01.9.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i8 > 2.5
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.1.ddl.sqlpp
new file mode 100644
index 0000000..079b5ca
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.1.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+* Description : Index selection for composite nested non-enforced indexes multiple indexes match.
+* Expected Res : Success
+*/
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen(TestOpenType) primary key c_id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.2.update.sqlpp
new file mode 100644
index 0000000..2ff5578
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.2.update.sqlpp
@@ -0,0 +1,95 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into TestOpen ({
+ "c_id": 1,
+ "c_x": 1,
+ "nested": {
+ "c_s": "hello",
+ "c_i64": 2,
+ "c_i8": 2,
+ "c_d": 2
+ }
+});
+insert into TestOpen ({
+ "c_id": 2,
+ "nested": {
+ "c_x": 2,
+ "c_s": 2,
+ "c_i64": "2",
+ "c_i8": 2.5,
+ "c_d": 3
+ }
+});
+insert into TestOpen ({
+ "c_id": 3,
+ "nested": {
+ "c_x": 3,
+ "c_s": "world",
+ "c_i64": 2,
+ "c_i8": 4,
+ "c_d": 3.125
+ }
+});
+insert into TestOpen ({
+ "c_id": 4,
+ "nested": {
+ "c_x": 4,
+ "c_s": null,
+ "c_i64": null,
+ "c_i8": 500,
+ "c_d": 3.25
+ }
+});
+insert into TestOpen ({
+ "c_id": 5,
+ "nested": {
+ "c_x": 5,
+ "c_s": "hello",
+ "c_i64": 2.25,
+ "c_i8": 10000.25,
+ "c_d": 3.5
+ }
+});
+insert into TestOpen ({
+ "c_id": 6,
+ "nested": {
+ "c_x": 6,
+ "c_s": false,
+ "c_i64": false,
+ "c_i8": 2e100,
+ "c_d": 2e100
+ }
+});
+insert into TestOpen ({
+ "c_id": 7,
+ "nested": {
+ "c_x": 7,
+ "c_s": "world",
+ "c_i64": 3
+ }
+});
+insert into TestOpen ({
+ "c_id": 8,
+ "nested": {
+ "c_x": 8
+ }
+});
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.3.ddl.sqlpp
new file mode 100644
index 0000000..85928cb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.3.ddl.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 idx_2 on TestOpen(nested.c_s:string, nested.c_i64:int64);
+
+create index idx_1 on TestOpen(nested.c_s:string, nested.c_d:double);
+
+create index idx_3 on TestOpen(nested.c_i64:int64);
+
+create index idx_4 on TestOpen(nested.c_i8:int8);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.4.query.sqlpp
new file mode 100644
index 0000000..2462bda
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_s = 'world'
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.5.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.5.query.sqlpp
new file mode 100644
index 0000000..b98ee96
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.5.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 t.nested.c_x as res
+from TestOpen t
+where t.nested.c_i8 > 2 and t.nested.c_i64 < 3
+order by t.nested.c_x;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.6.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.6.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.6.ddl.sqlpp
new file mode 100644
index 0000000..548e632
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-02/non-enforced-02.6.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.1.ddl.sqlpp
new file mode 100644
index 0000000..abf2843
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.1.ddl.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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 : Multiple nested composite non-enforced indexes on the same field, created before data loading
+* Expected Res : Success
+*/
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type GleambookUserType as { id: int };
+
+create dataset GleambookUsers(GleambookUserType) primary key id;
+
+create index gbUserSinceIdxDateTime on GleambookUsers(nested.userSince: datetime, nested.alias: string);
+
+create index gbUserSinceIdxInt on GleambookUsers(nested.userSince: int, nested.alias: string);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.2.update.sqlpp
new file mode 100644
index 0000000..9427b77
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.2.update.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;
+
+insert into GleambookUsers
+([
+{ "id":1, "nested": {"alias":"Bram", "userSince":datetime("2010-10-16T10:10:00")} },
+{ "id":2, "nested": {"alias":"Donald", "userSince":"yesterday"} },
+{ "id":3, "nested": {"alias":"Hillery", "userSince":2016} }
+]);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.3.query.sqlpp
new file mode 100644
index 0000000..7c80c09
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.3.query.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;
+
+select value user
+from GleambookUsers user
+where user.nested.userSince > 900;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.4.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.4.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.4.ddl.sqlpp
new file mode 100644
index 0000000..548e632
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-03/non-enforced-03.4.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.1.ddl.sqlpp
new file mode 100644
index 0000000..897df02
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.1.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+* Description : Index selection for non-enforced nested composite key indexes
+* Expected Res : Success
+*/
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen(TestOpenType) primary key c_id;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9a61bd2c/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.2.update.sqlpp
new file mode 100644
index 0000000..65b54fe
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-open-index/index-selection/non-enforced-04/non-enforced-04.2.update.sqlpp
@@ -0,0 +1,102 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+insert into TestOpen ({
+ "c_id": 1,
+ "nested": {
+ "c_x": 1,
+ "c_s": "hello",
+ "c_i64": 2,
+ "c_i8": 2,
+ "c_d": 2
+ }
+});
+insert into TestOpen ({
+ "c_id": 2,
+ "nested": {
+ "c_x": 2,
+ "c_s": 2,
+ "c_i64": "2",
+ "c_i8": 2.5,
+ "c_d": 3
+ }
+});
+insert into TestOpen ({
+ "c_id": 3,
+ "nested": {
+ "c_x": 3,
+ "c_s": "world",
+ "c_i64": 2,
+ "c_i8": 4,
+ "c_d": 3.125
+ }
+});
+insert into TestOpen ({
+ "c_id": 4,
+ "nested": {
+ "c_x": 4,
+ "c_s": null,
+ "c_i64": null,
+ "c_i8": 500,
+ "c_d": 3.25
+ }
+});
+insert into TestOpen ({
+ "c_id": 5,
+ "nested": {
+ "c_x": 5,
+ "c_s": "hello",
+ "c_i64": 2.25,
+ "c_i8": 10000.25,
+ "c_d": 3.5
+ }
+});
+insert into TestOpen ({
+ "c_id": 6,
+ "nested": {
+ "c_x": 6,
+ "c_s": false,
+ "c_i64": false,
+ "c_i8": 2e100,
+ "c_d": 2e100
+ }
+});
+insert into TestOpen ({
+ "c_id": 7,
+ "nested": {
+ "c_x": 7,
+ "c_s": "world",
+ "c_i64": 3
+ }
+});
+insert into TestOpen ({
+ "c_id": 8,
+ "nested": {
+ "c_x": 8
+ }
+});
+insert into TestOpen ({
+ "c_id": 9,
+ "nested": {
+ "c_x": 9,
+ "c_d": 3.25
+ }
+});
\ No newline at end of file