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/28 22:18:56 UTC
[29/51] [partial] incubator-asterixdb git commit: ASTERIXDB-1226:
implement SQL++ core group-by semantics and syntatic sugars.
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp
new file mode 100644
index 0000000..907be7b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.1.ddl.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.2.update.sqlpp
new file mode 100644
index 0000000..97a0b31
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/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 localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
new file mode 100644
index 0000000..2e2a632
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+WITH tmp AS
+(
+ SELECT l_orderkey l_orderkey, sum(l.l_quantity) t_sum_quantity
+ FROM LineItem AS l
+ GROUP BY l.l_orderkey as l_orderkey
+)
+
+SELECT c_name c_name, c_custkey c_custkey, o_orderkey o_orderkey,
+ o_orderdate o_orderdate, o_totalprice o_totalprice,
+ sum(l.l_quantity) sum_quantity
+FROM Customer c,
+ Orders o,
+ tmp t,
+ LineItem 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/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp
new file mode 100644
index 0000000..176cdc2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.1.ddl.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.2.update.sqlpp
new file mode 100644
index 0000000..97a0b31
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/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 localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
new file mode 100644
index 0000000..558786c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+set "import-private-functions" "true";
+
+sum(
+(
+ SELECT ELEMENT l.l_extendedprice * (1 - l.l_discount)
+ FROM LineItem l
+ JOIN Part p
+ ON p.p_partkey = l.l_partkey
+ WHERE
+ (
+ p.p_brand = 'Brand#12'
+ AND "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 "reg-exp"(l.l_shipmode, 'AIR||AIR REG')
+ AND l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ OR
+ (
+ p.p_brand = 'Brand#23'
+ AND "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 "reg-exp"(l.l_shipmode, 'AIR||AIR REG')
+ AND l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ OR
+ (
+ p.p_brand = 'Brand#34'
+ AND "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 "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/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp
new file mode 100644
index 0000000..176cdc2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.1.ddl.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.2.update.sqlpp
new file mode 100644
index 0000000..97a0b31
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/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 localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
new file mode 100644
index 0000000..4e71cc7
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+declare function q20_tmp1() {
+(
+ SELECT DISTINCT p.p_partkey as p_partkey
+ FROM Part p
+)
+};
+
+declare function q20_tmp2() {
+(
+ SELECT l_partkey AS l_partkey, l_suppkey AS l_suppkey, 0.5 * sum(l.l_quantity) AS sum_quantity
+ FROM LineItem l
+ GROUP BY l.l_partkey l_partkey, l.l_suppkey l_suppkey
+)
+};
+
+declare function q20_tmp3() {
+(
+ SELECT ps.ps_suppkey ps_suppkey, ps.ps_availqty ps_availqty, t2.sum_quantity sum_quantity
+ FROM Partsupp ps
+ JOIN q20_tmp1() t1
+ ON ps.ps_partkey = t1.p_partkey
+ JOIN q20_tmp2() t2
+ ON ps.ps_partkey = t2.l_partkey and ps.ps_suppkey = t2.l_suppkey
+)
+};
+
+declare function q20_tmp4() {
+(
+ SELECT ps_suppkey AS ps_suppkey
+ FROM q20_tmp3() t
+ WHERE t.ps_availqty > t.sum_quantity
+ GROUP BY t.ps_suppkey as ps_suppkey
+)
+};
+
+SELECT s.s_name AS s_name, s.s_address AS s_address
+FROM Supplier s
+JOIN Nation n
+ON s.s_nationkey = n.n_nationkey
+JOIN q20_tmp4() t4
+ON s.s_suppkey = t4.ps_suppkey
+ORDER BY s.s_name;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
new file mode 100644
index 0000000..907be7b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.1.ddl.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
new file mode 100644
index 0000000..97a0b31
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.2.update.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
new file mode 100644
index 0000000..c916481
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
@@ -0,0 +1,80 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+
+declare function tmp1() {
+(
+ SELECT l_orderkey AS l_orderkey,
+ count(l2.l_suppkey) AS count_suppkey,
+ max(l2.l_suppkey) AS max_suppkey
+ FROM (
+ SELECT l_orderkey1 AS l_orderkey, l_suppkey1 AS l_suppkey
+ from LineItem AS l
+ group by l.l_orderkey AS l_orderkey1,l.l_suppkey AS l_suppkey1
+ ) AS l2
+ GROUP BY l2.l_orderkey AS l_orderkey
+)
+};
+
+declare function tmp2() {
+(
+ SELECT l_orderkey AS l_orderkey,
+ count(l2.l_suppkey) AS count_suppkey,
+ max(l2.l_suppkey) AS max_suppkey
+ FROM (
+ SELECT l_orderkey1 AS l_orderkey, l_suppkey1 AS l_suppkey
+ FROM LineItem AS l
+ WHERE l.l_receiptdate > l.l_commitdate
+ GROUP BY l.l_orderkey AS l_orderkey1,l.l_suppkey AS l_suppkey1
+ ) AS l2
+ GROUP BY l2.l_orderkey as l_orderkey
+)
+};
+
+SELECT s_name AS s_name, count(t4) AS numwait
+FROM (
+ SELECT t3.s_name AS s_name,
+ t3.l_suppkey AS l_suppkey,
+ t2.l_orderkey AS l_orderkey,
+ t2.count_suppkey AS count_suppkey,
+ t2.max_suppkey AS max_suppkey
+ FROM (
+ SELECT ns.s_name AS s_name,
+ t1.l_orderkey AS l_orderkey,
+ l.l_suppkey AS l_suppkey
+ FROM LineItem AS l,
+ (
+ SELECT s.s_name AS s_name, s.s_suppkey AS s_suppkey
+ FROM Nation AS n,
+ Supplier AS s
+ WHERE s.s_nationkey = n.n_nationkey
+ ) AS ns,
+ Orders AS o,
+ tpch.tmp1() AS t1
+ WHERE ns.s_suppkey = l.l_suppkey AND l.l_receiptdate > l.l_commitdate
+ AND o.o_orderkey = l.l_orderkey AND l.l_orderkey = t1.l_orderkey
+ ) AS t3,
+ tmp2() AS t2
+ WHERE t2.count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
+) AS t4
+GROUP BY t4.s_name AS s_name
+ORDER BY numwait DESC,s_name
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
new file mode 100644
index 0000000..176cdc2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.1.ddl.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
new file mode 100644
index 0000000..97a0b31
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.2.update.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
new file mode 100644
index 0000000..3630541
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.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 q22_customer_tmp() {
+(
+ SELECT c.c_acctbal AS c_acctbal, c.c_custkey AS c_custkey, substring(c.c_phone,1,2) AS cntrycode
+ FROM Customer AS c
+)
+};
+
+WITH avg AS avg((
+ SELECT ELEMENT c.c_acctbal
+ FROM Customer AS c
+ WHERE c.c_acctbal > 0.0
+ ))
+SELECT cntrycode AS cntrycode, count(ct) AS numcust, tpch.sum(ct.c_acctbal) AS totacctbal
+FROM q22_customer_tmp() AS ct
+WHERE ct.c_acctbal > avg
+GROUP BY ct.cntrycode AS cntrycode
+ORDER BY cntrycode
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.1.ddl.sqlpp
new file mode 100644
index 0000000..9447c79
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.1.ddl.sqlpp
@@ -0,0 +1,140 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date : 15th Jan. 2015
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : int32,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create type tpch.OrderType as
+ closed {
+ o_orderkey : int32,
+ o_custkey : int32,
+ o_orderstatus : string,
+ o_totalprice : double,
+ o_orderdate : string,
+ o_orderpriority : string,
+ o_clerk : string,
+ o_shippriority : int32,
+ o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+ c_custkey : int32,
+ c_name : string,
+ c_address : string,
+ c_nationkey : int32,
+ c_phone : string,
+ c_acctbal : double,
+ c_mktsegment : string,
+ c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+ s_suppkey : int32,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int32,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+ n_nationkey : int32,
+ n_name : string,
+ n_regionkey : int32,
+ n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+ r_regionkey : int32,
+ r_name : string,
+ r_comment : string
+}
+
+create type tpch.PartType as
+ closed {
+ p_partkey : int32,
+ p_name : string,
+ p_mfgr : string,
+ p_brand : string,
+ p_type : string,
+ p_size : int32,
+ p_container : string,
+ p_retailprice : double,
+ p_comment : string
+}
+
+create type tpch.PartSuppType as
+ closed {
+ ps_partkey : int32,
+ ps_suppkey : int32,
+ ps_availqty : int32,
+ ps_supplycost : double,
+ ps_comment : string
+}
+
+create table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
+create table Orders(OrderType) primary key o_orderkey;
+
+create table Supplier(SupplierType) primary key s_suppkey;
+
+create table Region(RegionType) primary key r_regionkey;
+
+create table Nation(NationType) primary key n_nationkey;
+
+create table Part(PartType) primary key p_partkey;
+
+create table Partsupp(PartSuppType) primary key ps_partkey,ps_suppkey;
+
+create table Customer(CustomerType) primary key c_custkey;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.2.update.sqlpp
new file mode 100644
index 0000000..e3a990e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.2.update.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date : 15th Jan. 2015
+ */
+
+use tpch;
+
+
+load table LineItem using localfs (("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Part using localfs (("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Partsupp using localfs (("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
new file mode 100644
index 0000000..753b5d3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date : 15th Jan. 2015
+ */
+
+USE tpch;
+
+
+declare function q22_customer_tmp() {
+(
+ SELECT c.c_acctbal AS c_acctbal, c.c_custkey AS c_custkey, phone_substr AS cntrycode
+ FROM Customer AS c
+ WITH phone_substr AS substring(c.c_phone,1,2)
+ WHERE phone_substr = '13' OR phone_substr = '31' OR phone_substr = '23' OR phone_substr = '29'
+ OR phone_substr = '30' OR phone_substr = '18' OR phone_substr = '17'
+)
+};
+
+SELECT cntrycode AS cntrycode, count(ct) AS numcust, sum(ct.c_acctbal) AS totacctbal
+FROM q22_customer_tmp() as ct
+WHERE count((
+ SELECT ELEMENT o
+ FROM Orders AS o
+ WHERE ct.c_custkey = o.o_custkey
+ )) = 0
+GROUP BY ct.cntrycode AS cntrycode
+ORDER BY cntrycode
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.1.ddl.sqlpp
new file mode 100644
index 0000000..8a4e320
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue601
+ * https://code.google.com/p/asterixdb/issues/detail?id=601
+ * Expected Res : SUCCESS
+ * Date : 10th Oct 2014
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+ l_orderkey : int64,
+ l_partkey : int64,
+ l_suppkey : int64,
+ l_linenumber : int64,
+ l_quantity : double,
+ l_extendedprice : double,
+ l_discount : double,
+ l_tax : double,
+ l_returnflag : string,
+ l_linestatus : string,
+ l_shipdate : string,
+ l_commitdate : string,
+ l_receiptdate : string,
+ l_shipinstruct : string,
+ l_shipmode : string,
+ l_comment : string
+}
+
+create table LineItem(LineItemType) primary key l_orderkey,l_linenumber;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.2.update.sqlpp
new file mode 100644
index 0000000..8f6ad53
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue601
+ * https://code.google.com/p/asterixdb/issues/detail?id=601
+ * Expected Res : SUCCESS
+ * Date : 10th Oct 2014
+ */
+
+use tpch;
+
+
+load table LineItem using localfs (("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/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.3.query.sqlpp
new file mode 100644
index 0000000..bd88d13
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue601/query-issue601.3.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue601
+ * https://code.google.com/p/asterixdb/issues/detail?id=601
+ * Expected Res : SUCCESS
+ * Date : 10th Oct 2014
+ */
+
+USE tpch;
+
+
+SELECT l_linenumber AS l_linenumber, count(l) AS count_order
+FROM LineItem AS l
+GROUP BY l.l_linenumber AS l_linenumber
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.1.ddl.sqlpp
new file mode 100644
index 0000000..07b9107
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.1.ddl.sqlpp
@@ -0,0 +1,140 @@
+/*
+ * 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 issue638
+ * https://code.google.com/p/asterixdb/issues/detail?id=638
+ * Expected Res : SUCCESS
+ * Date : 24th Oct. 2014
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.LineItemType as
+ closed {
+ l_orderkey : int32,
+ l_partkey : int32,
+ l_suppkey : int32,
+ l_linenumber : int32,
+ l_quantity : 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 external table LineItem(LineItemType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Orders(OrderType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Supplier(SupplierType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Region(RegionType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Nation(NationType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Part(PartType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Partsupp(PartSuppType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+create external table Customer(CustomerType) using "localfs"(("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.2.update.sqlpp
new file mode 100644
index 0000000..86538e8
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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 issue638
+ * https://code.google.com/p/asterixdb/issues/detail?id=638
+ * Expected Res : SUCCESS
+ * Date : 24th Oct. 2014
+ */
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.3.query.sqlpp
new file mode 100644
index 0000000..45310aa
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue638/query-issue638.3.query.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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 issue638
+ * https://code.google.com/p/asterixdb/issues/detail?id=638
+ * Expected Res : SUCCESS
+ * Date : 24th Oct. 2014
+ */
+
+USE tpch;
+
+
+SELECT nation AS nation, o_year AS o_year, sum(profit.amount) AS sum_profit
+FROM
+ (
+ SELECT l3.n_name AS nation,
+ "get-year"(o.o_orderdate) AS o_year,
+ l3.l_extendedprice * (1 - l3.l_discount) - l3.ps_supplycost * l3.l_quantity AS amount
+ FROM
+ Orders o JOIN
+ (
+ SELECT l2.l_extendedprice AS l_extendedprice,
+ l2.l_discount AS l_discount,
+ l2.l_quantity AS l_quantity,
+ l2.l_orderkey AS l_orderkey,
+ l2.n_name AS n_name,
+ l2.ps_supplycost AS ps_supplycost
+ FROM Part p JOIN
+ (SELECT l1.l_extendedprice AS l_extendedprice,
+ l1.l_discount AS l_discount,
+ l1.l_quantity AS l_quantity,
+ l1.l_partkey AS l_partkey,
+ l1.l_orderkey AS l_orderkey,
+ l1.n_name AS n_name,
+ ps.ps_supplycost AS ps_supplycost
+ FROM Partsupp ps join
+ (SELECT l.l_suppkey AS l_suppkey,
+ l.l_extendedprice AS l_extendedprice,
+ l.l_discount AS l_discount,
+ l.l_quantity AS l_quantity,
+ l.l_partkey AS l_partkey,
+ l.l_orderkey AS l_orderkey,
+ s1.n_name AS n_name
+ FROM
+ (SELECT s.s_suppkey AS s_suppkey, n.n_name AS n_name
+ FROM Nation n join Supplier s on n.n_nationkey = s.s_nationkey
+ ) s1 JOIN LineItem l ON s1.s_suppkey = l.l_suppkey
+ ) l1 ON ((ps.ps_suppkey = l1.l_suppkey) and (ps.ps_partkey = l1.l_partkey))
+ ) l2 ON contains(p.p_name,'green') AND p.p_partkey = l2.l_partkey
+ ) l3 ON o.o_orderkey = l3.l_orderkey
+ ) profit
+GROUP BY profit.nation AS nation, profit.o_year AS o_year
+ORDER BY nation, o_year desc;
+
+
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.1.ddl.sqlpp
new file mode 100644
index 0000000..18e3fa1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.1.ddl.sqlpp
@@ -0,0 +1,94 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+ o_orderkey : int64,
+ o_custkey : int64,
+ o_orderstatus : string,
+ o_totalprice : double,
+ o_orderdate : string,
+ o_orderpriority : string,
+ o_clerk : string,
+ o_shippriority : int64,
+ o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+ c_custkey : int64,
+ c_name : string,
+ c_address : string,
+ c_nationkey : int64,
+ c_phone : string,
+ c_acctbal : double,
+ c_mktsegment : string,
+ c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+ s_suppkey : int64,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int64,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+ n_nationkey : int64,
+ n_name : string,
+ n_regionkey : int64,
+ n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+ r_regionkey : int64,
+ r_name : string,
+ r_comment : string
+}
+
+create table Orders(OrderType) primary key o_orderkey;
+
+create table Supplier(SupplierType) primary key s_suppkey;
+
+create table Region(RegionType) primary key r_regionkey;
+
+create table Nation(NationType) primary key n_nationkey;
+
+create table Customer(CustomerType) primary key c_custkey;
+
+create table SelectedNation(NationType) primary key n_nationkey;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.2.update.sqlpp
new file mode 100644
index 0000000..bc3766e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table SelectedNation using localfs (("path"="asterix_nc1://data/tpch0.001/selectednation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp
new file mode 100644
index 0000000..8ac3963
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+USE tpch;
+
+
+WITH t AS (
+ SELECT nation.n_nationkey AS n_nationkey, nation.n_name AS n_name
+ FROM Nation AS nation,
+ SelectedNation AS sn
+ WHERE nation.n_nationkey = sn.n_nationkey
+),
+X as (
+ SELECT nation_key AS nation_key, orderdate AS order_date, sum(orders.o_totalprice) AS sum_price
+ FROM t AS n,
+ Customer AS customer,
+ Orders AS orders
+ WHERE orders.o_custkey = customer.c_custkey and customer.c_nationkey = n.n_nationkey
+ GROUP BY orders.o_orderdate AS orderdate, n.n_nationkey AS nation_key
+)
+
+SELECT nation_key As nation_key,
+ (
+ SELECT y.order_date AS orderdate, y.sum_price As sum_price
+ FROM x AS y
+ ORDER BY y.sum_price desc
+ LIMIT 3
+ ) AS sum_price
+FROM X AS x
+GROUP BY x.nation_key AS nation_key
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.1.ddl.sqlpp
new file mode 100644
index 0000000..18e3fa1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.1.ddl.sqlpp
@@ -0,0 +1,94 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+drop database tpch if exists;
+create database tpch;
+
+use tpch;
+
+
+create type tpch.OrderType as
+ closed {
+ o_orderkey : int64,
+ o_custkey : int64,
+ o_orderstatus : string,
+ o_totalprice : double,
+ o_orderdate : string,
+ o_orderpriority : string,
+ o_clerk : string,
+ o_shippriority : int64,
+ o_comment : string
+}
+
+create type tpch.CustomerType as
+ closed {
+ c_custkey : int64,
+ c_name : string,
+ c_address : string,
+ c_nationkey : int64,
+ c_phone : string,
+ c_acctbal : double,
+ c_mktsegment : string,
+ c_comment : string
+}
+
+create type tpch.SupplierType as
+ closed {
+ s_suppkey : int64,
+ s_name : string,
+ s_address : string,
+ s_nationkey : int64,
+ s_phone : string,
+ s_acctbal : double,
+ s_comment : string
+}
+
+create type tpch.NationType as
+ closed {
+ n_nationkey : int64,
+ n_name : string,
+ n_regionkey : int64,
+ n_comment : string
+}
+
+create type tpch.RegionType as
+ closed {
+ r_regionkey : int64,
+ r_name : string,
+ r_comment : string
+}
+
+create table Orders(OrderType) primary key o_orderkey;
+
+create table Supplier(SupplierType) primary key s_suppkey;
+
+create table Region(RegionType) primary key r_regionkey;
+
+create table Nation(NationType) primary key n_nationkey;
+
+create table Customer(CustomerType) primary key c_custkey;
+
+create table SelectedNation(NationType) primary key n_nationkey;
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.2.update.sqlpp
new file mode 100644
index 0000000..bc3766e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+load table Orders using localfs (("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Supplier using localfs (("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Region using localfs (("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Nation using localfs (("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table Customer using localfs (("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load table SelectedNation using localfs (("path"="asterix_nc1://data/tpch0.001/selectednation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/acc12a9b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp
new file mode 100644
index 0000000..a142f2a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+USE tpch;
+
+
+SELECT nation_key AS nation_key,
+ (
+ SELECT od AS orderdate, sum_price AS sum_price
+ FROM x as i
+ GROUP BY i.order_date AS od
+ WITH sum_price AS sum(i.sum_price)
+ ORDER BY sum_price desc
+ LIMIT 3
+ ) AS sum_price
+FROM (
+ SELECT nation_key AS nation_key, orderdate AS order_date, sum(orders.o_totalprice) AS sum_price
+ FROM Nation AS n,
+ Customer AS customer,
+ Orders AS orders
+ WHERE orders.o_custkey = customer.c_custkey AND customer.c_nationkey = n.n_nationkey
+ GROUP BY orders.o_orderdate as orderdate,n.n_nationkey as nation_key
+) AS x
+GROUP BY x.nation_key AS nation_key
+;