You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2016/03/13 08:14:13 UTC
[7/7] incubator-asterixdb git commit: ASTERIXDB-1128: add TPC-H
queries with secondary indices into build tests.
ASTERIXDB-1128: add TPC-H queries with secondary indices into build tests.
Change-Id: I4237ca17d5e334959a320ed92fcdccf51cc4856d
Reviewed-on: https://asterix-gerrit.ics.uci.edu/704
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Till Westmann <ti...@apache.org>
Project: http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/commit/fb431ffe
Tree: http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/tree/fb431ffe
Diff: http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/diff/fb431ffe
Branch: refs/heads/master
Commit: fb431ffea6200d0c68e7331186d019c1ecbd568c
Parents: 472d2f5
Author: Yingyi Bu <yi...@couchbase.com>
Authored: Thu Mar 10 16:02:45 2016 -0800
Committer: Yingyi Bu <bu...@gmail.com>
Committed: Sat Mar 12 23:08:57 2016 -0800
----------------------------------------------------------------------
.../distinct_by/distinct_by.1.ddl.sqlpp | 52 +++++
.../distinct_by/distinct_by.2.update.sqlpp | 24 +++
.../distinct_by/distinct_by.3.query.sqlpp | 26 +++
.../nest_aggregate/nest_aggregate.1.ddl.sqlpp | 99 +++++++++
.../nest_aggregate.2.update.sqlpp | 40 ++++
.../nest_aggregate/nest_aggregate.3.query.sqlpp | 46 +++++
.../nest_aggregate2/nest_aggregate2.1.ddl.sqlpp | 99 +++++++++
.../nest_aggregate2.2.update.sqlpp | 40 ++++
.../nest_aggregate2.3.query.sqlpp | 46 +++++
.../q01_pricing_summary_report_nt.1.ddl.sqlpp | 54 +++++
...q01_pricing_summary_report_nt.2.update.sqlpp | 24 +++
.../q01_pricing_summary_report_nt.3.query.sqlpp | 52 +++++
.../q02_minimum_cost_supplier.1.ddl.sqlpp | 147 ++++++++++++++
.../q02_minimum_cost_supplier.2.update.sqlpp | 38 ++++
.../q02_minimum_cost_supplier.3.query.sqlpp | 80 ++++++++
.../q03_shipping_priority_nt.1.ddl.sqlpp | 146 ++++++++++++++
.../q03_shipping_priority_nt.2.update.sqlpp | 28 +++
.../q03_shipping_priority_nt.3.query.sqlpp | 36 ++++
.../q04_order_priority.1.ddl.sqlpp | 146 ++++++++++++++
.../q04_order_priority.2.update.sqlpp | 38 ++++
.../q04_order_priority.3.query.sqlpp | 36 ++++
.../q05_local_supplier_volume.1.ddl.sqlpp | 146 ++++++++++++++
.../q05_local_supplier_volume.2.update.sqlpp | 38 ++++
.../q05_local_supplier_volume.3.query.sqlpp | 54 +++++
.../q06_forecast_revenue_change.1.ddl.sqlpp | 147 ++++++++++++++
.../q06_forecast_revenue_change.2.update.sqlpp | 38 ++++
.../q06_forecast_revenue_change.3.query.sqlpp | 27 +++
.../q07_volume_shipping.1.ddl.sqlpp | 146 ++++++++++++++
.../q07_volume_shipping.2.update.sqlpp | 38 ++++
.../q07_volume_shipping.3.query.sqlpp | 57 ++++++
.../q08_national_market_share.1.ddl.sqlpp | 146 ++++++++++++++
.../q08_national_market_share.2.update.sqlpp | 38 ++++
.../q08_national_market_share.3.query.sqlpp | 69 +++++++
.../q09_product_type_profit_nt.1.ddl.sqlpp | 146 ++++++++++++++
.../q09_product_type_profit_nt.2.update.sqlpp | 38 ++++
.../q09_product_type_profit_nt.3.query.sqlpp | 57 ++++++
.../q10_returned_item.1.ddl.sqlpp | 146 ++++++++++++++
.../q10_returned_item.2.update.sqlpp | 38 ++++
.../q10_returned_item.3.query.sqlpp | 43 ++++
.../q10_returned_item_int64.1.ddl.sqlpp | 146 ++++++++++++++
.../q10_returned_item_int64.2.update.sqlpp | 38 ++++
.../q10_returned_item_int64.3.query.sqlpp | 43 ++++
.../q11_important_stock.1.ddl.sqlpp | 146 ++++++++++++++
.../q11_important_stock.2.update.sqlpp | 38 ++++
.../q11_important_stock.3.query.sqlpp | 52 +++++
.../q12_shipping/q12_shipping.1.ddl.sqlpp | 146 ++++++++++++++
.../q12_shipping/q12_shipping.2.update.sqlpp | 38 ++++
.../q12_shipping/q12_shipping.3.query.sqlpp | 35 ++++
.../q13_customer_distribution.1.ddl.sqlpp | 146 ++++++++++++++
.../q13_customer_distribution.2.update.sqlpp | 38 ++++
.../q13_customer_distribution.3.query.sqlpp | 44 ++++
.../q14_promotion_effect.1.ddl.sqlpp | 146 ++++++++++++++
.../q14_promotion_effect.2.update.sqlpp | 38 ++++
.../q14_promotion_effect.3.query.sqlpp | 34 ++++
.../q15_top_supplier.1.ddl.sqlpp | 146 ++++++++++++++
.../q15_top_supplier.2.update.sqlpp | 38 ++++
.../q15_top_supplier.3.query.sqlpp | 42 ++++
.../q16_parts_supplier_relationship.1.ddl.sqlpp | 146 ++++++++++++++
...6_parts_supplier_relationship.2.update.sqlpp | 38 ++++
...16_parts_supplier_relationship.3.query.sqlpp | 49 +++++
.../q17_large_gby_variant.1.ddl.sqlpp | 146 ++++++++++++++
.../q17_large_gby_variant.2.update.sqlpp | 38 ++++
.../q17_large_gby_variant.3.query.sqlpp | 57 ++++++
...q17_small_quantity_order_revenue.1.ddl.sqlpp | 146 ++++++++++++++
..._small_quantity_order_revenue.2.update.sqlpp | 38 ++++
...7_small_quantity_order_revenue.3.query.sqlpp | 40 ++++
.../q18_large_volume_customer.1.ddl.sqlpp | 146 ++++++++++++++
.../q18_large_volume_customer.2.update.sqlpp | 38 ++++
.../q18_large_volume_customer.3.query.sqlpp | 42 ++++
.../q19_discounted_revenue.1.ddl.sqlpp | 146 ++++++++++++++
.../q19_discounted_revenue.2.update.sqlpp | 38 ++++
.../q19_discounted_revenue.3.query.sqlpp | 30 +++
.../q20_potential_part_promotion.1.ddl.sqlpp | 146 ++++++++++++++
.../q20_potential_part_promotion.2.update.sqlpp | 38 ++++
.../q20_potential_part_promotion.3.query.sqlpp | 53 +++++
...uppliers_who_kept_orders_waiting.1.ddl.sqlpp | 146 ++++++++++++++
...liers_who_kept_orders_waiting.2.update.sqlpp | 38 ++++
...pliers_who_kept_orders_waiting.3.query.sqlpp | 80 ++++++++
.../q22_global_sales_opportunity.1.ddl.sqlpp | 146 ++++++++++++++
.../q22_global_sales_opportunity.2.update.sqlpp | 38 ++++
.../q22_global_sales_opportunity.3.query.sqlpp | 42 ++++
.../query-issue562/query-issue562.1.ddl.sqlpp | 152 ++++++++++++++
.../query-issue562.2.update.sqlpp | 44 ++++
.../query-issue562/query-issue562.3.query.sqlpp | 55 +++++
.../query-issue601/query-issue601.1.ddl.sqlpp | 58 ++++++
.../query-issue601.2.update.sqlpp | 30 +++
.../query-issue601/query-issue601.3.query.sqlpp | 32 +++
.../query-issue785-2.1.ddl.sqlpp | 100 ++++++++++
.../query-issue785-2.2.update.sqlpp | 40 ++++
.../query-issue785-2.3.query.sqlpp | 55 +++++
.../query-issue785/query-issue785.1.ddl.sqlpp | 100 ++++++++++
.../query-issue785.2.update.sqlpp | 40 ++++
.../query-issue785/query-issue785.3.query.sqlpp | 52 +++++
.../query-issue786/query-issue786.1.ddl.sqlpp | 100 ++++++++++
.../query-issue786.2.update.sqlpp | 40 ++++
.../query-issue786/query-issue786.3.query.sqlpp | 45 +++++
.../query-issue810-2.1.ddl.sqlpp | 58 ++++++
.../query-issue810-2.2.update.sqlpp | 30 +++
.../query-issue810-2.3.query.sqlpp | 44 ++++
.../query-issue810-3.1.ddl.sqlpp | 58 ++++++
.../query-issue810-3.2.update.sqlpp | 30 +++
.../query-issue810-3.3.query.sqlpp | 53 +++++
.../query-issue810/query-issue810.1.ddl.sqlpp | 58 ++++++
.../query-issue810.2.update.sqlpp | 30 +++
.../query-issue810/query-issue810.3.query.sqlpp | 45 +++++
.../query-issue827-2.1.ddl.sqlpp | 58 ++++++
.../query-issue827-2.2.update.sqlpp | 30 +++
.../query-issue827-2.3.query.sqlpp | 55 +++++
.../query-issue827/query-issue827.1.ddl.sqlpp | 58 ++++++
.../query-issue827.2.update.sqlpp | 30 +++
.../query-issue827/query-issue827.3.query.sqlpp | 38 ++++
.../distinct_by/distinct_by.1.adm | 28 +++
.../nest_aggregate/nest_aggregate.1.adm | 11 +
.../nest_aggregate2/nest_aggregate2.1.adm | 11 +
.../q01_pricing_summary_report_nt.1.adm | 4 +
.../q02_minimum_cost_supplier.1.adm | 13 ++
.../q03_shipping_priority_nt.1.adm | 8 +
.../q04_order_priority/q04_order_priority.1.adm | 5 +
.../q05_local_supplier_volume.1.adm | 8 +
.../q06_forecast_revenue_change.1.adm | 1 +
.../q07_volume_shipping.1.adm | 37 ++++
.../q08_national_market_share.1.adm | 2 +
.../q09_product_type_profit_nt.1.adm | 59 ++++++
.../q10_returned_item/q10_returned_ite.1.adm | 20 ++
.../q10_returned_item_int64.1.adm | 20 ++
.../q11_important_stock.1.adm | 200 +++++++++++++++++++
.../q12_shipping/q12_shipping.1.adm | 2 +
.../q13_customer_distribution.1.adm | 27 +++
.../q14_promotion_effect.1.adm | 1 +
.../q15_top_supplier/q15_top_supplier.1.adm | 1 +
.../q16_parts_supplier_relationship.1.adm | 34 ++++
.../q17_large_gby_variant.3.adm | 200 +++++++++++++++++++
.../q17_small_quantity_order_revenue.1.adm | 1 +
.../q18_large_volume_customer.1.adm | 100 ++++++++++
.../q19_discounted_revenue.1.adm | 1 +
.../q20_potential_part_promotion.1.adm | 10 +
.../q21_suppliers_who_kept_orders_waiting.1.adm | 10 +
.../q22_global_sales_opportunity.1.adm | 23 +++
.../query-ASTERIXDB-1127.1.adm | 8 +
.../query-issue562/query-issue562.1.adm | 7 +
.../query-issue601/query-issue601.1.adm | 7 +
.../query-issue785-2/query-issue785-2.1.adm | 10 +
.../query-issue785/query-issue785.1.adm | 24 +++
.../query-issue786/query-issue786.1.adm | 0
.../query-issue810-2/query-issue810-2.1.adm | 4 +
.../query-issue810-3/query-issue810-3.1.adm | 4 +
.../query-issue810/query-issue810.1.adm | 4 +
.../query-issue827-2/query-issue827-2.1.adm | 1 +
.../query-issue827/query-issue827.1.adm | 1 +
.../resources/runtimets/testsuite_sqlpp.xml | 187 +++++++++++++++++
150 files changed, 8640 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.1.ddl.sqlpp
new file mode 100644
index 0000000..86fe00c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.1.ddl.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.
+ */
+
+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/distinct_by/distinct_by.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.2.update.sqlpp
new file mode 100644
index 0000000..8353a07
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use 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/distinct_by/distinct_by.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.3.query.sqlpp
new file mode 100644
index 0000000..da0745e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/distinct_by/distinct_by.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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;
+
+
+select distinct element {'l_returnflag':l.l_returnflag,'l_linestatus':l.l_linestatus,'l_shipmode':l.l_shipmode}
+from LineItem as l
+order by l.l_returnflag,l.l_linestatus,l.l_shipmode
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.1.ddl.sqlpp
new file mode 100644
index 0000000..d2ca9f7
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.1.ddl.sqlpp
@@ -0,0 +1,99 @@
+/*
+ * 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 issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 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/nest_aggregate/nest_aggregate.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.2.update.sqlpp
new file mode 100644
index 0000000..24ff26a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.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 issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 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/nest_aggregate/nest_aggregate.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
new file mode 100644
index 0000000..3a015ae
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 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
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
+order by nation.n_nationkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.1.ddl.sqlpp
new file mode 100644
index 0000000..d2ca9f7
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.1.ddl.sqlpp
@@ -0,0 +1,99 @@
+/*
+ * 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 issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 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/nest_aggregate2/nest_aggregate2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.2.update.sqlpp
new file mode 100644
index 0000000..24ff26a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.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 issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 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/nest_aggregate2/nest_aggregate2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
new file mode 100644
index 0000000..1aa687c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+ select element orderdate
+ 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
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
+order by nation.n_nationkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.1.ddl.sqlpp
new file mode 100644
index 0000000..935e489
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.1.ddl.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 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/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.2.update.sqlpp
new file mode 100644
index 0000000..8353a07
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use 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/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
new file mode 100644
index 0000000..94b313f
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.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.
+ */
+
+use tpch;
+
+
+set "import-private-functions" "true";
+
+select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.sum((
+ select element i.l_quantity
+ from l as i
+ )),'sum_base_price':tpch.sum((
+ select element i.l_extendedprice
+ from l as i
+ )),'sum_disc_price':tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ )),'sum_charge':tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
+ from l as i
+ )),'ave_qty':tpch.avg((
+ select element i.l_quantity
+ from l as i
+ )),'ave_price':tpch.avg((
+ select element i.l_extendedprice
+ from l as i
+ )),'ave_disc':tpch.avg((
+ select element i.l_discount
+ from l as i
+ )),'count_order':tpch.count(l)}
+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
+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/q02_minimum_cost_supplier/q02_minimum_cost_supplier.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.1.ddl.sqlpp
new file mode 100644
index 0000000..f50eb7c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.1.ddl.sqlpp
@@ -0,0 +1,147 @@
+/*
+ * 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/q02_minimum_cost_supplier/q02_minimum_cost_supplier.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.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/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
new file mode 100644
index 0000000..a55c74d
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.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 {'s_acctbal':pssrn.s_acctbal,'s_name':pssrn.s_name,'n_name':pssrn.n_name,'p_partkey':p.p_partkey,'ps_supplycost':pssrn.ps_supplycost,'p_mfgr':p.p_mfgr,'s_address':pssrn.s_address,'s_phone':pssrn.s_phone,'s_comment':pssrn.s_comment}
+ from Part as p,
+ (
+ select element {'n_name':srn.n_name,'p_partkey':ps.ps_partkey,'ps_supplycost':ps.ps_supplycost,'s_name':srn.s_name,'s_acctbal':srn.s_acctbal,'s_address':srn.s_address,'s_phone':srn.s_phone,'s_comment':srn.s_comment}
+ from Partsupp as ps,
+ (
+ select element {'s_suppkey':s.s_suppkey,'n_name':rn.n_name,'s_name':s.s_name,'s_acctbal':s.s_acctbal,'s_address':s.s_address,'s_phone':s.s_phone,'s_comment':s.s_comment}
+ from Supplier as s,
+ (
+ select element {'n_nationkey':n.n_nationkey,'n_name':n.n_name}
+ from Region as r,
+ Nation as n
+ where ((n.n_regionkey = r.r_regionkey) and (r.r_name = 'EUROPE'))
+ ) as rn
+ where (s.s_nationkey = rn.n_nationkey)
+ ) as srn
+ where (srn.s_suppkey = ps.ps_suppkey)
+ ) as pssrn
+ where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+)
+};
+declare function tmp2() {
+(
+ select element {'p_partkey':p_partkey,'ps_min_supplycost':tpch.min((
+ select element i.ps_supplycost
+ from pssrn as i
+ ))}
+ from Part as p,
+ (
+ select element {'n_name':srn.n_name,'p_partkey':ps.ps_partkey,'ps_supplycost':ps.ps_supplycost,'s_name':srn.s_name,'s_acctbal':srn.s_acctbal,'s_address':srn.s_address,'s_phone':srn.s_phone,'s_comment':srn.s_comment}
+ from Partsupp as ps,
+ (
+ select element {'s_suppkey':s.s_suppkey,'n_name':rn.n_name,'s_name':s.s_name,'s_acctbal':s.s_acctbal,'s_address':s.s_address,'s_phone':s.s_phone,'s_comment':s.s_comment}
+ from Supplier as s,
+ (
+ select element {'n_nationkey':n.n_nationkey,'n_name':n.n_name}
+ from Region as r,
+ Nation as n
+ where ((n.n_regionkey = r.r_regionkey) and (r.r_name = 'EUROPE'))
+ ) as rn
+ where (s.s_nationkey = rn.n_nationkey)
+ ) as srn
+ where (srn.s_suppkey = ps.ps_suppkey)
+ ) as pssrn
+ where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+ /* +hash */
+ group by pssrn.p_partkey as p_partkey
+)
+};
+select element {'s_acctbal':t1.s_acctbal,'s_name':t1.s_name,'n_name':t1.n_name,'p_partkey':t1.p_partkey,'p_mfgr':t1.p_mfgr,'s_address':t1.s_address,'s_phone':t1.s_phone,'s_comment':t1.s_comment}
+from tpch.tmp2() as t2,
+ tpch.tmp1() as t1
+where ((t1.p_partkey = t2.p_partkey) and (t1.ps_supplycost = t2.ps_min_supplycost))
+order by t1.s_acctbal desc,t1.n_name,t1.s_name,t1.p_partkey
+limit 100
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.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/q03_shipping_priority_nt/q03_shipping_priority_nt.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.2.update.sqlpp
new file mode 100644
index 0000000..e27ea4e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.2.update.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use 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;
+
+load table Orders using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
+load table Customer using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter" (("path"="asterix_nc1://data/tpch0.001/customer.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/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
new file mode 100644
index 0000000..d30c6d3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * 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;
+
+
+select element {'l_orderkey':l_orderkey,'revenue':revenue,'o_orderdate':o_orderdate,'o_shippriority':o_shippriority}
+from Customer as c,
+ Orders as o,
+ LineItem as l
+where (((c.c_mktsegment = 'BUILDING') and (c.c_custkey = o.o_custkey)) and ((l.l_orderkey = o.o_orderkey) and (o.o_orderdate < '1995-03-15') and (l.l_shipdate > '1995-03-15')))
+/* +hash */
+group by l.l_orderkey as l_orderkey,o.o_orderdate as o_orderdate,o.o_shippriority as o_shippriority
+with revenue as tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ ))
+order by revenue desc,o_orderdate
+limit 10
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.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/q04_order_priority/q04_order_priority.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.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/q04_order_priority/q04_order_priority.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.3.query.sqlpp
new file mode 100644
index 0000000..f693ba3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q04_order_priority/q04_order_priority.3.query.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * 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 tmp() {
+(
+ select distinct element {'o_orderkey':l.l_orderkey}
+ from LineItem as l
+ where (l.l_commitdate < l.l_receiptdate)
+)
+};
+select element {'order_priority':o_orderpriority,'count':count(o)}
+from Orders as o join
+ tpch.tmp() as t on o.o_orderkey = t.o_orderkey
+where o.o_orderdate >= '1993-07-01' and o.o_orderdate < '1993-10-01'
+group by o.o_orderpriority as o_orderpriority
+order by o_orderpriority
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.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/q05_local_supplier_volume/q05_local_supplier_volume.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.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/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
new file mode 100644
index 0000000..150e4b1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.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.
+ */
+
+use tpch;
+
+
+select element {'n_name':n_name,'revenue':revenue}
+from Customer as c,
+ (
+ select element {'n_name':l1.n_name,'l_extendedprice':l1.l_extendedprice,'l_discount':l1.l_discount,'s_nationkey':l1.s_nationkey,'o_custkey':o.o_custkey}
+ from Orders as o,
+ (
+ select element {'n_name':s1.n_name,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_orderkey':l.l_orderkey,'s_nationkey':s1.s_nationkey}
+ from LineItem as l,
+ (
+ select element {'n_name':n1.n_name,'s_suppkey':s.s_suppkey,'s_nationkey':s.s_nationkey}
+ from Supplier as s,
+ (
+ select element {'n_name':n.n_name,'n_nationkey':n.n_nationkey}
+ from Nation as n,
+ Region as r
+ where (n.n_regionkey = r.r_regionkey)
+ ) as n1
+ where (s.s_nationkey = n1.n_nationkey)
+ ) as s1
+ where (l.l_suppkey = s1.s_suppkey)
+ ) as l1
+ where ((l1.l_orderkey = o.o_orderkey) and (o.o_orderdate >= '1990-01-01') and (o.o_orderdate < '1995-01-01'))
+) as o1
+where ((c.c_nationkey = o1.s_nationkey) and (c.c_custkey = o1.o_custkey))
+/* +hash */
+group by o1.n_name as n_name
+with revenue as tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from o1 as i
+ ))
+order by revenue desc
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.1.ddl.sqlpp
new file mode 100644
index 0000000..46ee240
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.1.ddl.sqlpp
@@ -0,0 +1,147 @@
+/*
+ * 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);