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:48 UTC
[3/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/q52/q52.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.1.ddl.sqlpp
new file mode 100644
index 0000000..acd3953
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.1.ddl.sqlpp
@@ -0,0 +1,115 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+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.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 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.2.update.sqlpp
new file mode 100644
index 0000000..dfdaad1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.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 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`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp
new file mode 100644
index 0000000..6c3ad96
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q52/q52.3.query.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+SELECT dt.d_year
+ ,item.i_brand_id brand_id
+ ,item.i_brand brand
+ ,SUM(ss_ext_sales_price) ext_price
+ FROM date_dim dt
+ ,store_sales
+ ,item
+ WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
+ AND store_sales.ss_item_sk = item.i_item_sk
+ AND item.i_manager_id = 1
+ AND dt.d_moy=12
+ AND dt.d_year=1998
+ GROUP BY dt.d_year
+ ,item.i_brand
+ ,item.i_brand_id
+ ORDER BY dt.d_year
+ ,ext_price desc
+ ,brand_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/q55/q55.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.1.ddl.sqlpp
new file mode 100644
index 0000000..acd3953
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.1.ddl.sqlpp
@@ -0,0 +1,115 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+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.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 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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.2.update.sqlpp
new file mode 100644
index 0000000..dfdaad1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.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 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`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.sqlpp
new file mode 100644
index 0000000..4928de3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q55/q55.3.query.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;
+
+SELECT i_brand_id brand_id, i_brand brand,
+ SUM(ss_ext_sales_price) ext_price
+ FROM date_dim, store_sales, item
+ WHERE d_date_sk = ss_sold_date_sk
+ AND ss_item_sk = i_item_sk
+ AND i_manager_id=36
+ AND d_moy=12
+ AND d_year=2001
+ GROUP BY i_brand, i_brand_id
+ ORDER BY ext_price desc, i_brand_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/q59/q59.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.1.ddl.sqlpp
new file mode 100644
index 0000000..fb4779f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.1.ddl.sqlpp
@@ -0,0 +1,122 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.2.update.sqlpp
new file mode 100644
index 0000000..4cadf19
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.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 store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_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/q59/q59.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp
new file mode 100644
index 0000000..ff45e4d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q59/q59.3.query.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * 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 wss AS
+ (SELECT d_week_seq,
+ ss_store_sk,
+ SUM(CASE WHEN d_day_name='Sunday' THEN ss_sales_price ELSE null END) sun_sales,
+ SUM(CASE WHEN d_day_name='Monday' THEN ss_sales_price ELSE null END) mon_sales,
+ SUM(CASE WHEN d_day_name='Tuesday' THEN ss_sales_price ELSE null END) tue_sales,
+ SUM(CASE WHEN d_day_name='Wednesday' THEN ss_sales_price ELSE null END) wed_sales,
+ SUM(CASE WHEN d_day_name='Thursday' THEN ss_sales_price ELSE null END) thu_sales,
+ SUM(CASE WHEN d_day_name='Friday' THEN ss_sales_price ELSE null END) fri_sales,
+ SUM(CASE WHEN d_day_name='Saturday' THEN ss_sales_price ELSE null END) sat_sales
+ FROM store_sales,date_dim
+ WHERE d_date_sk = ss_sold_date_sk
+ GROUP BY d_week_seq,ss_store_sk
+ )
+ SELECT s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ FROM
+ (SELECT s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ FROM wss,store,date_dim d
+ WHERE d.d_week_seq = wss.d_week_seq AND
+ ss_store_sk = s_store_sk AND
+ d_month_seq >= 1185 AND d_month_seq <= 1185 + 11) y,
+ (SELECT s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ FROM wss,store,date_dim d
+ WHERE d.d_week_seq = wss.d_week_seq AND
+ ss_store_sk = s_store_sk AND
+ d_month_seq >= 1185+ 12 AND d_month_seq <= 1185 + 23) x
+ WHERE s_store_id1=s_store_id2
+ AND d_week_seq1=d_week_seq2-52
+ ORDER BY s_store_name1,s_store_id1,d_week_seq1
+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/q62/q62.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.1.ddl.sqlpp
new file mode 100644
index 0000000..f9d0500
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.1.ddl.sqlpp
@@ -0,0 +1,162 @@
+/*
+ * 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.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 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_site_type as
+ closed {
+ web_site_sk: bigint,
+ web_site_id: string,
+ web_rec_start_date: string?,
+ web_rec_end_date: string?,
+ web_name: string?,
+ web_open_date_sk: bigint?,
+ web_close_date_sk: bigint?,
+ web_class: string?,
+ web_manager: string?,
+ web_mkt_id: bigint?,
+ web_mkt_class: string?,
+ web_mkt_desc: string?,
+ web_market_manager: string?,
+ web_company_id: bigint?,
+ web_company_name: string?,
+ web_street_number: string?,
+ web_street_name: string?,
+ web_street_type: string?,
+ web_suite_number: string?,
+ web_city: string?,
+ web_county: string?,
+ web_state: string?,
+ web_zip: string?,
+ web_country: string?,
+ web_gmt_offset: double?,
+ web_tax_percentage: double?
+}
+
+create type tpcds.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 type tpcds.ship_mode_type as
+ closed {
+ sm_ship_mode_sk : bigint,
+ sm_ship_mode_id : string,
+ sm_type : string?,
+ sm_code : string?,
+ sm_carrier : string?,
+ sm_contract : string?
+}
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset web_site (web_site_type) primary key web_site_sk;
+
+create dataset warehouse(warehouse_type) primary key w_warehouse_sk;
+
+create dataset ship_mode(ship_mode_type) primary key sm_ship_mode_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.2.update.sqlpp
new file mode 100644
index 0000000..2aab7e4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.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 web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_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 web_site using localfs ((`path`=`asterix_nc1://data/tpcds/web_site.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset warehouse using localfs ((`path`=`asterix_nc1://data/tpcds/warehouse.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset ship_mode using localfs ((`path`=`asterix_nc1://data/tpcds/ship_mode.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/q62/q62.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.3.query.sqlpp
new file mode 100644
index 0000000..4d45927
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q62/q62.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
+ SUBSTR(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+ ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) <= 30 THEN 1 ELSE 0 END) AS c30_days
+ ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 30 AND
+ (ws_ship_date_sk - ws_sold_date_sk) <= 60 THEN 1 ELSE 0 END ) AS c31_60_days
+ ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 60 AND
+ (ws_ship_date_sk - ws_sold_date_sk) <= 90 THEN 1 ELSE 0 END) AS c61_90_days
+ ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 90 AND
+ (ws_ship_date_sk - ws_sold_date_sk) <= 120 THEN 1 ELSE 0 END) AS c91_120_days
+ ,SUM(CASE WHEN (ws_ship_date_sk - ws_sold_date_sk) > 120 THEN 1 ELSE 0 END) AS gt120_days
+FROM
+ web_sales
+ ,warehouse
+ ,ship_mode
+ ,web_site
+ ,date_dim
+WHERE
+ d_month_seq >= 1212 AND d_month_seq <= 1212 + 11
+AND ws_ship_date_sk = d_date_sk
+AND ws_warehouse_sk = w_warehouse_sk
+AND ws_ship_mode_sk = sm_ship_mode_sk
+AND ws_web_site_sk = web_site_sk
+GROUP BY
+ SUBSTR(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+ORDER BY SUBSTR(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_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/q68/q68.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.1.ddl.sqlpp
new file mode 100644
index 0000000..3d99b67
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.1.ddl.sqlpp
@@ -0,0 +1,175 @@
+/*
+ * 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.household_demographics_type as
+ closed {
+ hd_demo_sk : bigint,
+ hd_income_band_sk : bigint?,
+ hd_buy_potential : string?,
+ hd_dep_count : bigint?,
+ hd_vehicle_count : bigint?
+}
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_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 household_demographics (household_demographics_type) primary key hd_demo_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset customer (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/q68/q68.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.2.update.sqlpp
new file mode 100644
index 0000000..6ab1d16
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.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 household_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/household_demographics.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 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`=`|`));
+
+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/q68/q68.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp
new file mode 100644
index 0000000..5e5833b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q68/q68.3.query.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+ SELECT c_last_name
+ ,c_first_name
+ ,ca_city
+ ,bought_city
+ ,ss_ticket_number
+ ,extended_price
+ ,extended_tax
+ ,list_price
+ FROM (SELECT ss_ticket_number
+ ,ss_customer_sk
+ ,ca_city bought_city
+ ,sum(ss_ext_sales_price) extended_price
+ ,sum(ss_ext_list_price) list_price
+ ,sum(ss_ext_tax) extended_tax
+ FROM store_sales
+ ,date_dim
+ ,store
+ ,household_demographics
+ ,customer_address
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_store_sk = store.s_store_sk
+ AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ AND store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND date_dim.d_dom >= 1 AND date_dim.d_dom <= 2
+ AND (household_demographics.hd_dep_count = 5 or
+ household_demographics.hd_vehicle_count= 3)
+ AND date_dim.d_year in [1999,1999+1,1999+2]
+ AND store.s_city IN ['Midway','Fairview']
+ GROUP BY ss_ticket_number
+ ,ss_customer_sk
+ ,ss_addr_sk,ca_city) dn
+ ,customer
+ ,customer_address current_addr
+ WHERE ss_customer_sk = c_customer_sk
+ AND customer.c_current_addr_sk = current_addr.ca_address_sk
+ AND current_addr.ca_city != bought_city
+ ORDER BY c_last_name
+ ,ss_ticket_number
+ 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/q73/q73.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.1.ddl.sqlpp
new file mode 100644
index 0000000..f7a6453
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.1.ddl.sqlpp
@@ -0,0 +1,156 @@
+/*
+ * 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.household_demographics_type as
+ closed {
+ hd_demo_sk : bigint,
+ hd_income_band_sk : bigint?,
+ hd_buy_potential : string?,
+ hd_dep_count : bigint?,
+ hd_vehicle_count : bigint?
+}
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_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 household_demographics (household_demographics_type) primary key hd_demo_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset customer (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/q73/q73.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.2.update.sqlpp
new file mode 100644
index 0000000..97ea2e8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.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 household_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/household_demographics.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 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`=`|`));
+
+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/q73/q73.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp
new file mode 100644
index 0000000..0036755
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q73/q73.3.query.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+SELECT c_last_name
+ ,c_first_name
+ ,c_salutation
+ ,c_preferred_cust_flag
+ ,ss_ticket_number
+ ,cnt FROM
+ (SELECT ss_ticket_number
+ ,ss_customer_sk
+ ,len(djgrp) cnt
+ FROM store_sales,date_dim,store,household_demographics
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_store_sk = store.s_store_sk
+ AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ AND date_dim.d_dom >= 1 AND date_dim.d_dom <= 2
+ AND (household_demographics.hd_buy_potential = '>10000' OR
+ household_demographics.hd_buy_potential = 'unknown')
+ AND household_demographics.hd_vehicle_count > 0
+ AND (CASE WHEN household_demographics.hd_vehicle_count > 0 THEN
+ household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count ELSE null END) > 1
+ AND date_dim.d_year in [1998,1998+1,1998+2]
+ AND store.s_county in ['Williamson County','Williamson County','Williamson County','Williamson County']
+ GROUP BY ss_ticket_number,ss_customer_sk
+ GROUP AS djgrp) dj,customer
+ WHERE ss_customer_sk = c_customer_sk
+ AND cnt >= 1 AND cnt <= 5
+ ORDER BY cnt desc;
\ 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/q79/q79.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.1.ddl.sqlpp
new file mode 100644
index 0000000..f7a6453
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.1.ddl.sqlpp
@@ -0,0 +1,156 @@
+/*
+ * 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.household_demographics_type as
+ closed {
+ hd_demo_sk : bigint,
+ hd_income_band_sk : bigint?,
+ hd_buy_potential : string?,
+ hd_dep_count : bigint?,
+ hd_vehicle_count : bigint?
+}
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create type tpcds.customer_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 household_demographics (household_demographics_type) primary key hd_demo_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store (store_type) primary key s_store_sk;
+
+create dataset customer (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/q79/q79.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.2.update.sqlpp
new file mode 100644
index 0000000..97ea2e8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.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 household_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/household_demographics.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 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`=`|`));
+
+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/q79/q79.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp
new file mode 100644
index 0000000..f9a9a5c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q79/q79.3.query.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+SELECT
+ c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+ FROM
+ (SELECT ss_ticket_number
+ ,ss_customer_sk
+ ,store.s_city
+ ,sum(ss_coupon_amt) amt
+ ,sum(ss_net_profit) profit
+ FROM store_sales,date_dim,store,household_demographics
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_store_sk = store.s_store_sk
+ AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ AND (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+ AND date_dim.d_dow = 1
+ AND date_dim.d_year IN [1998,1998+1,1998+2]
+ AND store.s_number_employees >= 200 AND store.s_number_employees <= 295
+ group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+ WHERE ss_customer_sk = c_customer_sk
+ ORDER BY c_last_name,c_first_name,SUBSTR(s_city,1,30), profit
+ 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/q81/q81.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.1.ddl.sqlpp
new file mode 100644
index 0000000..724dde8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.1.ddl.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.
+ */
+
+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.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.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 catalog_returns (catalog_returns_type) primary key cr_item_sk, cr_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/q81/q81.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.2.update.sqlpp
new file mode 100644
index 0000000..b7e0e29
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.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 catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.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/q81/q81.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp
new file mode 100644
index 0000000..1dbe2ef
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q81/q81.3.query.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+
+WITH customer_total_return AS
+ (SELECT cr_returning_customer_sk as ctr_customer_sk
+ ,ca_state as ctr_state,
+ SUM(cr_return_amt_inc_tax) as ctr_total_return
+ FROM catalog_returns
+ ,date_dim
+ ,customer_address
+ WHERE cr_returned_date_sk = d_date_sk
+ AND d_year =1998
+ AND cr_returning_addr_sk = ca_address_sk
+ GROUP BY cr_returning_customer_sk
+ ,ca_state )
+ SELECT c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+ ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+ ,ca_location_type,ctr_total_return
+ FROM customer_total_return ctr1
+ ,customer_address
+ ,customer
+ WHERE ctr1.ctr_total_return > (SELECT VALUE (AVG(ctr2.ctr_total_return)*1.2)
+ FROM customer_total_return ctr2
+ WHERE ctr1.ctr_state = ctr2.ctr_state)[0]
+ AND ca_address_sk = c_current_addr_sk
+ AND ca_state = 'IL'
+ AND ctr1.ctr_customer_sk = c_customer_sk
+ ORDER BY c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+ ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+ ,ca_location_type,ctr_total_return
+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/q82/q82.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.1.ddl.sqlpp
new file mode 100644
index 0000000..c2cc51b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.1.ddl.sqlpp
@@ -0,0 +1,125 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.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.inventory_type as
+ closed {
+ inv_date_sk : bigint,
+ inv_item_sk : bigint,
+ inv_warehouse_sk : bigint,
+ inv_quantity_on_hand : bigint?
+}
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset inventory (inventory_type) primary key inv_date_sk, inv_item_sk, inv_warehouse_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.2.update.sqlpp
new file mode 100644
index 0000000..037dc26
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.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 store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset inventory using localfs ((`path`=`asterix_nc1://data/tpcds/inventory.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/q82/q82.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
new file mode 100644
index 0000000..7463682
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q82/q82.3.query.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * 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_current_price
+ FROM item, inventory, date_dim, store_sales
+ WHERE i_current_price >= 30 AND i_current_price <= 30+30
+ AND inv_item_sk = i_item_sk
+ AND d_date_sk=inv_date_sk
+ AND date(d_date) >= date('2002-05-30') AND date(d_date) <= date('2002-07-01')
+ AND i_manufact_id in [437,129,727,663]
+ AND inv_quantity_on_hand >= 100 AND inv_quantity_on_hand <= 500
+ AND ss_item_sk = i_item_sk
+ GROUP BY i_item_id,i_item_desc,i_current_price
+ 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/q88/q88.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q88/q88.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q88/q88.1.ddl.sqlpp
new file mode 100644
index 0000000..342fecd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q88/q88.1.ddl.sqlpp
@@ -0,0 +1,113 @@
+/*
+ * 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.household_demographics_type as
+ closed {
+ hd_demo_sk : bigint,
+ hd_income_band_sk : bigint?,
+ hd_buy_potential : string?,
+ hd_dep_count : bigint?,
+ hd_vehicle_count : bigint?
+}
+create type tpcds.time_dim_type as
+ closed {
+ t_time_sk : bigint,
+ t_time_id : string,
+ t_time : bigint?,
+ t_hour : bigint?,
+ t_minute : bigint?,
+ t_second : bigint?,
+ t_am_pm : string?,
+ t_shift : string?,
+ t_sub_shift : string?,
+ t_meal_time : 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 dataset household_demographics (household_demographics_type) primary key hd_demo_sk;
+
+create dataset tpcds.time_dim(time_dim_type) primary key t_time_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;