asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ima...@apache.org
Subject [25/50] [abbrv] incubator-asterixdb git commit: Merge branch 'master' into hyracks-merge2
Date Thu, 07 Apr 2016 15:00:00 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q06_forecast_revenue_change/q06_forecast_revenue_change.3.query.sqlpp
index 9dee61f,0000000..e1012b1
mode 100644,000000..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
@@@ -1,27 -1,0 +1,27 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- {'revenue':tpch.sum((
++{'revenue':tpch.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/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
index 74d3b5a,0000000..f2838d7
mode 100644,000000..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
@@@ -1,57 -1,0 +1,57 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function q7_volume_shipping_tmp() {
 +(
 +    select element {'supp_nation':n1.n_name,'cust_nation':n2.n_name,'s_nationkey':n1.n_nationkey,'c_nationkey':n2.n_nationkey}
 +    from  Nation as n1,
 +          Nation as n2
 +    where ((n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY'))
 +)
 +};
 +select element {'supp_nation':supp_nation,'cust_nation':cust_nation,'l_year':l_year,'revenue':revenue}
 +from  (
 +    select element {'l_shipdate':loc.l_shipdate,'l_extendedprice':loc.l_extendedprice,'l_discount':loc.l_discount,'c_nationkey':loc.c_nationkey,'s_nationkey':s.s_nationkey}
 +    from  (
 +        select element {'l_shipdate':lo.l_shipdate,'l_extendedprice':lo.l_extendedprice,'l_discount':lo.l_discount,'l_suppkey':lo.l_suppkey,'c_nationkey':c.c_nationkey}
 +        from  (
 +            select element {'l_shipdate':l.l_shipdate,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_suppkey':l.l_suppkey,'o_custkey':o.o_custkey}
 +            from  LineItem as l,
 +                  Orders as o
 +            where ((o.o_orderkey = l.l_orderkey) and (l.l_shipdate >= '1992-01-01') and (l.l_shipdate <= '1996-12-31'))
 +        ) as lo,
 +              Customer as c
 +        where (c.c_custkey = lo.o_custkey)
 +    ) as loc,
 +          Supplier as s
 +    where (s.s_suppkey = loc.l_suppkey)
 +) as locs,
 +      tpch.q7_volume_shipping_tmp() as t
 +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.sum((
++with  revenue as tpch.coll_sum((
 +      select element (i.l_extendedprice * (1 - i.l_discount))
 +      from  locs as i
 +  ))
 +order by supp_nation,cust_nation,l_year
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp
index 77d3881,0000000..a221b8e
mode 100644,000000..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
@@@ -1,69 -1,0 +1,69 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element {'year':year,'mkt_share':(tpch.sum((
++select element {'year':year,'mkt_share':(tpch.coll_sum((
 +          select element tpch."switch-case"((i.s_name = 'BRAZIL'),true,i.revenue,false,0.0)
 +          from  t as i
-       )) / tpch.sum((
++      )) / tpch.coll_sum((
 +          select element i.revenue
 +          from  t as i
 +      )))}
 +from  (
 +    select element {'year':o_year,'revenue':(slnrcop.l_extendedprice * (1 - slnrcop.l_discount)),'s_name':n2.n_name}
 +    from  (
 +        select element {'o_orderdate':lnrcop.o_orderdate,'l_discount':lnrcop.l_discount,'l_extendedprice':lnrcop.l_extendedprice,'l_suppkey':lnrcop.l_suppkey,'s_nationkey':s.s_nationkey}
 +        from  Supplier as s,
 +              (
 +            select element {'o_orderdate':lnrco.o_orderdate,'l_discount':lnrco.l_discount,'l_extendedprice':lnrco.l_extendedprice,'l_suppkey':lnrco.l_suppkey}
 +            from  (
 +                select element {'o_orderdate':nrco.o_orderdate,'l_partkey':l.l_partkey,'l_discount':l.l_discount,'l_extendedprice':l.l_extendedprice,'l_suppkey':l.l_suppkey}
 +                from  LineItem as l,
 +                      (
 +                    select element {'o_orderdate':o.o_orderdate,'o_orderkey':o.o_orderkey}
 +                    from  Orders as o,
 +                          (
 +                        select element {'c_custkey':c.c_custkey}
 +                        from  Customer as c,
 +                              (
 +                            select element {'n_nationkey':n1.n_nationkey}
 +                            from  Nation as n1,
 +                                  Region as r1
 +                            where ((n1.n_regionkey = r1.r_regionkey) and (r1.r_name = 'AMERICA'))
 +                        ) as nr
 +                        where (c.c_nationkey = nr.n_nationkey)
 +                    ) as nrc
 +                    where (nrc.c_custkey = o.o_custkey)
 +                ) as nrco
 +                where ((l.l_orderkey = nrco.o_orderkey) and (nrco.o_orderdate >= '1995-01-01') and (nrco.o_orderdate < '1996-12-31'))
 +            ) as lnrco,
 +                  Part as p
 +            where ((p.p_partkey = lnrco.l_partkey) and (p.p_type = 'ECONOMY ANODIZED STEEL'))
 +        ) as lnrcop
 +        where (s.s_suppkey = lnrcop.l_suppkey)
 +    ) as slnrcop,
 +          Nation as n2
 +    with  o_year as tpch."get-year"(slnrcop.o_orderdate)
 +    where (slnrcop.s_nationkey = n2.n_nationkey)
 +) as t
 +group by t.year as year
 +order by year
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/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 --cc 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 b7e5e4b,0000000..a76e49e
mode 100644,000000..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
@@@ -1,57 -1,0 +1,57 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.sum((
++select element {'nation':nation,'o_year':o_year,'sum_profit':tpch.coll_sum((
 +        select element pr.amount
 +        from  profit as pr
 +    ))}
 +from  (
 +    select element {'nation':l3.n_name,'o_year':o_year,'amount':amount}
 +    from  Orders as o,
 +          (
 +        select element {'l_extendedprice':l2.l_extendedprice,'l_discount':l2.l_discount,'l_quantity':l2.l_quantity,'l_orderkey':l2.l_orderkey,'n_name':l2.n_name,'ps_supplycost':l2.ps_supplycost}
 +        from  Part as p join
 +              (
 +            select element {'l_extendedprice':l1.l_extendedprice,'l_discount':l1.l_discount,'l_quantity':l1.l_quantity,'l_partkey':l1.l_partkey,'l_orderkey':l1.l_orderkey,'n_name':l1.n_name,'ps_supplycost':ps.ps_supplycost}
 +            from  Partsupp as ps join
 +                  (
 +                select element {'l_suppkey':l.l_suppkey,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount,'l_quantity':l.l_quantity,'l_partkey':l.l_partkey,'l_orderkey':l.l_orderkey,'n_name':s1.n_name}
 +                from  (
 +                    select element {'s_suppkey':s.s_suppkey,'n_name':n.n_name}
 +                    from  Supplier as s,
 +                          Nation as n
 +                    where (n.n_nationkey = s.s_nationkey)
 +                ) as s1 join
 +                      LineItem as l
 +                on (s1.s_suppkey = l.l_suppkey)
 +            ) as l1
 +            on ((ps.ps_suppkey = l1.l_suppkey) and (ps.ps_partkey = l1.l_partkey))
 +        ) as l2
 +         on (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
 +order by nation,o_year desc
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item/q10_returned_item.3.query.sqlpp
index 50fb6c3,0000000..7278f81
mode 100644,000000..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
@@@ -1,43 -1,0 +1,43 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +select element {'c_custkey':c_custkey,'c_name':c_name,'revenue':revenue,'c_acctbal':c_acctbal,'n_name':n_name,'c_address':c_address,'c_phone':c_phone,'c_comment':c_comment}
 +from  (
 +    select element {'c_custkey':ocn.c_custkey,'c_name':ocn.c_name,'c_acctbal':ocn.c_acctbal,'n_name':ocn.n_name,'c_address':ocn.c_address,'c_phone':ocn.c_phone,'c_comment':ocn.c_comment,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount}
 +    from  LineItem as l,
 +          (
 +        select element {'c_custkey':c.c_custkey,'c_name':c.c_name,'c_acctbal':c.c_acctbal,'n_name':n.n_name,'c_address':c.c_address,'c_phone':c.c_phone,'c_comment':c.c_comment,'o_orderkey':o.o_orderkey}
 +        from  Orders as o,
 +              Customer as c,
 +              Nation as n
 +        where (((c.c_custkey = o.o_custkey) and (o.o_orderdate >= '1993-10-01') and (o.o_orderdate < '1994-01-01')) and (c.c_nationkey = n.n_nationkey))
 +    ) as ocn
 +    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.sum((
++with  revenue as tpch.coll_sum((
 +      select element (i.l_extendedprice * (1 - i.l_discount))
 +      from  locn as i
 +  ))
 +order by revenue desc
 +limit 20
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
index 50fb6c3,0000000..7278f81
mode 100644,000000..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
@@@ -1,43 -1,0 +1,43 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +select element {'c_custkey':c_custkey,'c_name':c_name,'revenue':revenue,'c_acctbal':c_acctbal,'n_name':n_name,'c_address':c_address,'c_phone':c_phone,'c_comment':c_comment}
 +from  (
 +    select element {'c_custkey':ocn.c_custkey,'c_name':ocn.c_name,'c_acctbal':ocn.c_acctbal,'n_name':ocn.n_name,'c_address':ocn.c_address,'c_phone':ocn.c_phone,'c_comment':ocn.c_comment,'l_extendedprice':l.l_extendedprice,'l_discount':l.l_discount}
 +    from  LineItem as l,
 +          (
 +        select element {'c_custkey':c.c_custkey,'c_name':c.c_name,'c_acctbal':c.c_acctbal,'n_name':n.n_name,'c_address':c.c_address,'c_phone':c.c_phone,'c_comment':c.c_comment,'o_orderkey':o.o_orderkey}
 +        from  Orders as o,
 +              Customer as c,
 +              Nation as n
 +        where (((c.c_custkey = o.o_custkey) and (o.o_orderdate >= '1993-10-01') and (o.o_orderdate < '1994-01-01')) and (c.c_nationkey = n.n_nationkey))
 +    ) as ocn
 +    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.sum((
++with  revenue as tpch.coll_sum((
 +      select element (i.l_extendedprice * (1 - i.l_discount))
 +      from  locn as i
 +  ))
 +order by revenue desc
 +limit 20
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q11_important_stock/q11_important_stock.3.query.sqlpp
index ea3bd5a,0000000..073835c
mode 100644,000000..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
@@@ -1,52 -1,0 +1,52 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- with  sum as tpch.sum((
++with  sum as tpch.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)
 +      ) as sn
 +      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.sum((
++    select element {'ps_partkey':ps_partkey,'part_value':tpch.coll_sum((
 +            select element (i.ps_supplycost * i.ps_availqty)
 +            from  ps 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)
 +    ) as sn
 +    where (ps.ps_suppkey = sn.s_suppkey)
 +    group by ps.ps_partkey as ps_partkey
 +) as t1
 +where (t1.part_value > (sum * 0.00001))
 +order by t1.part_value desc
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q12_shipping/q12_shipping.3.query.sqlpp
index 61b685e,0000000..2bf33fc
mode 100644,000000..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
@@@ -1,35 -1,0 +1,35 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element {'l_shipmode':l_shipmode,'high_line_count':tpch.sum((
++select element {'l_shipmode':l_shipmode,'high_line_count':tpch.coll_sum((
 +        select element tpch."switch-case"(((i.o_orderpriority = '1-URGENT') or (i.o_orderpriority = '2-HIGH')),true,1,false,0)
 +        from  o as i
-     )),'low_line_count':tpch.sum((
++    )),'low_line_count':tpch.coll_sum((
 +        select element tpch."switch-case"(((i.o_orderpriority = '1-URGENT') or (i.o_orderpriority = '2-HIGH')),true,0,false,1)
 +        from  o 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
 +order by l_shipmode
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index afa7c9b,0000000..d21e1f5
mode 100644,000000..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
@@@ -1,44 -1,0 +1,44 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +set "import-private-functions" "true";
 +
 +select element {'c_count':c_count,'custdist':custdist}
 +from  (
-     select element {'c_custkey':c_custkey,'c_count':tpch.sum((
++    select element {'c_custkey':c_custkey,'c_count':tpch.coll_sum((
 +            select element i.o_orderkey_count
 +            from  co as i
 +        ))}
 +    from  (
-         select element {'c_custkey':c.c_custkey,'o_orderkey_count':tpch.count((
++        select element {'c_custkey':c.c_custkey,'o_orderkey_count':coll_count((
 +                select element o.o_orderkey
 +                from  Orders as o
 +                where ((c.c_custkey = o.o_custkey) and tpch.not(tpch.like(o.o_comment,'%special%requests%')))
 +            ))}
 +        from  Customer as c
 +    ) as co
 +    group by co.c_custkey as c_custkey
 +) as gco
 +group by gco.c_count as c_count
 +with  custdist as tpch.count(gco)
 +order by custdist desc,c_count desc
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index a8f29d3,0000000..a376e45
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,35 -1,0 +1,35 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element (100.0 * tpch.sum((
++select element (100.0 * tpch.coll_sum((
 +      select element tpch."switch-case"(tpch.like(i.p_type,'PROMO%'),true,(i.l_extendedprice * (1 - i.l_discount)),false,0.0)
 +      from  lp as i
-   )) / tpch.sum((
++  )) / tpch.coll_sum((
 +      select element (i.l_extendedprice * (1 - i.l_discount))
 +      from  lp as i
 +  )))
 +from  LineItem as l,
 +      Part as p
 +let lp = {'p_type': p.p_type, 'l_extendedprice': l.l_extendedprice, 'l_discount': l.l_discount}
 +where ((l.l_partkey = p.p_partkey) and (l.l_shipdate >= '1995-09-01') and (l.l_shipdate < '1995-10-01'))
 +group by 1 as t
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q15_top_supplier/q15_top_supplier.3.query.sqlpp
index e21104b,0000000..fdc4fcc
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,42 -1,0 +1,42 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function revenue() {
 +(
-     select element {'supplier_no':l_suppkey,'total_revenue':tpch.sum((
++    select element {'supplier_no':l_suppkey,'total_revenue':tpch.coll_sum((
 +            select element (i.l_extendedprice * (1 - i.l_discount))
 +            from  l as i
 +        ))}
 +    from  LineItem as l
 +    where ((l.l_shipdate >= '1996-01-01') and (l.l_shipdate < '1996-04-01'))
 +    group by l.l_suppkey as l_suppkey
 +)
 +};
- with  m as tpch.max((
++with  m as tpch.coll_max((
 +      select element r2.total_revenue
 +      from  tpch.revenue() as r2
 +  ))
 +select element {'s_suppkey':s.s_suppkey,'s_name':s.s_name,'s_address':s.s_address,'s_phone':s.s_phone,'total_revenue':r.total_revenue}
 +from  Supplier as s,
 +      tpch.revenue() as r
 +where ((s.s_suppkey = r.supplier_no) and (r.total_revenue < (m + 0.000000001)) and (r.total_revenue > (m - 0.000000001)))
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index cc4d01a,0000000..acf3281
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,49 -1,0 +1,49 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function tmp() {
 +(
 +    select element {'p_brand':psp.p_brand,'p_type':psp.p_type,'p_size':psp.p_size,'ps_suppkey':psp.ps_suppkey}
 +    from  (
 +        select element {'p_brand':p.p_brand,'p_type':p.p_type,'p_size':p.p_size,'ps_suppkey':ps.ps_suppkey}
 +        from  Partsupp as ps,
 +              Part as p
 +        where ((p.p_partkey = ps.ps_partkey) and (p.p_brand != 'Brand#45') and tpch.not(tpch.like(p.p_type,'MEDIUM POLISHED%')))
 +    ) as psp,
 +          Supplier as s
 +    where ((psp.ps_suppkey = s.s_suppkey) and tpch.not(tpch.like(s.s_comment,'%Customer%Complaints%')))
 +)
 +};
 +select element {'p_brand':p_brand,'p_type':p_type,'p_size':p_size,'supplier_cnt':supplier_cnt}
 +from  (
 +    select element {'p_brand':p_brand1,'p_type':p_type1,'p_size':p_size1,'ps_suppkey':ps_suppkey1}
 +    from  tpch.tmp() as t
 +    where ((t.p_size = 49) or (t.p_size = 14) or (t.p_size = 23) or (t.p_size = 45) or (t.p_size = 19) or (t.p_size = 3) or (t.p_size = 36) or (t.p_size = 9))
 +    group by t.p_brand as p_brand1,t.p_type as p_type1,t.p_size as p_size1,t.ps_suppkey as ps_suppkey1
 +) as t2
 +group by t2.p_brand as p_brand,t2.p_type as p_type,t2.p_size as p_size
- with  supplier_cnt as tpch.count((
++with  supplier_cnt as coll_count((
 +      select element i.ps_suppkey
 +      from  t2 as i
 +  ))
 +order by supplier_cnt desc,p_brand,p_type,p_size
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_large_gby_variant/q17_large_gby_variant.3.query.sqlpp
index ada4f75,0000000..2e057d7
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,57 -1,0 +1,57 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.avg((
++select element {'t_partkey':l_partkey,'t_count':tpch.count(l),'t_avg_quantity':(0.2 * tpch.coll_avg((
 +          select element i.l_quantity
 +          from  l as i
-       ))),'t_max_suppkey':tpch.max((
++      ))),'t_max_suppkey':tpch.coll_max((
 +        select element i.l_suppkey
 +        from  l as i
-     )),'t_max_linenumber':tpch.max((
++    )),'t_max_linenumber':tpch.coll_max((
 +        select element i.l_linenumber
 +        from  l as i
-     )),'t_avg_extendedprice':tpch.avg((
++    )),'t_avg_extendedprice':tpch.coll_avg((
 +        select element i.l_extendedprice
 +        from  l as i
-     )),'t_avg_discount':tpch.avg((
++    )),'t_avg_discount':tpch.coll_avg((
 +        select element i.l_discount
 +        from  l as i
-     )),'t_avg_tax':tpch.avg((
++    )),'t_avg_tax':tpch.coll_avg((
 +        select element i.l_tax
 +        from  l as i
-     )),'t_max_shipdate':tpch.max((
++    )),'t_max_shipdate':tpch.coll_max((
 +        select element i.l_shipdate
 +        from  l as i
-     )),'t_min_commitdate':tpch.min((
++    )),'t_min_commitdate':tpch.coll_min((
 +        select element i.l_commitdate
 +        from  l as i
-     )),'t_min_receiptdate':tpch.min((
++    )),'t_min_receiptdate':tpch.coll_min((
 +        select element i.l_receiptdate
 +        from  l as i
-     )),'t_max_comment':tpch.max((
++    )),'t_max_comment':tpch.coll_max((
 +        select element i.l_comment
 +        from  l as i
 +    ))}
 +from  LineItem as l
 +group by l.l_partkey as l_partkey
 +order by l_partkey
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
index 79c397c,0000000..ddbbecc
mode 100644,000000..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
@@@ -1,40 -1,0 +1,40 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function tmp() {
 +(
-     select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.avg((
++    select element {'t_partkey':l_partkey,'t_avg_quantity':(0.2 * tpch.coll_avg((
 +              select element i.l_quantity
 +              from  l as i
 +          )))}
 +    from  LineItem as l
 +    group by l.l_partkey as l_partkey
 +)
 +};
 +
- select element (tpch.sum((
++select element (tpch.coll_sum((
 +      select element l.l_extendedprice
 +      from  tpch.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/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
index 89ff8f8,0000000..e0976a0
mode 100644,000000..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
@@@ -1,42 -1,0 +1,42 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
- select element {'c_name':c_name,'c_custkey':c_custkey,'o_orderkey':o_orderkey,'o_orderdate':o_orderdate,'o_totalprice':o_totalprice,'sum_quantity':tpch.sum((
++select element {'c_name':c_name,'c_custkey':c_custkey,'o_orderkey':o_orderkey,'o_orderdate':o_orderdate,'o_totalprice':o_totalprice,'sum_quantity':tpch.coll_sum((
 +        select element j.l_quantity
 +        from  l as j
 +    ))}
 +from  Customer as c,
 +      Orders as o,
 +      (
-     select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.sum((
++    select element {'l_orderkey':l_orderkey,'t_sum_quantity':tpch.coll_sum((
 +            select element i.l_quantity
 +            from  l as i
 +        ))}
 +    from  LineItem as l
 +    group by l.l_orderkey as l_orderkey
 +) as t,
 +      LineItem as l
 +where ((c.c_custkey = o.o_custkey) and (o.o_orderkey = t.l_orderkey) and (t.t_sum_quantity > 30) and (l.l_orderkey = 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
 +order by o_totalprice desc,o_orderdate
 +limit 100
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
index 7065f87,0000000..f245189
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,30 -1,0 +1,30 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +set "import-private-functions" "true";
 +
- select element tpch.sum((
++select element tpch.coll_sum((
 +    select element (l.l_extendedprice * (1 - l.l_discount))
 +    from  LineItem as l,
 +          Part as p
 +    where ((p.p_partkey = l.l_partkey) and (((p.p_brand = 'Brand#12') and tpch."reg-exp"(p.p_container,'SM CASE||SM BOX||SM PACK||SM PKG') and (l.l_quantity >= 1) and (l.l_quantity <= 11) and (p.p_size >= 1) and (p.p_size <= 5) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#23') and tpch."reg-exp"(p.p_container,'MED BAG||MED BOX||MED PKG||MED PACK') and (l.l_quantity >= 10) and (l.l_quantity <= 20) and (p.p_size >= 1) and (p.p_size <= 10) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON')) or ((p.p_brand = 'Brand#34') and tpch."reg-exp"(p.p_container,'LG CASE||LG BOX||LG PACK||LG PKG') and (l.l_quantity >= 20) and (l.l_quantity <= 30) and (p.p_size >= 1) and (p.p_size <= 15) and tpch."reg-exp"(l.l_shipmode,'AIR||AIR REG') and (l.l_shipinstruct = 'DELIVER IN PERSON'))))
 +));

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
index 0657ad3,0000000..82e38bf
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,53 -1,0 +1,53 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +select element {'s_name':t4.s_name,'s_address':t4.s_address}
 +from  (
 +    select distinct element {'ps_suppkey':pst1.ps_suppkey}
 +    from  (
-         select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * tpch.sum((
++        select element {'l_partkey':l_partkey,'l_suppkey':l_suppkey,'sum_quantity':(0.5 * tpch.coll_sum((
 +                  select element i.l_quantity
 +                  from  l as i
 +              )))}
 +        from  LineItem as l
 +        group by l.l_partkey as l_partkey,l.l_suppkey as l_suppkey
 +    ) as t2,
 +          (
 +        select element {'ps_suppkey':ps.ps_suppkey,'ps_partkey':ps.ps_partkey,'ps_availqty':ps.ps_availqty}
 +        from  Partsupp as ps,
 +              (
 +            select distinct element {'p_partkey':p.p_partkey}
 +            from  Part as p
 +        ) as t1
 +        where (ps.ps_partkey = t1.p_partkey)
 +    ) as pst1
 +    where ((pst1.ps_partkey = t2.l_partkey) and (pst1.ps_suppkey = t2.l_suppkey) and (pst1.ps_availqty > t2.sum_quantity))
 +) as t3,
 +      (
 +    select element {'s_name':s.s_name,'s_address':s.s_address,'s_suppkey':s.s_suppkey}
 +    from  Nation as n,
 +          Supplier as s
 +    where (s.s_nationkey = n.n_nationkey)
 +) as t4
 +where (t3.ps_suppkey = t4.s_suppkey)
 +order by t4.s_name
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
index f91068d,0000000..3ccb9b8
mode 100644,000000..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
@@@ -1,80 -1,0 +1,80 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function tmp1() {
 +(
-     select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
++    select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count((
 +            select element i.l_suppkey
 +            from  l2 as i
-         )),'max_suppkey':tpch.max((
++        )),'max_suppkey':tpch.coll_max((
 +            select element i.l_suppkey
 +            from  l2 as i
 +        ))}
 +    from  (
 +        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
 +        from  LineItem as l
 +        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
 +    ) as l2
 +    group by l2.l_orderkey as l_orderkey
 +)
 +};
 +declare function tmp2() {
 +(
-     select element {'l_orderkey':l_orderkey,'count_suppkey':tpch.count((
++    select element {'l_orderkey':l_orderkey,'count_suppkey':coll_count((
 +            select element i.l_suppkey
 +            from  l2 as i
-         )),'max_suppkey':tpch.max((
++        )),'max_suppkey':tpch.coll_max((
 +            select element i.l_suppkey
 +            from  l2 as i
 +        ))}
 +    from  (
 +        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
 +        from  LineItem as l
 +        where (l.l_receiptdate > l.l_commitdate)
 +        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
 +    ) as l2
 +    group by l2.l_orderkey as l_orderkey
 +)
 +};
 +select element {'s_name':s_name,'numwait':numwait}
 +from  (
 +    select element {'s_name':t3.s_name,'l_suppkey':t3.l_suppkey,'l_orderkey':t2.l_orderkey,'count_suppkey':t2.count_suppkey,'max_suppkey':t2.max_suppkey}
 +    from  (
 +            select element {'s_name':ns.s_name,'l_orderkey':t1.l_orderkey,'l_suppkey':l.l_suppkey}
 +            from  LineItem as l,
 +                  (
 +                        select element {'s_name':s.s_name,'s_suppkey':s.s_suppkey}
 +                        from  Nation as n,
 +                        Supplier as s
 +                        where (s.s_nationkey = n.n_nationkey)
 +                   ) as ns,
 +                   Orders as o,
 +                   tpch.tmp1() as t1
 +            where ns.s_suppkey = l.l_suppkey and l.l_receiptdate > l.l_commitdate and o.o_orderkey = l.l_orderkey and l.l_orderkey = t1.l_orderkey
 +    ) as t3,
 +      tpch.tmp2() as t2
 +    where ((t2.count_suppkey >= 0) and (t3.l_orderkey = t2.l_orderkey))
 +) as t4
 +group by t4.s_name as s_name
 +with  numwait as tpch.count(t4)
 +order by numwait desc,s_name
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.sqlpp
index 6136008,0000000..328c753
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-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
@@@ -1,42 -1,0 +1,42 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +declare function q22_customer_tmp() {
 +(
 +    select element {'c_acctbal':c.c_acctbal,'c_custkey':c.c_custkey,'cntrycode':tpch.substring(c.c_phone,1,2)}
 +    from  Customer as c
 +)
 +};
- with  avg as tpch.avg((
++with  avg as tpch.coll_avg((
 +      select element c.c_acctbal
 +      from  Customer as c
 +      where (c.c_acctbal > 0.0)
 +  ))
- select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.sum((
++select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum((
 +        select element i.c_acctbal
 +        from  ct as i
 +    ))}
 +from  tpch.q22_customer_tmp() as ct
 +where (ct.c_acctbal > avg)
 +group by ct.cntrycode as cntrycode
 +order by cntrycode
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue562/query-issue562.3.query.sqlpp
index ac54a30,0000000..eaac9ce
mode 100644,000000..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
@@@ -1,55 -1,0 +1,55 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue562
 + * https://code.google.com/p/asterixdb/issues/detail?id=562
 + * Expected Res : SUCCESS
 + * Date         : 15th Jan. 2015
 + */
 +
 +use tpch;
 +
 +
 +declare function q22_customer_tmp() {
 +(
 +    select 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)
 +    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.avg((
++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)
 +      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.sum((
++select element {'cntrycode':cntrycode,'numcust':tpch.count(ct),'totacctbal':tpch.coll_sum((
 +        select element i.c_acctbal
 +        from  ct as i
 +    ))}
 +from  tpch.q22_customer_tmp() as ct
- where (tpch.count((
++where (coll_count((
 +    select element o
 +    from  Orders as o
 +    where (ct.c_custkey = o.o_custkey)
 +)) = 0)
 +group by ct.cntrycode as cntrycode
 +order by cntrycode
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
index 2cad6ea,0000000..de434ef
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785-2/query-issue785-2.3.query.sqlpp
@@@ -1,55 -1,0 +1,55 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue785
 + * https://code.google.com/p/asterixdb/issues/detail?id=785
 + * Expected Res : SUCCESS
 + * Date         : 2nd Oct. 2014
 + */
 +
 +use tpch;
 +
 +
 +with  t as (
 +      select element {'n_nationkey':nation.n_nationkey,'n_name':nation.n_name}
 +      from  Nation as nation,
 +            SelectedNation as sn
 +      where (nation.n_nationkey = sn.n_nationkey)
 +  ),
 +      X as (
 +      select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':sum}
 +      from  t as n,
 +            Customer as customer,
 +            Orders as orders
 +      where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
 +      group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
-       with  sum as tpch.sum((
++      with  sum as tpch.coll_sum((
 +            select element o.o_totalprice
 +            from  orders as o
 +        ))
 +  )
 +select element {'nation_key':nation_key,'sum_price':(
 +        select element {'orderdate':y.order_date,'sum_price':y.sum_price}
 +        from  x as y
 +        order by y.sum_price desc
 +        limit 3
 +    )}
 +from  X as x
 +group by x.nation_key as nation_key
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
index ed649ca,0000000..c7761f3
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue785/query-issue785.3.query.sqlpp
@@@ -1,52 -1,0 +1,52 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue785
 + * https://code.google.com/p/asterixdb/issues/detail?id=785
 + * Expected Res : SUCCESS
 + * Date         : 2nd Oct. 2014
 + */
 +
 +use tpch;
 +
 +
 +select element {'nation_key':nation_key,'sum_price':(
 +        select element {'orderdate':od,'sum_price':sum}
 +        from  x as i
 +        group by i.order_date as od
-         with  sum as tpch.sum((
++        with  sum as tpch.coll_sum((
 +              select element s.sum_price
 +              from  i as s
 +          ))
 +        order by sum desc
 +        limit 3
 +    )}
 +from  (
-     select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':tpch.sum((
++    select element {'nation_key':nation_key,'order_date':orderdate,'sum_price':tpch.coll_sum((
 +            select element o.o_totalprice
 +            from  orders as o
 +        ))}
 +    from  Nation as n,
 +          Customer as customer,
 +          Orders as orders
 +    where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = n.n_nationkey))
 +    group by orders.o_orderdate as orderdate,n.n_nationkey as nation_key
 +) as x
 +group by x.nation_key as nation_key
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
index dead643,0000000..37e3c92
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue786/query-issue786.3.query.sqlpp
@@@ -1,45 -1,0 +1,45 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue786
 + * https://code.google.com/p/asterixdb/issues/detail?id=786
 + * Expected Res : SUCCESS
 + * Date         : 10th Oct. 2014
 + */
 +
 +use tpch;
 +
 +
 +select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
 +        select element {'order_date':orderdate,'sum_price':sum}
 +        from  Orders as orders,
 +              Customer as customer
 +        where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
 +        group by orders.o_orderdate as orderdate
-         with  sum as tpch.sum((
++        with  sum as tpch.coll_sum((
 +              select element o.o_totalprice
 +              from  orders as o
 +          ))
 +        order by sum desc
 +        limit 3
 +    )}
 +from  Nation as nation,
 +      SelectedNation as sn
 +where (nation.n_nationkey = sn.sn_nationkey)
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
index 16d3fe0,0000000..d75ea7b
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-2/query-issue810-2.3.query.sqlpp
@@@ -1,44 -1,0 +1,44 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue810
 + * https://code.google.com/p/asterixdb/issues/detail?id=810
 + * Expected Res : SUCCESS
 + * Date         : 16th Nov. 2014
 + */
 +
 +use tpch;
 +
 +
- select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheaps),'total_charges':tpch.sum(charges)}
++select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':coll_count(cheaps),'total_charges':tpch.coll_sum(charges)}
 +from  LineItem as l
 +where (l.l_shipdate <= '1998-09-02')
 +/* +hash */
 +group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
 +with  cheaps as (
 +      select element m
 +      from  l as m
 +      where (m.l_discount > 0.05)
 +  ),
 +      charges as (
 +      select element (a.l_extendedprice * (1 - a.l_discount) * (1 + a.l_tax))
 +      from  l as a
 +  )
 +order by l_returnflag,l_linestatus
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
index 576192d,0000000..f2656ee
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810-3/query-issue810-3.3.query.sqlpp
@@@ -1,53 -1,0 +1,53 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue810
 + * https://code.google.com/p/asterixdb/issues/detail?id=810
 + * Expected Res : SUCCESS
 + * Date         : 16th Nov. 2014
 + */
 +
 +use tpch;
 +
 +
- select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheaps),'avg_expensive_discounts':tpch.avg(expensives),'sum_disc_prices':tpch.sum(disc_prices),'total_charges':tpch.sum(charges)}
++select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':coll_count(cheaps),'avg_expensive_discounts':tpch.coll_avg(expensives),'sum_disc_prices':tpch.coll_sum(disc_prices),'total_charges':tpch.coll_sum(charges)}
 +from  LineItem as l
 +where (l.l_shipdate <= '1998-09-02')
 +/* +hash */
 +group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
 +with  expensives as (
 +      select element i.l_discount
 +      from  l as i
 +      where (i.l_discount <= 0.05)
 +  ),
 +      cheaps as (
 +      select element i
 +      from  l as i
 +      where (i.l_discount > 0.05)
 +  ),
 +      charges as (
 +      select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
 +      from  l as i
 +  ),
 +      disc_prices as (
 +      select element (i.l_extendedprice * (1 - i.l_discount))
 +      from  l as i
 +  )
 +order by l_returnflag,l_linestatus
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
index 01e1654,0000000..f4638f2
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue810/query-issue810.3.query.sqlpp
@@@ -1,45 -1,0 +1,45 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue810
 + * https://code.google.com/p/asterixdb/issues/detail?id=810
 + * Expected Res : SUCCESS
 + * Date         : 16th Nov. 2014
 + */
 +
 +use tpch;
 +
 +
- select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':tpch.count(cheap),'count_expensives':tpch.count(expensive)}
++select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'count_cheaps':coll_count(cheap),'count_expensives':coll_count(expensive)}
 +from  LineItem as l
 +where (l.l_shipdate <= '1998-09-02')
 +/* +hash */
 +group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
 +with  cheap as (
 +      select element m
 +      from  l as m
 +      where (m.l_discount > 0.05)
 +  ),
 +      expensive as (
 +      select element a
 +      from  l as a
 +      where (a.l_discount <= 0.05)
 +  )
 +order by l_returnflag,l_linestatus
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827-2/query-issue827-2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827-2/query-issue827-2.3.query.sqlpp
index 562f78f,0000000..aefe8a3
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827-2/query-issue827-2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827-2/query-issue827-2.3.query.sqlpp
@@@ -1,55 -1,0 +1,55 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue827
 + * https://code.google.com/p/asterixdb/issues/detail?id=827
 + * Expected Res : SUCCESS
 + * Date         : 3rd Dec. 2014
 + */
 +
 +use tpch;
 +
 +
- {'sum_qty_partial':tpch.sum((
++{'sum_qty_partial':tpch.coll_sum((
 +    select element i.l_quantity
 +    from  LineItem as i
 +    where (i.l_shipdate <= '1998-09-02')
- )),'sum_base_price':tpch.sum((
++)),'sum_base_price':tpch.coll_sum((
 +    select element i.l_extendedprice
 +    from  LineItem as i
- )),'sum_disc_price':tpch.sum((
++)),'sum_disc_price':tpch.coll_sum((
 +    select element (i.l_extendedprice * (1 - i.l_discount))
 +    from  LineItem as i
- )),'sum_charge':tpch.sum((
++)),'sum_charge':tpch.coll_sum((
 +    select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
 +    from  LineItem as i
- )),'ave_qty':tpch.avg((
++)),'ave_qty':tpch.coll_avg((
 +    select element i.l_quantity
 +    from  LineItem as i
 +    where (i.l_shipdate <= '1998-09-02')
- )),'ave_price':tpch.avg((
++)),'ave_price':tpch.coll_avg((
 +    select element i.l_extendedprice
 +    from  LineItem as i
- )),'ave_disc':tpch.avg((
++)),'ave_disc':tpch.coll_avg((
 +    select element i.l_discount
 +    from  LineItem as i
- )),'count_order':tpch.count((
++)),'count_order':coll_count((
 +    select element l
 +    from  LineItem as l
 +))};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp
index d056bcb,0000000..e735107
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue827/query-issue827.3.query.sqlpp
@@@ -1,38 -1,0 +1,38 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue827
 + * https://code.google.com/p/asterixdb/issues/detail?id=827
 + * Expected Res : SUCCESS
 + * Date         : 16th Nov. 2014
 + */
 +
 +use tpch;
 +
 +
- {'count_cheaps':tpch.count((
++{'count_cheaps':coll_count((
 +    select element l.l_quantity
 +    from  LineItem as l
- )),'count_expensives':tpch.sum((
++)),'count_expensives':tpch.coll_sum((
 +    select element e
 +    from  (
 +        select element l.l_extendedprice
 +        from  LineItem as l
 +    ) as e
 +))};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp
index 3a015ae,0000000..8efcb7f
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate/nest_aggregate.3.query.sqlpp
@@@ -1,46 -1,0 +1,46 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue782
 + * https://code.google.com/p/asterixdb/issues/detail?id=782
 + * Expected Res : SUCCESS
 + * Date         : 2nd Jun 2014
 + */
 +
 +use tpch;
 +
 +
 +select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
 +        select element {'order_date':orderdate,'sum_price':sum}
 +        from  Orders as orders,
 +              Customer as customer
 +        where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
 +        group by orders.o_orderdate as orderdate
-         with  sum as tpch.sum((
++        with  sum as tpch.coll_sum((
 +              select element o.o_totalprice
 +              from  orders as o
 +          ))
 +        order by sum
 +        limit 3
 +    )}
 +from  Nation as nation,
 +      SelectedNation as sn
 +where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
 +order by nation.n_nationkey
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp
index 1aa687c,0000000..1b70f0e
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/nest_aggregate2/nest_aggregate2.3.query.sqlpp
@@@ -1,46 -1,0 +1,46 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +/*
 + * Description  : This test case is to verify the fix for issue782
 + * https://code.google.com/p/asterixdb/issues/detail?id=782
 + * Expected Res : SUCCESS
 + * Date         : 2nd Jun 2014
 + */
 +
 +use tpch;
 +
 +
 +select element {'nation_key':nation.n_nationkey,'name':nation.n_name,'aggregates':(
 +        select element orderdate
 +        from  Orders as orders,
 +              Customer as customer
 +        where ((orders.o_custkey = customer.c_custkey) and (customer.c_nationkey = nation.n_nationkey))
 +        group by orders.o_orderdate as orderdate
-         with  sum as tpch.sum((
++        with  sum as tpch.coll_sum((
 +              select element o.o_totalprice
 +              from  orders as o
 +          ))
 +        order by sum
 +        limit 3
 +    )}
 +from  Nation as nation,
 +      SelectedNation as sn
 +where (nation.n_nationkey /*+ indexnl */ = sn.n_nationkey)
 +order by nation.n_nationkey
 +;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/8516517e/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --cc asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
index 94b313f,0000000..686dd30
mode 100644,000000..100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q01_pricing_summary_report_nt/q01_pricing_summary_report_nt.3.query.sqlpp
@@@ -1,52 -1,0 +1,52 @@@
 +/*
 + * Licensed to the Apache Software Foundation (ASF) under one
 + * or more contributor license agreements.  See the NOTICE file
 + * distributed with this work for additional information
 + * regarding copyright ownership.  The ASF licenses this file
 + * to you under the Apache License, Version 2.0 (the
 + * "License"); you may not use this file except in compliance
 + * with the License.  You may obtain a copy of the License at
 + *
 + *   http://www.apache.org/licenses/LICENSE-2.0
 + *
 + * Unless required by applicable law or agreed to in writing,
 + * software distributed under the License is distributed on an
 + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 + * KIND, either express or implied.  See the License for the
 + * specific language governing permissions and limitations
 + * under the License.
 + */
 +
 +use tpch;
 +
 +
 +set "import-private-functions" "true";
 +
- select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':tpch.sum((
++select element {'l_returnflag':l_returnflag,'l_linestatus':l_linestatus,'sum_qty':COLL_SUM((
 +        select element i.l_quantity
 +        from  l as i
-     )),'sum_base_price':tpch.sum((
++    )),'sum_base_price':COLL_SUM((
 +        select element i.l_extendedprice
 +        from  l as i
-     )),'sum_disc_price':tpch.sum((
++    )),'sum_disc_price':COLL_SUM((
 +        select element (i.l_extendedprice * (1 - i.l_discount))
 +        from  l as i
-     )),'sum_charge':tpch.sum((
++    )),'sum_charge':COLL_SUM((
 +        select element (i.l_extendedprice * (1 - i.l_discount) * (1 + i.l_tax))
 +        from  l as i
-     )),'ave_qty':tpch.avg((
++    )),'ave_qty':COLL_AVG((
 +        select element i.l_quantity
 +        from  l as i
-     )),'ave_price':tpch.avg((
++    )),'ave_price':COLL_AVG((
 +        select element i.l_extendedprice
 +        from  l as i
-     )),'ave_disc':tpch.avg((
++    )),'ave_disc':COLL_AVG((
 +        select element i.l_discount
 +        from  l as i
-     )),'count_order':tpch.count(l)}
++    )),'count_order':COLL_COUNT(l)}
 +from  LineItem as l
 +where (l.l_shipdate <= '1998-09-02')
 +/* +hash */
 +group by l.l_returnflag as l_returnflag,l.l_linestatus as l_linestatus
 +order by l_returnflag,l_linestatus
 +;



Mime
View raw message