asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [4/7] incubator-asterixdb git commit: ASTERIXDB-1128: add TPC-H queries with secondary indices into build tests.
Date Sun, 13 Mar 2016 07:14:10 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
@@ -0,0 +1,146 @@
+/*
+ * 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  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+  l_orderkey : int64,
+  l_partkey : int64,
+  l_suppkey : int64,
+  l_linenumber : int64,
+  l_quantity : int64,
+  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 : int64,
+  o_custkey : int64,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : int64,
+  o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+  c_custkey : int64,
+  c_name : string,
+  c_address : string,
+  c_nationkey : int64,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+  s_suppkey : int64,
+  s_name : string,
+  s_address : string,
+  s_nationkey : int64,
+  s_phone : string,
+  s_acctbal : double,
+  s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+  n_nationkey : int64,
+  n_name : string,
+  n_regionkey : int64,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int64,
+  r_name : string,
+  r_comment : string
+}
+
+create type tpch.PartType as
+ closed {
+  p_partkey : int64,
+  p_name : string,
+  p_mfgr : string,
+  p_brand : string,
+  p_type : string,
+  p_size : int64,
+  p_container : string,
+  p_retailprice : double,
+  p_comment : string
+}
+
+create type tpch.PartSuppType as
+ closed {
+  ps_partkey : int64,
+  ps_suppkey : int64,
+  ps_availqty : int64,
+  ps_supplycost : double,
+  ps_comment : string
+}
+
+create  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Part(PartType) primary key p_partkey;
+
+create  table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index partsupp_fk_part on Partsupp (ps_partkey);
+create index partsupp_fk_supplier on Partsupp (ps_suppkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Part using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Partsupp using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
new file mode 100644
index 0000000..f91068d
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
@@ -0,0 +1,80 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp1() {
+(
+    select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
+            select element i.l_suppkey
+            from  l2 as i
+        )),'max_suppkey':tpch.max((
+            select element i.l_suppkey
+            from  l2 as i
+        ))}
+    from  (
+        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
+        from  LineItem as l
+        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
+    ) as l2
+    group by l2.l_orderkey as l_orderkey
+)
+};
+declare function tmp2() {
+(
+    select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
+            select element i.l_suppkey
+            from  l2 as i
+        )),'max_suppkey':tpch.max((
+            select element i.l_suppkey
+            from  l2 as i
+        ))}
+    from  (
+        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
+        from  LineItem as l
+        where (l.l_receiptdate > l.l_commitdate)
+        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
+    ) as l2
+    group by l2.l_orderkey as l_orderkey
+)
+};
+select element {'s_name':s_name,'numwait':numwait}
+from  (
+    select element {'s_name':t3.s_name,'l_suppkey':t3.l_suppkey,'l_orderkey':t2.l_orderkey,'count_suppkey':t2.count_suppkey,'max_suppkey':t2.max_suppkey}
+    from  (
+            select element {'s_name':ns.s_name,'l_orderkey':t1.l_orderkey,'l_suppkey':l.l_suppkey}
+            from  LineItem as l,
+                  (
+                        select element {'s_name':s.s_name,'s_suppkey':s.s_suppkey}
+                        from  Nation as n,
+                        Supplier as s
+                        where (s.s_nationkey = n.n_nationkey)
+                   ) as ns,
+                   Orders as o,
+                   tpch.tmp1() as t1
+            where ns.s_suppkey = l.l_suppkey and l.l_receiptdate > l.l_commitdate and o.o_orderkey = l.l_orderkey and l.l_orderkey = t1.l_orderkey
+    ) as t3,
+      tpch.tmp2() as t2
+    where ((t2.count_suppkey >= 0) and (t3.l_orderkey = t2.l_orderkey))
+) as t4
+group by t4.s_name as s_name
+with  numwait as tpch.count(t4)
+order by numwait desc,s_name
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
@@ -0,0 +1,146 @@
+/*
+ * 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  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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 tpch.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 tpch.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 tpch.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 tpch.NationType as
+ closed {
+  n_nationkey : int32,
+  n_name : string,
+  n_regionkey : int32,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int32,
+  r_name : string,
+  r_comment : string
+}
+
+create type tpch.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 tpch.PartSuppType as
+ closed {
+  ps_partkey : int32,
+  ps_suppkey : int32,
+  ps_availqty : int32,
+  ps_supplycost : double,
+  ps_comment : string
+}
+
+create  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Part(PartType) primary key p_partkey;
+
+create  table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index partsupp_fk_part on Partsupp (ps_partkey);
+create index partsupp_fk_supplier on Partsupp (ps_suppkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Part using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Partsupp using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
new file mode 100644
index 0000000..6136008
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function q22_customer_tmp() {
+(
+    select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':tpch.substring(c.c_phone,1,2)}
+    from  Customer as c
+)
+};
+with  avg as tpch.avg((
+      select element c.c_acctbal
+      from  Customer as c
+      where (c.c_acctbal > 0.0)
+  ))
+select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.sum((
+        select element i.c_acctbal
+        from  ct as i
+    ))}
+from  tpch.q22_customer_tmp() as ct
+where (ct.c_acctbal > avg)
+group by ct.cntrycode as cntrycode
+order by cntrycode
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.1.ddl.sqlpp
new file mode 100644
index 0000000..cabd3be
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.1.ddl.sqlpp
@@ -0,0 +1,152 @@
+/*
+ * 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 issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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 tpch.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 tpch.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 tpch.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 tpch.NationType as
+ closed {
+  n_nationkey : int32,
+  n_name : string,
+  n_regionkey : int32,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int32,
+  r_name : string,
+  r_comment : string
+}
+
+create type tpch.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 tpch.PartSuppType as
+ closed {
+  ps_partkey : int32,
+  ps_suppkey : int32,
+  ps_availqty : int32,
+  ps_supplycost : double,
+  ps_comment : string
+}
+
+create  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Part(PartType) primary key p_partkey;
+
+create  table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index partsupp_fk_part on Partsupp (ps_partkey);
+create index partsupp_fk_supplier on Partsupp (ps_suppkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.2.update.sqlpp
new file mode 100644
index 0000000..9d5125c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.2.update.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 case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Part using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Partsupp using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp
new file mode 100644
index 0000000..ac54a30
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.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  : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+use tpch;
+
+
+declare function q22_customer_tmp() {
+(
+    select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':phone_substr}
+    from  Customer as c
+    with  phone_substr as tpch.substring(c.c_phone,1,2)
+    where ((phone_substr = '13') or (phone_substr = '31') or (phone_substr = '23') or (phone_substr = '29') or (phone_substr = '30') or (phone_substr = '18') or (phone_substr = '17'))
+)
+};
+with  avg as tpch.avg((
+      select element c.c_acctbal
+      from  Customer as c
+      with  phone_substr as tpch.substring(c.c_phone,1,2)
+      where ((c.c_acctbal > 0.0) and ((phone_substr = '13') or (phone_substr = '31') or (phone_substr = '23') or (phone_substr = '29') or (phone_substr = '30') or (phone_substr = '18') or (phone_substr = '17')))
+  ))
+select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.sum((
+        select element i.c_acctbal
+        from  ct as i
+    ))}
+from  tpch.q22_customer_tmp() as ct
+where (tpch.count((
+    select element o
+    from  Orders as o
+    where (ct.c_custkey = o.o_custkey)
+)) = 0)
+group by ct.cntrycode as cntrycode
+order by cntrycode
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.1.ddl.sqlpp
new file mode 100644
index 0000000..c925fd4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+  l_orderkey : int64,
+  l_partkey : int64,
+  l_suppkey : int64,
+  l_linenumber : int64,
+  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  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.2.update.sqlpp
new file mode 100644
index 0000000..4dab63a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
new file mode 100644
index 0000000..efe6954
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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
+ */
+
+use tpch;
+
+
+select element {'l_linenumber':l_linenumber,'count_order':tpch.count(l)}
+from  LineItem as l
+group by l.l_linenumber as l_linenumber
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.1.ddl.sqlpp
new file mode 100644
index 0000000..2c8a9e5
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.1.ddl.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+  o_orderkey : int64,
+  o_custkey : int64,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : int64,
+  o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+  c_custkey : int64,
+  c_name : string,
+  c_address : string,
+  c_nationkey : int64,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+  s_suppkey : int64,
+  s_name : string,
+  s_address : string,
+  s_nationkey : int64,
+  s_phone : string,
+  s_acctbal : double,
+  s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+  n_nationkey : int64,
+  n_name : string,
+  n_regionkey : int64,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int64,
+  r_name : string,
+  r_comment : string
+}
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create  table SelectedNation(NationType) primary key n_nationkey;
+
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.2.update.sqlpp
new file mode 100644
index 0000000..1c14a94
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table SelectedNation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/selectednation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
new file mode 100644
index 0000000..2cad6ea
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.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  : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+with  t as (
+      select element {'n_nationkey':nation.n_nationkey,'n_name':nation.n_name}
+      from  Nation as nation,
+            SelectedNation as sn
+      where (nation.n_nationkey = sn.n_nationkey)
+  ),
+      X as (
+      select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':sum}
+      from  t as n,
+            Customer as customer,
+            Orders as orders
+      where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
+      group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
+      with  sum as tpch.sum((
+            select element o.o_totalprice
+            from  orders as o
+        ))
+  )
+select element {'nation_key':nation_key,'sum_price':(
+        select element {'orderdate':y.order_date,'sum_price':y.sum_price}
+        from  x as y
+        order by y.sum_price desc
+        limit 3
+    )}
+from  X as x
+group by x.nation_key as nation_key
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.1.ddl.sqlpp
new file mode 100644
index 0000000..2c8a9e5
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.1.ddl.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+  o_orderkey : int64,
+  o_custkey : int64,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : int64,
+  o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+  c_custkey : int64,
+  c_name : string,
+  c_address : string,
+  c_nationkey : int64,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+  s_suppkey : int64,
+  s_name : string,
+  s_address : string,
+  s_nationkey : int64,
+  s_phone : string,
+  s_acctbal : double,
+  s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+  n_nationkey : int64,
+  n_name : string,
+  n_regionkey : int64,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int64,
+  r_name : string,
+  r_comment : string
+}
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create  table SelectedNation(NationType) primary key n_nationkey;
+
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.2.update.sqlpp
new file mode 100644
index 0000000..1c14a94
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table SelectedNation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/selectednation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
new file mode 100644
index 0000000..ed649ca
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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 issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date         : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation_key,'sum_price':(
+        select element {'orderdate':od,'sum_price':sum}
+        from  x as i
+        group by i.order_date as od
+        with  sum as tpch.sum((
+              select element s.sum_price
+              from  i as s
+          ))
+        order by sum desc
+        limit 3
+    )}
+from  (
+    select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':tpch.sum((
+            select element o.o_totalprice
+            from  orders as o
+        ))}
+    from  Nation as n,
+          Customer as customer,
+          Orders as orders
+    where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
+    group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
+) as x
+group by x.nation_key as nation_key
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.1.ddl.sqlpp
new file mode 100644
index 0000000..b36e90e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.1.ddl.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 issue786
+ * https://code.google.com/p/asterixdb/issues/detail?id=786
+ * Expected Res : SUCCESS
+ * Date         : 10th Oct. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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 tpch.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 tpch.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 tpch.NationType as
+ closed {
+  n_nationkey : int32,
+  n_name : string,
+  n_regionkey : int32,
+  n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+  r_regionkey : int32,
+  r_name : string,
+  r_comment : string
+}
+
+create  table Orders(OrderType) primary key o_orderkey;
+
+create  table Supplier(SupplierType) primary key s_suppkey;
+
+create  table Region(RegionType) primary key r_regionkey;
+
+create  table Nation(NationType) primary key n_nationkey;
+
+create  table Customer(CustomerType) primary key c_custkey;
+
+create  table SelectedNation(NationType) primary key n_nationkey;
+
+
+create index customer_fk_nation on Customer (c_nationkey);
+create index nation_fk_region on Nation(n_regionkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index supplier_fk_nation on Supplier (s_nationkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.2.update.sqlpp
new file mode 100644
index 0000000..90e2139
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 issue786
+ * https://code.google.com/p/asterixdb/issues/detail?id=786
+ * Expected Res : SUCCESS
+ * Date         : 10th Oct. 2014
+ */
+
+use tpch;
+
+
+load  table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Supplier using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Region using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Nation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load  table SelectedNation using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/selectednation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
new file mode 100644
index 0000000..dead643
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.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 case is to verify the fix for issue786
+ * https://code.google.com/p/asterixdb/issues/detail?id=786
+ * Expected Res : SUCCESS
+ * Date         : 10th Oct. 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+        select element {'order_date':orderdate,'sum_price':sum}
+        from  Orders as orders,
+              Customer as customer
+        where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
+        group by orders.o_orderdate as orderdate
+        with  sum as tpch.sum((
+              select element o.o_totalprice
+              from  orders as o
+          ))
+        order by sum desc
+        limit 3
+    )}
+from  Nation as nation,
+      SelectedNation as sn
+where (nation.n_nationkey = sn.sn_nationkey)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.1.ddl.sqlpp
new file mode 100644
index 0000000..6d147c2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.2.update.sqlpp
new file mode 100644
index 0000000..ab2b193
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
new file mode 100644
index 0000000..16d3fe0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.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 case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheaps),'total_charges':tpch.sum(charges)}
+from  LineItem as l
+where (l.l_shipdate <= '1998-09-02')
+/* +hash */
+group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
+with  cheaps as (
+      select element m
+      from  l as m
+      where (m.l_discount > 0.05)
+  ),
+      charges as (
+      select element (a.l_extendedprice * (1 - a.l_discount) * (1 + a.l_tax))
+      from  l as a
+  )
+order by l_returnflag,l_linestatus
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.1.ddl.sqlpp
new file mode 100644
index 0000000..6d147c2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.2.update.sqlpp
new file mode 100644
index 0000000..ab2b193
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
new file mode 100644
index 0000000..576192d
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.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  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheaps),'avg_expensive_discounts':tpch.avg(expensives),'sum_disc_prices':tpch.sum(disc_prices),'total_charges':tpch.sum(charges)}
+from  LineItem as l
+where (l.l_shipdate <= '1998-09-02')
+/* +hash */
+group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
+with  expensives as (
+      select element i.l_discount
+      from  l as i
+      where (i.l_discount <= 0.05)
+  ),
+      cheaps as (
+      select element i
+      from  l as i
+      where (i.l_discount > 0.05)
+  ),
+      charges as (
+      select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
+      from  l as i
+  ),
+      disc_prices as (
+      select element (i.l_extendedprice * (1 - i.l_discount))
+      from  l as i
+  )
+order by l_returnflag,l_linestatus
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.1.ddl.sqlpp
new file mode 100644
index 0000000..6d147c2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop  database tpch if exists;
+create  database tpch;
+
+use tpch;
+
+
+create type tpch.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  table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.2.update.sqlpp
new file mode 100644
index 0000000..ab2b193
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+load  table LineItem using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
new file mode 100644
index 0000000..01e1654
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.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 case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheap),'count_expensives':tpch.count(expensive)}
+from  LineItem as l
+where (l.l_shipdate <= '1998-09-02')
+/* +hash */
+group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
+with  cheap as (
+      select element m
+      from  l as m
+      where (m.l_discount > 0.05)
+  ),
+      expensive as (
+      select element a
+      from  l as a
+      where (a.l_discount <= 0.05)
+  )
+order by l_returnflag,l_linestatus
+;


Mime
View raw message