http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp index 4613e75..3f3a50a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp @@ -26,8 +26,9 @@ use tpch; -select element {'l_linenumber':l.l_linenumber,'count_order':tpch.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 +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-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp index a694e97..7a40cec 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp @@ -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 tpch.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-with-index/query-issue785/query-issue785.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp index 214a7c2..6c7d7bd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp @@ -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 tpch.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':tpch.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-with-index/query-issue786/query-issue786.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp index 37e3c92..8d37f4d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp @@ -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 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 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-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp index d75ea7b..38ba16f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp @@ -31,14 +31,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 +group as g with cheaps as ( - select element m - from l as m - where (m.l_discount > 0.05) + select element m.l + from g as m + where (m.l.l_discount > 0.05) ), charges as ( - select element (a.l_extendedprice * (1 - a.l_discount) * (1 + a.l_tax)) - from l as a + select element (a.l.l_extendedprice * (1 - a.l.l_discount) * (1 + a.l.l_tax)) + from g as a ) 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/query-issue810-3/query-issue810-3.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp index f2656ee..555437a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp @@ -31,23 +31,24 @@ 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) ), - charges as ( - select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax)) - from l as i + charges = ( + select element (i.l.l_extendedprice * (1 - i.l.l_discount) * (1 + i.l.l_tax)) + from g as i ), - disc_prices as ( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + disc_prices = ( + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i ) 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/query-issue810/query-issue810.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp index f4638f2..6877db8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp @@ -31,15 +31,16 @@ 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) +group as g +let cheap = ( + select element m.l + from g as m + where (m.l.l_discount > 0.05) ), - expensive as ( - select element a - from l as a - where (a.l_discount <= 0.05) + expensive = ( + select element a.l + from g as a + where (a.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-with-index/query-issue827/query-issue827.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp index e735107..c488ab1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp @@ -26,10 +26,10 @@ use tpch; -{'count_cheaps':coll_count(( +{'count_cheaps': coll_count(( select element l.l_quantity from LineItem as l -)),'count_expensives':tpch.coll_sum(( +)),'count_expensives': coll_sum(( select element e from ( select element l.l_extendedprice http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp index 8efcb7f..389a35d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/nest_aggregate2/nest_aggregate2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp index 1b70f0e..e137e9f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/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/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp index 9ac57fd..e42ce21 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp @@ -22,30 +22,31 @@ use tpch; set hash_merge "true" select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':COLL_SUM(( - select element i.l_quantity - from l as i + select element i.l.l_quantity + from g as i )),'sum_base_price':COLL_SUM(( - select element i.l_extendedprice - from l as i + select element i.l.l_extendedprice + from g as i )),'sum_disc_price':COLL_SUM(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + select element (i.l.l_extendedprice * (1 - i.l.l_discount)) + from g as i )),'sum_charge':COLL_SUM(( - select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax)) - from l as i + 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_quantity - from l as i + select element i.l.l_quantity + from g as i )),'ave_price':COLL_AVG(( - select element i.l_extendedprice - from l as i + select element i.l.l_extendedprice + from g as i )),'ave_disc':COLL_AVG(( - select element i.l_discount - from l as i - )),'count_order':COLL_COUNT(l)} + 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/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp index f6ad005..24180d2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp @@ -46,8 +46,8 @@ declare function tmp1() { declare function tmp2() { ( select element {'p_partkey':p_partkey,'ps_min_supplycost':COLL_MIN(( - select element i.ps_supplycost - from pssrn as i + 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/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp index ef0d15d..f076d71 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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 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/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp index f30ccbe..b82242e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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 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/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp index eacf03d..b6344b2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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 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/q08_national_market_share/q08_national_market_share.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q08_national_market_share/q08_national_market_share.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q08_national_market_share/q08_national_market_share.3.query.sqlpp index 70b59e0..ce12ffd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q08_national_market_share/q08_national_market_share.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q08_national_market_share/q08_national_market_share.3.query.sqlpp @@ -22,10 +22,10 @@ use tpch; 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 + 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/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/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q10_returned_item/q10_returned_item.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q10_returned_item/q10_returned_item.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q10_returned_item/q10_returned_item.3.query.sqlpp index 7278f81..e48e264 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q10_returned_item/q10_returned_item.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q11_important_stock/q11_important_stock.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q11_important_stock/q11_important_stock.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q11_important_stock/q11_important_stock.3.query.sqlpp index 6956c4b..2fb8110 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q11_important_stock/q11_important_stock.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q11_important_stock/q11_important_stock.3.query.sqlpp @@ -27,25 +27,26 @@ with sum as COLL_SUM(( 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':COLL_SUM(( - select element (i.ps_supplycost * i.ps_availqty) - from ps as i + 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/q12_shipping/q12_shipping.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q12_shipping/q12_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q12_shipping/q12_shipping.3.query.sqlpp index 58d0c9e..fa54051 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q12_shipping/q12_shipping.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q12_shipping/q12_shipping.3.query.sqlpp @@ -21,16 +21,19 @@ use tpch; select element {'l_shipmode':l_shipmode,'high_line_count':COLL_SUM(( - select element + select element CASE i.o_orderpriority = '1-URGENT' OR i.o_orderpriority = '2-HIGH' WHEN true THEN 1 WHEN false THEN 0 END - from o as i + from (select value o from g) as i )),'low_line_count':COLL_SUM(( select element case when i.o_orderpriority = '1-URGENT' OR i.o_orderpriority = '2-HIGH' then 0 else 1 end - from o as i + from (select value o 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'))) +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/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp index e68fcc4..4823a39 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp @@ -26,7 +26,7 @@ select element {'c_count':c_count,'custdist':custdist} from ( 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/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp index 89ade9d..2ec6a9c 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp @@ -22,14 +22,14 @@ use tpch; select element (100.0 * COLL_SUM(( select element case i.p_type like 'PROMO%' when true then i.l_extendedprice * (1 - i.l_discount) else 0.0 end - from lp as i + from (from g select value lp) as i )) / COLL_SUM(( select element (i.l_extendedprice * (1 - i.l_discount)) - from lp as i + from (from g select value 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')) +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); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q15_top_supplier/q15_top_supplier.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q15_top_supplier/q15_top_supplier.3.query.sqlpp index 70b5d38..acd8537 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q15_top_supplier/q15_top_supplier.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q15_top_supplier/q15_top_supplier.3.query.sqlpp @@ -23,20 +23,21 @@ use tpch; declare function revenue() { ( select element {'supplier_no':l_suppkey,'total_revenue':COLL_SUM(( - select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + 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 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/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp index 0cdbcfd..47dc549 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp index d39c75a..f22d29e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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':COLL_COUNT(l),'t_avg_quantity':(0.2 * 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':COLL_MAX(( + from (from g select value l) as i + ))),'t_max_suppkey':COLL_MAX(( select element i.l_suppkey - from l as i + from (from g select value l) as i )),'t_max_linenumber':COLL_MAX(( select element i.l_linenumber - from l as i + from (from g select value l) as i )),'t_avg_extendedprice':COLL_AVG(( select element i.l_extendedprice - from l as i + from (from g select value l) as i )),'t_avg_discount':COLL_AVG(( select element i.l_discount - from l as i + from (from g select value l) as i )),'t_avg_tax':COLL_AVG(( select element i.l_tax - from l as i + from (from g select value l) as i )),'t_max_shipdate':COLL_MAX(( select element i.l_shipdate - from l as i + from (from g select value l) as i )),'t_min_commitdate':COLL_MIN(( select element i.l_commitdate - from l as i + from (from g select value l) as i )),'t_min_receiptdate':COLL_MIN(( select element i.l_receiptdate - from l as i + 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/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/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp index 2bc3b25..2355162 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp @@ -26,10 +26,11 @@ from ( from ( 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/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/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/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/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/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/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp index 274adbc..bdd7323 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp @@ -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':COLL_COUNT(ct),'totacctbal':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/query-issue562/query-issue562.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue562/query-issue562.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue562/query-issue562.3.query.sqlpp index eaac9ce..dc54947 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue562/query-issue562.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/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 ; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp index 4613e75..3f3a50a 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp @@ -26,8 +26,9 @@ use tpch; -select element {'l_linenumber':l.l_linenumber,'count_order':tpch.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 +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/query-issue638/query-issue638.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue638/query-issue638.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue638/query-issue638.3.query.sqlpp index 6da0192..46a278c 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue638/query-issue638.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue638/query-issue638.3.query.sqlpp @@ -27,8 +27,8 @@ use tpch; select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.coll_sum(( - select element pr.amount - from profit as pr + select element g.profit.amount + from g ))} from ( select element {'nation':l3.n_name,'o_year':o_year,'amount':amount} @@ -59,5 +59,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/query-issue785-2/query-issue785-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785-2/query-issue785-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785-2/query-issue785-2.3.query.sqlpp index 7141505..7a40cec 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785-2/query-issue785-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785-2/query-issue785-2.3.query.sqlpp @@ -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 tpch.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':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 {'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 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/query-issue785/query-issue785.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785/query-issue785.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785/query-issue785.3.query.sqlpp index 1f5e8e3..6c7d7bd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785/query-issue785.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue785/query-issue785.3.query.sqlpp @@ -27,27 +27,31 @@ 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.coll_sum(( - select element s.sum_price - from i as s + 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':tpch.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 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/query-issue786/query-issue786.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue786/query-issue786.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue786/query-issue786.3.query.sqlpp index 37e3c92..8d37f4d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue786/query-issue786.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue786/query-issue786.3.query.sqlpp @@ -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 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 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/query-issue810-2/query-issue810-2.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-2/query-issue810-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-2/query-issue810-2.3.query.sqlpp index d75ea7b..38ba16f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-2/query-issue810-2.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-2/query-issue810-2.3.query.sqlpp @@ -31,14 +31,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 +group as g with cheaps as ( - select element m - from l as m - where (m.l_discount > 0.05) + select element m.l + from g as m + where (m.l.l_discount > 0.05) ), charges as ( - select element (a.l_extendedprice * (1 - a.l_discount) * (1 + a.l_tax)) - from l as a + select element (a.l.l_extendedprice * (1 - a.l.l_discount) * (1 + a.l.l_tax)) + from g as a ) 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/query-issue810-3/query-issue810-3.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-3/query-issue810-3.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-3/query-issue810-3.3.query.sqlpp index f2656ee..f25f99d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-3/query-issue810-3.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810-3/query-issue810-3.3.query.sqlpp @@ -31,23 +31,24 @@ 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 ( +group as g +let expensives = ( select element i.l_discount - from l as i + from (from g select value l) as i where (i.l_discount <= 0.05) ), - cheaps as ( + cheaps = ( select element i - from l as i + from (from g select value l) as i where (i.l_discount > 0.05) ), - charges as ( + charges = ( select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax)) - from l as i + from (from g select value l) as i ), - disc_prices as ( + disc_prices = ( select element (i.l_extendedprice * (1 - i.l_discount)) - from l as i + from (from g select value l) as i ) 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/query-issue810/query-issue810.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810/query-issue810.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810/query-issue810.3.query.sqlpp index f4638f2..6877db8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810/query-issue810.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue810/query-issue810.3.query.sqlpp @@ -31,15 +31,16 @@ 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) +group as g +let cheap = ( + select element m.l + from g as m + where (m.l.l_discount > 0.05) ), - expensive as ( - select element a - from l as a - where (a.l_discount <= 0.05) + expensive = ( + select element a.l + from g as a + where (a.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/query-issue827/query-issue827.3.query.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue827/query-issue827.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue827/query-issue827.3.query.sqlpp index e735107..c488ab1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue827/query-issue827.3.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue827/query-issue827.3.query.sqlpp @@ -26,10 +26,10 @@ use tpch; -{'count_cheaps':coll_count(( +{'count_cheaps': coll_count(( select element l.l_quantity from LineItem as l -)),'count_expensives':tpch.coll_sum(( +)),'count_expensives': coll_sum(( select element e from ( select element l.l_extendedprice http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm index 055512a..6463d6d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm @@ -1,44 +1,44 @@ -distribute result [$$19] +distribute result [$$17] -- DISTRIBUTE_RESULT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$19]) + project ([$$17]) -- STREAM_PROJECT |PARTITIONED| - assign [$$19] <- [{"deptId": $#1, "star_cost": $$22}] + assign [$$17] <- [{"deptId": $#1, "star_cost": $$20}] -- ASSIGN |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - group by ([$#1 := $$27]) decor ([]) { - aggregate [$$22] <- [agg-sql-sum($$26)] + group by ([$#1 := $$25]) decor ([]) { + aggregate [$$20] <- [agg-sql-sum($$24)] -- AGGREGATE |LOCAL| nested tuple source -- NESTED_TUPLE_SOURCE |LOCAL| } - -- SORT_GROUP_BY[$$27] |PARTITIONED| + -- SORT_GROUP_BY[$$25] |PARTITIONED| exchange - -- HASH_PARTITION_EXCHANGE [$$27] |PARTITIONED| - group by ([$$27 := $$20]) decor ([]) { - aggregate [$$26] <- [agg-local-sql-sum($$17)] + -- HASH_PARTITION_EXCHANGE [$$25] |PARTITIONED| + group by ([$$25 := $$18]) decor ([]) { + aggregate [$$24] <- [agg-local-sql-sum($$15)] -- AGGREGATE |LOCAL| nested tuple source -- NESTED_TUPLE_SOURCE |LOCAL| } - -- SORT_GROUP_BY[$$20] |PARTITIONED| + -- SORT_GROUP_BY[$$18] |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$17, $$20]) + project ([$$18, $$15]) -- STREAM_PROJECT |PARTITIONED| - assign [$$20] <- [substring($$24.getField("department_id"), 1)] + assign [$$18] <- [substring($$22.getField("department_id"), 1)] -- ASSIGN |PARTITIONED| - project ([$$17, $$24]) + project ([$$22, $$15]) -- STREAM_PROJECT |PARTITIONED| - assign [$$17, $$24] <- [$$e.getField("salary"), $$e.getField("dept")] + assign [$$15, $$22] <- [$$e.getField("salary"), $$e.getField("dept")] -- ASSIGN |PARTITIONED| project ([$$e]) -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - data-scan []<-[$$21, $$e] <- gby.Employee + data-scan []<-[$$19, $$e] <- gby.Employee -- DATASOURCE_SCAN |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm index d7f1893..864391b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm @@ -1,40 +1,40 @@ -distribute result [$$18] +distribute result [$$16] -- DISTRIBUTE_RESULT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$18]) + project ([$$16]) -- STREAM_PROJECT |PARTITIONED| - assign [$$18] <- [{"deptId": $#1, "star_cost": $$21}] + assign [$$16] <- [{"deptId": $#1, "star_cost": $$19}] -- ASSIGN |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - group by ([$#1 := $$25]) decor ([]) { - aggregate [$$21] <- [agg-sql-sum($$24)] + group by ([$#1 := $$23]) decor ([]) { + aggregate [$$19] <- [agg-sql-sum($$22)] -- AGGREGATE |LOCAL| nested tuple source -- NESTED_TUPLE_SOURCE |LOCAL| } - -- SORT_GROUP_BY[$$25] |PARTITIONED| + -- SORT_GROUP_BY[$$23] |PARTITIONED| exchange - -- HASH_PARTITION_EXCHANGE [$$25] |PARTITIONED| - group by ([$$25 := $$19]) decor ([]) { - aggregate [$$24] <- [agg-local-sql-sum($$16)] + -- HASH_PARTITION_EXCHANGE [$$23] |PARTITIONED| + group by ([$$23 := $$17]) decor ([]) { + aggregate [$$22] <- [agg-local-sql-sum($$14)] -- AGGREGATE |LOCAL| nested tuple source -- NESTED_TUPLE_SOURCE |LOCAL| } - -- SORT_GROUP_BY[$$19] |PARTITIONED| + -- SORT_GROUP_BY[$$17] |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - project ([$$16, $$19]) + project ([$$17, $$14]) -- STREAM_PROJECT |PARTITIONED| - assign [$$19, $$16] <- [substring($$e.getField(1), 1), $$e.getField(2)] + assign [$$17, $$14] <- [substring($$e.getField(1), 1), $$e.getField(2)] -- ASSIGN |PARTITIONED| project ([$$e]) -- STREAM_PROJECT |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - data-scan []<-[$$20, $$e] <- gby.Employee + data-scan []<-[$$18, $$e] <- gby.Employee -- DATASOURCE_SCAN |PARTITIONED| exchange -- ONE_TO_ONE_EXCHANGE |PARTITIONED| http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/big-object/big_object_groupby/big_object_groupby.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/big-object/big_object_groupby/big_object_groupby.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/big-object/big_object_groupby/big_object_groupby.3.ast index 885385d..1b4831f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/big-object/big_object_groupby/big_object_groupby.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/big-object/big_object_groupby/big_object_groupby.3.ast @@ -10,7 +10,18 @@ RecordConstructor [ ( LiteralExpr [STRING] [lines] : - Variable [ Name=$i ] + ( + SELECT ELEMENT [ + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [i] + Variable [ Name=$partkey ] + Variable [ Name=$g ] + ] + ] + FROM [ Variable [ Name=$g ] + AS Variable [ Name=$g ] + ] + ) ) ] ] @@ -26,7 +37,7 @@ Groupby Variable [ Name=$i ] Field=l_partkey ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( i:=Variable [ Name=$i ] ) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at02/at02.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at02/at02.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at02/at02.3.ast index 755ff18..e97fefb 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at02/at02.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at02/at02.3.ast @@ -30,14 +30,20 @@ RecordConstructor [ ] FROM [ ( SELECT ELEMENT [ - Variable [ Name=$ii ] + FieldAccessor [ + Variable [ Name=$ii ] + Field=m + ] ] - FROM [ Variable [ Name=$m ] + FROM [ Variable [ Name=$gr ] AS Variable [ Name=$ii ] ] Orderby FieldAccessor [ - Variable [ Name=$ii ] + FieldAccessor [ + Variable [ Name=$ii ] + Field=m + ] Field=message-id ] ASC @@ -80,7 +86,7 @@ Groupby Variable [ Name=$u ] Field=id ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$gr ] ( u:=Variable [ Name=$u ] m:=Variable [ Name=$m ] http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at03/at03.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at03/at03.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at03/at03.3.ast index 426052c..78388da 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at03/at03.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at03/at03.3.ast @@ -30,14 +30,20 @@ RecordConstructor [ ] FROM [ ( SELECT ELEMENT [ - Variable [ Name=$i1 ] + FieldAccessor [ + Variable [ Name=$i1 ] + Field=i + ] ] - FROM [ Variable [ Name=$i ] + FROM [ Variable [ Name=$g ] AS Variable [ Name=$i1 ] ] Orderby FieldAccessor [ - Variable [ Name=$i1 ] + FieldAccessor [ + Variable [ Name=$i1 ] + Field=i + ] Field=name ] ASC @@ -72,7 +78,7 @@ Groupby Field=user-since ] ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( i:=Variable [ Name=$i ] ) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at04/at04.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at04/at04.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at04/at04.3.ast index 1efc828..e99b1b6 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at04/at04.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at04/at04.3.ast @@ -30,14 +30,20 @@ RecordConstructor [ ] FROM [ ( SELECT ELEMENT [ - Variable [ Name=$i1 ] + FieldAccessor [ + Variable [ Name=$i1 ] + Field=i + ] ] - FROM [ Variable [ Name=$i ] + FROM [ Variable [ Name=$g ] AS Variable [ Name=$i1 ] ] Orderby FieldAccessor [ - Variable [ Name=$i1 ] + FieldAccessor [ + Variable [ Name=$i1 ] + Field=i + ] Field=o_orderkey ] ASC @@ -70,7 +76,7 @@ Groupby Variable [ Name=$i ] Field=o_custkey ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( i:=Variable [ Name=$i ] ) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at05/at05.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at05/at05.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at05/at05.3.ast index 12ed5fa..33ebf91 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at05/at05.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at05/at05.3.ast @@ -14,7 +14,17 @@ FROM [ ( ( LiteralExpr [STRING] [x] : - Variable [ Name=$x ] + ( + SELECT ELEMENT [ + FieldAccessor [ + Variable [ Name=$g ] + Field=x + ] + ] + FROM [ Variable [ Name=$g ] + AS Variable [ Name=$g ] + ] + ) ) ( LiteralExpr [STRING] [uid] @@ -44,7 +54,7 @@ FROM [ ( Variable [ Name=$x ] Field=o_custkey ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( x:=Variable [ Name=$x ] ) @@ -76,14 +86,20 @@ FROM [ ( ] FROM [ ( SELECT ELEMENT [ - Variable [ Name=$xxx ] + FieldAccessor [ + Variable [ Name=$xxx ] + Field=x + ] ] - FROM [ Variable [ Name=$x ] + FROM [ Variable [ Name=$g ] AS Variable [ Name=$xxx ] ] Orderby FieldAccessor [ - Variable [ Name=$xxx ] + FieldAccessor [ + Variable [ Name=$xxx ] + Field=x + ] Field=o_orderkey ] ASC http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast index d913c8b..11063f7 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast @@ -55,7 +55,11 @@ FROM [ ( LiteralExpr [STRING] [partkey] : FieldAccessor [ - Variable [ Name=$i ] + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [i] + Variable [ Name=$g ] + Variable [ Name=$l_partkey ] + ] Field=l_partkey ] ) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/query-issue550/query-issue550.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/query-issue550/query-issue550.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/query-issue550/query-issue550.3.ast index e5a516d..41a157e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/query-issue550/query-issue550.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/query-issue550/query-issue550.3.ast @@ -159,9 +159,12 @@ RecordConstructor [ : ( SELECT ELEMENT [ - Variable [ Name=$srec ] + FieldAccessor [ + Variable [ Name=$srec ] + Field=s ] - FROM [ Variable [ Name=$s ] + ] + FROM [ Variable [ Name=$g ] AS Variable [ Name=$srec ] ] ) @@ -178,7 +181,7 @@ Groupby Variable [ Name=$s ] Field=uid ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( s:=Variable [ Name=$s ] ) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/9c2e9f0e/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/hdfs/hdfs_02/hdfs_02.3.ast ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/hdfs/hdfs_02/hdfs_02.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/hdfs/hdfs_02/hdfs_02.3.ast index f1ac649..ba6fb25 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/hdfs/hdfs_02/hdfs_02.3.ast +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/hdfs/hdfs_02/hdfs_02.3.ast @@ -11,7 +11,18 @@ RecordConstructor [ LiteralExpr [STRING] [count] : FunctionCall test.count@1[ - Variable [ Name=$token ] + ( + SELECT ELEMENT [ + FunctionCall Metadata.resolve@-1[ + LiteralExpr [STRING] [token] + Variable [ Name=$g ] + Variable [ Name=$tok ] + ] + ] + FROM [ Variable [ Name=$g ] + AS Variable [ Name=$g ] + ] + ) ] ) ] @@ -33,7 +44,7 @@ Groupby Variable [ Name=$tok ] := Variable [ Name=$token ] - GROUP AS Variable [ Name=#1 ] + GROUP AS Variable [ Name=$g ] ( line:=Variable [ Name=$line ] token:=Variable [ Name=$token ]
