asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [3/6] asterixdb git commit: Add test TPC-DS data and 35 TPC-DS queries.
Date Tue, 04 Oct 2016 20:34:48 GMT
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;


Mime
View raw message