http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql
deleted file mode 100644
index f7504ad..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql
+++ /dev/null
@@ -1,91 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse tpch if exists;
-create dataverse tpch;
-use dataverse tpch;
-
-create type LineItemType as closed {
-  l_orderkey: int32, 
-  l_partkey: int32, 
-  l_suppkey: int32, 
-  l_linenumber: int32, 
-  l_quantity: double, 
-  l_extendedprice: double,
-  l_discount: double, 
-  l_tax: double,
-  l_returnflag: string, 
-  l_linestatus: string, 
-  l_shipdate: string,
-  l_commitdate: string, 
-  l_receiptdate: string, 
-  l_shipinstruct: string, 
-  l_shipmode: string, 
-  l_comment: string
-}
-
-create type OrderType as closed {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-create type CustomerType as closed {
-  c_custkey: int32, 
-  c_name: string, 
-  c_address: string, 
-  c_nationkey: int32, 
-  c_phone: string, 
-  c_acctbal: double, 
-  c_mktsegment: string,
-  c_comment: string
-}
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset LineItems(LineItemType)
-  primary key l_orderkey, l_linenumber on group1;
-create dataset Orders(OrderType)
-  primary key o_orderkey on group1;
-create dataset Customers(CustomerType) 
-  primary key c_custkey on group1;
-
-write output to asterix_nc1:"/tmp/nested_loj.adm";
-
-for $c in dataset('Customers')
-let $orders := 
-  for $o in dataset('Orders')
-  where $o.o_custkey = $c.c_custkey
-  let $items := 
-    for $l in dataset('LineItems')
-    where $l.l_orderkey = $o.o_orderkey
-    return $l
-  return {
-     "order": $o,
-     "items": $items
-  }  
-return {
-  "cust": $c,
-  "orders": $orders
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.sqlpp
new file mode 100644
index 0000000..aae756c
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.sqlpp
@@ -0,0 +1,94 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+  l_orderkey : integer,
+  l_partkey : integer,
+  l_suppkey : integer,
+  l_linenumber : integer,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create type tpch.OrderType as
+ closed {
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create type tpch.CustomerType as
+ closed {
+  c_custkey : integer,
+  c_name : string,
+  c_address : string,
+  c_nationkey : integer,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset LineItems(LineItemType) primary key l_orderkey,l_linenumber on 
group1;
+
+create  dataset Orders(OrderType) primary key o_orderkey on group1;
+
+create  dataset Customers(CustomerType) primary key c_custkey on group1;
+
+write output to asterix_nc1:"/tmp/nested_loj.adm";
+select element {'cust':c,'orders':orders}
+from  Customers as c
+with  orders as (
+      select element {'order':o,'items':items}
+      from  Orders as o
+      with  items as (
+            select element l
+            from  LineItems as l
+            where (l.l_orderkey = o.o_orderkey)
+        )
+      where (o.o_custkey = c.c_custkey)
+  )
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.aql
deleted file mode 100644
index 544e715..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.aql
+++ /dev/null
@@ -1,58 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse tpch if exists;
-create dataverse tpch;
-use dataverse tpch;
-
-
-create type OrderType as open {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset Orders(OrderType)
-  primary key o_orderkey on group1;
-
-load dataset Orders 
-using localfs
-(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"))
 pre-sorted;
-
-create index idx_Orders_Custkey on Orders(o_custkey);
-
-write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
-
-
-for $o in dataset('Orders')
-where
-  $o.o_custkey = 40 and $o.o_totalprice > 150000.0
-order by $o.o_orderkey
-return {  
-  "o_orderkey": $o.o_orderkey,
-  "o_custkey": $o.o_custkey
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.sqlpp
new file mode 100644
index 0000000..190f2f2
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_01.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+{
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset Orders(OrderType) primary key o_orderkey on group1;
+
+load  dataset Orders using localfs 
((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`))
 pre-sorted;
+
+create  index idx_Orders_Custkey  on Orders (o_custkey) type btree;
+
+write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
+select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey}
+from  Orders as o
+where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0))
+order by o.o_orderkey
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.aql
deleted file mode 100644
index 3f9aa3f..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.aql
+++ /dev/null
@@ -1,59 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse tpch if exists;
-create dataverse tpch;
-use dataverse tpch;
-
-
-create type OrderType as open {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset Orders(OrderType)
-  primary key o_orderkey on group1;
-
-load dataset Orders 
-using localfs
-(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"))
 pre-sorted;
-
-create index idx_Orders_Custkey on Orders(o_custkey);
-
-write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
-
-
-for $o in dataset('Orders')
-where
-  $o.o_custkey = 40 and $o.o_totalprice > 150000.0
-order by $o.o_orderkey
-return {  
-  "o_orderkey": $o.o_orderkey,
-  "o_custkey": $o.o_custkey,
-  "o_totalprice": $o.o_totalprice
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.sqlpp
new file mode 100644
index 0000000..64e2395
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive-open_02.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+{
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset Orders(OrderType) primary key o_orderkey on group1;
+
+load  dataset Orders using localfs 
((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`))
 pre-sorted;
+
+create  index idx_Orders_Custkey  on Orders (o_custkey) type btree;
+
+write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
+select element 
{'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey,'o_totalprice':o.o_totalprice}
+from  Orders as o
+where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0))
+order by o.o_orderkey
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
deleted file mode 100644
index 4eaf437..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
+++ /dev/null
@@ -1,58 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse tpch if exists;
-create dataverse tpch;
-use dataverse tpch;
-
-
-create type OrderType as closed {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset Orders(OrderType)
-  primary key o_orderkey on group1;
-
-load dataset Orders 
-using localfs
-(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"))
 pre-sorted;
-
-create index idx_Orders_Custkey on Orders(o_custkey);
-
-write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
-
-
-for $o in dataset('Orders')
-where
-  $o.o_custkey = 40 and $o.o_totalprice > 150000.0
-order by $o.o_orderkey
-return {  
-  "o_orderkey": $o.o_orderkey,
-  "o_custkey": $o.o_custkey
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.sqlpp
new file mode 100644
index 0000000..4fe9e18
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset Orders(OrderType) primary key o_orderkey on group1;
+
+load  dataset Orders using localfs 
((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`))
 pre-sorted;
+
+create  index idx_Orders_Custkey  on Orders (o_custkey) type btree;
+
+write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
+select element {'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey}
+from  Orders as o
+where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0))
+order by o.o_orderkey
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
deleted file mode 100644
index b2adee0..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
+++ /dev/null
@@ -1,59 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse tpch if exists;
-create dataverse tpch;
-use dataverse tpch;
-
-
-create type OrderType as closed {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset Orders(OrderType)
-  primary key o_orderkey on group1;
-
-load dataset Orders 
-using localfs
-(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"))
 pre-sorted;
-
-create index idx_Orders_Custkey on Orders(o_custkey);
-
-write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
-
-
-for $o in dataset('Orders')
-where
-  $o.o_custkey = 40 and $o.o_totalprice > 150000.0
-order by $o.o_orderkey
-return {  
-  "o_orderkey": $o.o_orderkey,
-  "o_custkey": $o.o_custkey,
-  "o_totalprice": $o.o_totalprice
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.sqlpp
new file mode 100644
index 0000000..c16810f
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset Orders(OrderType) primary key o_orderkey on group1;
+
+load  dataset Orders using localfs 
((`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`))
 pre-sorted;
+
+create  index idx_Orders_Custkey  on Orders (o_custkey) type btree;
+
+write output to asterix_nc1:"/tmp/index_search-conjunctive.adm";
+select element 
{'o_orderkey':o.o_orderkey,'o_custkey':o.o_custkey,'o_totalprice':o.o_totalprice}
+from  Orders as o
+where ((o.o_custkey = 40) and (o.o_totalprice > 150000.0))
+order by o.o_orderkey
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
deleted file mode 100644
index a909b11..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
+++ /dev/null
@@ -1,54 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse pull-select-above-eq-join if exists;
-
-create dataverse pull-select-above-eq-join;
-
-use dataverse pull-select-above-eq-join;
-
-create type UserType as open {
-  uid: int32, 
-  name: string,
-  city: string,
-  lottery_numbers: {{int32}}
-}
-
-create type VisitorType as open {
-  vid: int32, 
-  name: string,
-  city: string,
-  lottery_numbers: {{int32}}
-}
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset Users(UserType) 
-  primary key uid on group1;
-
-create dataset Visitors(VisitorType) 
-  primary key vid on group1;
-
-
-write output to asterix_nc1:"/tmp/pull-select-above-eq-join.adm";
-
-for $user in dataset('Users')
-for $visitor in dataset('Visitors')
-where $user.name = $visitor.name
-      and $user.city != $visitor.city
-return {"uid": $user.uid, "vid": $visitor.vid}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.sqlpp
new file mode 100644
index 0000000..8634de5
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse `pull-select-above-eq-join` if exists;
+create  dataverse `pull-select-above-eq-join`;
+
+use `pull-select-above-eq-join`;
+
+
+create type `pull-select-above-eq-join`.UserType as
+{
+  uid : integer,
+  name : string,
+  city : string,
+  lottery_numbers : {{integer}}
+};
+
+create type `pull-select-above-eq-join`.VisitorType as
+{
+  vid : integer,
+  name : string,
+  city : string,
+  lottery_numbers : {{integer}}
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset Users(UserType) primary key uid on group1;
+
+create  dataset Visitors(VisitorType) primary key vid on group1;
+
+write output to asterix_nc1:"/tmp/pull-select-above-eq-join.adm";
+select element {'uid':user.uid,'vid':visitor.vid}
+from  Users as user,
+      Visitors as visitor
+where ((user.name = visitor.name) and (user.city != visitor.city))
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
deleted file mode 100644
index 4b4ed34..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
+++ /dev/null
@@ -1,46 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse fuzzyjoin_080 if exists;
-
-create dataverse fuzzyjoin_080;
-
-use dataverse fuzzyjoin_080;
-
-create type DBLPType as open {
-  id: int32, 
-  dblpid: string,
-  title: string,
-  authors: string,
-  misc: string
-}
-
-create nodegroup group1 if not exists on asterix_nc1, asterix_nc2;
-
-create dataset DBLP(DBLPType) 
-  primary key id on group1;
-
-
-write output to asterix_nc1:'rttest/fuzzyjoin_080.adm';
-
-    for $paperDBLP in dataset('DBLP')
-    let $matches :=
-            for $paper in dataset('DBLP')
-            where $paper.authors = $paperDBLP.authors        
-            return $paper.title
-    return {'id': $paperDBLP.id, 'matches':$matches}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.sqlpp
new file mode 100644
index 0000000..535ae58
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.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.
+ */
+
+drop  dataverse fuzzyjoin_080 if exists;
+create  dataverse fuzzyjoin_080;
+
+use fuzzyjoin_080;
+
+
+create type fuzzyjoin_080.DBLPType as
+{
+  id : integer,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+};
+
+create  nodegroup group1 if not exists  on
+    asterix_nc1,
+    asterix_nc2
+;
+create  dataset DBLP(DBLPType) primary key id on group1;
+
+write output to asterix_nc1:"rttest/fuzzyjoin_080.adm";
+select element {'id':paperDBLP.id,'matches':matches}
+from  DBLP as paperDBLP
+with  matches as (
+      select element paper.title
+      from  DBLP as paper
+      where (paper.authors = paperDBLP.authors)
+  )
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.aql 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.aql
deleted file mode 100644
index 0961e44..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.aql
+++ /dev/null
@@ -1,167 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse q08_group_by if exists;
-
-create dataverse q08_group_by;
-
-use dataverse q08_group_by;
-
-create type LineItemType as closed {
-  l_orderkey: int32, 
-  l_partkey: int32, 
-  l_suppkey: int32, 
-  l_linenumber: int32, 
-  l_quantity: double, 
-  l_extendedprice: double,
-  l_discount: double, 
-  l_tax: double,
-  l_returnflag: string, 
-  l_linestatus: string, 
-  l_shipdate: string,
-  l_commitdate: string, 
-  l_receiptdate: string, 
-  l_shipinstruct: string, 
-  l_shipmode: string, 
-  l_comment: string
-}
-
-create type OrderType as closed {
-  o_orderkey: int32, 
-  o_custkey: int32, 
-  o_orderstatus: string, 
-  o_totalprice: double, 
-  o_orderdate: string, 
-  o_orderpriority: string,
-  o_clerk: string, 
-  o_shippriority: int32, 
-  o_comment: string
-}
-
-create type CustomerType as closed {
-  c_custkey: int32, 
-  c_name: string, 
-  c_address: string, 
-  c_nationkey: int32, 
-  c_phone: string, 
-  c_acctbal: double, 
-  c_mktsegment: string,
-  c_comment: string
-}
-
-create type SupplierType as closed {
-  s_suppkey: int32, 
-  s_name: string,
-  s_address: string,
-  s_nationkey: int32,
-  s_phone: string,
-  s_acctbal: double,
-  s_comment: string
-}
-
-create type NationType as closed {
-  n_nationkey: int32,
-  n_name: string,
-  n_regionkey: int32,
-  n_comment: string
-}
-
-create type RegionType as closed {
-  r_regionkey: int32,
-  r_name: string,
-  r_comment: string
-}
-
-create type PartType as closed {
-  p_partkey: int32, 
-  p_name: string, 
-  p_mfgr: string,
-  p_brand: string,
-  p_type: string,
-  p_size: int32,
-  p_container: string,
-  p_retailprice: double,
-  p_comment: string
-}
-
-create dataset LineItem(LineItemType)
-  primary key l_orderkey, l_linenumber;
-create dataset Orders(OrderType)
-  primary key o_orderkey;
-create dataset Customer(CustomerType) 
-  primary key c_custkey;
-create dataset Supplier(SupplierType)
-  primary key s_suppkey;
-create dataset Nation(NationType) 
-  primary key n_nationkey;
-create dataset Region(RegionType)
-  primary key r_regionkey;
-create dataset Part(PartType)
-  primary key p_partkey;
-
-for $s in dataset("Supplier")
-    for $lnrcop in (
-      for $lnrco in (
-        for $l in dataset('LineItem')
-        for $nrco in (
-          for $o in dataset('Orders')
-          for $nrc in (
-            for $c in dataset('Customer')
-            for $nr in (
-              for $n1 in dataset('Nation')
-              for $r1 in dataset('Region')
-              where $n1.n_regionkey = $r1.r_regionkey and $r1.r_name = 
'AMERICA'
-              return { "n_nationkey": $n1.n_nationkey }
-            )
-            where $c.c_nationkey = $nr.n_nationkey
-            return { "c_custkey": $c.c_custkey }
-          )
-          where $nrc.c_custkey = $o.o_custkey
-          return {
-            "o_orderdate" : $o.o_orderdate, 
-            "o_orderkey": $o.o_orderkey 
-          }
-        )
-        where $l.l_orderkey = $nrco.o_orderkey
-          and $nrco.o_orderdate >= '1995-01-01' 
-          and $nrco.o_orderdate <= '1996-12-31'
-        return {
-          "o_orderdate": $nrco.o_orderdate, 
-          "l_partkey": $l.l_partkey, 
-          "l_discount": $l.l_discount, 
-          "l_extendedprice": $l.l_extendedprice, 
-          "l_suppkey": $l.l_suppkey
-        }
-      )
-      for $p in dataset("Part")
-      where $p.p_partkey = $lnrco.l_partkey and $p.p_type = 'ECONOMY ANODIZED 
STEEL'
-      return {
-        "o_orderdate": $lnrco.o_orderdate, 
-        "l_discount": $lnrco.l_discount, 
-        "l_extendedprice": $lnrco.l_extendedprice, 
-        "l_suppkey": $lnrco.l_suppkey 
-      }
-    )
-    where $s.s_suppkey = $lnrcop.l_suppkey
-    return {
-      "o_orderdate": $lnrcop.o_orderdate, 
-      "l_discount": $lnrcop.l_discount, 
-      "l_extendedprice": $lnrcop.l_extendedprice, 
-      "l_suppkey": $lnrcop.l_suppkey, 
-      "s_nationkey": $s.s_nationkey
-    }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.sqlpp
new file mode 100644
index 0000000..4ff3d31
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q08_group_by.sqlpp
@@ -0,0 +1,153 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse q08_group_by if exists;
+create  dataverse q08_group_by;
+
+use q08_group_by;
+
+
+create type q08_group_by.LineItemType as
+ closed {
+  l_orderkey : integer,
+  l_partkey : integer,
+  l_suppkey : integer,
+  l_linenumber : integer,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create type q08_group_by.OrderType as
+ closed {
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create type q08_group_by.CustomerType as
+ closed {
+  c_custkey : integer,
+  c_name : string,
+  c_address : string,
+  c_nationkey : integer,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+};
+
+create type q08_group_by.SupplierType as
+ closed {
+  s_suppkey : integer,
+  s_name : string,
+  s_address : string,
+  s_nationkey : integer,
+  s_phone : string,
+  s_acctbal : double,
+  s_comment : string
+};
+
+create type q08_group_by.NationType as
+ closed {
+  n_nationkey : integer,
+  n_name : string,
+  n_regionkey : integer,
+  n_comment : string
+};
+
+create type q08_group_by.RegionType as
+ closed {
+  r_regionkey : integer,
+  r_name : string,
+  r_comment : string
+};
+
+create type q08_group_by.PartType as
+ closed {
+  p_partkey : integer,
+  p_name : string,
+  p_mfgr : string,
+  p_brand : string,
+  p_type : string,
+  p_size : integer,
+  p_container : string,
+  p_retailprice : double,
+  p_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create  dataset Orders(OrderType) primary key o_orderkey;
+
+create  dataset Customer(CustomerType) primary key c_custkey;
+
+create  dataset Supplier(SupplierType) primary key s_suppkey;
+
+create  dataset Nation(NationType) primary key n_nationkey;
+
+create  dataset Region(RegionType) primary key r_regionkey;
+
+create  dataset Part(PartType) primary key p_partkey;
+
+select element 
{'o_orderdate':lnrcop.o_orderdate,'l_discount':lnrcop.l_discount,'l_extendedprice':lnrcop.l_extendedprice,'l_suppkey':lnrcop.l_suppkey,'s_nationkey':s.s_nationkey}
+from  Supplier as s,
+      (
+    select element 
{'o_orderdate':lnrco.o_orderdate,'l_discount':lnrco.l_discount,'l_extendedprice':lnrco.l_extendedprice,'l_suppkey':lnrco.l_suppkey}
+    from  (
+        select element 
{'o_orderdate':nrco.o_orderdate,'l_partkey':l.l_partkey,'l_discount':l.l_discount,'l_extendedprice':l.l_extendedprice,'l_suppkey':l.l_suppkey}
+        from  LineItem as l,
+              (
+            select element 
{'o_orderdate':o.o_orderdate,'o_orderkey':o.o_orderkey}
+            from  Orders as o,
+                  (
+                select element {'c_custkey':c.c_custkey}
+                from  Customer as c,
+                      (
+                    select element {'n_nationkey':n1.n_nationkey}
+                    from  Nation as n1,
+                          Region as r1
+                    where ((n1.n_regionkey = r1.r_regionkey) and (r1.r_name = 
'AMERICA'))
+                ) as nr
+                where (c.c_nationkey = nr.n_nationkey)
+            ) as nrc
+            where (nrc.c_custkey = o.o_custkey)
+        ) as nrco
+        where ((l.l_orderkey = nrco.o_orderkey) and (nrco.o_orderdate >= 
'1995-01-01') and (nrco.o_orderdate <= '1996-12-31'))
+    ) as lnrco,
+          Part as p
+    where ((p.p_partkey = lnrco.l_partkey) and (p.p_type = 'ECONOMY ANODIZED 
STEEL'))
+) as lnrcop
+where (s.s_suppkey = lnrcop.l_suppkey)
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.aql 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.aql
deleted file mode 100644
index adb6efe..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.aql
+++ /dev/null
@@ -1,171 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-drop dataverse q09_group_by if exists;
-
-create dataverse q09_group_by;
-
-use dataverse q09_group_by;
-
-create type LineItemType as closed {
-  l_orderkey: int32,
-  l_partkey: int32,
-  l_suppkey: int32,
-  l_linenumber: int32,
-  l_quantity: int32,
-  l_extendedprice: double,
-  l_discount: double,
-  l_tax: double,
-  l_returnflag: string,
-  l_linestatus: string,
-  l_shipdate: string,
-  l_commitdate: string,
-  l_receiptdate: string,
-  l_shipinstruct: string,
-  l_shipmode: string,
-  l_comment: string
-}
-
-create type OrderType as closed {
-  o_orderkey: int32,
-  o_custkey: int32,
-  o_orderstatus: string,
-  o_totalprice: double,
-  o_orderdate: string,
-  o_orderpriority: string,
-  o_clerk: string,
-  o_shippriority: int32,
-  o_comment: string
-}
-
-create type CustomerType as closed {
-  c_custkey: int32,
-  c_name: string,
-  c_address: string,
-  c_nationkey: int32,
-  c_phone: string,
-  c_acctbal: double,
-  c_mktsegment: string,
-  c_comment: string
-}
-
-create type SupplierType as closed {
-  s_suppkey: int32,
-  s_name: string,
-  s_address: string,
-  s_nationkey: int32,
-  s_phone: string,
-  s_acctbal: double,
-  s_comment: string
-}
-
-create type NationType as closed {
-  n_nationkey: int32,
-  n_name: string,
-  n_regionkey: int32,
-  n_comment: string
-}
-
-create type RegionType as closed {
-    r_regionkey: int32,
-    r_name: string,
-    r_comment: string
-}
-
-create type PartType as closed {
-  p_partkey: int32,
-  p_name: string,
-  p_mfgr: string,
-  p_brand: string,
-  p_type: string,
-  p_size: int32,
-  p_container: string,
-  p_retailprice: double,
-  p_comment: string
-}
-
-create type PartSuppType as closed {
-  ps_partkey: int32,
-  ps_suppkey: int32,
-  ps_availqty: int32,
-  ps_supplycost: double,
-  ps_comment: string
-}
-
-create dataset LineItem(LineItemType)
-  primary key l_orderkey, l_linenumber;
-create dataset Orders(OrderType)
-  primary key o_orderkey;
-create dataset Supplier(SupplierType)
-  primary key s_suppkey;
-create dataset Region(RegionType)
-  primary key r_regionkey;
-create dataset Nation(NationType)
-  primary key n_nationkey;
-create dataset Part(PartType)
-  primary key p_partkey;
-create dataset Partsupp(PartSuppType)
-  primary key ps_partkey, ps_suppkey;
-create dataset Customer(CustomerType)
-  primary key c_custkey;
-
-for $p in dataset('Part')
-    for $l2 in (
-      for $ps in dataset('Partsupp')
-      for $l1 in (
-        for $s1 in (
-          for $s in dataset('Supplier')
-          for $n in dataset('Nation')
-          where $n.n_nationkey = $s.s_nationkey
-          return {
-            "s_suppkey": $s.s_suppkey,
-            "n_name": $n.n_name
-          }
-        )
-        for $l in dataset('LineItem')
-        where $s1.s_suppkey = $l.l_suppkey
-        return  {
-          "l_suppkey": $l.l_suppkey,
-          "l_extendedprice": $l.l_extendedprice,
-          "l_discount": $l.l_discount,
-          "l_quantity": $l.l_quantity,
-          "l_partkey": $l.l_partkey,
-          "l_orderkey": $l.l_orderkey,
-          "n_name": $s1.n_name
-        }
-      )
-      where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey
-      return {
-        "l_extendedprice": $l1.l_extendedprice,
-        "l_discount": $l1.l_discount,
-        "l_quantity": $l1.l_quantity,
-        "l_partkey": $l1.l_partkey,
-        "l_orderkey": $l1.l_orderkey,
-        "n_name": $l1.n_name,
-        "ps_supplycost": $ps.ps_supplycost
-      }
-    )
-    where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey
-    return {
-      "l_extendedprice": $l2.l_extendedprice,
-      "l_discount": $l2.l_discount,
-      "l_quantity": $l2.l_quantity,
-      "l_orderkey": $l2.l_orderkey,
-      "n_name": $l2.n_name,
-      "ps_supplycost": $l2.ps_supplycost
-    }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.sqlpp
new file mode 100644
index 0000000..44f4760
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/q09_group_by.sqlpp
@@ -0,0 +1,154 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse q09_group_by if exists;
+create  dataverse q09_group_by;
+
+use q09_group_by;
+
+
+create type q09_group_by.LineItemType as
+ closed {
+  l_orderkey : integer,
+  l_partkey : integer,
+  l_suppkey : integer,
+  l_linenumber : integer,
+  l_quantity : integer,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create type q09_group_by.OrderType as
+ closed {
+  o_orderkey : integer,
+  o_custkey : integer,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : integer,
+  o_comment : string
+};
+
+create type q09_group_by.CustomerType as
+ closed {
+  c_custkey : integer,
+  c_name : string,
+  c_address : string,
+  c_nationkey : integer,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+};
+
+create type q09_group_by.SupplierType as
+ closed {
+  s_suppkey : integer,
+  s_name : string,
+  s_address : string,
+  s_nationkey : integer,
+  s_phone : string,
+  s_acctbal : double,
+  s_comment : string
+};
+
+create type q09_group_by.NationType as
+ closed {
+  n_nationkey : integer,
+  n_name : string,
+  n_regionkey : integer,
+  n_comment : string
+};
+
+create type q09_group_by.RegionType as
+ closed {
+  r_regionkey : integer,
+  r_name : string,
+  r_comment : string
+};
+
+create type q09_group_by.PartType as
+ closed {
+  p_partkey : integer,
+  p_name : string,
+  p_mfgr : string,
+  p_brand : string,
+  p_type : string,
+  p_size : integer,
+  p_container : string,
+  p_retailprice : double,
+  p_comment : string
+};
+
+create type q09_group_by.PartSuppType as
+ closed {
+  ps_partkey : integer,
+  ps_suppkey : integer,
+  ps_availqty : integer,
+  ps_supplycost : double,
+  ps_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create  dataset Orders(OrderType) primary key o_orderkey;
+
+create  dataset Supplier(SupplierType) primary key s_suppkey;
+
+create  dataset Region(RegionType) primary key r_regionkey;
+
+create  dataset Nation(NationType) primary key n_nationkey;
+
+create  dataset Part(PartType) primary key p_partkey;
+
+create  dataset Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create  dataset Customer(CustomerType) primary key c_custkey;
+
+select element 
{'l_extendedprice':l2.l_extendedprice,'l_discount':l2.l_discount,'l_quantity':l2.l_quantity,'l_orderkey':l2.l_orderkey,'n_name':l2.n_name,'ps_supplycost':l2.ps_supplycost}
+from  Part as p,
+      (
+    select element 
{'l_extendedprice':l1.l_extendedprice,'l_discount':l1.l_discount,'l_quantity':l1.l_quantity,'l_partkey':l1.l_partkey,'l_orderkey':l1.l_orderkey,'n_name':l1.n_name,'ps_supplycost':ps.ps_supplycost}
+    from  Partsupp as ps,
+          (
+        select element 
{'l_suppkey':l.l_suppkey,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_quantity':l.l_quantity,'l_partkey':l.l_partkey,'l_orderkey':l.l_orderkey,'n_name':s1.n_name}
+        from  (
+            select element {'s_suppkey':s.s_suppkey,'n_name':n.n_name}
+            from  Supplier as s,
+                  Nation as n
+            where (n.n_nationkey = s.s_nationkey)
+        ) as s1,
+              LineItem as l
+        where (s1.s_suppkey = l.l_suppkey)
+    ) as l1
+    where ((ps.ps_suppkey = l1.l_suppkey) and (ps.ps_partkey = l1.l_partkey))
+) as l2
+where (q09_group_by.contains(p.p_name,'green') and (p.p_partkey = 
l2.l_partkey))
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.aql
deleted file mode 100644
index 827b02a..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.aql
+++ /dev/null
@@ -1,58 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description  : This test case is to verify the fix for issue601
- * https://code.google.com/p/asterixdb/issues/detail?id=601
- * Expected Res : SUCCESS
- * Date         : 10th Oct 2014
- */
-
-drop dataverse tpch if exists;
-create dataverse tpch;
-
-use dataverse tpch;
-
-create type LineItemType as closed {
-  l_orderkey: int32, 
-  l_partkey: int32, 
-  l_suppkey: int32, 
-  l_linenumber: int32, 
-  l_quantity: double, 
-  l_extendedprice: double,
-  l_discount: double, 
-  l_tax: double,
-  l_returnflag: string, 
-  l_linestatus: string, 
-  l_shipdate: string,
-  l_commitdate: string, 
-  l_receiptdate: string, 
-  l_shipinstruct: string, 
-  l_shipmode: string, 
-  l_comment: string
-}
-
-create dataset LineItem(LineItemType)
-  primary key l_orderkey, l_linenumber;
-  
-for $l in dataset('LineItem')
-group by $l_linenumber := $l.l_linenumber with $l
-return {
-  "l_linenumber": $l_linenumber,
-  "count_order": count($l)
-}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.sqlpp
new file mode 100644
index 0000000..a5861be
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query-issue601.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : This test case is to verify the fix for issue601
+ * https://code.google.com/p/asterixdb/issues/detail?id=601
+ * Expected Res : SUCCESS
+ * Date         : 10th Oct 2014
+ */
+
+drop  dataverse tpch if exists;
+create  dataverse tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+  l_orderkey : integer,
+  l_partkey : integer,
+  l_suppkey : integer,
+  l_linenumber : integer,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+select element {'l_linenumber':l_linenumber,'count_order':count(l)}
+from  LineItem as l
+group by l.l_linenumber as l_linenumber
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.aql
deleted file mode 100644
index 18acdf0..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.aql
+++ /dev/null
@@ -1,42 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description  : This test case is to verify the fix for issue827
- * https://code.google.com/p/asterixdb/issues/detail?id=849
- * Expected Res : SUCCESS
- * Date         : 2nd Feb. 2015
- */
-
-drop dataverse test if exists;
-create dataverse test;
-
-use dataverse test;
-
-create type sType as closed{b : int32};
-create dataset s(sType) primary key b;
-
-insert into dataset s ({ "b" : 1});
-insert into dataset s ({ "b" : 3});
-
-for $x in dataset s
-for $y in (
- for $z in {{ {"a":1, "c":1},{"a":2, "c":2},{"a":1, "c":null} }} where 
$x.b=$z.a
-    return $z.c
-)
-return {"x":$x,"y":$y}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.sqlpp
new file mode 100644
index 0000000..ee21bc4
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849-2.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  : This test case is to verify the fix for issue827
+ * https://code.google.com/p/asterixdb/issues/detail?id=849
+ * Expected Res : SUCCESS
+ * Date         : 2nd Feb. 2015
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.sType as
+ closed {
+  b : integer
+};
+
+create  dataset s(sType) primary key b;
+
+insert into s
+select element {'b':1};
+insert into s
+select element {'b':3};
+select element {'x':x,'y':y}
+from  s as x,
+      (
+    select element z.c
+    from  {{{'a':1,'c':1},{'a':2,'c':2},{'a':1,'c':null}}} as z
+    where (x.b = z.a)
+) as y
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.aql
deleted file mode 100644
index 3e2bea3..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.aql
+++ /dev/null
@@ -1,42 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description  : This test case is to verify the fix for issue827
- * https://code.google.com/p/asterixdb/issues/detail?id=849
- * Expected Res : SUCCESS
- * Date         : 2nd Feb. 2015
- */
-
-drop dataverse test if exists;
-create dataverse test;
-
-use dataverse test;
-
-create type sType as closed{b : int32};
-create dataset s(sType) primary key b;
-
-insert into dataset s ({ "b" : 1});
-insert into dataset s ({ "b" : 3});
-
-for $x in {{ {"a":1},{"a":2} }}
-for $y in (
- for $z in dataset s where $x.a=$z.b
-    return $z.b
-)
-return {"x":$x,"y":$y}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.sqlpp
new file mode 100644
index 0000000..5b0e7fd
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/query_issue849.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  : This test case is to verify the fix for issue827
+ * https://code.google.com/p/asterixdb/issues/detail?id=849
+ * Expected Res : SUCCESS
+ * Date         : 2nd Feb. 2015
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.sType as
+ closed {
+  b : integer
+};
+
+create  dataset s(sType) primary key b;
+
+insert into s
+select element {'b':1};
+insert into s
+select element {'b':3};
+select element {'x':x,'y':y}
+from  {{{'a':1},{'a':2}}} as x,
+      (
+    select element z.b
+    from  s as z
+    where (x.a = z.b)
+) as y
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
deleted file mode 100644
index 2b6df8a..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
+++ /dev/null
@@ -1,71 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description  : Test that left-outer-join may use two available indexes, one 
for primary index in prob subtree and another for secondary rtree index in 
index subtree.
- * Issue        : 730, 741
- * Expected Res : Success
- * Date         : 8th May 2014
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
-
-create type TwitterUserType as closed {
-    screen-name: string,
-    lang: string,
-    friends-count: int32,
-    statuses-count: int32,
-    name: string,
-    followers-count: int32
-}
-
-create type TweetMessageType as closed {
-    tweetid: int64,
-        user: TwitterUserType,
-        sender-location: point,
-    send-time: datetime,
-        referred-topics: {{ string }},
-    message-text: string,
-    countA: int32,
-    countB: int32
-}
-
-create dataset TweetMessages(TweetMessageType)
-primary key tweetid;
-
-create index twmSndLocIx on TweetMessages(sender-location) type rtree;
-create index msgCountAIx on TweetMessages(countA) type btree;
-create index msgCountBIx on TweetMessages(countB) type btree;
-create index msgTextIx on TweetMessages(message-text) type keyword;
-
-write output to 
asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm";
-
-for $t1 in dataset('TweetMessages')
-let $n :=  create-circle($t1.sender-location, 0.5)
-where $t1.tweetid < int64("10")
-order by $t1.tweetid
-return {
-"tweetid1": $t1.tweetid,
-"loc1":$t1.sender-location,
-"nearby-message": for $t2 in dataset('TweetMessages')
-                             where spatial-intersect($t2.sender-location, $n) 
and $t1.tweetid != $t2.tweetid
-                             order by $t2.tweetid
-                             return {"tweetid2":$t2.tweetid, 
"loc2":$t2.sender-location}
-};

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
new file mode 100644
index 0000000..a30f22c
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.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  : Test that left-outer-join may use two available indexes, one 
for primary index in prob subtree and another for secondary rtree index in 
index subtree.
+ * Issue        : 730, 741
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  `screen-name` : string,
+  lang : string,
+  `friends-count` : integer,
+  `statuses-count` : integer,
+  name : string,
+  `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+ closed {
+  tweetid : bigint,
+  user : TwitterUserType,
+  `sender-location` : point,
+  `send-time` : datetime,
+  `referred-topics` : {{string}},
+  `message-text` : string,
+  countA : integer,
+  countB : integer
+};
+
+create  dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create  index twmSndLocIx  on TweetMessages (`sender-location`) type rtree;
+
+create  index msgCountAIx  on TweetMessages (countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (countB) type btree;
+
+create  index msgTextIx  on TweetMessages (`message-text`) type keyword;
+
+write output to 
asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm";
+select element 
{'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':(
+        select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`}
+        from  TweetMessages as t2
+        where (test.`spatial-intersect`(t2.`sender-location`,n) and 
(t1.tweetid != t2.tweetid))
+        order by t2.tweetid
+    )}
+from  TweetMessages as t1
+with  n as test.`create-circle`(t1.`sender-location`,0.5)
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.aql
deleted file mode 100644
index dcbf53f..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.aql
+++ /dev/null
@@ -1,51 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description    : Joins two datasets on the intersection of their point 
attributes.
- *                  The dataset 'MyData2' has an RTree index, and we expect 
the 
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
-
-create type MyRecord as closed {
-  id: int32,
-  point: point,
-  kwds: string,
-  line1: line,
-  line2: line,
-  poly1: polygon,
-  poly2: polygon,
-  rec: rectangle
-}
-
-create dataset MyData1(MyRecord) primary key id;
-create dataset MyData2(MyRecord) primary key id;
-
-create index rtree_index on MyData2(point) type rtree;
-
-write output to 
asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm";
-
-for $a in dataset('MyData1')
-for $b in dataset('MyData2')
-where spatial-intersect($a.point, $b.point)
-return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.sqlpp
new file mode 100644
index 0000000..f87832e
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_02.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Joins two datasets on the intersection of their point 
attributes.
+ *                  The dataset 'MyData2' has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : integer,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+};
+
+create  dataset MyData1(MyRecord) primary key id;
+
+create  dataset MyData2(MyRecord) primary key id;
+
+create  index rtree_index  on MyData2 (point) type rtree;
+
+write output to 
asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm";
+select element {'a':a,'b':b}
+from  MyData1 as a,
+      MyData2 as b
+where test.`spatial-intersect`(a.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.aql
deleted file mode 100644
index 624fe9e..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.aql
+++ /dev/null
@@ -1,50 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description    : Self-joins a dataset on the intersection of its point 
attribute.
- *                  The dataset has an RTree index, and we expect the 
- *                  join to be transformed into an indexed nested-loop join.
- * Success        : Yes
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
-
-create type MyRecord as closed {
-  id: int32,
-  point: point,
-  kwds: string,
-  line1: line,
-  line2: line,
-  poly1: polygon,
-  poly2: polygon,
-  rec: rectangle
-}
-
-create dataset MyData(MyRecord) primary key id;
-
-create index rtree_index on MyData(point) type rtree;
-
-write output to 
asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm";
-
-for $a in dataset('MyData')
-for $b in dataset('MyData')
-where spatial-intersect($a.point, $b.point)
-return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.sqlpp
new file mode 100644
index 0000000..afe7a72
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/rtree-index-join/spatial-intersect-point_03.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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-joins a dataset on the intersection of its point 
attribute.
+ *                  The dataset has an RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : integer,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+};
+
+create  dataset MyData(MyRecord) primary key id;
+
+create  index rtree_index  on MyData (point) type rtree;
+
+write output to 
asterix_nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm";
+select element {'a':a,'b':b}
+from  MyData as a,
+      MyData as b
+where test.`spatial-intersect`(a.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.aql
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.aql
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.aql
deleted file mode 100644
index b8c7a8a..0000000
--- 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.aql
+++ /dev/null
@@ -1,43 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-/*
- * Description  : Notice the query hint to avoid using any secondary index to 
evaluate the predicate in the where clause
- * Expected Res : Success
- * Date         : 21th December 2013
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
-
-write output to asterix_nc1:"rttest/btree-index_btree-secondary-57.adm";
-
-create type TestType as open {
-    id : int32,
-    fname : string,
-    lname : string
-}
-
-create dataset testdst(TestType) primary key id;
-
-create index sec_Idx on testdst(fname);
-
-for $emp in dataset('testdst') 
-where $emp.fname /*+ skip-index */ >= "Max" and $emp.fname <= "Roger" 
-return $emp

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/33a656d1/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.sqlpp
----------------------------------------------------------------------
diff --git 
a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.sqlpp
 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.sqlpp
new file mode 100644
index 0000000..6235842
--- /dev/null
+++ 
b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/skip-index/skip-secondary-btree-index-2.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 avoid using any secondary index to 
evaluate the predicate in the where clause
+ * Expected Res : Success
+ * Date         : 21th December 2013
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+write output to asterix_nc1:"rttest/btree-index_btree-secondary-57.adm";
+create type test.TestType as
+{
+  id : integer,
+  fname : string,
+  lname : string
+};
+
+create  dataset testdst(TestType) primary key id;
+
+create  index sec_Idx  on testdst (fname) type btree;
+
+select element emp
+from  testdst as emp
+where ((emp.fname /*+ skip-index */  >= 'Max') and (emp.fname <= 'Roger'))
+;

Reply via email to