asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [37/51] [partial] incubator-asterixdb git commit: SQL++ parser: 1. refactored asterix-aql to become asterix-lang-common and asterix-lang-aql, where the former is the common part for different languages; 2. added asterix-lang-sqlpp on top of asterix-lang-
Date Fri, 30 Oct 2015 23:16:45 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-neg_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-neg_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-neg_01.sqlpp
new file mode 100644
index 0000000..fcb60c3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join-neg_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : This is a negative test, mis-spelt/incorrect HINT should result in
+ *                a plan not using an indexed-nested loops join strategy. We expect a hash join.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-neg_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 = y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_01.sqlpp
new file mode 100644
index 0000000..2602ff3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 /*+ indexnl */  = y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_02.sqlpp
new file mode 100644
index 0000000..edac5f3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_02.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_02.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key2 /*+ indexnl */  = y.nested.key1)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_03.sqlpp
new file mode 100644
index 0000000..75e669a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_03.sqlpp
@@ -0,0 +1,81 @@
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+  number : int32,
+  street : string,
+  city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+  cid : int32,
+  name : string,
+  age : int32?,
+  address : AddressType?,
+  lastorder : {
+      oid : int32,
+      total : float
+  }
+
+}
+
+create type test.OrderTypetmp as
+ closed {
+  oid : int32,
+  cid : int32,
+  orderstatus : string,
+  orderpriority : string,
+  clerk : string,
+  total : float
+}
+
+create type test.CustomerType as
+ closed {
+  nested : CustomerTypetmp
+}
+
+create type test.OrderType as
+ closed {
+  nested : OrderTypetmp
+}
+
+create  table Customers(CustomerType) primary key nested.cid;
+
+create  table Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_04.adm"
+select element {'customer':c.nested,'order':o.nested}
+from  Customers as c,
+      Orders as o
+where (c.nested.cid /*+ indexnl */  = o.nested.cid)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_04.sqlpp
new file mode 100644
index 0000000..eb25a0c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_04.sqlpp
@@ -0,0 +1,81 @@
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+  number : int32,
+  street : string,
+  city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+  cid : int32,
+  name : string,
+  age : int32?,
+  address : AddressType?,
+  lastorder : {
+      oid : int32,
+      total : float
+  }
+
+}
+
+create type test.OrderTypetmp as
+ closed {
+  oid : int32,
+  cid : int32,
+  orderstatus : string,
+  orderpriority : string,
+  clerk : string,
+  total : float
+}
+
+create type test.CustomerType as
+ closed {
+  nested : CustomerTypetmp
+}
+
+create type test.OrderType as
+ closed {
+  nested : OrderTypetmp
+}
+
+create  table Customers(CustomerType) primary key nested.cid;
+
+create  table Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_05.adm"
+select element {'customer':c.nested,'order':o.nested}
+from  Orders as o,
+      Customers as c
+where (o.nested.cid /*+ indexnl */  = c.nested.cid)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_05.sqlpp
new file mode 100644
index 0000000..98eee3e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-equi-join_05.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Self-equi joins a dataset, Customers, 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+  number : int32,
+  street : string,
+  city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+  cid : int32,
+  name : string,
+  age : int32?,
+  address : AddressType?,
+  lastorder : {
+      oid : int32,
+      total : float
+  }
+
+}
+
+create type test.CustomerType as
+ closed {
+  nested : CustomerTypetmp
+}
+
+create  table Customers(CustomerType) primary key nested.cid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_06.adm"
+select element {'customer1':c1.nested,'customer2':c2.nested}
+from  Customers as c1,
+      Customers as c2
+where (c1.nested.cid /*+ indexnl */  = c2.nested.cid)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-ge-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-ge-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-ge-join_01.sqlpp
new file mode 100644
index 0000000..a93546f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-ge-join_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-ge-join_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 /*+ indexnl */  >= y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-gt-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-gt-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-gt-join_01.sqlpp
new file mode 100644
index 0000000..e478f8f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-gt-join_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-gt-join_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 /*+ indexnl */  > y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-le-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-le-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-le-join_01.sqlpp
new file mode 100644
index 0000000..ef88ee3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-le-join_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-le-join_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 /*+ indexnl */  <= y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-lt-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-lt-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-lt-join_01.sqlpp
new file mode 100644
index 0000000..e72eb83
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/primary-lt-join_01.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop  database test1 if exists;
+create  database test1;
+
+create type test1.TestTypetmp as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create type test1.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table test1.DsOne(TestType) primary key nested.key1;
+
+create  table test1.DsTwo(TestType) primary key nested.key1;
+
+write output to nc1:"rttest/btree-index-join_primary-lt-join_01.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.nested.key1 /*+ indexnl */  < y.nested.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multiindex.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
new file mode 100644
index 0000000..ddfcffb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
@@ -0,0 +1,85 @@
+/*
+ * 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, FacebookUsers and FacebookMessages, based on their user's id.
+ *                  We first expect FacebookUsers' primary index to be used
+ *                  to satisfy the range condition on it's primary key.
+ *                  FacebookMessages has a secondary btree index on author-id-copy, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.EmploymentType as
+ closed {
+  "organization-name" : string,
+  "start-date" : date,
+  "end-date" : date?
+}
+
+create type test.FacebookUserTypetmp as
+ closed {
+  id : int32,
+  "id-copy" : int32,
+  alias : string,
+  name : string,
+  "user-since" : datetime,
+  "user-since-copy" : datetime,
+  "friend-ids" : {{int32}},
+  employment : [EmploymentType]
+}
+
+create type test.FacebookMessageTypetmp as
+ closed {
+  "message-id" : int32,
+  "message-id-copy" : int32,
+  "author-id" : int32,
+  "author-id-copy" : int32,
+  "in-response-to" : int32?,
+  "sender-location" : point?,
+  message : string
+}
+
+create type test.FacebookUserType as
+ closed {
+  nested : FacebookUserTypetmp
+}
+
+create type test.FacebookMessageType as
+ closed {
+  nested : FacebookMessageTypetmp
+}
+
+create  table FacebookUsers(FacebookUserType) primary key nested.id;
+
+create  table FacebookMessages(FacebookMessageType) primary key nested."message-id";
+
+create  index fbmIdxAutId  if not exists  on FacebookMessages (nested."author-id-copy") type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multiindex.adm"
+select element {'fbu-ID':user.nested.id,'fbm-auth-ID':message.nested."author-id",'uname':user.nested.name,'message':message.nested.message}
+from  FacebookUsers as user,
+      FacebookMessages as message
+where ((user.nested.id /*+ indexnl */  = message.nested."author-id-copy") and (user.nested.id >= 11000) and (user.nested.id <= 12000))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multipred.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multipred.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multipred.sqlpp
new file mode 100644
index 0000000..10adfc6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join-multipred.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * 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 title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ *                  We expect the additional predicates to be put into a select above the
+ *                  primary index search.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.DBLPType as
+ closed {
+  nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+  nested : CSXTypetmp
+}
+
+create  table DBLP(DBLPType) primary key nested.id;
+
+create  table CSX(CSXType) primary key nested.id;
+
+create  index title_index  on DBLP (nested.title) type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multipred.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((a.nested.title /*+ indexnl */  = b.nested.title) and (a.nested.authors < b.nested.authors) and (a.nested.misc > b.nested.misc))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join_01.sqlpp
new file mode 100644
index 0000000..41af3b9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index-join/secondary-equi-join_01.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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 title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.DBLPType as
+ closed {
+  nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+  nested : CSXTypetmp
+}
+
+create  table DBLP(DBLPType) primary key nested.id;
+
+create  table CSX(CSXType) primary key nested.id;
+
+create  index title_index  on DBLP (nested.title) type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.nested.title /*+ indexnl */  = b.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-01.sqlpp
new file mode 100644
index 0000000..653591f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-01.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-01.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname > 'Roger')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-02.sqlpp
new file mode 100644
index 0000000..2450dd8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-02.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-02.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname >= 'Susan')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-03.sqlpp
new file mode 100644
index 0000000..721e906
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-03.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-03.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname < 'Isa')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-04.sqlpp
new file mode 100644
index 0000000..b770353
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-04.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-04.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname <= 'Vanpatten')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-05.sqlpp
new file mode 100644
index 0000000..bea6079
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-05.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-05.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname != 'Max')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-06.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-06.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-06.sqlpp
new file mode 100644
index 0000000..5e9feea
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-06.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-06.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.fname = 'Julio')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-07.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-07.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-07.sqlpp
new file mode 100644
index 0000000..b05b895
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-07.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-07.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where (emp.nested.lname = 'Kim')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-08.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-08.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-08.sqlpp
new file mode 100644
index 0000000..9a9f663
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-08.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used in the optimized query plan
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-08.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname = 'Young Seok') and (emp.nested.lname = 'Kim'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-09.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-09.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-09.sqlpp
new file mode 100644
index 0000000..eb5c695
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-09.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-09.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname = 'Julio') or (emp.nested.lname = 'Malaika'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-10.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-10.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-10.sqlpp
new file mode 100644
index 0000000..46a5192
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-10.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-10.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname > 'Alex') and (emp.nested.lname < 'Zach'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-11.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-11.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-11.sqlpp
new file mode 100644
index 0000000..f5b32e7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-11.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan for predicates.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-11.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname > 'Allan') and (emp.nested.lname < 'Zubi'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-12.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-12.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-12.sqlpp
new file mode 100644
index 0000000..1a48bd8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-12.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-12.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname > 'Allan') and (emp.nested.lname = 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-13.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-13.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-13.sqlpp
new file mode 100644
index 0000000..f9a434f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-13.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-13.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname = 'Julio') and (emp.nested.lname < 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-14.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-14.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-14.sqlpp
new file mode 100644
index 0000000..6ce745d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-14.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-14.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname >= 'Michael') and (emp.nested.lname <= 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-15.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-15.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-15.sqlpp
new file mode 100644
index 0000000..93f1835
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-15.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used 
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-15.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname > 'Craig') and (emp.nested.lname > 'Kevin') and (emp.nested.fname < 'Mary') and (emp.nested.lname < 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-16.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-16.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-16.sqlpp
new file mode 100644
index 0000000..aa403aa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-16.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-16.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname >= 'Craig') and (emp.nested.lname >= 'Kevin') and (emp.nested.fname <= 'Mary') and (emp.nested.lname <= 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-17.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-17.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-17.sqlpp
new file mode 100644
index 0000000..c85d28d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-17.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-17.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname <= 'Craig') and (emp.nested.lname > 'Kevin'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-18.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-18.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-18.sqlpp
new file mode 100644
index 0000000..228a3c3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-18.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-18.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp.nested
+from  testdst as emp
+where ((emp.nested.fname != 'Michael') and (emp.nested.lname != 'Carey'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-19.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-19.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-19.sqlpp
new file mode 100644
index 0000000..1e8cbe3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-19.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-19.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname > 'Craig') and (emp.nested.lname > 'Kevin') and (emp.nested.fname <= 'Mary') and (emp.nested.lname <= 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-20.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-20.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-20.sqlpp
new file mode 100644
index 0000000..5eb4acd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-20.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-20.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname,nested.lname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname >= 'Craig') and (emp.nested.lname >= 'Kevin') and (emp.nested.fname < 'Mary') and (emp.nested.lname < 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-21.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-21.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-21.sqlpp
new file mode 100644
index 0000000..053f740
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-21.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-21.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname > 'Max')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-22.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-22.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-22.sqlpp
new file mode 100644
index 0000000..6fd56e4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-22.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-22.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname >= 'Sofia')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-23.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-23.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-23.sqlpp
new file mode 100644
index 0000000..870d33e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-23.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-23.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname < 'Chen')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-24.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-24.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-24.sqlpp
new file mode 100644
index 0000000..71fd2c7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-24.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-24.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname <= 'Julio')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-25.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-25.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-25.sqlpp
new file mode 100644
index 0000000..a7bba9c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/btree-index/btree-primary-25.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-25.adm"
+create type test.TestTypetmp as
+{
+  fname : string,
+  lname : string
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.fname;
+
+select element emp
+from  testdst as emp
+where ((emp.nested.fname > 'Neil') and (emp.nested.fname < 'Roger'))
+;



Mime
View raw message