You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by we...@apache.org on 2017/05/31 00:11:54 UTC
[16/17] hive git commit: HIVE-16764: Support numeric as same as
decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
HIVE-16764: Support numeric as same as decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/86b18772
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/86b18772
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/86b18772
Branch: refs/heads/hive-14535
Commit: 86b18772d277488b04146c9618a1da544389ae47
Parents: 824b9c8
Author: Pengcheng Xiong <px...@hortonworks.com>
Authored: Tue May 30 17:00:59 2017 -0700
Committer: Pengcheng Xiong <px...@hortonworks.com>
Committed: Tue May 30 17:00:59 2017 -0700
----------------------------------------------------------------------
.../org/apache/hadoop/hive/ql/parse/HiveLexer.g | 2 +-
.../test/queries/clientpositive/perf/query1.q | 7 +-
.../test/queries/clientpositive/perf/query10.q | 61 ++
.../test/queries/clientpositive/perf/query12.q | 46 +-
.../test/queries/clientpositive/perf/query13.q | 106 +--
.../test/queries/clientpositive/perf/query14.q | 5 +-
.../test/queries/clientpositive/perf/query15.q | 23 +-
.../test/queries/clientpositive/perf/query16.q | 9 +-
.../test/queries/clientpositive/perf/query17.q | 48 +-
.../test/queries/clientpositive/perf/query18.q | 37 +-
.../test/queries/clientpositive/perf/query19.q | 28 +-
.../test/queries/clientpositive/perf/query2.q | 62 ++
.../test/queries/clientpositive/perf/query20.q | 32 +-
.../test/queries/clientpositive/perf/query21.q | 3 +
.../test/queries/clientpositive/perf/query22.q | 3 +
.../test/queries/clientpositive/perf/query23.q | 6 +-
.../test/queries/clientpositive/perf/query24.q | 100 +--
.../test/queries/clientpositive/perf/query25.q | 51 +-
.../test/queries/clientpositive/perf/query26.q | 24 +-
.../test/queries/clientpositive/perf/query27.q | 3 +
.../test/queries/clientpositive/perf/query28.q | 5 +-
.../test/queries/clientpositive/perf/query29.q | 50 +-
.../test/queries/clientpositive/perf/query3.q | 24 +-
.../test/queries/clientpositive/perf/query30.q | 7 +-
.../test/queries/clientpositive/perf/query31.q | 54 +-
.../test/queries/clientpositive/perf/query32.q | 47 +-
.../test/queries/clientpositive/perf/query33.q | 6 +-
.../test/queries/clientpositive/perf/query34.q | 33 +-
.../test/queries/clientpositive/perf/query35.q | 59 ++
.../test/queries/clientpositive/perf/query36.q | 9 +-
.../test/queries/clientpositive/perf/query37.q | 3 +
.../test/queries/clientpositive/perf/query38.q | 3 +
.../test/queries/clientpositive/perf/query39.q | 56 +-
.../test/queries/clientpositive/perf/query40.q | 3 +
.../test/queries/clientpositive/perf/query42.q | 25 +-
.../test/queries/clientpositive/perf/query43.q | 22 +-
.../test/queries/clientpositive/perf/query44.q | 37 +
.../test/queries/clientpositive/perf/query45.q | 22 +
.../test/queries/clientpositive/perf/query46.q | 38 +-
.../test/queries/clientpositive/perf/query48.q | 69 +-
.../test/queries/clientpositive/perf/query49.q | 129 +++
.../test/queries/clientpositive/perf/query5.q | 3 +
.../test/queries/clientpositive/perf/query50.q | 25 +-
.../test/queries/clientpositive/perf/query51.q | 14 +-
.../test/queries/clientpositive/perf/query52.q | 25 +-
.../test/queries/clientpositive/perf/query53.q | 30 +
.../test/queries/clientpositive/perf/query54.q | 59 +-
.../test/queries/clientpositive/perf/query55.q | 17 +-
.../test/queries/clientpositive/perf/query56.q | 7 +-
.../test/queries/clientpositive/perf/query58.q | 86 +-
.../test/queries/clientpositive/perf/query59.q | 46 +
.../test/queries/clientpositive/perf/query6.q | 7 +-
.../test/queries/clientpositive/perf/query60.q | 5 +-
.../test/queries/clientpositive/perf/query61.q | 46 +
.../test/queries/clientpositive/perf/query63.q | 31 +
.../test/queries/clientpositive/perf/query64.q | 121 ++-
.../test/queries/clientpositive/perf/query65.q | 65 +-
.../test/queries/clientpositive/perf/query66.q | 259 +++---
.../test/queries/clientpositive/perf/query67.q | 3 +-
.../test/queries/clientpositive/perf/query68.q | 45 +-
.../test/queries/clientpositive/perf/query69.q | 7 +-
.../test/queries/clientpositive/perf/query7.q | 24 +-
.../test/queries/clientpositive/perf/query70.q | 19 +-
.../test/queries/clientpositive/perf/query71.q | 42 +-
.../test/queries/clientpositive/perf/query72.q | 3 +
.../test/queries/clientpositive/perf/query73.q | 30 +-
.../test/queries/clientpositive/perf/query75.q | 73 +-
.../test/queries/clientpositive/perf/query76.q | 27 +-
.../test/queries/clientpositive/perf/query77.q | 109 +++
.../test/queries/clientpositive/perf/query78.q | 60 ++
.../test/queries/clientpositive/perf/query79.q | 26 +-
.../test/queries/clientpositive/perf/query8.q | 3 +
.../test/queries/clientpositive/perf/query80.q | 3 +
.../test/queries/clientpositive/perf/query81.q | 7 +-
.../test/queries/clientpositive/perf/query82.q | 3 +
.../test/queries/clientpositive/perf/query83.q | 6 +-
.../test/queries/clientpositive/perf/query84.q | 24 +-
.../test/queries/clientpositive/perf/query85.q | 87 +-
.../test/queries/clientpositive/perf/query86.q | 9 +-
.../test/queries/clientpositive/perf/query87.q | 3 +
.../test/queries/clientpositive/perf/query88.q | 54 +-
.../test/queries/clientpositive/perf/query89.q | 9 +-
.../test/queries/clientpositive/perf/query9.q | 19 +-
.../test/queries/clientpositive/perf/query90.q | 24 +-
.../test/queries/clientpositive/perf/query91.q | 33 +-
.../test/queries/clientpositive/perf/query92.q | 33 +-
.../test/queries/clientpositive/perf/query93.q | 21 +-
.../test/queries/clientpositive/perf/query94.q | 32 +-
.../test/queries/clientpositive/perf/query95.q | 35 +-
.../test/queries/clientpositive/perf/query96.q | 19 +-
.../test/queries/clientpositive/perf/query97.q | 28 +-
.../test/queries/clientpositive/perf/query98.q | 39 +-
.../test/queries/clientpositive/perf/query99.q | 37 +
.../results/clientpositive/perf/query1.q.out | 6 +-
.../results/clientpositive/perf/query10.q.out | 296 +++++++
.../results/clientpositive/perf/query12.q.out | 178 ++--
.../results/clientpositive/perf/query13.q.out | 204 +++--
.../results/clientpositive/perf/query15.q.out | 38 +-
.../results/clientpositive/perf/query16.q.out | 302 +++----
.../results/clientpositive/perf/query17.q.out | 88 +-
.../results/clientpositive/perf/query18.q.out | 70 +-
.../results/clientpositive/perf/query19.q.out | 48 +-
.../results/clientpositive/perf/query2.q.out | 259 ++++++
.../results/clientpositive/perf/query20.q.out | 74 +-
.../results/clientpositive/perf/query23.q.out | 8 +-
.../results/clientpositive/perf/query24.q.out | 190 ++--
.../results/clientpositive/perf/query25.q.out | 100 ++-
.../results/clientpositive/perf/query26.q.out | 40 +-
.../results/clientpositive/perf/query28.q.out | 6 +-
.../results/clientpositive/perf/query29.q.out | 98 ++-
.../results/clientpositive/perf/query3.q.out | 40 +-
.../results/clientpositive/perf/query30.q.out | 6 +-
.../results/clientpositive/perf/query31.q.out | 585 +++++++------
.../results/clientpositive/perf/query32.q.out | 248 +++---
.../results/clientpositive/perf/query33.q.out | 6 +-
.../results/clientpositive/perf/query34.q.out | 66 +-
.../results/clientpositive/perf/query35.q.out | 292 +++++++
.../results/clientpositive/perf/query36.q.out | 12 +-
.../results/clientpositive/perf/query39.q.out | 108 ++-
.../results/clientpositive/perf/query42.q.out | 126 ++-
.../results/clientpositive/perf/query43.q.out | 36 +-
.../results/clientpositive/perf/query44.q.out | 279 ++++++
.../results/clientpositive/perf/query45.q.out | 180 ++++
.../results/clientpositive/perf/query46.q.out | 72 +-
.../results/clientpositive/perf/query48.q.out | 130 ++-
.../results/clientpositive/perf/query49.q.out | 504 +++++++++++
.../results/clientpositive/perf/query50.q.out | 44 +-
.../results/clientpositive/perf/query51.q.out | 26 +-
.../results/clientpositive/perf/query52.q.out | 42 +-
.../results/clientpositive/perf/query53.q.out | 141 +++
.../results/clientpositive/perf/query54.q.out | 521 +++++++----
.../results/clientpositive/perf/query55.q.out | 26 +-
.../results/clientpositive/perf/query56.q.out | 6 +-
.../results/clientpositive/perf/query58.q.out | 674 ++++++++------
.../results/clientpositive/perf/query59.q.out | 238 +++++
.../results/clientpositive/perf/query6.q.out | 6 +-
.../results/clientpositive/perf/query60.q.out | 6 +-
.../results/clientpositive/perf/query61.q.out | 300 +++++++
.../results/clientpositive/perf/query62.q.out | 164 ++++
.../results/clientpositive/perf/query63.q.out | 143 +++
.../results/clientpositive/perf/query64.q.out | 244 +++++-
.../results/clientpositive/perf/query65.q.out | 124 ++-
.../results/clientpositive/perf/query66.q.out | 512 +++++------
.../results/clientpositive/perf/query68.q.out | 86 +-
.../results/clientpositive/perf/query69.q.out | 6 +-
.../results/clientpositive/perf/query7.q.out | 40 +-
.../results/clientpositive/perf/query70.q.out | 34 +-
.../results/clientpositive/perf/query71.q.out | 76 +-
.../results/clientpositive/perf/query73.q.out | 60 +-
.../results/clientpositive/perf/query75.q.out | 874 +++++++++++--------
.../results/clientpositive/perf/query76.q.out | 46 +-
.../results/clientpositive/perf/query77.q.out | 509 +++++++++++
.../results/clientpositive/perf/query78.q.out | 302 +++++++
.../results/clientpositive/perf/query79.q.out | 68 +-
.../results/clientpositive/perf/query81.q.out | 6 +-
.../results/clientpositive/perf/query83.q.out | 6 +-
.../results/clientpositive/perf/query84.q.out | 66 +-
.../results/clientpositive/perf/query85.q.out | 168 +++-
.../results/clientpositive/perf/query86.q.out | 12 +-
.../results/clientpositive/perf/query88.q.out | 96 +-
.../results/clientpositive/perf/query89.q.out | 12 +-
.../results/clientpositive/perf/query9.q.out | 26 +-
.../results/clientpositive/perf/query90.q.out | 42 +-
.../results/clientpositive/perf/query91.q.out | 62 +-
.../results/clientpositive/perf/query92.q.out | 229 +++--
.../results/clientpositive/perf/query93.q.out | 34 +-
.../results/clientpositive/perf/query94.q.out | 333 ++++---
.../results/clientpositive/perf/query95.q.out | 341 +++++---
.../results/clientpositive/perf/query96.q.out | 144 +--
.../results/clientpositive/perf/query97.q.out | 52 +-
.../results/clientpositive/perf/query98.q.out | 86 +-
.../results/clientpositive/perf/query99.q.out | 164 ++++
172 files changed, 12021 insertions(+), 3070 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g b/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
index 393df65..da52cd5 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
@@ -134,7 +134,7 @@ KW_TIMESTAMPTZ: 'TIMESTAMPTZ';
KW_TIME: 'TIME';
KW_ZONE: 'ZONE';
KW_INTERVAL: 'INTERVAL';
-KW_DECIMAL: 'DECIMAL' | 'DEC';
+KW_DECIMAL: 'DECIMAL' | 'DEC' | 'NUMERIC';
KW_STRING: 'STRING';
KW_CHAR: 'CHAR';
KW_VARCHAR: 'VARCHAR';
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query1.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query1.q b/ql/src/test/queries/clientpositive/perf/query1.q
index d40f66a..a8d7072 100644
--- a/ql/src/test/queries/clientpositive/perf/query1.q
+++ b/ql/src/test/queries/clientpositive/perf/query1.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain with customer_total_return as
+-- start query 1 in stream 0 using template query1.tpl and seed 2031708268
+explain
+with customer_total_return as
(select sr_customer_sk as ctr_customer_sk
,sr_store_sk as ctr_store_sk
,sum(SR_FEE) as ctr_total_return
@@ -22,3 +23,5 @@ and s_state = 'NM'
and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id
limit 100;
+
+-- end query 1 in stream 0 using template query1.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query10.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query10.q b/ql/src/test/queries/clientpositive/perf/query10.q
new file mode 100644
index 0000000..d3b1be7
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query10.q
@@ -0,0 +1,61 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query10.tpl and seed 797269820
+explain
+select
+ cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ count(*) cnt1,
+ cd_purchase_estimate,
+ count(*) cnt2,
+ cd_credit_rating,
+ count(*) cnt3,
+ cd_dep_count,
+ count(*) cnt4,
+ cd_dep_employed_count,
+ count(*) cnt5,
+ cd_dep_college_count,
+ count(*) cnt6
+ from
+ customer c,customer_address ca,customer_demographics
+ where
+ c.c_current_addr_sk = ca.ca_address_sk and
+ ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ exists (select *
+ from store_sales,date_dim
+ where c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3) and
+ (exists (select *
+ from web_sales,date_dim
+ where c.c_customer_sk = ws_bill_customer_sk and
+ ws_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 ANd 4+3) or
+ exists (select *
+ from catalog_sales,date_dim
+ where c.c_customer_sk = cs_ship_customer_sk and
+ cs_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3))
+ group by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ order by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+limit 100;
+
+-- end query 1 in stream 0 using template query10.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query12.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query12.q b/ql/src/test/queries/clientpositive/perf/query12.q
index 7ef6cb8..59b50ac 100644
--- a/ql/src/test/queries/clientpositive/perf/query12.q
+++ b/ql/src/test/queries/clientpositive/perf/query12.q
@@ -1,13 +1,35 @@
-set hive.cbo.enable=false;
-explain
-select
-i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio
-from web_sales ,item ,date_dim
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query12.tpl and seed 345591136
+explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(ws_ext_sales_price) as itemrevenue
+ ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+from
+ web_sales
+ ,item
+ ,date_dim
where
-web_sales.ws_item_sk = item.i_item_sk
-and item.i_category in ('Jewelry', 'Sports', 'Books')
-and web_sales.ws_sold_date_sk = date_dim.d_date_sk
-and date_dim.d_date between cast('2001-01-12' as date)
- and (cast('2001-01-12' as date) + 30 days)
-group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100;
-set hive.cbo.enable=true;
+ ws_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ws_sold_date_sk = d_date_sk
+ and d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+group by
+ i_item_id
+ ,i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+order by
+ i_category
+ ,i_class
+ ,i_item_id
+ ,i_item_desc
+ ,revenueratio
+limit 100;
+
+-- end query 1 in stream 0 using template query12.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query13.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query13.q b/ql/src/test/queries/clientpositive/perf/query13.q
index 6f43d47..dca19b0 100644
--- a/ql/src/test/queries/clientpositive/perf/query13.q
+++ b/ql/src/test/queries/clientpositive/perf/query13.q
@@ -1,54 +1,54 @@
set hive.mapred.mode=nonstrict;
-EXPLAIN SELECT Avg(ss_quantity) ,
- Avg(ss_ext_sales_price) ,
- Avg(ss_ext_wholesale_cost) ,
- Sum(ss_ext_wholesale_cost)
-FROM store_sales ,
- store ,
- customer_demographics ,
- household_demographics ,
- customer_address ,
- date_dim
-WHERE store.s_store_sk = store_sales.ss_store_sk
-AND store_sales.ss_sold_date_sk = date_dim.d_date_sk
-AND date_dim.d_year = 2001
-AND ((
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'M'
- AND customer_demographics.cd_education_status = '4 yr Degree'
- AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00
- AND household_demographics.hd_dep_count = 3 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'D'
- AND customer_demographics.cd_education_status = 'Primary'
- AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00
- AND household_demographics.hd_dep_count = 1 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'U'
- AND customer_demographics.cd_education_status = 'Advanced Degree'
- AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00
- AND household_demographics.hd_dep_count = 1 ))
-AND ((
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('KY',
- 'GA',
- 'NM')
- AND store_sales.ss_net_profit BETWEEN 100 AND 200 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('MT',
- 'OR',
- 'IN')
- AND store_sales.ss_net_profit BETWEEN 150 AND 300 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('WI', 'MO', 'WV')
- AND store_sales.ss_net_profit BETWEEN 50 AND 250 )) ;
\ No newline at end of file
+-- start query 1 in stream 0 using template query13.tpl and seed 622697896
+explain
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Primary'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'U'
+ and cd_education_status = 'Advanced Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 250
+ ))
+;
+
+-- end query 1 in stream 0 using template query13.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query14.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query14.q b/ql/src/test/queries/clientpositive/perf/query14.q
index e075f4f..c12ecb5 100644
--- a/ql/src/test/queries/clientpositive/perf/query14.q
+++ b/ql/src/test/queries/clientpositive/perf/query14.q
@@ -1,6 +1,5 @@
--- SORT_BEFORE_DIFF
-set hive.strict.checks.cartesian.product=false;
-
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query14.tpl and seed 1819994127
explain
with cross_items as
(select i_item_sk ss_item_sk
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query15.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query15.q b/ql/src/test/queries/clientpositive/perf/query15.q
index 9f75410..9e1711a 100644
--- a/ql/src/test/queries/clientpositive/perf/query15.q
+++ b/ql/src/test/queries/clientpositive/perf/query15.q
@@ -1 +1,22 @@
-explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query15.tpl and seed 1819994127
+explain
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip
+ order by ca_zip
+ limit 100;
+
+-- end query 1 in stream 0 using template query15.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query16.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query16.q b/ql/src/test/queries/clientpositive/perf/query16.q
index 0243bf3..05625f7 100644
--- a/ql/src/test/queries/clientpositive/perf/query16.q
+++ b/ql/src/test/queries/clientpositive/perf/query16.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain select
+-- start query 1 in stream 0 using template query16.tpl and seed 171719422
+explain
+select
count(distinct cs_order_number) as `order count`
,sum(cs_ext_ship_cost) as `total shipping cost`
,sum(cs_net_profit) as `total net profit`
@@ -26,5 +27,7 @@ and exists (select *
and not exists(select *
from catalog_returns cr1
where cs1.cs_order_number = cr1.cr_order_number)
-order by `order count`
+order by count(distinct cs_order_number)
limit 100;
+
+-- end query 1 in stream 0 using template query16.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query17.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query17.q b/ql/src/test/queries/clientpositive/perf/query17.q
index bb8a33b..0cd4201 100644
--- a/ql/src/test/queries/clientpositive/perf/query17.q
+++ b/ql/src/test/queries/clientpositive/perf/query17.q
@@ -1 +1,47 @@
-explain select i_item_id ,i_item_desc ,s_state ,count(ss_quantity) as store_sales_quantitycount ,avg(ss_quantity) as store_sales_quantityave ,stddev_samp(ss_quantity) as store_sales_quantitystdev ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov ,count(sr_return_quantity) as_store_returns_quantitycount ,avg(sr_return_quantity) as_store_returns_quantityave ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_quarter_name = '2000Q1' and d1.d_date_sk = store_sales.ss_sold_date_sk and item.i_item_sk = store_sales.ss_item_sk
and store.s_store_sk = store_sales.ss_store_sk and store_sales.ss_customer_sk = store_returns.sr_customer_sk and store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number and store_returns.sr_returned_date_sk = d2.d_date_sk and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3') and store_returns.sr_customer_sk = catalog_sales.cs_bill_customer_sk and store_returns.sr_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_sold_date_sk = d3.d_date_sk and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3') group by i_item_id ,i_item_desc ,s_state order by i_item_id ,i_item_desc ,s_state limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query17.tpl and seed 1819994127
+explain
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as_store_returns_quantitycount
+ ,avg(sr_return_quantity) as_store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2000Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100;
+
+-- end query 1 in stream 0 using template query17.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query18.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query18.q b/ql/src/test/queries/clientpositive/perf/query18.q
index e6061f5..bf1ff59 100644
--- a/ql/src/test/queries/clientpositive/perf/query18.q
+++ b/ql/src/test/queries/clientpositive/perf/query18.q
@@ -1 +1,36 @@
-explain select i_item_id, ca_country, ca_state, ca_county, avg( cast(cs_quantity as decimal(12,2))) agg1, avg( cast(cs_list_price as decimal(12,2))) agg2, avg( cast(cs_coupon_amt as decimal(12,2))) agg3, avg( cast(cs_sales_price as decimal(12,2))) agg4, avg( cast(cs_net_profit as decimal(12,2))) agg5, avg( cast(c_birth_year as decimal(12,2))) agg6, avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7 from catalog_sales, date_dim, customer_demographics cd1, item, customer, customer_address, customer_demographics cd2 where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and catalog_sales.cs_item_sk = item.i_item_sk and catalog_sales.cs_bill_cdemo_sk = cd1.cd_demo_sk and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and cd1.cd_gender = 'M' and cd1.cd_education_status = 'College' and customer.c_current_cdemo_sk = cd2.cd_demo_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and c_birth_month in (9,5,12,4,1,10) and d_year = 2001 and ca_state in ('ND','WI','
AL' ,'NC','OK','MS','TN') group by i_item_id, ca_country, ca_state, ca_county with rollup order by ca_country, ca_state, ca_county, i_item_id limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query18.tpl and seed 1978355063
+explain
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as numeric(12,2))) agg1,
+ avg( cast(cs_list_price as numeric(12,2))) agg2,
+ avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
+ avg( cast(cs_sales_price as numeric(12,2))) agg4,
+ avg( cast(cs_net_profit as numeric(12,2))) agg5,
+ avg( cast(c_birth_year as numeric(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query18.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query19.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query19.q b/ql/src/test/queries/clientpositive/perf/query19.q
index cfb6218..5768e4b 100644
--- a/ql/src/test/queries/clientpositive/perf/query19.q
+++ b/ql/src/test/queries/clientpositive/perf/query19.q
@@ -1 +1,27 @@
-explain select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, sum(ss_ext_sales_price) ext_price from date_dim, store_sales, item,customer,customer_address,store where date_dim.d_date_sk = store_sales.ss_sold_date_sk and store_sales.ss_item_sk = item.i_item_sk and i_manager_id=7 and d_moy=11 and d_year=1999 and store_sales.ss_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and substr(ca_zip,1,5) <> substr(s_zip,1,5) and store_sales.ss_store_sk = store.s_store_sk group by i_brand ,i_brand_id ,i_manufact_id ,i_manufact order by ext_price desc ,i_brand ,i_brand_id ,i_manufact_id ,i_manufact limit 100 ;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query19.tpl and seed 1930872976
+explain
+select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item,customer,customer_address,store
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=7
+ and d_moy=11
+ and d_year=1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip,1,5) <> substr(s_zip,1,5)
+ and ss_store_sk = s_store_sk
+ group by i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ order by ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+limit 100 ;
+
+-- end query 1 in stream 0 using template query19.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query2.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query2.q b/ql/src/test/queries/clientpositive/perf/query2.q
new file mode 100644
index 0000000..26a52ef
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query2.q
@@ -0,0 +1,62 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query2.tpl and seed 1819994127
+explain
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales) x
+ union all
+ (select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales)),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,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 wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,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 wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1;
+
+-- end query 1 in stream 0 using template query2.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query20.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query20.q b/ql/src/test/queries/clientpositive/perf/query20.q
index d4ea10d..c5f8848 100644
--- a/ql/src/test/queries/clientpositive/perf/query20.q
+++ b/ql/src/test/queries/clientpositive/perf/query20.q
@@ -1 +1,31 @@
-explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(cs_ext_sales_price) as itemrevenue ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over (partition by i_class) as revenueratio from catalog_sales ,item ,date_dim where catalog_sales.cs_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query20.tpl and seed 345591136
+explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(cs_ext_sales_price) as itemrevenue
+ ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and cs_sold_date_sk = d_date_sk
+ and d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+ group by i_item_id
+ ,i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ order by i_category
+ ,i_class
+ ,i_item_id
+ ,i_item_desc
+ ,revenueratio
+limit 100;
+
+-- end query 1 in stream 0 using template query20.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query21.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query21.q b/ql/src/test/queries/clientpositive/perf/query21.q
index 4c68df9..34b458b 100644
--- a/ql/src/test/queries/clientpositive/perf/query21.q
+++ b/ql/src/test/queries/clientpositive/perf/query21.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query21.tpl and seed 1819994127
explain
select *
from(select w_warehouse_name
@@ -27,3 +29,4 @@ select *
,i_item_id
limit 100;
+-- end query 1 in stream 0 using template query21.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query22.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query22.q b/ql/src/test/queries/clientpositive/perf/query22.q
index adb509d..7049173 100644
--- a/ql/src/test/queries/clientpositive/perf/query22.q
+++ b/ql/src/test/queries/clientpositive/perf/query22.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query22.tpl and seed 1819994127
explain
select i_product_name
,i_brand
@@ -19,3 +21,4 @@ select i_product_name
order by qoh, i_product_name, i_brand, i_class, i_category
limit 100;
+-- end query 1 in stream 0 using template query22.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query23.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query23.q b/ql/src/test/queries/clientpositive/perf/query23.q
index 4939416..1e02655 100644
--- a/ql/src/test/queries/clientpositive/perf/query23.q
+++ b/ql/src/test/queries/clientpositive/perf/query23.q
@@ -1,7 +1,7 @@
--- SORT_BEFORE_DIFF
set hive.mapred.mode=nonstrict;
-
-explain with frequent_ss_items as
+-- start query 1 in stream 0 using template query23.tpl and seed 2031708268
+explain
+with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from store_sales
,date_dim
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query24.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query24.q b/ql/src/test/queries/clientpositive/perf/query24.q
index b9b747f..007d7ee 100644
--- a/ql/src/test/queries/clientpositive/perf/query24.q
+++ b/ql/src/test/queries/clientpositive/perf/query24.q
@@ -1,51 +1,51 @@
set hive.mapred.mode=nonstrict;
-
-explain with ssales as
- (select c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size
- ,sum(ss_sales_price) netpaid
- from store_sales
- ,store_returns
- ,store
- ,item
- ,customer
- ,customer_address
- where ss_ticket_number = sr_ticket_number
- and ss_item_sk = sr_item_sk
- and ss_customer_sk = c_customer_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and c_birth_country = upper(ca_country)
- and s_zip = ca_zip
- and s_market_id=7
- group by c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size)
- select c_last_name
- ,c_first_name
- ,s_store_name
- ,sum(netpaid) paid
- from ssales
- where i_color = 'orchid'
- group by c_last_name
- ,c_first_name
- ,s_store_name
- having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
- ;
-
+-- start query 1 in stream 0 using template query24.tpl and seed 1220860970
+explain
+with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_sales_price) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_birth_country = upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id=7
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'orchid'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+;
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query25.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query25.q b/ql/src/test/queries/clientpositive/perf/query25.q
index 80185ab..358cdc5 100644
--- a/ql/src/test/queries/clientpositive/perf/query25.q
+++ b/ql/src/test/queries/clientpositive/perf/query25.q
@@ -1 +1,50 @@
-explain select i_item_id ,i_item_desc ,s_store_id ,s_store_name ,sum(ss_net_profit) as store_sales_profit ,sum(sr_net_loss) as store_returns_loss ,sum(cs_net_profit) as catalog_sales_profit from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_moy = 4 and d1.d_year = 1998 and d1.d_date_sk = ss_sold_date_sk and i_item_sk = ss_item_sk and s_store_sk = ss_store_sk and ss_customer_sk = sr_customer_sk and ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number and sr_returned_date_sk = d2.d_date_sk and d2.d_moy between 4 and 10 and d2.d_year = 1998 and sr_customer_sk = cs_bill_customer_sk and sr_item_sk = cs_item_sk and cs_sold_date_sk = d3.d_date_sk and d3.d_moy between 4 and 10 and d3.d_year = 1998 group by i_item_id ,i_item_desc ,s_store_id ,s_store_name order by i_item_id ,i_item_desc ,s_store_id ,s_store_name limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query25.tpl and seed 1819994127
+explain
+select
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ,sum(ss_net_profit) as store_sales_profit
+ ,sum(sr_net_loss) as store_returns_loss
+ ,sum(cs_net_profit) as catalog_sales_profit
+ from
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where
+ d1.d_moy = 4
+ and d1.d_year = 2000
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_moy between 4 and 10
+ and d2.d_year = 2000
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_moy between 4 and 10
+ and d3.d_year = 2000
+ group by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ order by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ limit 100;
+
+-- end query 1 in stream 0 using template query25.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query26.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query26.q b/ql/src/test/queries/clientpositive/perf/query26.q
index 0e81120..b35d98c 100644
--- a/ql/src/test/queries/clientpositive/perf/query26.q
+++ b/ql/src/test/queries/clientpositive/perf/query26.q
@@ -1 +1,23 @@
-explain select i_item_id, avg(cs_quantity) agg1, avg(cs_list_price) agg2, avg(cs_coupon_amt) agg3, avg(cs_sales_price) agg4 from catalog_sales, customer_demographics, date_dim, item, promotion where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and catalog_sales.cs_item_sk = item.i_item_sk and catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk and catalog_sales.cs_promo_sk = promotion.p_promo_sk and cd_gender = 'F' and cd_marital_status = 'W' and cd_education_status = 'Primary' and (p_channel_email = 'N' or p_channel_event = 'N') and d_year = 1998 group by i_item_id order by i_item_id limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query26.tpl and seed 1930872976
+explain
+select i_item_id,
+ avg(cs_quantity) agg1,
+ avg(cs_list_price) agg2,
+ avg(cs_coupon_amt) agg3,
+ avg(cs_sales_price) agg4
+ from catalog_sales, customer_demographics, date_dim, item, promotion
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd_demo_sk and
+ cs_promo_sk = p_promo_sk and
+ cd_gender = 'F' and
+ cd_marital_status = 'W' and
+ cd_education_status = 'Primary' and
+ (p_channel_email = 'N' or p_channel_event = 'N') and
+ d_year = 1998
+ group by i_item_id
+ order by i_item_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query26.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query27.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query27.q b/ql/src/test/queries/clientpositive/perf/query27.q
index 0cbb3ae..ec09e1d 100644
--- a/ql/src/test/queries/clientpositive/perf/query27.q
+++ b/ql/src/test/queries/clientpositive/perf/query27.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query27.tpl and seed 2017787633
explain
select i_item_id,
s_state, grouping(s_state) g_state,
@@ -20,3 +22,4 @@ select i_item_id,
,s_state
limit 100;
+-- end query 1 in stream 0 using template query27.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query28.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query28.q b/ql/src/test/queries/clientpositive/perf/query28.q
index 2a5f092..b0cdfac 100644
--- a/ql/src/test/queries/clientpositive/perf/query28.q
+++ b/ql/src/test/queries/clientpositive/perf/query28.q
@@ -1,5 +1,7 @@
set hive.mapred.mode=nonstrict;
-explain select *
+-- start query 1 in stream 0 using template query28.tpl and seed 444293455
+explain
+select *
from (select avg(ss_list_price) B1_LP
,count(ss_list_price) B1_CNT
,count(distinct ss_list_price) B1_CNTD
@@ -50,3 +52,4 @@ from (select avg(ss_list_price) B1_LP
or ss_wholesale_cost between 42 and 42+20)) B6
limit 100;
+-- end query 1 in stream 0 using template query28.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query29.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query29.q b/ql/src/test/queries/clientpositive/perf/query29.q
index 1634b1f..8bf4d51 100644
--- a/ql/src/test/queries/clientpositive/perf/query29.q
+++ b/ql/src/test/queries/clientpositive/perf/query29.q
@@ -1 +1,49 @@
-explain select i_item_id ,i_item_desc ,s_store_id ,s_store_name ,sum(ss_quantity) as store_sales_quantity ,sum(sr_return_quantity) as store_returns_quantity ,sum(cs_quantity) as catalog_sales_quantity from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_moy = 2 and d1.d_year = 2000 and d1.d_date_sk = ss_sold_date_sk and i_item_sk = ss_item_sk and s_store_sk = ss_store_sk and ss_customer_sk = sr_customer_sk and ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number and sr_returned_date_sk = d2.d_date_sk and d2.d_moy between 2 and 2 + 3 and d2.d_year = 2000 and sr_customer_sk = cs_bill_customer_sk and sr_item_sk = cs_item_sk and cs_sold_date_sk = d3.d_date_sk and d3.d_year in (2000,2000+1,2000+2) group by i_item_id ,i_item_desc ,s_store_id ,s_store_name order by i_item_id ,i_item_desc ,s_store_id ,s_store_name limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query29.tpl and seed 2031708268
+explain
+select
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ ,sum(ss_quantity) as store_sales_quantity
+ ,sum(sr_return_quantity) as store_returns_quantity
+ ,sum(cs_quantity) as catalog_sales_quantity
+ from
+ store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where
+ d1.d_moy = 4
+ and d1.d_year = 1999
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_moy between 4 and 4 + 3
+ and d2.d_year = 1999
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_year in (1999,1999+1,1999+2)
+ group by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ order by
+ i_item_id
+ ,i_item_desc
+ ,s_store_id
+ ,s_store_name
+ limit 100;
+
+-- end query 1 in stream 0 using template query29.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query3.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query3.q b/ql/src/test/queries/clientpositive/perf/query3.q
index 47dfac5..a70a62f 100644
--- a/ql/src/test/queries/clientpositive/perf/query3.q
+++ b/ql/src/test/queries/clientpositive/perf/query3.q
@@ -1 +1,23 @@
-explain select dt.d_year ,item.i_brand_id brand_id ,item.i_brand brand ,sum(ss_ext_sales_price) sum_agg 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_manufact_id = 436 and dt.d_moy=12 group by dt.d_year ,item.i_brand ,item.i_brand_id order by dt.d_year ,sum_agg desc ,brand_id limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query3.tpl and seed 2031708268
+explain
+select dt.d_year
+ ,item.i_brand_id brand_id
+ ,item.i_brand brand
+ ,sum(ss_ext_sales_price) sum_agg
+ 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_manufact_id = 436
+ and dt.d_moy=12
+ group by dt.d_year
+ ,item.i_brand
+ ,item.i_brand_id
+ order by dt.d_year
+ ,sum_agg desc
+ ,brand_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query3.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query30.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query30.q b/ql/src/test/queries/clientpositive/perf/query30.q
index 25c9e07..47f0d93 100644
--- a/ql/src/test/queries/clientpositive/perf/query30.q
+++ b/ql/src/test/queries/clientpositive/perf/query30.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain with customer_total_return as
+-- start query 1 in stream 0 using template query30.tpl and seed 1819994127
+explain
+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
@@ -28,3 +29,5 @@ explain with customer_total_return as
,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;
+
+-- end query 1 in stream 0 using template query30.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query31.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query31.q b/ql/src/test/queries/clientpositive/perf/query31.q
index d448dc3..42c3ca6 100644
--- a/ql/src/test/queries/clientpositive/perf/query31.q
+++ b/ql/src/test/queries/clientpositive/perf/query31.q
@@ -1,2 +1,54 @@
set hive.mapred.mode=nonstrict;
-explain 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 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 = 1998 and ss1.ca_county = ss2.ca_county and ss2.d_qoy = 2 and ss2.d_year = 1998 and ss2.ca_county = ss3.ca_county and ss3.d_qoy = 3 and ss3.d_year = 1998 and ss1.ca_county = ws1.ca_county and ws1.d_qoy = 1 and ws1.d_year =
1998 and ws1.ca_county = ws2.ca_county and ws2.d_qoy = 2 and ws2.d_year = 1998 and ws1.ca_county = ws3.ca_county and ws3.d_qoy = 3 and ws3.d_year =1998 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 web_q1_q2_increase;
+-- start query 1 in stream 0 using template query31.tpl and seed 1819994127
+explain
+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;
+
+-- end query 1 in stream 0 using template query31.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query32.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query32.q b/ql/src/test/queries/clientpositive/perf/query32.q
index ceb2ddb..ed43b4d 100644
--- a/ql/src/test/queries/clientpositive/perf/query32.q
+++ b/ql/src/test/queries/clientpositive/perf/query32.q
@@ -1,19 +1,30 @@
-explain SELECT sum(cs1.cs_ext_discount_amt) as excess_discount_amount
-FROM (SELECT cs.cs_item_sk as cs_item_sk,
- cs.cs_ext_discount_amt as cs_ext_discount_amt
- FROM catalog_sales cs
- JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk)
- WHERE d.d_date between '2000-01-27' and '2000-04-27') cs1
-JOIN item i ON (i.i_item_sk = cs1.cs_item_sk)
-JOIN (SELECT cs2.cs_item_sk as cs_item_sk,
- 1.3 * avg(cs_ext_discount_amt) as avg_cs_ext_discount_amt
- FROM (SELECT cs.cs_item_sk as cs_item_sk,
- cs.cs_ext_discount_amt as cs_ext_discount_amt
- FROM catalog_sales cs
- JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk)
- WHERE d.d_date between '2000-01-27' and '2000-04-27') cs2
- GROUP BY cs2.cs_item_sk) tmp1
-ON (i.i_item_sk = tmp1.cs_item_sk)
-WHERE i.i_manufact_id = 436 and
- cs1.cs_ext_discount_amt > tmp1.avg_cs_ext_discount_amt;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query32.tpl and seed 2031708268
+explain
+select sum(cs_ext_discount_amt) as `excess discount amount`
+from
+ catalog_sales
+ ,item
+ ,date_dim
+where
+i_manufact_id = 269
+and i_item_sk = cs_item_sk
+and d_date between '1998-03-18' and
+ (cast('1998-03-18' as date) + 90 days)
+and d_date_sk = cs_sold_date_sk
+and cs_ext_discount_amt
+ > (
+ select
+ 1.3 * avg(cs_ext_discount_amt)
+ from
+ catalog_sales
+ ,date_dim
+ where
+ cs_item_sk = i_item_sk
+ and d_date between '1998-03-18' and
+ (cast('1998-03-18' as date) + 90 days)
+ and d_date_sk = cs_sold_date_sk
+ )
+limit 100;
+-- end query 1 in stream 0 using template query32.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query33.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query33.q b/ql/src/test/queries/clientpositive/perf/query33.q
index 06628bd..1dfa9be 100644
--- a/ql/src/test/queries/clientpositive/perf/query33.q
+++ b/ql/src/test/queries/clientpositive/perf/query33.q
@@ -1,5 +1,7 @@
+set hive.mapred.mode=nonstrict;
-- start query 1 in stream 0 using template query33.tpl and seed 1930872976
-explain with ss as (
+explain
+with ss as (
select
i_manufact_id,sum(ss_ext_sales_price) total_sales
from
@@ -71,3 +73,5 @@ where i_category in ('Books'))
group by i_manufact_id
order by total_sales
limit 100;
+
+-- end query 1 in stream 0 using template query33.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query34.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query34.q b/ql/src/test/queries/clientpositive/perf/query34.q
index 5c3efe1..427eed6 100644
--- a/ql/src/test/queries/clientpositive/perf/query34.q
+++ b/ql/src/test/queries/clientpositive/perf/query34.q
@@ -1,2 +1,33 @@
set hive.mapred.mode=nonstrict;
-explain 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 ,count(*) 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 between 1 and 3 or date_dim.d_dom between 25 and 28) and (household_demographics.hd_buy_potential = '1001-5000' or household_demographics.hd_buy_potential = '5001-10000') 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 ('Kittitas County','Adams County','Richland County','Furnas County', 'Orange County','Appanoose County','Franklin Parish','Tehama County') group by
ss_ticket_number,ss_customer_sk) dn,customer where dn.ss_customer_sk = customer.c_customer_sk and cnt between 15 and 20 order by c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc;
+-- start query 1 in stream 0 using template query34.tpl and seed 1971067816
+explain
+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
+ ,count(*) 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 between 1 and 3 or date_dim.d_dom between 25 and 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 (2000,2000+1,2000+2)
+ and store.s_county in ('Mobile County','Maverick County','Huron County','Kittitas County',
+ 'Fairfield County','Jackson County','Barrow County','Pennington County')
+ group by ss_ticket_number,ss_customer_sk) dn,customer
+ where ss_customer_sk = c_customer_sk
+ and cnt between 15 and 20
+ order by c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc;
+
+-- end query 1 in stream 0 using template query34.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query35.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query35.q b/ql/src/test/queries/clientpositive/perf/query35.q
new file mode 100644
index 0000000..19951ac
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query35.q
@@ -0,0 +1,59 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query35.tpl and seed 1930872976
+explain
+select
+ ca_state,
+ cd_gender,
+ cd_marital_status,
+ count(*) cnt1,
+ avg(cd_dep_count),
+ max(cd_dep_count),
+ sum(cd_dep_count),
+ cd_dep_employed_count,
+ count(*) cnt2,
+ avg(cd_dep_employed_count),
+ max(cd_dep_employed_count),
+ sum(cd_dep_employed_count),
+ cd_dep_college_count,
+ count(*) cnt3,
+ avg(cd_dep_college_count),
+ max(cd_dep_college_count),
+ sum(cd_dep_college_count)
+ from
+ customer c,customer_address ca,customer_demographics
+ where
+ c.c_current_addr_sk = ca.ca_address_sk and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ exists (select *
+ from store_sales,date_dim
+ where c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 1999 and
+ d_qoy < 4) and
+ (exists (select *
+ from web_sales,date_dim
+ where c.c_customer_sk = ws_bill_customer_sk and
+ ws_sold_date_sk = d_date_sk and
+ d_year = 1999 and
+ d_qoy < 4) or
+ exists (select *
+ from catalog_sales,date_dim
+ where c.c_customer_sk = cs_ship_customer_sk and
+ cs_sold_date_sk = d_date_sk and
+ d_year = 1999 and
+ d_qoy < 4))
+ group by ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ order by ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ limit 100;
+
+-- end query 1 in stream 0 using template query35.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query36.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query36.q b/ql/src/test/queries/clientpositive/perf/query36.q
index 6c3a945..789f932 100644
--- a/ql/src/test/queries/clientpositive/perf/query36.q
+++ b/ql/src/test/queries/clientpositive/perf/query36.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query36.tpl and seed 1544728811
explain
select
sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
@@ -5,9 +7,9 @@ select
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
- partition by grouping(i_category)+grouping(i_class),
- case when grouping(i_class) = 0 then i_category end
- order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
+ partition by grouping(i_category)+grouping(i_class),
+ case when grouping(i_class) = 0 then i_category end
+ order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
from
store_sales
,date_dim d1
@@ -27,3 +29,4 @@ select
,rank_within_parent
limit 100;
+-- end query 1 in stream 0 using template query36.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query37.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query37.q b/ql/src/test/queries/clientpositive/perf/query37.q
index c71ec56..811eab0 100644
--- a/ql/src/test/queries/clientpositive/perf/query37.q
+++ b/ql/src/test/queries/clientpositive/perf/query37.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query37.tpl and seed 301843662
explain
select i_item_id
,i_item_desc
@@ -14,3 +16,4 @@ select i_item_id
order by i_item_id
limit 100;
+-- end query 1 in stream 0 using template query37.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query38.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query38.q b/ql/src/test/queries/clientpositive/perf/query38.q
index 2e8517e..8eade8a 100644
--- a/ql/src/test/queries/clientpositive/perf/query38.q
+++ b/ql/src/test/queries/clientpositive/perf/query38.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query38.tpl and seed 1819994127
explain
select count(*) from (
select distinct c_last_name, c_first_name, d_date
@@ -20,3 +22,4 @@ select count(*) from (
) hot_cust
limit 100;
+-- end query 1 in stream 0 using template query38.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query39.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query39.q b/ql/src/test/queries/clientpositive/perf/query39.q
index c9f8f2b..d3c806d 100644
--- a/ql/src/test/queries/clientpositive/perf/query39.q
+++ b/ql/src/test/queries/clientpositive/perf/query39.q
@@ -1,2 +1,56 @@
set hive.mapred.mode=nonstrict;
-explain with inv as (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy ,stdev,mean, case mean when 0 then null else stdev/mean end cov from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean from inventory ,item ,warehouse ,date_dim where inv_item_sk = i_item_sk and inv_warehouse_sk = w_warehouse_sk and inv_date_sk = d_date_sk and d_year =1999 group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo where case mean when 0 then 0 else stdev/mean end > 1) select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov from inv inv1,inv inv2 where inv1.i_item_sk = inv2.i_item_sk and inv1.w_warehouse_sk = inv2.w_warehouse_sk and inv1.d_moy=3 and inv2.d_moy=3+1 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov ,inv2.d_moy,inv2.mean, inv2.cov ; with inv as (select w_warehouse_name,w_warehouse_sk,i_item_sk,
d_moy ,stdev,mean, case mean when 0 then null else stdev/mean end cov from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean from inventory ,item ,warehouse ,date_dim where inv_item_sk = i_item_sk and inv_warehouse_sk = w_warehouse_sk and inv_date_sk = d_date_sk and d_year =1999 group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo where case mean when 0 then 0 else stdev/mean end > 1) select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov from inv inv1,inv inv2 where inv1.i_item_sk = inv2.i_item_sk and inv1.w_warehouse_sk = inv2.w_warehouse_sk and inv1.d_moy=3 and inv2.d_moy=3+1 and inv1.cov > 1.5 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov ,inv2.d_moy,inv2.mean, inv2.cov ;
+-- start query 1 in stream 0 using template query39.tpl and seed 1327317894
+explain
+with inv as
+(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stdev,mean, case mean when 0 then null else stdev/mean end cov
+ from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
+ from inventory
+ ,item
+ ,warehouse
+ ,date_dim
+ where inv_item_sk = i_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_year =1999
+ group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
+ where case mean when 0 then 0 else stdev/mean end > 1)
+select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
+ ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
+from inv inv1,inv inv2
+where inv1.i_item_sk = inv2.i_item_sk
+ and inv1.w_warehouse_sk = inv2.w_warehouse_sk
+ and inv1.d_moy=4
+ and inv2.d_moy=4+1
+order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
+ ,inv2.d_moy,inv2.mean, inv2.cov
+;
+with inv as
+(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stdev,mean, case mean when 0 then null else stdev/mean end cov
+ from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
+ ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
+ from inventory
+ ,item
+ ,warehouse
+ ,date_dim
+ where inv_item_sk = i_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_year =1999
+ group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
+ where case mean when 0 then 0 else stdev/mean end > 1)
+select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
+ ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
+from inv inv1,inv inv2
+where inv1.i_item_sk = inv2.i_item_sk
+ and inv1.w_warehouse_sk = inv2.w_warehouse_sk
+ and inv1.d_moy=4
+ and inv2.d_moy=4+1
+ and inv1.cov > 1.5
+order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
+ ,inv2.d_moy,inv2.mean, inv2.cov
+;
+
+-- end query 1 in stream 0 using template query39.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query40.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query40.q b/ql/src/test/queries/clientpositive/perf/query40.q
index 155fa08..61f5ad3 100644
--- a/ql/src/test/queries/clientpositive/perf/query40.q
+++ b/ql/src/test/queries/clientpositive/perf/query40.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query40.tpl and seed 1819994127
explain
select
w_state
@@ -25,3 +27,4 @@ select
order by w_state,i_item_id
limit 100;
+-- end query 1 in stream 0 using template query40.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query42.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query42.q b/ql/src/test/queries/clientpositive/perf/query42.q
index c4b115b..6b8abe0 100644
--- a/ql/src/test/queries/clientpositive/perf/query42.q
+++ b/ql/src/test/queries/clientpositive/perf/query42.q
@@ -1 +1,24 @@
-explain select dt.d_year ,item.i_category_id ,item.i_category ,sum(ss_ext_sales_price) as s 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 s desc,dt.d_year ,item.i_category_id ,item.i_category limit 100 ;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query42.tpl and seed 1819994127
+explain
+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 ;
+
+-- end query 1 in stream 0 using template query42.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query43.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query43.q b/ql/src/test/queries/clientpositive/perf/query43.q
index f1c22e8..ebdc69d 100644
--- a/ql/src/test/queries/clientpositive/perf/query43.q
+++ b/ql/src/test/queries/clientpositive/perf/query43.q
@@ -1 +1,21 @@
-explain 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 date_dim.d_date_sk = store_sales.ss_sold_date_sk and store.s_store_sk = store_sales.ss_store_sk and s_gmt_offset = -6 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;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query43.tpl and seed 1819994127
+explain
+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 = -6 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;
+
+-- end query 1 in stream 0 using template query43.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query44.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query44.q b/ql/src/test/queries/clientpositive/perf/query44.q
new file mode 100644
index 0000000..712bbfb
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query44.q
@@ -0,0 +1,37 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query44.tpl and seed 1819994127
+explain
+select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing
+from(select *
+ from (select item_sk,rank() over (order by rank_col asc) rnk
+ from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
+ from store_sales ss1
+ where ss_store_sk = 410
+ group by ss_item_sk
+ having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col
+ from store_sales
+ where ss_store_sk = 410
+ and ss_hdemo_sk is null
+ group by ss_store_sk))V1)V11
+ where rnk < 11) asceding,
+ (select *
+ from (select item_sk,rank() over (order by rank_col desc) rnk
+ from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
+ from store_sales ss1
+ where ss_store_sk = 410
+ group by ss_item_sk
+ having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col
+ from store_sales
+ where ss_store_sk = 410
+ and ss_hdemo_sk is null
+ group by ss_store_sk))V2)V21
+ where rnk < 11) descending,
+item i1,
+item i2
+where asceding.rnk = descending.rnk
+ and i1.i_item_sk=asceding.item_sk
+ and i2.i_item_sk=descending.item_sk
+order by asceding.rnk
+limit 100;
+
+-- end query 1 in stream 0 using template query44.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query45.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query45.q b/ql/src/test/queries/clientpositive/perf/query45.q
new file mode 100644
index 0000000..4db3fb2
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query45.q
@@ -0,0 +1,22 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query45.tpl and seed 2031708268
+explain
+select ca_zip, ca_county, sum(ws_sales_price)
+ from web_sales, customer, customer_address, date_dim, item
+ where ws_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ws_item_sk = i_item_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792')
+ or
+ i_item_id in (select i_item_id
+ from item
+ where 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;
+
+-- end query 1 in stream 0 using template query45.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query46.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query46.q b/ql/src/test/queries/clientpositive/perf/query46.q
index 3e8711f..46f8be3 100644
--- a/ql/src/test/queries/clientpositive/perf/query46.q
+++ b/ql/src/test/queries/clientpositive/perf/query46.q
@@ -1 +1,37 @@
-explain 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 = 4 or household_demographics.hd_vehicle_count= 2) and date_dim.d_dow in (6,0) and date_dim.d_year in (1998,1998+1,1998+2) and store.s_city in ('Rosedale','Bethlehem','Clinton','Clifton','Springfield') group by ss_ticket_number,ss_customer_sk,ss_addr_sk,ca_city) dn,customer,customer_address current_addr where dn.ss_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = current_addr.ca_address_sk and current_addr.ca_city <> bough
t_city order by c_last_name ,c_first_name ,ca_city ,bought_city ,ss_ticket_number limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query46.tpl and seed 803547492
+explain
+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 = 2 or
+ household_demographics.hd_vehicle_count= 1)
+ and date_dim.d_dow in (6,0)
+ and date_dim.d_year in (1998,1998+1,1998+2)
+ and store.s_city in ('Cedar Grove','Wildwood','Union','Salem','Highland Park')
+ 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;
+
+-- end query 1 in stream 0 using template query46.tpl