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:11 UTC
[06/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/query77.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out
new file mode 100644
index 0000000..78f7c28
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out
@@ -0,0 +1,504 @@
+Warning: Shuffle Join MERGEJOIN[225][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 14' is a cross product
+PREHOOK: query: explain
+with ss as
+ (select s_store_sk,
+ sum(ss_ext_sales_price) as sales,
+ sum(ss_net_profit) as profit
+ from store_sales,
+ date_dim,
+ store
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ss_store_sk = s_store_sk
+ group by s_store_sk)
+ ,
+ sr as
+ (select s_store_sk,
+ sum(sr_return_amt) as returns,
+ sum(sr_net_loss) as profit_loss
+ from store_returns,
+ date_dim,
+ store
+ where sr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and sr_store_sk = s_store_sk
+ group by s_store_sk),
+ cs as
+ (select cs_call_center_sk,
+ sum(cs_ext_sales_price) as sales,
+ sum(cs_net_profit) as profit
+ from catalog_sales,
+ date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ group by cs_call_center_sk
+ ),
+ cr as
+ (select
+ sum(cr_return_amount) as returns,
+ sum(cr_net_loss) as profit_loss
+ from catalog_returns,
+ date_dim
+ where cr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ ),
+ ws as
+ ( select wp_web_page_sk,
+ sum(ws_ext_sales_price) as sales,
+ sum(ws_net_profit) as profit
+ from web_sales,
+ date_dim,
+ web_page
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ws_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk),
+ wr as
+ (select wp_web_page_sk,
+ sum(wr_return_amt) as returns,
+ sum(wr_net_loss) as profit_loss
+ from web_returns,
+ date_dim,
+ web_page
+ where wr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and wr_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , ss.s_store_sk as id
+ , sales
+ , coalesce(returns, 0) as returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ss left join sr
+ on ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+ , cs_call_center_sk as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from cs
+ , cr
+ union all
+ select 'web channel' as channel
+ , ws.wp_web_page_sk as id
+ , sales
+ , coalesce(returns, 0) returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ws left join wr
+ on ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_returns
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_returns
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_page
+PREHOOK: Input: default@web_returns
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+with ss as
+ (select s_store_sk,
+ sum(ss_ext_sales_price) as sales,
+ sum(ss_net_profit) as profit
+ from store_sales,
+ date_dim,
+ store
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ss_store_sk = s_store_sk
+ group by s_store_sk)
+ ,
+ sr as
+ (select s_store_sk,
+ sum(sr_return_amt) as returns,
+ sum(sr_net_loss) as profit_loss
+ from store_returns,
+ date_dim,
+ store
+ where sr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and sr_store_sk = s_store_sk
+ group by s_store_sk),
+ cs as
+ (select cs_call_center_sk,
+ sum(cs_ext_sales_price) as sales,
+ sum(cs_net_profit) as profit
+ from catalog_sales,
+ date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ group by cs_call_center_sk
+ ),
+ cr as
+ (select
+ sum(cr_return_amount) as returns,
+ sum(cr_net_loss) as profit_loss
+ from catalog_returns,
+ date_dim
+ where cr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ ),
+ ws as
+ ( select wp_web_page_sk,
+ sum(ws_ext_sales_price) as sales,
+ sum(ws_net_profit) as profit
+ from web_sales,
+ date_dim,
+ web_page
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ws_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk),
+ wr as
+ (select wp_web_page_sk,
+ sum(wr_return_amt) as returns,
+ sum(wr_net_loss) as profit_loss
+ from web_returns,
+ date_dim,
+ web_page
+ where wr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and wr_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , ss.s_store_sk as id
+ , sales
+ , coalesce(returns, 0) as returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ss left join sr
+ on ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+ , cs_call_center_sk as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from cs
+ , cr
+ union all
+ select 'web channel' as channel
+ , ws.wp_web_page_sk as id
+ , sales
+ , coalesce(returns, 0) returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ws left join wr
+ on ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_returns
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_returns
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_page
+POSTHOOK: Input: default@web_returns
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 9 (BROADCAST_EDGE)
+Map 25 <- Reducer 15 (BROADCAST_EDGE)
+Map 27 <- Reducer 21 (BROADCAST_EDGE)
+Reducer 10 <- Map 24 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 11 <- Reducer 10 (SIMPLE_EDGE)
+Reducer 12 <- Map 25 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 13 <- Reducer 12 (SIMPLE_EDGE)
+Reducer 14 <- Reducer 13 (CUSTOM_SIMPLE_EDGE), Reducer 17 (CUSTOM_SIMPLE_EDGE), Union 5 (CONTAINS)
+Reducer 15 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+Reducer 16 <- Map 26 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 17 <- Reducer 16 (CUSTOM_SIMPLE_EDGE)
+Reducer 18 <- Map 27 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 19 <- Reducer 18 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 20 <- Reducer 19 (ONE_TO_ONE_EDGE), Reducer 23 (ONE_TO_ONE_EDGE), Union 5 (CONTAINS)
+Reducer 21 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+Reducer 22 <- Map 28 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 23 <- Reducer 22 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 11 (ONE_TO_ONE_EDGE), Reducer 3 (ONE_TO_ONE_EDGE), Union 5 (CONTAINS)
+Reducer 6 <- Union 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_271]
+ Limit [LIM_270] (rows=24 width=437)
+ Number of rows:100
+ Select Operator [SEL_269] (rows=24 width=437)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 6 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_268]
+ Select Operator [SEL_267] (rows=24 width=437)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_266] (rows=24 width=445)
+ Output:["_col0","_col1","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Union 5 [SIMPLE_EDGE]
+ <-Reducer 14 [CONTAINS]
+ Reduce Output Operator [RS_230]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_229] (rows=58 width=445)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L
+ Top N Key Operator [TNK_228] (rows=39 width=435)
+ keys:_col0, _col1, 0L,sort order:+++,top n:100
+ Select Operator [SEL_226] (rows=2 width=439)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Merge Join Operator [MERGEJOIN_225] (rows=2 width=452)
+ Conds:(Inner),Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 13 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_278]
+ Group By Operator [GBY_277] (rows=2 width=228)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_45]
+ PartitionCols:_col0
+ Group By Operator [GBY_44] (rows=56 width=227)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)","sum(_col3)"],keys:_col1
+ Merge Join Operator [MERGEJOIN_212] (rows=31836679 width=222)
+ Conds:RS_276._col0=RS_242._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_242]
+ PartitionCols:_col0
+ Select Operator [SEL_238] (rows=8116 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_237] (rows=8116 width=98)
+ predicate:CAST( d_date AS TIMESTAMP) BETWEEN TIMESTAMP'1998-08-04 00:00:00' AND TIMESTAMP'1998-09-03 00:00:00'
+ TableScan [TS_3] (rows=73049 width=98)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_date"]
+ <-Map 25 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_276]
+ PartitionCols:_col0
+ Select Operator [SEL_275] (rows=286549727 width=231)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_274] (rows=286549727 width=231)
+ predicate:((cs_sold_date_sk BETWEEN DynamicValue(RS_41_date_dim_d_date_sk_min) AND DynamicValue(RS_41_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_41_date_dim_d_date_sk_bloom_filter))) and cs_sold_date_sk is not null)
+ TableScan [TS_34] (rows=287989836 width=231)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_call_center_sk","cs_ext_sales_price","cs_net_profit"]
+ <-Reducer 15 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_273]
+ Group By Operator [GBY_272] (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_252]
+ Group By Operator [GBY_249] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_243] (rows=8116 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_238]
+ <-Reducer 17 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_283]
+ Group By Operator [GBY_282] (rows=1 width=224)
+ Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"]
+ <-Reducer 16 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_59]
+ Group By Operator [GBY_58] (rows=1 width=224)
+ Output:["_col0","_col1"],aggregations:["sum(_col1)","sum(_col2)"]
+ Merge Join Operator [MERGEJOIN_213] (rows=3199657 width=183)
+ Conds:RS_281._col0=RS_244._col0(Inner),Output:["_col1","_col2"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_244]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_238]
+ <-Map 26 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_281]
+ PartitionCols:_col0
+ Select Operator [SEL_280] (rows=28798881 width=223)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_279] (rows=28798881 width=223)
+ predicate:cr_returned_date_sk is not null
+ TableScan [TS_48] (rows=28798881 width=223)
+ default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["cr_returned_date_sk","cr_return_amount","cr_net_loss"]
+ <-Reducer 20 [CONTAINS]
+ Reduce Output Operator [RS_236]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_235] (rows=58 width=445)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L
+ Top N Key Operator [TNK_234] (rows=39 width=435)
+ keys:_col0, _col1, 0L,sort order:+++,top n:100
+ Select Operator [SEL_232] (rows=25 width=435)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Merge Join Operator [MERGEJOIN_231] (rows=25 width=452)
+ Conds:RS_290._col0=RS_295._col0(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5"]
+ <-Reducer 19 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_290]
+ PartitionCols:_col0
+ Group By Operator [GBY_289] (rows=24 width=228)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0
+ <-Reducer 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_79]
+ PartitionCols:_col0
+ Group By Operator [GBY_78] (rows=360 width=228)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0
+ Select Operator [SEL_76] (rows=15991254 width=227)
+ Output:["_col0","_col1","_col2"]
+ Merge Join Operator [MERGEJOIN_214] (rows=15991254 width=227)
+ Conds:RS_288._col0=RS_245._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_245]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_238]
+ <-Map 27 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_288]
+ PartitionCols:_col0
+ Select Operator [SEL_287] (rows=143931136 width=231)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_286] (rows=143931136 width=231)
+ predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_74_date_dim_d_date_sk_min) AND DynamicValue(RS_74_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_74_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null and ws_web_page_sk is not null)
+ TableScan [TS_67] (rows=144002668 width=231)
+ default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_web_page_sk","ws_ext_sales_price","ws_net_profit"]
+ <-Reducer 21 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_285]
+ Group By Operator [GBY_284] (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_253]
+ Group By Operator [GBY_250] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_246] (rows=8116 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_238]
+ <-Reducer 23 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_295]
+ PartitionCols:_col0
+ Group By Operator [GBY_294] (rows=24 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0
+ <-Reducer 22 [SIMPLE_EDGE]
+ SHUFFLE [RS_94]
+ PartitionCols:_col0
+ Group By Operator [GBY_93] (rows=24 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0
+ Select Operator [SEL_91] (rows=1458758 width=135)
+ Output:["_col0","_col1","_col2"]
+ Merge Join Operator [MERGEJOIN_215] (rows=1458758 width=135)
+ Conds:RS_293._col0=RS_247._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_247]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_238]
+ <-Map 28 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_293]
+ PartitionCols:_col0
+ Select Operator [SEL_292] (rows=13129719 width=221)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_291] (rows=13129719 width=221)
+ predicate:(wr_returned_date_sk is not null and wr_web_page_sk is not null)
+ TableScan [TS_82] (rows=14398467 width=221)
+ default@web_returns,web_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["wr_returned_date_sk","wr_web_page_sk","wr_return_amt","wr_net_loss"]
+ <-Reducer 4 [CONTAINS]
+ Reduce Output Operator [RS_224]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_223] (rows=58 width=445)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L
+ Top N Key Operator [TNK_222] (rows=39 width=435)
+ keys:_col0, _col1, 0L,sort order:+++,top n:100
+ Select Operator [SEL_220] (rows=12 width=436)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Merge Join Operator [MERGEJOIN_219] (rows=12 width=451)
+ Conds:RS_260._col0=RS_265._col0(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5"]
+ <-Reducer 11 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_265]
+ PartitionCols:_col0
+ Group By Operator [GBY_264] (rows=11 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0
+ <-Reducer 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ PartitionCols:_col0
+ Group By Operator [GBY_26] (rows=44 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0
+ Select Operator [SEL_24] (rows=5959021 width=156)
+ Output:["_col0","_col1","_col2"]
+ Merge Join Operator [MERGEJOIN_211] (rows=5959021 width=156)
+ Conds:RS_263._col0=RS_241._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_241]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_238]
+ <-Map 24 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_263]
+ PartitionCols:_col0
+ Select Operator [SEL_262] (rows=53634860 width=223)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_261] (rows=53634860 width=223)
+ predicate:(sr_returned_date_sk is not null and sr_store_sk is not null)
+ TableScan [TS_15] (rows=57591150 width=223)
+ default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_returned_date_sk","sr_store_sk","sr_return_amt","sr_net_loss"]
+ <-Reducer 3 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_260]
+ PartitionCols:_col0
+ Group By Operator [GBY_259] (rows=11 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Group By Operator [GBY_11] (rows=341 width=226)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0
+ Select Operator [SEL_9] (rows=58365993 width=135)
+ Output:["_col0","_col1","_col2"]
+ Merge Join Operator [MERGEJOIN_210] (rows=58365993 width=135)
+ Conds:RS_258._col0=RS_239._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_239]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_238]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_258]
+ PartitionCols:_col0
+ Select Operator [SEL_257] (rows=525329897 width=221)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_256] (rows=525329897 width=221)
+ predicate:((ss_sold_date_sk BETWEEN DynamicValue(RS_7_date_dim_d_date_sk_min) AND DynamicValue(RS_7_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_7_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=221)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_255]
+ Group By Operator [GBY_254] (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_251]
+ Group By Operator [GBY_248] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_240] (rows=8116 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_238]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out
new file mode 100644
index 0000000..ef4d6db
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out
@@ -0,0 +1,345 @@
+PREHOOK: query: explain
+with ws as
+ (select d_year AS ws_sold_year, ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ from web_sales
+ left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+ join date_dim on ws_sold_date_sk = d_date_sk
+ where wr_order_number is null
+ group by d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+cs as
+ (select d_year AS cs_sold_year, cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ from catalog_sales
+ left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+ join date_dim on cs_sold_date_sk = d_date_sk
+ where cr_order_number is null
+ group by d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ss as
+ (select d_year AS ss_sold_year, ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ from store_sales
+ left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+ join date_dim on ss_sold_date_sk = d_date_sk
+ where sr_ticket_number is null
+ group by d_year, ss_item_sk, ss_customer_sk
+ )
+ select
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
+where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000
+order by
+ ss_sold_year, ss_item_sk, ss_customer_sk,
+ ss_qty desc, ss_wc desc, ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_returns
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store_returns
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_returns
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+with ws as
+ (select d_year AS ws_sold_year, ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ from web_sales
+ left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+ join date_dim on ws_sold_date_sk = d_date_sk
+ where wr_order_number is null
+ group by d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+cs as
+ (select d_year AS cs_sold_year, cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ from catalog_sales
+ left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+ join date_dim on cs_sold_date_sk = d_date_sk
+ where cr_order_number is null
+ group by d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ss as
+ (select d_year AS ss_sold_year, ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ from store_sales
+ left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+ join date_dim on ss_sold_date_sk = d_date_sk
+ where sr_ticket_number is null
+ group by d_year, ss_item_sk, ss_customer_sk
+ )
+ select
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
+where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000
+order by
+ ss_sold_year, ss_item_sk, ss_customer_sk,
+ ss_qty desc, ss_wc desc, ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_returns
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store_returns
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_returns
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 14 <- Reducer 7 (BROADCAST_EDGE)
+Map 17 <- Reducer 10 (BROADCAST_EDGE)
+Map 20 <- Reducer 13 (BROADCAST_EDGE)
+Reducer 10 <- Map 1 (CUSTOM_SIMPLE_EDGE)
+Reducer 11 <- Map 1 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 11 (SIMPLE_EDGE)
+Reducer 13 <- Map 1 (CUSTOM_SIMPLE_EDGE)
+Reducer 15 <- Map 14 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE)
+Reducer 18 <- Map 17 (SIMPLE_EDGE), Map 19 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE)
+Reducer 21 <- Map 20 (SIMPLE_EDGE), Map 22 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (ONE_TO_ONE_EDGE), Reducer 9 (ONE_TO_ONE_EDGE)
+Reducer 5 <- Reducer 12 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Map 1 (CUSTOM_SIMPLE_EDGE)
+Reducer 8 <- Map 1 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 6 vectorized
+ File Output Operator [FS_238]
+ Limit [LIM_237] (rows=100 width=484)
+ Number of rows:100
+ Select Operator [SEL_236] (rows=462576000046 width=483)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_73]
+ Select Operator [SEL_72] (rows=462576000046 width=719)
+ Output:["_col0","_col1","_col6","_col7","_col8","_col9","_col10","_col11","_col12"]
+ Filter Operator [FIL_71] (rows=462576000046 width=702)
+ predicate:CASE WHEN (_col11 is not null) THEN ((_col11 > 0L)) ELSE (false) END
+ Merge Join Operator [MERGEJOIN_191] (rows=925152000093 width=702)
+ Conds:RS_68._col1=RS_235._col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9","_col11","_col12","_col13"]
+ <-Reducer 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_235]
+ PartitionCols:_col0
+ Select Operator [SEL_234] (rows=101592102 width=235)
+ Output:["_col0","_col1","_col2","_col3"]
+ Group By Operator [GBY_233] (rows=101592102 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_65]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_64] (rows=101592102 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col2, _col3
+ Merge Join Operator [MERGEJOIN_189] (rows=101592102 width=233)
+ Conds:RS_198._col0=RS_61._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_198]
+ PartitionCols:_col0
+ Select Operator [SEL_193] (rows=652 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_192] (rows=652 width=8)
+ predicate:(d_year = 2000)
+ TableScan [TS_0] (rows=73049 width=8)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"]
+ <-Reducer 21 [SIMPLE_EDGE]
+ SHUFFLE [RS_61]
+ PartitionCols:_col0
+ Select Operator [SEL_59] (rows=286549727 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_58] (rows=286549727 width=240)
+ predicate:_col8 is null
+ Merge Join Operator [MERGEJOIN_188] (rows=468719906 width=240)
+ Conds:RS_230._col2, _col3=RS_232._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+ <-Map 20 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_230]
+ PartitionCols:_col2, _col3
+ Select Operator [SEL_229] (rows=286549727 width=242)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Filter Operator [FIL_228] (rows=286549727 width=242)
+ predicate:((cs_sold_date_sk BETWEEN DynamicValue(RS_60_date_dim_d_date_sk_min) AND DynamicValue(RS_60_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_60_date_dim_d_date_sk_bloom_filter))) and cs_sold_date_sk is not null)
+ TableScan [TS_50] (rows=287989836 width=242)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk","cs_order_number","cs_quantity","cs_wholesale_cost","cs_sales_price"]
+ <-Reducer 13 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_227]
+ Group By Operator [GBY_226] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_205]
+ Group By Operator [GBY_202] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_199] (rows=652 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_193]
+ <-Map 22 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_232]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_231] (rows=28798881 width=8)
+ Output:["_col0","_col1"]
+ TableScan [TS_53] (rows=28798881 width=8)
+ default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["cr_item_sk","cr_order_number"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_68]
+ PartitionCols:_col1
+ Filter Operator [FIL_45] (rows=4580227799 width=471)
+ predicate:CASE WHEN (_col7 is not null) THEN ((_col7 > 0L)) ELSE (false) END
+ Merge Join Operator [MERGEJOIN_190] (rows=9160455599 width=471)
+ Conds:RS_215._col1, _col0=RS_225._col1, _col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9"]
+ <-Reducer 3 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_215]
+ PartitionCols:_col1, _col0
+ Select Operator [SEL_214] (rows=114214965 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_213] (rows=114214965 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_17] (rows=114214965 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col3, _col2
+ Merge Join Operator [MERGEJOIN_185] (rows=114214965 width=217)
+ Conds:RS_194._col0=RS_14._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_194]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_193]
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_14]
+ PartitionCols:_col0
+ Select Operator [SEL_12] (rows=319876350 width=233)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_11] (rows=319876350 width=235)
+ predicate:_col8 is null
+ Merge Join Operator [MERGEJOIN_184] (rows=883006376 width=235)
+ Conds:RS_210._col1, _col3=RS_212._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+ <-Map 14 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_210]
+ PartitionCols:_col1, _col3
+ Select Operator [SEL_209] (rows=550076554 width=233)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Filter Operator [FIL_208] (rows=550076554 width=233)
+ predicate:((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)
+ TableScan [TS_3] (rows=575995635 width=233)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_ticket_number","ss_quantity","ss_wholesale_cost","ss_sales_price"]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_207]
+ Group By Operator [GBY_206] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_203]
+ Group By Operator [GBY_200] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_195] (rows=652 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_193]
+ <-Map 16 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_212]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_211] (rows=57591150 width=8)
+ Output:["_col0","_col1"]
+ TableScan [TS_6] (rows=57591150 width=8)
+ default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_item_sk","sr_ticket_number"]
+ <-Reducer 9 [ONE_TO_ONE_EDGE] vectorized
+ FORWARD [RS_225]
+ PartitionCols:_col1, _col0
+ Select Operator [SEL_224] (rows=40539971 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_223] (rows=40539971 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+ <-Reducer 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_39]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_38] (rows=40539971 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col3, _col2
+ Merge Join Operator [MERGEJOIN_187] (rows=40539971 width=235)
+ Conds:RS_196._col0=RS_35._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_196]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_193]
+ <-Reducer 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_35]
+ PartitionCols:_col0
+ Select Operator [SEL_33] (rows=113538342 width=239)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_32] (rows=113538342 width=242)
+ predicate:_col8 is null
+ Merge Join Operator [MERGEJOIN_186] (rows=254679677 width=242)
+ Conds:RS_220._col1, _col3=RS_222._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+ <-Map 17 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_220]
+ PartitionCols:_col1, _col3
+ Select Operator [SEL_219] (rows=143966864 width=243)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Filter Operator [FIL_218] (rows=143966864 width=243)
+ predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_34_date_dim_d_date_sk_min) AND DynamicValue(RS_34_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_34_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null)
+ TableScan [TS_24] (rows=144002668 width=243)
+ default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_item_sk","ws_bill_customer_sk","ws_order_number","ws_quantity","ws_wholesale_cost","ws_sales_price"]
+ <-Reducer 10 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_217]
+ Group By Operator [GBY_216] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_204]
+ Group By Operator [GBY_201] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_197] (rows=652 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_193]
+ <-Map 19 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_222]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_221] (rows=14398467 width=8)
+ Output:["_col0","_col1"]
+ TableScan [TS_27] (rows=14398467 width=8)
+ default@web_returns,web_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["wr_item_sk","wr_order_number"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out
new file mode 100644
index 0000000..fb94428
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out
@@ -0,0 +1,175 @@
+PREHOOK: query: explain
+select
+ c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+ from
+ (select ss_ticket_number
+ ,ss_customer_sk
+ ,store.s_city
+ ,sum(ss_coupon_amt) amt
+ ,sum(ss_net_profit) profit
+ from store_sales,date_dim,store,household_demographics
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+ and date_dim.d_dow = 1
+ and date_dim.d_year in (1998,1998+1,1998+2)
+ and store.s_number_employees between 200 and 295
+ group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+ where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,substr(s_city,1,30), profit
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@household_demographics
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+select
+ c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+ from
+ (select ss_ticket_number
+ ,ss_customer_sk
+ ,store.s_city
+ ,sum(ss_coupon_amt) amt
+ ,sum(ss_net_profit) profit
+ from store_sales,date_dim,store,household_demographics
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+ and date_dim.d_dow = 1
+ and date_dim.d_year in (1998,1998+1,1998+2)
+ and store.s_number_employees between 200 and 295
+ group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+ where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,substr(s_city,1,30), profit
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@household_demographics
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 4 <- Reducer 10 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE)
+Reducer 10 <- Map 9 (CUSTOM_SIMPLE_EDGE)
+Reducer 13 <- Map 12 (CUSTOM_SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 5 <- Map 4 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
+Reducer 6 <- Map 11 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Map 12 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 3 vectorized
+ File Output Operator [FS_129]
+ Limit [LIM_128] (rows=100 width=776)
+ Number of rows:100
+ Select Operator [SEL_127] (rows=43530621 width=776)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_32]
+ Select Operator [SEL_31] (rows=43530621 width=776)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Merge Join Operator [MERGEJOIN_99] (rows=43530621 width=685)
+ Conds:RS_101._col0=RS_126._col1(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7","_col8"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_101]
+ PartitionCols:_col0
+ Select Operator [SEL_100] (rows=80000000 width=184)
+ Output:["_col0","_col1","_col2"]
+ TableScan [TS_0] (rows=80000000 width=184)
+ default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_first_name","c_last_name"]
+ <-Reducer 8 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_126]
+ PartitionCols:_col1
+ Select Operator [SEL_125] (rows=43530621 width=507)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Group By Operator [GBY_124] (rows=43530621 width=325)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3
+ <-Reducer 7 [SIMPLE_EDGE]
+ SHUFFLE [RS_25]
+ PartitionCols:_col0, _col1, _col2, _col3
+ Group By Operator [GBY_24] (rows=43530621 width=325)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col6)","sum(_col7)"],keys:_col1, _col3, _col5, _col10
+ Merge Join Operator [MERGEJOIN_98] (rows=43530621 width=214)
+ Conds:RS_20._col2=RS_112._col0(Inner),Output:["_col1","_col3","_col5","_col6","_col7","_col10"]
+ <-Map 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_112]
+ PartitionCols:_col0
+ Select Operator [SEL_111] (rows=3055 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_110] (rows=3055 width=12)
+ predicate:((hd_dep_count = 8) or (hd_vehicle_count > 0))
+ TableScan [TS_11] (rows=7200 width=12)
+ default@household_demographics,household_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["hd_demo_sk","hd_dep_count","hd_vehicle_count"]
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_97] (rows=102592623 width=283)
+ Conds:RS_17._col4=RS_123._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7","_col10"]
+ <-Map 11 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_123]
+ PartitionCols:_col0
+ Select Operator [SEL_122] (rows=1704 width=97)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_121] (rows=1704 width=100)
+ predicate:s_number_employees BETWEEN 200 AND 295
+ TableScan [TS_8] (rows=1704 width=100)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_number_employees","s_city"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col4
+ Merge Join Operator [MERGEJOIN_96] (rows=102592623 width=193)
+ Conds:RS_120._col0=RS_104._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 9 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_104]
+ PartitionCols:_col0
+ Select Operator [SEL_103] (rows=391 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_102] (rows=391 width=12)
+ predicate:((d_dow = 1) and (d_year) IN (1998, 1999, 2000))
+ TableScan [TS_5] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_dow"]
+ <-Map 4 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_120]
+ PartitionCols:_col0
+ Select Operator [SEL_119] (rows=479121995 width=237)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Filter Operator [FIL_118] (rows=479121995 width=237)
+ predicate:((ss_hdemo_sk BETWEEN DynamicValue(RS_21_household_demographics_hd_demo_sk_min) AND DynamicValue(RS_21_household_demographics_hd_demo_sk_max) and in_bloom_filter(ss_hdemo_sk, DynamicValue(RS_21_household_demographics_hd_demo_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_15_date_dim_d_date_sk_min) AND DynamicValue(RS_15_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_15_date_dim_d_date_sk_bloom_filter))) and ss_customer_sk is not null and ss_hdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_2] (rows=575995635 width=237)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_customer_sk","ss_hdemo_sk","ss_addr_sk","ss_store_sk","ss_ticket_number","ss_coupon_amt","ss_net_profit"]
+ <-Reducer 10 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_109]
+ Group By Operator [GBY_108] (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_107]
+ Group By Operator [GBY_106] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_105] (rows=391 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_103]
+ <-Reducer 13 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_117]
+ Group By Operator [GBY_116] (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_115]
+ Group By Operator [GBY_114] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_113] (rows=3055 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_111]
+