asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [29/51] [partial] incubator-asterixdb git commit: ASTERIXDB-1226: implement SQL++ core group-by semantics and syntatic sugars.
Date Mon, 28 Mar 2016 20:18:56 GMT
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
+;



Mime
View raw message