You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@asterixdb.apache.org by "Dmitry Lychagin (Code Review)" <do...@asterixdb.incubator.apache.org> on 2019/01/17 20:22:31 UTC

Change in asterixdb[master]: [NO ISSUE][COMP] Additional tests for window functions

Dmitry Lychagin has uploaded a new change for review.

  https://asterix-gerrit.ics.uci.edu/3121

Change subject: [NO ISSUE][COMP] Additional tests for window functions
......................................................................

[NO ISSUE][COMP] Additional tests for window functions

- user model changes: no
- storage format changes: no
- interface changes: no

Details:
- Add more tests for window functions

Change-Id: I47e3a4eb479754a04afb8bd6a3b14fd341cb7927
---
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.3.query.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q12/q12.1.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q63/q63.1.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q89/q89.1.adm
M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
26 files changed, 1,651 insertions(+), 63 deletions(-)


  git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/21/3121/1

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.1.ddl.sqlpp
new file mode 100644
index 0000000..77de948
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.1.ddl.sqlpp
@@ -0,0 +1,126 @@
+/*
+ * 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.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.web_sales_type as
+ closed {
+  ws_sold_date_sk : bigint?,
+  ws_sold_time_sk : bigint?,
+  ws_ship_date_sk : bigint?,
+  ws_item_sk : bigint,
+  ws_bill_customer_sk : bigint?,
+  ws_bill_cdemo_sk : bigint?,
+  ws_bill_hdemo_sk : bigint?,
+  ws_bill_addr_sk : bigint?,
+  ws_ship_customer_sk : bigint?,
+  ws_ship_cdemo_sk : bigint?,
+  ws_ship_hdemo_sk : bigint?,
+  ws_ship_addr_sk : bigint?,
+  ws_web_page_sk : bigint?,
+  ws_web_site_sk : bigint?,
+  ws_ship_mode_sk : bigint?,
+  ws_warehouse_sk : bigint?,
+  ws_promo_sk : bigint?,
+  ws_order_number : bigint,
+  ws_quantity : bigint?,
+  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 date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item(item_type) primary key i_item_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/q12/q12.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.2.update.sqlpp
new file mode 100644
index 0000000..b8ec434
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.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 date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.3.query.sqlpp
new file mode 100644
index 0000000..c5ff104
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q12/q12.3.query.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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_item_id,
+  i_item_desc,
+  i_category,
+  i_class,
+  i_current_price,
+  SUM(ws.ws_ext_sales_price) AS itemrevenue,
+  SUM(ws.ws_ext_sales_price) * 100 / SUM(SUM(ws.ws_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio
+FROM
+  web_sales ws,
+  item i,
+  date_dim d
+WHERE
+  ws.ws_item_sk = i.i_item_sk
+  AND i.i_category IN ['Home', 'Men', 'Women']
+  AND 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'))
+GROUP BY
+  i.i_item_id,
+  i.i_item_desc,
+  i.i_category,
+  i.i_class,
+  i.i_current_price
+ORDER BY
+  i_category,
+  i_class,
+  i_item_id,
+  i_item_desc,
+  revenueratio
+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
new file mode 100644
index 0000000..3326d7a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.1.ddl.sqlpp
@@ -0,0 +1,124 @@
+/*
+ * 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_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.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 dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item(item_type) primary key i_item_sk;
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
new file mode 100644
index 0000000..a14b6b6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.2.update.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
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
new file mode 100644
index 0000000..28834d3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q20/q20.3.query.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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_item_id,
+  i_item_desc,
+  i_category,
+  i_class,
+  i_current_price,
+  SUM(cs.cs_ext_sales_price) AS itemrevenue,
+  SUM(cs.cs_ext_sales_price) * 100 / SUM(SUM(cs.cs_ext_sales_price)) OVER (PARTITION BY i_class) AS revenueratio
+FROM
+  catalog_sales cs,
+  item i,
+  date_dim d
+WHERE
+  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'))
+GROUP BY
+  i.i_item_id,
+  i.i_item_desc,
+  i.i_category,
+  i.i_class,
+  i.i_current_price
+ORDER BY
+  i_category,
+  i_class,
+  i_item_id,
+  i_item_desc,
+  revenueratio
+LIMIT 100;
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
new file mode 100644
index 0000000..36c7f47
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.1.ddl.sqlpp
@@ -0,0 +1,254 @@
+/*
+ * 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_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.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_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.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_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.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.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.web_sales_type as
+ closed {
+  ws_sold_date_sk : bigint?,
+  ws_sold_time_sk : bigint?,
+  ws_ship_date_sk : bigint?,
+  ws_item_sk : bigint,
+  ws_bill_customer_sk : bigint?,
+  ws_bill_cdemo_sk : bigint?,
+  ws_bill_hdemo_sk : bigint?,
+  ws_bill_addr_sk : bigint?,
+  ws_ship_customer_sk : bigint?,
+  ws_ship_cdemo_sk : bigint?,
+  ws_ship_hdemo_sk : bigint?,
+  ws_ship_addr_sk : bigint?,
+  ws_web_page_sk : bigint?,
+  ws_web_site_sk : bigint?,
+  ws_ship_mode_sk : bigint?,
+  ws_warehouse_sk : bigint?,
+  ws_promo_sk : bigint?,
+  ws_order_number : bigint,
+  ws_quantity : bigint?,
+  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 catalog_returns (catalog_returns_type) primary key cr_item_sk, cr_order_number;
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number;
+
+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/q49/q49.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
new file mode 100644
index 0000000..2ed4e32
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.2.update.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ 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
new file mode 100644
index 0000000..b906174
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q49/q49.3.query.sqlpp
@@ -0,0 +1,134 @@
+/*
+ * 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
+  'web' AS channel,
+  web.item,
+  web.return_ratio,
+  web.return_rank,
+  web.currency_rank
+FROM
+  (SELECT
+    item,
+    return_ratio,
+    currency_ratio,
+    RANK() OVER (ORDER BY return_ratio) AS return_rank,
+    RANK() OVER (ORDER BY currency_ratio) AS currency_rank
+  FROM
+    (SELECT
+      ws.ws_item_sk AS item,
+      ROUND_HALF_TO_EVEN(SUM(COALESCE(wr.wr_return_quantity, 0)) / SUM(COALESCE(ws.ws_quantity, 0)), 2) AS return_ratio,
+      SUM(COALESCE(wr.wr_return_amt, 0)) / SUM(COALESCE(ws.ws_net_paid, 0)) AS currency_ratio
+    FROM
+      web_sales ws
+      LEFT OUTER JOIN web_returns wr ON ( ws.ws_order_number = wr.wr_order_number AND ws.ws_item_sk = wr.wr_item_sk ),
+      date_dim d
+    WHERE
+      wr.wr_return_amt > 100
+      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
+      /* AND d.d_year = 1999 AND d.d_moy = 12*/
+    GROUP BY
+      ws.ws_item_sk
+    ) in_web
+  ) web
+WHERE (web.return_rank <= 10 OR web.currency_rank <= 10)
+
+UNION ALL
+
+SELECT
+  'catalog' AS channel,
+  catalog.item,
+  catalog.return_ratio,
+  catalog.return_rank,
+  catalog.currency_rank
+FROM
+  (SELECT
+    item,
+    return_ratio,
+    currency_ratio,
+    RANK() OVER (ORDER BY return_ratio) AS return_rank,
+    RANK() OVER (ORDER BY currency_ratio) AS currency_rank
+  FROM
+    (SELECT
+      cs.cs_item_sk AS item,
+      ROUND_HALF_TO_EVEN(SUM(COALESCE(cr.cr_return_quantity, 0)) / SUM(COALESCE(cs.cs_quantity, 0)), 2) AS return_ratio,
+      SUM(COALESCE(cr.cr_return_amount, 0)) / SUM(COALESCE(cs.cs_net_paid, 0)) AS currency_ratio
+    FROM
+      catalog_sales cs
+      LEFT OUTER JOIN catalog_returns cr ON ( cs.cs_order_number = cr.cr_order_number AND cs.cs_item_sk = cr.cr_item_sk ),
+      date_dim d
+    WHERE
+      cr.cr_return_amount > 100
+      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*/
+    GROUP BY
+      cs.cs_item_sk
+    ) in_cat
+  ) catalog
+WHERE ( catalog.return_rank <= 10 OR catalog.currency_rank <= 10 )
+
+UNION ALL
+
+SELECT
+  'store' AS channel,
+  store.item,
+  store.return_ratio,
+  store.return_rank,
+  store.currency_rank
+FROM
+  (SELECT
+    item,
+    return_ratio,
+    currency_ratio,
+    RANK() OVER (ORDER BY return_ratio) AS return_rank,
+    RANK() OVER (ORDER BY currency_ratio) AS currency_rank
+  FROM
+    (SELECT
+      sts.ss_item_sk AS item,
+      ROUND_HALF_TO_EVEN(SUM(COALESCE(sr.sr_return_quantity, 0)) / SUM(COALESCE(sts.ss_quantity, 0)), 2) AS return_ratio,
+      SUM(COALESCE(sr.sr_return_amt, 0)) / SUM(COALESCE(sts.ss_net_paid, 0)) AS currency_ratio
+    FROM
+      store_sales sts
+      LEFT OUTER JOIN store_returns sr ON ( sts.ss_ticket_number = sr.sr_ticket_number AND sts.ss_item_sk = sr.sr_item_sk ),
+      date_dim d
+    WHERE
+      sr.sr_return_amt > 100
+      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*/
+    GROUP BY
+      sts.ss_item_sk
+    ) in_store
+  ) store
+WHERE ( store.return_rank <= 10 OR store.currency_rank <= 10 )
+ORDER BY
+  channel,
+  return_rank,
+  currency_rank
+LIMIT 100;
\ No newline at end of file
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
new file mode 100644
index 0000000..8581407
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.1.ddl.sqlpp
@@ -0,0 +1,161 @@
+/*
+ * 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.call_center_type as
+ closed {
+  cc_call_center_sk : bigint,
+  cc_call_center_id : string,
+  cc_rec_start_date : string?,
+  cc_rec_end_date : string?,
+  cc_closed_date_sk : bigint?,
+  cc_open_date_sk : bigint?,
+  cc_name : string?,
+  cc_class : string?,
+  cc_employees : bigint?,
+  cc_sq_ft : bigint?,
+  cc_hours : string?,
+  cc_manager : string?,
+  cc_mkt_id : bigint?,
+  cc_mkt_class : string?,
+  cc_mkt_desc : string?,
+  cc_market_manager : string?,
+  cc_division : bigint?,
+  cc_division_name : string?,
+  cc_company : bigint?,
+  cc_company_name : string?,
+  cc_street_number : bigint?,
+  cc_street_name : string?,
+  cc_street_type : string?,
+  cc_suite_number : string?,
+  cc_city : string?,
+  cc_county : string?,
+  cc_state : string?,
+  cc_zip : string?,
+  cc_country : string?,
+  cc_gmt_offset : double?,
+  cc_tax_percentage : 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_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.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 dataset call_center (call_center_type) primary key cc_call_center_sk;
+
+create dataset catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item(item_type) primary key i_item_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.2.update.sqlpp
new file mode 100644
index 0000000..290b1ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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 call_center using localfs ((`path`=`asterix_nc1://data/tpcds/call_center.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
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
new file mode 100644
index 0000000..802f904
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q57/q57.3.query.sqlpp
@@ -0,0 +1,83 @@
+/*
+ * 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
+  v1 AS (
+  SELECT
+    i_category,
+    i_brand,
+    cc_name,
+    d_year,
+    d_moy,
+    SUM(cs.cs_sales_price) sum_sales,
+    AVG(SUM(cs.cs_sales_price)) OVER (PARTITION BY i_category, i_brand, cc_name, d_year) avg_monthly_sales,
+    RANK() OVER (PARTITION BY i_category, i_brand, cc_name ORDER BY d_year, d_moy) rn
+  FROM
+    item i,
+    catalog_sales cs,
+    date_dim d,
+    call_center cc
+  WHERE
+    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 ) )*/
+  GROUP BY
+    i.i_category,
+    i.i_brand,
+    cc.cc_name,
+    d.d_year,
+    d.d_moy),
+
+  v2 AS (
+  SELECT
+    v1_orig.i_category,
+    v1_orig.i_brand,
+    v1_orig.cc_name,
+    v1_orig.d_year,
+    v1_orig.d_moy,
+    v1_orig.avg_monthly_sales,
+    v1_orig.sum_sales,
+    v1_lag.sum_sales psum,
+    v1_lead.sum_sales nsum
+  FROM
+    v1 v1_orig,
+    v1 v1_lag,
+    v1 v1_lead
+  WHERE
+    v1_orig.i_category = v1_lag.i_category
+    AND v1_orig.i_category = v1_lead.i_category
+    AND v1_orig.i_brand = v1_lag.i_brand
+    AND v1_orig.i_brand = v1_lead.i_brand
+    AND v1_orig.cc_name = v1_lag.cc_name
+    AND v1_orig.cc_name = v1_lead.cc_name
+    AND v1_orig.rn = v1_lag.rn + 1
+    AND v1_orig.rn = v1_lead.rn - 1)
+
+SELECT v2.*
+FROM v2
+WHERE
+  d_year = 2000 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
+  sum_sales - avg_monthly_sales, cc_name
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.1.ddl.sqlpp
new file mode 100644
index 0000000..768ed9b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.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.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.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 dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item(item_type) primary key i_item_sk;
+
+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;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.2.update.sqlpp
new file mode 100644
index 0000000..c2cef59
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.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 date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.3.query.sqlpp
new file mode 100644
index 0000000..c364bb6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q63/q63.3.query.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * 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 tmp1.*
+FROM (
+  SELECT
+    i_manager_id,
+    ROUND_HALF_TO_EVEN(SUM(ss.ss_sales_price), 2) sum_sales,
+    ROUND_HALF_TO_EVEN(AVG(SUM(ss.ss_sales_price)) OVER (PARTITION BY i_manager_id), 2) avg_monthly_sales
+  FROM
+    item i,
+    store_sales ss,
+    date_dim d,
+    store s
+  WHERE
+    ss.ss_item_sk = i.i_item_sk
+    AND ss.ss_sold_date_sk = d.d_date_sk
+    AND ss.ss_store_sk = s.s_store_sk
+    AND d.d_month_seq IN [0]
+    AND ( ( i.i_category IN ['Books', 'Children', 'Electronics']
+      /*AND i.i_class IN ['history', 'toddlers', 'reference', 'newborn', 'karoke', 'musical']
+      AND i.i_brand IN ['scholarmaxi #4', 'exportiexporti #1', 'amalgexporti #2', 'scholarunivamalg #7', 'scholarunivamalg #6', 'corpunivamalg #7']*/)
+      OR ( i.i_category IN ['Women', 'Music', 'Men']
+      /*AND i.i_class IN ['fragrances', 'pop', 'shirts', 'pants']
+      AND i.i_brand IN ['importoamalg #1', 'exportischolar #2', 'importoimporto #2', 'exportiimporto #1']*/ ) )
+  GROUP BY
+    i.i_manager_id,
+    d.d_moy
+) tmp1
+WHERE
+  CASE WHEN avg_monthly_sales > 0 THEN ABS(sum_sales - avg_monthly_sales) / avg_monthly_sales ELSE NULL END > 0.1
+ORDER BY
+  i_manager_id,
+  avg_monthly_sales,
+  sum_sales
+LIMIT 100;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.1.ddl.sqlpp
new file mode 100644
index 0000000..768ed9b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.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.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.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 dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset item(item_type) primary key i_item_sk;
+
+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;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.2.update.sqlpp
new file mode 100644
index 0000000..c2cef59
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.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 date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.3.query.sqlpp
new file mode 100644
index 0000000..9d4944a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q89/q89.3.query.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * 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 tmp1.*
+FROM (
+  SELECT
+    i_category,
+    i_class,
+    i_brand,
+    s_store_name,
+    s_company_name,
+    d_moy,
+    ROUND_HALF_TO_EVEN(SUM(ss.ss_sales_price), 2) sum_sales,
+    ROUND_HALF_TO_EVEN(
+      AVG(SUM(ss.ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name), 2)
+      avg_monthly_sales
+  FROM
+    item i,
+    store_sales ss,
+    date_dim d,
+    store s
+  WHERE
+    ss.ss_item_sk = i.i_item_sk
+    AND ss.ss_sold_date_sk = d.d_date_sk
+    AND ss.ss_store_sk = s.s_store_sk
+    AND d.d_year IN [2000, 2001]
+    AND ((i.i_category IN ['Home', 'Men', 'Sports'] AND i.i_class IN ['furniture', 'accessories', 'hockey'])
+      OR (i.i_category IN ['Electronics', 'Jewelry', 'Women'] AND i.i_class IN ['musical', 'estate', 'fragrances']))
+  GROUP BY
+    i.i_category,
+    i.i_class,
+    i.i_brand,
+    s.s_store_name,
+    s.s_company_name,
+    d.d_moy
+) tmp1
+WHERE
+  CASE WHEN (avg_monthly_sales <> 0) THEN (ABS(sum_sales - avg_monthly_sales) / avg_monthly_sales) ELSE NULL END > 0.1
+ORDER BY
+  sum_sales - avg_monthly_sales, s_store_name
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
index 17038de..32faa06 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp
@@ -20,20 +20,12 @@
 
 USE tpcds;
 
-SELECT
-       currpricetable.i_item_desc,
-       currpricetable.i_category,
-       currpricetable.i_class,
-       currpricetable.i_current_price,
-       currpricetable.itemrevenue,
-       (currpricetable.itemrevenue * 100 / revrattable.revrat) revenueratio
-FROM
-(SELECT i_item_desc,
-        i_category,
-        i_class,
-        i_current_price,
-        i_item_id,
-        SUM(ss.ss_ext_sales_price) itemrevenue
+SELECT i_item_desc,
+       i_category,
+       i_class,
+       i_current_price,
+       SUM(ss.ss_ext_sales_price) itemrevenue,
+       SUM(ss.ss_ext_sales_price) * 100 / SUM(SUM(ss.ss_ext_sales_price)) OVER (PARTITION BY i_class) revenueratio
 FROM
   store_sales ss,
   item i,
@@ -42,34 +34,16 @@
   ss.ss_item_sk = i.i_item_sk
   AND i.i_category IN ["Jewelry", "Sports", "Books"]
   AND ss.ss_sold_date_sk = d.d_date_sk
-  AND date(d.d_date) >= date('2001-01-12')
-  AND date(d.d_date) <= date('2001-02-11')
+  AND date(d.d_date) BETWEEN date('2001-01-12') AND date('2001-02-11')
 GROUP BY
   i.i_item_id,
   i.i_item_desc,
   i.i_category,
   i.i_class,
   i.i_current_price
-) AS currpricetable
-JOIN
-(SELECT i_item_id, i_item_desc, i_category, i_class, SUM(ss.ss_ext_sales_price) revrat
-  FROM
-  store_sales ss,
-  item i
-  WHERE ss.ss_item_sk = i.i_item_sk
-  GROUP BY
-     i.i_item_id,
-     i.i_item_desc,
-     i.i_category,
-     i.i_class
-) AS revrattable
-ON  currpricetable.i_item_id = revrattable.i_item_id
-AND currpricetable.i_class = revrattable.i_class
-AND currpricetable.i_item_desc = revrattable.i_item_desc
-AND currpricetable.i_category = revrattable.i_category
 ORDER BY
-  currpricetable.i_category,
-  currpricetable.i_class,
-  currpricetable.i_item_id,
-  currpricetable.i_item_desc,
+  i_category,
+  i_class,
+  i_item_id,
+  i_item_desc,
   revenueratio;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q12/q12.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q12/q12.1.adm
new file mode 100644
index 0000000..31207a5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q12/q12.1.adm
@@ -0,0 +1,2 @@
+{ "i_item_id": "AAAAAAAAEAAAAAAA", "i_item_desc": "Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D", "i_category": "Women", "i_class": "Jewelry", "i_current_price": 1.35, "itemrevenue": 4305.66, "revenueratio": 100.0 }
+{ "i_item_id": "AAAAAAAAIAAAAAAA", "i_item_desc": "F", "i_category": "Women", "i_class": "swimwear", "i_current_price": 4.46, "itemrevenue": 935.54, "revenueratio": 100.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
new file mode 100644
index 0000000..3cc1b0b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q20/q20.1.adm
@@ -0,0 +1,2 @@
+{ "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
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
new file mode 100644
index 0000000..e94c4ac
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q49/q49.1.adm
@@ -0,0 +1,4 @@
+{ "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
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
new file mode 100644
index 0000000..e8ef0c1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q57/q57.1.adm
@@ -0,0 +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
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q63/q63.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q63/q63.1.adm
new file mode 100644
index 0000000..29d6045
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q63/q63.1.adm
@@ -0,0 +1,4 @@
+{ "i_manager_id": 11, "sum_sales": 61.5, "avg_monthly_sales": 83.09 }
+{ "i_manager_id": 11, "sum_sales": 104.67, "avg_monthly_sales": 83.09 }
+{ "i_manager_id": 34, "sum_sales": 3.52, "avg_monthly_sales": 36.46 }
+{ "i_manager_id": 34, "sum_sales": 69.39, "avg_monthly_sales": 36.46 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q89/q89.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q89/q89.1.adm
new file mode 100644
index 0000000..bc211cf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q89/q89.1.adm
@@ -0,0 +1,2 @@
+{ "i_category": "Home", "i_class": "furniture", "i_brand": "corpnameless #3", "s_store_name": "ese", "s_company_name": "Unknown", "d_moy": 5, "sum_sales": 7.59, "avg_monthly_sales": 28.41 }
+{ "i_category": "Home", "i_class": "furniture", "i_brand": "corpnameless #3", "s_store_name": "ese", "s_company_name": "Unknown", "d_moy": 12, "sum_sales": 49.23, "avg_monthly_sales": 28.41 }
\ 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 dbd0d29..9280813 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -8074,6 +8074,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q01">
+        <output-dir compare="Text">q01</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q03">
         <output-dir compare="Text">q03</output-dir>
       </compilation-unit>
@@ -8084,6 +8089,16 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q09">
+        <output-dir compare="Text">q09</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="q12">
+        <output-dir compare="Text">q12</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q15">
         <output-dir compare="Text">q15</output-dir>
       </compilation-unit>
@@ -8091,6 +8106,11 @@
     <test-case FilePath="tpcds">
       <compilation-unit name="q19">
         <output-dir compare="Text">q19</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="q20">
+        <output-dir compare="Text">q20</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
@@ -8169,6 +8189,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q49">
+        <output-dir compare="Text">q49</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q50">
         <output-dir compare="Text">q50</output-dir>
       </compilation-unit>
@@ -8184,6 +8209,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q57">
+        <output-dir compare="Text">q57</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q59">
         <output-dir compare="Text">q59</output-dir>
       </compilation-unit>
@@ -8191,6 +8221,11 @@
     <test-case FilePath="tpcds">
       <compilation-unit name="q62">
         <output-dir compare="Text">q62</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="q63">
+        <output-dir compare="Text">q63</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
@@ -8219,13 +8254,33 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q85">
+        <output-dir compare="Text">q85</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q88">
         <output-dir compare="Text">q88</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="q89">
+        <output-dir compare="Text">q89</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="q90">
+        <output-dir compare="Text">q90</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="q91">
         <output-dir compare="Text">q91</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
+      <compilation-unit name="q92">
+        <output-dir compare="Text">q92</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
@@ -8238,7 +8293,7 @@
         <output-dir compare="Text">q95</output-dir>
       </compilation-unit>
     </test-case>
-     <test-case FilePath="tpcds">
+    <test-case FilePath="tpcds">
       <compilation-unit name="q96">
         <output-dir compare="Text">q96</output-dir>
       </compilation-unit>
@@ -8246,31 +8301,6 @@
     <test-case FilePath="tpcds">
       <compilation-unit name="q98">
         <output-dir compare="Text">q98</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpcds">
-      <compilation-unit name="q01">
-        <output-dir compare="Text">q01</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpcds">
-      <compilation-unit name="q09">
-        <output-dir compare="Text">q09</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpcds">
-      <compilation-unit name="q92">
-        <output-dir compare="Text">q92</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpcds">
-      <compilation-unit name="q90">
-        <output-dir compare="Text">q90</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpcds">
-      <compilation-unit name="q85">
-        <output-dir compare="Text">q85</output-dir>
       </compilation-unit>
     </test-case>
   </test-group>

-- 
To view, visit https://asterix-gerrit.ics.uci.edu/3121
To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings

Gerrit-MessageType: newchange
Gerrit-Change-Id: I47e3a4eb479754a04afb8bd6a3b14fd341cb7927
Gerrit-PatchSet: 1
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Owner: Dmitry Lychagin <dm...@couchbase.com>