asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [5/7] asterixdb git commit: [ASTERIXDB-1753][SQL] Disable auto-plural for group-by.
Date Fri, 30 Jun 2017 23:44:13 GMT
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
 ;


Mime
View raw message