asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [44/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:52 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_01.sqlpp
new file mode 100644
index 0000000..4628891
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */  = y.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_02.sqlpp
new file mode 100644
index 0000000..4022363
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_02.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key2 /*+ indexnl */  = y.key1)
+;

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

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

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_05.sqlpp
new file mode 100644
index 0000000..3dbb8e3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-equi-join_05.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * 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.CustomerType as
+ closed {
+  cid : int32,
+  name : string,
+  age : int32?,
+  address : AddressType?,
+  lastorder : {
+      oid : int32,
+      total : float
+  }
+
+}
+
+create  table Customers(CustomerType) primary key cid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_06.adm"
+select element {'customer1':c1,'customer2':c2}
+from  Customers as c1,
+      Customers as c2
+where (c1.cid /*+ indexnl */  = c2.cid)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_01.sqlpp
new file mode 100644
index 0000000..64f3c59
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */  >= y.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_02.sqlpp
new file mode 100644
index 0000000..3c2e9f3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-ge-join_02.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key key1;
+
+write output to nc1:"rttest/btree-index-join_primary-ge-join_02.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.key2 /*+ indexnl */  <= y.key1)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_01.sqlpp
new file mode 100644
index 0000000..e1e5ce2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */  > y.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_02.sqlpp
new file mode 100644
index 0000000..49677d4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-gt-join_02.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key key1;
+
+write output to nc1:"rttest/btree-index-join_primary-gt-join_02.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.key2 /*+ indexnl */  < y.key1)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_01.sqlpp
new file mode 100644
index 0000000..e22bed0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */  <= y.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_02.sqlpp
new file mode 100644
index 0000000..8bcbcff
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-le-join_02.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key key1;
+
+write output to nc1:"rttest/btree-index-join_primary-le-join_02.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.key2 /*+ indexnl */  >= y.key1)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_01.sqlpp
new file mode 100644
index 0000000..1e70a4d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */  < y.key2)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_02.sqlpp
new file mode 100644
index 0000000..0cd44d0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/primary-lt-join_02.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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.TestType as
+{
+  key1 : int32,
+  key2 : int32,
+  fname : string,
+  lname : string
+}
+
+create  table test1.DsOne(TestType) primary key key1;
+
+create  table test1.DsTwo(TestType) primary key key1;
+
+write output to nc1:"rttest/btree-index-join_primary-lt-join_02.adm"
+select element x
+from  "test1.DsOne" as x,
+      "test1.DsTwo" as y
+where (x.key2 /*+ indexnl */  > y.key1)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multiindex.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multiindex.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multiindex.sqlpp
new file mode 100644
index 0000000..c15319b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multiindex.sqlpp
@@ -0,0 +1,75 @@
+/*
+ * 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.FacebookUserType 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.FacebookMessageType 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  table FacebookUsers(FacebookUserType) primary key id;
+
+create  table FacebookMessages(FacebookMessageType) primary key "message-id";
+
+create  index fbmIdxAutId  if not exists  on FacebookMessages ("author-id-copy") type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multiindex.adm"
+select element {'fbu-ID':user.id,'fbm-auth-ID':message."author-id",'uname':user.name,'message':message.message}
+from  FacebookUsers as user,
+      FacebookMessages as message
+where ((user.id /*+ indexnl */  = message."author-id-copy") and (user.id >= 11000) and (user.id <= 12000))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multipred.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multipred.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multipred.sqlpp
new file mode 100644
index 0000000..93cac14
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join-multipred.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 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.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index title_index  on DBLP (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.title /*+ indexnl */  = b.title) and (a.authors < b.authors) and (a.misc > b.misc))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_01.sqlpp
new file mode 100644
index 0000000..28d1996
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_01.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * 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.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index title_index  on DBLP (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.title /*+ indexnl */  = b.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_02.sqlpp
new file mode 100644
index 0000000..368d5de
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_02.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * 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.
+ *                  CSX 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.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index title_index  on CSX (title) type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.title /*+ indexnl */  = b.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_03.sqlpp
new file mode 100644
index 0000000..817f1e5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index-join/secondary-equi-join_03.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    : Equi self-joins a dataset, DBLP, based on its 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.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index title_index  on DBLP (title) type btree;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where (a.title /*+ indexnl */  = b.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-01.sqlpp
new file mode 100644
index 0000000..390b32f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-01.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used 
+ *                  : for an invocation of current-datetime()
+ *  Expected Result : Success
+ *  Date            : 27th Aug 2015
+ *	Author			: Steven Jacobs
+ */
+
+drop  database emergencyTest if exists;
+create  database emergencyTest;
+
+use emergencyTest;
+
+
+create type emergencyTest.CHPReport as
+{
+  id : int64,
+  timestamp : datetime,
+  title : string,
+  message : string
+}
+
+create  table CHPReports(CHPReport) primary key timestamp;
+
+select element emergency.message
+from  CHPReports as emergency
+where ((emergency.timestamp >= (emergencyTest."current-datetime"() - emergencyTest."day-time-duration"('PT10H'))) and (emergency.title = 'ghost'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-02.sqlpp
new file mode 100644
index 0000000..0f3577a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-02.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used 
+ *                  : for an invocation of current-datetime()
+ *  Expected Result : Success
+ *  Date            : 27th Aug 2015
+ *	Author			: Steven Jacobs
+ */
+
+drop  database emergencyTest if exists;
+create  database emergencyTest;
+
+use emergencyTest;
+
+
+create type emergencyTest.CHPReport as
+{
+  id : int64,
+  timestamp : datetime,
+  title : string,
+  message : string
+}
+
+create  table CHPReports(CHPReport) primary key timestamp;
+
+select element emergency
+from  CHPReports as emergency
+with  time as (emergencyTest."current-datetime"() - emergencyTest."day-time-duration"('PT10H'))
+where (emergency.timestamp >= time)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-03.sqlpp
new file mode 100644
index 0000000..ae94e7b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-03.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used 
+ *                  : for an invocation of current-datetime()
+ *  Expected Result : Success
+ *  Date            : 27th Aug 2015
+ *	Author			: Steven Jacobs
+ */
+
+drop  database emergencyTest if exists;
+create  database emergencyTest;
+
+use emergencyTest;
+
+
+create type emergencyTest.CHPReport as
+{
+  id : int64,
+  timestamp : datetime,
+  title : string,
+  message : string
+}
+
+create  table CHPReports(CHPReport) primary key timestamp;
+
+select element emergency
+from  CHPReports as emergency
+where (emergency.timestamp >= emergencyTest."current-datetime"())
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-04.sqlpp
new file mode 100644
index 0000000..16b0fc6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-datetime-04.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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 makes sure that the plan is still okay when not converted to index search
+ *  Expected Result : Success
+ *  Date            : 27th Aug 2015
+ *	Author			: Steven Jacobs
+ */
+
+drop  database emergencyTest if exists;
+create  database emergencyTest;
+
+use emergencyTest;
+
+
+create type emergencyTest.CHPReport as
+{
+  id : int64,
+  timestamp : datetime,
+  title : string,
+  message : string
+}
+
+create  table CHPReports(CHPReport) primary key id;
+
+select element emergency
+from  CHPReports as emergency
+where (emergency.timestamp >= emergencyTest."current-datetime"())
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-01.sqlpp
new file mode 100644
index 0000000..ac242c9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-01.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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-01.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname > 'Roger')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-02.sqlpp
new file mode 100644
index 0000000..078fbfc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-02.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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-02.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname >= 'Susan')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-03.sqlpp
new file mode 100644
index 0000000..70ebdf5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-03.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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-03.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname < 'Isa')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-04.sqlpp
new file mode 100644
index 0000000..d0938fd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-04.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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-04.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname <= 'Vanpatten')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-05.sqlpp
new file mode 100644
index 0000000..5efab34
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-05.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname != 'Max')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-06.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-06.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-06.sqlpp
new file mode 100644
index 0000000..47aae4e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-06.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.fname = 'Julio')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-07.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-07.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-07.sqlpp
new file mode 100644
index 0000000..dcfdcc6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-07.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where (emp.lname = 'Kim')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-08.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-08.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-08.sqlpp
new file mode 100644
index 0000000..c205a75
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-08.sqlpp
@@ -0,0 +1,43 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname = 'Young Seok') and (emp.lname = 'Kim'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-09.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-09.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-09.sqlpp
new file mode 100644
index 0000000..2a1bbe2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-09.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname = 'Julio') or (emp.lname = 'Malaika'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-10.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-10.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-10.sqlpp
new file mode 100644
index 0000000..7194424
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-10.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname > 'Alex') and (emp.lname < 'Zach'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-11.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-11.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-11.sqlpp
new file mode 100644
index 0000000..764a57e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-11.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname > 'Allan') and (emp.lname < 'Zubi'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-12.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-12.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-12.sqlpp
new file mode 100644
index 0000000..0e000ca
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-12.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname > 'Allan') and (emp.lname = 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-13.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-13.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-13.sqlpp
new file mode 100644
index 0000000..31002c8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-13.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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-13.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname = 'Julio') and (emp.lname < 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-14.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-14.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-14.sqlpp
new file mode 100644
index 0000000..d5c0fab
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-14.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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-14.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname >= 'Michael') and (emp.lname <= 'Xu'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-15.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-15.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-15.sqlpp
new file mode 100644
index 0000000..6fb032c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-15.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname > 'Craig') and (emp.lname > 'Kevin') and (emp.fname < 'Mary') and (emp.lname < 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-16.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-16.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-16.sqlpp
new file mode 100644
index 0000000..f229494
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-16.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname >= 'Craig') and (emp.lname >= 'Kevin') and (emp.fname <= 'Mary') and (emp.lname <= 'Tomes'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-17.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-17.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-17.sqlpp
new file mode 100644
index 0000000..cea3c05
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-17.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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-17.adm"
+create type test.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname <= 'Craig') and (emp.lname > 'Kevin'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-18.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-18.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-18.sqlpp
new file mode 100644
index 0000000..9269f32
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/btree-index/btree-primary-18.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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.TestType as
+{
+  fname : string,
+  lname : string
+}
+
+create  table testdst(TestType) primary key fname,lname;
+
+select element emp
+from  testdst as emp
+where ((emp.fname != 'Michael') and (emp.lname != 'Carey'))
+;



Mime
View raw message