You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by im...@apache.org on 2016/04/07 17:00:01 UTC
[26/50] [abbrv] incubator-asterixdb git commit: Merge branch 'master'
into hyracks-merge2
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index a8f29d3,0000000..a376e45
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@@ -1,35 -1,0 +1,35 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
- select element (100.0 * tpch.sum((
++select element (100.0 * tpch.coll_sum((
+ select element tpch."switch-case"(tpch.like(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
+ from lp as i
- )) / tpch.sum((
++ )) / tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from lp as i
+ )))
+from LineItem as l,
+ Part as p
+let lp = {'p_type': p.p_type, 'l_extendedprice': l.l_extendedprice, 'l_discount': l.l_discount}
+where ((l.l_partkey = p.p_partkey) and (l.l_shipdate >= '1995-09-01') and (l.l_shipdate < '1995-10-01'))
+group by 1 as t
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp
index e21104b,0000000..fdc4fcc
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q15_top_supplier/q15_top_supplier.3.query.sqlpp
@@@ -1,42 -1,0 +1,42 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function revenue() {
+(
- select element {'supplier_no':l_suppkey,'total_revenue':tpch.sum((
++ select element {'supplier_no':l_suppkey,'total_revenue':tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ ))}
+ from LineItem as l
+ where ((l.l_shipdate >= '1996-01-01') and (l.l_shipdate < '1996-04-01'))
+ group by l.l_suppkey as l_suppkey
+)
+};
- with m as tpch.max((
++with m as tpch.coll_max((
+ select element r2.total_revenue
+ from tpch.revenue() as r2
+ ))
+select element {'s_suppkey':s.s_suppkey,'s_name':s.s_name,'s_address':s.s_address,'s_phone':s.s_phone,'total_revenue':r.total_revenue}
+from Supplier as s,
+ tpch.revenue() as r
+where ((s.s_suppkey = r.supplier_no) and (r.total_revenue < (m + 0.000000001)) and (r.total_revenue > (m - 0.000000001)))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index cc4d01a,0000000..acf3281
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@@ -1,49 -1,0 +1,49 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp() {
+(
+ select element {'p_brand':psp.p_brand,'p_type':psp.p_type,'p_size':psp.p_size,'ps_suppkey':psp.ps_suppkey}
+ from (
+ select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
+ from Partsupp as ps,
+ Part as p
+ where ((p.p_partkey = ps.ps_partkey) and (p.p_brand != 'Brand#45') and tpch.not(tpch.like(p.p_type,'MEDIUM POLISHED%')))
+ ) as psp,
+ Supplier as s
+ where ((psp.ps_suppkey = s.s_suppkey) and tpch.not(tpch.like(s.s_comment,'%Customer%Complaints%')))
+)
+};
+select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
+from (
+ select element {'p_brand':p_brand1,'p_type':p_type1,'p_size':p_size1,'ps_suppkey':ps_suppkey1}
+ from tpch.tmp() as t
+ where ((t.p_size = 49) or (t.p_size = 14) or (t.p_size = 23) or (t.p_size = 45) or (t.p_size = 19) or (t.p_size = 3) or (t.p_size = 36) or (t.p_size = 9))
+ group by t.p_brand as p_brand1,t.p_type as p_type1,t.p_size as p_size1,t.ps_suppkey as ps_suppkey1
+) as t2
+group by t2.p_brand as p_brand,t2.p_type as p_type,t2.p_size as p_size
- with supplier_cnt as tpch.count((
++with supplier_cnt as coll_count((
+ select element i.ps_suppkey
+ from t2 as i
+ ))
+order by supplier_cnt desc,p_brand,p_type,p_size
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
index ada4f75,0000000..2e057d7
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
@@@ -1,57 -1,0 +1,57 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
- select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.avg((
++select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.coll_avg((
+ select element i.l_quantity
+ from l as i
- ))),'t_max_suppkey':tpch.max((
++ ))),'t_max_suppkey':tpch.coll_max((
+ select element i.l_suppkey
+ from l as i
- )),'t_max_linenumber':tpch.max((
++ )),'t_max_linenumber':tpch.coll_max((
+ select element i.l_linenumber
+ from l as i
- )),'t_avg_extendedprice':tpch.avg((
++ )),'t_avg_extendedprice':tpch.coll_avg((
+ select element i.l_extendedprice
+ from l as i
- )),'t_avg_discount':tpch.avg((
++ )),'t_avg_discount':tpch.coll_avg((
+ select element i.l_discount
+ from l as i
- )),'t_avg_tax':tpch.avg((
++ )),'t_avg_tax':tpch.coll_avg((
+ select element i.l_tax
+ from l as i
- )),'t_max_shipdate':tpch.max((
++ )),'t_max_shipdate':tpch.coll_max((
+ select element i.l_shipdate
+ from l as i
- )),'t_min_commitdate':tpch.min((
++ )),'t_min_commitdate':tpch.coll_min((
+ select element i.l_commitdate
+ from l as i
- )),'t_min_receiptdate':tpch.min((
++ )),'t_min_receiptdate':tpch.coll_min((
+ select element i.l_receiptdate
+ from l as i
- )),'t_max_comment':tpch.max((
++ )),'t_max_comment':tpch.coll_max((
+ select element i.l_comment
+ from l as i
+ ))}
+from LineItem as l
+group by l.l_partkey as l_partkey
+order by l_partkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
index 5aa971e,0000000..2f48cf3
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
@@@ -1,40 -1,0 +1,40 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp() {
+(
- select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.avg((
++ select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * COLL_AVG((
+ select element i.l_quantity
+ from l as i
+ )))}
+ from LineItem as l
+ group by l.l_partkey as l_partkey
+)
+};
+
- select element (tpch.sum((
++select element (COLL_SUM((
+ select element l.l_extendedprice
+ from LineItem as l,
+ Part as p,
+ tpch.tmp() as t
+ where (((p.p_partkey = l.l_partkey) and (p.p_container = 'MED BOX')) and ((l.l_partkey = t.t_partkey) and (l.l_quantity < t.t_avg_quantity)))
+ )) / 7.0);
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
index 15081ff,0000000..5215013
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
@@@ -1,42 -1,0 +1,42 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
- select element {'c_name':c_name,'c_custkey':c_custkey,'o_orderkey':o_orderkey,'o_orderdate':o_orderdate,'o_totalprice':o_totalprice,'sum_quantity':tpch.sum((
++select element {'c_name':c_name,'c_custkey':c_custkey,'o_orderkey':o_orderkey,'o_orderdate':o_orderdate,'o_totalprice':o_totalprice,'sum_quantity':COLL_SUM((
+ select element j.l_quantity
+ from l as j
+ ))}
+from Customer as c,
+ Orders as o,
+ (
- select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.sum((
++ select element {'l_orderkey':l_orderkey,'t_sum_quantity':COLL_SUM((
+ select element i.l_quantity
+ from l as i
+ ))}
+ from LineItem as l
+ group by l.l_orderkey as l_orderkey
+) as t,
+ LineItem as l
+where ((c.c_custkey = o.o_custkey) and ((o.o_orderkey = t.l_orderkey) and (t.t_sum_quantity > 30)) and (l.l_orderkey = o.o_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/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
index 7065f87,0000000..f245189
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
@@@ -1,30 -1,0 +1,30 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+set "import-private-functions" "true";
+
- select element tpch.sum((
++select element tpch.coll_sum((
+ select element (l.l_extendedprice * (1 - l.l_discount))
+ from LineItem as l,
+ Part as p
+ where ((p.p_partkey = l.l_partkey) and (((p.p_brand = 'Brand#12') and tpch."reg-exp"(p.p_container,'SM CASE||SM BOX||SM PACK||SM PKG') and (l.l_quantity >= 1) and (l.l_quantity <= 11) and (p.p_size >= 1) and (p.p_size <= 5) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#23') and tpch."reg-exp"(p.p_container,'MED BAG||MED BOX||MED PKG||MED PACK') and (l.l_quantity >= 10) and (l.l_quantity <= 20) and (p.p_size >= 1) and (p.p_size <= 10) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#34') and tpch."reg-exp"(p.p_container,'LG CASE||LG BOX||LG PACK||LG PKG') and (l.l_quantity >= 20) and (l.l_quantity <= 30) and (p.p_size >= 1) and (p.p_size <= 15) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON'))))
+));
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
index 0657ad3,0000000..82e38bf
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
@@@ -1,53 -1,0 +1,53 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+select element {'s_name':t4.s_name,'s_address':t4.s_address}
+from (
+ select distinct element {'ps_suppkey':pst1.ps_suppkey}
+ from (
- select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * tpch.sum((
++ select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * tpch.coll_sum((
+ select element i.l_quantity
+ from l as i
+ )))}
+ from LineItem as l
+ group by l.l_partkey as l_partkey,l.l_suppkey as l_suppkey
+ ) as t2,
+ (
+ select element {'ps_suppkey':ps.ps_suppkey,'ps_partkey':ps.ps_partkey,'ps_availqty':ps.ps_availqty}
+ from Partsupp as ps,
+ (
+ select distinct element {'p_partkey':p.p_partkey}
+ from Part as p
+ ) as t1
+ where (ps.ps_partkey = t1.p_partkey)
+ ) as pst1
+ where ((pst1.ps_partkey = t2.l_partkey) and (pst1.ps_suppkey = t2.l_suppkey) and (pst1.ps_availqty > t2.sum_quantity))
+) as t3,
+ (
+ select element {'s_name':s.s_name,'s_address':s.s_address,'s_suppkey':s.s_suppkey}
+ from Nation as n,
+ Supplier as s
+ where (s.s_nationkey = n.n_nationkey)
+) as t4
+where (t3.ps_suppkey = t4.s_suppkey)
+order by t4.s_name
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
index 5382019,0000000..578d72e
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
@@@ -1,80 -1,0 +1,80 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp1() {
+(
- select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
++ select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT((
+ select element i.l_suppkey
+ from l2 as i
- )),'max_suppkey':tpch.max((
++ )),'max_suppkey':tpch.coll_max((
+ select element i.l_suppkey
+ from l2 as i
+ ))}
+ from (
+ select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
+ from LineItem as l
+ group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
+ ) as l2
+ group by l2.l_orderkey as l_orderkey
+)
+};
+declare function tmp2() {
+(
- select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
++ select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT((
+ select element i.l_suppkey
+ from l2 as i
- )),'max_suppkey':tpch.max((
++ )),'max_suppkey':COLL_MAX((
+ select element i.l_suppkey
+ from l2 as i
+ ))}
+ from (
+ select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
+ from LineItem as l
+ where (l.l_receiptdate > l.l_commitdate)
+ group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
+ ) as l2
+ group by l2.l_orderkey as l_orderkey
+)
+};
+select element {'s_name':s_name,'numwait':numwait}
+from (
+ select element {'s_name':t3.s_name,'l_suppkey':t3.l_suppkey,'l_orderkey':t2.l_orderkey,'count_suppkey':t2.count_suppkey,'max_suppkey':t2.max_suppkey}
+ from (
+ select element {'s_name':ns.s_name,'l_orderkey':t1.l_orderkey,'l_suppkey':l.l_suppkey}
+ from LineItem as l,
+ (
+ select element {'s_name':s.s_name,'s_suppkey':s.s_suppkey}
+ from Nation as n,
+ Supplier as s
+ where (s.s_nationkey = n.n_nationkey)
+ ) as ns,
+ Orders as o,
+ tpch.tmp1() as t1
+ where (((ns.s_suppkey = l.l_suppkey) and (l.l_receiptdate > l.l_commitdate)) and (o.o_orderkey = l.l_orderkey) and (l.l_orderkey = t1.l_orderkey))
+ ) as t3,
+ tpch.tmp2() as t2
+ where ((t2.count_suppkey >= 0) and (t3.l_orderkey = t2.l_orderkey))
+) as t4
+group by t4.s_name as s_name
- with numwait as tpch.count(t4)
++with numwait as COLL_COUNT(t4)
+order by numwait desc,s_name
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
index 6136008,0000000..328c753
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
@@@ -1,42 -1,0 +1,42 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function q22_customer_tmp() {
+(
+ select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':tpch.substring(c.c_phone,1,2)}
+ from Customer as c
+)
+};
- with avg as tpch.avg((
++with avg as tpch.coll_avg((
+ select element c.c_acctbal
+ from Customer as c
+ where (c.c_acctbal > 0.0)
+ ))
- select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.sum((
++select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum((
+ select element i.c_acctbal
+ from ct as i
+ ))}
+from tpch.q22_customer_tmp() as ct
+where (ct.c_acctbal > avg)
+group by ct.cntrycode as cntrycode
+order by cntrycode
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue601/query-issue601.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue601/query-issue601.3.query.sqlpp
index 44852ec,0000000..733924e
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue601/query-issue601.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue601/query-issue601.3.query.sqlpp
@@@ -1,32 -1,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 from issue601
+ * https://code.google.com/p/asterixdb/issues/detail?id=601
+ * Expected Res : SUCCESS
+ * Date : 10th Oct 2014
+ */
+
+use tpch;
+
+
- select element {'l_linenumber':l_linenumber,'count_order':tpch.count(l)}
++select element {'l_linenumber':l_linenumber,'count_order':COLL_COUNT(l)}
+from LineItem as l
+group by l.l_linenumber as l_linenumber
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue638/query-issue638.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue638/query-issue638.3.query.sqlpp
index d2e8a7e,0000000..78bc3cd
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue638/query-issue638.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue638/query-issue638.3.query.sqlpp
@@@ -1,63 -1,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.
+ */
+/*
+ * Description : This test case is to verify the fix from issue638
+ * https://code.google.com/p/asterixdb/issues/detail?id=638
+ * Expected Res : SUCCESS
+ * Date : 24th Oct. 2014
+ */
+
+use tpch;
+
+
- select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.sum((
++select element {'nation':nation,'o_year':o_year,'sum_profit':COLL_SUM((
+ select element pr.amount
+ from profit as pr
+ ))}
+from (
+ select element {'nation':l3.n_name,'o_year':o_year,'amount':amount}
+ from Orders as o,
+ (
+ select element {'l_extendedprice':l2.l_extendedprice,'l_discount':l2.l_discount,'l_quantity':l2.l_quantity,'l_orderkey':l2.l_orderkey,'n_name':l2.n_name,'ps_supplycost':l2.ps_supplycost}
+ from Part as p,
+ (
+ select element {'l_extendedprice':l1.l_extendedprice,'l_discount':l1.l_discount,'l_quantity':l1.l_quantity,'l_partkey':l1.l_partkey,'l_orderkey':l1.l_orderkey,'n_name':l1.n_name,'ps_supplycost':ps.ps_supplycost}
+ from Partsupp as ps,
+ (
+ select element {'l_suppkey':l.l_suppkey,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_quantity':l.l_quantity,'l_partkey':l.l_partkey,'l_orderkey':l.l_orderkey,'n_name':s1.n_name}
+ from (
+ select element {'s_suppkey':s.s_suppkey,'n_name':n.n_name}
+ from Supplier as s,
+ Nation as n
+ where (n.n_nationkey = s.s_nationkey)
+ ) as s1,
+ LineItem as l
+ where (s1.s_suppkey = l.l_suppkey)
+ ) as l1
+ where ((ps.ps_suppkey = l1.l_suppkey) and (ps.ps_partkey = l1.l_partkey))
+ ) as l2
- where (tpch.contains(p.p_name,'green') and (p.p_partkey = l2.l_partkey))
++ where (CONTAINS(p.p_name,'green') and (p.p_partkey = l2.l_partkey))
+ ) as l3
+ with amount as ((l3.l_extendedprice * (1 - l3.l_discount)) - (l3.ps_supplycost * l3.l_quantity)),
+ o_year as tpch."get-year"(o.o_orderdate)
+ where (o.o_orderkey = l3.l_orderkey)
+) as 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/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785-2/query-issue785-2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785-2/query-issue785-2.3.query.sqlpp
index 341d824,0000000..0127261
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785-2/query-issue785-2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785-2/query-issue785-2.3.query.sqlpp
@@@ -1,55 -1,0 +1,55 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix from issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+with t as (
+ select element {'n_nationkey':nation.n_nationkey,'n_name':nation.n_name}
+ from Nation as nation,
+ SelectedNation as sn
+ where (nation.n_nationkey = sn.n_nationkey)
+ ),
+ X as (
+ select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':sum}
+ from t as n,
+ Customer as customer,
+ Orders as orders
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
+ group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
- with sum as tpch.sum((
++ with sum as COLL_SUM((
+ select element o.o_totalprice
+ from orders as o
+ ))
+ )
+select element {'nation_key':nation_key,'sum_price':(
+ select element {'orderdate':y.order_date,'sum_price':y.sum_price}
+ from x as y
+ order by y.sum_price desc
+ limit 3
+ )}
+from X as x
+group by x.nation_key as nation_key
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785/query-issue785.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785/query-issue785.3.query.sqlpp
index 0f31629,0000000..db98f32
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785/query-issue785.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785/query-issue785.3.query.sqlpp
@@@ -1,52 -1,0 +1,52 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix from issue785
+ * https://code.google.com/p/asterixdb/issues/detail?id=785
+ * Expected Res : SUCCESS
+ * Date : 2nd Oct. 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation_key,'sum_price':(
+ select element {'orderdate':od,'sum_price':sum}
+ from x as i
+ group by i.order_date as od
- with sum as tpch.sum((
++ with sum as COLL_SUM((
+ select element s.sum_price
+ from i as s
+ ))
+ order by sum desc
+ limit 3
+ )}
+from (
- select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':tpch.sum((
++ select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':COLL_SUM((
+ select element o.o_totalprice
+ from orders as o
+ ))}
+ from Nation as n,
+ Customer as customer,
+ Orders as orders
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
+ group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
+) as x
+group by x.nation_key as nation_key
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue786/query-issue786.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue786/query-issue786.3.query.sqlpp
index c35853c,0000000..7098a55
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue786/query-issue786.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue786/query-issue786.3.query.sqlpp
@@@ -1,45 -1,0 +1,45 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix from issue786
+ * https://code.google.com/p/asterixdb/issues/detail?id=786
+ * Expected Res : SUCCESS
+ * Date : 10th Oct. 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+ select element {'order_date':orderdate,'sum_price':sum}
+ from Orders as orders,
+ Customer as customer
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
+ group by orders.o_orderdate as orderdate
- with sum as tpch.sum((
++ with sum as COLL_SUM((
+ select element o.o_totalprice
+ from orders as o
+ ))
+ order by sum desc
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey = sn.sn_nationkey)
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
index ba4f3e8,0000000..77ad1b6
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
@@@ -1,34 -1,0 +1,33 @@@
+/*
+ * 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;
+
+
- {'revenue':sum(
++{'revenue':
+ (
- SELECT ELEMENT l.l_extendedprice * l.l_discount
++ SELECT ELEMENT SUM(l.l_extendedprice * l.l_discount)
+ FROM LineItem AS l
+ WHERE l.l_shipdate >= '1994-01-01'
+ and l.l_shipdate < '1995-01-01'
+ and l.l_discount >= 0.05
+ and l.l_discount <= 0.07
+ and l.l_quantity < 24
- )
- )
++ )[0]
+};
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp
index 0e84e68,0000000..6faf579
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q11_important_stock/q11_important_stock.3.query.sqlpp
@@@ -1,54 -1,0 +1,52 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
- WITH sum as sum(
- (
- SELECT ELEMENT ps.ps_supplycost * ps.ps_availqty
++WITH sum as (
++ SELECT ELEMENT SUM(ps.ps_supplycost * ps.ps_availqty)
+ FROM Partsupp AS ps,
+ (
+ SELECT s.s_suppkey s_suppkey
+ FROM Supplier as s,
+ Nation as n
+ WHERE s.s_nationkey = n.n_nationkey
+ ) AS sn
+ WHERE ps.ps_suppkey = sn.s_suppkey
- )
- )
++)[0]
+
+SELECT t1.ps_partkey AS partkey,
+ t1.part_value AS part_value
+FROM (
+ SELECT ps_partkey AS ps_partkey,
+ tpch.sum(ps.ps_supplycost * ps.ps_availqty) AS part_value
+ FROM Partsupp ps,
+ (
+ SELECT s.s_suppkey s_suppkey
+ FROM Supplier AS s,
+ Nation as n
+ WHERE s.s_nationkey = n.n_nationkey
+ ) sn
+ WHERE ps.ps_suppkey = sn.s_suppkey
+ GROUP BY ps.ps_partkey AS ps_partkey
+) t1
+WHERE t1.part_value > sum * 0.00001
+ORDER BY t1.part_value DESC
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 24794de,0000000..e069759
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@@ -1,40 -1,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;
+
+SELECT c_count AS c_count, count(gco) AS custdist
+FROM (
+ SELECT c_custkey AS c_custkey, sum(co.o_orderkey_count) AS c_count
+ FROM (
+ SELECT c.c_custkey AS c_custkey,
- count(
++ coll_count(
+ (
+ select element o.o_orderkey
+ from Orders as o
+ where c.c_custkey = o.o_custkey and not(like(o.o_comment,'%special%requests%'))
+ )
+ ) AS o_orderkey_count
+ from Customer c
+ ) co
+ GROUP BY co.c_custkey c_custkey
+) gco
+GROUP BY gco.c_count as c_count
+ORDER BY custdist desc,c_count DESC
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp
index 1a036cc,0000000..d75a39b
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q15_top_supplier/q15_top_supplier.3.query.sqlpp
@@@ -1,46 -1,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.
+ */
+
+USE tpch;
+
+
+declare function revenue() {
+(
+ SELECT l_suppkey AS supplier_no,
+ sum(l.l_extendedprice * (1 - l.l_discount)) AS total_revenue
+ FROM LineItem l
+ WHERE l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01'
+ GROUP BY l.l_suppkey l_suppkey
+)
+};
+
- WITH m AS max((
- SELECT ELEMENT r2.total_revenue
++WITH m AS (
++ SELECT ELEMENT max(r2.total_revenue)
+ FROM revenue() r2
- ))
++)[0]
++
+SELECT s.s_suppkey s_suppkey,
+ s.s_name s_name,
+ s.s_address s_address,
+ s.s_phone s_phone,
+ r.total_revenue total_revenue
+FROM Supplier s,
+ revenue() r
+WHERE s.s_suppkey = r.supplier_no AND r.total_revenue < m + 0.000000001
+ AND r.total_revenue > m - 0.000000001
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
index 4519fa6,0000000..63a33ce
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
@@@ -1,37 -1,0 +1,36 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+
+WITH tmp AS
+(
+ SELECT l_partkey t_partkey, 0.2 * avg(l.l_quantity) t_avg_quantity
+ FROM LineItem AS l
+ GROUP BY l.l_partkey AS l_partkey
+)
+
- SELECT ELEMENT sum((
- SELECT ELEMENT l.l_extendedprice
- FROM tmp t,
++SELECT ELEMENT SUM(l.l_extendedprice) / 7.0
++FROM tmp t,
+ LineItem l,
+ Part p
- WHERE p.p_partkey = l.l_partkey AND p.p_container = 'MED BOX'
++WHERE p.p_partkey = l.l_partkey AND p.p_container = 'MED BOX'
+ AND l.l_partkey = t.t_partkey AND l.l_quantity < t.t_avg_quantity
- )) / 7.0;
++;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
index 558786c,0000000..1003b48
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
@@@ -1,58 -1,0 +1,55 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+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
++SELECT ELEMENT SUM(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/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
index 3630541,0000000..109e5a9
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
@@@ -1,40 -1,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
++WITH avg AS (
++ SELECT ELEMENT AVG(c.c_acctbal)
+ FROM Customer AS c
+ WHERE c.c_acctbal > 0.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/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
index 753b5d3,0000000..3c99324
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue562/query-issue562.3.query.sqlpp
@@@ -1,48 -1,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((
++WHERE coll_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/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-2/query-issue810-2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-2/query-issue810-2.3.query.sqlpp
index 5fe9965,0000000..af3421f
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-2/query-issue810-2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-2/query-issue810-2.3.query.sqlpp
@@@ -1,44 -1,0 +1,44 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date : 16th Nov. 2014
+ */
+
+use tpch;
+
+
+SELECT l_returnflag AS l_returnflag,
+ l_linestatus AS l_linestatus,
- count(cheaps) AS count_cheaps,
++ coll_count(cheaps) AS count_cheaps,
+ total_charges AS total_charges
+FROM LineItem as l
+WHERE l.l_shipdate <= '1998-09-02'
+/* +hash */
+GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus
+WITH cheaps AS (
+ SELECT ELEMENT m
+ FROM l AS m
+ WHERE m.l_discount > 0.05
+ ),
+total_charges AS sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax))
+ORDER BY l_returnflag,l_linestatus
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-3/query-issue810-3.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-3/query-issue810-3.3.query.sqlpp
index db4a46c,0000000..dbb9b88
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-3/query-issue810-3.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-3/query-issue810-3.3.query.sqlpp
@@@ -1,52 -1,0 +1,52 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date : 16th Nov. 2014
+ */
+
+USE tpch;
+
+
+SELECT l_returnflag AS l_returnflag,
+ l_linestatus AS l_linestatus,
- count(cheaps) AS count_cheaps,
- avg(expensives) AS avg_expensive_discounts,
++ coll_count(cheaps) AS count_cheaps,
++ coll_avg(expensives) AS avg_expensive_discounts,
+ sum_disc_prices AS sum_disc_prices,
+ total_charges AS total_charges
+FROM LineItem AS l
+WHERE l.l_shipdate <= '1998-09-02'
+/* +hash */
+GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus
+WITH expensives AS (
+ SELECT ELEMENT i.l_discount
+ FROM l AS i
+ WHERE i.l_discount <= 0.05
+ ),
+cheaps as (
+ SELECT ELEMENT i
+ FROM l AS i
+ WHERE i.l_discount > 0.05
+ ),
+sum_disc_prices AS sum(l.l_extendedprice * (1 - l.l_discount)),
+total_charges AS sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax))
+ORDER BY l_returnflag,l_linestatus
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810/query-issue810.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810/query-issue810.3.query.sqlpp
index 7e5cb54,0000000..fc80184
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810/query-issue810.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810/query-issue810.3.query.sqlpp
@@@ -1,48 -1,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 issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date : 16th Nov. 2014
+ */
+
+USE tpch;
+
+
+SELECT l_returnflag AS l_returnflag,
+ l_linestatus AS l_linestatus,
- count(cheap) AS count_cheaps,
- count(expensive) AS count_expensives
++ coll_count(cheap) AS count_cheaps,
++ coll_count(expensive) AS count_expensives
+FROM LineItem AS l
+WHERE l.l_shipdate <= '1998-09-02'
+/* +hash */
+GROUP BY l.l_returnflag AS l_returnflag,l.l_linestatus AS l_linestatus
+with cheap as (
+ SELECT ELEMENT m
+ FROM l AS m
+ WHERE m.l_discount > 0.05
+),
+expensive AS (
+ SELECT ELEMENT a
+ FROM l AS a
+ WHERE a.l_discount <= 0.05
+)
+ORDER BY l_returnflag,l_linestatus
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
index 3a015ae,0000000..8efcb7f
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp
@@@ -1,46 -1,0 +1,46 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+ select element {'order_date':orderdate,'sum_price':sum}
+ from Orders as orders,
+ Customer as customer
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
+ group by orders.o_orderdate as orderdate
- with sum as tpch.sum((
++ with sum as tpch.coll_sum((
+ select element o.o_totalprice
+ from orders as o
+ ))
+ order by sum
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
+order by nation.n_nationkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
index 1aa687c,0000000..1b70f0e
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp
@@@ -1,46 -1,0 +1,46 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : This test case is to verify the fix for issue782
+ * https://code.google.com/p/asterixdb/issues/detail?id=782
+ * Expected Res : SUCCESS
+ * Date : 2nd Jun 2014
+ */
+
+use tpch;
+
+
+select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
+ select element orderdate
+ from Orders as orders,
+ Customer as customer
+ where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
+ group by orders.o_orderdate as orderdate
- with sum as tpch.sum((
++ with sum as tpch.coll_sum((
+ select element o.o_totalprice
+ from orders as o
+ ))
+ order by sum
+ limit 3
+ )}
+from Nation as nation,
+ SelectedNation as sn
+where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
+order by nation.n_nationkey
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
index 94b313f,0000000..a971652
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
@@@ -1,52 -1,0 +1,52 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+set "import-private-functions" "true";
+
- select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.sum((
++select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.coll_sum((
+ select element i.l_quantity
+ from l as i
- )),'sum_base_price':tpch.sum((
++ )),'sum_base_price':tpch.coll_sum((
+ select element i.l_extendedprice
+ from l as i
- )),'sum_disc_price':tpch.sum((
++ )),'sum_disc_price':tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
- )),'sum_charge':tpch.sum((
++ )),'sum_charge':tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
+ from l as i
- )),'ave_qty':tpch.avg((
++ )),'ave_qty':tpch.coll_avg((
+ select element i.l_quantity
+ from l as i
- )),'ave_price':tpch.avg((
++ )),'ave_price':tpch.coll_avg((
+ select element i.l_extendedprice
+ from l as i
- )),'ave_disc':tpch.avg((
++ )),'ave_disc':tpch.coll_avg((
+ select element i.l_discount
+ from l as i
+ )),'count_order':tpch.count(l)}
+from LineItem as l
+where (l.l_shipdate <= '1998-09-02')
+/* +hash */
+group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
+order by l_returnflag,l_linestatus
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index a55c74d,0000000..499899d
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@@ -1,80 -1,0 +1,80 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+declare function tmp1() {
+(
+ select element {'s_acctbal':pssrn.s_acctbal,'s_name':pssrn.s_name,'n_name':pssrn.n_name,'p_partkey':p.p_partkey,'ps_supplycost':pssrn.ps_supplycost,'p_mfgr':p.p_mfgr,'s_address':pssrn.s_address,'s_phone':pssrn.s_phone,'s_comment':pssrn.s_comment}
+ from Part as p,
+ (
+ select element {'n_name':srn.n_name,'p_partkey':ps.ps_partkey,'ps_supplycost':ps.ps_supplycost,'s_name':srn.s_name,'s_acctbal':srn.s_acctbal,'s_address':srn.s_address,'s_phone':srn.s_phone,'s_comment':srn.s_comment}
+ from Partsupp as ps,
+ (
+ select element {'s_suppkey':s.s_suppkey,'n_name':rn.n_name,'s_name':s.s_name,'s_acctbal':s.s_acctbal,'s_address':s.s_address,'s_phone':s.s_phone,'s_comment':s.s_comment}
+ from Supplier as s,
+ (
+ select element {'n_nationkey':n.n_nationkey,'n_name':n.n_name}
+ from Region as r,
+ Nation as n
+ where ((n.n_regionkey = r.r_regionkey) and (r.r_name = 'EUROPE'))
+ ) as rn
+ where (s.s_nationkey = rn.n_nationkey)
+ ) as srn
+ where (srn.s_suppkey = ps.ps_suppkey)
+ ) as pssrn
+ where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+)
+};
+declare function tmp2() {
+(
- select element {'p_partkey':p_partkey,'ps_min_supplycost':tpch.min((
++ select element {'p_partkey':p_partkey,'ps_min_supplycost':tpch.coll_min((
+ select element i.ps_supplycost
+ from pssrn as i
+ ))}
+ from Part as p,
+ (
+ select element {'n_name':srn.n_name,'p_partkey':ps.ps_partkey,'ps_supplycost':ps.ps_supplycost,'s_name':srn.s_name,'s_acctbal':srn.s_acctbal,'s_address':srn.s_address,'s_phone':srn.s_phone,'s_comment':srn.s_comment}
+ from Partsupp as ps,
+ (
+ select element {'s_suppkey':s.s_suppkey,'n_name':rn.n_name,'s_name':s.s_name,'s_acctbal':s.s_acctbal,'s_address':s.s_address,'s_phone':s.s_phone,'s_comment':s.s_comment}
+ from Supplier as s,
+ (
+ select element {'n_nationkey':n.n_nationkey,'n_name':n.n_name}
+ from Region as r,
+ Nation as n
+ where ((n.n_regionkey = r.r_regionkey) and (r.r_name = 'EUROPE'))
+ ) as rn
+ where (s.s_nationkey = rn.n_nationkey)
+ ) as srn
+ where (srn.s_suppkey = ps.ps_suppkey)
+ ) as pssrn
+ where ((p.p_partkey = pssrn.p_partkey) and tpch.like(p.p_type,'%BRASS'))
+ /* +hash */
+ group by pssrn.p_partkey as p_partkey
+)
+};
+select element {'s_acctbal':t1.s_acctbal,'s_name':t1.s_name,'n_name':t1.n_name,'p_partkey':t1.p_partkey,'p_mfgr':t1.p_mfgr,'s_address':t1.s_address,'s_phone':t1.s_phone,'s_comment':t1.s_comment}
+from tpch.tmp2() as t2,
+ tpch.tmp1() as t1
+where ((t1.p_partkey = t2.p_partkey) and (t1.ps_supplycost = t2.ps_min_supplycost))
+order by t1.s_acctbal desc,t1.n_name,t1.s_name,t1.p_partkey
+limit 100
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
index d30c6d3,0000000..bddbb53
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
@@@ -1,36 -1,0 +1,36 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+select element {'l_orderkey':l_orderkey,'revenue':revenue,'o_orderdate':o_orderdate,'o_shippriority':o_shippriority}
+from Customer as c,
+ Orders as o,
+ LineItem as l
+where (((c.c_mktsegment = 'BUILDING') and (c.c_custkey = o.o_custkey)) and ((l.l_orderkey = o.o_orderkey) and (o.o_orderdate < '1995-03-15') and (l.l_shipdate > '1995-03-15')))
+/* +hash */
+group by l.l_orderkey as l_orderkey,o.o_orderdate as o_orderdate,o.o_shippriority as o_shippriority
- with revenue as tpch.sum((
++with revenue as tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from l as i
+ ))
+order by revenue desc,o_orderdate
+limit 10
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
index 150e4b1,0000000..9050001
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
@@@ -1,54 -1,0 +1,54 @@@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpch;
+
+
+select element {'n_name':n_name,'revenue':revenue}
+from Customer as c,
+ (
+ select element {'n_name':l1.n_name,'l_extendedprice':l1.l_extendedprice,'l_discount':l1.l_discount,'s_nationkey':l1.s_nationkey,'o_custkey':o.o_custkey}
+ from Orders as o,
+ (
+ select element {'n_name':s1.n_name,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_orderkey':l.l_orderkey,'s_nationkey':s1.s_nationkey}
+ from LineItem as l,
+ (
+ select element {'n_name':n1.n_name,'s_suppkey':s.s_suppkey,'s_nationkey':s.s_nationkey}
+ from Supplier as s,
+ (
+ select element {'n_name':n.n_name,'n_nationkey':n.n_nationkey}
+ from Nation as n,
+ Region as r
+ where (n.n_regionkey = r.r_regionkey)
+ ) as n1
+ where (s.s_nationkey = n1.n_nationkey)
+ ) as s1
+ where (l.l_suppkey = s1.s_suppkey)
+ ) as l1
+ where ((l1.l_orderkey = o.o_orderkey) and (o.o_orderdate >= '1990-01-01') and (o.o_orderdate < '1995-01-01'))
+) as o1
+where ((c.c_nationkey = o1.s_nationkey) and (c.c_custkey = o1.o_custkey))
+/* +hash */
+group by o1.n_name as n_name
- with revenue as tpch.sum((
++with revenue as tpch.coll_sum((
+ select element (i.l_extendedprice * (1 - i.l_discount))
+ from o1 as i
+ ))
+order by revenue desc
+;