You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2017/06/30 23:44:13 UTC
[5/7] asterixdb git commit: [ASTERIXDB-1753][SQL] Disable auto-plural
for group-by.
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
;