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:49 UTC
[4/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/q30/q30.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.3.query.sqlpp
new file mode 100644
index 0000000..0793006
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q30/q30.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 wr_returning_customer_sk as ctr_customer_sk
+ ,ca_state as ctr_state,
+ sum(wr_return_amt) as ctr_total_return
+ FROM web_returns
+ ,date_dim
+ ,customer_address
+ where wr_returned_date_sk = d_date_sk
+ AND d_year =2002
+ AND wr_returning_addr_sk = ca_address_sk
+ group by wr_returning_customer_sk
+ ,ca_state)
+ SELECT c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag
+ ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
+ ,c_last_review_date,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,c_preferred_cust_flag
+ ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address
+ ,c_last_review_date,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/q31/q31.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.1.ddl.sqlpp
new file mode 100644
index 0000000..3762222
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.1.ddl.sqlpp
@@ -0,0 +1,146 @@
+/*
+ * 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.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.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 dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number;
+
+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;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.2.update.sqlpp
new file mode 100644
index 0000000..b25c37b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.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 web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.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`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp
new file mode 100644
index 0000000..b869d5e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q31/q31.3.query.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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 ss AS
+ (SELECT ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
+ FROM store_sales,date_dim,customer_address
+ WHERE ss_sold_date_sk = d_date_sk
+ AND ss_addr_sk=ca_address_sk
+ GROUP BY ca_county,d_qoy, d_year),
+ ws AS
+ (SELECT ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
+ FROM web_sales,date_dim,customer_address
+ WHERE ws_sold_date_sk = d_date_sk
+ AND ws_bill_addr_sk=ca_address_sk
+ GROUP BY ca_county,d_qoy, d_year)
+ SELECT /* tt */
+ ss1.ca_county
+ ,ss1.d_year
+ ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
+ ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
+ ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
+ ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
+ FROM
+ ss ss1
+ ,ss ss2
+ ,ss ss3
+ ,ws ws1
+ ,ws ws2
+ ,ws ws3
+ WHERE
+ ss1.d_qoy = 1
+ AND ss1.d_year = 2000
+ AND ss1.ca_county = ss2.ca_county
+ AND ss2.d_qoy = 2
+ AND ss2.d_year = 2000
+ AND ss2.ca_county = ss3.ca_county
+ AND ss3.d_qoy = 3
+ AND ss3.d_year = 2000
+ AND ss1.ca_county = ws1.ca_county
+ AND ws1.d_qoy = 1
+ AND ws1.d_year = 2000
+ AND ws1.ca_county = ws2.ca_county
+ AND ws2.d_qoy = 2
+ AND ws2.d_year = 2000
+ AND ws1.ca_county = ws3.ca_county
+ AND ws3.d_qoy = 3
+ AND ws3.d_year =2000
+ AND (CASE WHEN ws1.web_sales > 0 THEN ws2.web_sales/ws1.web_sales ELSE null END)
+ > (CASE WHEN ss1.store_sales > 0 THEN ss2.store_sales/ss1.store_sales ELSE null END)
+ AND (CASE WHEN ws2.web_sales > 0 THEN ws3.web_sales/ws2.web_sales ELSE null END)
+ > (CASE WHEN ss2.store_sales > 0 THEN ss3.store_sales/ss2.store_sales ELSE null END)
+ORDER BY ss1.d_year;
\ 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/q34/q34.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.1.ddl.sqlpp
new file mode 100644
index 0000000..f7a6453
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.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/q34/q34.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.2.update.sqlpp
new file mode 100644
index 0000000..97ea2e8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.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/q34/q34.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp
new file mode 100644
index 0000000..e81d182
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q34/q34.3.query.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * 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(dngrp) 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 <= 3
+ OR date_dim.d_dom >= 25
+ AND date_dim.d_dom <= 28)
+ 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.2
+ AND date_dim.d_year in [1998,1998+1,1998+2]
+ AND store.s_county in ['Williamson County','Williamson County','Williamson County','Williamson County',
+ 'Williamson County','Williamson County','Williamson County','Williamson County']
+GROUP BY ss_ticket_number,ss_customer_sk GROUP AS dngrp) dn, customer
+WHERE dn.ss_customer_sk = c_customer_sk
+ AND cnt >= 15
+ AND cnt <= 20
+ORDER BY c_last_name,c_first_name,c_salutation,c_preferred_cust_flag 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/q37/q37.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp
new file mode 100644
index 0000000..cb90a52
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.1.ddl.sqlpp
@@ -0,0 +1,135 @@
+/*
+ * 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.catalog_sales_type as
+ closed {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+}
+
+create type tpcds.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 catalog_sales (catalog_sales_type) primary key cs_item_sk, cs_order_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/q37/q37.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.2.update.sqlpp
new file mode 100644
index 0000000..729df27
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.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 catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_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/q37/q37.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.3.query.sqlpp
new file mode 100644
index 0000000..df1b580
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q37/q37.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, catalog_sales
+ WHERE i_current_price >= 22 AND i_current_price <= 22 + 30
+ AND inv_item_sk = i_item_sk
+ AND d_date_sk=inv_date_sk
+ AND date(d_date) >= date('2001-06-02') AND date(d_date) <= date('2001-08-01')
+ AND i_manufact_id in [678,964,918,849]
+ AND inv_quantity_on_hand >= 100 AND inv_quantity_on_hand <= 500
+ AND cs_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/q41/q41.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.1.ddl.sqlpp
new file mode 100644
index 0000000..8bc86f0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.1.ddl.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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 dataset item (item_type) primary key i_item_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.2.update.sqlpp
new file mode 100644
index 0000000..a04c6dd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.2.update.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * 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`=`|`));
\ 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/q41/q41.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.3.query.sqlpp
new file mode 100644
index 0000000..f1fbe45
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q41/q41.3.query.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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 distinct(i_product_name)
+ FROM item i1
+ WHERE i1.i_manufact_id >= 742 AND i1.i_manufact_id <= 742+40
+ AND array_count((select 1 as item_cnt
+ from item i2
+ where (i2.i_manufact = i1.i_manufact AND
+ ((i2.i_category = 'Women' AND
+ (i2.i_color = 'orchid' OR i2.i_color = 'papaya') AND
+ (i2.i_units = 'Pound' OR i2.i_units = 'Lb') AND
+ (i2.i_size = 'petite' OR i2.i_size = 'medium')
+ ) or
+ (i2.i_category = 'Women' AND
+ (i2.i_color = 'burlywood' OR i2.i_color = 'navy') AND
+ (i2.i_units = 'Bundle' OR i2.i_units = 'Each') AND
+ (i2.i_size = 'N/A' OR i2.i_size = 'extra large')
+ ) or
+ (i2.i_category = 'Men' AND
+ (i2.i_color = 'bisque' OR i2.i_color = 'azure') AND
+ (i2.i_units = 'N/A' OR i2.i_units = 'Tsp') AND
+ (i2.i_size = 'small' OR i2.i_size = 'large')
+ ) or
+ (i2.i_category = 'Men' AND
+ (i2.i_color = 'chocolate' OR i2.i_color = 'cornflower') AND
+ (i2.i_units = 'Bunch' OR i2.i_units = 'Gross') AND
+ (i2.i_size = 'petite' OR i2.i_size = 'medium')
+ ))) or
+ (i2.i_manufact = i1.i_manufact AND
+ ((i2.i_category = 'Women' AND
+ (i2.i_color = 'salmon' OR i2.i_color = 'midnight') AND
+ (i2.i_units = 'Oz' OR i2.i_units = 'Box') AND
+ (i2.i_size = 'petite' OR i2.i_size = 'medium')
+ ) or
+ (i2.i_category = 'Women' AND
+ (i2.i_color = 'snow' OR i2.i_color = 'steel') AND
+ (i2.i_units = 'Carton' OR i2.i_units = 'Tbl') AND
+ (i2.i_size = 'N/A' OR i2.i_size = 'extra large')
+ ) or
+ (i2.i_category = 'Men' AND
+ (i2.i_color = 'purple' OR i2.i_color = 'gainsboro') AND
+ (i2.i_units = 'Dram' OR i2.i_units = 'Unknown') AND
+ (i2.i_size = 'small' OR i2.i_size = 'large')
+ ) or
+ (i2.i_category = 'Men' AND
+ (i2.i_color = 'metallic' OR i2.i_color = 'forest') AND
+ (i2.i_units = 'Gram' OR i2.i_units = 'Ounce') AND
+ (i2.i_size = 'petite' OR i2.i_size = 'medium')
+ ))))) > 0
+ ORDER BY i1.i_product_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/q42/q42.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.1.ddl.sqlpp
new file mode 100644
index 0000000..acd3953
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.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/q42/q42.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.2.update.sqlpp
new file mode 100644
index 0000000..dfdaad1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.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/q42/q42.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.3.query.sqlpp
new file mode 100644
index 0000000..cee34eb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q42/q42.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_category_id
+ ,item.i_category
+ ,sum(ss_ext_sales_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_category_id
+ ,item.i_category
+ ORDER BY SUM(ss_ext_sales_price) desc,dt.d_year
+ ,item.i_category_id
+ ,item.i_category
+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/q43/q43.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.1.ddl.sqlpp
new file mode 100644
index 0000000..1c86ec3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.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.date_dim_type as
+ closed {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+}
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.store_type as
+ closed {
+ s_store_sk : bigint,
+ s_store_id : string,
+ s_rec_start_date : string?,
+ s_rec_end_date : string?,
+ s_closed_date_sk : bigint?,
+ s_store_name : string?,
+ s_number_employees : bigint?,
+ s_floor_space : bigint?,
+ s_hours : string?,
+ s_manager : string?,
+ s_market_id : bigint?,
+ s_geography_class : string?,
+ s_market_desc : string?,
+ s_market_manager : string?,
+ s_division_id : bigint?,
+ s_division_name : string?,
+ s_company_id : bigint?,
+ s_company_name : string?,
+ s_street_number : string?,
+ s_street_name : string?,
+ s_street_type : string?,
+ s_suite_number : string?,
+ s_city : string?,
+ s_county : string?,
+ s_state : string?,
+ s_zip : string?,
+ s_country : string?,
+ s_gmt_offset : double?,
+ s_tax_precentage : double?
+}
+
+create dataset date_dim(date_dim_type) primary key d_date_sk;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset store (store_type) primary key s_store_sk;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.2.update.sqlpp
new file mode 100644
index 0000000..bbf1838
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.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 date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/36b2c10f/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp
new file mode 100644
index 0000000..6e77ed4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q43/q43.3.query.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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 s_store_name, s_store_id,
+ 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 date_dim, store_sales, store
+ where d_date_sk = ss_sold_date_sk and
+ s_store_sk = ss_store_sk and
+ s_gmt_offset = -5 and
+ d_year = 1998
+ GROUP BY s_store_name, s_store_id
+ ORDER BY s_store_name, s_store_id,sun_sales,mon_sales,tue_sales,wed_sales,thu_sales,fri_sales,sat_sales
+ 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/q45/q45.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.1.ddl.sqlpp
new file mode 100644
index 0000000..4eebc51
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.1.ddl.sqlpp
@@ -0,0 +1,169 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+
+
+create type tpcds.customer_address_type as closed {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ }
+
+create type tpcds.item_type as
+ closed {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+}
+
+create type tpcds.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.customer_type as
+ closed {
+ c_customer_sk : bigint,
+ c_customer_id : string,
+ c_current_cdemo_sk : bigint?,
+ c_current_hdemo_sk : bigint?,
+ c_current_addr_sk : bigint?,
+ c_first_shipto_date_sk : bigint?,
+ c_first_sales_date_sk : bigint?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : bigint?,
+ c_birth_month : bigint?,
+ c_birth_year : bigint?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+}
+
+create dataset customer_address(customer_address_type) primary key ca_address_sk;
+
+create dataset item (item_type) primary key i_item_sk;
+
+create dataset 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 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/q45/q45.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.2.update.sqlpp
new file mode 100644
index 0000000..cd8b95d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.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 customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset 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 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/q45/q45.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp
new file mode 100644
index 0000000..83acd4a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q45/q45.3.query.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE tpcds;
+SELECT ca_zip, ca_county, SUM(ws_sales_price)
+ FROM web_sales, customer, customer_address, date_dim, item i2
+ WHERE ws_bill_customer_sk = c_customer_sk
+ AND c_current_addr_sk = ca_address_sk
+ AND ws_item_sk = i2.i_item_sk
+ AND ( substr(ca_zip,1,5) IN ['85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792']
+ OR
+ i2.i_item_id IN (SELECT VALUE i1.i_item_id
+ FROM item i1
+ WHERE i1.i_item_sk in [2, 3, 5, 7, 11, 13, 17, 19, 23, 29]
+ )
+ )
+ AND ws_sold_date_sk = d_date_sk
+ AND d_qoy = 2 and d_year = 2000
+ GROUP BY ca_zip, ca_county
+ ORDER BY ca_zip, ca_county
+ 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/q46/q46.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.1.ddl.sqlpp
new file mode 100644
index 0000000..67e2de3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.1.ddl.sqlpp
@@ -0,0 +1,176 @@
+/*
+ * 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/q46/q46.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.2.update.sqlpp
new file mode 100644
index 0000000..6ab1d16
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.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/q46/q46.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.3.query.sqlpp
new file mode 100644
index 0000000..873d78b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q46/q46.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 c_last_name
+ ,c_first_name
+ ,ca_city
+ ,bought_city
+ ,ss_ticket_number
+ ,amt,profit
+ FROM
+ (SELECT ss_ticket_number
+ ,ss_customer_sk
+ ,ca_city bought_city
+ ,sum(ss_coupon_amt) amt
+ ,sum(ss_net_profit) profit
+ 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 (household_demographics.hd_dep_count = 5 or
+ household_demographics.hd_vehicle_count= 3)
+ AND date_dim.d_dow in [6,0]
+ AND date_dim.d_year in [1999,1999+1,1999+2]
+ AND store.s_city in ['Midway','Fairview','Fairview','Fairview','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
+ ,c_first_name
+ ,ca_city
+ ,bought_city
+ ,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/q50/q50.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.1.ddl.sqlpp
new file mode 100644
index 0000000..8779419
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.1.ddl.sqlpp
@@ -0,0 +1,148 @@
+/*
+ * 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_returns_type as
+ closed {
+ sr_returned_date_sk : bigint?,
+ sr_return_time_sk : bigint?,
+ sr_item_sk : bigint,
+ sr_customer_sk : bigint?,
+ sr_cdemo_sk : bigint?,
+ sr_hdemo_sk : bigint?,
+ sr_addr_sk : bigint?,
+ sr_store_sk : bigint?,
+ sr_reason_sk : bigint?,
+ sr_ticket_number : bigint,
+ sr_return_quantity : bigint?,
+ sr_return_amt : double,
+ sr_return_tax : double?,
+ sr_return_amt_inc_tax : double?,
+ sr_fee : double?,
+ sr_return_ship_cost : double?,
+ sr_refunded_cash : double?,
+ sr_reversed_charge : double?,
+ sr_store_credit : double?,
+ sr_net_loss : double?
+}
+
+create type tpcds.store_sales_type as
+ closed {
+ ss_sold_date_sk: bigint?,
+ ss_sold_time_sk: bigint?,
+ ss_item_sk: bigint,
+ ss_customer_sk: bigint?,
+ ss_cdemo_sk: bigint?,
+ ss_hdemo_sk: bigint?,
+ ss_addr_sk: bigint?,
+ ss_store_sk: bigint?,
+ ss_promo_sk: bigint?,
+ ss_ticket_number: bigint,
+ ss_quantity: bigint?,
+ ss_wholesale_cost: double?,
+ ss_list_price: double?,
+ ss_sales_price: double?,
+ ss_ext_discount_amt: double?,
+ ss_ext_sales_price: double?,
+ ss_ext_wholesale_cost: double?,
+ ss_ext_list_price: double?,
+ ss_ext_tax: double?,
+ ss_coupon_amt: double?,
+ ss_net_paid: double?,
+ ss_net_paid_inc_tax: double?,
+ ss_net_profit: double?
+}
+
+create type tpcds.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_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number;
+
+create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number;
+
+create dataset 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/q50/q50.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.2.update.sqlpp
new file mode 100644
index 0000000..d50d706
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.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 store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+load dataset 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/q50/q50.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp
new file mode 100644
index 0000000..5bf8683
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q50/q50.3.query.sqlpp
@@ -0,0 +1,78 @@
+/*
+ * 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
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+ ,SUM((CASE WHEN (sr_returned_date_sk - ss_sold_date_sk) <= 30 THEN 1 ELSE 0 END)) AS c30_days
+ ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 30 AND
+ (sr_returned_date_sk - ss_sold_date_sk) <= 60) THEN 1 ELSE 0 END )) AS c31_60_days
+ ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 60 AND
+ (sr_returned_date_sk - ss_sold_date_sk) <= 90) THEN 1 ELSE 0 END)) AS c61_90_days
+ ,SUM((CASE WHEN ((sr_returned_date_sk - ss_sold_date_sk) > 90 AND
+ (sr_returned_date_sk - ss_sold_date_sk) <= 120) THEN 1 ELSE 0 END)) AS c91_120_days
+ ,SUM((CASE WHEN (sr_returned_date_sk - ss_sold_date_sk) > 120 THEN 1 ELSE 0 END)) AS gt120_days
+FROM
+ store_sales
+ ,store_returns
+ ,store
+ ,date_dim d1
+ ,date_dim d2
+WHERE
+ d2.d_year = 2000
+AND d2.d_moy = 9
+AND ss_ticket_number = sr_ticket_number
+AND ss_item_sk = sr_item_sk
+AND ss_sold_date_sk = d1.d_date_sk
+AND sr_returned_date_sk = d2.d_date_sk
+AND ss_customer_sk = sr_customer_sk
+AND ss_store_sk = s_store_sk
+GROUP BY
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+ORDER BY s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+LIMIT 100;
\ No newline at end of file