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:20 UTC
[15/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/query51.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out
new file mode 100644
index 0000000..9862559
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out
@@ -0,0 +1,222 @@
+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
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+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
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 8 (BROADCAST_EDGE)
+Map 12 <- Reducer 11 (BROADCAST_EDGE)
+Reducer 10 <- Reducer 9 (SIMPLE_EDGE)
+Reducer 11 <- Map 7 (CUSTOM_SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (CUSTOM_SIMPLE_EDGE)
+Reducer 9 <- Map 12 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 6 vectorized
+ File Output Operator [FS_117]
+ Limit [LIM_116] (rows=100 width=636)
+ Number of rows:100
+ Select Operator [SEL_115] (rows=363803676 width=636)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ <-Reducer 5 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_114]
+ Select Operator [SEL_113] (rows=363803676 width=636)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_112] (rows=363803676 width=420)
+ predicate:(max_window_0 > max_window_1)
+ PTF Operator [PTF_111] (rows=1091411029 width=420)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"CASE WHEN (_col4 is not null) THEN (_col4) ELSE (_col1) END ASC NULLS LAST","partition by:":"CASE WHEN (_col3 is not null) THEN (_col3) ELSE (_col0) END"}]
+ Select Operator [SEL_110] (rows=1091411029 width=420)
+ 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_87] (rows=1091411029 width=420)
+ Conds:RS_40._col0, _col1=RS_41._col0, _col1(Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ <-Reducer 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_41]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_37] (rows=3442937 width=210)
+ Output:["_col0","_col1","_col2"]
+ PTF Operator [PTF_36] (rows=3442937 width=210)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"_col0"}]
+ Group By Operator [GBY_32] (rows=3442937 width=210)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1
+ <-Reducer 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_31]
+ PartitionCols:_col0
+ Group By Operator [GBY_30] (rows=24992810 width=210)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4
+ Merge Join Operator [MERGEJOIN_86] (rows=24992810 width=209)
+ Conds:RS_108._col0=RS_92._col0(Inner),Output:["_col1","_col2","_col4"]
+ <-Map 7 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_92]
+ PartitionCols:_col0
+ Select Operator [SEL_89] (rows=317 width=98)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_88] (rows=317 width=102)
+ predicate:d_month_seq BETWEEN 1212 AND 1223
+ TableScan [TS_3] (rows=73049 width=102)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_date","d_month_seq"]
+ <-Map 12 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_108]
+ PartitionCols:_col0
+ Select Operator [SEL_107] (rows=143966864 width=119)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_106] (rows=143966864 width=119)
+ predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_27_date_dim_d_date_sk_min) AND DynamicValue(RS_27_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_27_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null)
+ TableScan [TS_20] (rows=144002668 width=119)
+ default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_item_sk","ws_sales_price"]
+ <-Reducer 11 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_105]
+ Group By Operator [GBY_104] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 7 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_97]
+ Group By Operator [GBY_95] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_93] (rows=317 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_89]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_40]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_17] (rows=3442937 width=210)
+ Output:["_col0","_col1","_col2"]
+ PTF Operator [PTF_16] (rows=3442937 width=210)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"_col0"}]
+ Group By Operator [GBY_12] (rows=3442937 width=210)
+ 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=95493908 width=210)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4
+ Merge Join Operator [MERGEJOIN_85] (rows=95493908 width=180)
+ Conds:RS_102._col0=RS_90._col0(Inner),Output:["_col1","_col2","_col4"]
+ <-Map 7 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_90]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_89]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_102]
+ PartitionCols:_col0
+ Select Operator [SEL_101] (rows=550076554 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_100] (rows=550076554 width=114)
+ 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)
+ TableScan [TS_0] (rows=575995635 width=114)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_sales_price"]
+ <-Reducer 8 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_99]
+ Group By Operator [GBY_98] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 7 [CUSTOM_SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_96]
+ Group By Operator [GBY_94] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_91] (rows=317 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_89]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out
new file mode 100644
index 0000000..72f9151
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out
@@ -0,0 +1,139 @@
+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
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+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
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 5 vectorized
+ File Output Operator [FS_79]
+ Select Operator [SEL_78] (rows=100 width=220)
+ Output:["_col0","_col1","_col2","_col3"]
+ Limit [LIM_77] (rows=100 width=216)
+ Number of rows:100
+ Select Operator [SEL_76] (rows=7333 width=216)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 4 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_75]
+ Group By Operator [GBY_74] (rows=7333 width=216)
+ 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=7333 width=216)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col5, _col6
+ Merge Join Operator [MERGEJOIN_54] (rows=2301098 width=104)
+ Conds:RS_12._col1=RS_65._col0(Inner),Output:["_col2","_col5","_col6"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_65]
+ PartitionCols:_col0
+ Select Operator [SEL_64] (rows=7333 width=107)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_63] (rows=7333 width=111)
+ predicate:(i_manager_id = 1)
+ TableScan [TS_6] (rows=462000 width=111)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,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_53] (rows=15062131 width=4)
+ Conds:RS_73._col0=RS_57._col0(Inner),Output:["_col1","_col2"]
+ <-Map 6 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_57]
+ PartitionCols:_col0
+ Select Operator [SEL_56] (rows=50 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_55] (rows=50 width=12)
+ predicate:((d_moy = 12) and (d_year = 1998))
+ TableScan [TS_3] (rows=73049 width=12)
+ default@date_dim,dt,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_73]
+ PartitionCols:_col0
+ Select Operator [SEL_72] (rows=550076554 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_71] (rows=550076554 width=114)
+ predicate:((ss_item_sk BETWEEN DynamicValue(RS_13_item_i_item_sk_min) AND DynamicValue(RS_13_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_13_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_10_dt_d_date_sk_min) AND DynamicValue(RS_10_dt_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_10_dt_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=114)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_62]
+ Group By Operator [GBY_61] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_60]
+ Group By Operator [GBY_59] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_58] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_56]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_70]
+ Group By Operator [GBY_69] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_68]
+ Group By Operator [GBY_67] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_66] (rows=7333 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_64]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out
new file mode 100644
index 0000000..27adc6e
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out
@@ -0,0 +1,161 @@
+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
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+select * from
+(select i_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
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 5 vectorized
+ File Output Operator [FS_86]
+ Limit [LIM_85] (rows=30 width=228)
+ Number of rows:100
+ Select Operator [SEL_84] (rows=30 width=228)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ Select Operator [SEL_24] (rows=30 width=228)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_36] (rows=30 width=228)
+ predicate:CASE WHEN ((avg_window_0 > 0)) THEN (((abs((_col2 - avg_window_0)) / avg_window_0) > 0.1)) ELSE (null) END
+ Select Operator [SEL_23] (rows=60 width=116)
+ Output:["avg_window_0","_col0","_col2"]
+ PTF Operator [PTF_22] (rows=60 width=116)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col0 ASC NULLS FIRST","partition by:":"_col0"}]
+ Select Operator [SEL_19] (rows=60 width=116)
+ Output:["_col0","_col2"]
+ Group By Operator [GBY_18] (rows=60 width=120)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Group By Operator [GBY_16] (rows=60 width=120)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col4, _col6
+ Merge Join Operator [MERGEJOIN_62] (rows=129200 width=8)
+ Conds:RS_12._col0=RS_73._col0(Inner),Output:["_col2","_col4","_col6"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_73]
+ PartitionCols:_col0
+ Select Operator [SEL_72] (rows=317 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_71] (rows=317 width=12)
+ predicate:(d_month_seq) IN (1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223)
+ TableScan [TS_6] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq","d_qoy"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_61] (rows=744232 width=4)
+ Conds:RS_81._col1=RS_65._col0(Inner),Output:["_col0","_col2","_col4"]
+ <-Map 6 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_65]
+ PartitionCols:_col0
+ Select Operator [SEL_64] (rows=68 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_63] (rows=68 width=290)
+ predicate:((((i_category) IN ('Books', 'Children', 'Electronics') and (i_class) IN ('personal', 'portable', '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', 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') and (i_category) IN ('Books', 'Children', 'Electronics', 'Women', 'Music', 'Men') and (i_class) IN ('personal', 'portable', 'reference', 'self-help', 'accessories', 'classical', 'fragrances', 'pants'))
+ TableScan [TS_3] (rows=462000 width=289)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand","i_class","i_category","i_manufact_id"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_81]
+ PartitionCols:_col1
+ Select Operator [SEL_80] (rows=525329897 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_79] (rows=525329897 width=118)
+ predicate:((ss_item_sk BETWEEN DynamicValue(RS_10_item_i_item_sk_min) AND DynamicValue(RS_10_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_10_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_13_date_dim_d_date_sk_min) AND DynamicValue(RS_13_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_13_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=118)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_sales_price"]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_70]
+ Group By Operator [GBY_69] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_68]
+ Group By Operator [GBY_67] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_66] (rows=68 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_64]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_78]
+ Group By Operator [GBY_77] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_76]
+ Group By Operator [GBY_75] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_74] (rows=317 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_72]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out
new file mode 100644
index 0000000..0210163
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out
@@ -0,0 +1,439 @@
+Warning: Shuffle Join MERGEJOIN[270][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3]] in Stage 'Reducer 4' is a cross product
+Warning: Shuffle Join MERGEJOIN[271][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product
+Warning: Shuffle Join MERGEJOIN[269][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 32' is a cross product
+Warning: Shuffle Join MERGEJOIN[272][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
+PREHOOK: query: explain
+with my_customers as (
+ select distinct c_customer_sk
+ , c_current_addr_sk
+ from
+ ( select cs_sold_date_sk sold_date_sk,
+ cs_bill_customer_sk customer_sk,
+ cs_item_sk item_sk
+ from catalog_sales
+ union all
+ select ws_sold_date_sk sold_date_sk,
+ ws_bill_customer_sk customer_sk,
+ ws_item_sk item_sk
+ from web_sales
+ ) cs_or_ws_sales,
+ item,
+ date_dim,
+ customer
+ where sold_date_sk = d_date_sk
+ and item_sk = i_item_sk
+ and i_category = 'Jewelry'
+ and i_class = 'consignment'
+ and c_customer_sk = cs_or_ws_sales.customer_sk
+ and d_moy = 3
+ and d_year = 1999
+ )
+ , my_revenue as (
+ select c_customer_sk,
+ sum(ss_ext_sales_price) as revenue
+ from my_customers,
+ store_sales,
+ customer_address,
+ store,
+ date_dim
+ where c_current_addr_sk = ca_address_sk
+ and ca_county = s_county
+ and ca_state = s_state
+ and ss_sold_date_sk = d_date_sk
+ and c_customer_sk = ss_customer_sk
+ and d_month_seq between (select distinct d_month_seq+1
+ from date_dim where d_year = 1999 and d_moy = 3)
+ and (select distinct d_month_seq+3
+ from date_dim where d_year = 1999 and d_moy = 3)
+ group by c_customer_sk
+ )
+ , segments as
+ (select cast((revenue/50) as int) as segment
+ from my_revenue
+ )
+ select segment, count(*) as num_customers, segment*50 as segment_base
+ from segments
+ group by segment
+ order by segment, num_customers
+ limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@catalog_sales
+PREHOOK: Input: default@customer
+PREHOOK: Input: default@customer_address
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store
+PREHOOK: Input: default@store_sales
+PREHOOK: Input: default@web_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+with my_customers as (
+ select distinct c_customer_sk
+ , c_current_addr_sk
+ from
+ ( select cs_sold_date_sk sold_date_sk,
+ cs_bill_customer_sk customer_sk,
+ cs_item_sk item_sk
+ from catalog_sales
+ union all
+ select ws_sold_date_sk sold_date_sk,
+ ws_bill_customer_sk customer_sk,
+ ws_item_sk item_sk
+ from web_sales
+ ) cs_or_ws_sales,
+ item,
+ date_dim,
+ customer
+ where sold_date_sk = d_date_sk
+ and item_sk = i_item_sk
+ and i_category = 'Jewelry'
+ and i_class = 'consignment'
+ and c_customer_sk = cs_or_ws_sales.customer_sk
+ and d_moy = 3
+ and d_year = 1999
+ )
+ , my_revenue as (
+ select c_customer_sk,
+ sum(ss_ext_sales_price) as revenue
+ from my_customers,
+ store_sales,
+ customer_address,
+ store,
+ date_dim
+ where c_current_addr_sk = ca_address_sk
+ and ca_county = s_county
+ and ca_state = s_state
+ and ss_sold_date_sk = d_date_sk
+ and c_customer_sk = ss_customer_sk
+ and d_month_seq between (select distinct d_month_seq+1
+ from date_dim where d_year = 1999 and d_moy = 3)
+ and (select distinct d_month_seq+3
+ from date_dim where d_year = 1999 and d_moy = 3)
+ group by c_customer_sk
+ )
+ , segments as
+ (select cast((revenue/50) as int) as segment
+ from my_revenue
+ )
+ select segment, count(*) as num_customers, segment*50 as segment_base
+ from segments
+ group by segment
+ order by segment, num_customers
+ limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@catalog_sales
+POSTHOOK: Input: default@customer
+POSTHOOK: Input: default@customer_address
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Input: default@web_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 14 (BROADCAST_EDGE)
+Map 16 <- Reducer 24 (BROADCAST_EDGE), Reducer 26 (BROADCAST_EDGE), Union 17 (CONTAINS)
+Map 22 <- Reducer 24 (BROADCAST_EDGE), Reducer 26 (BROADCAST_EDGE), Union 17 (CONTAINS)
+Reducer 12 <- Map 11 (SIMPLE_EDGE), Map 15 (SIMPLE_EDGE)
+Reducer 13 <- Reducer 12 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE)
+Reducer 14 <- Reducer 13 (CUSTOM_SIMPLE_EDGE)
+Reducer 18 <- Map 23 (SIMPLE_EDGE), Union 17 (SIMPLE_EDGE)
+Reducer 19 <- Map 25 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 10 (SIMPLE_EDGE)
+Reducer 20 <- Map 27 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE)
+Reducer 21 <- Reducer 20 (SIMPLE_EDGE)
+Reducer 24 <- Map 23 (CUSTOM_SIMPLE_EDGE)
+Reducer 26 <- Map 25 (CUSTOM_SIMPLE_EDGE)
+Reducer 29 <- Map 28 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 13 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 30 <- Reducer 29 (CUSTOM_SIMPLE_EDGE)
+Reducer 31 <- Map 28 (SIMPLE_EDGE)
+Reducer 32 <- Reducer 31 (CUSTOM_SIMPLE_EDGE), Reducer 34 (CUSTOM_SIMPLE_EDGE)
+Reducer 33 <- Map 28 (SIMPLE_EDGE)
+Reducer 34 <- Reducer 33 (CUSTOM_SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (CUSTOM_SIMPLE_EDGE), Reducer 30 (CUSTOM_SIMPLE_EDGE)
+Reducer 5 <- Reducer 29 (CUSTOM_SIMPLE_EDGE), Reducer 4 (CUSTOM_SIMPLE_EDGE)
+Reducer 6 <- Reducer 32 (CUSTOM_SIMPLE_EDGE), Reducer 5 (CUSTOM_SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 9 vectorized
+ File Output Operator [FS_352]
+ Limit [LIM_351] (rows=1 width=16)
+ Number of rows:100
+ Select Operator [SEL_350] (rows=1 width=16)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 8 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_349]
+ Select Operator [SEL_348] (rows=1 width=16)
+ Output:["_col0","_col1","_col2"]
+ Group By Operator [GBY_347] (rows=1 width=12)
+ Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
+ <-Reducer 7 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_346]
+ PartitionCols:_col0
+ Group By Operator [GBY_345] (rows=1 width=12)
+ Output:["_col0","_col1"],aggregations:["count()"],keys:_col0
+ Select Operator [SEL_344] (rows=1 width=116)
+ Output:["_col0"]
+ Group By Operator [GBY_343] (rows=1 width=116)
+ Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_118]
+ PartitionCols:_col0
+ Group By Operator [GBY_117] (rows=312 width=116)
+ Output:["_col0","_col1"],aggregations:["sum(_col1)"],keys:_col0
+ Select Operator [SEL_116] (rows=624257222 width=127)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_115] (rows=624257222 width=127)
+ predicate:_col2 BETWEEN _col3 AND _col4
+ Select Operator [SEL_114] (rows=5618315000 width=127)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Merge Join Operator [MERGEJOIN_272] (rows=5618315000 width=127)
+ Conds:(Inner),Output:["_col0","_col2","_col6","_col13","_col15"]
+ <-Reducer 32 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_111]
+ Merge Join Operator [MERGEJOIN_269] (rows=25 width=4)
+ Conds:(Right Outer),Output:["_col0"]
+ <-Reducer 31 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_334]
+ Group By Operator [GBY_333] (rows=25 width=4)
+ Output:["_col0"],keys:KEY._col0
+ <-Map 28 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_322]
+ PartitionCols:_col0
+ Group By Operator [GBY_319] (rows=25 width=4)
+ Output:["_col0"],keys:_col0
+ Select Operator [SEL_316] (rows=50 width=12)
+ Output:["_col0"]
+ Filter Operator [FIL_314] (rows=50 width=12)
+ predicate:((d_moy = 3) and (d_year = 1999))
+ TableScan [TS_72] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_month_seq","d_year","d_moy"]
+ <-Reducer 34 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_342]
+ Select Operator [SEL_341] (rows=1 width=8)
+ Filter Operator [FIL_340] (rows=1 width=8)
+ predicate:(sq_count_check(_col0) <= 1)
+ Group By Operator [GBY_339] (rows=1 width=8)
+ Output:["_col0"],aggregations:["count(VALUE._col0)"]
+ <-Reducer 33 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_338]
+ Group By Operator [GBY_337] (rows=1 width=8)
+ Output:["_col0"],aggregations:["count()"]
+ Select Operator [SEL_336] (rows=25 width=4)
+ Group By Operator [GBY_335] (rows=25 width=4)
+ Output:["_col0"],keys:KEY._col0
+ <-Map 28 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_323]
+ PartitionCols:_col0
+ Group By Operator [GBY_320] (rows=25 width=4)
+ Output:["_col0"],keys:_col0
+ Select Operator [SEL_317] (rows=50 width=12)
+ Output:["_col0"]
+ Please refer to the previous Filter Operator [FIL_314]
+ <-Reducer 5 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_112]
+ Select Operator [SEL_107] (rows=224732600 width=119)
+ Output:["_col0","_col4","_col11","_col13"]
+ Merge Join Operator [MERGEJOIN_271] (rows=224732600 width=119)
+ Conds:(Left Outer),Output:["_col2","_col4","_col10","_col13"]
+ <-Reducer 29 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_326]
+ Group By Operator [GBY_324] (rows=25 width=4)
+ Output:["_col0"],keys:KEY._col0
+ <-Map 28 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_321]
+ PartitionCols:_col0
+ Group By Operator [GBY_318] (rows=25 width=4)
+ Output:["_col0"],keys:_col0
+ Select Operator [SEL_315] (rows=50 width=12)
+ Output:["_col0"]
+ Please refer to the previous Filter Operator [FIL_314]
+ <-Reducer 4 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_104]
+ Merge Join Operator [MERGEJOIN_270] (rows=8989304 width=8)
+ Conds:(Inner),Output:["_col2","_col4","_col10"]
+ <-Reducer 3 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_101]
+ Merge Join Operator [MERGEJOIN_268] (rows=8989304 width=8)
+ Conds:RS_98._col1=RS_99._col5(Inner),Output:["_col2","_col4","_col10"]
+ <-Reducer 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_99]
+ PartitionCols:_col5
+ Merge Join Operator [MERGEJOIN_267] (rows=55046 width=4)
+ Conds:RS_68._col0=RS_306._col1(Inner),Output:["_col5"]
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_68]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_263] (rows=39720279 width=4)
+ Conds:RS_285._col1, _col2=RS_288._col0, _col1(Inner),Output:["_col0"]
+ <-Map 11 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_285]
+ PartitionCols:_col1, _col2
+ Select Operator [SEL_284] (rows=40000000 width=188)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_283] (rows=40000000 width=188)
+ predicate:(ca_county is not null and ca_state is not null)
+ TableScan [TS_28] (rows=40000000 width=188)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_address_sk","ca_county","ca_state"]
+ <-Map 15 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_288]
+ PartitionCols:_col0, _col1
+ Select Operator [SEL_287] (rows=1704 width=184)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_286] (rows=1704 width=184)
+ predicate:(s_county is not null and s_state is not null)
+ TableScan [TS_31] (rows=1704 width=184)
+ default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_county","s_state"]
+ <-Reducer 21 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_306]
+ PartitionCols:_col1
+ Select Operator [SEL_305] (rows=55046 width=8)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_304] (rows=55046 width=8)
+ Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+ <-Reducer 20 [SIMPLE_EDGE]
+ SHUFFLE [RS_62]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_61] (rows=55046 width=8)
+ Output:["_col0","_col1"],keys:_col6, _col5
+ Merge Join Operator [MERGEJOIN_266] (rows=110092 width=8)
+ Conds:RS_57._col1=RS_303._col0(Inner),Output:["_col5","_col6"]
+ <-Map 27 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_303]
+ PartitionCols:_col0
+ Select Operator [SEL_302] (rows=80000000 width=8)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_301] (rows=80000000 width=8)
+ predicate:c_current_addr_sk is not null
+ TableScan [TS_48] (rows=80000000 width=8)
+ default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_current_addr_sk"]
+ <-Reducer 19 [SIMPLE_EDGE]
+ SHUFFLE [RS_57]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_265] (rows=110092 width=0)
+ Conds:RS_54._col2=RS_297._col0(Inner),Output:["_col1"]
+ <-Map 25 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_297]
+ PartitionCols:_col0
+ Select Operator [SEL_296] (rows=453 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_295] (rows=453 width=186)
+ predicate:((i_category = 'Jewelry') and (i_class = 'consignment'))
+ TableScan [TS_45] (rows=462000 width=186)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_class","i_category"]
+ <-Reducer 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_54]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_264] (rows=11665117 width=7)
+ Conds:Union 17._col0=RS_291._col0(Inner),Output:["_col1","_col2"]
+ <-Map 23 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_291]
+ PartitionCols:_col0
+ Select Operator [SEL_290] (rows=50 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_289] (rows=50 width=12)
+ predicate:((d_moy = 3) and (d_year = 1999))
+ TableScan [TS_42] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"]
+ <-Union 17 [SIMPLE_EDGE]
+ <-Map 16 [CONTAINS] vectorized
+ Reduce Output Operator [RS_361]
+ PartitionCols:_col0
+ Select Operator [SEL_360] (rows=285117831 width=11)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_359] (rows=285117831 width=11)
+ predicate:((cs_item_sk BETWEEN DynamicValue(RS_55_item_i_item_sk_min) AND DynamicValue(RS_55_item_i_item_sk_max) and in_bloom_filter(cs_item_sk, DynamicValue(RS_55_item_i_item_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_52_date_dim_d_date_sk_min) AND DynamicValue(RS_52_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_52_date_dim_d_date_sk_bloom_filter))) and cs_bill_customer_sk is not null and cs_sold_date_sk is not null)
+ TableScan [TS_273] (rows=287989836 width=11)
+ Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk"]
+ <-Reducer 24 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_354]
+ Group By Operator [GBY_353] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_294]
+ Group By Operator [GBY_293] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_292] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_290]
+ <-Reducer 26 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_357]
+ Group By Operator [GBY_356] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 25 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_300]
+ Group By Operator [GBY_299] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_298] (rows=453 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_296]
+ <-Map 22 [CONTAINS] vectorized
+ Reduce Output Operator [RS_364]
+ PartitionCols:_col0
+ Select Operator [SEL_363] (rows=143930993 width=11)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_362] (rows=143930993 width=11)
+ predicate:((ws_item_sk BETWEEN DynamicValue(RS_55_item_i_item_sk_min) AND DynamicValue(RS_55_item_i_item_sk_max) and in_bloom_filter(ws_item_sk, DynamicValue(RS_55_item_i_item_sk_bloom_filter))) and (ws_sold_date_sk BETWEEN DynamicValue(RS_52_date_dim_d_date_sk_min) AND DynamicValue(RS_52_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_52_date_dim_d_date_sk_bloom_filter))) and ws_bill_customer_sk is not null and ws_sold_date_sk is not null)
+ TableScan [TS_278] (rows=144002668 width=11)
+ Output:["ws_sold_date_sk","ws_item_sk","ws_bill_customer_sk"]
+ <-Reducer 24 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_355]
+ Please refer to the previous Group By Operator [GBY_353]
+ <-Reducer 26 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_358]
+ Please refer to the previous Group By Operator [GBY_356]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_98]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_262] (rows=525327388 width=114)
+ Conds:RS_311._col0=RS_313._col0(Inner),Output:["_col1","_col2","_col4"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_311]
+ PartitionCols:_col0
+ Select Operator [SEL_310] (rows=525327388 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_309] (rows=525327388 width=114)
+ predicate:((ss_customer_sk BETWEEN DynamicValue(RS_99_customer_c_customer_sk_min) AND DynamicValue(RS_99_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_99_customer_c_customer_sk_bloom_filter))) and ss_customer_sk is not null and ss_sold_date_sk is not null)
+ TableScan [TS_23] (rows=575995635 width=114)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_customer_sk","ss_ext_sales_price"]
+ <-Reducer 14 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_308]
+ Group By Operator [GBY_307] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Reducer 13 [CUSTOM_SIMPLE_EDGE]
+ SHUFFLE [RS_182]
+ Group By Operator [GBY_181] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_180] (rows=55046 width=8)
+ Output:["_col0"]
+ Please refer to the previous Merge Join Operator [MERGEJOIN_267]
+ <-Map 10 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_313]
+ PartitionCols:_col0
+ Select Operator [SEL_312] (rows=73049 width=8)
+ Output:["_col0","_col1"]
+ TableScan [TS_26] (rows=73049 width=8)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq"]
+ <-Reducer 30 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_332]
+ Select Operator [SEL_331] (rows=1 width=8)
+ Filter Operator [FIL_330] (rows=1 width=8)
+ predicate:(sq_count_check(_col0) <= 1)
+ Group By Operator [GBY_329] (rows=1 width=8)
+ Output:["_col0"],aggregations:["count(VALUE._col0)"]
+ <-Reducer 29 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_328]
+ Group By Operator [GBY_327] (rows=1 width=8)
+ Output:["_col0"],aggregations:["count()"]
+ Select Operator [SEL_325] (rows=25 width=4)
+ Please refer to the previous Group By Operator [GBY_324]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out
new file mode 100644
index 0000000..eb6b84f
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out
@@ -0,0 +1,123 @@
+PREHOOK: query: explain
+select i_brand_id brand_id, i_brand brand,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=36
+ and d_moy=12
+ and d_year=2001
+ group by i_brand, i_brand_id
+ order by ext_price desc, i_brand_id
+limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@date_dim
+PREHOOK: Input: default@item
+PREHOOK: Input: default@store_sales
+PREHOOK: Output: hdfs://### HDFS PATH ###
+POSTHOOK: query: explain
+select i_brand_id brand_id, i_brand brand,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=36
+ and d_moy=12
+ and d_year=2001
+ group by i_brand, i_brand_id
+ order by ext_price desc, i_brand_id
+limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@date_dim
+POSTHOOK: Input: default@item
+POSTHOOK: Input: default@store_sales
+POSTHOOK: Output: hdfs://### HDFS PATH ###
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE)
+Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 5 vectorized
+ File Output Operator [FS_79]
+ Limit [LIM_78] (rows=100 width=220)
+ Number of rows:100
+ Select Operator [SEL_77] (rows=7333 width=220)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 4 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_76]
+ Select Operator [SEL_75] (rows=7333 width=220)
+ Output:["_col1","_col2","_col3"]
+ Group By Operator [GBY_74] (rows=7333 width=216)
+ 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=7333 width=216)
+ Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col5, _col6
+ Merge Join Operator [MERGEJOIN_54] (rows=2301098 width=104)
+ Conds:RS_12._col1=RS_65._col0(Inner),Output:["_col2","_col5","_col6"]
+ <-Map 8 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_65]
+ PartitionCols:_col0
+ Select Operator [SEL_64] (rows=7333 width=107)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_63] (rows=7333 width=111)
+ predicate:(i_manager_id = 36)
+ TableScan [TS_6] (rows=462000 width=111)
+ default@item,item,Tbl:COMPLETE,Col:COMPLETE,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_53] (rows=15062131 width=4)
+ Conds:RS_73._col0=RS_57._col0(Inner),Output:["_col1","_col2"]
+ <-Map 6 [SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_57]
+ PartitionCols:_col0
+ Select Operator [SEL_56] (rows=50 width=4)
+ Output:["_col0"]
+ Filter Operator [FIL_55] (rows=50 width=12)
+ predicate:((d_moy = 12) and (d_year = 2001))
+ TableScan [TS_3] (rows=73049 width=12)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"]
+ <-Map 1 [SIMPLE_EDGE] vectorized
+ SHUFFLE [RS_73]
+ PartitionCols:_col0
+ Select Operator [SEL_72] (rows=550076554 width=114)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_71] (rows=550076554 width=114)
+ predicate:((ss_item_sk BETWEEN DynamicValue(RS_13_item_i_item_sk_min) AND DynamicValue(RS_13_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_13_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_10_date_dim_d_date_sk_min) AND DynamicValue(RS_10_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_10_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=114)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"]
+ <-Reducer 7 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_62]
+ Group By Operator [GBY_61] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_60]
+ Group By Operator [GBY_59] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_58] (rows=50 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_56]
+ <-Reducer 9 [BROADCAST_EDGE] vectorized
+ BROADCAST [RS_70]
+ Group By Operator [GBY_69] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"]
+ <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized
+ PARTITION_ONLY_SHUFFLE [RS_68]
+ Group By Operator [GBY_67] (rows=1 width=12)
+ Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"]
+ Select Operator [SEL_66] (rows=7333 width=4)
+ Output:["_col0"]
+ Please refer to the previous Select Operator [SEL_64]
+