hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From pxi...@apache.org
Subject [15/15] hive git commit: HIVE-16764: Support numeric as same as decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
Date Wed, 31 May 2017 00:01:22 GMT
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/master
Commit: 86b18772d277488b04146c9618a1da544389ae47
Parents: 824b9c8
Author: Pengcheng Xiong <pxiong@hortonworks.com>
Authored: Tue May 30 17:00:59 2017 -0700
Committer: Pengcheng Xiong <pxiong@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


Mime
View raw message