You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by se...@apache.org on 2018/10/26 21:11:32 UTC
[22/75] [abbrv] [partial] hive git commit: HIVE-20718: Add perf cli
driver with constraints (Jesus Camacho Rodriguez,
reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out
new file mode 100644
index 0000000..7fbbc05
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out
@@ -0,0 +1,343 @@
+Warning: Shuffle Join MERGEJOIN[287][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: type: QUERY
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_returns
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+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
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_returns
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 10 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE)
+Map 24 <- Reducer 20 (BROADCAST_EDGE)
+Reducer 10 <- Map 9 (CUSTOM_SIMPLE_EDGE)
+Reducer 11 <- Map 9 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE)
+Reducer 12 <- Map 22 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE)
+Reducer 13 <- Map 23 (SIMPLE_EDGE), Reducer 12 (SIMPLE_EDGE)
+Reducer 14 <- Reducer 13 (SIMPLE_EDGE)
+Reducer 15 <- Reducer 14 (CUSTOM_SIMPLE_EDGE)
+Reducer 17 <- Map 16 (CUSTOM_SIMPLE_EDGE)
+Reducer 18 <- Map 16 (SIMPLE_EDGE), Map 24 (SIMPLE_EDGE)
+Reducer 19 <- Map 21 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
+Reducer 20 <- Map 16 (CUSTOM_SIMPLE_EDGE)
+Reducer 3 <- Map 16 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 21 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 22 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Map 23 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 15 (CUSTOM_SIMPLE_EDGE), Reducer 7 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 8
+ File Output Operator [FS_88]
+ Select Operator [SEL_87] (rows=78393744 width=380)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_86] (rows=78393744 width=492)
+ predicate:(_col3 > _col4)
+ Merge Join Operator [MERGEJOIN_287] (rows=235181232 width=492)
+ Conds:(Inner),Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 15 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_340]
+ Select Operator [SEL_339] (rows=1 width=112)
+ Output:["_col0"]
+ Group By Operator [GBY_338] (rows=1 width=120)
+ Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"]
+ <-Reducer 14 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_337]
+ Group By Operator [GBY_336] (rows=1 width=120)
+ Output:["_col0","_col1"],aggregations:["sum(_col10)","count(_col10)"]
+ Select Operator [SEL_335] (rows=2121289008973 width=932)
+ Output:["_col10"]
+ Group By Operator [GBY_334] (rows=2121289008973 width=932)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8, KEY._col9
+ <-Reducer 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_75]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9
+ Group By Operator [GBY_74] (rows=2121289008973 width=932)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(_col4)"],keys:_col10, _col11, _col6, _col7, _col14, _col15, _col16, _col17, _col18, _col21
+ Merge Join Operator [MERGEJOIN_286] (rows=2121289008973 width=932)
+ Conds:RS_70._col12, _col8=RS_323._col2, _col1(Inner),Output:["_col4","_col6","_col7","_col10","_col11","_col14","_col15","_col16","_col17","_col18","_col21"]
+ <-Map 23 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_323]
+ PartitionCols:_col2, _col1
+ Select Operator [SEL_321] (rows=40000000 width=359)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_320] (rows=40000000 width=272)
+ predicate:(ca_zip is not null and upper(ca_country) is not null)
+ TableScan [TS_14] (rows=40000000 width=272)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_state","ca_zip","ca_country"]
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_70]
+ PartitionCols:_col12, _col8
+ Merge Join Operator [MERGEJOIN_285] (rows=537799796 width=1023)
+ Conds:RS_67._col0, _col3=RS_319._col0, _col1(Inner),Output:["_col4","_col6","_col7","_col8","_col10","_col11","_col12","_col14","_col15","_col16","_col17","_col18"]
+ <-Map 22 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_319]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_317] (rows=57591150 width=8)
+ Output:["_col0","_col1"]
+ TableScan [TS_12] (rows=57591150 width=8)
+ default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_item_sk","sr_ticket_number"]
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_67]
+ PartitionCols:_col0, _col3
+ Merge Join Operator [MERGEJOIN_284] (rows=385681992 width=1029)
+ Conds:RS_64._col0=RS_291._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col10","_col11","_col12","_col14","_col15","_col16","_col17","_col18"]
+ <-Map 9 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_291]
+ PartitionCols:_col0
+ Select Operator [SEL_289] (rows=462000 width=384)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ TableScan [TS_3] (rows=462000 width=384)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_current_price","i_size","i_color","i_units","i_manager_id"]
+ <-Reducer 19 [SIMPLE_EDGE]
+ SHUFFLE [RS_64]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_283] (rows=385681992 width=648)
+ Conds:RS_61._col1=RS_316._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col10","_col11","_col12"]
+ <-Map 21 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_316]
+ PartitionCols:_col0
+ Select Operator [SEL_314] (rows=80000000 width=276)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_313] (rows=80000000 width=276)
+ predicate:c_birth_country is not null
+ TableScan [TS_9] (rows=80000000 width=276)
+ default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_first_name","c_last_name","c_birth_country"]
+ <-Reducer 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_61]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_282] (rows=385681992 width=379)
+ Conds:RS_333._col2=RS_302._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col6","_col7","_col8"]
+ <-Map 16 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_302]
+ PartitionCols:_col0
+ Select Operator [SEL_299] (rows=155 width=267)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_298] (rows=155 width=271)
+ predicate:((s_market_id = 7) and s_zip is not null)
+ TableScan [TS_6] (rows=1704 width=270)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_store_name","s_market_id","s_state","s_zip"]
+ <-Map 24 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_333]
+ PartitionCols:_col2
+ Select Operator [SEL_332] (rows=525333486 width=122)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_331] (rows=525333486 width=122)
+ predicate:((ss_store_sk BETWEEN DynamicValue(RS_59_store_s_store_sk_min) AND DynamicValue(RS_59_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_59_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_store_sk is not null)
+ TableScan [TS_42] (rows=575995635 width=122)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_sales_price"]
+ <-Reducer 20 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_330]
+ Group By Operator [GBY_329] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 16 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_307]
+ Group By Operator [GBY_305] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_303] (rows=155 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_299]
+ <-Reducer 7 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_328]
+ Select Operator [SEL_327] (rows=235181232 width=380)
+ Output:["_col0","_col1","_col2","_col3"]
+ Group By Operator [GBY_326] (rows=235181232 width=380)
+ Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col9)"],keys:_col1, _col2, _col7
+ Select Operator [SEL_325] (rows=365777643230 width=843)
+ Output:["_col1","_col2","_col7","_col9"]
+ Group By Operator [GBY_324] (rows=365777643230 width=843)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_34]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_33] (rows=365777643230 width=843)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(_col4)"],keys:_col15, _col16, _col11, _col20, _col6, _col7, _col8, _col9, _col12
+ Merge Join Operator [MERGEJOIN_281] (rows=365777643230 width=843)
+ Conds:RS_29._col13, _col17=RS_322._col1, _col2(Inner),Output:["_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col15","_col16","_col20"]
+ <-Map 23 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_322]
+ PartitionCols:_col1, _col2
+ Please refer to the previous Select Operator [SEL_321]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_29]
+ PartitionCols:_col13, _col17
+ Merge Join Operator [MERGEJOIN_280] (rows=92733777 width=910)
+ Conds:RS_26._col0, _col3=RS_318._col0, _col1(Inner),Output:["_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13","_col15","_col16","_col17"]
+ <-Map 22 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_318]
+ PartitionCols:_col0, _col1
+ Please refer to the previous Select Operator [SEL_317]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_26]
+ PartitionCols:_col0, _col3
+ Merge Join Operator [MERGEJOIN_279] (rows=56246341 width=899)
+ Conds:RS_23._col1=RS_315._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13","_col15","_col16","_col17"]
+ <-Map 21 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_315]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_314]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_23]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_278] (rows=56246341 width=630)
+ Conds:RS_20._col2=RS_300._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13"]
+ <-Map 16 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_300]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_299]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_277] (rows=76612563 width=382)
+ Conds:RS_312._col0=RS_292._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col6","_col7","_col8","_col9"]
+ <-Map 9 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_292]
+ PartitionCols:_col0
+ Select Operator [SEL_290] (rows=7000 width=295)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_288] (rows=7000 width=384)
+ predicate:(i_color = 'orchid')
+ Please refer to the previous TableScan [TS_3]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_312]
+ PartitionCols:_col0
+ Select Operator [SEL_311] (rows=525333486 width=122)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_310] (rows=525333486 width=122)
+ predicate:((ss_item_sk BETWEEN DynamicValue(RS_18_item_i_item_sk_min) AND DynamicValue(RS_18_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_18_item_i_item_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_21_store_s_store_sk_min) AND DynamicValue(RS_21_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_21_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=122)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_sales_price"]
+ <-Reducer 10 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_297]
+ Group By Operator [GBY_296] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 9 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_295]
+ Group By Operator [GBY_294] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_293] (rows=7000 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_290]
+ <-Reducer 17 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_309]
+ Group By Operator [GBY_308] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 16 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_306]
+ Group By Operator [GBY_304] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_301] (rows=155 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_299]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out
new file mode 100644
index 0000000..a353c6a
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out
@@ -0,0 +1,321 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_returns
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_returns
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 12 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE), Reducer 16 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Map 18 <- Reducer 14 (BROADCAST_EDGE), Reducer 16 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE)
+Reducer 10 <- Map 18 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 11 <- Reducer 10 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 10 (CUSTOM_SIMPLE_EDGE)
+Reducer 13 <- Reducer 10 (CUSTOM_SIMPLE_EDGE)
+Reducer 14 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+Reducer 15 <- Map 19 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 16 <- Reducer 15 (CUSTOM_SIMPLE_EDGE)
+Reducer 17 <- Reducer 15 (CUSTOM_SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 11 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 20 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 21 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7 vectorized
+ File Output Operator [FS_259]
+ Limit [LIM_258] (rows=100 width=808)
+ Number of rows:100
+ Select Operator [SEL_257] (rows=4248052806 width=808)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ <-Reducer 6 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_256]
+ Group By Operator [GBY_255] (rows=4248052806 width=808)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_47]
+ PartitionCols:_col0, _col1, _col2, _col3
+ Group By Operator [GBY_46] (rows=4248052806 width=808)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["sum(_col5)","sum(_col16)","sum(_col10)"],keys:_col19, _col20, _col22, _col23
+ Top N Key Operator [TNK_93] (rows=4248052806 width=807)
+ keys:_col19, _col20, _col22, _col23,sort order:++++,top n:100
+ Merge Join Operator [MERGEJOIN_212] (rows=4248052806 width=807)
+ Conds:RS_42._col3=RS_254._col0(Inner),Output:["_col5","_col10","_col16","_col19","_col20","_col22","_col23"]
+ <-Map 21 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_254]
+ PartitionCols:_col0
+ Select Operator [SEL_253] (rows=1704 width=192)
+ Output:["_col0","_col1","_col2"]
+ TableScan [TS_31] (rows=1704 width=192)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_store_id","s_store_name"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_42]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_211] (rows=4248052806 width=623)
+ Conds:RS_39._col1=RS_252._col0(Inner),Output:["_col3","_col5","_col10","_col16","_col19","_col20"]
+ <-Map 20 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_252]
+ PartitionCols:_col0
+ Select Operator [SEL_251] (rows=462000 width=288)
+ Output:["_col0","_col1","_col2"]
+ TableScan [TS_29] (rows=462000 width=288)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id","i_item_desc"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_39]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_210] (rows=4248052806 width=343)
+ Conds:RS_36._col1, _col2, _col4=RS_37._col6, _col7, _col8(Inner),Output:["_col1","_col3","_col5","_col10","_col16"]
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_37]
+ PartitionCols:_col6, _col7, _col8
+ Merge Join Operator [MERGEJOIN_209] (rows=1893811716 width=235)
+ Conds:RS_25._col2, _col1=RS_26._col1, _col2(Inner),Output:["_col3","_col6","_col7","_col8","_col9"]
+ <-Reducer 10 [SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_25]
+ PartitionCols:_col2, _col1
+ Merge Join Operator [MERGEJOIN_207] (rows=54418158 width=119)
+ Conds:RS_243._col0=RS_221._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_221]
+ PartitionCols:_col0
+ Select Operator [SEL_217] (rows=351 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_214] (rows=351 width=12)
+ predicate:((d_year = 2000) and d_moy BETWEEN 4 AND 10)
+ TableScan [TS_3] (rows=73049 width=12)
+ default@date_dim,d1,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"]
+ <-Map 18 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_243]
+ PartitionCols:_col0
+ Select Operator [SEL_242] (rows=285117831 width=123)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_241] (rows=285117831 width=123)
+ predicate:((cs_bill_customer_sk BETWEEN DynamicValue(RS_26_store_returns_sr_customer_sk_min) AND DynamicValue(RS_26_store_returns_sr_customer_sk_max) and in_bloom_filter(cs_bill_customer_sk, DynamicValue(RS_26_store_returns_sr_customer_sk_bloom_filter))) and (cs_item_sk BETWEEN DynamicValue(RS_26_store_returns_sr_item_sk_min) AND DynamicValue(RS_26_store_returns_sr_item_sk_max) and in_bloom_filter(cs_item_sk, DynamicValue(RS_26_store_returns_sr_item_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_23_d3_d_date_sk_min) AND DynamicValue(RS_23_d3_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_23_d3_d_date_sk_bloom_filter))) and cs_bill_customer_sk is not null and cs_sold_date_sk is not null)
+ TableScan [TS_6] (rows=287989836 width=123)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk","cs_net_profit"]
+ <-Reducer 16 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_235]
+ Group By Operator [GBY_233] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Reducer 15 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_106]
+ Group By Operator [GBY_105] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_104] (rows=9402909 width=8)
+ Output:["_col0"]
+ Merge Join Operator [MERGEJOIN_208] (rows=9402909 width=100)
+ Conds:RS_232._col0=RS_223._col0(Inner),Output:["_col1","_col2","_col3","_col4"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_223]
+ PartitionCols:_col0
+ Select Operator [SEL_218] (rows=351 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_215] (rows=351 width=12)
+ predicate:((d_year = 2000) and d_moy BETWEEN 4 AND 10)
+ Please refer to the previous TableScan [TS_3]
+ <-Map 19 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_232]
+ PartitionCols:_col0
+ Select Operator [SEL_231] (rows=53632139 width=123)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_230] (rows=53632139 width=123)
+ predicate:(sr_customer_sk is not null and sr_returned_date_sk is not null)
+ TableScan [TS_12] (rows=57591150 width=123)
+ default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_returned_date_sk","sr_item_sk","sr_customer_sk","sr_ticket_number","sr_net_loss"]
+ <-Reducer 17 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_240]
+ Group By Operator [GBY_238] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Reducer 15 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_121]
+ Group By Operator [GBY_120] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_119] (rows=9402909 width=6)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_208]
+ <-Reducer 14 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_237]
+ Group By Operator [GBY_236] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_227]
+ Group By Operator [GBY_225] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_222] (rows=351 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_217]
+ <-Reducer 15 [SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_26]
+ PartitionCols:_col1, _col2
+ Please refer to the previous Merge Join Operator [MERGEJOIN_208]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_36]
+ PartitionCols:_col1, _col2, _col4
+ Merge Join Operator [MERGEJOIN_206] (rows=13737330 width=8)
+ Conds:RS_250._col0=RS_219._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_219]
+ PartitionCols:_col0
+ Select Operator [SEL_216] (rows=50 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_213] (rows=50 width=12)
+ predicate:((d_moy = 4) and (d_year = 2000))
+ Please refer to the previous TableScan [TS_3]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_250]
+ PartitionCols:_col0
+ Select Operator [SEL_249] (rows=501694138 width=126)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_248] (rows=501694138 width=126)
+ predicate:((ss_customer_sk BETWEEN DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_min) AND DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_bloom_filter))) and (ss_customer_sk BETWEEN DynamicValue(RS_26_store_returns_sr_customer_sk_min) AND DynamicValue(RS_26_store_returns_sr_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_26_store_returns_sr_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_25_catalog_sales_cs_item_sk_min) AND DynamicValue(RS_25_catalog_sales_cs_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_25_catalog_sales_cs_item_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_26_store_returns_sr_item_sk_min) AND DynamicValue(RS_26_store_returns_sr_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_26_store_returns_sr_item_sk_bloom_filter))) and (ss_sold_date_sk
BETWEEN DynamicValue(RS_34_d1_d_date_sk_min) AND DynamicValue(RS_34_d1_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_34_d1_d_date_sk_bloom_filter))) and ss_customer_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=126)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_net_profit"]
+ <-Reducer 16 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_234]
+ Please refer to the previous Group By Operator [GBY_233]
+ <-Reducer 17 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_239]
+ Please refer to the previous Group By Operator [GBY_238]
+ <-Reducer 12 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_245]
+ Group By Operator [GBY_244] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Reducer 10 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_111]
+ Group By Operator [GBY_110] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_109] (rows=54418158 width=8)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_207]
+ <-Reducer 13 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_247]
+ Group By Operator [GBY_246] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Reducer 10 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_126]
+ Group By Operator [GBY_125] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_124] (rows=54418158 width=7)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_207]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_229]
+ Group By Operator [GBY_228] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_226]
+ Group By Operator [GBY_224] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_220] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_216]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out
new file mode 100644
index 0000000..ed032b1
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out
@@ -0,0 +1,171 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer_demographics
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@promotion
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer_demographics
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@promotion
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 11 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 11 <- Map 10 (CUSTOM_SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7 vectorized
+ File Output Operator [FS_128]
+ Limit [LIM_127] (rows=100 width=444)
+ Number of rows:100
+ Select Operator [SEL_126] (rows=310774 width=444)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 6 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_125]
+ Select Operator [SEL_124] (rows=310774 width=444)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_123] (rows=310774 width=476)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)","sum(VALUE._col2)","count(VALUE._col3)","sum(VALUE._col4)","count(VALUE._col5)","sum(VALUE._col6)","count(VALUE._col7)"],keys:KEY._col0
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_28]
+ PartitionCols:_col0
+ Group By Operator [GBY_27] (rows=462000 width=476)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(_col4)","count(_col4)","sum(_col5)","count(_col5)","sum(_col7)","count(_col7)","sum(_col6)","count(_col6)"],keys:_col12
+ Top N Key Operator [TNK_54] (rows=809521 width=100)
+ keys:_col12,sort order:+,top n:100
+ Merge Join Operator [MERGEJOIN_98] (rows=809521 width=100)
+ Conds:RS_23._col2=RS_122._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col12"]
+ <-Map 13 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_122]
+ PartitionCols:_col0
+ Select Operator [SEL_121] (rows=462000 width=104)
+ Output:["_col0","_col1"]
+ TableScan [TS_12] (rows=462000 width=104)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_23]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_97] (rows=809521 width=4)
+ Conds:RS_20._col3=RS_120._col0(Inner),Output:["_col2","_col4","_col5","_col6","_col7"]
+ <-Map 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_120]
+ PartitionCols:_col0
+ Select Operator [SEL_119] (rows=2300 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_118] (rows=2300 width=174)
+ predicate:((p_channel_email = 'N') or (p_channel_event = 'N'))
+ TableScan [TS_9] (rows=2300 width=174)
+ default@promotion,promotion,Tbl:COMPLETE,Col:COMPLETE,Output:["p_promo_sk","p_channel_email","p_channel_event"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_96] (rows=809521 width=4)
+ Conds:RS_17._col0=RS_109._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 10 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_109]
+ PartitionCols:_col0
+ Select Operator [SEL_108] (rows=652 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_107] (rows=652 width=8)
+ predicate:(d_year = 1998)
+ TableScan [TS_6] (rows=73049 width=8)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_95] (rows=2283326 width=135)
+ Conds:RS_117._col1=RS_101._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_101]
+ PartitionCols:_col0
+ Select Operator [SEL_100] (rows=14776 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_99] (rows=14776 width=268)
+ predicate:((cd_education_status = 'Primary') and (cd_gender = 'F') and (cd_marital_status = 'W'))
+ TableScan [TS_3] (rows=1861800 width=268)
+ default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_117]
+ PartitionCols:_col1
+ Select Operator [SEL_116] (rows=283691050 width=354)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Filter Operator [FIL_115] (rows=283691050 width=354)
+ predicate:((cs_bill_cdemo_sk BETWEEN DynamicValue(RS_15_customer_demographics_cd_demo_sk_min) AND DynamicValue(RS_15_customer_demographics_cd_demo_sk_max) and in_bloom_filter(cs_bill_cdemo_sk, DynamicValue(RS_15_customer_demographics_cd_demo_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_18_date_dim_d_date_sk_min) AND DynamicValue(RS_18_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_18_date_dim_d_date_sk_bloom_filter))) and cs_bill_cdemo_sk is not null and cs_promo_sk is not null and cs_sold_date_sk is not null)
+ TableScan [TS_0] (rows=287989836 width=354)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_cdemo_sk","cs_item_sk","cs_promo_sk","cs_quantity","cs_list_price","cs_sales_price","cs_coupon_amt"]
+ <-Reducer 11 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_114]
+ Group By Operator [GBY_113] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 10 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_112]
+ Group By Operator [GBY_111] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_110] (rows=652 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_108]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_106]
+ Group By Operator [GBY_105] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_104]
+ Group By Operator [GBY_103] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_102] (rows=14776 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_100]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out
new file mode 100644
index 0000000..fdc1791
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out
@@ -0,0 +1,189 @@
+PREHOOK: query: explain
+select i_item_id,
+ s_state, grouping(s_state) g_state,
+ avg(ss_quantity) agg1,
+ avg(ss_list_price) agg2,
+ avg(ss_coupon_amt) agg3,
+ avg(ss_sales_price) agg4
+ from store_sales, customer_demographics, date_dim, store, item
+ where ss_sold_date_sk = d_date_sk and
+ ss_item_sk = i_item_sk and
+ ss_store_sk = s_store_sk and
+ ss_cdemo_sk = cd_demo_sk and
+ cd_gender = 'M' and
+ cd_marital_status = 'U' and
+ cd_education_status = '2 yr Degree' and
+ d_year = 2001 and
+ s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
+ group by rollup (i_item_id, s_state)
+ order by i_item_id
+ ,s_state
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@customer_demographics
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+select i_item_id,
+ s_state, grouping(s_state) g_state,
+ avg(ss_quantity) agg1,
+ avg(ss_list_price) agg2,
+ avg(ss_coupon_amt) agg3,
+ avg(ss_sales_price) agg4
+ from store_sales, customer_demographics, date_dim, store, item
+ where ss_sold_date_sk = d_date_sk and
+ ss_item_sk = i_item_sk and
+ ss_store_sk = s_store_sk and
+ ss_cdemo_sk = cd_demo_sk and
+ cd_gender = 'M' and
+ cd_marital_status = 'U' and
+ cd_education_status = '2 yr Degree' and
+ d_year = 2001 and
+ s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
+ group by rollup (i_item_id, s_state)
+ order by i_item_id
+ ,s_state
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@customer_demographics
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 11 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 11 <- Map 10 (CUSTOM_SIMPLE_EDGE)
+Reducer 13 <- Map 12 (CUSTOM_SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 14 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7 vectorized
+ File Output Operator [FS_134]
+ Limit [LIM_133] (rows=100 width=538)
+ Number of rows:100
+ Select Operator [SEL_132] (rows=4281825 width=538)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ <-Reducer 6 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_131]
+ Select Operator [SEL_130] (rows=4281825 width=538)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Group By Operator [GBY_129] (rows=4281825 width=570)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)","sum(VALUE._col2)","count(VALUE._col3)","sum(VALUE._col4)","count(VALUE._col5)","sum(VALUE._col6)","count(VALUE._col7)"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_29]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_28] (rows=4281825 width=570)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(_col2)","count(_col2)","sum(_col3)","count(_col3)","sum(_col4)","count(_col4)","sum(_col5)","count(_col5)"],keys:_col0, _col1, 0L
+ Top N Key Operator [TNK_55] (rows=1427275 width=186)
+ keys:_col0, _col1, 0L,sort order:+++,top n:100
+ Select Operator [SEL_26] (rows=1427275 width=186)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Merge Join Operator [MERGEJOIN_99] (rows=1427275 width=186)
+ Conds:RS_23._col1=RS_128._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col11","_col13"]
+ <-Map 14 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_128]
+ PartitionCols:_col0
+ Select Operator [SEL_127] (rows=462000 width=104)
+ Output:["_col0","_col1"]
+ TableScan [TS_12] (rows=462000 width=104)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_23]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_98] (rows=1427275 width=90)
+ Conds:RS_20._col3=RS_118._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col11"]
+ <-Map 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_118]
+ PartitionCols:_col0
+ Select Operator [SEL_117] (rows=209 width=90)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_116] (rows=209 width=90)
+ predicate:(s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC')
+ TableScan [TS_9] (rows=1704 width=90)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_state"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_97] (rows=1441779 width=4)
+ Conds:RS_17._col0=RS_110._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 10 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_110]
+ PartitionCols:_col0
+ Select Operator [SEL_109] (rows=652 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_108] (rows=652 width=8)
+ predicate:(d_year = 2001)
+ TableScan [TS_6] (rows=73049 width=8)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_96] (rows=4037920 width=4)
+ Conds:RS_126._col2=RS_102._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_102]
+ PartitionCols:_col0
+ Select Operator [SEL_101] (rows=14776 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_100] (rows=14776 width=268)
+ predicate:((cd_education_status = '2 yr Degree') and (cd_gender = 'M') and (cd_marital_status = 'U'))
+ TableScan [TS_3] (rows=1861800 width=268)
+ default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_126]
+ PartitionCols:_col2
+ Select Operator [SEL_125] (rows=501690006 width=340)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Filter Operator [FIL_124] (rows=501690006 width=340)
+ predicate:((ss_cdemo_sk BETWEEN DynamicValue(RS_15_customer_demographics_cd_demo_sk_min) AND DynamicValue(RS_15_customer_demographics_cd_demo_sk_max) and in_bloom_filter(ss_cdemo_sk, DynamicValue(RS_15_customer_demographics_cd_demo_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_18_date_dim_d_date_sk_min) AND DynamicValue(RS_18_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_18_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_21_store_s_store_sk_min) AND DynamicValue(RS_21_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_21_store_s_store_sk_bloom_filter))) and ss_cdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=340)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_store_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"]
+ <-Reducer 11 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_115]
+ Group By Operator [GBY_114] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 10 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_113]
+ Group By Operator [GBY_112] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_111] (rows=652 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_109]
+ <-Reducer 13 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_123]
+ Group By Operator [GBY_122] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 12 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_121]
+ Group By Operator [GBY_120] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_119] (rows=209 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_117]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_107]
+ Group By Operator [GBY_106] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_105]
+ Group By Operator [GBY_104] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_103] (rows=14776 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_101]
+