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:11 UTC
[5/7] incubator-asterixdb git commit: ASTERIXDB-1128: add TPC-H
queries with secondary indices into build tests.
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
new file mode 100644
index 0000000..afa7c9b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.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.
+ */
+
+use tpch;
+
+
+set "import-private-functions" "true";
+
+select element {'c_count':c_count,'custdist':custdist}
+from (
+ select element {'c_custkey':c_custkey,'c_count':tpch.sum((
+ select element i.o_orderkey_count
+ from co as i
+ ))}
+ from (
+ select element {'c_custkey':c.c_custkey,'o_orderkey_count':tpch.count((
+ select element o.o_orderkey
+ from Orders as o
+ where ((c.c_custkey = o.o_custkey) and tpch.not(tpch.like(o.o_comment,'%special%requests%')))
+ ))}
+ from Customer as c
+ ) as co
+ group by co.c_custkey as c_custkey
+) as gco
+group by gco.c_count as c_count
+with custdist as tpch.count(gco)
+order by custdist desc,c_count desc
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.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/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.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/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
new file mode 100644
index 0000000..559dfc8
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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 (100.0 * tpch.sum((
+ select element tpch."switch-case"(tpch.like(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
+ from l as i
+ )) / tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ )))
+from LineItem as l,
+ Part as p
+where ((l.l_partkey = p.p_partkey) and (l.l_shipdate >= '1995-09-01') and (l.l_shipdate < '1995-10-01'))
+group by 1 as t
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.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/q15_top_supplier/q15_top_supplier.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_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/q15_top_supplier/q15_top_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/q15_top_supplier/q15_top_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp
new file mode 100644
index 0000000..e21104b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.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 revenue() {
+(
+ select element {'supplier_no':l_suppkey,'total_revenue':tpch.sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ ))}
+ from LineItem as l
+ where ((l.l_shipdate >= '1996-01-01') and (l.l_shipdate < '1996-04-01'))
+ group by l.l_suppkey as l_suppkey
+)
+};
+with m as tpch.max((
+ select element r2.total_revenue
+ from tpch.revenue() as r2
+ ))
+select element {'s_suppkey':s.s_suppkey,'s_name':s.s_name,'s_address':s.s_address,'s_phone':s.s_phone,'total_revenue':r.total_revenue}
+from Supplier as s,
+ tpch.revenue() as r
+where ((s.s_suppkey = r.supplier_no) and (r.total_revenue < (m + 0.000000001)) and (r.total_revenue > (m - 0.000000001)))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.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/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.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/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
new file mode 100644
index 0000000..cc4d01a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp() {
+(
+ select element {'p_brand':psp.p_brand,'p_type':psp.p_type,'p_size':psp.p_size,'ps_suppkey':psp.ps_suppkey}
+ from (
+ select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
+ from Partsupp as ps,
+ Part as p
+ where ((p.p_partkey = ps.ps_partkey) and (p.p_brand != 'Brand#45') and tpch.not(tpch.like(p.p_type,'MEDIUM POLISHED%')))
+ ) as psp,
+ Supplier as s
+ where ((psp.ps_suppkey = s.s_suppkey) and tpch.not(tpch.like(s.s_comment,'%Customer%Complaints%')))
+)
+};
+select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
+from (
+ select element {'p_brand':p_brand1,'p_type':p_type1,'p_size':p_size1,'ps_suppkey':ps_suppkey1}
+ from tpch.tmp() as t
+ where ((t.p_size = 49) or (t.p_size = 14) or (t.p_size = 23) or (t.p_size = 45) or (t.p_size = 19) or (t.p_size = 3) or (t.p_size = 36) or (t.p_size = 9))
+ group by t.p_brand as p_brand1,t.p_type as p_type1,t.p_size as p_size1,t.ps_suppkey as ps_suppkey1
+) as t2
+group by t2.p_brand as p_brand,t2.p_type as p_type,t2.p_size as p_size
+with supplier_cnt as tpch.count((
+ select element i.ps_suppkey
+ from t2 as i
+ ))
+order by supplier_cnt desc,p_brand,p_type,p_size
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.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/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.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/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
new file mode 100644
index 0000000..ada4f75
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.avg((
+ select element i.l_quantity
+ from l as i
+ ))),'t_max_suppkey':tpch.max((
+ select element i.l_suppkey
+ from l as i
+ )),'t_max_linenumber':tpch.max((
+ select element i.l_linenumber
+ from l as i
+ )),'t_avg_extendedprice':tpch.avg((
+ select element i.l_extendedprice
+ from l as i
+ )),'t_avg_discount':tpch.avg((
+ select element i.l_discount
+ from l as i
+ )),'t_avg_tax':tpch.avg((
+ select element i.l_tax
+ from l as i
+ )),'t_max_shipdate':tpch.max((
+ select element i.l_shipdate
+ from l as i
+ )),'t_min_commitdate':tpch.min((
+ select element i.l_commitdate
+ from l as i
+ )),'t_min_receiptdate':tpch.min((
+ select element i.l_receiptdate
+ from l as i
+ )),'t_max_comment':tpch.max((
+ select element i.l_comment
+ from l as i
+ ))}
+from LineItem as l
+group by l.l_partkey as l_partkey
+order by l_partkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.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/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.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/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
new file mode 100644
index 0000000..79c397c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.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.
+ */
+
+use tpch;
+
+
+declare function tmp() {
+(
+ select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.avg((
+ select element i.l_quantity
+ from l as i
+ )))}
+ from LineItem as l
+ group by l.l_partkey as l_partkey
+)
+};
+
+select element (tpch.sum((
+ select element l.l_extendedprice
+ from tpch.tmp() as t,
+ LineItem as l,
+ Part as p
+ where p.p_partkey = l.l_partkey and p.p_container = 'MED BOX' and l.l_partkey = t.t_partkey and l.l_quantity < t.t_avg_quantity
+ )) / 7.0);
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp
new file mode 100644
index 0000000..0fdf4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.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/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.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/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
new file mode 100644
index 0000000..89ff8f8
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.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;
+
+
+select element {'c_name':c_name,'c_custkey':c_custkey,'o_orderkey':o_orderkey,'o_orderdate':o_orderdate,'o_totalprice':o_totalprice,'sum_quantity':tpch.sum((
+ select element j.l_quantity
+ from l as j
+ ))}
+from Customer as c,
+ Orders as o,
+ (
+ select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.sum((
+ select element i.l_quantity
+ from l as i
+ ))}
+ from LineItem as l
+ group by l.l_orderkey as l_orderkey
+) as t,
+ LineItem as l
+where ((c.c_custkey = o.o_custkey) and (o.o_orderkey = t.l_orderkey) and (t.t_sum_quantity > 30) and (l.l_orderkey = t.l_orderkey))
+group by c.c_name as c_name,c.c_custkey as c_custkey,o.o_orderkey as o_orderkey,o.o_orderdate as o_orderdate,o.o_totalprice as o_totalprice
+order by o_totalprice desc,o_orderdate
+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/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.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/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.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/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
new file mode 100644
index 0000000..7065f87
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.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.
+ */
+
+use tpch;
+
+
+set "import-private-functions" "true";
+
+select element tpch.sum((
+ select element (l.l_extendedprice * (1 - l.l_discount))
+ from LineItem as l,
+ Part as p
+ where ((p.p_partkey = l.l_partkey) and (((p.p_brand = 'Brand#12') and tpch."reg-exp"(p.p_container,'SM CASE||SM BOX||SM PACK||SM PKG') and (l.l_quantity >= 1) and (l.l_quantity <= 11) and (p.p_size >= 1) and (p.p_size <= 5) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#23') and tpch."reg-exp"(p.p_container,'MED BAG||MED BOX||MED PKG||MED PACK') and (l.l_quantity >= 10) and (l.l_quantity <= 20) and (p.p_size >= 1) and (p.p_size <= 10) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#34') and tpch."reg-exp"(p.p_container,'LG CASE||LG BOX||LG PACK||LG PKG') and (l.l_quantity >= 20) and (l.l_quantity <= 30) and (p.p_size >= 1) and (p.p_size <= 15) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON'))))
+));
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/fb431ffe/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp
new file mode 100644
index 0000000..b881621
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.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/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp
new file mode 100644
index 0000000..824fc6b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.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/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
new file mode 100644
index 0000000..0657ad3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.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.
+ */
+
+use tpch;
+
+
+select element {'s_name':t4.s_name,'s_address':t4.s_address}
+from (
+ select distinct element {'ps_suppkey':pst1.ps_suppkey}
+ from (
+ select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * tpch.sum((
+ select element i.l_quantity
+ from l as i
+ )))}
+ from LineItem as l
+ group by l.l_partkey as l_partkey,l.l_suppkey as l_suppkey
+ ) as t2,
+ (
+ select element {'ps_suppkey':ps.ps_suppkey,'ps_partkey':ps.ps_partkey,'ps_availqty':ps.ps_availqty}
+ from Partsupp as ps,
+ (
+ select distinct element {'p_partkey':p.p_partkey}
+ from Part as p
+ ) as t1
+ where (ps.ps_partkey = t1.p_partkey)
+ ) as pst1
+ where ((pst1.ps_partkey = t2.l_partkey) and (pst1.ps_suppkey = t2.l_suppkey) and (pst1.ps_availqty > t2.sum_quantity))
+) as t3,
+ (
+ select element {'s_name':s.s_name,'s_address':s.s_address,'s_suppkey':s.s_suppkey}
+ from Nation as n,
+ Supplier as s
+ where (s.s_nationkey = n.n_nationkey)
+) as t4
+where (t3.ps_suppkey = t4.s_suppkey)
+order by t4.s_name
+;