[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
Reviewed-on: https://asterix-gerrit.ics.uci.edu/3121
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Contrib: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
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>
@@ -8094,6 +8109,11 @@
</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">
<compilation-unit name="q21">
<output-dir compare="Text">q21</output-dir>
</compilation-unit>
@@ -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>
@@ -8194,6 +8224,11 @@
</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">
<compilation-unit name="q68">
<output-dir compare="Text">q68</output-dir>
</compilation-unit>
@@ -8219,16 +8254,36 @@
</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">
<compilation-unit name="q94">
<output-dir compare="Text">q94</output-dir>
</compilation-unit>
@@ -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>
@@ -8248,31 +8303,6 @@
<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>
<test-group name="tpch">
<test-case FilePath="tpch">