You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by dl...@apache.org on 2017/12/16 01:29:07 UTC
[06/10] asterixdb git commit: [ASTERIXDB-2170][SQL] Fix resolution
order of implicit field access
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
index 7463682..a82b05f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
@@ -23,14 +23,14 @@ USE tpcds;
SELECT i_item_id
,i_item_desc
,i_current_price
- FROM item, inventory, date_dim, store_sales
- WHERE i_current_price >= 30 AND i_current_price <= 30+30
- AND inv_item_sk = i_item_sk
- AND d_date_sk=inv_date_sk
- AND date(d_date) >= date('2002-05-30') AND date(d_date) <= date('2002-07-01')
- AND i_manufact_id in [437,129,727,663]
- AND inv_quantity_on_hand >= 100 AND inv_quantity_on_hand <= 500
- AND ss_item_sk = i_item_sk
- GROUP BY i_item_id,i_item_desc,i_current_price
+ FROM item i, inventory inv, date_dim d, store_sales ss
+ WHERE i.i_current_price >= 30 AND i.i_current_price <= 30+30
+ AND inv.inv_item_sk = i.i_item_sk
+ AND d.d_date_sk=inv.inv_date_sk
+ AND date(d.d_date) >= date('2002-05-30') AND date(d.d_date) <= date('2002-07-01')
+ AND i.i_manufact_id in [437,129,727,663]
+ AND inv.inv_quantity_on_hand >= 100 AND inv.inv_quantity_on_hand <= 500
+ AND ss.ss_item_sk = i.i_item_sk
+ GROUP BY i.i_item_id,i.i_item_desc,i.i_current_price
ORDER BY i_item_id
LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp
index 6330d9b..8ab3f25 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp
@@ -21,19 +21,19 @@
use tpcds;
select substr(r_reason_desc,1,20)
- ,avg(ws_quantity)
- ,avg(wr_refunded_cash)
- ,avg(wr_fee)
- from web_sales, web_returns, web_page, customer_demographics cd1,
- customer_demographics cd2, customer_address, date_dim, reason
- where ws_web_page_sk = wp_web_page_sk
- and ws_item_sk = wr_item_sk
- and ws_order_number = wr_order_number
- and ws_sold_date_sk = d_date_sk and d_year = 1998
- and cd1.cd_demo_sk = wr_refunded_cdemo_sk
- and cd2.cd_demo_sk = wr_returning_cdemo_sk
- and ca_address_sk = wr_refunded_addr_sk
- and r_reason_sk = wr_reason_sk
+ ,avg(ws.ws_quantity)
+ ,avg(wr.wr_refunded_cash)
+ ,avg(wr.wr_fee)
+ from web_sales ws, web_returns wr, web_page wp, customer_demographics cd1,
+ customer_demographics cd2, customer_address ca, date_dim d, reason r
+ where ws.ws_web_page_sk = wp.wp_web_page_sk
+ and ws.ws_item_sk = wr.wr_item_sk
+ and ws.ws_order_number = wr.wr_order_number
+ and ws.ws_sold_date_sk = d.d_date_sk and d.d_year = 1998
+ and cd1.cd_demo_sk = wr.wr_refunded_cdemo_sk
+ and cd2.cd_demo_sk = wr.wr_returning_cdemo_sk
+ and ca.ca_address_sk = wr.wr_refunded_addr_sk
+ and r.r_reason_sk = wr.wr_reason_sk
and
(
(
@@ -45,7 +45,7 @@ select substr(r_reason_desc,1,20)
and
cd1.cd_education_status = cd2.cd_education_status
and
- ws_sales_price >= 100.00 and ws_sales_price <= 150.00
+ ws.ws_sales_price >= 100.00 and ws.ws_sales_price <= 150.00
)
or
(
@@ -57,7 +57,7 @@ select substr(r_reason_desc,1,20)
and
cd1.cd_education_status = cd2.cd_education_status
and
- ws_sales_price >= 50.00 and ws_sales_price <= 100.00
+ ws.ws_sales_price >= 50.00 and ws.ws_sales_price <= 100.00
)
or
(
@@ -69,35 +69,35 @@ select substr(r_reason_desc,1,20)
and
cd1.cd_education_status = cd2.cd_education_status
and
- ws_sales_price >= 150.00 and ws_sales_price <= 200.00
+ ws.ws_sales_price >= 150.00 and ws.ws_sales_price <= 200.00
)
)
and
(
(
- ca_country = 'United States'
+ ca.ca_country = 'United States'
and
- ca_state in ['KY', 'GA', 'NM']
- and ws_net_profit >= 100 and ws_net_profit <= 200
+ ca.ca_state in ['KY', 'GA', 'NM']
+ and ws.ws_net_profit >= 100 and ws.ws_net_profit <= 200
)
or
(
- ca_country = 'United States'
+ ca.ca_country = 'United States'
and
- ca_state in ['MT', 'OR', 'IN']
- and ws_net_profit >= 150 and ws_net_profit <= 300
+ ca.ca_state in ['MT', 'OR', 'IN']
+ and ws.ws_net_profit >= 150 and ws.ws_net_profit <= 300
)
or
(
- ca_country = 'United States'
+ ca.ca_country = 'United States'
and
- ca_state in ['WI', 'MO', 'WV']
- and ws_net_profit >= 50 and ws_net_profit <= 250
+ ca.ca_state in ['WI', 'MO', 'WV']
+ and ws.ws_net_profit >= 50 and ws.ws_net_profit <= 250
)
)
-group by r_reason_desc
+group by r.r_reason_desc
order by substr(r_reason_desc,1,20)
- ,avg(ws_quantity)
- ,avg(wr_refunded_cash)
- ,avg(wr_fee)
+ ,avg(ws.ws_quantity)
+ ,avg(wr.wr_refunded_cash)
+ ,avg(wr.wr_fee)
limit 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q91/q91.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q91/q91.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q91/q91.3.query.sqlpp
index 9ce8ffe..29166c3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q91/q91.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q91/q91.3.query.sqlpp
@@ -24,27 +24,27 @@ SELECT
cc_call_center_id Call_Center,
cc_name Call_Center_Name,
cc_manager Manager,
- SUM(cr_net_loss) Returns_Loss
+ SUM(cr.cr_net_loss) Returns_Loss
FROM
- call_center,
- catalog_returns,
- date_dim,
- customer,
- customer_address,
- customer_demographics,
- household_demographics
+ call_center cc,
+ catalog_returns cr,
+ date_dim d,
+ customer c,
+ customer_address ca,
+ customer_demographics cd,
+ household_demographics hd
WHERE
- cr_call_center_sk = cc_call_center_sk
-AND cr_returned_date_sk = d_date_sk
-AND cr_returning_customer_sk= c_customer_sk
-AND cd_demo_sk = c_current_cdemo_sk
-AND hd_demo_sk = c_current_hdemo_sk
-AND ca_address_sk = c_current_addr_sk
-AND d_year = 1999
-AND d_moy = 11
-AND ( (cd_marital_status = 'M' AND cd_education_status = 'Unknown')
- OR (cd_marital_status = 'W' AND cd_education_status = 'Advanced Degree'))
-AND hd_buy_potential like '0-500%'
-AND ca_gmt_offset = -7
-GROUP BY cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status
-ORDER BY SUM(cr_net_loss) desc;
\ No newline at end of file
+ cr.cr_call_center_sk = cc.cc_call_center_sk
+AND cr.cr_returned_date_sk = d.d_date_sk
+AND cr.cr_returning_customer_sk= c.c_customer_sk
+AND cd.cd_demo_sk = c.c_current_cdemo_sk
+AND hd.hd_demo_sk = c.c_current_hdemo_sk
+AND ca.ca_address_sk = c.c_current_addr_sk
+AND d.d_year = 1999
+AND d.d_moy = 11
+AND ( (cd.cd_marital_status = 'M' AND cd.cd_education_status = 'Unknown')
+ OR (cd.cd_marital_status = 'W' AND cd.cd_education_status = 'Advanced Degree'))
+AND hd.hd_buy_potential like '0-500%'
+AND ca.ca_gmt_offset = -7
+GROUP BY cc.cc_call_center_id,cc.cc_name,cc.cc_manager,cd.cd_marital_status,cd.cd_education_status
+ORDER BY SUM(cr.cr_net_loss) desc;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q94/q94.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q94/q94.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q94/q94.3.query.sqlpp
index 9de99d7..c30002f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q94/q94.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q94/q94.3.query.sqlpp
@@ -22,24 +22,24 @@ USE tpcds;
SELECT
COUNT(wsnondup.ws_order_number) order_count,
- SUM(ws_ext_ship_cost) total_shipping_cost,
- SUM(ws_net_profit) total_net_profit
+ SUM(ws1.ws_ext_ship_cost) total_shipping_cost,
+ SUM(ws1.ws_net_profit) total_net_profit
FROM
(SELECT ws_order_number
FROM web_sales
GROUP BY ws_order_number) AS wsnondup,
web_sales as ws1,
- date_dim,
- customer_address,
- web_site
+ date_dim d,
+ customer_address ca,
+ web_site web
WHERE
- date(d_date) >= date('1999-05-01')
-AND date(d_date) <= date('1999-06-29')
-AND ws1.ws_ship_date_sk = d_date_sk
-AND ws1.ws_ship_addr_sk = ca_address_sk
-AND ca_state = 'TX'
-AND ws1.ws_web_site_sk = web_site_sk
-AND web_company_name = 'pri'
+ date(d.d_date) >= date('1999-05-01')
+AND date(d.d_date) <= date('1999-06-29')
+AND ws1.ws_ship_date_sk = d.d_date_sk
+AND ws1.ws_ship_addr_sk = ca.ca_address_sk
+AND ca.ca_state = 'TX'
+AND ws1.ws_web_site_sk = web.web_site_sk
+AND web.web_company_name = 'pri'
AND EXISTS (SELECT *
FROM web_sales ws2
WHERE ws1.ws_order_number = ws2.ws_order_number
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q95/q95.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q95/q95.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q95/q95.3.query.sqlpp
index e32122f..9e04780 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q95/q95.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q95/q95.3.query.sqlpp
@@ -32,27 +32,27 @@ WITH ws_wh AS
FROM
(
SELECT
- distinct ws_order_number
- ,ws_ext_ship_cost
- ,ws_net_profit
+ distinct ws1.ws_order_number
+ ,ws1.ws_ext_ship_cost
+ ,ws1.ws_net_profit
FROM
web_sales ws1
- ,date_dim
- ,customer_address
- ,web_site
+ ,date_dim d
+ ,customer_address ca
+ ,web_site web
WHERE
- date(d_date) >= date('1999-05-01')
-AND date(d_date) <= date('1999-06-30')
-AND ws1.ws_ship_date_sk = d_date_sk
-AND ws1.ws_ship_addr_sk = ca_address_sk
-AND ca_state = 'TX'
-AND ws1.ws_web_site_sk = web_site_sk
-AND web_company_name = 'pri'
-AND ws1.ws_order_number IN (SELECT VALUE ws_wh.ws_order_number
+ date(d.d_date) >= date('1999-05-01')
+AND date(d.d_date) <= date('1999-06-30')
+AND ws1.ws_ship_date_sk = d.d_date_sk
+AND ws1.ws_ship_addr_sk = ca.ca_address_sk
+AND ca.ca_state = 'TX'
+AND ws1.ws_web_site_sk = web.web_site_sk
+AND web.web_company_name = 'pri'
+AND ws1.ws_order_number IN (SELECT VALUE ws_order_number
FROM ws_wh)
-AND ws1.ws_order_number IN (SELECT VALUE wr_order_number
- FROM web_returns,ws_wh
- WHERE wr_order_number = ws_wh.ws_order_number)
+AND ws1.ws_order_number IN (SELECT VALUE wr.wr_order_number
+ FROM web_returns wr,ws_wh
+ WHERE wr.wr_order_number = ws_wh.ws_order_number)
) t1
ORDER BY COUNT(ws_order_number)
LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q96/q96.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q96/q96.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q96/q96.3.query.sqlpp
index 1fa8c67..b35f5c2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q96/q96.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q96/q96.3.query.sqlpp
@@ -26,9 +26,9 @@ FROM
FROM store_sales,
household_demographics,
time_dim, store
-WHERE ss_sold_time_sk = time_dim.t_time_sk
- AND ss_hdemo_sk = household_demographics.hd_demo_sk
- AND ss_store_sk = s_store_sk
+WHERE store_sales.ss_sold_time_sk = time_dim.t_time_sk
+ AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ AND store_sales.ss_store_sk = store.s_store_sk
AND time_dim.t_hour = 8
AND time_dim.t_minute >= 30
AND household_demographics.hd_dep_count = 5
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
index 99d81e0..17038de 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
@@ -33,35 +33,35 @@ FROM
i_class,
i_current_price,
i_item_id,
- SUM(ss_ext_sales_price) itemrevenue
+ SUM(ss.ss_ext_sales_price) itemrevenue
FROM
- store_sales,
- item,
- date_dim
+ store_sales ss,
+ item i,
+ date_dim d
WHERE
- ss_item_sk = i_item_sk
- AND i_category IN ["Jewelry", "Sports", "Books"]
- AND ss_sold_date_sk = d_date_sk
- AND date(d_date) >= date('2001-01-12')
- AND date(d_date) <= date('2001-02-11')
+ ss.ss_item_sk = i.i_item_sk
+ AND i.i_category IN ["Jewelry", "Sports", "Books"]
+ AND ss.ss_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) >= date('2001-01-12')
+ AND date(d.d_date) <= date('2001-02-11')
GROUP BY
- i_item_id,
- i_item_desc,
- i_category,
- i_class,
- i_current_price
+ i.i_item_id,
+ i.i_item_desc,
+ i.i_category,
+ i.i_class,
+ i.i_current_price
) AS currpricetable
JOIN
-(SELECT i_item_id, i_item_desc, i_category, i_class, SUM(ss_ext_sales_price) revrat
+(SELECT i_item_id, i_item_desc, i_category, i_class, SUM(ss.ss_ext_sales_price) revrat
FROM
- store_sales,
- item
- WHERE ss_item_sk = i_item_sk
+ store_sales ss,
+ item i
+ WHERE ss.ss_item_sk = i.i_item_sk
GROUP BY
- i_item_id,
- i_item_desc,
- i_category,
- i_class
+ i.i_item_id,
+ i.i_item_desc,
+ i.i_category,
+ i.i_class
) AS revrattable
ON currpricetable.i_item_id = revrattable.i_item_id
AND currpricetable.i_class = revrattable.i_class
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.3.query.sqlpp
index 95889bc..3a1b5c2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.3.query.sqlpp
@@ -33,7 +33,7 @@ with tab as
where d3.d_date in ['1900-01-02', '1900-01-12', '1900-01-15']
)
)
- group by i_item_id
+ group by item.i_item_id
)
select coll_count((
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 4823a39..a15ab14 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -41,6 +41,6 @@ from (
) as gco
group by gco.c_count as c_count
group as g
-let custdist = count(( select value gco from g ))
+let custdist = coll_count(( select value gco from g ))
order by custdist desc,c_count desc
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 3963de0..65293dc 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
@@ -78,6 +78,6 @@ from (
) as t4
group by t4.s_name as s_name
group as g
-let numwait = count(( from g select value t4 ))
+let numwait = coll_count(( from g select value t4 ))
order by numwait desc,s_name
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 3f3a50a..4689676 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
@@ -26,7 +26,7 @@
use tpch;
-select element {'l_linenumber':l.l_linenumber,'count_order': count((from g select value g))}
+select element {'l_linenumber':l.l_linenumber,'count_order': coll_count((from g select value g))}
from LineItem as l
group by l.l_linenumber
group as g
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate/nest_aggregate.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate/nest_aggregate.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate/nest_aggregate.3.query.sqlpp
index 6d0d928..ba3297f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate/nest_aggregate.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate/nest_aggregate.3.query.sqlpp
@@ -29,11 +29,11 @@ USE tpch;
SELECT Nation.n_nationkey AS nation_key,
Nation.n_name AS name,
(
- SELECT orderdate AS order_date, sum(o_totalprice) AS sum_price
- FROM Orders,
- Customer
- WHERE o_custkey = c_custkey AND c_nationkey = Nation.n_nationkey
- GROUP BY o_orderdate AS orderdate
+ SELECT orderdate AS order_date, sum(o.o_totalprice) AS sum_price
+ FROM Orders o,
+ Customer c
+ WHERE o.o_custkey = c.c_custkey AND c.c_nationkey = Nation.n_nationkey
+ GROUP BY o.o_orderdate AS orderdate
ORDER BY sum_price
LIMIT 3
) AS aggregates
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate2/nest_aggregate2.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate2/nest_aggregate2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate2/nest_aggregate2.3.query.sqlpp
index 4a4cfc5..ae1dd27 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate2/nest_aggregate2.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/nest_aggregate2/nest_aggregate2.3.query.sqlpp
@@ -31,11 +31,11 @@ SELECT Nation.n_nationkey AS nation_key,
Nation.n_name AS name,
(
SELECT ELEMENT orderdate
- FROM Orders,
- Customer
- WHERE o_custkey = c_custkey AND c_nationkey = Nation.n_nationkey
- GROUP BY o_orderdate AS orderdate
- ORDER BY sum(o_totalprice)
+ FROM Orders o,
+ Customer c
+ WHERE o.o_custkey = c.c_custkey AND c.c_nationkey = Nation.n_nationkey
+ GROUP BY o.o_orderdate AS orderdate
+ ORDER BY sum(o.o_totalprice)
LIMIT 3
) AS aggregates
from Nation,
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
index e1704fe..1decc72 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q02_minimum_cost_supplier/q02_minimum_cost_supplier.3.query.sqlpp
@@ -22,11 +22,11 @@ USE tpch;
WITH q2_minimum_cost_supplier_tmp1 AS
(
- SELECT s_acctbal, s_name, n_name, p_partkey, ps_supplycost, p_mfgr, s_address, s_phone, s_comment
- FROM Nation JOIN Region ON n_regionkey = r_regionkey and r_name = 'EUROPE'
- JOIN Supplier ON s_nationkey = n_nationkey
- JOIN Partsupp ON s_suppkey = ps_suppkey
- JOIN Part ON p_partkey = ps_partkey AND p_type LIKE '%BRASS'
+ SELECT s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
+ FROM Nation n JOIN Region r ON n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
+ JOIN Supplier s ON s.s_nationkey = n.n_nationkey
+ JOIN Partsupp ps ON s.s_suppkey = ps.ps_suppkey
+ JOIN Part p ON p.p_partkey = ps.ps_partkey AND p.p_type LIKE '%BRASS'
),
q2_minimum_cost_supplier_tmp2 AS
(
@@ -35,10 +35,10 @@ q2_minimum_cost_supplier_tmp2 AS
GROUP BY p_partkey
)
-SELECT s_acctbal, s_name, n_name, t1.p_partkey, p_mfgr, s_address, s_phone, s_comment
+SELECT t1.s_acctbal, t1.s_name, t1.n_name, t1.p_partkey, t1.p_mfgr, t1.s_address, t1.s_phone, t1.s_comment
FROM
q2_minimum_cost_supplier_tmp1 t1 JOIN q2_minimum_cost_supplier_tmp2 t2
ON
- t1.p_partkey = t2.p_partkey AND ps_supplycost=ps_min_supplycost
-ORDER BY s_acctbal DESC, n_name, s_name, t1.p_partkey
+ t1.p_partkey = t2.p_partkey AND t1.ps_supplycost = t2.ps_min_supplycost
+ORDER BY t1.s_acctbal DESC, t1.n_name, t1.s_name, t1.p_partkey
LIMIT 100;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
index c620bdc..f5dcb4a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q03_shipping_priority_nt/q03_shipping_priority_nt.3.query.sqlpp
@@ -21,15 +21,15 @@ USE tpch;
SELECT l_orderkey,
- sum(l_extendedprice * (1 - l_discount)) AS revenue,
+ sum(l.l_extendedprice * (1 - l.l_discount)) AS revenue,
o_orderdate,
o_shippriority
-FROM Customer,
- Orders,
- LineItem
-where c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < '1995-03-15' AND l_shipdate > '1995-03-15'
+FROM Customer c,
+ Orders o,
+ LineItem l
+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_orderkey, o_orderdate, o_shippriority
+GROUP BY l.l_orderkey, o.o_orderdate, o.o_shippriority
ORDER BY revenue DESC,o_orderdate
LIMIT 10
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q04_order_priority/q04_order_priority.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q04_order_priority/q04_order_priority.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q04_order_priority/q04_order_priority.3.query.sqlpp
index e2e486d..c9457d5 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q04_order_priority/q04_order_priority.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q04_order_priority/q04_order_priority.3.query.sqlpp
@@ -27,11 +27,11 @@ WITH tmp AS
WHERE l_commitdate < l_receiptdate
)
-SELECT o_orderpriority AS order_priority, count(Orders) AS count
-FROM Orders
-JOIN tmp
-ON l_orderkey = o_orderkey
-WHERE o_orderdate >= '1993-07-01' AND o_orderdate < '1993-10-01'
-GROUP BY o_orderpriority
+SELECT o_orderpriority AS order_priority, count(o) AS count
+FROM Orders o
+JOIN tmp t
+ON t.l_orderkey = o.o_orderkey
+WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01'
+GROUP BY o.o_orderpriority
ORDER BY o_orderpriority
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
index dce8e76..921810b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q05_local_supplier_volume/q05_local_supplier_volume.3.query.sqlpp
@@ -19,24 +19,24 @@
USE tpch;
-SELECT n_name, sum(l_extendedprice * (1 - l_discount)) AS revenue
+SELECT n_name, sum(o1.l_extendedprice * (1 - o1.l_discount)) AS revenue
FROM
- Customer JOIN
- ( SELECT n_name, l_extendedprice, l_discount, s_nationkey, o_custkey
- FROM Orders JOIN
- ( SELECT n_name, l_extendedprice, l_discount, l_orderkey, s_nationkey
- FROM LineItem JOIN
- ( SELECT n_name, s_suppkey, s_nationkey
- FROM Supplier JOIN
- ( SELECT n_name, n_nationkey
+ Customer c JOIN
+ ( SELECT l1.n_name, l1.l_extendedprice, l1.l_discount, l1.s_nationkey, o.o_custkey
+ FROM Orders o JOIN
+ ( SELECT s1.n_name, l.l_extendedprice, l.l_discount, l.l_orderkey, s1.s_nationkey
+ FROM LineItem l JOIN
+ ( SELECT n1.n_name, s.s_suppkey, s.s_nationkey
+ FROM Supplier s JOIN
+ ( SELECT n.n_name, n.n_nationkey
FROM Nation n JOIN Region r
- ON n_regionkey = r_regionkey
- ) n1 ON s_nationkey = n_nationkey
+ ON n.n_regionkey = r.r_regionkey
+ ) n1 ON s.s_nationkey = n1.n_nationkey
) s1
- ON l_suppkey = s_suppkey
- ) l1 ON l_orderkey = o_orderkey AND o_orderdate >= '1990-01-01'
- AND o_orderdate < '1995-01-01'
+ ON l.l_suppkey = s1.s_suppkey
+ ) l1 ON l1.l_orderkey = o.o_orderkey AND o.o_orderdate >= '1990-01-01'
+ AND o.o_orderdate < '1995-01-01'
) o1
-ON c_nationkey = s_nationkey AND c_custkey = o_custkey
-GROUP BY n_name
+ON c.c_nationkey = o1.s_nationkey AND c.c_custkey = o1.o_custkey
+GROUP BY o1.n_name
ORDER BY revenue DESC;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
index 0016fad..4166d0c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q07_volume_shipping/q07_volume_shipping.3.query.sqlpp
@@ -34,21 +34,21 @@ WITH q7_volume_shipping_tmp AS
SELECT supp_nation, cust_nation, l_year, sum(volume) AS revenue
FROM
(
- SELECT supp_nation, cust_nation,
- get_year(l_shipdate) AS l_year,
- l_extendedprice * (1 - l_discount) AS volume
+ SELECT t.supp_nation, t.cust_nation,
+ get_year(l3.l_shipdate) AS l_year,
+ l3.l_extendedprice * (1 - l3.l_discount) AS volume
FROM
q7_volume_shipping_tmp t JOIN
- (SELECT l_shipdate, l_extendedprice, l_discount, c_nationkey, s_nationkey
- FROM Supplier JOIN
- (SELECT l_shipdate, l_extendedprice, l_discount, l_suppkey, c_nationkey
- FROM Customer JOIN
- (SELECT l_shipdate, l_extendedprice, l_discount, l_suppkey, o_custkey
- FROM Orders
- JOIN LineItem ON o_orderkey = l_orderkey AND l_shipdate >= '1992-01-01'
- AND l_shipdate <= '1996-12-31'
- ) l1 ON c_custkey = o_custkey
- ) l2 ON s_suppkey = l_suppkey
+ (SELECT l2.l_shipdate, l2.l_extendedprice, l2.l_discount, l2.c_nationkey, s.s_nationkey
+ FROM Supplier s JOIN
+ (SELECT l1.l_shipdate, l1.l_extendedprice, l1.l_discount, l1.l_suppkey, c.c_nationkey
+ FROM Customer c JOIN
+ (SELECT l.l_shipdate, l.l_extendedprice, l.l_discount, l.l_suppkey, o.o_custkey
+ FROM Orders o
+ JOIN LineItem l ON o.o_orderkey = l.l_orderkey AND l.l_shipdate >= '1992-01-01'
+ AND l.l_shipdate <= '1996-12-31'
+ ) l1 ON c.c_custkey = l1.o_custkey
+ ) l2 ON s.s_suppkey = l2.l_suppkey
) l3 ON t.c_nationkey = l3.c_nationkey AND t.s_nationkey = l3.s_nationkey
) shipping
GROUP BY supp_nation, cust_nation, l_year
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q08_national_market_share/q08_national_market_share.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q08_national_market_share/q08_national_market_share.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q08_national_market_share/q08_national_market_share.3.query.sqlpp
index ce91102..704ea1b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q08_national_market_share/q08_national_market_share.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q08_national_market_share/q08_national_market_share.3.query.sqlpp
@@ -24,43 +24,43 @@ SELECT year,
SUM( CASE s_name = 'BRAZIL' WHEN true THEN revenue ELSE 0.0 END ) / SUM(revenue) AS mkt_share
FROM (
SELECT o_year AS year,
- l_extendedprice * (1 - l_discount) AS revenue,
- n_name AS s_name
+ slnrcop.l_extendedprice * (1 - slnrcop.l_discount) AS revenue,
+ n.n_name AS s_name
FROM (
- SELECT o_orderdate, l_discount, l_extendedprice, l_suppkey, s_nationkey
- FROM Supplier,
+ SELECT lnrcop.o_orderdate, lnrcop.l_discount, lnrcop.l_extendedprice, lnrcop.l_suppkey, s.s_nationkey
+ FROM Supplier s,
(
- SELECT o_orderdate, l_discount, l_extendedprice, l_suppkey
+ SELECT lnrco.o_orderdate, lnrco.l_discount, lnrco.l_extendedprice, lnrco.l_suppkey
FROM (
- SELECT o_orderdate, l_partkey, l_discount, l_extendedprice, l_suppkey
- FROM LineItem,
+ SELECT nrco.o_orderdate, l.l_partkey, l.l_discount, l.l_extendedprice, l.l_suppkey
+ FROM LineItem l,
(
- SELECT o_orderdate, o_orderkey
- FROM Orders,
+ SELECT o.o_orderdate, o.o_orderkey
+ FROM Orders o,
(
- SELECT c_custkey
- FROM Customer,
+ SELECT c.c_custkey
+ FROM Customer c,
(
- SELECT n_nationkey
- FROM Nation,
- Region
- WHERE n_regionkey = r_regionkey AND r_name = 'AMERICA'
+ SELECT n.n_nationkey
+ FROM Nation n,
+ Region r
+ WHERE n.n_regionkey = r.r_regionkey AND r.r_name = 'AMERICA'
) AS nr
- WHERE c_nationkey = n_nationkey
+ WHERE c.c_nationkey = nr.n_nationkey
) AS nrc
- WHERE c_custkey = o_custkey
+ WHERE nrc.c_custkey = o.o_custkey
) AS nrco
- WHERE l_orderkey = o_orderkey and o_orderdate >= '1995-01-01' and o_orderdate < '1996-12-31'
+ WHERE l.l_orderkey = nrco.o_orderkey and nrco.o_orderdate >= '1995-01-01' and nrco.o_orderdate < '1996-12-31'
) AS lnrco,
- Part
- WHERE p_partkey = l_partkey and p_type = 'ECONOMY ANODIZED STEEL'
+ Part p
+ WHERE p.p_partkey = lnrco.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL'
) AS lnrcop
- WHERE s_suppkey = l_suppkey
- ) AS slnrcop,
- Nation
- LET o_year = GET_YEAR(o_orderdate)
- WHERE s_nationkey = n_nationkey
- ) as t
+ WHERE s.s_suppkey = lnrcop.l_suppkey
+ ) AS slnrcop,
+ Nation n
+ LET o_year = GET_YEAR(slnrcop.o_orderdate)
+ WHERE slnrcop.s_nationkey = n.n_nationkey
+ ) as t
GROUP BY year
ORDER BY year
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sql-sugar/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp
index bcb3687..2e3bf7d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_nt/q09_product_type_profit_nt.3.query.sqlpp
@@ -23,24 +23,24 @@ USE tpch;
SELECT nation, o_year, sum(amount) AS sum_profit
FROM
(
- SELECT n_name AS nation,
- get_year(o_orderdate) AS o_year,
- l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
+ SELECT l3.n_name AS nation,
+ get_year(o.o_orderdate) AS o_year,
+ l3.l_extendedprice * (1 - l3.l_discount) - l3.ps_supplycost * l3.l_quantity AS amount
FROM
- Orders JOIN
+ Orders o JOIN
(
- SELECT l_extendedprice, l_discount, l_quantity, l_orderkey, n_name, ps_supplycost
- FROM Part JOIN
- (SELECT l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name, ps_supplycost
- FROM Partsupp join
- (SELECT l_suppkey, l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name
+ SELECT l2.l_extendedprice, l2.l_discount, l2.l_quantity, l2.l_orderkey, l2.n_name, l2.ps_supplycost
+ FROM Part p JOIN
+ (SELECT l1.l_extendedprice, l1.l_discount, l1.l_quantity, l1.l_partkey, l1.l_orderkey, l1.n_name, ps.ps_supplycost
+ FROM Partsupp ps join
+ (SELECT l.l_suppkey, l.l_extendedprice, l.l_discount, l.l_quantity, l.l_partkey, l.l_orderkey, s1.n_name
FROM
- (SELECT s_suppkey, n_name
- FROM Nation JOIN Supplier ON n_nationkey = s_nationkey
- ) s1 JOIN LineItem ON s_suppkey = l_suppkey
- ) l1 ON ps_suppkey = l_suppkey AND ps_partkey = l_partkey
- ) l2 ON contains(p_name,'green') AND p_partkey = l_partkey
- ) l3 ON o_orderkey = l_orderkey
+ (SELECT s.s_suppkey, n.n_name
+ FROM Nation n JOIN Supplier s ON n.n_nationkey = s.s_nationkey
+ ) s1 JOIN LineItem l ON s1.s_suppkey = l.l_suppkey
+ ) l1 ON ps.ps_suppkey = l1.l_suppkey AND ps.ps_partkey = l1.l_partkey
+ ) l2 ON contains(p.p_name,'green') AND p.p_partkey = l2.l_partkey
+ ) l3 ON o.o_orderkey = l3.l_orderkey
) profit
GROUP BY nation, o_year
ORDER BY nation, o_year desc;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_parameter/q09_product_type_profit_parameter.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_parameter/q09_product_type_profit_parameter.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_parameter/q09_product_type_profit_parameter.3.query.sqlpp
index d7ab8ec..9a8c03c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_parameter/q09_product_type_profit_parameter.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q09_product_type_profit_parameter/q09_product_type_profit_parameter.3.query.sqlpp
@@ -24,24 +24,24 @@ SET `compiler.joinmemory` "-32GB";
SELECT nation, o_year, sum(amount) AS sum_profit
FROM
(
- SELECT n_name AS nation,
- get_year(o_orderdate) AS o_year,
- l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
+ SELECT l3.n_name AS nation,
+ get_year(o.o_orderdate) AS o_year,
+ l3.l_extendedprice * (1 - l3.l_discount) - l3.ps_supplycost * l3.l_quantity AS amount
FROM
- Orders JOIN
+ Orders o JOIN
(
- SELECT l_extendedprice, l_discount, l_quantity, l_orderkey, n_name, ps_supplycost
- FROM Part JOIN
- (SELECT l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name, ps_supplycost
- FROM Partsupp join
- (SELECT l_suppkey, l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name
+ SELECT l2.l_extendedprice, l2.l_discount, l2.l_quantity, l2.l_orderkey, l2.n_name, l2.ps_supplycost
+ FROM Part p JOIN
+ (SELECT l1.l_extendedprice, l1.l_discount, l1.l_quantity, l1.l_partkey, l1.l_orderkey, l1.n_name, ps.ps_supplycost
+ FROM Partsupp ps join
+ (SELECT l.l_suppkey, l.l_extendedprice, l.l_discount, l.l_quantity, l.l_partkey, l.l_orderkey, s1.n_name
FROM
- (SELECT s_suppkey, n_name
- FROM Nation JOIN Supplier ON n_nationkey = s_nationkey
- ) s1 JOIN LineItem ON s_suppkey = l_suppkey
- ) l1 ON ps_suppkey = l_suppkey AND ps_partkey = l_partkey
- ) l2 ON contains(p_name,'green') AND p_partkey = l_partkey
- ) l3 ON o_orderkey = l_orderkey
+ (SELECT s.s_suppkey, n.n_name
+ FROM Nation n JOIN Supplier s ON n.n_nationkey = s.s_nationkey
+ ) s1 JOIN LineItem l ON s1.s_suppkey = l.l_suppkey
+ ) l1 ON ps.ps_suppkey = l1.l_suppkey AND ps.ps_partkey = l1.l_partkey
+ ) l2 ON contains(p.p_name,'green') AND p.p_partkey = l2.l_partkey
+ ) l3 ON o.o_orderkey = l3.l_orderkey
) profit
GROUP BY nation, o_year
ORDER BY nation, o_year desc;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item/q10_returned_item.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item/q10_returned_item.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item/q10_returned_item.3.query.sqlpp
index ef499de..f2f15ae 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item/q10_returned_item.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item/q10_returned_item.3.query.sqlpp
@@ -24,17 +24,17 @@ SELECT c_custkey, c_name,
sum(l_extendedprice * (1 - l_discount)) AS revenue,
c_acctbal, n_name, c_address, c_phone, c_comment
FROM (
- SELECT c_custkey, c_name, c_acctbal, n_name, c_address, c_phone, c_comment, l_extendedprice, l_discount
- FROM LineItem,
+ SELECT ocn.c_custkey, ocn.c_name, ocn.c_acctbal, ocn.n_name, ocn.c_address, ocn.c_phone, ocn.c_comment, l.l_extendedprice, l.l_discount
+ FROM LineItem l,
(
- SELECT c_custkey, c_name, c_acctbal, n_name, c_address, c_phone, c_comment, o_orderkey
- from Orders,
- Customer,
- Nation
- WHERE c_custkey = o_custkey AND o_orderdate >= '1993-10-01'
- AND o_orderdate < '1994-01-01' AND c_nationkey = n_nationkey
+ SELECT c.c_custkey, c.c_name, c.c_acctbal, n.n_name, c.c_address, c.c_phone, c.c_comment, o.o_orderkey
+ from Orders o,
+ Customer c,
+ Nation 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_orderkey = o_orderkey and l_returnflag = 'R'
+ WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R'
) AS locn
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
ORDER BY revenue DESC
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
index bd07d48..846954c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q10_returned_item_int64/q10_returned_item_int64.3.query.sqlpp
@@ -25,17 +25,17 @@ SELECT c_custkey, c_name,
sum(l_extendedprice * (1 - l_discount)) AS revenue,
c_acctbal, n_name, c_address, c_phone, c_comment
FROM (
- SELECT c_custkey, c_name, c_acctbal, n_name, c_address, c_phone, c_comment, l_extendedprice, l_discount
- FROM LineItem,
+ SELECT ocn.c_custkey, ocn.c_name, ocn.c_acctbal, ocn.n_name, ocn.c_address, ocn.c_phone, ocn.c_comment, l.l_extendedprice, l.l_discount
+ FROM LineItem l,
(
- SELECT c_custkey, c_name, c_acctbal, n_name, c_address, c_phone, c_comment, o_orderkey
- from Orders,
- Customer,
- Nation
- WHERE c_custkey = o_custkey AND o_orderdate >= '1993-10-01'
- AND o_orderdate < '1994-01-01' AND c_nationkey = n_nationkey
+ SELECT c.c_custkey, c.c_name, c.c_acctbal, n.n_name, c.c_address, c.c_phone, c.c_comment, o.o_orderkey
+ from Orders o,
+ Customer c,
+ Nation 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_orderkey = o_orderkey and l_returnflag = 'R'
+ WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R'
) AS locn
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
ORDER BY revenue DESC
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q11_important_stock/q11_important_stock.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q11_important_stock/q11_important_stock.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q11_important_stock/q11_important_stock.3.query.sqlpp
index b3a08e8..1b1c05a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q11_important_stock/q11_important_stock.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q11_important_stock/q11_important_stock.3.query.sqlpp
@@ -21,30 +21,30 @@ use tpch;
WITH sum as (
- SELECT ELEMENT SUM(ps_supplycost * ps_availqty)
- FROM Partsupp,
+ SELECT ELEMENT SUM(ps.ps_supplycost * ps.ps_availqty)
+ FROM Partsupp ps,
(
- SELECT s_suppkey
- FROM Supplier,
- Nation
- WHERE s_nationkey = n_nationkey
+ SELECT s.s_suppkey
+ FROM Supplier s,
+ Nation n
+ WHERE s.s_nationkey = n.n_nationkey
) AS sn
- WHERE ps_suppkey = s_suppkey
+ WHERE ps.ps_suppkey = sn.s_suppkey
)[0]
SELECT ps_partkey AS partkey, part_value
FROM (
- SELECT ps_partkey,
- sum(ps_supplycost * ps_availqty) AS part_value
- FROM Partsupp,
+ SELECT ps.ps_partkey,
+ sum(ps.ps_supplycost * ps.ps_availqty) AS part_value
+ FROM Partsupp ps,
(
- SELECT s_suppkey
- FROM Supplier,
- Nation
- WHERE s_nationkey = n_nationkey
+ SELECT s.s_suppkey
+ FROM Supplier s,
+ Nation n
+ WHERE s.s_nationkey = n.n_nationkey
) sn
- WHERE ps_suppkey = s_suppkey
- GROUP BY ps_partkey
+ WHERE ps.ps_suppkey = sn.s_suppkey
+ GROUP BY ps.ps_partkey
) t1
WHERE part_value > sum * 0.00001
ORDER BY part_value DESC
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping/q12_shipping.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping/q12_shipping.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping/q12_shipping.3.query.sqlpp
index 5d0378a..b1fdff2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping/q12_shipping.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping/q12_shipping.3.query.sqlpp
@@ -21,13 +21,13 @@ USE tpch;
SELECT l_shipmode,
- sum(CASE WHEN o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count,
- sum(CASE o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' WHEN true THEN 0 ELSE 1 END) low_line_count
-FROM LineItem,
- Orders
-WHERE o_orderkey = l_orderkey AND l_commitdate < l_receiptdate AND
- l_shipdate < l_commitdate AND l_receiptdate >= '1994-01-01' AND
- l_receiptdate < '1995-01-01' AND (l_shipmode = 'MAIL' OR l_shipmode = 'SHIP')
-GROUP BY l_shipmode
+ sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count,
+ sum(CASE o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' WHEN true THEN 0 ELSE 1 END) low_line_count
+FROM LineItem l,
+ Orders 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
ORDER BY l_shipmode
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping_broadcast/q12_shipping_broadcast.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping_broadcast/q12_shipping_broadcast.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping_broadcast/q12_shipping_broadcast.3.query.sqlpp
index 75b359c..28c9a9b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping_broadcast/q12_shipping_broadcast.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q12_shipping_broadcast/q12_shipping_broadcast.3.query.sqlpp
@@ -21,13 +21,13 @@ USE tpch;
SELECT l_shipmode,
- sum(CASE WHEN o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count,
- sum(CASE o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' WHEN true THEN 0 ELSE 1 END) low_line_count
-FROM LineItem,
- Orders
-WHERE l_orderkey /*+ bcast */ = o_orderkey AND l_commitdate < l_receiptdate AND
- l_shipdate < l_commitdate AND l_receiptdate >= '1994-01-01' AND
- l_receiptdate < '1995-01-01' AND (l_shipmode = 'MAIL' OR l_shipmode = 'SHIP')
-GROUP BY l_shipmode
+ sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count,
+ sum(CASE o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' WHEN true THEN 0 ELSE 1 END) low_line_count
+FROM LineItem l,
+ Orders o
+WHERE l.l_orderkey /*+ bcast */ = o.o_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
ORDER BY l_shipmode
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index bd5f431..f8211c9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -28,10 +28,10 @@ FROM (
(
select element o_orderkey
from Orders
- where c_custkey = o_custkey and o_comment NOT LIKE '%special%requests%'
+ where c.c_custkey = o_custkey and o_comment NOT LIKE '%special%requests%'
)
) AS o_orderkey_count
- from Customer
+ from Customer c
) co
GROUP BY c_custkey
) gco
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
index d359369..f6b80fc8 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q14_promotion_effect/q14_promotion_effect.3.query.sqlpp
@@ -21,9 +21,9 @@ use tpch;
SELECT ELEMENT 100.0 *
- SUM( CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0.0 END )
- / SUM(l_extendedprice * (1 - l_discount))
-FROM LineItem,
- Part
-WHERE l_partkey = p_partkey AND l_shipdate >= '1995-09-01' AND l_shipdate < '1995-10-01'
+ SUM( CASE WHEN p.p_type LIKE 'PROMO%' THEN l.l_extendedprice * (1 - l.l_discount) ELSE 0.0 END )
+ / SUM(l.l_extendedprice * (1 - l.l_discount))
+FROM LineItem l,
+ Part p
+WHERE l.l_partkey = p.p_partkey AND l.l_shipdate >= '1995-09-01' AND l.l_shipdate < '1995-10-01'
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q15_top_supplier/q15_top_supplier.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q15_top_supplier/q15_top_supplier.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q15_top_supplier/q15_top_supplier.3.query.sqlpp
index 95a360e..ba35a74 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q15_top_supplier/q15_top_supplier.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q15_top_supplier/q15_top_supplier.3.query.sqlpp
@@ -33,13 +33,13 @@ m AS (
FROM revenue
)[0]
-SELECT s_suppkey,
- s_name,
- s_address,
- s_phone,
- total_revenue
-FROM Supplier,
- revenue
-WHERE s_suppkey = supplier_no AND total_revenue < m + 0.000000001
- AND total_revenue > m - 0.000000001
+SELECT s.s_suppkey,
+ s.s_name,
+ s.s_address,
+ s.s_phone,
+ r.total_revenue
+FROM Supplier s,
+ revenue 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/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
index 5d9eef3..f253f86 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.sqlpp
@@ -22,16 +22,16 @@ USE tpch;
WITH tmp AS
(
- SELECT p_brand, p_type, p_size, ps_suppkey
+ SELECT psp.p_brand, psp.p_type, psp.p_size, psp.ps_suppkey
FROM (
- SELECT p_brand, p_type, p_size, ps_suppkey
- FROM Partsupp,
- Part
- WHERE p_partkey = ps_partkey AND p_brand != 'Brand#45' AND
- p_type NOT LIKE 'MEDIUM POLISHED%'
+ SELECT p.p_brand, p.p_type, p.p_size, ps.ps_suppkey
+ FROM Partsupp ps,
+ Part p
+ WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND
+ p.p_type NOT LIKE 'MEDIUM POLISHED%'
) AS psp,
- Supplier
- WHERE ps_suppkey = s_suppkey AND s_comment NOT LIKE '%Customer%Complaints%'
+ Supplier s
+ WHERE psp.ps_suppkey = s.s_suppkey AND s.s_comment NOT LIKE '%Customer%Complaints%'
)
SELECT p_brand, p_type, p_size, count(ps_suppkey) supplier_cnt
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/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-sugar/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
index e4f3299..569352f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.sqlpp
@@ -27,10 +27,10 @@ WITH tmp AS
GROUP BY l_partkey
)
-SELECT ELEMENT SUM(l_extendedprice) / 7.0
-FROM tmp,
- LineItem,
- Part
-WHERE p_partkey = l_partkey AND p_container = 'MED BOX'
- AND l_partkey = t_partkey AND l_quantity < t_avg_quantity
+SELECT ELEMENT SUM(l.l_extendedprice) / 7.0
+FROM tmp t,
+ LineItem l,
+ Part 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/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
index 4f0561c..2b12cfd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q18_large_volume_customer/q18_large_volume_customer.3.query.sqlpp
@@ -27,14 +27,14 @@ WITH tmp AS
)
SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
- sum(l_quantity) sum_quantity
-FROM Customer,
- Orders,
+ sum(l.l_quantity) sum_quantity
+FROM Customer c,
+ Orders o,
tmp t,
LineItem l
-WHERE c_custkey = o_custkey AND o_orderkey = t.l_orderkey
- AND t.l_orderkey = l.l_orderkey AND t_sum_quantity > 30
-GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+WHERE c.c_custkey = o.o_custkey AND o.o_orderkey = t.l_orderkey
+ AND t.l_orderkey = l.l_orderkey AND t.t_sum_quantity > 30
+GROUP BY c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice
ORDER BY o_totalprice DESC,o_orderdate
LIMIT 100
;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
index 318e939..61f065a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q19_discounted_revenue/q19_discounted_revenue.3.query.sqlpp
@@ -21,35 +21,35 @@ USE tpch;
set `import-private-functions` `true`;
-SELECT ELEMENT SUM(l_extendedprice * (1 - l_discount))
-FROM LineItem
-JOIN Part
-ON p_partkey = l_partkey
+SELECT ELEMENT SUM(l.l_extendedprice * (1 - l.l_discount))
+FROM LineItem l
+JOIN Part p
+ON p.p_partkey = l.l_partkey
WHERE
(
- p_brand = 'Brand#12'
- AND regexp_contains(p_container, 'SM CASE||SM BOX||SM PACK||SM PKG')
- AND l_quantity >= 1 and l_quantity <= 11
- AND p_size >= 1 and p_size <= 5
- AND regexp_contains(l_shipmode, 'AIR||AIR REG')
- AND l_shipinstruct = 'DELIVER IN PERSON'
+ p.p_brand = 'Brand#12'
+ AND regexp_contains(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 regexp_contains(l.l_shipmode, 'AIR||AIR REG')
+ AND l.l_shipinstruct = 'DELIVER IN PERSON'
)
OR
(
- p_brand = 'Brand#23'
- AND regexp_contains(p_container, 'MED BAG||MED BOX||MED PKG||MED PACK')
- AND l_quantity >= 10 and l_quantity <= 20
- AND p_size >= 1 and p_size <= 10
- AND regexp_contains(l_shipmode, 'AIR||AIR REG')
- AND l_shipinstruct = 'DELIVER IN PERSON'
+ p.p_brand = 'Brand#23'
+ AND regexp_contains(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 regexp_contains(l.l_shipmode, 'AIR||AIR REG')
+ AND l.l_shipinstruct = 'DELIVER IN PERSON'
)
OR
(
- p_brand = 'Brand#34'
- AND regexp_contains(p_container, 'LG CASE||LG BOX||LG PACK||LG PKG')
- AND l_quantity >= 20 and l_quantity <= 30
- AND p_size >= 1 and p_size <= 15
- AND regexp_contains(l_shipmode, 'AIR||AIR REG')
- AND l_shipinstruct = 'DELIVER IN PERSON'
+ p.p_brand = 'Brand#34'
+ AND regexp_contains(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 regexp_contains(l.l_shipmode, 'AIR||AIR REG')
+ AND l.l_shipinstruct = 'DELIVER IN PERSON'
)
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
index 2c138e8..0d28df3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/q20_potential_part_promotion/q20_potential_part_promotion.3.query.sqlpp
@@ -34,12 +34,12 @@ q20_tmp2 AS
,
q20_tmp3 AS
(
- SELECT ps_suppkey, ps_availqty, sum_quantity
- FROM Partsupp
- JOIN q20_tmp1
- ON ps_partkey = p_partkey
- JOIN q20_tmp2
- ON ps_partkey = l_partkey and ps_suppkey = l_suppkey
+ SELECT ps.ps_suppkey, ps.ps_availqty, t2.sum_quantity
+ FROM Partsupp ps
+ JOIN q20_tmp1 t1
+ ON ps.ps_partkey = t1.p_partkey
+ JOIN q20_tmp2 t2
+ ON ps.ps_partkey = t2.l_partkey and ps.ps_suppkey = t2.l_suppkey
)
,
q20_tmp4 AS
@@ -50,10 +50,10 @@ q20_tmp4 AS
GROUP BY ps_suppkey
)
-SELECT s_name, s_address
-FROM Supplier
-JOIN Nation
-ON s_nationkey = n_nationkey
-JOIN q20_tmp4
-ON s_suppkey = ps_suppkey
-ORDER BY s_name;
+SELECT s.s_name, s.s_address
+FROM Supplier s
+JOIN Nation n
+ON s.s_nationkey = n.n_nationkey
+JOIN q20_tmp4 t4
+ON s.s_suppkey = t4.ps_suppkey
+ORDER BY s.s_name;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/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-sugar/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
index 62a6d9d..13ea032 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/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-sugar/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
@@ -48,22 +48,22 @@ tmp2 AS
SELECT s_name, count(t4) AS numwait
FROM (
- SELECT s_name, l_suppkey, t2.l_orderkey, count_suppkey, max_suppkey
+ SELECT t3.s_name, t3.l_suppkey, t2.l_orderkey, t2.count_suppkey, t2.max_suppkey
FROM (
- SELECT s_name, t1.l_orderkey AS l_orderkey, l_suppkey
+ SELECT ns.s_name, t1.l_orderkey AS l_orderkey, l.l_suppkey
FROM LineItem l,
(
- SELECT s_name, s_suppkey
- FROM Nation,
- Supplier
- WHERE s_nationkey = n_nationkey
+ SELECT s.s_name, s.s_suppkey
+ FROM Nation n,
+ Supplier s
+ WHERE s.s_nationkey = n.n_nationkey
) AS ns,
- Orders,
+ Orders o,
tmp1 AS t1
- WHERE s_suppkey = l_suppkey AND l_receiptdate > l_commitdate
- AND o_orderkey = t1.l_orderkey AND l.l_orderkey = t1.l_orderkey
+ WHERE ns.s_suppkey = l.l_suppkey AND l.l_receiptdate > l.l_commitdate
+ AND o.o_orderkey = t1.l_orderkey AND l.l_orderkey = t1.l_orderkey
) AS t3
- JOIN tmp2 AS t2 ON count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
+ JOIN tmp2 AS t2 ON t2.count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
) AS t4
GROUP BY s_name
ORDER BY numwait DESC, s_name
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue562/query-issue562.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue562/query-issue562.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue562/query-issue562.3.query.sqlpp
index afd3db5..4a23a50 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue562/query-issue562.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue562/query-issue562.3.query.sqlpp
@@ -28,8 +28,8 @@ USE tpch;
WITH q22_customer_tmp AS
(
- SELECT c_acctbal, c_custkey, phone_substr AS cntrycode
- FROM Customer
+ SELECT c.c_acctbal, c.c_custkey, phone_substr AS cntrycode
+ FROM Customer c
LET phone_substr = substring(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'
@@ -40,7 +40,7 @@ FROM q22_customer_tmp as ct
WHERE coll_count((
SELECT ELEMENT Orders
FROM Orders
- WHERE c_custkey = o_custkey
+ WHERE ct.c_custkey = o_custkey
)) = 0
GROUP BY cntrycode
ORDER BY cntrycode
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue638/query-issue638.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue638/query-issue638.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue638/query-issue638.3.query.sqlpp
index 5854816..cb6eca7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue638/query-issue638.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue638/query-issue638.3.query.sqlpp
@@ -29,24 +29,24 @@ USE tpch;
SELECT nation, o_year, sum(amount) AS sum_profit
FROM
(
- SELECT n_name AS nation,
- GET_YEAR(o_orderdate) AS o_year,
- l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
+ SELECT l3.n_name AS nation,
+ GET_YEAR(o.o_orderdate) AS o_year,
+ l3.l_extendedprice * (1 - l3.l_discount) - l3.ps_supplycost * l3.l_quantity AS amount
FROM
Orders o JOIN
(
- SELECT l_extendedprice, l_discount, l_quantity, l_orderkey, n_name, ps_supplycost
+ SELECT l2.l_extendedprice, l2.l_discount, l2.l_quantity, l2.l_orderkey, l2.n_name, l2.ps_supplycost
FROM Part p JOIN
- (SELECT l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name, ps_supplycost
+ (SELECT l1.l_extendedprice, l1.l_discount, l1.l_quantity, l1.l_partkey, l1.l_orderkey, l1.n_name, ps.ps_supplycost
FROM Partsupp ps join
- (SELECT l_suppkey, l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, n_name
+ (SELECT l.l_suppkey, l.l_extendedprice, l.l_discount, l.l_quantity, l.l_partkey, l.l_orderkey, s1.n_name
FROM
- (SELECT s_suppkey, n_name
- FROM Nation n join Supplier s ON n_nationkey = s_nationkey
- ) s1 JOIN LineItem l ON s_suppkey = l_suppkey
- ) l1 ON ps_suppkey = l_suppkey and ps_partkey = l_partkey
- ) l2 ON contains(p_name,'green') AND p_partkey = l_partkey
- ) l3 ON o_orderkey = l_orderkey
+ (SELECT s.s_suppkey, n.n_name
+ FROM Nation n join Supplier s ON n.n_nationkey = s.s_nationkey
+ ) s1 JOIN LineItem l ON s1.s_suppkey = l.l_suppkey
+ ) l1 ON ps.ps_suppkey = l1.l_suppkey and ps.ps_partkey = l1.l_partkey
+ ) l2 ON contains(p.p_name,'green') AND p.p_partkey = l2.l_partkey
+ ) l3 ON o.o_orderkey = l3.l_orderkey
) profit
GROUP BY nation, o_year
ORDER BY nation, o_year desc;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 9254656..609ad06 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
@@ -33,19 +33,19 @@ WITH t AS (
WHERE nation.n_nationkey = sn.n_nationkey
),
X as (
- SELECT nation_key, orderdate AS order_date, sum(o_totalprice) AS sum_price
- FROM t,
- Customer,
- Orders
- WHERE o_custkey = c_custkey and c_nationkey = n_nationkey
- GROUP BY o_orderdate AS orderdate, n_nationkey AS nation_key
+ SELECT nation_key, orderdate AS order_date, sum(o.o_totalprice) AS sum_price
+ FROM t n,
+ Customer c,
+ Orders o
+ WHERE o.o_custkey = c.c_custkey and c.c_nationkey = n.n_nationkey
+ GROUP BY o.o_orderdate AS orderdate, n.n_nationkey AS nation_key
)
SELECT nation_key,
(
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
+ ORDER BY g.X.sum_price desc
LIMIT 3
) AS sum_price
FROM X
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 8d4460a..541b3ec 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
@@ -36,12 +36,12 @@ SELECT nation_key,
LIMIT 3
) AS sum_price
FROM (
- SELECT nation_key, orderdate AS order_date, sum(o_totalprice) AS sum_price
- FROM Nation,
- Customer,
- Orders AS orders
- WHERE o_custkey = c_custkey AND c_nationkey = n_nationkey
- GROUP BY o_orderdate as orderdate, n_nationkey as nation_key
+ SELECT nation_key, orderdate AS order_date, sum(o.o_totalprice) AS sum_price
+ FROM Nation n,
+ Customer c,
+ Orders o
+ WHERE o.o_custkey = c.c_custkey AND c.c_nationkey = n.n_nationkey
+ GROUP BY o.o_orderdate as orderdate, n.n_nationkey as nation_key
) AS x
GROUP BY nation_key
GROUP AS g
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue786/query-issue786.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue786/query-issue786.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue786/query-issue786.3.query.sqlpp
index a9fc808..5eda643 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue786/query-issue786.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-sugar/query-issue786/query-issue786.3.query.sqlpp
@@ -29,11 +29,11 @@ USE tpch;
SELECT nation.n_nationkey AS nation_key,
nation.n_name AS name,
(
- SELECT orderdate AS order_date, sum(o_totalprice) AS sum_price
- FROM Orders,
- Customer
- WHERE o_custkey = c_custkey AND c_nationkey = nation.n_nationkey
- GROUP BY o_orderdate as orderdate
+ SELECT orderdate AS order_date, sum(o.o_totalprice) AS sum_price
+ FROM Orders o,
+ Customer c
+ WHERE o.o_custkey = c.c_custkey AND c.c_nationkey = nation.n_nationkey
+ GROUP BY o.o_orderdate as orderdate
ORDER BY sum_price DESC
LIMIT 3
) AS aggregates
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 4823a39..0c0bdaa 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
@@ -41,6 +41,6 @@ from (
) as gco
group by gco.c_count as c_count
group as g
-let custdist = count(( select value gco from g ))
+let custdist = count(gco)
order by custdist desc,c_count desc
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/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 3963de0..65293dc 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
@@ -78,6 +78,6 @@ from (
) as t4
group by t4.s_name as s_name
group as g
-let numwait = count(( from g select value t4 ))
+let numwait = coll_count(( from g select value t4 ))
order by numwait desc,s_name
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
index 3f3a50a..4689676 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/query-issue601/query-issue601.3.query.sqlpp
@@ -26,7 +26,7 @@
use tpch;
-select element {'l_linenumber':l.l_linenumber,'count_order': count((from g select value g))}
+select element {'l_linenumber':l.l_linenumber,'count_order': coll_count((from g select value g))}
from LineItem as l
group by l.l_linenumber
group as g
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
index 4823a39..0c0bdaa 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q13_customer_distribution/q13_customer_distribution.3.query.sqlpp
@@ -41,6 +41,6 @@ from (
) as gco
group by gco.c_count as c_count
group as g
-let custdist = count(( select value gco from g ))
+let custdist = count(gco)
order by custdist desc,c_count desc
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
index 3963de0..65293dc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.sqlpp
@@ -78,6 +78,6 @@ from (
) as t4
group by t4.s_name as s_name
group as g
-let numwait = count(( from g select value t4 ))
+let numwait = coll_count(( from g select value t4 ))
order by numwait desc,s_name
;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/ef1719e3/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp
index 3f3a50a..4689676 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch/query-issue601/query-issue601.3.query.sqlpp
@@ -26,7 +26,7 @@
use tpch;
-select element {'l_linenumber':l.l_linenumber,'count_order': count((from g select value g))}
+select element {'l_linenumber':l.l_linenumber,'count_order': coll_count((from g select value g))}
from LineItem as l
group by l.l_linenumber
group as g