http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query5.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query5.q.out b/ql/src/test/results/clientpositive/perf/query5.q.out deleted file mode 100644 index ff197d2..0000000 --- a/ql/src/test/results/clientpositive/perf/query5.q.out +++ /dev/null @@ -1,473 +0,0 @@ -PREHOOK: query: explain -with ssr as - (select s_store_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select ss_store_sk as store_sk, - ss_sold_date_sk as date_sk, - ss_ext_sales_price as sales_price, - ss_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from store_sales - union all - select sr_store_sk as store_sk, - sr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - sr_return_amt as return_amt, - sr_net_loss as net_loss - from store_returns - ) salesreturns, - date_dim, - store - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and store_sk = s_store_sk - group by s_store_id) - , - csr as - (select cp_catalog_page_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select cs_catalog_page_sk as page_sk, - cs_sold_date_sk as date_sk, - cs_ext_sales_price as sales_price, - cs_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from catalog_sales - union all - select cr_catalog_page_sk as page_sk, - cr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - cr_return_amount as return_amt, - cr_net_loss as net_loss - from catalog_returns - ) salesreturns, - date_dim, - catalog_page - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and page_sk = cp_catalog_page_sk - group by cp_catalog_page_id) - , - wsr as - (select web_site_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select ws_web_site_sk as wsr_web_site_sk, - ws_sold_date_sk as date_sk, - ws_ext_sales_price as sales_price, - ws_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from web_sales - union all - select ws_web_site_sk as wsr_web_site_sk, - wr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - wr_return_amt as return_amt, - wr_net_loss as net_loss - from web_returns left outer join web_sales on - ( wr_item_sk = ws_item_sk - and wr_order_number = ws_order_number) - ) salesreturns, - date_dim, - web_site - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and wsr_web_site_sk = web_site_sk - group by web_site_id) - select channel - , id - , sum(sales) as sales - , sum(returns) as returns - , sum(profit) as profit - from - (select 'store channel' as channel - , 'store' || s_store_id as id - , sales - , returns - , (profit - profit_loss) as profit - from ssr - union all - select 'catalog channel' as channel - , 'catalog_page' || cp_catalog_page_id as id - , sales - , returns - , (profit - profit_loss) as profit - from csr - union all - select 'web channel' as channel - , 'web_site' || web_site_id as id - , sales - , returns - , (profit - profit_loss) as profit - from wsr - ) x - group by rollup (channel, id) - order by channel - ,id - limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with ssr as - (select s_store_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select ss_store_sk as store_sk, - ss_sold_date_sk as date_sk, - ss_ext_sales_price as sales_price, - ss_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from store_sales - union all - select sr_store_sk as store_sk, - sr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - sr_return_amt as return_amt, - sr_net_loss as net_loss - from store_returns - ) salesreturns, - date_dim, - store - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and store_sk = s_store_sk - group by s_store_id) - , - csr as - (select cp_catalog_page_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select cs_catalog_page_sk as page_sk, - cs_sold_date_sk as date_sk, - cs_ext_sales_price as sales_price, - cs_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from catalog_sales - union all - select cr_catalog_page_sk as page_sk, - cr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - cr_return_amount as return_amt, - cr_net_loss as net_loss - from catalog_returns - ) salesreturns, - date_dim, - catalog_page - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and page_sk = cp_catalog_page_sk - group by cp_catalog_page_id) - , - wsr as - (select web_site_id, - sum(sales_price) as sales, - sum(profit) as profit, - sum(return_amt) as returns, - sum(net_loss) as profit_loss - from - ( select ws_web_site_sk as wsr_web_site_sk, - ws_sold_date_sk as date_sk, - ws_ext_sales_price as sales_price, - ws_net_profit as profit, - cast(0 as decimal(7,2)) as return_amt, - cast(0 as decimal(7,2)) as net_loss - from web_sales - union all - select ws_web_site_sk as wsr_web_site_sk, - wr_returned_date_sk as date_sk, - cast(0 as decimal(7,2)) as sales_price, - cast(0 as decimal(7,2)) as profit, - wr_return_amt as return_amt, - wr_net_loss as net_loss - from web_returns left outer join web_sales on - ( wr_item_sk = ws_item_sk - and wr_order_number = ws_order_number) - ) salesreturns, - date_dim, - web_site - where date_sk = d_date_sk - and d_date between cast('1998-08-04' as date) - and (cast('1998-08-04' as date) + 14 days) - and wsr_web_site_sk = web_site_sk - group by web_site_id) - select channel - , id - , sum(sales) as sales - , sum(returns) as returns - , sum(profit) as profit - from - (select 'store channel' as channel - , 'store' || s_store_id as id - , sales - , returns - , (profit - profit_loss) as profit - from ssr - union all - select 'catalog channel' as channel - , 'catalog_page' || cp_catalog_page_id as id - , sales - , returns - , (profit - profit_loss) as profit - from csr - union all - select 'web channel' as channel - , 'web_site' || web_site_id as id - , sales - , returns - , (profit - profit_loss) as profit - from wsr - ) x - group by rollup (channel, id) - order by channel - ,id - limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Map 1 <- Union 2 (CONTAINS) -Map 18 <- Union 19 (CONTAINS) -Map 20 <- Union 19 (CONTAINS) -Map 22 <- Union 23 (CONTAINS) -Map 9 <- Union 2 (CONTAINS) -Reducer 11 <- Map 10 (SIMPLE_EDGE), Union 19 (SIMPLE_EDGE) -Reducer 12 <- Map 21 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE) -Reducer 13 <- Reducer 12 (SIMPLE_EDGE), Union 6 (CONTAINS) -Reducer 14 <- Map 10 (SIMPLE_EDGE), Union 23 (SIMPLE_EDGE) -Reducer 15 <- Map 27 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE) -Reducer 16 <- Reducer 15 (SIMPLE_EDGE), Union 6 (CONTAINS) -Reducer 25 <- Map 24 (SIMPLE_EDGE), Map 26 (SIMPLE_EDGE), Union 23 (CONTAINS) -Reducer 3 <- Map 10 (SIMPLE_EDGE), Union 2 (SIMPLE_EDGE) -Reducer 4 <- Map 17 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (SIMPLE_EDGE), Union 6 (CONTAINS) -Reducer 7 <- Union 6 (SIMPLE_EDGE) -Reducer 8 <- Reducer 7 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 8 - File Output Operator [FS_92] - Limit [LIM_91] (rows=100 width=110) - Number of rows:100 - Select Operator [SEL_90] (rows=1136898901 width=110) - Output:["_col0","_col1","_col2","_col3","_col4"] - <-Reducer 7 [SIMPLE_EDGE] - SHUFFLE [RS_89] - Select Operator [SEL_88] (rows=1136898901 width=110) - Output:["_col0","_col1","_col2","_col3","_col4"] - Group By Operator [GBY_87] (rows=1136898901 width=110) - Output:["_col0","_col1","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2 - <-Union 6 [SIMPLE_EDGE] - <-Reducer 13 [CONTAINS] - Reduce Output Operator [RS_86] - PartitionCols:_col0, _col1, _col2 - Group By Operator [GBY_85] (rows=2273797803 width=110) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0 - Select Operator [SEL_49] (rows=191657181 width=132) - Output:["_col0","_col1","_col2","_col3","_col4"] - Group By Operator [GBY_48] (rows=191657181 width=132) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0 - <-Reducer 12 [SIMPLE_EDGE] - SHUFFLE [RS_47] - PartitionCols:_col0 - Group By Operator [GBY_46] (rows=383314363 width=132) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9 - Merge Join Operator [MERGEJOIN_135] (rows=383314363 width=132) - Conds:RS_42._col0=RS_43._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"] - <-Map 21 [SIMPLE_EDGE] - SHUFFLE [RS_43] - PartitionCols:_col0 - Select Operator [SEL_38] (rows=46000 width=460) - Output:["_col0","_col1"] - Filter Operator [FIL_125] (rows=46000 width=460) - predicate:cp_catalog_page_sk is not null - TableScan [TS_36] (rows=46000 width=460) - default@catalog_page,catalog_page,Tbl:COMPLETE,Col:NONE,Output:["cp_catalog_page_sk","cp_catalog_page_id"] - <-Reducer 11 [SIMPLE_EDGE] - SHUFFLE [RS_42] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_134] (rows=348467596 width=132) - Conds:Union 19._col1=RS_40._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_40] - PartitionCols:_col0 - Select Operator [SEL_10] (rows=8116 width=1119) - Output:["_col0"] - Filter Operator [FIL_120] (rows=8116 width=1119) - predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 1998-08-04 00:00:00.0 AND 1998-08-18 00:00:00.0 and d_date_sk is not null) - TableScan [TS_8] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"] - <-Union 19 [SIMPLE_EDGE] - <-Map 18 [CONTAINS] - Reduce Output Operator [RS_39] - PartitionCols:_col1 - Select Operator [SEL_27] (rows=287989836 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_122] (rows=287989836 width=135) - predicate:(cs_catalog_page_sk is not null and cs_sold_date_sk is not null) - TableScan [TS_25] (rows=287989836 width=135) - Output:["cs_sold_date_sk","cs_catalog_page_sk","cs_ext_sales_price","cs_net_profit"] - <-Map 20 [CONTAINS] - Reduce Output Operator [RS_39] - PartitionCols:_col1 - Select Operator [SEL_30] (rows=28798881 width=106) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_123] (rows=28798881 width=106) - predicate:(cr_catalog_page_sk is not null and cr_returned_date_sk is not null) - TableScan [TS_28] (rows=28798881 width=106) - Output:["cr_returned_date_sk","cr_catalog_page_sk","cr_return_amount","cr_net_loss"] - <-Reducer 16 [CONTAINS] - Reduce Output Operator [RS_86] - PartitionCols:_col0, _col1, _col2 - Group By Operator [GBY_85] (rows=2273797803 width=110) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0 - Select Operator [SEL_82] (rows=182955399 width=135) - Output:["_col0","_col1","_col2","_col3","_col4"] - Group By Operator [GBY_81] (rows=182955399 width=135) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0 - <-Reducer 15 [SIMPLE_EDGE] - SHUFFLE [RS_80] - PartitionCols:_col0 - Group By Operator [GBY_79] (rows=365910798 width=135) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9 - Merge Join Operator [MERGEJOIN_137] (rows=365910798 width=135) - Conds:RS_75._col0=RS_76._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"] - <-Map 27 [SIMPLE_EDGE] - SHUFFLE [RS_76] - PartitionCols:_col0 - Select Operator [SEL_71] (rows=84 width=1850) - Output:["_col0","_col1"] - Filter Operator [FIL_130] (rows=84 width=1850) - predicate:web_site_sk is not null - TableScan [TS_69] (rows=84 width=1850) - default@web_site,web_site,Tbl:COMPLETE,Col:NONE,Output:["web_site_sk","web_site_id"] - <-Reducer 14 [SIMPLE_EDGE] - SHUFFLE [RS_75] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_136] (rows=332646173 width=135) - Conds:Union 23._col1=RS_73._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_73] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_10] - <-Union 23 [SIMPLE_EDGE] - <-Map 22 [CONTAINS] - Reduce Output Operator [RS_72] - PartitionCols:_col1 - Select Operator [SEL_53] (rows=144002668 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_126] (rows=144002668 width=135) - predicate:(ws_sold_date_sk is not null and ws_web_site_sk is not null) - TableScan [TS_51] (rows=144002668 width=135) - Output:["ws_sold_date_sk","ws_web_site_sk","ws_ext_sales_price","ws_net_profit"] - <-Reducer 25 [CONTAINS] - Reduce Output Operator [RS_72] - PartitionCols:_col1 - Select Operator [SEL_63] (rows=158402938 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Merge Join Operator [MERGEJOIN_131] (rows=158402938 width=135) - Conds:RS_60._col0, _col2=RS_61._col1, _col2(Inner),Output:["_col1","_col3","_col6","_col7"] - <-Map 24 [SIMPLE_EDGE] - SHUFFLE [RS_60] - PartitionCols:_col0, _col2 - Select Operator [SEL_56] (rows=144002668 width=135) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_127] (rows=144002668 width=135) - predicate:(ws_item_sk is not null and ws_order_number is not null and ws_web_site_sk is not null) - TableScan [TS_54] (rows=144002668 width=135) - default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_item_sk","ws_web_site_sk","ws_order_number"] - <-Map 26 [SIMPLE_EDGE] - SHUFFLE [RS_61] - PartitionCols:_col1, _col2 - Select Operator [SEL_59] (rows=14398467 width=92) - Output:["_col0","_col1","_col2","_col3","_col4"] - Filter Operator [FIL_128] (rows=14398467 width=92) - predicate:(wr_item_sk is not null and wr_order_number is not null and wr_returned_date_sk is not null) - TableScan [TS_57] (rows=14398467 width=92) - default@web_returns,web_returns,Tbl:COMPLETE,Col:NONE,Output:["wr_returned_date_sk","wr_item_sk","wr_order_number","wr_return_amt","wr_net_loss"] - <-Reducer 5 [CONTAINS] - Reduce Output Operator [RS_86] - PartitionCols:_col0, _col1, _col2 - Group By Operator [GBY_85] (rows=2273797803 width=110) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0 - Select Operator [SEL_24] (rows=383320021 width=87) - Output:["_col0","_col1","_col2","_col3","_col4"] - Group By Operator [GBY_23] (rows=383320021 width=87) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0 - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_22] - PartitionCols:_col0 - Group By Operator [GBY_21] (rows=766640042 width=87) - Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9 - Merge Join Operator [MERGEJOIN_133] (rows=766640042 width=87) - Conds:RS_17._col0=RS_18._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"] - <-Map 17 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col0 - Select Operator [SEL_13] (rows=1704 width=1910) - Output:["_col0","_col1"] - Filter Operator [FIL_121] (rows=1704 width=1910) - predicate:s_store_sk is not null - TableScan [TS_11] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_id"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_17] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_132] (rows=696945478 width=87) - Conds:Union 2._col1=RS_15._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_15] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_10] - <-Union 2 [SIMPLE_EDGE] - <-Map 1 [CONTAINS] - Reduce Output Operator [RS_14] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_118] (rows=575995635 width=88) - predicate:(ss_sold_date_sk is not null and ss_store_sk is not null) - TableScan [TS_0] (rows=575995635 width=88) - Output:["ss_sold_date_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"] - <-Map 9 [CONTAINS] - Reduce Output Operator [RS_14] - PartitionCols:_col1 - Select Operator [SEL_5] (rows=57591150 width=77) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_119] (rows=57591150 width=77) - predicate:(sr_returned_date_sk is not null and sr_store_sk is not null) - TableScan [TS_3] (rows=57591150 width=77) - Output:["sr_returned_date_sk","sr_store_sk","sr_return_amt","sr_net_loss"] -
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query50.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query50.q.out b/ql/src/test/results/clientpositive/perf/query50.q.out deleted file mode 100644 index 02d9447..0000000 --- a/ql/src/test/results/clientpositive/perf/query50.q.out +++ /dev/null @@ -1,210 +0,0 @@ -PREHOOK: query: explain -select - s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and - (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and - (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and - (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as `>120 days` -from - store_sales - ,store_returns - ,store - ,date_dim d1 - ,date_dim d2 -where - d2.d_year = 2000 -and d2.d_moy = 9 -and ss_ticket_number = sr_ticket_number -and ss_item_sk = sr_item_sk -and ss_sold_date_sk = d1.d_date_sk -and sr_returned_date_sk = d2.d_date_sk -and ss_customer_sk = sr_customer_sk -and ss_store_sk = s_store_sk -group by - s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip -order by s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select - s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and - (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and - (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and - (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days` - ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as `>120 days` -from - store_sales - ,store_returns - ,store - ,date_dim d1 - ,date_dim d2 -where - d2.d_year = 2000 -and d2.d_moy = 9 -and ss_ticket_number = sr_ticket_number -and ss_item_sk = sr_item_sk -and ss_sold_date_sk = d1.d_date_sk -and sr_returned_date_sk = d2.d_date_sk -and ss_customer_sk = sr_customer_sk -and ss_store_sk = s_store_sk -group by - s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip -order by s_store_name - ,s_company_id - ,s_street_number - ,s_street_name - ,s_street_type - ,s_suite_number - ,s_city - ,s_county - ,s_state - ,s_zip -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) -Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Map 11 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) -Reducer 7 <- Reducer 6 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 7 - File Output Operator [FS_36] - Limit [LIM_35] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_34] (rows=383325119 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"] - <-Reducer 6 [SIMPLE_EDGE] - SHUFFLE [RS_33] - Group By Operator [GBY_31] (rows=383325119 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8, KEY._col9 - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_30] - PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9 - Group By Operator [GBY_29] (rows=766650239 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"],aggregations:["sum(_col10)","sum(_col11)","sum(_col12)","sum(_col13)","sum(_col14)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9 - Select Operator [SEL_27] (rows=766650239 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"] - Merge Join Operator [MERGEJOIN_59] (rows=766650239 width=88) - Conds:RS_24._col10=RS_25._col0(Inner),Output:["_col0","_col7","_col14","_col15","_col16","_col17","_col18","_col19","_col20","_col21","_col22","_col23"] - <-Map 11 [SIMPLE_EDGE] - SHUFFLE [RS_25] - PartitionCols:_col0 - Select Operator [SEL_14] (rows=1704 width=1910) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"] - Filter Operator [FIL_55] (rows=1704 width=1910) - predicate:s_store_sk is not null - TableScan [TS_12] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_name","s_company_id","s_street_number","s_street_name","s_street_type","s_suite_number","s_city","s_county","s_state","s_zip"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_24] - PartitionCols:_col10 - Merge Join Operator [MERGEJOIN_58] (rows=696954748 width=88) - Conds:RS_21._col7=RS_22._col0(Inner),Output:["_col0","_col7","_col10"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_22] - PartitionCols:_col0 - Select Operator [SEL_11] (rows=73049 width=1119) - Output:["_col0"] - Filter Operator [FIL_54] (rows=73049 width=1119) - predicate:d_date_sk is not null - TableScan [TS_9] (rows=73049 width=1119) - default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_21] - PartitionCols:_col7 - Merge Join Operator [MERGEJOIN_57] (rows=633595212 width=88) - Conds:RS_18._col1, _col2, _col3=RS_19._col1, _col2, _col4(Inner),Output:["_col0","_col7","_col10"] - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col1, _col2, _col4 - Select Operator [SEL_8] (rows=575995635 width=88) - Output:["_col0","_col1","_col2","_col3","_col4"] - Filter Operator [FIL_53] (rows=575995635 width=88) - predicate:(ss_customer_sk is not null and ss_item_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null and ss_ticket_number is not null) - TableScan [TS_6] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col1, _col2, _col3 - Merge Join Operator [MERGEJOIN_56] (rows=63350266 width=77) - Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col0","_col1","_col2","_col3"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_15] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=57591150 width=77) - Output:["_col0","_col1","_col2","_col3"] - Filter Operator [FIL_51] (rows=57591150 width=77) - predicate:(sr_customer_sk is not null and sr_item_sk is not null and sr_returned_date_sk is not null and sr_ticket_number is not null) - TableScan [TS_0] (rows=57591150 width=77) - default@store_returns,store_returns,Tbl:COMPLETE,Col:NONE,Output:["sr_returned_date_sk","sr_item_sk","sr_customer_sk","sr_ticket_number"] - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_16] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=18262 width=1119) - Output:["_col0"] - Filter Operator [FIL_52] (rows=18262 width=1119) - predicate:((d_moy = 9) and (d_year = 2000) and d_date_sk is not null) - TableScan [TS_3] (rows=73049 width=1119) - default@date_dim,d2,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query51.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query51.q.out b/ql/src/test/results/clientpositive/perf/query51.q.out deleted file mode 100644 index 2517306..0000000 --- a/ql/src/test/results/clientpositive/perf/query51.q.out +++ /dev/null @@ -1,188 +0,0 @@ -PREHOOK: query: explain -WITH web_v1 as ( -select - ws_item_sk item_sk, d_date, - sum(sum(ws_sales_price)) - over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales -from web_sales - ,date_dim -where ws_sold_date_sk=d_date_sk - and d_month_seq between 1212 and 1212+11 - and ws_item_sk is not NULL -group by ws_item_sk, d_date), -store_v1 as ( -select - ss_item_sk item_sk, d_date, - sum(sum(ss_sales_price)) - over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales -from store_sales - ,date_dim -where ss_sold_date_sk=d_date_sk - and d_month_seq between 1212 and 1212+11 - and ss_item_sk is not NULL -group by ss_item_sk, d_date) - select * -from (select item_sk - ,d_date - ,web_sales - ,store_sales - ,max(web_sales) - over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative - ,max(store_sales) - over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative - from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk - ,case when web.d_date is not null then web.d_date else store.d_date end d_date - ,web.cume_sales web_sales - ,store.cume_sales store_sales - from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk - and web.d_date = store.d_date) - )x )y -where web_cumulative > store_cumulative -order by item_sk - ,d_date -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -WITH web_v1 as ( -select - ws_item_sk item_sk, d_date, - sum(sum(ws_sales_price)) - over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales -from web_sales - ,date_dim -where ws_sold_date_sk=d_date_sk - and d_month_seq between 1212 and 1212+11 - and ws_item_sk is not NULL -group by ws_item_sk, d_date), -store_v1 as ( -select - ss_item_sk item_sk, d_date, - sum(sum(ss_sales_price)) - over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales -from store_sales - ,date_dim -where ss_sold_date_sk=d_date_sk - and d_month_seq between 1212 and 1212+11 - and ss_item_sk is not NULL -group by ss_item_sk, d_date) - select * -from (select item_sk - ,d_date - ,web_sales - ,store_sales - ,max(web_sales) - over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative - ,max(store_sales) - over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative - from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk - ,case when web.d_date is not null then web.d_date else store.d_date end d_date - ,web.cume_sales web_sales - ,store.cume_sales store_sales - from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk - and web.d_date = store.d_date) - )x )y -where web_cumulative > store_cumulative -order by item_sk - ,d_date -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 3 <- Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) -Reducer 8 <- Map 10 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 9 <- Reducer 8 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 6 - File Output Operator [FS_53] - Limit [LIM_52] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_51] (rows=116159124 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_50] - Select Operator [SEL_46] (rows=116159124 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_58] (rows=116159124 width=88) - predicate:(max_window_0 > max_window_1) - PTF Operator [PTF_45] (rows=348477374 width=88) - Function definitions:[{},{"name:":"windowingtablefunction","order by:":"CASE WHEN (_col4 is not null) THEN (_col4) ELSE (_col1) END ASC NULLS FIRST","partition by:":"CASE WHEN (_col3 is not null) THEN (_col3) ELSE (_col0) END"}] - Select Operator [SEL_44] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_43] - PartitionCols:CASE WHEN (_col3 is not null) THEN (_col3) ELSE (_col0) END - Merge Join Operator [MERGEJOIN_67] (rows=348477374 width=88) - Conds:RS_40._col0, _col1=RS_41._col0, _col1(Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_40] - PartitionCols:_col0, _col1 - Select Operator [SEL_17] (rows=316797606 width=88) - Output:["_col0","_col1","_col2"] - PTF Operator [PTF_16] (rows=316797606 width=88) - Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS FIRST","partition by:":"_col0"}] - Group By Operator [GBY_12] (rows=316797606 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_11] - PartitionCols:_col0 - Group By Operator [GBY_10] (rows=633595212 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4 - Merge Join Operator [MERGEJOIN_65] (rows=633595212 width=88) - Conds:RS_6._col0=RS_7._col0(Inner),Output:["_col1","_col2","_col4"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_7] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=8116 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_60] (rows=8116 width=1119) - predicate:(d_date_sk is not null and d_month_seq BETWEEN 1212 AND 1223) - TableScan [TS_3] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date","d_month_seq"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_6] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_59] (rows=575995635 width=88) - predicate:(ss_item_sk is not null and ss_sold_date_sk is not null) - TableScan [TS_0] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_sales_price"] - <-Reducer 9 [SIMPLE_EDGE] - SHUFFLE [RS_41] - PartitionCols:_col0, _col1 - Select Operator [SEL_37] (rows=79201469 width=135) - Output:["_col0","_col1","_col2"] - PTF Operator [PTF_36] (rows=79201469 width=135) - Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS FIRST","partition by:":"_col0"}] - Group By Operator [GBY_32] (rows=79201469 width=135) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 - <-Reducer 8 [SIMPLE_EDGE] - SHUFFLE [RS_31] - PartitionCols:_col0 - Group By Operator [GBY_30] (rows=158402938 width=135) - Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4 - Merge Join Operator [MERGEJOIN_66] (rows=158402938 width=135) - Conds:RS_26._col0=RS_27._col0(Inner),Output:["_col1","_col2","_col4"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_27] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_5] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_26] - PartitionCols:_col0 - Select Operator [SEL_22] (rows=144002668 width=135) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_61] (rows=144002668 width=135) - predicate:(ws_item_sk is not null and ws_sold_date_sk is not null) - TableScan [TS_20] (rows=144002668 width=135) - default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_sales_price"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query52.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query52.q.out b/ql/src/test/results/clientpositive/perf/query52.q.out deleted file mode 100644 index b14ab53..0000000 --- a/ql/src/test/results/clientpositive/perf/query52.q.out +++ /dev/null @@ -1,106 +0,0 @@ -PREHOOK: query: explain -select dt.d_year - ,item.i_brand_id brand_id - ,item.i_brand brand - ,sum(ss_ext_sales_price) ext_price - from date_dim dt - ,store_sales - ,item - where dt.d_date_sk = store_sales.ss_sold_date_sk - and store_sales.ss_item_sk = item.i_item_sk - and item.i_manager_id = 1 - and dt.d_moy=12 - and dt.d_year=1998 - group by dt.d_year - ,item.i_brand - ,item.i_brand_id - order by dt.d_year - ,ext_price desc - ,brand_id -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select dt.d_year - ,item.i_brand_id brand_id - ,item.i_brand brand - ,sum(ss_ext_sales_price) ext_price - from date_dim dt - ,store_sales - ,item - where dt.d_date_sk = store_sales.ss_sold_date_sk - and store_sales.ss_item_sk = item.i_item_sk - and item.i_manager_id = 1 - and dt.d_moy=12 - and dt.d_year=1998 - group by dt.d_year - ,item.i_brand - ,item.i_brand_id - order by dt.d_year - ,ext_price desc - ,brand_id -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) -Reducer 3 <- Map 7 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:-1 - Stage-1 - Reducer 5 - File Output Operator [FS_25] - Select Operator [SEL_24] (rows=100 width=88) - Output:["_col0","_col1","_col2","_col3"] - Limit [LIM_23] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_22] (rows=348477374 width=88) - Output:["_col0","_col1","_col2"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_21] - Group By Operator [GBY_18] (rows=348477374 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_17] - PartitionCols:_col0, _col1 - Group By Operator [GBY_16] (rows=696954748 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col7, _col8 - Merge Join Operator [MERGEJOIN_35] (rows=696954748 width=88) - Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col7","_col8"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_13] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=231000 width=1436) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_33] (rows=231000 width=1436) - predicate:((i_manager_id = 1) and i_item_sk is not null) - TableScan [TS_6] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_brand_id","i_brand","i_manager_id"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_12] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_34] (rows=633595212 width=88) - Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_9] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_31] (rows=575995635 width=88) - predicate:(ss_item_sk is not null and ss_sold_date_sk is not null) - TableScan [TS_0] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"] - <-Map 6 [SIMPLE_EDGE] - SHUFFLE [RS_10] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=18262 width=1119) - Output:["_col0"] - Filter Operator [FIL_32] (rows=18262 width=1119) - predicate:((d_moy = 12) and (d_year = 1998) and d_date_sk is not null) - TableScan [TS_3] (rows=73049 width=1119) - default@date_dim,dt,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query53.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query53.q.out b/ql/src/test/results/clientpositive/perf/query53.q.out deleted file mode 100644 index a10ee45..0000000 --- a/ql/src/test/results/clientpositive/perf/query53.q.out +++ /dev/null @@ -1,141 +0,0 @@ -PREHOOK: query: explain -select * from -(select i_manufact_id, -sum(ss_sales_price) sum_sales, -avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_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','reference','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_manufact_id, d_qoy ) tmp1 -where case when avg_quarterly_sales > 0 - then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales - else null end > 0.1 -order by avg_quarterly_sales, - sum_sales, - i_manufact_id -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select * from -(select i_manufact_id, -sum(ss_sales_price) sum_sales, -avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_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','reference','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_manufact_id, d_qoy ) tmp1 -where case when avg_quarterly_sales > 0 - then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales - else null end > 0.1 -order by avg_quarterly_sales, - sum_sales, - i_manufact_id -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Map 9 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 6 - File Output Operator [FS_36] - Limit [LIM_35] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_34] (rows=191662559 width=88) - Output:["_col0","_col1","_col2"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_33] - Select Operator [SEL_30] (rows=191662559 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_46] (rows=191662559 width=88) - predicate:CASE WHEN ((avg_window_0 > 0)) THEN (((abs((_col2 - avg_window_0)) / avg_window_0) > 0.1)) ELSE (null) END - Select Operator [SEL_29] (rows=383325119 width=88) - Output:["avg_window_0","_col0","_col2"] - PTF Operator [PTF_28] (rows=383325119 width=88) - Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col0 ASC NULLS FIRST","partition by:":"_col0"}] - Select Operator [SEL_25] (rows=383325119 width=88) - Output:["_col0","_col2"] - Group By Operator [GBY_24] (rows=383325119 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_23] - PartitionCols:_col0 - Group By Operator [GBY_22] (rows=766650239 width=88) - Output:["_col0","_col1","_col2"],aggregations:["sum(_col3)"],keys:_col8, _col11 - Merge Join Operator [MERGEJOIN_54] (rows=766650239 width=88) - Conds:RS_18._col2=RS_19._col0(Inner),Output:["_col3","_col8","_col11"] - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col0 - Select Operator [SEL_11] (rows=1704 width=1910) - Output:["_col0"] - Filter Operator [FIL_50] (rows=1704 width=1910) - predicate:s_store_sk is not null - TableScan [TS_9] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_53] (rows=696954748 width=88) - Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col2","_col3","_col8","_col11"] - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_16] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=36525 width=1119) - Output:["_col0","_col2"] - Filter Operator [FIL_49] (rows=36525 width=1119) - predicate:((d_month_seq) IN (1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223) and d_date_sk is not null) - TableScan [TS_6] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq","d_qoy"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_15] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_52] (rows=633595212 width=88) - Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col0","_col2","_col3","_col8"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_12] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2","_col3"] - Filter Operator [FIL_47] (rows=575995635 width=88) - predicate:(ss_item_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=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_sales_price"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_13] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=115500 width=1436) - Output:["_col0","_col4"] - Filter Operator [FIL_48] (rows=115500 width=1436) - predicate:((((i_category) IN ('Books', 'Children', 'Electronics') and (i_class) IN ('personal', 'portable', 'reference', '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') or (i_brand) IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')) and ((i_category) IN ('Books', 'Children', 'Electronics') or (i_category) IN ('Women', 'Music', 'Men')) and ((i_class) IN ('personal', 'portable', 'reference', 'self-help') or (i_class) IN ('accessories', 'classical', 'fragrances', 'pants')) and i_item_sk is not null) - TableScan [TS_3] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_brand","i_class","i_category","i_manufact_id"] -
