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);