You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by jc...@apache.org on 2018/10/22 02:10:17 UTC
[12/51] [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/query61.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out
new file mode 100644
index 0000000..d9543ad
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out
@@ -0,0 +1,388 @@
+Warning: Shuffle Join MERGEJOIN[266][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 5' is a cross product
+PREHOOK: query: explain
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@promotion
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@promotion
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 12 <- Reducer 18 (BROADCAST_EDGE), Reducer 24 (BROADCAST_EDGE), Reducer 27 (BROADCAST_EDGE), Reducer 7 (BROADCAST_EDGE)
+Map 30 <- Reducer 10 (BROADCAST_EDGE), Reducer 22 (BROADCAST_EDGE), Reducer 25 (BROADCAST_EDGE), Reducer 28 (BROADCAST_EDGE)
+Reducer 10 <- Reducer 2 (CUSTOM_SIMPLE_EDGE)
+Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE)
+Reducer 14 <- Map 23 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE)
+Reducer 15 <- Map 26 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE)
+Reducer 16 <- Map 29 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE)
+Reducer 18 <- Map 17 (CUSTOM_SIMPLE_EDGE)
+Reducer 19 <- Map 17 (SIMPLE_EDGE), Map 30 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 11 (SIMPLE_EDGE)
+Reducer 20 <- Map 23 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE)
+Reducer 21 <- Map 26 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE)
+Reducer 22 <- Map 17 (CUSTOM_SIMPLE_EDGE)
+Reducer 24 <- Map 23 (CUSTOM_SIMPLE_EDGE)
+Reducer 25 <- Map 23 (CUSTOM_SIMPLE_EDGE)
+Reducer 27 <- Map 26 (CUSTOM_SIMPLE_EDGE)
+Reducer 28 <- Map 26 (CUSTOM_SIMPLE_EDGE)
+Reducer 3 <- Reducer 16 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (CUSTOM_SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (CUSTOM_SIMPLE_EDGE), Reducer 9 (CUSTOM_SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 2 (CUSTOM_SIMPLE_EDGE)
+Reducer 8 <- Reducer 2 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 6 vectorized
+ File Output Operator [FS_336]
+ Limit [LIM_335] (rows=1 width=336)
+ Number of rows:100
+ Select Operator [SEL_334] (rows=1 width=336)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_88]
+ Select Operator [SEL_87] (rows=1 width=336)
+ Output:["_col0","_col1","_col2"]
+ Merge Join Operator [MERGEJOIN_266] (rows=1 width=448)
+ Conds:(Inner),Output:["_col0","_col1","_col2","_col3"]
+ <-Reducer 4 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_319]
+ Select Operator [SEL_318] (rows=1 width=224)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_317] (rows=1 width=112)
+ Output:["_col0"],aggregations:["sum(VALUE._col0)"]
+ <-Reducer 3 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_42]
+ Group By Operator [GBY_41] (rows=1 width=112)
+ Output:["_col0"],aggregations:["sum(_col8)"]
+ Merge Join Operator [MERGEJOIN_264] (rows=505397 width=0)
+ Conds:RS_37._col0=RS_38._col2(Inner),Output:["_col8"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_37]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_255] (rows=16000001 width=4)
+ Conds:RS_269._col1=RS_272._col0(Inner),Output:["_col0"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_269]
+ PartitionCols:_col1
+ Select Operator [SEL_268] (rows=80000000 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_267] (rows=80000000 width=8)
+ predicate:c_current_addr_sk is not null
+ TableScan [TS_0] (rows=80000000 width=8)
+ default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_current_addr_sk"]
+ <-Map 11 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_272]
+ PartitionCols:_col0
+ Select Operator [SEL_271] (rows=8000000 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_270] (rows=8000000 width=112)
+ predicate:(ca_gmt_offset = -7)
+ TableScan [TS_3] (rows=40000000 width=112)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_address_sk","ca_gmt_offset"]
+ <-Reducer 16 [SIMPLE_EDGE]
+ SHUFFLE [RS_38]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_259] (rows=2526982 width=0)
+ Conds:RS_30._col4=RS_316._col0(Inner),Output:["_col2","_col5"]
+ <-Map 29 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_316]
+ PartitionCols:_col0
+ Select Operator [SEL_315] (rows=2300 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_314] (rows=2300 width=259)
+ predicate:((p_channel_dmail = 'Y') or (p_channel_email = 'Y') or (p_channel_tv = 'Y'))
+ TableScan [TS_18] (rows=2300 width=259)
+ default@promotion,promotion,Tbl:COMPLETE,Col:COMPLETE,Output:["p_promo_sk","p_channel_dmail","p_channel_email","p_channel_tv"]
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_30]
+ PartitionCols:_col4
+ Merge Join Operator [MERGEJOIN_258] (rows=2526982 width=0)
+ Conds:RS_27._col3=RS_299._col0(Inner),Output:["_col2","_col4","_col5"]
+ <-Map 26 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_299]
+ PartitionCols:_col0
+ Select Operator [SEL_298] (rows=341 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_297] (rows=341 width=115)
+ predicate:(s_gmt_offset = -7)
+ TableScan [TS_15] (rows=1704 width=115)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_gmt_offset"]
+ <-Reducer 14 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_257] (rows=12627499 width=0)
+ Conds:RS_24._col1=RS_287._col0(Inner),Output:["_col2","_col3","_col4","_col5"]
+ <-Map 23 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_287]
+ PartitionCols:_col0
+ Select Operator [SEL_286] (rows=46200 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_285] (rows=46200 width=94)
+ predicate:(i_category = 'Electronics')
+ TableScan [TS_12] (rows=462000 width=94)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_category"]
+ <-Reducer 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_24]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_256] (rows=13119234 width=4)
+ Conds:RS_313._col0=RS_275._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5"]
+ <-Map 17 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_275]
+ PartitionCols:_col0
+ Select Operator [SEL_274] (rows=50 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_273] (rows=50 width=12)
+ predicate:((d_moy = 11) and (d_year = 1999))
+ TableScan [TS_9] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"]
+ <-Map 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_313]
+ PartitionCols:_col0
+ Select Operator [SEL_312] (rows=479120970 width=126)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_311] (rows=479120970 width=126)
+ predicate:((ss_customer_sk BETWEEN DynamicValue(RS_37_customer_c_customer_sk_min) AND DynamicValue(RS_37_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_37_customer_c_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_25_item_i_item_sk_min) AND DynamicValue(RS_25_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_25_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_22_date_dim_d_date_sk_min) AND DynamicValue(RS_22_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_22_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_28_store_s_store_sk_min) AND DynamicValue(RS_28_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_28_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_promo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_6] (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_promo_sk","ss_ext_sales_price"]
+ <-Reducer 18 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_284]
+ Group By Operator [GBY_283] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 17 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_281]
+ Group By Operator [GBY_279] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_276] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_274]
+ <-Reducer 24 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_296]
+ Group By Operator [GBY_295] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_293]
+ Group By Operator [GBY_291] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_288] (rows=46200 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_286]
+ <-Reducer 27 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_308]
+ Group By Operator [GBY_307] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 26 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_305]
+ Group By Operator [GBY_303] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_300] (rows=341 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_298]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_310]
+ Group By Operator [GBY_309] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=14591048)"]
+ <-Reducer 2 [CUSTOM_SIMPLE_EDGE]
+ SHUFFLE [RS_182]
+ Group By Operator [GBY_181] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=14591048)"]
+ Select Operator [SEL_180] (rows=16000001 width=4)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_255]
+ <-Reducer 9 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_333]
+ Select Operator [SEL_332] (rows=1 width=224)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_331] (rows=1 width=112)
+ Output:["_col0"],aggregations:["sum(VALUE._col0)"]
+ <-Reducer 8 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_81]
+ Group By Operator [GBY_80] (rows=1 width=112)
+ Output:["_col0"],aggregations:["sum(_col7)"]
+ Merge Join Operator [MERGEJOIN_265] (rows=529208 width=0)
+ Conds:RS_76._col0=RS_77._col2(Inner),Output:["_col7"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_76]
+ PartitionCols:_col0
+ Please refer to the previous Merge Join Operator [MERGEJOIN_255]
+ <-Reducer 21 [SIMPLE_EDGE]
+ SHUFFLE [RS_77]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_263] (rows=2646038 width=0)
+ Conds:RS_69._col3=RS_301._col0(Inner),Output:["_col2","_col4"]
+ <-Map 26 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_301]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_298]
+ <-Reducer 20 [SIMPLE_EDGE]
+ SHUFFLE [RS_69]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_262] (rows=13222427 width=0)
+ Conds:RS_66._col1=RS_289._col0(Inner),Output:["_col2","_col3","_col4"]
+ <-Map 23 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_289]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_286]
+ <-Reducer 19 [SIMPLE_EDGE]
+ SHUFFLE [RS_66]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_261] (rows=13737330 width=4)
+ Conds:RS_330._col0=RS_277._col0(Inner),Output:["_col1","_col2","_col3","_col4"]
+ <-Map 17 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_277]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_274]
+ <-Map 30 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_330]
+ PartitionCols:_col0
+ Select Operator [SEL_329] (rows=501694138 width=122)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_328] (rows=501694138 width=122)
+ predicate:((ss_customer_sk BETWEEN DynamicValue(RS_76_customer_c_customer_sk_min) AND DynamicValue(RS_76_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_76_customer_c_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_67_item_i_item_sk_min) AND DynamicValue(RS_67_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_67_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_64_date_dim_d_date_sk_min) AND DynamicValue(RS_64_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_64_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_70_store_s_store_sk_min) AND DynamicValue(RS_70_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_70_store_s_store_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_51] (rows=575995635 width=122)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_store_sk","ss_ext_sales_price"]
+ <-Reducer 10 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_327]
+ Group By Operator [GBY_326] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=14591048)"]
+ <-Reducer 2 [CUSTOM_SIMPLE_EDGE]
+ SHUFFLE [RS_237]
+ Group By Operator [GBY_236] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=14591048)"]
+ Select Operator [SEL_235] (rows=16000001 width=4)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_255]
+ <-Reducer 22 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_321]
+ Group By Operator [GBY_320] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 17 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_282]
+ Group By Operator [GBY_280] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_278] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_274]
+ <-Reducer 25 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_323]
+ Group By Operator [GBY_322] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_294]
+ Group By Operator [GBY_292] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_290] (rows=46200 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_286]
+ <-Reducer 28 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_325]
+ Group By Operator [GBY_324] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 26 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_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_302] (rows=341 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_298]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out
new file mode 100644
index 0000000..a1f8413
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out
@@ -0,0 +1,163 @@
+PREHOOK: query: explain
+select *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
+ from item
+ ,store_sales
+ ,date_dim
+ ,store
+ where ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','refernece','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, d_moy) tmp1
+where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100
+PREHOOK: type: QUERY
+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 *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
+ from item
+ ,store_sales
+ ,date_dim
+ ,store
+ where ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','refernece','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, d_moy) tmp1
+where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100
+POSTHOOK: type: QUERY
+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 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 5 vectorized
+ File Output Operator [FS_86]
+ Limit [LIM_85] (rows=71 width=228)
+ Number of rows:100
+ Select Operator [SEL_84] (rows=71 width=228)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ Select Operator [SEL_24] (rows=71 width=228)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_36] (rows=71 width=228)
+ predicate:CASE WHEN ((avg_window_0 > 0)) THEN (((abs((_col2 - avg_window_0)) / avg_window_0) > 0.1)) ELSE (null) END
+ Select Operator [SEL_23] (rows=143 width=116)
+ Output:["avg_window_0","_col0","_col2"]
+ PTF Operator [PTF_22] (rows=143 width=116)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col0 ASC NULLS FIRST","partition by:":"_col0"}]
+ Select Operator [SEL_19] (rows=143 width=116)
+ Output:["_col0","_col2"]
+ Group By Operator [GBY_18] (rows=143 width=120)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Group By Operator [GBY_16] (rows=143 width=120)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col4, _col6
+ Merge Join Operator [MERGEJOIN_62] (rows=129200 width=8)
+ Conds:RS_12._col0=RS_73._col0(Inner),Output:["_col2","_col4","_col6"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_73]
+ PartitionCols:_col0
+ Select Operator [SEL_72] (rows=317 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_71] (rows=317 width=12)
+ predicate:(d_month_seq) IN (1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223)
+ TableScan [TS_6] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq","d_moy"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_61] (rows=744232 width=4)
+ Conds:RS_81._col1=RS_65._col0(Inner),Output:["_col0","_col2","_col4"]
+ <-Map 6 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_65]
+ PartitionCols:_col0
+ Select Operator [SEL_64] (rows=68 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_63] (rows=68 width=290)
+ predicate:((((i_category) IN ('Books', 'Children', 'Electronics') and (i_class) IN ('personal', 'portable', 'refernece', 'self-help') and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) or ((i_category) IN ('Women', 'Music', 'Men') and (i_class) IN ('accessories', 'classical', 'fragrances', 'pants') and (i_brand) IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9', 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') and (i_category) IN ('Books', 'Children', 'Electronics', 'Women', 'Music', 'Men') and (i_class) IN ('personal', 'portable', 'refernece', 'self-help', 'accessories', 'classical', 'fragrances', 'pants'))
+ TableScan [TS_3] (rows=462000 width=289)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand","i_class","i_category","i_manager_id"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_81]
+ PartitionCols:_col1
+ Select Operator [SEL_80] (rows=525329897 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_79] (rows=525329897 width=118)
+ predicate:((ss_item_sk BETWEEN DynamicValue(RS_10_item_i_item_sk_min) AND DynamicValue(RS_10_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_10_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_13_date_dim_d_date_sk_min) AND DynamicValue(RS_13_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_13_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=118)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_sales_price"]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_70]
+ Group By Operator [GBY_69] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_68]
+ Group By Operator [GBY_67] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_66] (rows=68 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_64]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_78]
+ Group By Operator [GBY_77] (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_76]
+ Group By Operator [GBY_75] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_74] (rows=317 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_72]
+