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
