http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 2e057d7..f22d29e 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 @@ -20,38 +20,40 @@ use tpch; -select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.coll_avg(( +select element {'t_partkey':l_partkey,'t_count':COLL_COUNT((from g select value l)), + 't_avg_quantity':(0.2 * COLL_AVG(( select element i.l_quantity - from l as i - ))),'t_max_suppkey':tpch.coll_max(( + from (from g select value l) as i + ))),'t_max_suppkey':COLL_MAX(( select element i.l_suppkey - from l as i - )),'t_max_linenumber':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_linenumber':COLL_MAX(( select element i.l_linenumber - from l as i - )),'t_avg_extendedprice':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_extendedprice':COLL_AVG(( select element i.l_extendedprice - from l as i - )),'t_avg_discount':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_discount':COLL_AVG(( select element i.l_discount - from l as i - )),'t_avg_tax':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_tax':COLL_AVG(( select element i.l_tax - from l as i - )),'t_max_shipdate':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_shipdate':COLL_MAX(( select element i.l_shipdate - from l as i - )),'t_min_commitdate':tpch.coll_min(( + from (from g select value l) as i + )),'t_min_commitdate':COLL_MIN(( select element i.l_commitdate - from l as i - )),'t_min_receiptdate':tpch.coll_min(( + from (from g select value l) as i + )),'t_min_receiptdate':COLL_MIN(( select element i.l_receiptdate - from l as i - )),'t_max_comment':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_comment':COLL_MAX(( select element i.l_comment - from l as i + from (from g select value l) as i ))} from LineItem as l group by l.l_partkey as l_partkey +group as g order by l_partkey ;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/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 --git 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 index 2f48cf3..010d03a 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 @@ -22,19 +22,20 @@ use tpch; declare function tmp() { ( - select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * COLL_AVG(( + select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.coll_avg(( select element i.l_quantity - from l as i + from (from g select value l) as i )))} from LineItem as l group by l.l_partkey as l_partkey + group as g ) }; -select element (COLL_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))) + from tmp() as t, + LineItem as l, + Part as p + where p.p_partkey = l.l_partkey and p.p_container = 'MED BOX' and l.l_partkey = t.t_partkey and l.l_quantity < t.t_avg_quantity )) / 7.0); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 5215013..1a11819 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 @@ -20,23 +20,27 @@ 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':COLL_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':tpch.coll_sum(( select element j.l_quantity - from l as j + from (from g select value l) as j ))} from Customer as c, Orders as o, ( - select element {'l_orderkey':l_orderkey,'t_sum_quantity':COLL_SUM(( + select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.coll_sum(( select element i.l_quantity - from l as i + from (select value l from g2) as i ))} from LineItem as l group by l.l_orderkey as l_orderkey + group as g2 ) 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 +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 +group as g order by o_totalprice desc,o_orderdate limit 100 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 9057ccb..df4e276 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 @@ -22,7 +22,7 @@ use tpch; set `import-private-functions` `true`; -select element coll_sum(( +select element COLL_SUM(( select element (l.l_extendedprice * (1 - l.l_discount)) from LineItem as l, Part as p http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 82e38bf..2355162 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 @@ -24,12 +24,13 @@ 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.coll_sum(( + select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * COLL_SUM(( select element i.l_quantity - from l as i + from (from g select value l) as i )))} from LineItem as l group by l.l_partkey as l_partkey,l.l_suppkey as l_suppkey + group as g ) as t2, ( select element {'ps_suppkey':ps.ps_suppkey,'ps_partkey':ps.ps_partkey,'ps_availqty':ps.ps_availqty} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/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 --git 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 index 578d72e..3963de0 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 @@ -22,12 +22,12 @@ use tpch; declare function tmp1() { ( - select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT(( + select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count(( select element i.l_suppkey - from l2 as i - )),'max_suppkey':tpch.coll_max(( + from (from g1 select value g1.l2) as i + )),'max_suppkey': coll_max(( select element i.l_suppkey - from l2 as i + from (from g1 select value g1.l2) as i ))} from ( select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1} @@ -35,16 +35,17 @@ declare function tmp1() { group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1 ) as l2 group by l2.l_orderkey as l_orderkey + group as g1 ) }; declare function tmp2() { ( - select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT(( + select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count(( select element i.l_suppkey - from l2 as i - )),'max_suppkey':COLL_MAX(( + from (from g2 select value g2.l2) as i + )),'max_suppkey': coll_max(( select element i.l_suppkey - from l2 as i + from (from g2 select value g2.l2) as i ))} from ( select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1} @@ -53,28 +54,30 @@ declare function tmp2() { group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1 ) as l2 group by l2.l_orderkey as l_orderkey + group as g2 ) }; 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)) + 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)) + 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 COLL_COUNT(t4) +group as g +let numwait = count(( from g select value t4 )) order by numwait desc,s_name ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 328c753..bdd7323 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 @@ -22,7 +22,7 @@ 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)} + select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':SUBSTR(c.c_phone,1,2)} from Customer as c ) }; @@ -31,12 +31,13 @@ with avg as tpch.coll_avg(( from Customer as c where (c.c_acctbal > 0.0) )) -select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum(( +select element {'cntrycode':cntrycode,'numcust':COLL_COUNT(( from g select value ct )),'totacctbal':COLL_SUM(( select element i.c_acctbal - from ct as i + from (from g select value ct) as i ))} from tpch.q22_customer_tmp() as ct where (ct.c_acctbal > avg) group by ct.cntrycode as cntrycode +group as g order by cntrycode ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue601/query-issue601.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 86568ca..3f3a50a 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 @@ -17,7 +17,7 @@ * under the License. */ /* - * Description : This test case is to verify the fix from issue601 + * 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 @@ -26,8 +26,9 @@ use tpch; -select element {'l_linenumber':l_linenumber,'count_order':COLL_COUNT(l)} +select element {'l_linenumber':l.l_linenumber,'count_order': count((from g select value g))} from LineItem as l -group by l.l_linenumber as l_linenumber -order by l_linenumber +group by l.l_linenumber +group as g +order by l.l_linenumber ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue638/query-issue638.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 3507b9f..46a278c 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 @@ -17,7 +17,7 @@ * under the License. */ /* - * Description : This test case is to verify the fix from issue638 + * 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 @@ -26,9 +26,9 @@ use tpch; -select element {'nation':nation,'o_year':o_year,'sum_profit':COLL_SUM(( - select element pr.amount - from profit as pr +select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.coll_sum(( + select element g.profit.amount + from g ))} from ( select element {'nation':l3.n_name,'o_year':o_year,'amount':amount} @@ -52,12 +52,13 @@ from ( ) as l1 where ((ps.ps_suppkey = l1.l_suppkey) and (ps.ps_partkey = l1.l_partkey)) ) as l2 - where (CONTAINS(p.p_name,'green') and (p.p_partkey = l2.l_partkey)) + where (tpch.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 +group as g order by nation,o_year desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785-2/query-issue785-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index d07b004..7a40cec 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 @@ -17,7 +17,7 @@ * under the License. */ /* - * Description : This test case is to verify the fix from issue785 + * 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 @@ -27,30 +27,32 @@ use tpch; with t as ( - select element {'n_nationkey':nation.n_nationkey,'n_name':nation.n_name} - from Nation as nation, + 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) - ), + 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)) + 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 COLL_SUM(( - select element o.o_totalprice - from orders as o - )) + group as g2 + let sum = coll_sum(( + select element g2.orders.o_totalprice + from g2 + )) ) -select element {'nation_key':x.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 +select element {'nation_key':nation_key,'sum_price':( + select element {'orderdate':g.x.order_date,'sum_price': g.x.sum_price} + from g + order by g.x.sum_price desc limit 3 )} from X as x -group by x.nation_key -order by x.nation_key +group by x.nation_key as nation_key +group as g +order by nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue785/query-issue785.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 02edeea..6c7d7bd 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 @@ -17,7 +17,7 @@ * under the License. */ /* - * Description : This test case is to verify the fix from issue785 + * 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 @@ -26,28 +26,32 @@ use tpch; -select element {'nation_key':x.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 COLL_SUM(( - select element s.sum_price - from i as s +select element {'nation_key':nation_key,'sum_price':( + select element {'orderdate': od,'sum_price':sum} + from g as i + group by i.x.order_date as od + group as g2 + let sum = coll_sum(( + select element g2.i.x.sum_price + from g2 )) order by sum desc limit 3 )} from ( - select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':COLL_SUM(( - select element o.o_totalprice - from orders as o - ))} + select element {'nation_key':nation_key,'order_date':orderdate, + 'sum_price': coll_sum(( + select value g3.orders.o_totalprice + from g3 + ))} from Nation as n, Customer as customer, Orders as orders - where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey)) + 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 + group as g3 ) as x -group by x.nation_key -order by x.nation_key +group by x.nation_key as nation_key +group as g +order by nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/query-issue786/query-issue786.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 7098a55..8d37f4d 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 @@ -17,7 +17,7 @@ * under the License. */ /* - * Description : This test case is to verify the fix from issue786 + * Description : This test case is to verify the fix for issue786 * https://code.google.com/p/asterixdb/issues/detail?id=786 * Expected Res : SUCCESS * Date : 10th Oct. 2014 @@ -30,16 +30,17 @@ 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)) + where orders.o_custkey = customer.c_custkey and customer.c_nationkey = nation.n_nationkey group by orders.o_orderdate as orderdate - with sum as COLL_SUM(( - select element o.o_totalprice - from orders as o - )) + group as g + let sum = coll_sum(( + select element g.orders.o_totalprice + from g + )) order by sum desc limit 3 )} from Nation as nation, SelectedNation as sn -where (nation.n_nationkey = sn.sn_nationkey) +where nation.n_nationkey = sn.sn_nationkey ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785-2/query-issue785-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785-2/query-issue785-2.3.query.sqlpp index 7f17b7a..9254656 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785-2/query-issue785-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785-2/query-issue785-2.3.query.sqlpp @@ -43,12 +43,13 @@ X as ( SELECT nation_key, ( - SELECT order_date AS orderdate, sum_price - FROM X // the X here refers to implicit variable X defined in the outer FROM. + SELECT g.X.order_date AS orderdate, g.X.sum_price + FROM g // the X here refers to implicit variable X defined in the outer FROM. ORDER BY sum_price desc LIMIT 3 ) AS sum_price FROM X GROUP BY nation_key +GROUP AS g ORDER BY nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785/query-issue785.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785/query-issue785.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785/query-issue785.3.query.sqlpp index c775c9a..8d4460a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785/query-issue785.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue785/query-issue785.3.query.sqlpp @@ -29,7 +29,7 @@ USE tpch; SELECT nation_key, ( SELECT od AS orderdate, sum_price - FROM x + FROM (FROM g SELECT VALUE g.x) x GROUP BY order_date AS od LET sum_price = sum(sum_price) ORDER BY sum_price desc @@ -44,5 +44,6 @@ FROM ( GROUP BY o_orderdate as orderdate, n_nationkey as nation_key ) AS x GROUP BY nation_key +GROUP AS g ORDER BY nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-2/query-issue810-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-2/query-issue810-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-2/query-issue810-2.3.query.sqlpp index e361e74..fc9c64d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-2/query-issue810-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-2/query-issue810-2.3.query.sqlpp @@ -34,11 +34,12 @@ FROM LineItem as l WHERE l_shipdate <= '1998-09-02' /* +hash */ GROUP BY l_returnflag, l_linestatus -WITH cheaps AS ( - SELECT ELEMENT l - FROM l - WHERE l_discount > 0.05 +GROUP AS g +LET cheaps = ( + SELECT ELEMENT l.l + FROM g AS l + WHERE l.l.l_discount > 0.05 ), -total_charges AS sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) +total_charges = sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) ORDER BY l_returnflag,l_linestatus ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-3/query-issue810-3.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-3/query-issue810-3.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-3/query-issue810-3.3.query.sqlpp index d8c9222..5186119 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-3/query-issue810-3.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810-3/query-issue810-3.3.query.sqlpp @@ -36,14 +36,15 @@ FROM LineItem AS l WHERE l_shipdate <= '1998-09-02' /* +hash */ GROUP BY l_returnflag, l_linestatus +GROUP AS g WITH expensives AS ( SELECT ELEMENT l_discount - FROM l + FROM (FROM g SELECT VALUE l) l WHERE l_discount <= 0.05 ), cheaps as ( SELECT ELEMENT l - FROM l + FROM (FROM g SELECT VALUE l) l WHERE l_discount > 0.05 ), sum_disc_prices AS sum(l_extendedprice * (1 - l_discount)), http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810/query-issue810.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810/query-issue810.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810/query-issue810.3.query.sqlpp index cf9582a..cb1b00f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810/query-issue810.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue810/query-issue810.3.query.sqlpp @@ -34,15 +34,16 @@ FROM LineItem AS l WHERE l_shipdate <= '1998-09-02' /* +hash */ GROUP BY l_returnflag, l_linestatus +GROUP AS g LET cheap = ( - SELECT ELEMENT l - FROM l - WHERE l_discount > 0.05 + SELECT ELEMENT g.l + FROM g + WHERE g.l.l_discount > 0.05 ), expensive = ( - SELECT ELEMENT l - FROM l - WHERE l_discount <= 0.05 + SELECT ELEMENT g.l + FROM g + WHERE g.l.l_discount <= 0.05 ) ORDER BY l_returnflag,l_linestatus ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp index 864f97b..eaa9369 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785-2/query-issue785-2.3.query.sqlpp @@ -44,11 +44,12 @@ X as ( SELECT x.nation_key As nation_key, ( SELECT y.order_date AS orderdate, y.sum_price As sum_price - FROM x AS y + FROM (FROM g SELECT VALUE x) AS y ORDER BY y.sum_price desc LIMIT 3 ) AS sum_price FROM X AS x GROUP BY x.nation_key +GROUP AS g ORDER BY x.nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp index 7cdce6d..4283a19 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue785/query-issue785.3.query.sqlpp @@ -29,9 +29,9 @@ USE tpch; SELECT x.nation_key AS nation_key, ( SELECT od AS orderdate, sum_price AS sum_price - FROM x as i + FROM (FROM g SELECT VALUE x) as i GROUP BY i.order_date AS od - WITH sum_price AS sum(i.sum_price) + LET sum_price = sum(i.sum_price) ORDER BY sum_price desc LIMIT 3 ) AS sum_price @@ -44,5 +44,6 @@ FROM ( GROUP BY orders.o_orderdate as orderdate,n.n_nationkey as nation_key ) AS x GROUP BY x.nation_key +GROUP AS g ORDER BY x.nation_key ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-2/query-issue810-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index af3421f..bfd62c9 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 @@ -34,11 +34,12 @@ 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 +GROUP AS g +LET cheaps = ( + SELECT ELEMENT m.l + FROM g AS m + WHERE m.l.l_discount > 0.05 ), -total_charges AS sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) +total_charges = 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/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810-3/query-issue810-3.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index dbb9b88..3ee3a31 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 @@ -36,17 +36,18 @@ 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 +GROUP AS g +LET expensives = ( + SELECT ELEMENT i.l.l_discount + FROM g AS i + WHERE i.l.l_discount <= 0.05 ), -cheaps as ( - SELECT ELEMENT i - FROM l AS i - WHERE i.l_discount > 0.05 +cheaps = ( + SELECT ELEMENT i.l + FROM g AS i + WHERE i.l.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)) +sum_disc_prices = sum(l.l_extendedprice * (1 - l.l_discount)), +total_charges = 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/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql/query-issue810/query-issue810.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index fc80184..17e37b3 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 @@ -34,14 +34,15 @@ 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 ( +GROUP AS g +LET cheap = ( SELECT ELEMENT m - FROM l AS m + FROM (FROM g SELECT VALUE l) AS m WHERE m.l_discount > 0.05 ), -expensive AS ( +expensive = ( SELECT ELEMENT a - FROM l AS a + FROM (FROM g SELECT VALUE l) AS a WHERE a.l_discount <= 0.05 ) ORDER BY l_returnflag,l_linestatus http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate/nest_aggregate.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 8efcb7f..389a35d 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 @@ -30,11 +30,12 @@ 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)) + 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.coll_sum(( - select element o.o_totalprice - from orders as o + group as g + let sum = coll_sum(( + select element g.orders.o_totalprice + from g )) order by sum limit 3 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/nest_aggregate2/nest_aggregate2.3.query.sqlpp ---------------------------------------------------------------------- diff --git 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 index 1b70f0e..e137e9f 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 @@ -32,9 +32,10 @@ select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates 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.coll_sum(( - select element o.o_totalprice - from orders as o + group as g + let sum = tpch.coll_sum(( + select element g.orders.o_totalprice + from g )) order by sum limit 3 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/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 --git 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 index ae276b5..e42ce21 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 @@ -19,34 +19,34 @@ use tpch; +set hash_merge "true" -set `import-private-functions` `true`; - -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.coll_sum(( - select element i.l_extendedprice - from l as i - )),'sum_disc_price':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i - )),'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.coll_avg(( - select element i.l_quantity - from l as i - )),'ave_price':tpch.coll_avg(( - select element i.l_extendedprice - from l as i - )),'ave_disc':tpch.coll_avg(( - select element i.l_discount - from l as i - )),'count_order':tpch.count(l)} +select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':COLL_SUM(( + select element i.l.l_quantity + from g as i + )),'sum_base_price':COLL_SUM(( + select element i.l.l_extendedprice + from g as i + )),'sum_disc_price':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i + )),'sum_charge':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount) * (1 + i.l.l_tax)) + from g as i + )),'ave_qty':COLL_AVG(( + select element i.l.l_quantity + from g as i + )),'ave_price':COLL_AVG(( + select element i.l.l_extendedprice + from g as i + )),'ave_disc':COLL_AVG(( + select element i.l.l_discount + from g as i + )),'count_order':COLL_COUNT(( from g select value 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 +group as g order by l_returnflag,l_linestatus ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/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 index cbb55c4..24180d2 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 @@ -45,9 +45,9 @@ declare function tmp1() { }; declare function tmp2() { ( - select element {'p_partkey':p_partkey,'ps_min_supplycost':tpch.coll_min(( - select element i.ps_supplycost - from pssrn as i + select element {'p_partkey':p_partkey,'ps_min_supplycost':COLL_MIN(( + select element i.pssrn.ps_supplycost + from g as i ))} from Part as p, ( @@ -69,6 +69,7 @@ declare function tmp2() { where p.p_partkey = pssrn.p_partkey and p.p_type like '%BRASS' /* +hash */ group by pssrn.p_partkey as p_partkey + group as g ) }; 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} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/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 index bddbb53..f076d71 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 @@ -27,9 +27,10 @@ from Customer as c, 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.coll_sum(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + from (from g select value l) as i )) order by revenue desc,o_orderdate limit 10 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/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 index 9050001..b82242e 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 @@ -46,9 +46,10 @@ from Customer as c, 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.coll_sum(( +group as g +let revenue = COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from o1 as i + from (from g select value o1) as i )) order by revenue desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp index e1012b1..b960393 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp @@ -20,7 +20,7 @@ use tpch; -{'revenue':tpch.coll_sum(( +{'revenue':COLL_SUM(( select element (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)) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp index deeec6b..b6344b2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp @@ -49,9 +49,10 @@ from ( with l_year0 as tpch.`get-year`(locs.l_shipdate) where ((locs.c_nationkey = t.c_nationkey) and (locs.s_nationkey = t.s_nationkey)) group by t.supp_nation as supp_nation,t.cust_nation as cust_nation,l_year0 as l_year -with revenue as tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from locs as i +group as g +let revenue = COLL_SUM(( + select element (i.locs.l_extendedprice * (1 - i.locs.l_discount)) + from g as i )) order by supp_nation,cust_nation,l_year ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp index b9b739d..ce12ffd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp @@ -20,12 +20,12 @@ use tpch; -select element {'year':year,'mkt_share':(tpch.coll_sum(( +select element {'year':year,'mkt_share':(COLL_SUM(( select element case i.s_name = 'BRAZIL' when true then i.revenue when false then 0.0 end - from t as i - )) / tpch.coll_sum(( + from (from g select value t) as i + )) / COLL_SUM(( select element i.revenue - from t as i + from (from g select value t) as i )))} from ( select element {'year':o_year,'revenue':(slnrcop.l_extendedprice * (1 - slnrcop.l_discount)),'s_name':n2.n_name} @@ -65,5 +65,6 @@ from ( where (slnrcop.s_nationkey = n2.n_nationkey) ) as t group by t.year as year +group as g order by year ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp index 0f56186..f469707 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp @@ -22,7 +22,7 @@ use tpch; select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.coll_sum(( select element pr.amount - from profit as pr + from (from g select value profit) as pr ))} from ( select element {'nation':l3.n_name,'o_year':o_year,'amount':amount} @@ -53,5 +53,6 @@ from ( where (o.o_orderkey = l3.l_orderkey) ) as profit group by profit.nation as nation,profit.o_year as o_year +group as g order by nation,o_year desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp index 7278f81..e48e264 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp @@ -34,9 +34,10 @@ from ( where ((l.l_orderkey = ocn.o_orderkey) and (l.l_returnflag = 'R')) ) as locn group by locn.c_custkey as c_custkey,locn.c_name as c_name,locn.c_acctbal as c_acctbal,locn.c_phone as c_phone,locn.n_name as n_name,locn.c_address as c_address,locn.c_comment as c_comment -with revenue as tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from locn as i +group as g +let revenue = coll_sum(( + select element (i.locn.l_extendedprice * (1 - i.locn.l_discount)) + from g as i )) order by revenue desc limit 20 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp index 7278f81..e48e264 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp @@ -34,9 +34,10 @@ from ( where ((l.l_orderkey = ocn.o_orderkey) and (l.l_returnflag = 'R')) ) as locn group by locn.c_custkey as c_custkey,locn.c_name as c_name,locn.c_acctbal as c_acctbal,locn.c_phone as c_phone,locn.n_name as n_name,locn.c_address as c_address,locn.c_comment as c_comment -with revenue as tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from locn as i +group as g +let revenue = coll_sum(( + select element (i.locn.l_extendedprice * (1 - i.locn.l_discount)) + from g as i )) order by revenue desc limit 20 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp index 073835c..2fb8110 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp @@ -20,32 +20,33 @@ use tpch; -with sum as tpch.coll_sum(( +with sum as COLL_SUM(( select element (ps.ps_supplycost * ps.ps_availqty) from Partsupp as ps, ( select element {'s_suppkey':s.s_suppkey} from Supplier as s, Nation as n - where (s.s_nationkey = n.n_nationkey) + where s.s_nationkey = n.n_nationkey ) as sn - where (ps.ps_suppkey = sn.s_suppkey) + where ps.ps_suppkey = sn.s_suppkey )) select element {'partkey':t1.ps_partkey,'part_value':t1.part_value} from ( - select element {'ps_partkey':ps_partkey,'part_value':tpch.coll_sum(( - select element (i.ps_supplycost * i.ps_availqty) - from ps as i + select element {'ps_partkey':ps_partkey,'part_value':COLL_SUM(( + select element (i.ps.ps_supplycost * i.ps.ps_availqty) + from g as i ))} from Partsupp as ps, ( select element {'s_suppkey':s.s_suppkey} from Supplier as s, Nation as n - where (s.s_nationkey = n.n_nationkey) + where s.s_nationkey = n.n_nationkey ) as sn - where (ps.ps_suppkey = sn.s_suppkey) + where ps.ps_suppkey = sn.s_suppkey group by ps.ps_partkey as ps_partkey + group as g ) as t1 where (t1.part_value > (sum * 0.00001)) order by t1.part_value desc http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp index 395279b..e8097c0 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp @@ -17,25 +17,25 @@ * under the License. */ -use tpch; - +use tpch select element {'l_shipmode':l_shipmode,'high_line_count':tpch.coll_sum(( select element case - when i.o_orderpriority = '1-URGENT' or i.o_orderpriority = '2-HIGH' then 1 + when i.o.o_orderpriority = '1-URGENT' or i.o.o_orderpriority = '2-HIGH' then 1 else 0 end - from o as i + from g as i )),'low_line_count':tpch.coll_sum(( select element case - when i.o_orderpriority = '1-URGENT' or i.o_orderpriority = '2-HIGH' then 0 + when i.o.o_orderpriority = '1-URGENT' or i.o.o_orderpriority = '2-HIGH' then 0 else 1 end - from o as i + from g as i ))} from LineItem as l, Orders as o where ((o.o_orderkey = l.l_orderkey) and (l.l_commitdate < l.l_receiptdate) and (l.l_shipdate < l.l_commitdate) and (l.l_receiptdate >= '1994-01-01') and (l.l_receiptdate < '1995-01-01') and ((l.l_shipmode = 'MAIL') or (l.l_shipmode = 'SHIP'))) group by l.l_shipmode as l_shipmode +group as g order by l_shipmode ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp index 24f4f46..4823a39 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp @@ -24,9 +24,9 @@ set `import-private-functions` `true`; select element {'c_count':c_count,'custdist':custdist} from ( - select element {'c_custkey':c_custkey,'c_count':tpch.coll_sum(( + select element {'c_custkey':c_custkey,'c_count':COLL_SUM(( select element i.o_orderkey_count - from co as i + from (select value co from g2) as i ))} from ( select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count(( @@ -37,8 +37,10 @@ from ( from Customer as c ) as co group by co.c_custkey as c_custkey + group as g2 ) as gco group by gco.c_count as c_count -with custdist as tpch.count(gco) +group as g +let custdist = count(( select value gco from g )) order by custdist desc,c_count desc ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp index c20b6ae..864793d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp @@ -19,20 +19,19 @@ use tpch; - select element (100.0 * tpch.coll_sum(( select element case - when i.p_type like 'PROMO%' then i.l_extendedprice * (1 - i.l_discount) + when i.lp.p_type like 'PROMO%' then i.lp.l_extendedprice * (1 - i.lp.l_discount) else 0.0 end - from lp as i + from g as i )) / tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from lp as i + select element (i.lp.l_extendedprice * (1 - i.lp.l_discount)) + from g 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 group as g(lp as lp) -; +; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp index fdc4fcc..acd8537 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp @@ -22,21 +22,22 @@ use tpch; declare function revenue() { ( - select element {'supplier_no':l_suppkey,'total_revenue':tpch.coll_sum(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + select element {'supplier_no':l_suppkey,'total_revenue':COLL_SUM(( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i ))} from LineItem as l - where ((l.l_shipdate >= '1996-01-01') and (l.l_shipdate < '1996-04-01')) + where l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01' group by l.l_suppkey as l_suppkey + group as g ) }; -with m as tpch.coll_max(( +with m as COLL_MAX(( select element r2.total_revenue - from tpch.revenue() as r2 + from 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))) +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/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp index 131c8a0..47dc549 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp @@ -41,9 +41,10 @@ from ( 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 coll_count(( +group as g +let supplier_cnt = COLL_COUNT(( select element i.ps_suppkey - from t2 as i + from (from g select value t2) as i )) order by supplier_cnt desc,p_brand,p_type,p_size ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp index 2e057d7..f22d29e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp @@ -20,38 +20,40 @@ use tpch; -select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.coll_avg(( +select element {'t_partkey':l_partkey,'t_count':COLL_COUNT((from g select value l)), + 't_avg_quantity':(0.2 * COLL_AVG(( select element i.l_quantity - from l as i - ))),'t_max_suppkey':tpch.coll_max(( + from (from g select value l) as i + ))),'t_max_suppkey':COLL_MAX(( select element i.l_suppkey - from l as i - )),'t_max_linenumber':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_linenumber':COLL_MAX(( select element i.l_linenumber - from l as i - )),'t_avg_extendedprice':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_extendedprice':COLL_AVG(( select element i.l_extendedprice - from l as i - )),'t_avg_discount':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_discount':COLL_AVG(( select element i.l_discount - from l as i - )),'t_avg_tax':tpch.coll_avg(( + from (from g select value l) as i + )),'t_avg_tax':COLL_AVG(( select element i.l_tax - from l as i - )),'t_max_shipdate':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_shipdate':COLL_MAX(( select element i.l_shipdate - from l as i - )),'t_min_commitdate':tpch.coll_min(( + from (from g select value l) as i + )),'t_min_commitdate':COLL_MIN(( select element i.l_commitdate - from l as i - )),'t_min_receiptdate':tpch.coll_min(( + from (from g select value l) as i + )),'t_min_receiptdate':COLL_MIN(( select element i.l_receiptdate - from l as i - )),'t_max_comment':tpch.coll_max(( + from (from g select value l) as i + )),'t_max_comment':COLL_MAX(( select element i.l_comment - from l as i + from (from g select value l) as i ))} from LineItem as l group by l.l_partkey as l_partkey +group as g order by l_partkey ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp index ddbbecc..010d03a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp @@ -24,16 +24,17 @@ declare function tmp() { ( select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.coll_avg(( select element i.l_quantity - from l as i + from (from g select value l) as i )))} from LineItem as l group by l.l_partkey as l_partkey + group as g ) }; -select element (tpch.coll_sum(( +select element (coll_sum(( select element l.l_extendedprice - from tpch.tmp() as t, + from tmp() as t, LineItem as l, Part as p where p.p_partkey = l.l_partkey and p.p_container = 'MED BOX' and l.l_partkey = t.t_partkey and l.l_quantity < t.t_avg_quantity http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp index e0976a0..1a11819 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp @@ -22,21 +22,25 @@ 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.coll_sum(( select element j.l_quantity - from l as j + from (from g select value l) as j ))} from Customer as c, Orders as o, ( select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.coll_sum(( select element i.l_quantity - from l as i + from (select value l from g2) as i ))} from LineItem as l group by l.l_orderkey as l_orderkey + group as g2 ) as t, LineItem as l -where ((c.c_custkey = o.o_custkey) and (o.o_orderkey = t.l_orderkey) and (t.t_sum_quantity > 30) and (l.l_orderkey = t.l_orderkey)) -group by c.c_name as c_name,c.c_custkey as c_custkey,o.o_orderkey as o_orderkey,o.o_orderdate as o_orderdate,o.o_totalprice as o_totalprice +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 +group as g order by o_totalprice desc,o_orderdate limit 100 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp index 9057ccb..df4e276 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp @@ -22,7 +22,7 @@ use tpch; set `import-private-functions` `true`; -select element coll_sum(( +select element COLL_SUM(( select element (l.l_extendedprice * (1 - l.l_discount)) from LineItem as l, Part as p http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp index 82e38bf..2355162 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp @@ -24,12 +24,13 @@ 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.coll_sum(( + select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * COLL_SUM(( select element i.l_quantity - from l as i + from (from g select value l) as i )))} from LineItem as l group by l.l_partkey as l_partkey,l.l_suppkey as l_suppkey + group as g ) as t2, ( select element {'ps_suppkey':ps.ps_suppkey,'ps_partkey':ps.ps_partkey,'ps_availqty':ps.ps_availqty} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/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-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp index 3ccb9b8..3963de0 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/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-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp @@ -24,10 +24,10 @@ declare function tmp1() { ( select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count(( select element i.l_suppkey - from l2 as i - )),'max_suppkey':tpch.coll_max(( + from (from g1 select value g1.l2) as i + )),'max_suppkey': coll_max(( select element i.l_suppkey - from l2 as i + from (from g1 select value g1.l2) as i ))} from ( select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1} @@ -35,16 +35,17 @@ declare function tmp1() { group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1 ) as l2 group by l2.l_orderkey as l_orderkey + group as g1 ) }; declare function tmp2() { ( select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count(( select element i.l_suppkey - from l2 as i - )),'max_suppkey':tpch.coll_max(( + from (from g2 select value g2.l2) as i + )),'max_suppkey': coll_max(( select element i.l_suppkey - from l2 as i + from (from g2 select value g2.l2) as i ))} from ( select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1} @@ -53,6 +54,7 @@ declare function tmp2() { group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1 ) as l2 group by l2.l_orderkey as l_orderkey + group as g2 ) }; select element {'s_name':s_name,'numwait':numwait} @@ -65,16 +67,17 @@ from ( 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) + 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)) + 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) +group as g +let numwait = count(( from g select value t4 )) order by numwait desc,s_name ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp index 328c753..bdd7323 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp @@ -22,7 +22,7 @@ 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)} + select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':SUBSTR(c.c_phone,1,2)} from Customer as c ) }; @@ -31,12 +31,13 @@ with avg as tpch.coll_avg(( from Customer as c where (c.c_acctbal > 0.0) )) -select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum(( +select element {'cntrycode':cntrycode,'numcust':COLL_COUNT(( from g select value ct )),'totacctbal':COLL_SUM(( select element i.c_acctbal - from ct as i + from (from g select value ct) as i ))} from tpch.q22_customer_tmp() as ct where (ct.c_acctbal > avg) group by ct.cntrycode as cntrycode +group as g order by cntrycode ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp index eaac9ce..dc54947 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp @@ -30,26 +30,27 @@ declare function q22_customer_tmp() { ( select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':phone_substr} from Customer as c - with phone_substr as tpch.substring(c.c_phone,1,2) + let phone_substr = tpch.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')) ) }; with avg as tpch.coll_avg(( select element c.c_acctbal from Customer as c - with phone_substr as tpch.substring(c.c_phone,1,2) + let phone_substr = tpch.substring(c.c_phone,1,2) where ((c.c_acctbal > 0.0) and ((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 element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum(( select element i.c_acctbal - from ct as i + from (from g select value ct) as i ))} from tpch.q22_customer_tmp() as ct where (coll_count(( select element o from Orders as o - where (ct.c_custkey = o.o_custkey) + where ct.c_custkey = o.o_custkey )) = 0) group by ct.cntrycode as cntrycode +group as g order by cntrycode ;
