hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From w...@apache.org
Subject [13/17] hive git commit: HIVE-16764: Support numeric as same as decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
Date Wed, 31 May 2017 00:11:51 GMT
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query13.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query13.q.out b/ql/src/test/results/clientpositive/perf/query13.q.out
index d67abe2..ac9d3cb 100644
--- a/ql/src/test/results/clientpositive/perf/query13.q.out
+++ b/ql/src/test/results/clientpositive/perf/query13.q.out
@@ -1,110 +1,102 @@
-PREHOOK: query: 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 ))
+PREHOOK: query: 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  
+     ))
 PREHOOK: type: QUERY
-POSTHOOK: query: 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 ))
+POSTHOOK: query: 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  
+     ))
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query15.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query15.q.out b/ql/src/test/results/clientpositive/perf/query15.q.out
index adec728..a2d95f6 100644
--- a/ql/src/test/results/clientpositive/perf/query15.q.out
+++ b/ql/src/test/results/clientpositive/perf/query15.q.out
@@ -1,6 +1,40 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query16.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query16.q.out b/ql/src/test/results/clientpositive/perf/query16.q.out
index cf90c0c..449b9c8 100644
--- a/ql/src/test/results/clientpositive/perf/query16.q.out
+++ b/ql/src/test/results/clientpositive/perf/query16.q.out
@@ -1,5 +1,6 @@
-Warning: Shuffle Join MERGEJOIN[106][tables = [$hdt$_2, $hdt$_3, $hdt$_1, $hdt$_4]] in Stage 'Reducer 17' is a cross product
-PREHOOK: query: explain select  
+Warning: Shuffle Join MERGEJOIN[107][tables = [$hdt$_2, $hdt$_3, $hdt$_1, $hdt$_4]] in Stage 'Reducer 17' is a cross product
+PREHOOK: query: 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`
@@ -25,10 +26,11 @@ 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
 PREHOOK: type: QUERY
-POSTHOOK: query: explain select  
+POSTHOOK: query: 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`
@@ -54,7 +56,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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
@@ -75,157 +77,159 @@ Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
-    limit:100
+    limit:-1
     Stage-1
       Reducer 8
-      File Output Operator [FS_73]
+      File Output Operator [FS_74]
         Limit [LIM_72] (rows=1 width=344)
           Number of rows:100
           Select Operator [SEL_71] (rows=1 width=344)
             Output:["_col0","_col1","_col2"]
           <-Reducer 7 [SIMPLE_EDGE]
             SHUFFLE [RS_70]
-              Group By Operator [GBY_68] (rows=1 width=344)
-                Output:["_col0","_col1","_col2"],aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
-              <-Reducer 6 [SIMPLE_EDGE]
-                SHUFFLE [RS_67]
-                  Group By Operator [GBY_66] (rows=1395035081047425024 width=1)
-                    Output:["_col0","_col1","_col2","_col3"],aggregations:["count(DISTINCT _col4)","sum(_col5)","sum(_col6)"],keys:_col4
-                    Select Operator [SEL_65] (rows=1395035081047425024 width=1)
-                      Output:["_col4","_col5","_col6"]
-                      Filter Operator [FIL_64] (rows=1395035081047425024 width=1)
-                        predicate:_col16 is null
-                        Select Operator [SEL_63] (rows=2790070162094850048 width=1)
-                          Output:["_col4","_col5","_col6","_col16"]
-                          Merge Join Operator [MERGEJOIN_112] (rows=2790070162094850048 width=1)
-                            Conds:RS_60._col3, _col4=RS_61._col0, _col1(Inner),Output:["_col4","_col5","_col6","_col14"]
-                          <-Reducer 16 [SIMPLE_EDGE]
-                            SHUFFLE [RS_61]
-                              PartitionCols:_col0, _col1
-                              Group By Operator [GBY_46] (rows=2536427365110644736 width=1)
-                                Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
-                              <-Reducer 15 [SIMPLE_EDGE]
-                                SHUFFLE [RS_45]
-                                  PartitionCols:_col0, _col1
-                                  Group By Operator [GBY_44] (rows=5072854730221289472 width=1)
-                                    Output:["_col0","_col1"],keys:_col2, _col3
-                                    Select Operator [SEL_43] (rows=5072854730221289472 width=1)
-                                      Output:["_col2","_col3"]
-                                      Filter Operator [FIL_42] (rows=5072854730221289472 width=1)
-                                        predicate:(_col2 <> _col0)
-                                        Merge Join Operator [MERGEJOIN_110] (rows=5072854730221289472 width=1)
-                                          Conds:RS_39._col1=RS_40._col1(Inner),Output:["_col0","_col2","_col3"]
-                                        <-Map 14 [SIMPLE_EDGE]
-                                          PARTITION_ONLY_SHUFFLE [RS_39]
-                                            PartitionCols:_col1
-                                            Select Operator [SEL_20] (rows=287989836 width=135)
-                                              Output:["_col0","_col1"]
-                                              TableScan [TS_19] (rows=287989836 width=135)
-                                                default@catalog_sales,cs2,Tbl:COMPLETE,Col:NONE,Output:["cs_warehouse_sk","cs_order_number"]
-                                        <-Reducer 18 [SIMPLE_EDGE]
-                                          SHUFFLE [RS_40]
-                                            PartitionCols:_col1
-                                            Select Operator [SEL_38] (rows=4611686018427387903 width=1)
-                                              Output:["_col0","_col1"]
-                                              Group By Operator [GBY_37] (rows=4611686018427387903 width=1)
-                                                Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
-                                              <-Reducer 17 [SIMPLE_EDGE]
-                                                SHUFFLE [RS_36]
-                                                  PartitionCols:_col0, _col1
-                                                  Group By Operator [GBY_35] (rows=9223372036854775807 width=1)
-                                                    Output:["_col0","_col1"],keys:_col4, _col3
-                                                    Merge Join Operator [MERGEJOIN_106] (rows=9223372036854775807 width=1)
-                                                      Conds:(Inner),(Inner),(Inner),Output:["_col3","_col4"]
-                                                    <-Map 14 [CUSTOM_SIMPLE_EDGE]
-                                                      PARTITION_ONLY_SHUFFLE [RS_32]
-                                                        Select Operator [SEL_28] (rows=287989836 width=135)
-                                                          Output:["_col0","_col1"]
-                                                           Please refer to the previous TableScan [TS_19]
-                                                    <-Map 19 [CUSTOM_SIMPLE_EDGE]
-                                                      PARTITION_ONLY_SHUFFLE [RS_29]
-                                                        Select Operator [SEL_22] (rows=73049 width=4)
-                                                          TableScan [TS_21] (rows=73049 width=1119)
-                                                            default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE
-                                                    <-Map 20 [CUSTOM_SIMPLE_EDGE]
-                                                      PARTITION_ONLY_SHUFFLE [RS_30]
-                                                        Select Operator [SEL_24] (rows=60 width=4)
-                                                          TableScan [TS_23] (rows=60 width=2045)
-                                                            default@call_center,call_center,Tbl:COMPLETE,Col:COMPLETE
-                                                    <-Map 21 [CUSTOM_SIMPLE_EDGE]
-                                                      PARTITION_ONLY_SHUFFLE [RS_31]
-                                                        Select Operator [SEL_26] (rows=40000000 width=4)
-                                                          TableScan [TS_25] (rows=40000000 width=1014)
-                                                            default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE
-                          <-Reducer 5 [SIMPLE_EDGE]
-                            SHUFFLE [RS_60]
-                              PartitionCols:_col3, _col4
-                              Merge Join Operator [MERGEJOIN_111] (rows=421645953 width=135)
-                                Conds:RS_57._col4=RS_58._col0(Left Outer),Output:["_col3","_col4","_col5","_col6","_col14"]
-                              <-Reducer 13 [SIMPLE_EDGE]
-                                SHUFFLE [RS_58]
-                                  PartitionCols:_col0
-                                  Select Operator [SEL_18] (rows=14399440 width=106)
-                                    Output:["_col0","_col1"]
-                                    Group By Operator [GBY_17] (rows=14399440 width=106)
-                                      Output:["_col0"],keys:KEY._col0
-                                    <-Map 12 [SIMPLE_EDGE]
-                                      SHUFFLE [RS_16]
-                                        PartitionCols:_col0
-                                        Group By Operator [GBY_15] (rows=28798881 width=106)
-                                          Output:["_col0"],keys:cr_order_number
-                                          Filter Operator [FIL_103] (rows=28798881 width=106)
-                                            predicate:cr_order_number is not null
-                                            TableScan [TS_12] (rows=28798881 width=106)
-                                              default@catalog_returns,cr1,Tbl:COMPLETE,Col:NONE,Output:["cr_order_number"]
-                              <-Reducer 4 [SIMPLE_EDGE]
-                                SHUFFLE [RS_57]
-                                  PartitionCols:_col4
-                                  Merge Join Operator [MERGEJOIN_109] (rows=383314495 width=135)
-                                    Conds:RS_54._col2=RS_55._col0(Inner),Output:["_col3","_col4","_col5","_col6"]
-                                  <-Map 11 [SIMPLE_EDGE]
-                                    SHUFFLE [RS_55]
-                                      PartitionCols:_col0
-                                      Select Operator [SEL_11] (rows=30 width=2045)
-                                        Output:["_col0"]
-                                        Filter Operator [FIL_102] (rows=30 width=2045)
-                                          predicate:((cc_county) IN ('Ziebach County', 'Levy County', 'Huron County', 'Franklin Parish', 'Daviess County') and cc_call_center_sk is not null)
-                                          TableScan [TS_9] (rows=60 width=2045)
-                                            default@call_center,call_center,Tbl:COMPLETE,Col:NONE,Output:["cc_call_center_sk","cc_county"]
-                                  <-Reducer 3 [SIMPLE_EDGE]
-                                    SHUFFLE [RS_54]
-                                      PartitionCols:_col2
-                                      Merge Join Operator [MERGEJOIN_108] (rows=348467716 width=135)
-                                        Conds:RS_51._col1=RS_52._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
-                                      <-Map 10 [SIMPLE_EDGE]
-                                        SHUFFLE [RS_52]
+              Select Operator [SEL_69] (rows=1 width=344)
+                Output:["_col1","_col2","_col3"]
+                Group By Operator [GBY_68] (rows=1 width=344)
+                  Output:["_col0","_col1","_col2"],aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
+                <-Reducer 6 [SIMPLE_EDGE]
+                  SHUFFLE [RS_67]
+                    Group By Operator [GBY_66] (rows=1395035081047425024 width=1)
+                      Output:["_col0","_col1","_col2","_col3"],aggregations:["count(DISTINCT _col4)","sum(_col5)","sum(_col6)"],keys:_col4
+                      Select Operator [SEL_65] (rows=1395035081047425024 width=1)
+                        Output:["_col4","_col5","_col6"]
+                        Filter Operator [FIL_64] (rows=1395035081047425024 width=1)
+                          predicate:_col16 is null
+                          Select Operator [SEL_63] (rows=2790070162094850048 width=1)
+                            Output:["_col4","_col5","_col6","_col16"]
+                            Merge Join Operator [MERGEJOIN_113] (rows=2790070162094850048 width=1)
+                              Conds:RS_60._col3, _col4=RS_61._col0, _col1(Inner),Output:["_col4","_col5","_col6","_col14"]
+                            <-Reducer 16 [SIMPLE_EDGE]
+                              SHUFFLE [RS_61]
+                                PartitionCols:_col0, _col1
+                                Group By Operator [GBY_46] (rows=2536427365110644736 width=1)
+                                  Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                                <-Reducer 15 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_45]
+                                    PartitionCols:_col0, _col1
+                                    Group By Operator [GBY_44] (rows=5072854730221289472 width=1)
+                                      Output:["_col0","_col1"],keys:_col2, _col3
+                                      Select Operator [SEL_43] (rows=5072854730221289472 width=1)
+                                        Output:["_col2","_col3"]
+                                        Filter Operator [FIL_42] (rows=5072854730221289472 width=1)
+                                          predicate:(_col2 <> _col0)
+                                          Merge Join Operator [MERGEJOIN_111] (rows=5072854730221289472 width=1)
+                                            Conds:RS_39._col1=RS_40._col1(Inner),Output:["_col0","_col2","_col3"]
+                                          <-Map 14 [SIMPLE_EDGE]
+                                            PARTITION_ONLY_SHUFFLE [RS_39]
+                                              PartitionCols:_col1
+                                              Select Operator [SEL_20] (rows=287989836 width=135)
+                                                Output:["_col0","_col1"]
+                                                TableScan [TS_19] (rows=287989836 width=135)
+                                                  default@catalog_sales,cs2,Tbl:COMPLETE,Col:NONE,Output:["cs_warehouse_sk","cs_order_number"]
+                                          <-Reducer 18 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_40]
+                                              PartitionCols:_col1
+                                              Select Operator [SEL_38] (rows=4611686018427387903 width=1)
+                                                Output:["_col0","_col1"]
+                                                Group By Operator [GBY_37] (rows=4611686018427387903 width=1)
+                                                  Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                                                <-Reducer 17 [SIMPLE_EDGE]
+                                                  SHUFFLE [RS_36]
+                                                    PartitionCols:_col0, _col1
+                                                    Group By Operator [GBY_35] (rows=9223372036854775807 width=1)
+                                                      Output:["_col0","_col1"],keys:_col4, _col3
+                                                      Merge Join Operator [MERGEJOIN_107] (rows=9223372036854775807 width=1)
+                                                        Conds:(Inner),(Inner),(Inner),Output:["_col3","_col4"]
+                                                      <-Map 14 [CUSTOM_SIMPLE_EDGE]
+                                                        PARTITION_ONLY_SHUFFLE [RS_32]
+                                                          Select Operator [SEL_28] (rows=287989836 width=135)
+                                                            Output:["_col0","_col1"]
+                                                             Please refer to the previous TableScan [TS_19]
+                                                      <-Map 19 [CUSTOM_SIMPLE_EDGE]
+                                                        PARTITION_ONLY_SHUFFLE [RS_29]
+                                                          Select Operator [SEL_22] (rows=73049 width=4)
+                                                            TableScan [TS_21] (rows=73049 width=1119)
+                                                              default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE
+                                                      <-Map 20 [CUSTOM_SIMPLE_EDGE]
+                                                        PARTITION_ONLY_SHUFFLE [RS_30]
+                                                          Select Operator [SEL_24] (rows=60 width=4)
+                                                            TableScan [TS_23] (rows=60 width=2045)
+                                                              default@call_center,call_center,Tbl:COMPLETE,Col:COMPLETE
+                                                      <-Map 21 [CUSTOM_SIMPLE_EDGE]
+                                                        PARTITION_ONLY_SHUFFLE [RS_31]
+                                                          Select Operator [SEL_26] (rows=40000000 width=4)
+                                                            TableScan [TS_25] (rows=40000000 width=1014)
+                                                              default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE
+                            <-Reducer 5 [SIMPLE_EDGE]
+                              SHUFFLE [RS_60]
+                                PartitionCols:_col3, _col4
+                                Merge Join Operator [MERGEJOIN_112] (rows=421645953 width=135)
+                                  Conds:RS_57._col4=RS_58._col0(Left Outer),Output:["_col3","_col4","_col5","_col6","_col14"]
+                                <-Reducer 13 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_58]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_18] (rows=14399440 width=106)
+                                      Output:["_col0","_col1"]
+                                      Group By Operator [GBY_17] (rows=14399440 width=106)
+                                        Output:["_col0"],keys:KEY._col0
+                                      <-Map 12 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_16]
                                           PartitionCols:_col0
-                                          Select Operator [SEL_8] (rows=20000000 width=1014)
-                                            Output:["_col0"]
-                                            Filter Operator [FIL_101] (rows=20000000 width=1014)
-                                              predicate:((ca_state = 'NY') and ca_address_sk is not null)
-                                              TableScan [TS_6] (rows=40000000 width=1014)
-                                                default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"]
-                                      <-Reducer 2 [SIMPLE_EDGE]
-                                        SHUFFLE [RS_51]
-                                          PartitionCols:_col1
-                                          Merge Join Operator [MERGEJOIN_107] (rows=316788826 width=135)
-                                            Conds:RS_48._col0=RS_49._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6"]
-                                          <-Map 1 [SIMPLE_EDGE]
-                                            SHUFFLE [RS_48]
-                                              PartitionCols:_col0
-                                              Select Operator [SEL_2] (rows=287989836 width=135)
-                                                Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
-                                                Filter Operator [FIL_99] (rows=287989836 width=135)
-                                                  predicate:(cs_ship_date_sk is not null and cs_ship_addr_sk is not null and cs_call_center_sk is not null)
-                                                  TableScan [TS_0] (rows=287989836 width=135)
-                                                    default@catalog_sales,cs1,Tbl:COMPLETE,Col:NONE,Output:["cs_ship_date_sk","cs_ship_addr_sk","cs_call_center_sk","cs_warehouse_sk","cs_order_number","cs_ext_ship_cost","cs_net_profit"]
-                                          <-Map 9 [SIMPLE_EDGE]
-                                            SHUFFLE [RS_49]
-                                              PartitionCols:_col0
-                                              Select Operator [SEL_5] (rows=8116 width=1119)
-                                                Output:["_col0"]
-                                                Filter Operator [FIL_100] (rows=8116 width=1119)
-                                                  predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-04-01 00:00:00.0 AND 2001-05-31 01:00:00.0 and d_date_sk is not null)
-                                                  TableScan [TS_3] (rows=73049 width=1119)
-                                                    default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+                                          Group By Operator [GBY_15] (rows=28798881 width=106)
+                                            Output:["_col0"],keys:cr_order_number
+                                            Filter Operator [FIL_104] (rows=28798881 width=106)
+                                              predicate:cr_order_number is not null
+                                              TableScan [TS_12] (rows=28798881 width=106)
+                                                default@catalog_returns,cr1,Tbl:COMPLETE,Col:NONE,Output:["cr_order_number"]
+                                <-Reducer 4 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_57]
+                                    PartitionCols:_col4
+                                    Merge Join Operator [MERGEJOIN_110] (rows=383314495 width=135)
+                                      Conds:RS_54._col2=RS_55._col0(Inner),Output:["_col3","_col4","_col5","_col6"]
+                                    <-Map 11 [SIMPLE_EDGE]
+                                      SHUFFLE [RS_55]
+                                        PartitionCols:_col0
+                                        Select Operator [SEL_11] (rows=30 width=2045)
+                                          Output:["_col0"]
+                                          Filter Operator [FIL_103] (rows=30 width=2045)
+                                            predicate:((cc_county) IN ('Ziebach County', 'Levy County', 'Huron County', 'Franklin Parish', 'Daviess County') and cc_call_center_sk is not null)
+                                            TableScan [TS_9] (rows=60 width=2045)
+                                              default@call_center,call_center,Tbl:COMPLETE,Col:NONE,Output:["cc_call_center_sk","cc_county"]
+                                    <-Reducer 3 [SIMPLE_EDGE]
+                                      SHUFFLE [RS_54]
+                                        PartitionCols:_col2
+                                        Merge Join Operator [MERGEJOIN_109] (rows=348467716 width=135)
+                                          Conds:RS_51._col1=RS_52._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
+                                        <-Map 10 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_52]
+                                            PartitionCols:_col0
+                                            Select Operator [SEL_8] (rows=20000000 width=1014)
+                                              Output:["_col0"]
+                                              Filter Operator [FIL_102] (rows=20000000 width=1014)
+                                                predicate:((ca_state = 'NY') and ca_address_sk is not null)
+                                                TableScan [TS_6] (rows=40000000 width=1014)
+                                                  default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"]
+                                        <-Reducer 2 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_51]
+                                            PartitionCols:_col1
+                                            Merge Join Operator [MERGEJOIN_108] (rows=316788826 width=135)
+                                              Conds:RS_48._col0=RS_49._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6"]
+                                            <-Map 1 [SIMPLE_EDGE]
+                                              SHUFFLE [RS_48]
+                                                PartitionCols:_col0
+                                                Select Operator [SEL_2] (rows=287989836 width=135)
+                                                  Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                  Filter Operator [FIL_100] (rows=287989836 width=135)
+                                                    predicate:(cs_ship_date_sk is not null and cs_ship_addr_sk is not null and cs_call_center_sk is not null)
+                                                    TableScan [TS_0] (rows=287989836 width=135)
+                                                      default@catalog_sales,cs1,Tbl:COMPLETE,Col:NONE,Output:["cs_ship_date_sk","cs_ship_addr_sk","cs_call_center_sk","cs_warehouse_sk","cs_order_number","cs_ext_ship_cost","cs_net_profit"]
+                                            <-Map 9 [SIMPLE_EDGE]
+                                              SHUFFLE [RS_49]
+                                                PartitionCols:_col0
+                                                Select Operator [SEL_5] (rows=8116 width=1119)
+                                                  Output:["_col0"]
+                                                  Filter Operator [FIL_101] (rows=8116 width=1119)
+                                                    predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-04-01 00:00:00.0 AND 2001-05-31 01:00:00.0 and d_date_sk is not null)
+                                                    TableScan [TS_3] (rows=73049 width=1119)
+                                                      default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query17.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query17.q.out b/ql/src/test/results/clientpositive/perf/query17.q.out
index 2f32a9f..5b1f2a8 100644
--- a/ql/src/test/results/clientpositive/perf/query17.q.out
+++ b/ql/src/test/results/clientpositive/perf/query17.q.out
@@ -1,6 +1,90 @@
-PREHOOK: query: 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_s
 ales.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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query18.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query18.q.out b/ql/src/test/results/clientpositive/perf/query18.q.out
index da35ca0..2ac6d4b 100644
--- a/ql/src/test/results/clientpositive/perf/query18.q.out
+++ b/ql/src/test/results/clientpositive/perf/query18.q.out
@@ -1,6 +1,68 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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_stat
 e 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
@@ -89,7 +151,7 @@ Stage-0
                                           Select Operator [SEL_11] (rows=287989836 width=135)
                                             Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
                                             Filter Operator [FIL_74] (rows=287989836 width=135)
-                                              predicate:(cs_sold_date_sk is not null and cs_bill_cdemo_sk is not null and cs_item_sk is not null and cs_bill_customer_sk is not null)
+                                              predicate:(cs_bill_cdemo_sk is not null and cs_bill_customer_sk is not null and cs_sold_date_sk is not null and cs_item_sk is not null)
                                               TableScan [TS_9] (rows=287989836 width=135)
                                                 default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_bill_cdemo_sk","cs_item_sk","cs_quantity","cs_list_price","cs_sales_price","cs_coupon_amt","cs_net_profit"]
                         <-Reducer 3 [SIMPLE_EDGE]
@@ -117,7 +179,7 @@ Stage-0
                                     Select Operator [SEL_2] (rows=40000000 width=860)
                                       Output:["_col0","_col1","_col2","_col4"]
                                       Filter Operator [FIL_71] (rows=40000000 width=860)
-                                        predicate:((c_birth_month) IN (9, 5, 12, 4, 1, 10) and c_customer_sk is not null and c_current_addr_sk is not null and c_current_cdemo_sk is not null)
+                                        predicate:((c_birth_month) IN (9, 5, 12, 4, 1, 10) and c_customer_sk is not null and c_current_cdemo_sk is not null and c_current_addr_sk is not null)
                                         TableScan [TS_0] (rows=80000000 width=860)
                                           default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_current_cdemo_sk","c_current_addr_sk","c_birth_month","c_birth_year"]
                                 <-Map 7 [SIMPLE_EDGE]

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query19.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query19.q.out b/ql/src/test/results/clientpositive/perf/query19.q.out
index cebfc3a..fccc0c3 100644
--- a/ql/src/test/results/clientpositive/perf/query19.q.out
+++ b/ql/src/test/results/clientpositive/perf/query19.q.out
@@ -1,6 +1,50 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query2.q.out b/ql/src/test/results/clientpositive/perf/query2.q.out
new file mode 100644
index 0000000..50d7f7b
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query2.q.out
@@ -0,0 +1,259 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Union 2 (CONTAINS)
+Map 14 <- Union 15 (CONTAINS)
+Map 16 <- Union 15 (CONTAINS)
+Map 8 <- Union 2 (CONTAINS)
+Reducer 10 <- Map 9 (SIMPLE_EDGE), Union 15 (SIMPLE_EDGE)
+Reducer 11 <- Reducer 10 (SIMPLE_EDGE)
+Reducer 12 <- Map 13 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE)
+Reducer 3 <- Map 9 (SIMPLE_EDGE), Union 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 12 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+
+Stage-0
+  Fetch Operator
+    limit:-1
+    Stage-1
+      Reducer 7
+      File Output Operator [FS_60]
+        Select Operator [SEL_59] (rows=287491028 width=135)
+          Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+        <-Reducer 6 [SIMPLE_EDGE]
+          SHUFFLE [RS_58]
+            Select Operator [SEL_57] (rows=287491028 width=135)
+              Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+              Merge Join Operator [MERGEJOIN_95] (rows=287491028 width=135)
+                Conds:RS_54._col0=RS_55.(_col0 - 53)(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col9","_col10","_col11","_col12","_col13","_col14","_col15"]
+              <-Reducer 12 [SIMPLE_EDGE]
+                SHUFFLE [RS_55]
+                  PartitionCols:(_col0 - 53)
+                  Merge Join Operator [MERGEJOIN_94] (rows=261355475 width=135)
+                    Conds:RS_50._col0=RS_51._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                  <-Map 13 [SIMPLE_EDGE]
+                    SHUFFLE [RS_51]
+                      PartitionCols:_col0
+                      Select Operator [SEL_49] (rows=36524 width=1119)
+                        Output:["_col0"]
+                        Filter Operator [FIL_90] (rows=36524 width=1119)
+                          predicate:((d_year = 2002) and d_week_seq is not null)
+                          TableScan [TS_20] (rows=73049 width=1119)
+                            default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_week_seq","d_year"]
+                  <-Reducer 11 [SIMPLE_EDGE]
+                    SHUFFLE [RS_50]
+                      PartitionCols:_col0
+                      Group By Operator [GBY_45] (rows=237595882 width=135)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)"],keys:KEY._col0
+                      <-Reducer 10 [SIMPLE_EDGE]
+                        SHUFFLE [RS_44]
+                          PartitionCols:_col0
+                          Group By Operator [GBY_43] (rows=475191764 width=135)
+                            Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)"],keys:_col0
+                            Select Operator [SEL_41] (rows=475191764 width=135)
+                              Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                              Merge Join Operator [MERGEJOIN_93] (rows=475191764 width=135)
+                                Conds:Union 15._col0=RS_39._col0(Inner),Output:["_col1","_col3","_col4"]
+                              <-Map 9 [SIMPLE_EDGE]
+                                SHUFFLE [RS_39]
+                                  PartitionCols:_col0
+                                  Select Operator [SEL_37] (rows=73049 width=1119)
+                                    Output:["_col0","_col1","_col2"]
+                                    Filter Operator [FIL_89] (rows=73049 width=1119)
+                                      predicate:(d_date_sk is not null and d_week_seq is not null)
+                                      TableScan [TS_8] (rows=73049 width=1119)
+                                        default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_week_seq","d_day_name"]
+                              <-Union 15 [SIMPLE_EDGE]
+                                <-Map 14 [CONTAINS]
+                                  Reduce Output Operator [RS_38]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_29] (rows=144002668 width=135)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_87] (rows=144002668 width=135)
+                                        predicate:ws_sold_date_sk is not null
+                                        TableScan [TS_27] (rows=144002668 width=135)
+                                          Output:["ws_sold_date_sk","ws_ext_sales_price"]
+                                <-Map 16 [CONTAINS]
+                                  Reduce Output Operator [RS_38]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_32] (rows=287989836 width=135)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_88] (rows=287989836 width=135)
+                                        predicate:cs_sold_date_sk is not null
+                                        TableScan [TS_30] (rows=287989836 width=135)
+                                          Output:["cs_sold_date_sk","cs_ext_sales_price"]
+              <-Reducer 5 [SIMPLE_EDGE]
+                SHUFFLE [RS_54]
+                  PartitionCols:_col0
+                  Merge Join Operator [MERGEJOIN_92] (rows=261355475 width=135)
+                    Conds:RS_23._col0=RS_24._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                  <-Map 13 [SIMPLE_EDGE]
+                    SHUFFLE [RS_24]
+                      PartitionCols:_col0
+                      Select Operator [SEL_22] (rows=36524 width=1119)
+                        Output:["_col0"]
+                        Filter Operator [FIL_86] (rows=36524 width=1119)
+                          predicate:((d_year = 2001) and d_week_seq is not null)
+                           Please refer to the previous TableScan [TS_20]
+                  <-Reducer 4 [SIMPLE_EDGE]
+                    SHUFFLE [RS_23]
+                      PartitionCols:_col0
+                      Group By Operator [GBY_18] (rows=237595882 width=135)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)"],keys:KEY._col0
+                      <-Reducer 3 [SIMPLE_EDGE]
+                        SHUFFLE [RS_17]
+                          PartitionCols:_col0
+                          Group By Operator [GBY_16] (rows=475191764 width=135)
+                            Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)"],keys:_col0
+                            Select Operator [SEL_14] (rows=475191764 width=135)
+                              Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                              Merge Join Operator [MERGEJOIN_91] (rows=475191764 width=135)
+                                Conds:Union 2._col0=RS_12._col0(Inner),Output:["_col1","_col3","_col4"]
+                              <-Map 9 [SIMPLE_EDGE]
+                                SHUFFLE [RS_12]
+                                  PartitionCols:_col0
+                                  Select Operator [SEL_10] (rows=73049 width=1119)
+                                    Output:["_col0","_col1","_col2"]
+                                    Filter Operator [FIL_85] (rows=73049 width=1119)
+                                      predicate:(d_date_sk is not null and d_week_seq is not null)
+                                       Please refer to the previous TableScan [TS_8]
+                              <-Union 2 [SIMPLE_EDGE]
+                                <-Map 1 [CONTAINS]
+                                  Reduce Output Operator [RS_11]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_2] (rows=144002668 width=135)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_83] (rows=144002668 width=135)
+                                        predicate:ws_sold_date_sk is not null
+                                        TableScan [TS_0] (rows=144002668 width=135)
+                                          Output:["ws_sold_date_sk","ws_ext_sales_price"]
+                                <-Map 8 [CONTAINS]
+                                  Reduce Output Operator [RS_11]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_5] (rows=287989836 width=135)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_84] (rows=287989836 width=135)
+                                        predicate:cs_sold_date_sk is not null
+                                        TableScan [TS_3] (rows=287989836 width=135)
+                                          Output:["cs_sold_date_sk","cs_ext_sales_price"]
+

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query20.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query20.q.out b/ql/src/test/results/clientpositive/perf/query20.q.out
index cff652f..c07141b 100644
--- a/ql/src/test/results/clientpositive/perf/query20.q.out
+++ b/ql/src/test/results/clientpositive/perf/query20.q.out
@@ -1,6 +1,58 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
@@ -13,14 +65,14 @@ Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
-    limit:100
+    limit:-1
     Stage-1
       Reducer 6
-      File Output Operator [FS_28]
+      File Output Operator [FS_29]
         Limit [LIM_27] (rows=100 width=135)
           Number of rows:100
           Select Operator [SEL_26] (rows=174233858 width=135)
-            Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+            Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
           <-Reducer 5 [SIMPLE_EDGE]
             SHUFFLE [RS_25]
               Select Operator [SEL_23] (rows=174233858 width=135)
@@ -41,28 +93,28 @@ Stage-0
                             PartitionCols:_col0, _col1, _col2, _col3, _col4
                             Group By Operator [GBY_16] (rows=348467716 width=135)
                               Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)"],keys:_col10, _col9, _col6, _col7, _col8
-                              Merge Join Operator [MERGEJOIN_38] (rows=348467716 width=135)
+                              Merge Join Operator [MERGEJOIN_39] (rows=348467716 width=135)
                                 Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col6","_col7","_col8","_col9","_col10"]
                               <-Map 8 [SIMPLE_EDGE]
                                 SHUFFLE [RS_13]
                                   PartitionCols:_col0
                                   Select Operator [SEL_8] (rows=231000 width=1436)
                                     Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
-                                    Filter Operator [FIL_36] (rows=231000 width=1436)
+                                    Filter Operator [FIL_37] (rows=231000 width=1436)
                                       predicate:((i_category) IN ('Jewelry', 'Sports', 'Books') and i_item_sk is not null)
                                       TableScan [TS_6] (rows=462000 width=1436)
                                         default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id","i_item_desc","i_current_price","i_class","i_category"]
                               <-Reducer 2 [SIMPLE_EDGE]
                                 SHUFFLE [RS_12]
                                   PartitionCols:_col1
-                                  Merge Join Operator [MERGEJOIN_37] (rows=316788826 width=135)
+                                  Merge Join Operator [MERGEJOIN_38] (rows=316788826 width=135)
                                     Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2"]
                                   <-Map 1 [SIMPLE_EDGE]
                                     SHUFFLE [RS_9]
                                       PartitionCols:_col0
                                       Select Operator [SEL_2] (rows=287989836 width=135)
                                         Output:["_col0","_col1","_col2"]
-                                        Filter Operator [FIL_34] (rows=287989836 width=135)
+                                        Filter Operator [FIL_35] (rows=287989836 width=135)
                                           predicate:(cs_item_sk is not null and cs_sold_date_sk is not null)
                                           TableScan [TS_0] (rows=287989836 width=135)
                                             default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_item_sk","cs_ext_sales_price"]
@@ -71,8 +123,8 @@ Stage-0
                                       PartitionCols:_col0
                                       Select Operator [SEL_5] (rows=8116 width=1119)
                                         Output:["_col0"]
-                                        Filter Operator [FIL_35] (rows=8116 width=1119)
-                                          predicate:(d_date BETWEEN '2001-01-12' AND '2001-02-11' and d_date_sk is not null)
+                                        Filter Operator [FIL_36] (rows=8116 width=1119)
+                                          predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-01-12 00:00:00.0 AND 2001-02-11 00:00:00.0 and d_date_sk is not null)
                                           TableScan [TS_3] (rows=73049 width=1119)
                                             default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
 

http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query23.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query23.q.out b/ql/src/test/results/clientpositive/perf/query23.q.out
index 5794301..b8cdad3 100644
--- a/ql/src/test/results/clientpositive/perf/query23.q.out
+++ b/ql/src/test/results/clientpositive/perf/query23.q.out
@@ -1,6 +1,7 @@
-Warning: Shuffle Join MERGEJOIN[369][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 30' is a cross product
 Warning: Shuffle Join MERGEJOIN[367][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 25' is a cross product
-PREHOOK: query: explain with frequent_ss_items as 
+Warning: Shuffle Join MERGEJOIN[369][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 30' is a cross product
+PREHOOK: query: 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 
@@ -50,7 +51,8 @@ from
          and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer))) y
  limit 100
 PREHOOK: type: QUERY
-POSTHOOK: query: explain with frequent_ss_items as 
+POSTHOOK: query: 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/results/clientpositive/perf/query24.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query24.q.out b/ql/src/test/results/clientpositive/perf/query24.q.out
index 105871f..2aa0c19 100644
--- a/ql/src/test/results/clientpositive/perf/query24.q.out
+++ b/ql/src/test/results/clientpositive/perf/query24.q.out
@@ -1,99 +1,101 @@
 Warning: Shuffle Join MERGEJOIN[154][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 8' is a cross product
-PREHOOK: query: 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)
+PREHOOK: query: 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)
 PREHOOK: type: QUERY
-POSTHOOK: query: 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)
+POSTHOOK: query: 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)
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 


Mime
View raw message