HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query27.q.out
1 PREHOOK: query: explain
2 select  i_item_id,
3         s_state, grouping(s_state) g_state,
4         avg(ss_quantity) agg1,
5         avg(ss_list_price) agg2,
6         avg(ss_coupon_amt) agg3,
7         avg(ss_sales_price) agg4
8  from store_sales, customer_demographics, date_dim, store, item
9  where ss_sold_date_sk = d_date_sk and
10        ss_item_sk = i_item_sk and
11        ss_store_sk = s_store_sk and
12        ss_cdemo_sk = cd_demo_sk and
13        cd_gender = 'M' and
14        cd_marital_status = 'U' and
15        cd_education_status = '2 yr Degree' and
16        d_year = 2001 and
17        s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
18  group by rollup (i_item_id, s_state)
19  order by i_item_id
20          ,s_state
21  limit 100
22 PREHOOK: type: QUERY
23 POSTHOOK: query: explain
24 select  i_item_id,
25         s_state, grouping(s_state) g_state,
26         avg(ss_quantity) agg1,
27         avg(ss_list_price) agg2,
28         avg(ss_coupon_amt) agg3,
29         avg(ss_sales_price) agg4
30  from store_sales, customer_demographics, date_dim, store, item
31  where ss_sold_date_sk = d_date_sk and
32        ss_item_sk = i_item_sk and
33        ss_store_sk = s_store_sk and
34        ss_cdemo_sk = cd_demo_sk and
35        cd_gender = 'M' and
36        cd_marital_status = 'U' and
37        cd_education_status = '2 yr Degree' and
38        d_year = 2001 and
39        s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC')
40  group by rollup (i_item_id, s_state)
41  order by i_item_id
42          ,s_state
43  limit 100
44 POSTHOOK: type: QUERY
45 Plan optimized by CBO.
46
47 Vertex dependency in root stage
48 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
49 Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
50 Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
51 Reducer 5 <- Map 11 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
52 Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
53 Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
54
55 Stage-0
56   Fetch Operator
57     limit:100
58     Stage-1
59       Reducer 7
60       File Output Operator [FS_36]
61         Limit [LIM_35] (rows=100 width=88)
62           Number of rows:100
63           Select Operator [SEL_34] (rows=1264972921 width=88)
64             Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
65           <-Reducer 6 [SIMPLE_EDGE]
66             SHUFFLE [RS_33]
67               Select Operator [SEL_32] (rows=1264972921 width=88)
68                 Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
69                 Group By Operator [GBY_31] (rows=1264972921 width=88)
70                   Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","avg(VALUE._col3)"],keys:KEY._col0, KEY._col1, KEY._col2
71                 <-Reducer 5 [SIMPLE_EDGE]
72                   SHUFFLE [RS_30]
73                     PartitionCols:_col0, _col1, _col2
74                     Group By Operator [GBY_29] (rows=2529945843 width=88)
75                       Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["avg(_col2)","avg(_col3)","avg(_col4)","avg(_col5)"],keys:_col0, _col1, 0L
76                       Select Operator [SEL_27] (rows=843315281 width=88)
77                         Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
78                         Merge Join Operator [MERGEJOIN_59] (rows=843315281 width=88)
79                           Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col15","_col17"]
80                         <-Map 11 [SIMPLE_EDGE]
81                           SHUFFLE [RS_25]
82                             PartitionCols:_col0
83                             Select Operator [SEL_14] (rows=462000 width=1436)
84                               Output:["_col0","_col1"]
85                               Filter Operator [FIL_55] (rows=462000 width=1436)
86                                 predicate:i_item_sk is not null
87                                 TableScan [TS_12] (rows=462000 width=1436)
88                                   default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"]
89                         <-Reducer 4 [SIMPLE_EDGE]
90                           SHUFFLE [RS_24]
91                             PartitionCols:_col1
92                             Merge Join Operator [MERGEJOIN_58] (rows=766650239 width=88)
93                               Conds:RS_21._col3=RS_22._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col15"]
94                             <-Map 10 [SIMPLE_EDGE]
95                               SHUFFLE [RS_22]
96                                 PartitionCols:_col0
97                                 Select Operator [SEL_11] (rows=852 width=1910)
98                                   Output:["_col0","_col1"]
99                                   Filter Operator [FIL_54] (rows=852 width=1910)
100                                     predicate:((s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC') and s_store_sk is not null)
101                                     TableScan [TS_9] (rows=1704 width=1910)
102                                       default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"]
103                             <-Reducer 3 [SIMPLE_EDGE]
104                               SHUFFLE [RS_21]
105                                 PartitionCols:_col3
106                                 Merge Join Operator [MERGEJOIN_57] (rows=696954748 width=88)
107                                   Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"]
108                                 <-Map 9 [SIMPLE_EDGE]
109                                   SHUFFLE [RS_19]
110                                     PartitionCols:_col0
111                                     Select Operator [SEL_8] (rows=36524 width=1119)
112                                       Output:["_col0"]
113                                       Filter Operator [FIL_53] (rows=36524 width=1119)
114                                         predicate:((d_year = 2001) and d_date_sk is not null)
115                                         TableScan [TS_6] (rows=73049 width=1119)
116                                           default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
117                                 <-Reducer 2 [SIMPLE_EDGE]
118                                   SHUFFLE [RS_18]
119                                     PartitionCols:_col0
120                                     Merge Join Operator [MERGEJOIN_56] (rows=633595212 width=88)
121                                       Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"]
122                                     <-Map 1 [SIMPLE_EDGE]
123                                       SHUFFLE [RS_15]
124                                         PartitionCols:_col2
125                                         Select Operator [SEL_2] (rows=575995635 width=88)
126                                           Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
127                                           Filter Operator [FIL_51] (rows=575995635 width=88)
128                                             predicate:(ss_cdemo_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)
129                                             TableScan [TS_0] (rows=575995635 width=88)
130                                               default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_store_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"]
131                                     <-Map 8 [SIMPLE_EDGE]
132                                       SHUFFLE [RS_16]
133                                         PartitionCols:_col0
134                                         Select Operator [SEL_5] (rows=232725 width=385)
135                                           Output:["_col0"]
136                                           Filter Operator [FIL_52] (rows=232725 width=385)
137                                             predicate:((cd_education_status = '2 yr Degree') and (cd_gender = 'M') and (cd_marital_status = 'U') and cd_demo_sk is not null)
138                                             TableScan [TS_3] (rows=1861800 width=385)
139                                               default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"]
140