You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2016/10/04 20:34:50 UTC
[5/6] asterixdb git commit: Add test TPC-DS data and 35 TPC-DS
queries.
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
new file mode 100644
index 0000000..5a9dea9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.1.ddl.sqlpp
@@ -0,0 +1,140 @@
+/*
+ * 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.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.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.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_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 customer (customer_type) primary key c_customer_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.2.update.sqlpp
new file mode 100644
index 0000000..5159a42
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.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 customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp
new file mode 100644
index 0000000..ceb7134
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q15/q15.3.query.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpcds;
+
+
+SELECT ca_zip
+ ,sum(cs_sales_price)
+FROM catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+WHERE cs_bill_customer_sk = c_customer_sk
+AND c_current_addr_sk = ca_address_sk
+AND ( substr(ca_zip,1,5) in ['85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792']
+OR ca_state in ['CA','WA','GA']
+OR cs_sales_price > 500)
+AND cs_sold_date_sk = d_date_sk
+AND d_qoy = 2
+AND d_year = 2000
+GROUP BY ca_zip
+ORDER BY ca_zip
+LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.1.ddl.sqlpp
new file mode 100644
index 0000000..1b30bcd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.1.ddl.sqlpp
@@ -0,0 +1,193 @@
+/*
+ * 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.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset customer (customer_type) primary key c_customer_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.2.update.sqlpp
new file mode 100644
index 0000000..b59d644
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.2.update.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.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_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp
new file mode 100644
index 0000000..12ca91f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q19/q19.3.query.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ SUM(ss_ext_sales_price) ext_price
+FROM date_dim, store_sales, item,customer,customer_address,store
+WHERE d_date_sk = ss_sold_date_sk
+AND ss_item_sk = i_item_sk
+AND i_manager_id=7
+AND d_moy=11
+AND d_year=1999
+AND ss_customer_sk = c_customer_sk
+AND c_current_addr_sk = ca_address_sk
+AND substr(ca_zip,1,5) != substr(s_zip,1,5)
+AND ss_store_sk = s_store_sk
+GROUP BY i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ORDER BY ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.1.ddl.sqlpp
new file mode 100644
index 0000000..de454ba
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.1.ddl.sqlpp
@@ -0,0 +1,116 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.inventory_type as
+ closed {
+ inv_date_sk : bigint,
+ inv_item_sk : bigint,
+ inv_warehouse_sk : bigint,
+ inv_quantity_on_hand : bigint?
+}
+
+create type tpcds.warehouse_type as
+ closed {
+ w_warehouse_sk : bigint,
+ w_warehouse_id : string,
+ w_warehouse_name : string?,
+ w_warehouse_sq_ft : bigint?,
+ w_street_number : string?,
+ w_street_name : string?,
+ w_street_type : string?,
+ w_suite_number : string?,
+ w_city : string?,
+ w_county : string?,
+ w_state : string?,
+ w_zip : string?,
+ w_country : string?,
+ w_gmt_offset : double?
+}
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset inventory (inventory_type) primary key inv_date_sk, inv_item_sk, inv_warehouse_sk;
+
+create dataset warehouse(warehouse_type) primary key w_warehouse_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.2.update.sqlpp
new file mode 100644
index 0000000..74f893d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.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 item using localfs ((`path`=`asterix_nc1://data/tpcds/item.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 inventory using localfs ((`path`=`asterix_nc1://data/tpcds/inventory.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset warehouse using localfs ((`path`=`asterix_nc1://data/tpcds/warehouse.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp
new file mode 100644
index 0000000..d39c5bd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q21/q21.3.query.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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 *
+FROM (SELECT w_warehouse_name
+ ,i_item_id
+ ,SUM(CASE WHEN date(d_date) < date('1998-04-08')
+ THEN inv_quantity_on_hand
+ ELSE 0 END) AS inv_before
+ ,SUM(CASE WHEN date(d_date) >= date('1998-04-08')
+ THEN inv_quantity_on_hand
+ ELSE 0 END) AS inv_after
+ FROM inventory
+ ,warehouse
+ ,item
+ ,date_dim
+ WHERE i_current_price >= 0.99
+ AND i_current_price <= 1.49
+ AND i_item_sk = inv_item_sk
+ AND inv_warehouse_sk = w_warehouse_sk
+ AND inv_date_sk = d_date_sk
+ AND date(d_date) >= date('1998-03-09')
+ AND date(d_date) <= date('1998-05-08')
+ GROUP BY w_warehouse_name, i_item_id) x
+ WHERE (CASE WHEN inv_before > 0
+ THEN inv_after / inv_before
+ ELSE null
+ END) >= 2.0/3.0
+ AND (CASE WHEN inv_before > 0
+ THEN inv_after / inv_before
+ ELSE null
+ END) <= 3.0/2.0
+ ORDER BY w_warehouse_name, i_item_id
+ LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.1.ddl.sqlpp
new file mode 100644
index 0000000..b7b449f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.1.ddl.sqlpp
@@ -0,0 +1,185 @@
+/*
+ * 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.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : bigint?,
+ sr_return_time_sk : bigint?,
+ sr_item_sk : bigint,
+ sr_customer_sk : bigint?,
+ sr_cdemo_sk : bigint?,
+ sr_hdemo_sk : bigint?,
+ sr_addr_sk : bigint?,
+ sr_store_sk : bigint?,
+ sr_reason_sk : bigint?,
+ sr_ticket_number : bigint,
+ sr_return_quantity : bigint?,
+ 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.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset item (item_type) primary key i_item_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 store (store_type) primary key s_store_sk;
+
+create dataset customer (customer_type) primary key c_customer_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.2.update.sqlpp
new file mode 100644
index 0000000..a423018
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.2.update.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.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 store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp
new file mode 100644
index 0000000..1286f34
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24a/q24a.3.query.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * 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 ssales as
+(SELECT c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,SUM(ss_sales_price) netpaid
+FROM store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+WHERE ss_ticket_number = sr_ticket_number
+ AND ss_item_sk = sr_item_sk
+ AND ss_customer_sk = c_customer_sk
+ AND ss_item_sk = i_item_sk
+ AND ss_store_sk = s_store_sk
+ AND c_birth_country = UPPERCASE(ca_country)
+ AND s_zip = ca_zip
+AND s_market_id=7
+GROUP BY c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+SELECT c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,SUM(netpaid) paid
+FROM ssales
+WHERE i_color = 'orchid'
+GROUP BY c_last_name
+ ,c_first_name
+ ,s_store_name
+HAVING paid > (SELECT value (0.05*avg(netpaid))
+ FROM ssales)[0]
+;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.1.ddl.sqlpp
new file mode 100644
index 0000000..b7b449f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.1.ddl.sqlpp
@@ -0,0 +1,185 @@
+/*
+ * 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.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : bigint?,
+ sr_return_time_sk : bigint?,
+ sr_item_sk : bigint,
+ sr_customer_sk : bigint?,
+ sr_cdemo_sk : bigint?,
+ sr_hdemo_sk : bigint?,
+ sr_addr_sk : bigint?,
+ sr_store_sk : bigint?,
+ sr_reason_sk : bigint?,
+ sr_ticket_number : bigint,
+ sr_return_quantity : bigint?,
+ 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.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset item (item_type) primary key i_item_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 store (store_type) primary key s_store_sk;
+
+create dataset customer (customer_type) primary key c_customer_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.2.update.sqlpp
new file mode 100644
index 0000000..a423018
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.2.update.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.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 store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp
new file mode 100644
index 0000000..07fa88f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q24b/q24b.3.query.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * 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 ssales AS
+(SELECT c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,SUM(ss_sales_price) netpaid
+FROM store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+WHERE ss_ticket_number = sr_ticket_number
+ AND ss_item_sk = sr_item_sk
+ AND ss_customer_sk = c_customer_sk
+ AND ss_item_sk = i_item_sk
+ AND ss_store_sk = s_store_sk
+ AND c_birth_country = uppercase(ca_country)
+ AND s_zip = ca_zip
+ AND s_market_id = 7
+GROUP BY c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+SELECT c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,SUM(netpaid) paid
+FROM ssales
+WHERE i_color = 'chiffon'
+GROUP BY c_last_name
+ ,c_first_name
+ ,s_store_name
+HAVING SUM(netpaid) > (SELECT value (0.05*avg(netpaid))
+ FROM ssales)[0]
+;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
new file mode 100644
index 0000000..418a756
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.1.ddl.sqlpp
@@ -0,0 +1,215 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : bigint?,
+ sr_return_time_sk : bigint?,
+ sr_item_sk : bigint,
+ sr_customer_sk : bigint?,
+ sr_cdemo_sk : bigint?,
+ sr_hdemo_sk : bigint?,
+ sr_addr_sk : bigint?,
+ sr_store_sk : bigint?,
+ sr_reason_sk : bigint?,
+ sr_ticket_number : bigint,
+ sr_return_quantity : bigint?,
+ 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.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_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset store_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 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 (store_type) primary key s_store_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.2.update.sqlpp
new file mode 100644
index 0000000..3bf9f8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.2.update.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+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 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 using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp
new file mode 100644
index 0000000..8245836
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q25/q25.3.query.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * 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
+ ,s_store_id
+ ,s_store_name
+ ,SUM(ss_net_profit) as store_sales_profit
+ ,SUM(sr_net_loss) as store_returns_loss
+ ,SUM(cs_net_profit) as catalog_sales_profit
+ FROM
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ WHERE
+ d1.d_moy = 4
+ AND d1.d_year = 2000
+ AND d1.d_date_sk = ss_sold_date_sk
+ AND i_item_sk = ss_item_sk
+ AND s_store_sk = ss_store_sk
+ AND ss_customer_sk = sr_customer_sk
+ AND ss_item_sk = sr_item_sk
+ AND ss_ticket_number = sr_ticket_number
+ AND sr_returned_date_sk = d2.d_date_sk
+ AND d2.d_moy >= 4
+ AND d2.d_moy <= 10
+ AND d2.d_year = 2000
+ AND sr_customer_sk = cs_bill_customer_sk
+ AND sr_item_sk = cs_item_sk
+ AND cs_sold_date_sk = d3.d_date_sk
+ AND d3.d_moy >= 4
+ AND d3.d_moy <= 10
+ AND d3.d_year = 2000
+ GROUP BY
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ORDER BY
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
new file mode 100644
index 0000000..2a668dd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.1.ddl.sqlpp
@@ -0,0 +1,164 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.customer_demographics_type as
+ closed {
+ cd_demo_sk : bigint,
+ cd_gender : string?,
+ cd_marital_status : string?,
+ cd_education_status : string?,
+ cd_purchase_estimate : bigint?,
+ cd_credit_rating : string?,
+ cd_dep_count : bigint?,
+ cd_dep_employed_count : bigint?,
+ cd_dep_college_count : bigint?
+}
+create 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.promotion_type as
+ closed {
+ p_promo_sk : bigint,
+ p_promo_id : string,
+ p_start_date_sk : bigint?,
+ p_end_date_sk : bigint?,
+ p_item_sk : bigint?,
+ p_cost : double?,
+ p_response_target : bigint?,
+ p_promo_name : string?,
+ p_channel_dmail : string?,
+ p_channel_email : string?,
+ p_channel_catalog : string?,
+ p_channel_tv : string?,
+ p_channel_radio : string?,
+ p_channel_press : string?,
+ p_channel_event : string?,
+ p_channel_demo : string?,
+ p_channel_details : string?,
+ p_purpose : string?,
+ p_discount_active : string?
+}
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset customer_demographics(customer_demographics_type) primary key cd_demo_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 promotion (promotion_type) primary key p_promo_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
new file mode 100644
index 0000000..bcbd71f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.2.update.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.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 promotion using localfs ((`path`=`asterix_nc1://data/tpcds/promotion.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp
new file mode 100644
index 0000000..d5c38ce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q26/q26.3.query.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+SELECT i_item_id,
+ avg(cs_quantity) agg1,
+ avg(cs_list_price) agg2,
+ avg(cs_coupon_amt) agg3,
+ avg(cs_sales_price) agg4
+ FROM catalog_sales, customer_demographics, date_dim, item, promotion
+ WHERE cs_sold_date_sk = d_date_sk AND
+ cs_item_sk = i_item_sk AND
+ cs_bill_cdemo_sk = cd_demo_sk AND
+ cs_promo_sk = p_promo_sk AND
+ cd_gender = 'F' AND
+ cd_marital_status = 'W' AND
+ cd_education_status = 'Primary' AND
+ (p_channel_email = 'N' OR p_channel_event = 'N') AND
+ d_year = 1998
+ GROUP BY i_item_id
+ ORDER BY i_item_id
+ LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
new file mode 100644
index 0000000..418a756
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.1.ddl.sqlpp
@@ -0,0 +1,215 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.store_returns_type as
+ closed {
+ sr_returned_date_sk : bigint?,
+ sr_return_time_sk : bigint?,
+ sr_item_sk : bigint,
+ sr_customer_sk : bigint?,
+ sr_cdemo_sk : bigint?,
+ sr_hdemo_sk : bigint?,
+ sr_addr_sk : bigint?,
+ sr_store_sk : bigint?,
+ sr_reason_sk : bigint?,
+ sr_ticket_number : bigint,
+ sr_return_quantity : bigint?,
+ 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.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_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset store_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 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 (store_type) primary key s_store_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.2.update.sqlpp
new file mode 100644
index 0000000..3bf9f8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.2.update.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+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 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 using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp
new file mode 100644
index 0000000..e2ba54d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q29/q29.3.query.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * 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
+ ,s_store_id
+ ,s_store_name
+ ,SUM(ss_quantity) as store_sales_quantity
+ ,SUM(sr_return_quantity) as store_returns_quantity
+ ,SUM(cs_quantity) as catalog_sales_quantity
+ FROM
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ WHERE
+ d1.d_moy = 4
+ AND d1.d_year = 1999
+ AND d1.d_date_sk = ss_sold_date_sk
+ AND i_item_sk = ss_item_sk
+ AND s_store_sk = ss_store_sk
+ AND ss_customer_sk = sr_customer_sk
+ AND ss_item_sk = sr_item_sk
+ AND ss_ticket_number = sr_ticket_number
+ AND sr_returned_date_sk = d2.d_date_sk
+ AND d2.d_moy >= 4 AND d2.d_moy <= 4 + 3
+ AND d2.d_year = 1999
+ AND sr_customer_sk = cs_bill_customer_sk
+ AND sr_item_sk = cs_item_sk
+ AND cs_sold_date_sk = d3.d_date_sk
+ AND d3.d_year IN [1999,1999+1,1999+2]
+ GROUP BY
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ORDER BY
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ LIMIT 100;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.1.ddl.sqlpp
new file mode 100644
index 0000000..86c6792
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.1.ddl.sqlpp
@@ -0,0 +1,131 @@
+/*
+ * 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.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.web_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.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number;
+
+create dataset customer (customer_type) primary key c_customer_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.2.update.sqlpp
new file mode 100644
index 0000000..190a142
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.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 web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));