[NO ISSUE][COMP] Add TPC-DS ROLLUP tests
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Add GROUP ROLLUP tests from TPC-DS
- Fix incorrect UNION ALL handling by ComplexUnnestToProductRule
- Add variable checks to plan structure verifier
Change-Id: If6558909998f0cd036c0fb367047cba49224d6fd
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/9824
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.1.ddl.sqlpp
new file mode 100644
index 0000000..f02107b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.1.ddl.sqlpp
@@ -0,0 +1,337 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : int64?,
+ sr_return_time_sk : int64?,
+ sr_item_sk : int64,
+ sr_customer_sk : int64?,
+ sr_cdemo_sk : int64?,
+ sr_hdemo_sk : int64?,
+ sr_addr_sk : int64?,
+ sr_store_sk : int64?,
+ sr_reason_sk : int64?,
+ sr_ticket_number : int64,
+ sr_return_quantity : int64?,
+ sr_return_amt : double,
+ sr_return_tax : double?,
+ sr_return_amt_inc_tax : double?,
+ sr_fee : double?,
+ sr_return_ship_cost : double?,
+ sr_refunded_cash : double?,
+ sr_reversed_charge : double?,
+ sr_store_credit : double?,
+ sr_net_loss : double?
+};
+
+create type tpcds.catalog_page_type as
+ closed {
+ cp_catalog_page_sk: bigint,
+ cp_catalog_page_id: string,
+ cp_start_date_sk: bigint?,
+ cp_end_date_sk: bigint?,
+ cp_department: string?,
+ cp_catalog_number: bigint?,
+ cp_catalog_page_number: bigint?,
+ cp_description: string?,
+ cp_type: string?
+};
+
+create type tpcds.catalog_sales_type as
+ closed {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+};
+
+create type tpcds.catalog_returns_type as
+ closed {
+ cr_returned_date_sk : bigint?,
+ cr_returned_time_sk : bigint?,
+ cr_item_sk : bigint,
+ cr_refunded_customer_sk : bigint?,
+ cr_refunded_cdemo_sk : bigint?,
+ cr_refunded_hdemo_sk : bigint?,
+ cr_refunded_addr_sk : bigint?,
+ cr_returning_customer_sk : bigint?,
+ cr_returning_cdemo_sk : bigint?,
+ cr_returning_hdemo_sk : bigint?,
+ cr_returning_addr_sk : bigint?,
+ cr_call_center_sk : bigint?,
+ cr_catalog_page_sk : bigint?,
+ cr_ship_mode_sk : bigint?,
+ cr_warehouse_sk : bigint?,
+ cr_reason_sk : bigint?,
+ cr_order_number : bigint,
+ cr_return_quantity : bigint?,
+ cr_return_amount : double?,
+ cr_return_tax : double?,
+ cr_return_amt_inc_tax : double?,
+ cr_fee : double?,
+ cr_return_ship_cost : double?,
+ cr_refunded_cash : double?,
+ cr_reversed_charge : double?,
+ cr_store_credit : double?,
+ cr_net_loss : double?
+};
+
+create type tpcds.web_site_type as
+ closed {
+ web_site_sk: bigint,
+ web_site_id: string,
+ web_rec_start_date: string?,
+ web_rec_end_date: string?,
+ web_name: string?,
+ web_open_date_sk: bigint?,
+ web_close_date_sk: bigint?,
+ web_class: string?,
+ web_manager: string?,
+ web_mkt_id: bigint?,
+ web_mkt_class: string?,
+ web_mkt_desc: string?,
+ web_market_manager: string?,
+ web_company_id: bigint?,
+ web_company_name: string?,
+ web_street_number: string?,
+ web_street_name: string?,
+ web_street_type: string?,
+ web_suite_number: string?,
+ web_city: string?,
+ web_county: string?,
+ web_state: string?,
+ web_zip: string?,
+ web_country: string?,
+ web_gmt_offset: double?,
+ web_tax_percentage: double?
+};
+
+create type tpcds.web_sales_type as
+ closed {
+ ws_sold_date_sk : int64?,
+ ws_sold_time_sk : int64?,
+ ws_ship_date_sk : int64?,
+ ws_item_sk : int64,
+ ws_bill_customer_sk : int64?,
+ ws_bill_cdemo_sk : int64?,
+ ws_bill_hdemo_sk : int64?,
+ ws_bill_addr_sk : int64?,
+ ws_ship_customer_sk : int64?,
+ ws_ship_cdemo_sk : int64?,
+ ws_ship_hdemo_sk : int64?,
+ ws_ship_addr_sk : int64?,
+ ws_web_page_sk : int64?,
+ ws_web_site_sk : int64?,
+ ws_ship_mode_sk : int64?,
+ ws_warehouse_sk : int64?,
+ ws_promo_sk : int64?,
+ ws_order_number : int64,
+ ws_quantity : int64?,
+ ws_wholesale_cost : double?,
+ ws_list_price : double?,
+ ws_sales_price : double?,
+ ws_ext_discount_amt : double?,
+ ws_ext_sales_price : double?,
+ ws_ext_wholesale_cost : double?,
+ ws_ext_list_price : double?,
+ ws_ext_tax : double?,
+ ws_coupon_amt : double?,
+ ws_ext_ship_cost : double?,
+ ws_net_paid : double?,
+ ws_net_paid_inc_tax : double?,
+ ws_net_paid_inc_ship : double?,
+ ws_net_paid_inc_ship_tax : double?,
+ ws_net_profit : double?
+};
+
+create type tpcds.web_returns_type as
+ closed {
+ wr_returned_date_sk : bigint?,
+ wr_returned_time_sk : bigint?,
+ wr_item_sk : bigint,
+ wr_refunded_customer_sk : bigint?,
+ wr_refunded_cdemo_sk : bigint?,
+ wr_refunded_hdemo_sk : bigint?,
+ wr_refunded_addr_sk : bigint?,
+ wr_returning_customer_sk : bigint?,
+ wr_returning_cdemo_sk : bigint?,
+ wr_returning_hdemo_sk : bigint?,
+ wr_returning_addr_sk : bigint?,
+ wr_web_page_sk : bigint?,
+ wr_reason_sk : bigint?,
+ wr_order_number : bigint,
+ wr_return_quantity : bigint?,
+ wr_return_amt : double?,
+ wr_return_tax : double?,
+ wr_return_amt_inc_tax : double?,
+ wr_fee : double?,
+ wr_return_ship_cost: double?,
+ wr_refunded_cash: double?,
+ wr_reversed_charge: double?,
+ wr_account_credit: double?,
+ wr_net_loss: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset store_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number;
+
+create dataset catalog_page (catalog_page_type) primary key cp_catalog_page_sk;
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset catalog_returns (catalog_returns_type) primary key cr_item_sk, cr_order_number;
+
+create dataset web_site (web_site_type) primary key web_site_sk;
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
+
+create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.2.update.sqlpp
new file mode 100644
index 0000000..9b66400
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.2.update.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_page using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_site using localfs ((`path`=`asterix_nc1://data/tpcds/web_site.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.3.query.sqlpp
new file mode 100644
index 0000000..7997b21
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q05/q05.3.query.sqlpp
@@ -0,0 +1,155 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+WITH
+ssr AS (
+ SELECT s.s_store_id,
+ SUM(salesreturns.sales_price) AS sales,
+ SUM(salesreturns.profit) AS profit,
+ SUM(salesreturns.return_amt) AS returns,
+ SUM(salesreturns.net_loss) AS profit_loss
+ FROM (
+ SELECT ss_store_sk AS store_sk,
+ ss_sold_date_sk AS date_sk,
+ ss_ext_sales_price AS sales_price,
+ ss_net_profit AS profit,
+ 0 AS return_amt,
+ 0 AS net_loss
+ FROM store_sales
+
+ UNION ALL
+
+ SELECT sr_store_sk AS store_sk,
+ sr_returned_date_sk AS date_sk,
+ 0 AS sales_price,
+ 0 AS profit,
+ sr_return_amt AS return_amt,
+ sr_net_loss AS net_loss
+ FROM store_returns
+ ) salesreturns,
+ date_dim d,
+ store s
+ WHERE salesreturns.date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND salesreturns.store_sk = s.s_store_sk
+ GROUP BY s.s_store_id
+),
+
+csr AS (
+ SELECT cp.cp_catalog_page_id,
+ SUM(salesreturns.sales_price) AS sales,
+ SUM(salesreturns.profit) AS profit,
+ SUM(salesreturns.return_amt) AS returns,
+ SUM(salesreturns.net_loss) AS profit_loss
+ FROM (
+ SELECT cs_catalog_page_sk AS page_sk,
+ cs_sold_date_sk AS date_sk,
+ cs_ext_sales_price AS sales_price,
+ cs_net_profit AS profit,
+ 0 AS return_amt,
+ 0 AS net_loss
+ FROM catalog_sales
+
+ UNION ALL
+
+ SELECT cr_catalog_page_sk AS page_sk,
+ cr_returned_date_sk AS date_sk,
+ 0 AS sales_price,
+ 0 AS profit,
+ cr_return_amount AS return_amt,
+ cr_net_loss AS net_loss
+ FROM catalog_returns
+ ) salesreturns,
+ date_dim d,
+ catalog_page cp
+ WHERE salesreturns.date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND salesreturns.page_sk = cp.cp_catalog_page_sk
+ GROUP BY cp.cp_catalog_page_id
+),
+
+wsr AS (
+ SELECT web.web_site_id,
+ SUM(salesreturns.sales_price) AS sales,
+ SUM(salesreturns.profit) AS profit,
+ SUM(salesreturns.return_amt) AS returns,
+ SUM(salesreturns.net_loss) AS profit_loss
+ FROM (
+ SELECT ws_web_site_sk AS wsr_web_site_sk,
+ ws_sold_date_sk AS date_sk,
+ ws_ext_sales_price AS sales_price,
+ ws_net_profit AS profit,
+ 0 AS return_amt,
+ 0 AS net_loss
+ FROM web_sales
+
+ UNION ALL
+
+ SELECT ws.ws_web_site_sk AS wsr_web_site_sk,
+ wr.wr_returned_date_sk AS date_sk,
+ 0 AS sales_price,
+ 0 AS profit,
+ wr.wr_return_amt AS return_amt,
+ wr.wr_net_loss AS net_loss
+ FROM web_returns wr LEFT OUTER JOIN web_sales ws
+ ON ( wr.wr_item_sk = ws.ws_item_sk AND wr.wr_order_number = ws.ws_order_number)
+ ) salesreturns,
+ date_dim d,
+ web_site web
+ WHERE salesreturns.date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND salesreturns.wsr_web_site_sk = web.web_site_sk
+ GROUP BY web.web_site_id
+)
+
+SELECT channel, id,
+ ROUND(SUM(sales), 2) AS sales,
+ ROUND(SUM(returns), 2) AS returns,
+ ROUND(SUM(profit), 2) AS profit
+FROM (
+ SELECT 'store channel' AS channel,
+ 'store' || s_store_id AS id,
+ sales,
+ returns,
+ (profit - profit_loss) AS profit
+ FROM ssr
+
+ UNION ALL
+
+ SELECT 'catalog channel' AS channel,
+ 'catalog_page' || cp_catalog_page_id AS id,
+ sales,
+ returns,
+ (profit - profit_loss) AS profit
+ FROM csr
+
+ UNION ALL
+
+ SELECT 'web channel' AS channel,
+ 'web_site' || web_site_id AS id,
+ sales,
+ returns,
+ (profit - profit_loss) AS profit
+ FROM wsr
+) x
+GROUP BY ROLLUP (channel, id)
+ORDER BY channel, id
+LIMIT 100;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
index 3c40721..66a4d36 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
@@ -54,6 +54,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp
index 5159a42..b2f60d4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp
@@ -20,10 +20,10 @@
use tpcds;
-load dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.1.ddl.sqlpp
new file mode 100644
index 0000000..85e3a8c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.1.ddl.sqlpp
@@ -0,0 +1,183 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.catalog_sales_type as
+ closed {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+};
+
+create type tpcds.customer_demographics_type as
+ closed {
+ cd_demo_sk : int64,
+ cd_gender : string?,
+ cd_marital_status : string?,
+ cd_education_status : string?,
+ cd_purchase_estimate : int64?,
+ cd_credit_rating : string?,
+ cd_dep_count : int64?,
+ cd_dep_employed_count : int64?,
+ cd_dep_college_count : int64?
+};
+
+create type tpcds.customer_type as
+ closed {
+ c_customer_sk : int64,
+ c_customer_id : string,
+ c_current_cdemo_sk : int64?,
+ c_current_hdemo_sk : int64?,
+ c_current_addr_sk : int64?,
+ c_first_shipto_date_sk : int64?,
+ c_first_sales_date_sk : int64?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : int64?,
+ c_birth_month : int64?,
+ c_birth_year : int64?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+};
+
+create type tpcds.customer_address_type as
+ closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ };
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset customer_demographics(customer_demographics_type) primary key cd_demo_sk;
+
+create dataset customer (customer_type) primary key c_customer_sk;
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.2.update.sqlpp
new file mode 100644
index 0000000..406067f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.2.update.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.3.query.sqlpp
new file mode 100644
index 0000000..4bb6203
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.3.query.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+SET `compiler.joinmemory` "160KB";
+
+USE tpcds;
+
+SELECT i.i_item_id,
+ ca.ca_country,
+ ca.ca_state,
+ ca.ca_county,
+ ROUND(AVG(cs.cs_quantity), 2) agg1,
+ ROUND(AVG(cs.cs_list_price), 2) agg2,
+ ROUND(AVG(cs.cs_coupon_amt), 2) agg3,
+ ROUND(AVG(cs.cs_sales_price), 2) agg4,
+ ROUND(AVG(cs.cs_net_profit), 2) agg5,
+ ROUND(AVG(cs.c_birth_year), 2) agg6,
+ ROUND(AVG(cd1.cd_dep_count), 2) agg7
+FROM catalog_sales cs, customer_demographics cd1,
+ customer_demographics cd2, customer c, customer_address ca, date_dim d, item i
+WHERE cs.cs_sold_date_sk = d.d_date_sk
+ AND cs.cs_item_sk = i.i_item_sk
+ AND cs.cs_bill_cdemo_sk = cd1.cd_demo_sk
+ AND cs.cs_bill_customer_sk = c.c_customer_sk
+ AND cd1.cd_gender = 'F'
+ AND cd1.cd_education_status = 'Secondary'
+ AND c.c_current_cdemo_sk = cd2.cd_demo_sk
+ AND c.c_current_addr_sk = ca.ca_address_sk
+ AND c.c_birth_month in [4,5,6,9,10,12]
+ AND d.d_year = 2000
+ AND ca.ca_state in ['IL','PA','TX','MO','WA','OR']
+GROUP BY ROLLUP (i.i_item_id, ca.ca_country, ca.ca_state, ca.ca_county)
+ORDER BY ca.ca_country, ca.ca_state, ca.ca_county, i.i_item_id
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp
index 3326d7a..c76f81a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp
@@ -36,6 +36,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp
index a14b6b6..1fe0fe6 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp
@@ -19,9 +19,9 @@
use tpcds;
-load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp
index 28834d3..2c6148d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp
@@ -35,7 +35,7 @@
cs.cs_item_sk = i.i_item_sk
AND i.i_category IN ['Home', 'Men', 'Women']
AND cs.cs_sold_date_sk = d.d_date_sk
- AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P3Y'))
GROUP BY
i.i_item_id,
i.i_item_desc,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.1.ddl.sqlpp
new file mode 100644
index 0000000..73a7309
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.1.ddl.sqlpp
@@ -0,0 +1,95 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.inventory_type as
+ closed {
+ inv_date_sk : bigint,
+ inv_item_sk : bigint,
+ inv_warehouse_sk : bigint,
+ inv_quantity_on_hand : bigint?
+};
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset inventory (inventory_type) primary key inv_date_sk, inv_item_sk, inv_warehouse_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.2.update.sqlpp
new file mode 100644
index 0000000..daaf2ad
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.2.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset inventory using localfs ((`path`=`asterix_nc1://data/tpcds/inventory.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.3.query.sqlpp
new file mode 100644
index 0000000..fc8a488
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q22/q22.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT i.i_product_name, i.i_brand, i.i_class, i.i_category,
+ AVG(inv.inv_quantity_on_hand) qoh
+FROM inventory inv, date_dim d, item i
+WHERE inv.inv_date_sk=d.d_date_sk
+ AND inv.inv_item_sk=i.i_item_sk
+ AND d.d_month_seq BETWEEN 1000 AND 2000
+GROUP BY ROLLUP(i.i_product_name, i.i_brand, i.i_class, i.i_category)
+ORDER BY qoh, i.i_product_name, i.i_brand, i.i_class, i.i_category
+LIMIT 100;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
index 8391405..0ccd813 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
@@ -114,6 +114,8 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ /* TODO(dmitry): removed until we get better data
+ cs_catalog_page_sk: bigint?, */
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
index 1320742..2e13554 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
@@ -75,6 +75,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
index bcbd71f..0e880dd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
@@ -20,12 +20,12 @@
use tpcds;
-load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset promotion using localfs ((`path`=`asterix_nc1://data/tpcds/promotion.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset promotion using localfs ((`path`=`asterix_nc1://data/tpcds/promotion.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.1.ddl.sqlpp
new file mode 100644
index 0000000..e0a51c0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.1.ddl.sqlpp
@@ -0,0 +1,165 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create type tpcds.customer_demographics_type as
+ closed {
+ cd_demo_sk : bigint,
+ cd_gender : string?,
+ cd_marital_status : string?,
+ cd_education_status : string?,
+ cd_purchase_estimate : bigint?,
+ cd_credit_rating : string?,
+ cd_dep_count : bigint?,
+ cd_dep_employed_count : bigint?,
+ cd_dep_college_count : bigint?
+};
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset customer_demographics(customer_demographics_type) primary key cd_demo_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.2.update.sqlpp
new file mode 100644
index 0000000..2344686
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.3.query.sqlpp
new file mode 100644
index 0000000..4a33d6c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q27/q27.3.query.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT i.i_item_id,
+ s.s_state, GROUPING(s.s_state) g_state,
+ ROUND(AVG(ss.ss_quantity), 2) agg1,
+ ROUND(AVG(ss.ss_list_price), 2) agg2,
+ ROUND(AVG(ss.ss_coupon_amt), 2) agg3,
+ ROUND(AVG(ss.ss_sales_price), 2) agg4
+FROM store_sales ss, customer_demographics cd, date_dim d, store s, item i
+WHERE ss.ss_sold_date_sk = d.d_date_sk
+ AND ss.ss_item_sk = i.i_item_sk
+ AND ss.ss_store_sk = s.s_store_sk
+ AND ss.ss_cdemo_sk = cd.cd_demo_sk
+ AND cd.cd_gender = 'M'
+ AND cd.cd_marital_status = 'M'
+ AND cd.cd_education_status = 'Primary'
+ AND d.d_year = 1999
+ AND s.s_state IN ['TN','TN']
+GROUP BY ROLLUP (i.i_item_id, s.s_state)
+ORDER BY i.i_item_id, s.s_state
+LIMIT 100;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
index 8391405..0ccd813 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
@@ -114,6 +114,8 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ /* TODO(dmitry): removed until we get better data
+ cs_catalog_page_sk: bigint?, */
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.1.ddl.sqlpp
new file mode 100644
index 0000000..f26563f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.1.ddl.sqlpp
@@ -0,0 +1,150 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.2.update.sqlpp
new file mode 100644
index 0000000..277a679
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.2.update.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.3.query.sqlpp
new file mode 100644
index 0000000..8a24510
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q36/q36.3.query.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT
+ ROUND(SUM(ss.ss_net_profit) / SUM(ss.ss_ext_sales_price), 2) AS gross_margin,
+ i.i_category,
+ i.i_class,
+ GROUPING(i.i_category) + GROUPING(i.i_class) AS lochierarchy,
+ RANK() OVER (
+ PARTITION BY
+ GROUPING(i.i_category) + GROUPING(i.i_class),
+ CASE WHEN GROUPING(i.i_class) = 0 THEN i.i_category END
+ ORDER BY
+ SUM(ss.ss_net_profit) / SUM(ss.ss_ext_sales_price) ASC
+ ) AS rank_within_parent
+FROM store_sales ss, date_dim d, item i, store s
+WHERE d.d_year = 1999
+ AND d.d_date_sk = ss.ss_sold_date_sk
+ AND i.i_item_sk = ss.ss_item_sk
+ AND s.s_store_sk = ss.ss_store_sk
+ AND s.s_state IN ['TN','TN']
+GROUP BY
+ ROLLUP(i.i_category, i.i_class)
+ORDER BY
+ lochierarchy DESC,
+ CASE WHEN lochierarchy = 0 THEN i.i_category END,
+ rank_within_parent
+ /* TODO(dmitry). makes result stable. revisit. */
+ , i.i_category, i.i_class
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp
index 24cbaa4..25fdb80 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp
@@ -95,6 +95,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp
index 36c7f47..4378cfd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp
@@ -67,6 +67,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
index 2ed4e32..773390c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
@@ -19,16 +19,16 @@
use tpcds;
-load dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
-load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ No newline at end of file
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp
index b906174..4fc3bf2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp
@@ -43,7 +43,7 @@
date_dim d
WHERE
wr.wr_return_amt > 100
- AND ws.ws_net_profit > 1
+ /* AND ws.ws_net_profit > 1 */
AND ws.ws_net_paid > 0
AND ws.ws_quantity > 0
AND ws.ws_sold_date_sk = d.d_date_sk
@@ -80,11 +80,11 @@
date_dim d
WHERE
cr.cr_return_amount > 100
- AND cs.cs_net_profit > 1
+ /* AND cs.cs_net_profit > 1 */
AND cs.cs_net_paid > 0
AND cs.cs_quantity > 0
AND cs.cs_sold_date_sk = d.d_date_sk
- /*AND d.d_year = 1999 AND d.d_moy = 12*/
+ /* AND d.d_year = 1999 AND d.d_moy = 12 */
GROUP BY
cs.cs_item_sk
) in_cat
@@ -117,11 +117,11 @@
date_dim d
WHERE
sr.sr_return_amt > 100
- AND sts.ss_net_profit > 1
+ /* AND sts.ss_net_profit > 1 */
AND sts.ss_net_paid > 0
AND sts.ss_quantity > 0
AND sts.ss_sold_date_sk = d.d_date_sk
- /*AND d.d_year = 1999 AND d.d_moy = 12*/
+ /* AND d.d_year = 1999 AND d.d_moy = 12 */
GROUP BY
sts.ss_item_sk
) in_store
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp
index 8581407..cf294d5 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp
@@ -71,6 +71,7 @@
cs_ship_hdemo_sk: bigint?,
cs_ship_addr_sk: bigint?,
cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
cs_ship_mode_sk: bigint?,
cs_warehouse_sk: bigint?,
cs_item_sk: bigint,
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp
index 802f904..f11b617 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp
@@ -39,7 +39,7 @@
cs.cs_item_sk = i.i_item_sk
AND cs.cs_sold_date_sk = d.d_date_sk
AND cc.cc_call_center_sk = cs.cs_call_center_sk
- /*AND ( d.d_year = 2000 OR ( d.d_year = 2000 - 1 AND d.d_moy = 12 ) OR ( d.d_year = 2000 + 1 AND d.d_moy = 1 ) )*/
+ /*AND ( d.d_year = 2001 OR ( d.d_year = 2001 - 1 AND d.d_moy = 12 ) OR ( d.d_year = 2001 + 1 AND d.d_moy = 1 ) )*/
GROUP BY
i.i_category,
i.i_brand,
@@ -75,7 +75,7 @@
SELECT v2.*
FROM v2
WHERE
- d_year = 2000 AND
+ d_year = 2001 AND
avg_monthly_sales > 0
/*AND CASE WHEN avg_monthly_sales > 0 THEN ABS(sum_sales - avg_monthly_sales) / avg_monthly_sales ELSE NULL END > 0.1*/
ORDER BY
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.1.ddl.sqlpp
new file mode 100644
index 0000000..f26563f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.1.ddl.sqlpp
@@ -0,0 +1,150 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.2.update.sqlpp
new file mode 100644
index 0000000..277a679
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.2.update.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.3.query.sqlpp
new file mode 100644
index 0000000..8ab128a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q67/q67.3.query.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT dw2.*
+FROM (
+ SELECT
+ i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales,
+ RANK() OVER (PARTITION BY i_category ORDER BY sumsales DESC) rk
+ FROM (
+ SELECT
+ i.i_category, i.i_class, i.i_brand, i.i_product_name, d.d_year, d.d_qoy, d.d_moy, s.s_store_id,
+ ROUND(SUM(COALESCE(ss.ss_sales_price * ss.ss_quantity, 0)), 2) sumsales
+ FROM
+ store_sales ss, date_dim d, store s, item i
+ WHERE ss.ss_sold_date_sk = d.d_date_sk
+ AND ss.ss_item_sk = i.i_item_sk
+ AND ss.ss_store_sk = s.s_store_sk
+ AND d.d_month_seq BETWEEN 1000 AND 2000
+ GROUP BY ROLLUP(i.i_category, i.i_class, i.i_brand, i.i_product_name, d.d_year, d.d_qoy, d.d_moy, s.s_store_id)
+ ) dw1
+ ) dw2
+WHERE rk <= 100
+ORDER BY i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales, rk
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.1.ddl.sqlpp
new file mode 100644
index 0000000..91d69c9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.1.ddl.sqlpp
@@ -0,0 +1,121 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.2.update.sqlpp
new file mode 100644
index 0000000..a6a6a04
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.2.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.3.query.sqlpp
new file mode 100644
index 0000000..76b9c7e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q70/q70.3.query.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT
+ ROUND(SUM(ss1.ss_net_profit), 2) AS total_sum,
+ s1.s_state,
+ s1.s_county,
+ GROUPING(s1.s_state) + GROUPING(s1.s_county) AS lochierarchy,
+ RANK() OVER (
+ PARTITION BY
+ GROUPING(s1.s_state) + GROUPING(s1.s_county),
+ CASE WHEN GROUPING(s1.s_county) = 0 THEN s1.s_state END
+ ORDER BY
+ SUM(ss1.ss_net_profit) DESC
+ ) AS rank_within_parent
+FROM
+ store_sales ss1, date_dim d1, store s1
+WHERE d1.d_month_seq BETWEEN 1000 AND 2000
+ AND d1.d_date_sk = ss1.ss_sold_date_sk
+ AND s1.s_store_sk = ss1.ss_store_sk
+ AND s1.s_state IN (
+ SELECT VALUE s_state
+ FROM (
+ SELECT
+ s.s_state AS s_state,
+ RANK() OVER (
+ PARTITION BY s.s_state
+ ORDER BY SUM(ss.ss_net_profit) DESC
+ ) AS ranking
+ FROM store_sales ss, store s, date_dim d
+ WHERE d.d_month_seq BETWEEN 1000 AND 2000
+ AND d.d_date_sk = ss.ss_sold_date_sk
+ AND s.s_store_sk = ss.ss_store_sk
+ GROUP BY s.s_state
+ ) tmp1
+ WHERE ranking <= 5
+ )
+GROUP BY ROLLUP(s1.s_state, s1.s_county)
+ORDER BY
+ lochierarchy DESC,
+ CASE WHEN lochierarchy = 0 THEN s1.s_state END,
+ rank_within_parent
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.1.ddl.sqlpp
new file mode 100644
index 0000000..e0acbda
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.1.ddl.sqlpp
@@ -0,0 +1,310 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : int64?,
+ sr_return_time_sk : int64?,
+ sr_item_sk : int64,
+ sr_customer_sk : int64?,
+ sr_cdemo_sk : int64?,
+ sr_hdemo_sk : int64?,
+ sr_addr_sk : int64?,
+ sr_store_sk : int64?,
+ sr_reason_sk : int64?,
+ sr_ticket_number : int64,
+ sr_return_quantity : int64?,
+ sr_return_amt : double,
+ sr_return_tax : double?,
+ sr_return_amt_inc_tax : double?,
+ sr_fee : double?,
+ sr_return_ship_cost : double?,
+ sr_refunded_cash : double?,
+ sr_reversed_charge : double?,
+ sr_store_credit : double?,
+ sr_net_loss : double?
+};
+
+create type tpcds.catalog_sales_type as
+ closed {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+};
+
+create type tpcds.catalog_returns_type as
+ closed {
+ cr_returned_date_sk : bigint?,
+ cr_returned_time_sk : bigint?,
+ cr_item_sk : bigint,
+ cr_refunded_customer_sk : bigint?,
+ cr_refunded_cdemo_sk : bigint?,
+ cr_refunded_hdemo_sk : bigint?,
+ cr_refunded_addr_sk : bigint?,
+ cr_returning_customer_sk : bigint?,
+ cr_returning_cdemo_sk : bigint?,
+ cr_returning_hdemo_sk : bigint?,
+ cr_returning_addr_sk : bigint?,
+ cr_call_center_sk : bigint?,
+ cr_catalog_page_sk : bigint?,
+ cr_ship_mode_sk : bigint?,
+ cr_warehouse_sk : bigint?,
+ cr_reason_sk : bigint?,
+ cr_order_number : bigint,
+ cr_return_quantity : bigint?,
+ cr_return_amount : double?,
+ cr_return_tax : double?,
+ cr_return_amt_inc_tax : double?,
+ cr_fee : double?,
+ cr_return_ship_cost : double?,
+ cr_refunded_cash : double?,
+ cr_reversed_charge : double?,
+ cr_store_credit : double?,
+ cr_net_loss : double?
+};
+
+create type tpcds.web_page_type as
+ closed {
+ wp_web_page_sk : int64,
+ wp_web_page_id : string,
+ wp_rec_start_date : string?,
+ wp_rec_end_date : string?,
+ wp_creation_date_sk : int64?,
+ wp_access_date_sk : int64?,
+ wp_autogen_flag : string?,
+ wp_customer_sk : int64?,
+ wp_url : string?,
+ wp_type : string?,
+ wp_char_count : int64?,
+ wp_link_count : int64?,
+ wp_image_count : int64?,
+ wp_max_ad_count : int64?
+};
+
+create type tpcds.web_sales_type as
+ closed {
+ ws_sold_date_sk : int64?,
+ ws_sold_time_sk : int64?,
+ ws_ship_date_sk : int64?,
+ ws_item_sk : int64,
+ ws_bill_customer_sk : int64?,
+ ws_bill_cdemo_sk : int64?,
+ ws_bill_hdemo_sk : int64?,
+ ws_bill_addr_sk : int64?,
+ ws_ship_customer_sk : int64?,
+ ws_ship_cdemo_sk : int64?,
+ ws_ship_hdemo_sk : int64?,
+ ws_ship_addr_sk : int64?,
+ ws_web_page_sk : int64?,
+ ws_web_site_sk : int64?,
+ ws_ship_mode_sk : int64?,
+ ws_warehouse_sk : int64?,
+ ws_promo_sk : int64?,
+ ws_order_number : int64,
+ ws_quantity : int64?,
+ ws_wholesale_cost : double?,
+ ws_list_price : double?,
+ ws_sales_price : double?,
+ ws_ext_discount_amt : double?,
+ ws_ext_sales_price : double?,
+ ws_ext_wholesale_cost : double?,
+ ws_ext_list_price : double?,
+ ws_ext_tax : double?,
+ ws_coupon_amt : double?,
+ ws_ext_ship_cost : double?,
+ ws_net_paid : double?,
+ ws_net_paid_inc_tax : double?,
+ ws_net_paid_inc_ship : double?,
+ ws_net_paid_inc_ship_tax : double?,
+ ws_net_profit : double?
+};
+
+create type tpcds.web_returns_type as
+ closed {
+ wr_returned_date_sk : bigint?,
+ wr_returned_time_sk : bigint?,
+ wr_item_sk : bigint,
+ wr_refunded_customer_sk : bigint?,
+ wr_refunded_cdemo_sk : bigint?,
+ wr_refunded_hdemo_sk : bigint?,
+ wr_refunded_addr_sk : bigint?,
+ wr_returning_customer_sk : bigint?,
+ wr_returning_cdemo_sk : bigint?,
+ wr_returning_hdemo_sk : bigint?,
+ wr_returning_addr_sk : bigint?,
+ wr_web_page_sk : bigint?,
+ wr_reason_sk : bigint?,
+ wr_order_number : bigint,
+ wr_return_quantity : bigint?,
+ wr_return_amt : double?,
+ wr_return_tax : double?,
+ wr_return_amt_inc_tax : double?,
+ wr_fee : double?,
+ wr_return_ship_cost: double?,
+ wr_refunded_cash: double?,
+ wr_reversed_charge: double?,
+ wr_account_credit: double?,
+ wr_net_loss: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset store_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number;
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset catalog_returns (catalog_returns_type) primary key cr_item_sk, cr_order_number;
+
+create dataset web_page (web_page_type) primary key wp_web_page_sk;
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
+
+create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.2.update.sqlpp
new file mode 100644
index 0000000..6e356c7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.2.update.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_page using localfs ((`path`=`asterix_nc1://data/tpcds/web_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.3.query.sqlpp
new file mode 100644
index 0000000..05ff05a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q77/q77.3.query.sqlpp
@@ -0,0 +1,115 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+WITH
+ss AS (
+ SELECT s.s_store_sk,
+ SUM(ss.ss_ext_sales_price) AS sales,
+ SUM(ss.ss_net_profit) AS profit
+ FROM store_sales ss, date_dim d, store s
+ WHERE ss.ss_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ AND ss.ss_store_sk = s.s_store_sk
+ GROUP BY s.s_store_sk
+),
+sr AS (
+ SELECT s.s_store_sk,
+ SUM(sr.sr_return_amt) AS returns,
+ SUM(sr.sr_net_loss) AS profit_loss
+ FROM store_returns sr, date_dim d, store s
+ WHERE sr.sr_returned_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ AND sr.sr_store_sk = s.s_store_sk
+ GROUP BY s.s_store_sk
+),
+cs AS (
+ SELECT cs.cs_call_center_sk,
+ SUM(cs.cs_ext_sales_price) AS sales,
+ SUM(cs.cs_net_profit) AS profit
+ FROM catalog_sales cs, date_dim d
+ WHERE cs.cs_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ GROUP BY cs.cs_call_center_sk
+),
+cr AS (
+ SELECT cr.cr_call_center_sk,
+ SUM(cr.cr_return_amount) AS returns,
+ SUM(cr.cr_net_loss) AS profit_loss
+ FROM catalog_returns cr, date_dim d
+ WHERE cr.cr_returned_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ GROUP by cr.cr_call_center_sk
+),
+ws AS (
+ SELECT wp.wp_web_page_sk,
+ SUM(ws.ws_ext_sales_price) AS sales,
+ SUM(ws.ws_net_profit) AS profit
+ FROM web_sales ws, date_dim d, web_page wp
+ WHERE ws.ws_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ AND ws.ws_web_page_sk = wp.wp_web_page_sk
+ GROUP BY wp.wp_web_page_sk
+),
+wr AS (
+ SELECT wp.wp_web_page_sk,
+ SUM(wr.wr_return_amt) AS returns,
+ SUM(wr.wr_net_loss) AS profit_loss
+ FROM web_returns wr, date_dim d, web_page wp
+ WHERE wr.wr_returned_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1999-01-01') AND (date('1999-01-01') + duration('P1Y'))
+ AND wr.wr_web_page_sk = wp.wp_web_page_sk
+ GROUP BY wp.wp_web_page_sk
+)
+
+SELECT channel,
+ id,
+ ROUND(SUM(sales), 2) AS sales,
+ ROUND(SUM(returns), 2) AS returns,
+ ROUND(SUM(profit), 2) AS profit
+FROM (
+ SELECT 'store channel' AS channel,
+ ss.s_store_sk AS id,
+ ss.sales,
+ COALESCE(sr.returns, 0.0) AS returns,
+ (ss.profit - COALESCE(sr.profit_loss, 0.0)) AS profit
+ FROM ss LEFT JOIN sr ON ss.s_store_sk = sr.s_store_sk
+
+ UNION ALL
+
+ SELECT 'catalog channel' AS channel,
+ cs.cs_call_center_sk AS id,
+ cs.sales,
+ cr.returns,
+ (cs.profit - cr.profit_loss) AS profit
+ FROM cs LEFT JOIN cr ON cs.cs_call_center_sk = cr.cr_call_center_sk
+
+ UNION ALL
+
+ SELECT 'web channel' AS channel,
+ ws.wp_web_page_sk AS id,
+ ROUND(ws.sales, 2) AS sales,
+ ROUND(COALESCE(wr.returns, 0.0), 2) AS returns,
+ ROUND(ws.profit - COALESCE(wr.profit_loss, 0.0), 2) AS profit
+ FROM ws LEFT JOIN wr ON ws.wp_web_page_sk = wr.wp_web_page_sk
+) x
+GROUP BY ROLLUP(channel, id)
+ORDER BY channel, id
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.1.ddl.sqlpp
new file mode 100644
index 0000000..a720685
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.1.ddl.sqlpp
@@ -0,0 +1,410 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+};
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+};
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : int64?,
+ sr_return_time_sk : int64?,
+ sr_item_sk : int64,
+ sr_customer_sk : int64?,
+ sr_cdemo_sk : int64?,
+ sr_hdemo_sk : int64?,
+ sr_addr_sk : int64?,
+ sr_store_sk : int64?,
+ sr_reason_sk : int64?,
+ sr_ticket_number : int64,
+ sr_return_quantity : int64?,
+ sr_return_amt : double,
+ sr_return_tax : double?,
+ sr_return_amt_inc_tax : double?,
+ sr_fee : double?,
+ sr_return_ship_cost : double?,
+ sr_refunded_cash : double?,
+ sr_reversed_charge : double?,
+ sr_store_credit : double?,
+ sr_net_loss : double?
+};
+
+create type tpcds.catalog_sales_type as
+ closed {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+};
+
+create type tpcds.catalog_returns_type as
+ closed {
+ cr_returned_date_sk : bigint?,
+ cr_returned_time_sk : bigint?,
+ cr_item_sk : bigint,
+ cr_refunded_customer_sk : bigint?,
+ cr_refunded_cdemo_sk : bigint?,
+ cr_refunded_hdemo_sk : bigint?,
+ cr_refunded_addr_sk : bigint?,
+ cr_returning_customer_sk : bigint?,
+ cr_returning_cdemo_sk : bigint?,
+ cr_returning_hdemo_sk : bigint?,
+ cr_returning_addr_sk : bigint?,
+ cr_call_center_sk : bigint?,
+ cr_catalog_page_sk : bigint?,
+ cr_ship_mode_sk : bigint?,
+ cr_warehouse_sk : bigint?,
+ cr_reason_sk : bigint?,
+ cr_order_number : bigint,
+ cr_return_quantity : bigint?,
+ cr_return_amount : double?,
+ cr_return_tax : double?,
+ cr_return_amt_inc_tax : double?,
+ cr_fee : double?,
+ cr_return_ship_cost : double?,
+ cr_refunded_cash : double?,
+ cr_reversed_charge : double?,
+ cr_store_credit : double?,
+ cr_net_loss : double?
+};
+
+create type tpcds.web_page_type as
+ closed {
+ wp_web_page_sk : int64,
+ wp_web_page_id : string,
+ wp_rec_start_date : string?,
+ wp_rec_end_date : string?,
+ wp_creation_date_sk : int64?,
+ wp_access_date_sk : int64?,
+ wp_autogen_flag : string?,
+ wp_customer_sk : int64?,
+ wp_url : string?,
+ wp_type : string?,
+ wp_char_count : int64?,
+ wp_link_count : int64?,
+ wp_image_count : int64?,
+ wp_max_ad_count : int64?
+};
+
+create type tpcds.web_sales_type as
+ closed {
+ ws_sold_date_sk : int64?,
+ ws_sold_time_sk : int64?,
+ ws_ship_date_sk : int64?,
+ ws_item_sk : int64,
+ ws_bill_customer_sk : int64?,
+ ws_bill_cdemo_sk : int64?,
+ ws_bill_hdemo_sk : int64?,
+ ws_bill_addr_sk : int64?,
+ ws_ship_customer_sk : int64?,
+ ws_ship_cdemo_sk : int64?,
+ ws_ship_hdemo_sk : int64?,
+ ws_ship_addr_sk : int64?,
+ ws_web_page_sk : int64?,
+ ws_web_site_sk : int64?,
+ ws_ship_mode_sk : int64?,
+ ws_warehouse_sk : int64?,
+ ws_promo_sk : int64?,
+ ws_order_number : int64,
+ ws_quantity : int64?,
+ ws_wholesale_cost : double?,
+ ws_list_price : double?,
+ ws_sales_price : double?,
+ ws_ext_discount_amt : double?,
+ ws_ext_sales_price : double?,
+ ws_ext_wholesale_cost : double?,
+ ws_ext_list_price : double?,
+ ws_ext_tax : double?,
+ ws_coupon_amt : double?,
+ ws_ext_ship_cost : double?,
+ ws_net_paid : double?,
+ ws_net_paid_inc_tax : double?,
+ ws_net_paid_inc_ship : double?,
+ ws_net_paid_inc_ship_tax : double?,
+ ws_net_profit : double?
+};
+
+create type tpcds.web_returns_type as
+ closed {
+ wr_returned_date_sk : bigint?,
+ wr_returned_time_sk : bigint?,
+ wr_item_sk : bigint,
+ wr_refunded_customer_sk : bigint?,
+ wr_refunded_cdemo_sk : bigint?,
+ wr_refunded_hdemo_sk : bigint?,
+ wr_refunded_addr_sk : bigint?,
+ wr_returning_customer_sk : bigint?,
+ wr_returning_cdemo_sk : bigint?,
+ wr_returning_hdemo_sk : bigint?,
+ wr_returning_addr_sk : bigint?,
+ wr_web_page_sk : bigint?,
+ wr_reason_sk : bigint?,
+ wr_order_number : bigint,
+ wr_return_quantity : bigint?,
+ wr_return_amt : double?,
+ wr_return_tax : double?,
+ wr_return_amt_inc_tax : double?,
+ wr_fee : double?,
+ wr_return_ship_cost: double?,
+ wr_refunded_cash: double?,
+ wr_reversed_charge: double?,
+ wr_account_credit: double?,
+ wr_net_loss: double?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.promotion_type as
+ closed {
+ p_promo_sk : bigint,
+ p_promo_id : string,
+ p_start_date_sk : bigint?,
+ p_end_date_sk : bigint?,
+ p_item_sk : bigint?,
+ p_cost : double?,
+ p_response_target : bigint?,
+ p_promo_name : string?,
+ p_channel_dmail : string?,
+ p_channel_email : string?,
+ p_channel_catalog : string?,
+ p_channel_tv : string?,
+ p_channel_radio : string?,
+ p_channel_press : string?,
+ p_channel_event : string?,
+ p_channel_demo : string?,
+ p_channel_details : string?,
+ p_purpose : string?,
+ p_discount_active : string?
+};
+
+create type tpcds.web_site_type as
+ closed {
+ web_site_sk: bigint,
+ web_site_id: string,
+ web_rec_start_date: string?,
+ web_rec_end_date: string?,
+ web_name: string?,
+ web_open_date_sk: bigint?,
+ web_close_date_sk: bigint?,
+ web_class: string?,
+ web_manager: string?,
+ web_mkt_id: bigint?,
+ web_mkt_class: string?,
+ web_mkt_desc: string?,
+ web_market_manager: string?,
+ web_company_id: bigint?,
+ web_company_name: string?,
+ web_street_number: string?,
+ web_street_name: string?,
+ web_street_type: string?,
+ web_suite_number: string?,
+ web_city: string?,
+ web_county: string?,
+ web_state: string?,
+ web_zip: string?,
+ web_country: string?,
+ web_gmt_offset: double?,
+ web_tax_percentage: double?
+};
+
+create type tpcds.catalog_page_type as
+ closed {
+ cp_catalog_page_sk: bigint,
+ cp_catalog_page_id: string,
+ cp_start_date_sk: bigint?,
+ cp_end_date_sk: bigint?,
+ cp_department: string?,
+ cp_catalog_number: bigint?,
+ cp_catalog_page_number: bigint?,
+ cp_description: string?,
+ cp_type: string?
+};
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset store_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number;
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset catalog_returns (catalog_returns_type) primary key cr_item_sk, cr_order_number;
+
+create dataset web_page (web_page_type) primary key wp_web_page_sk;
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
+
+create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset promotion (promotion_type) primary key p_promo_sk;
+
+create dataset web_site (web_site_type) primary key web_site_sk;
+
+create dataset catalog_page (catalog_page_type) primary key cp_catalog_page_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.2.update.sqlpp
new file mode 100644
index 0000000..795b065
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.2.update.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_page using localfs ((`path`=`asterix_nc1://data/tpcds/web_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset promotion using localfs ((`path`=`asterix_nc1://data/tpcds/promotion.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_site using localfs ((`path`=`asterix_nc1://data/tpcds/web_site.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset catalog_page using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.3.query.sqlpp
new file mode 100644
index 0000000..09165e3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q80/q80.3.query.sqlpp
@@ -0,0 +1,109 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+WITH
+ssr AS (
+ SELECT s.s_store_id AS store_id,
+ SUM(ss.ss_ext_sales_price) AS sales,
+ SUM(COALESCE(sr.sr_return_amt, 0.0)) AS returns,
+ SUM(ss.ss_net_profit - COALESCE(sr.sr_net_loss, 0.0)) AS profit
+ FROM store_sales ss
+ LEFT OUTER JOIN store_returns sr ON (ss.ss_item_sk = sr.sr_item_sk AND ss.ss_ticket_number = sr.sr_ticket_number),
+ date_dim d, store s, item i, promotion p
+ WHERE ss.ss_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND ss.ss_store_sk = s.s_store_sk
+ AND ss.ss_item_sk = i.i_item_sk
+ AND i.i_current_price > 1
+ AND ss.ss_promo_sk = p.p_promo_sk
+ AND p.p_channel_tv = 'N'
+ GROUP BY s.s_store_id
+),
+
+csr AS (
+ SELECT cp.cp_catalog_page_id AS catalog_page_id,
+ SUM(cs.cs_ext_sales_price) AS sales,
+ SUM(COALESCE(cr.cr_return_amount, 0.0)) AS returns,
+ SUM(cs.cs_net_profit - COALESCE(cr.cr_net_loss, 0.0)) AS profit
+ FROM catalog_sales cs
+ LEFT OUTER JOIN catalog_returns cr ON (cs.cs_item_sk = cr.cr_item_sk AND cs.cs_order_number = cr.cr_order_number),
+ date_dim d, catalog_page cp, item i, promotion p
+ WHERE cs.cs_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND cs.cs_catalog_page_sk = cp.cp_catalog_page_sk
+ AND cs.cs_item_sk = i.i_item_sk
+ AND i.i_current_price > 1
+ AND cs.cs_promo_sk = p.p_promo_sk
+ AND p.p_channel_tv = 'N'
+ GROUP BY cp.cp_catalog_page_id
+),
+
+wsr AS (
+ SELECT web.web_site_id,
+ SUM(ws.ws_ext_sales_price) AS sales,
+ SUM(COALESCE(wr.wr_return_amt, 0.0)) AS returns,
+ SUM(ws.ws_net_profit - COALESCE(wr.wr_net_loss, 0.0)) AS profit
+ FROM web_sales ws
+ LEFT OUTER JOIN web_returns wr ON (ws.ws_item_sk = wr.wr_item_sk AND ws.ws_order_number = wr.wr_order_number),
+ date_dim d, web_site web, item i, promotion p
+ WHERE ws.ws_sold_date_sk = d.d_date_sk
+ AND date(d.d_date) BETWEEN date('1998-01-01') AND (date('1998-01-01') + duration('P5Y'))
+ AND ws.ws_web_site_sk = web.web_site_sk
+ AND ws.ws_item_sk = i.i_item_sk
+ AND i.i_current_price > 1
+ AND ws.ws_promo_sk = p.p_promo_sk
+ AND p.p_channel_tv = 'N'
+ GROUP BY web.web_site_id
+)
+
+SELECT channel, id,
+ ROUND(SUM(sales), 2) AS sales,
+ ROUND(SUM(returns), 2) AS returns,
+ ROUND(SUM(profit), 2) AS profit
+FROM (
+ SELECT 'store channel' AS channel,
+ 'store' || store_id AS id,
+ sales,
+ returns,
+ profit
+ FROM ssr
+
+ UNION ALL
+
+ SELECT 'catalog channel' AS channel,
+ 'catalog_page' || catalog_page_id AS id,
+ sales,
+ returns,
+ profit
+ FROM csr
+
+ UNION ALL
+
+ SELECT 'web channel' AS channel,
+ 'web_site' || web_site_id AS id,
+ ROUND(sales, 2) AS sales,
+ ROUND(returns, 2) AS returns,
+ ROUND(profit, 2) AS profit
+ FROM wsr
+) x
+GROUP BY ROLLUP (channel, id)
+ORDER BY channel, id
+LIMIT 100;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.1.ddl.sqlpp
new file mode 100644
index 0000000..0cee82b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.1.ddl.sqlpp
@@ -0,0 +1,125 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+create type tpcds.web_sales_type as
+ closed {
+ ws_sold_date_sk : int64?,
+ ws_sold_time_sk : int64?,
+ ws_ship_date_sk : int64?,
+ ws_item_sk : int64,
+ ws_bill_customer_sk : int64?,
+ ws_bill_cdemo_sk : int64?,
+ ws_bill_hdemo_sk : int64?,
+ ws_bill_addr_sk : int64?,
+ ws_ship_customer_sk : int64?,
+ ws_ship_cdemo_sk : int64?,
+ ws_ship_hdemo_sk : int64?,
+ ws_ship_addr_sk : int64?,
+ ws_web_page_sk : int64?,
+ ws_web_site_sk : int64?,
+ ws_ship_mode_sk : int64?,
+ ws_warehouse_sk : int64?,
+ ws_promo_sk : int64?,
+ ws_order_number : int64,
+ ws_quantity : int64?,
+ ws_wholesale_cost : double?,
+ ws_list_price : double?,
+ ws_sales_price : double?,
+ ws_ext_discount_amt : double?,
+ ws_ext_sales_price : double?,
+ ws_ext_wholesale_cost : double?,
+ ws_ext_list_price : double?,
+ ws_ext_tax : double?,
+ ws_coupon_amt : double?,
+ ws_ext_ship_cost : double?,
+ ws_net_paid : double?,
+ ws_net_paid_inc_tax : double?,
+ ws_net_paid_inc_ship : double?,
+ ws_net_paid_inc_ship_tax : double?,
+ ws_net_profit : double?
+};
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.2.update.sqlpp
new file mode 100644
index 0000000..33b61cd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.2.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`),(`null`=``));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.3.query.sqlpp
new file mode 100644
index 0000000..c47fba0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q86/q86.3.query.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+SELECT
+ ROUND(SUM(ws.ws_net_paid), 2) AS total_sum,
+ i.i_category,
+ i.i_class,
+ GROUPING(i.i_category) + GROUPING(i.i_class) AS lochierarchy,
+ RANK() OVER (
+ PARTITION BY GROUPING(i.i_category) + GROUPING(i.i_class),
+ CASE WHEN GROUPING(i.i_class) = 0 THEN i.i_category END
+ ORDER BY SUM(ws.ws_net_paid) DESC
+ ) AS rank_within_parent
+ FROM web_sales ws, date_dim d, item i
+ WHERE d.d_month_seq between 1000 and 2000
+ AND d.d_date_sk = ws.ws_sold_date_sk
+ AND i.i_item_sk = ws.ws_item_sk
+ GROUP BY ROLLUP(i.i_category, i.i_class)
+ ORDER BY
+ lochierarchy DESC,
+ CASE WHEN lochierarchy = 0 THEN i.i_category END,
+ rank_within_parent
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q05/q05.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q05/q05.1.adm
new file mode 100644
index 0000000..ccf54a5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q05/q05.1.adm
@@ -0,0 +1,15 @@
+{ "channel": null, "id": null, "sales": 92502.1, "returns": 20611.78, "profit": -44495.08 }
+{ "channel": "catalog channel", "id": null, "sales": 34305.02, "returns": 0, "profit": 3323.76 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAABAAAAAAA", "sales": 20382.0, "returns": 0, "profit": 6726.16 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAGODAAAAA", "sales": 2799.03, "returns": 0, "profit": -4495.31 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAHGFBAAAA", "sales": 6573.16, "returns": 0, "profit": -680.23 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAKKPAAAAA", "sales": 1362.3, "returns": 0, "profit": 337.82 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAPNHAAAAA", "sales": 3188.53, "returns": 0, "profit": 1435.32 }
+{ "channel": "store channel", "id": null, "sales": 34634.58, "returns": 16009.26, "profit": -31862.3 }
+{ "channel": "store channel", "id": "storeAAAAAAAABAAAAAAA", "sales": 14371.21, "returns": 1639.07, "profit": -10779.36 }
+{ "channel": "store channel", "id": "storeAAAAAAAACAAAAAAA", "sales": 17291.82, "returns": 12466.15, "profit": -17067.08 }
+{ "channel": "store channel", "id": "storeAAAAAAAAEAAAAAAA", "sales": 2971.55, "returns": 1904.04, "profit": -4015.86 }
+{ "channel": "web channel", "id": null, "sales": 23562.5, "returns": 4602.52, "profit": -15956.54 }
+{ "channel": "web channel", "id": "web_siteAAAAAAAABAAAAAAA", "sales": 2697.0, "returns": 0.0, "profit": -3422.4 }
+{ "channel": "web channel", "id": "web_siteAAAAAAAACAAAAAAA", "sales": 10639.24, "returns": 946.44, "profit": -5474.67 }
+{ "channel": "web channel", "id": "web_siteAAAAAAAAEAAAAAAA", "sales": 10226.26, "returns": 3656.08, "profit": -7059.47 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q15/q15.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q15/q15.1.adm
index cdb4592..d587183 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q15/q15.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q15/q15.1.adm
@@ -1 +1 @@
-{ "ca_zip": "35708", "$1": 108.92 }
\ No newline at end of file
+{ "ca_zip": "35708", "$1": 75.15 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.1.adm
new file mode 100644
index 0000000..f8abab3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.1.adm
@@ -0,0 +1,5 @@
+{ "i_item_id": null, "ca_country": null, "ca_state": null, "ca_county": null, "agg1": 94.0, "agg2": 105.88, "agg3": 0.0, "agg4": 530.82, "agg5": 889.24, "agg6": null, "agg7": 0.0 }
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "ca_country": null, "ca_state": null, "ca_county": null, "agg1": 94.0, "agg2": 105.88, "agg3": 0.0, "agg4": 530.82, "agg5": 889.24, "agg6": null, "agg7": 0.0 }
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "ca_country": "United States", "ca_state": null, "ca_county": null, "agg1": 94.0, "agg2": 105.88, "agg3": 0.0, "agg4": 530.82, "agg5": 889.24, "agg6": null, "agg7": 0.0 }
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "ca_country": "United States", "ca_state": "WA", "ca_county": null, "agg1": 94.0, "agg2": 105.88, "agg3": 0.0, "agg4": 530.82, "agg5": 889.24, "agg6": null, "agg7": 0.0 }
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "ca_country": "United States", "ca_state": "WA", "ca_county": "Bledsoe County", "agg1": 94.0, "agg2": 105.88, "agg3": 0.0, "agg4": 530.82, "agg5": 889.24, "agg6": null, "agg7": 0.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm
index 3cc1b0b..ef84a63 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm
@@ -1,2 +1,3 @@
-{ "i_item_id": "AAAAAAAACAAAAAAA", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_category": "Home", "i_class": "decor", "i_current_price": 7.11, "itemrevenue": 99.71, "revenueratio": 100.0 }
-{ "i_item_id": "AAAAAAAACAAAAAAA", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_category": "Women", "i_class": "dresses", "i_current_price": 1.12, "itemrevenue": 2139.4, "revenueratio": 100.0 }
\ No newline at end of file
+{ "i_item_id": "AAAAAAAACAAAAAAA", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_category": "Home", "i_class": "decor", "i_current_price": 7.11, "itemrevenue": 3078.4, "revenueratio": 100.0 }
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "i_item_desc": "Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D", "i_category": "Men", "i_class": "shirts", "i_current_price": 1.3, "itemrevenue": 5333.21, "revenueratio": 100.0 }
+{ "i_item_id": "AAAAAAAACAAAAAAA", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_category": "Women", "i_class": "dresses", "i_current_price": 1.12, "itemrevenue": 396.45, "revenueratio": 100.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q22/q22.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q22/q22.1.adm
new file mode 100644
index 0000000..824c9d0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q22/q22.1.adm
@@ -0,0 +1,21 @@
+{ "i_product_name": "pri", "i_brand": null, "i_class": null, "i_category": null, "qoh": 24.0 }
+{ "i_product_name": "pri", "i_brand": "brandbrand #4", "i_class": null, "i_category": null, "qoh": 24.0 }
+{ "i_product_name": "pri", "i_brand": "brandbrand #4", "i_class": "decor", "i_category": null, "qoh": 24.0 }
+{ "i_product_name": "pri", "i_brand": "brandbrand #4", "i_class": "decor", "i_category": "Home", "qoh": 24.0 }
+{ "i_product_name": "anti", "i_brand": null, "i_class": null, "i_category": null, "qoh": 200.0 }
+{ "i_product_name": "anti", "i_brand": "importoimporto #2", "i_class": null, "i_category": null, "qoh": 200.0 }
+{ "i_product_name": "anti", "i_brand": "importoimporto #2", "i_class": "shirts", "i_category": null, "qoh": 200.0 }
+{ "i_product_name": "anti", "i_brand": "importoimporto #2", "i_class": "shirts", "i_category": "Men", "qoh": 200.0 }
+{ "i_product_name": "ought", "i_brand": null, "i_class": null, "i_category": null, "qoh": 405.0 }
+{ "i_product_name": "ought", "i_brand": "exportischolar #2", "i_class": null, "i_category": null, "qoh": 405.0 }
+{ "i_product_name": "ought", "i_brand": "exportischolar #2", "i_class": "pop", "i_category": null, "qoh": 405.0 }
+{ "i_product_name": "ought", "i_brand": "exportischolar #2", "i_class": "pop", "i_category": "Music", "qoh": 405.0 }
+{ "i_product_name": null, "i_brand": null, "i_class": null, "i_category": null, "qoh": 441.6 }
+{ "i_product_name": "n st", "i_brand": null, "i_class": null, "i_category": null, "qoh": 745.0 }
+{ "i_product_name": "n st", "i_brand": "edu packamalg #2", "i_class": null, "i_category": null, "qoh": 745.0 }
+{ "i_product_name": "n st", "i_brand": "edu packamalg #2", "i_class": "swimwear", "i_category": null, "qoh": 745.0 }
+{ "i_product_name": "n st", "i_brand": "edu packamalg #2", "i_class": "swimwear", "i_category": "Women", "qoh": 745.0 }
+{ "i_product_name": "barought", "i_brand": null, "i_class": null, "i_category": null, "qoh": 834.0 }
+{ "i_product_name": "barought", "i_brand": "namelessunivamalg #11", "i_class": null, "i_category": null, "qoh": 834.0 }
+{ "i_product_name": "barought", "i_brand": "namelessunivamalg #11", "i_class": "scanners", "i_category": null, "qoh": 834.0 }
+{ "i_product_name": "barought", "i_brand": "namelessunivamalg #11", "i_class": "scanners", "i_category": "Electronics", "qoh": 834.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q26/q26.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q26/q26.1.adm
index 7fc4a51..95f489b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q26/q26.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q26/q26.1.adm
@@ -1 +1 @@
-{ "i_item_id": "AAAAAAAAKAAAAAAA", "agg1": 7.0, "agg2": 40.88, "agg3": 11.56, "agg4": 51.91 }
\ No newline at end of file
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "agg1": 31.0, "agg2": 51.91, "agg3": 0.0, "agg4": 6.22 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q27/q27.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q27/q27.1.adm
new file mode 100644
index 0000000..fbe8c57
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q27/q27.1.adm
@@ -0,0 +1,13 @@
+{ "i_item_id": null, "s_state": null, "g_state": 1, "agg1": 97.4, "agg2": 14.92, "agg3": 4.6, "agg4": 7.32 }
+{ "i_item_id": "AAAAAAAAABAAAAAA", "s_state": null, "g_state": 1, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAABAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAACAAAAAAA", "s_state": null, "g_state": 1, "agg1": 2.0, "agg2": 101.17, "agg3": 46.03, "agg4": 41.47 }
+{ "i_item_id": "AAAAAAAACAAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 2.0, "agg2": 101.17, "agg3": 46.03, "agg4": 41.47 }
+{ "i_item_id": "AAAAAAAADBAAAAAA", "s_state": null, "g_state": 1, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAADBAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAEBAAAAAA", "s_state": null, "g_state": 1, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAEBAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAGBAAAAAA", "s_state": null, "g_state": 1, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAGBAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAKAAAAAAA", "s_state": null, "g_state": 1, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
+{ "i_item_id": "AAAAAAAAKAAAAAAA", "s_state": "TN", "g_state": 0, "agg1": 108.0, "agg2": 5.34, "agg3": 0.0, "agg4": 3.52 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q36/q36.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q36/q36.1.adm
new file mode 100644
index 0000000..80a52f0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q36/q36.1.adm
@@ -0,0 +1,19 @@
+{ "gross_margin": -0.71, "i_category": null, "i_class": null, "lochierarchy": 2, "rank_within_parent": 1 }
+{ "gross_margin": -19.71, "i_category": "Music", "i_class": null, "lochierarchy": 1, "rank_within_parent": 1 }
+{ "gross_margin": -0.56, "i_category": "Women", "i_class": null, "lochierarchy": 1, "rank_within_parent": 2 }
+{ "gross_margin": -0.3, "i_category": "Electronics", "i_class": null, "lochierarchy": 1, "rank_within_parent": 3 }
+{ "gross_margin": -0.3, "i_category": "Jewelry", "i_class": null, "lochierarchy": 1, "rank_within_parent": 3 }
+{ "gross_margin": -0.29, "i_category": "Men", "i_class": null, "lochierarchy": 1, "rank_within_parent": 5 }
+{ "gross_margin": -0.3, "i_category": "Electronics", "i_class": "karoke", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -0.3, "i_category": "Electronics", "i_class": "scanners", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -0.3, "i_category": "Jewelry", "i_class": "consignment", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -33.36, "i_category": "Men", "i_class": "shirts", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -0.3, "i_category": "Men", "i_class": "accessories", "lochierarchy": 0, "rank_within_parent": 2 }
+{ "gross_margin": -0.3, "i_category": "Men", "i_class": "sports-apparel", "lochierarchy": 0, "rank_within_parent": 2 }
+{ "gross_margin": 0.0, "i_category": "Men", "i_class": "pants", "lochierarchy": 0, "rank_within_parent": 4 }
+{ "gross_margin": -298.29, "i_category": "Music", "i_class": "country", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -3.53, "i_category": "Music", "i_class": "pop", "lochierarchy": 0, "rank_within_parent": 2 }
+{ "gross_margin": null, "i_category": "Women", "i_class": "swimwear", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "gross_margin": -0.56, "i_category": "Women", "i_class": "dresses", "lochierarchy": 0, "rank_within_parent": 2 }
+{ "gross_margin": -0.3, "i_category": "Women", "i_class": "fragrances", "lochierarchy": 0, "rank_within_parent": 3 }
+{ "gross_margin": -0.3, "i_category": "Women", "i_class": "maternity", "lochierarchy": 0, "rank_within_parent": 3 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm
index e94c4ac..1959e09 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm
@@ -1,4 +1,7 @@
-{ "channel": "catalog", "item": 10, "return_ratio": 2.43, "return_rank": 1, "currency_rank": 2 }
-{ "channel": "catalog", "item": 1, "return_ratio": 3.0, "return_rank": 2, "currency_rank": 1 }
-{ "channel": "store", "item": 13, "return_ratio": 9.0, "return_rank": 1, "currency_rank": 1 }
-{ "channel": "web", "item": 25, "return_ratio": 2.67, "return_rank": 1, "currency_rank": 1 }
\ No newline at end of file
+{ "channel": "catalog", "item": 5, "return_ratio": 2.32, "return_rank": 1, "currency_rank": 1 }
+{ "channel": "store", "item": 15, "return_ratio": 0.08, "return_rank": 1, "currency_rank": 2 }
+{ "channel": "store", "item": 5, "return_ratio": 0.3, "return_rank": 2, "currency_rank": 3 }
+{ "channel": "store", "item": 13, "return_ratio": 9.0, "return_rank": 3, "currency_rank": 1 }
+{ "channel": "web", "item": 5, "return_ratio": 0.18, "return_rank": 1, "currency_rank": 1 }
+{ "channel": "web", "item": 25, "return_ratio": 0.35, "return_rank": 2, "currency_rank": 2 }
+{ "channel": "web", "item": 3, "return_ratio": 4.33, "return_rank": 3, "currency_rank": 3 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm
index e8ef0c1..fccb18d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm
@@ -1 +1 @@
-{ "i_category": "Home", "i_brand": "brandbrand #4", "cc_name": "NY Metro", "d_year": 2000, "d_moy": 5, "avg_monthly_sales": 108.92, "sum_sales": 108.92, "psum": 84.42, "nsum": 153.03 }
\ No newline at end of file
+{ "i_category": "Women", "i_brand": "amalgamalg #1", "cc_name": "NY Metro", "d_year": 2001, "d_moy": 12, "avg_monthly_sales": 8.81, "sum_sales": 8.81, "psum": 5.51, "nsum": 2.77 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q67/q67.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q67/q67.1.adm
new file mode 100644
index 0000000..2125ebe
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q67/q67.1.adm
@@ -0,0 +1,47 @@
+{ "i_category": null, "i_class": null, "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 6144.62 }
+{ "i_category": "Home", "i_class": null, "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": "pri", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": "pri", "d_year": 1998, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": "pri", "d_year": 1998, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": "pri", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": null, "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Home", "i_class": "decor", "i_brand": "brandbrand #4", "i_product_name": "pri", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": "AAAAAAAACAAAAAAA", "rk": 1, "sumsales": 699.3 }
+{ "i_category": "Jewelry", "i_class": null, "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 3735.3 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": "priought", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": "priought", "d_year": 1998, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": "priought", "d_year": 1998, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": "priought", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": null, "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "costume", "i_brand": "importobrand #6", "i_product_name": "priought", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": "AAAAAAAAEAAAAAAA", "rk": 9, "sumsales": 251.94 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": "oughtought", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": "oughtought", "d_year": 2001, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": "oughtought", "d_year": 2001, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": "oughtought", "d_year": 2001, "d_qoy": 1, "d_moy": 12, "s_store_id": null, "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Jewelry", "i_class": "estate", "i_brand": "edu packbrand #4", "i_product_name": "oughtought", "d_year": 2001, "d_qoy": 1, "d_moy": 12, "s_store_id": "AAAAAAAACAAAAAAA", "rk": 2, "sumsales": 3483.36 }
+{ "i_category": "Men", "i_class": null, "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 1685.62 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": "cally", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": "cally", "d_year": 1999, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": "cally", "d_year": 1999, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": "cally", "d_year": 1999, "d_qoy": 1, "d_moy": 11, "s_store_id": null, "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "pants", "i_brand": "exportiimporto #1", "i_product_name": "cally", "d_year": 1999, "d_qoy": 1, "d_moy": 11, "s_store_id": "AAAAAAAACAAAAAAA", "rk": 2, "sumsales": 1678.32 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": "anti", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": "anti", "d_year": 1999, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": "anti", "d_year": 1999, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": "anti", "d_year": 1999, "d_qoy": 1, "d_moy": 11, "s_store_id": null, "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Men", "i_class": "shirts", "i_brand": "importoimporto #2", "i_product_name": "anti", "d_year": 1999, "d_qoy": 1, "d_moy": 11, "s_store_id": "AAAAAAAAEAAAAAAA", "rk": 9, "sumsales": 7.3 }
+{ "i_category": "Women", "i_class": null, "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": null, "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": null, "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": "ese", "d_year": null, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": "ese", "d_year": 1998, "d_qoy": null, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": "ese", "d_year": 1998, "d_qoy": 1, "d_moy": null, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": "ese", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": null, "rk": 1, "sumsales": 24.4 }
+{ "i_category": "Women", "i_class": "Jewelry", "i_brand": "importoexporti #1", "i_product_name": "ese", "d_year": 1998, "d_qoy": 1, "d_moy": 12, "s_store_id": "AAAAAAAAEAAAAAAA", "rk": 1, "sumsales": 24.4 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q70/q70.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q70/q70.1.adm
new file mode 100644
index 0000000..edafb00
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q70/q70.1.adm
@@ -0,0 +1,3 @@
+{ "total_sum": -3169.6, "s_state": null, "s_county": null, "lochierarchy": 2, "rank_within_parent": 1 }
+{ "total_sum": -3169.6, "s_state": "TN", "s_county": null, "lochierarchy": 1, "rank_within_parent": 1 }
+{ "total_sum": -3169.6, "s_state": "TN", "s_county": "Williamson County", "lochierarchy": 0, "rank_within_parent": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q77/q77.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q77/q77.1.adm
new file mode 100644
index 0000000..82b1b8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q77/q77.1.adm
@@ -0,0 +1,12 @@
+{ "channel": null, "id": null, "sales": 18263.2, "returns": 709.19, "profit": -11830.54 }
+{ "channel": "catalog channel", "id": null, "sales": 7959.47, "returns": null, "profit": null }
+{ "channel": "catalog channel", "id": 1, "sales": 4881.07, "returns": null, "profit": null }
+{ "channel": "catalog channel", "id": 4, "sales": 3078.4, "returns": null, "profit": null }
+{ "channel": "store channel", "id": null, "sales": 3605.88, "returns": 574.79, "profit": -6673.96 }
+{ "channel": "store channel", "id": 1, "sales": 0.0, "returns": 347.42, "profit": -597.66 }
+{ "channel": "store channel", "id": 3, "sales": 3384.68, "returns": 0.0, "profit": -5247.1 }
+{ "channel": "store channel", "id": 5, "sales": 221.2, "returns": 227.37, "profit": -829.2 }
+{ "channel": "web channel", "id": null, "sales": 6697.85, "returns": 134.4, "profit": -5156.58 }
+{ "channel": "web channel", "id": 7, "sales": 2546.73, "returns": 0.0, "profit": -2674.29 }
+{ "channel": "web channel", "id": 8, "sales": 2168.28, "returns": 0.0, "profit": -895.53 }
+{ "channel": "web channel", "id": 10, "sales": 1982.84, "returns": 134.4, "profit": -1586.76 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q80/q80.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q80/q80.1.adm
new file mode 100644
index 0000000..46f9e96
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q80/q80.1.adm
@@ -0,0 +1,13 @@
+{ "channel": null, "id": null, "sales": 62214.72, "returns": 4839.2, "profit": -26998.14 }
+{ "channel": "catalog channel", "id": null, "sales": 34305.02, "returns": 2157.76, "profit": 1953.48 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAABAAAAAAA", "sales": 20382.0, "returns": 0.0, "profit": 6726.16 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAGODAAAAA", "sales": 2799.03, "returns": 2157.76, "profit": -5865.59 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAHGFBAAAA", "sales": 6573.16, "returns": 0.0, "profit": -680.23 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAKKPAAAAA", "sales": 1362.3, "returns": 0.0, "profit": 337.82 }
+{ "channel": "catalog channel", "id": "catalog_pageAAAAAAAAPNHAAAAA", "sales": 3188.53, "returns": 0.0, "profit": 1435.32 }
+{ "channel": "store channel", "id": null, "sales": 25741.42, "returns": 2681.44, "profit": -28056.09 }
+{ "channel": "store channel", "id": "storeAAAAAAAABAAAAAAA", "sales": 14371.21, "returns": 37.23, "profit": -10319.54 }
+{ "channel": "store channel", "id": "storeAAAAAAAACAAAAAAA", "sales": 8398.66, "returns": 2416.84, "profit": -15384.3 }
+{ "channel": "store channel", "id": "storeAAAAAAAAEAAAAAAA", "sales": 2971.55, "returns": 227.37, "profit": -2352.25 }
+{ "channel": "web channel", "id": null, "sales": 2168.28, "returns": 0.0, "profit": -895.53 }
+{ "channel": "web channel", "id": "web_siteAAAAAAAAEAAAAAAA", "sales": 2168.28, "returns": 0.0, "profit": -895.53 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q86/q86.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q86/q86.1.adm
new file mode 100644
index 0000000..2811d77
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q86/q86.1.adm
@@ -0,0 +1,10 @@
+{ "total_sum": 6620.01, "i_category": null, "i_class": null, "lochierarchy": 2, "rank_within_parent": 1 }
+{ "total_sum": 2650.56, "i_category": "Books", "i_class": null, "lochierarchy": 1, "rank_within_parent": 1 }
+{ "total_sum": 2568.02, "i_category": "Electronics", "i_class": null, "lochierarchy": 1, "rank_within_parent": 2 }
+{ "total_sum": 1219.57, "i_category": "Children", "i_class": null, "lochierarchy": 1, "rank_within_parent": 3 }
+{ "total_sum": 181.86, "i_category": "Home", "i_class": null, "lochierarchy": 1, "rank_within_parent": 4 }
+{ "total_sum": 2650.56, "i_category": "Books", "i_class": "history", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "total_sum": 1219.57, "i_category": "Children", "i_class": "newborn", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "total_sum": 2180.16, "i_category": "Electronics", "i_class": "musical", "lochierarchy": 0, "rank_within_parent": 1 }
+{ "total_sum": 387.86, "i_category": "Electronics", "i_class": "karoke", "lochierarchy": 0, "rank_within_parent": 2 }
+{ "total_sum": 181.86, "i_category": "Home", "i_class": "decor", "lochierarchy": 0, "rank_within_parent": 1 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 497202f..ebc8ec0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -10585,6 +10585,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q05">
+ <output-dir compare="Text">q05</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q07">
<output-dir compare="Text">q07</output-dir>
</compilation-unit>
@@ -10605,6 +10610,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q18">
+ <output-dir compare="Text">q18</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q19">
<output-dir compare="Text">q19</output-dir>
</compilation-unit>
@@ -10620,6 +10630,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q22">
+ <output-dir compare="Text">q22</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q24a">
<output-dir compare="Text">q24a</output-dir>
</compilation-unit>
@@ -10640,6 +10655,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q27">
+ <output-dir compare="Text">q27</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q29">
<output-dir compare="Text">q29</output-dir>
</compilation-unit>
@@ -10660,6 +10680,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q36">
+ <output-dir compare="Text">q36</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q37">
<output-dir compare="Text">q37</output-dir>
</compilation-unit>
@@ -10730,21 +10755,41 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q67">
+ <output-dir compare="Text">q67</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q68">
<output-dir compare="Text">q68</output-dir>
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q70">
+ <output-dir compare="Text">q70</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q73">
<output-dir compare="Text">q73</output-dir>
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q77">
+ <output-dir compare="Text">q77</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q79">
<output-dir compare="Text">q79</output-dir>
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q80">
+ <output-dir compare="Text">q80</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q81">
<output-dir compare="Text">q81</output-dir>
</compilation-unit>
@@ -10760,6 +10805,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="tpcds">
+ <compilation-unit name="q86">
+ <output-dir compare="Text">q86</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="tpcds">
<compilation-unit name="q88">
<output-dir compare="Text">q88</output-dir>
</compilation-unit>
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/VariableUtilities.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/VariableUtilities.java
index c37e67e..dd7bc34 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/VariableUtilities.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/logical/visitors/VariableUtilities.java
@@ -24,6 +24,7 @@
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
+import java.util.Objects;
import java.util.Set;
import org.apache.commons.lang3.mutable.Mutable;
@@ -167,7 +168,7 @@
boolean goThroughNts, ITypingContext ctx) throws AlgebricksException {
ILogicalOperatorVisitor<Void, Pair<LogicalVariable, LogicalVariable>> visitor =
new SubstituteVariableVisitor(goThroughNts, ctx);
- op.accept(visitor, new Pair<LogicalVariable, LogicalVariable>(v1, v2));
+ op.accept(visitor, new Pair<>(Objects.requireNonNull(v1), Objects.requireNonNull(v2)));
}
public static <T> boolean varListEqualUnordered(List<T> var, List<T> varArg) {
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/plan/PlanStructureVerifier.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/plan/PlanStructureVerifier.java
index 8495099..80a949a 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/plan/PlanStructureVerifier.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/plan/PlanStructureVerifier.java
@@ -21,13 +21,20 @@
import java.util.ArrayDeque;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.Collections;
+import java.util.Comparator;
import java.util.Deque;
+import java.util.HashSet;
import java.util.IdentityHashMap;
+import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
+import java.util.Set;
+import java.util.stream.Collectors;
+import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.mutable.Mutable;
import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
import org.apache.hyracks.algebricks.common.utils.Pair;
@@ -35,7 +42,10 @@
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
import org.apache.hyracks.algebricks.core.algebra.base.ILogicalPlan;
import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
import org.apache.hyracks.algebricks.core.algebra.prettyprint.IPlanPrettyPrinter;
import org.apache.hyracks.algebricks.core.algebra.typing.ITypingContext;
import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
@@ -60,6 +70,13 @@
private static final String ERROR_MESSAGE_TEMPLATE_4 = "missing schema in %s";
+ private static final String ERROR_MESSAGE_TEMPLATE_5 =
+ "produced variables %s that intersect used variables %s on %s in %s";
+
+ private static final String ERROR_MESSAGE_TEMPLATE_6 = "undefined used variables %s in %s";
+
+ public static final Comparator<LogicalVariable> VARIABLE_CMP = Comparator.comparing(LogicalVariable::toString);
+
private final ExpressionReferenceVerifierVisitor exprVisitor = new ExpressionReferenceVerifierVisitor();
private final Map<Mutable<ILogicalOperator>, ILogicalOperator> opRefMap = new IdentityHashMap<>();
@@ -72,6 +89,10 @@
private final Deque<Pair<Mutable<ILogicalOperator>, ILogicalOperator>> workQueue = new ArrayDeque<>();
+ private final Set<LogicalVariable> tmpVarSet1 = new HashSet<>();
+
+ private final Set<LogicalVariable> tmpVarSet2 = new HashSet<>();
+
private final IPlanPrettyPrinter prettyPrinter;
private final ITypingContext typeEnvProvider;
@@ -155,14 +176,9 @@
exprVisitor.setOperator(op);
op.acceptExpressionTransform(exprVisitor);
- if (ensureTypeEnv && typeEnvProvider.getOutputTypeEnvironment(op) == null) {
- throw new AlgebricksException(
- String.format(ERROR_MESSAGE_TEMPLATE_3, PlanStabilityVerifier.printOperator(op, prettyPrinter)));
- }
- if (ensureSchema && op.getSchema() == null) {
- throw new AlgebricksException(
- String.format(ERROR_MESSAGE_TEMPLATE_4, PlanStabilityVerifier.printOperator(op, prettyPrinter)));
- }
+ checkOperatorTypeEnvironment(op);
+ checkOperatorSchema(op);
+ checkOperatorVariables(op);
List<Mutable<ILogicalOperator>> children = op.getInputs();
if (op instanceof AbstractOperatorWithNestedPlans) {
@@ -174,6 +190,78 @@
return children;
}
+ private void checkOperatorTypeEnvironment(ILogicalOperator op) throws AlgebricksException {
+ if (ensureTypeEnv && typeEnvProvider.getOutputTypeEnvironment(op) == null) {
+ throw new AlgebricksException(
+ String.format(ERROR_MESSAGE_TEMPLATE_3, PlanStabilityVerifier.printOperator(op, prettyPrinter)));
+ }
+ }
+
+ private void checkOperatorSchema(ILogicalOperator op) throws AlgebricksException {
+ if (ensureSchema && op.getSchema() == null) {
+ throw new AlgebricksException(
+ String.format(ERROR_MESSAGE_TEMPLATE_4, PlanStabilityVerifier.printOperator(op, prettyPrinter)));
+ }
+ }
+
+ private void checkOperatorVariables(ILogicalOperator op) throws AlgebricksException {
+ if (op instanceof AbstractOperatorWithNestedPlans) {
+ return;
+ }
+
+ tmpVarSet1.clear();
+ VariableUtilities.getUsedVariables(op, tmpVarSet1);
+ if (!tmpVarSet1.isEmpty()) {
+ ensureUsedVarsAreDefined(op, tmpVarSet1);
+ //TODO(dmitry): disabled for now
+ //ensureProducedVarsDisjointFromUsedVars(op, tmpVarSet1);
+ }
+ }
+
+ private void ensureUsedVarsAreDefined(ILogicalOperator op, Collection<LogicalVariable> usedVars)
+ throws AlgebricksException {
+ if (!ensureTypeEnv) {
+ return;
+ }
+
+ tmpVarSet2.clear();
+ tmpVarSet2.addAll(usedVars);
+ Set<LogicalVariable> usedVarsCopy = tmpVarSet2;
+
+ for (Mutable<ILogicalOperator> childRef : op.getInputs()) {
+ ILogicalOperator childOp = childRef.getValue();
+ IVariableTypeEnvironment childOpTypeEnv = typeEnvProvider.getOutputTypeEnvironment(childOp);
+ if (childOpTypeEnv == null) {
+ throw new AlgebricksException(String.format(ERROR_MESSAGE_TEMPLATE_3,
+ PlanStabilityVerifier.printOperator(childOp, prettyPrinter)));
+ }
+ for (Iterator<LogicalVariable> i = usedVarsCopy.iterator(); i.hasNext();) {
+ LogicalVariable usedVar = i.next();
+ if (childOpTypeEnv.getVarType(usedVar) != null) {
+ i.remove();
+ }
+ }
+ }
+ if (!usedVarsCopy.isEmpty()) {
+ throw new AlgebricksException(String.format(ERROR_MESSAGE_TEMPLATE_6, sorted(usedVarsCopy, VARIABLE_CMP),
+ PlanStabilityVerifier.printOperator(op, prettyPrinter)));
+ }
+ }
+
+ private void ensureProducedVarsDisjointFromUsedVars(ILogicalOperator op, Set<LogicalVariable> usedVars)
+ throws AlgebricksException {
+ tmpVarSet2.clear();
+ VariableUtilities.getProducedVariables(op, tmpVarSet2);
+ Set<LogicalVariable> producedVars = tmpVarSet2;
+
+ Collection<LogicalVariable> intersection = CollectionUtils.intersection(producedVars, usedVars);
+ if (!intersection.isEmpty()) {
+ throw new AlgebricksException(String.format(ERROR_MESSAGE_TEMPLATE_5, sorted(producedVars, VARIABLE_CMP),
+ sorted(usedVars, VARIABLE_CMP), sorted(intersection, VARIABLE_CMP),
+ PlanStabilityVerifier.printOperator(op, prettyPrinter)));
+ }
+ }
+
private void raiseException(String sharedReferenceKind, String sharedEntity, ILogicalOperator firstOp,
ILogicalOperator secondOp) throws AlgebricksException {
String errorMessage;
@@ -188,6 +276,10 @@
throw new AlgebricksException(errorMessage);
}
+ private <T> List<T> sorted(Collection<T> inColl, Comparator<T> comparator) {
+ return inColl.stream().sorted(comparator).collect(Collectors.toList());
+ }
+
private final class ExpressionReferenceVerifierVisitor implements ILogicalExpressionReferenceTransform {
private ILogicalOperator currentOp;
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ComplexUnnestToProductRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ComplexUnnestToProductRule.java
index 54fe09d..7edefc7 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ComplexUnnestToProductRule.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ComplexUnnestToProductRule.java
@@ -86,9 +86,8 @@
// The last operator must be an unnest or join.
AbstractLogicalOperator unnestOrJoin = (AbstractLogicalOperator) outerOps.get(outerOps.size() - 1);
- ILogicalOperator outerRoot = null;
- ILogicalOperator innerRoot = null;
- EmptyTupleSourceOperator ets = new EmptyTupleSourceOperator();
+ ILogicalOperator outerRoot;
+ ILogicalOperator innerRoot;
// If we found a join, simply use it as the outer root.
if (unnestOrJoin.getOperatorTag() != LogicalOperatorTag.INNERJOIN
&& unnestOrJoin.getOperatorTag() != LogicalOperatorTag.LEFTOUTERJOIN) {
@@ -110,6 +109,7 @@
return false;
}
}
+ EmptyTupleSourceOperator ets = new EmptyTupleSourceOperator();
innerRoot = buildOperatorChain(innerOps, ets, context);
context.computeAndSetTypeEnvironmentForOperator(innerRoot);
outerRoot = buildOperatorChain(outerOps, null, context);
@@ -213,6 +213,10 @@
}
}
default: {
+ if (op.getInputs().size() > 1) {
+ return false;
+ }
+
// The inner is trivially independent.
if (!belowSecondUnnest && innerUsedVars.isEmpty()) {
outerOps.add(op);