HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query39.q.out
1 PREHOOK: query: explain
2 with inv as
3 (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
4        ,stdev,mean, case mean when 0 then null else stdev/mean end cov
5  from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
6             ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
7       from inventory
8           ,item
9           ,warehouse
10           ,date_dim
11       where inv_item_sk = i_item_sk
12         and inv_warehouse_sk = w_warehouse_sk
13         and inv_date_sk = d_date_sk
14         and d_year =1999
15       group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
16  where case mean when 0 then 0 else stdev/mean end > 1)
17 select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
18         ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
19 from inv inv1,inv inv2
20 where inv1.i_item_sk = inv2.i_item_sk
21   and inv1.w_warehouse_sk =  inv2.w_warehouse_sk
22   and inv1.d_moy=4
23   and inv2.d_moy=4+1
24 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
25         ,inv2.d_moy,inv2.mean, inv2.cov
26 PREHOOK: type: QUERY
27 POSTHOOK: query: explain
28 with inv as
29 (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
30        ,stdev,mean, case mean when 0 then null else stdev/mean end cov
31  from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
32             ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
33       from inventory
34           ,item
35           ,warehouse
36           ,date_dim
37       where inv_item_sk = i_item_sk
38         and inv_warehouse_sk = w_warehouse_sk
39         and inv_date_sk = d_date_sk
40         and d_year =1999
41       group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
42  where case mean when 0 then 0 else stdev/mean end > 1)
43 select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
44         ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
45 from inv inv1,inv inv2
46 where inv1.i_item_sk = inv2.i_item_sk
47   and inv1.w_warehouse_sk =  inv2.w_warehouse_sk
48   and inv1.d_moy=4
49   and inv2.d_moy=4+1
50 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
51         ,inv2.d_moy,inv2.mean, inv2.cov
52 POSTHOOK: type: QUERY
53 Plan optimized by CBO.
54
55 Vertex dependency in root stage
56 Reducer 10 <- Map 14 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
57 Reducer 11 <- Reducer 10 (SIMPLE_EDGE)
58 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE)
59 Reducer 3 <- Map 13 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
60 Reducer 4 <- Map 14 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
61 Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
62 Reducer 6 <- Reducer 11 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
63 Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
64 Reducer 8 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE)
65 Reducer 9 <- Map 13 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
66
67 Stage-0
68   Fetch Operator
69     limit:-1
70     Stage-1
71       Reducer 7
72       File Output Operator [FS_63]
73         Select Operator [SEL_61] (rows=13756683 width=15)
74           Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
75         <-Reducer 6 [SIMPLE_EDGE]
76           SHUFFLE [RS_60]
77             Merge Join Operator [MERGEJOIN_104] (rows=13756683 width=15)
78               Conds:RS_56._col0, _col1=RS_57._col0, _col1(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
79             <-Reducer 11 [SIMPLE_EDGE]
80               SHUFFLE [RS_57]
81                 PartitionCols:_col0, _col1
82                 Select Operator [SEL_55] (rows=12506076 width=15)
83                   Output:["_col0","_col1","_col2","_col3"]
84                   Filter Operator [FIL_54] (rows=12506076 width=15)
85                     predicate:CASE WHEN ((_col4 = 0.0D)) THEN (false) ELSE (((_col3 / _col4) > 1.0D)) END
86                     Select Operator [SEL_53] (rows=25012152 width=15)
87                       Output:["_col1","_col2","_col3","_col4"]
88                       Group By Operator [GBY_52] (rows=25012152 width=15)
89                         Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["stddev_samp(VALUE._col0)","avg(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2
90                       <-Reducer 10 [SIMPLE_EDGE]
91                         SHUFFLE [RS_51]
92                           PartitionCols:_col0, _col1, _col2
93                           Group By Operator [GBY_50] (rows=50024305 width=15)
94                             Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["stddev_samp(_col3)","avg(_col3)"],keys:_col8, _col7, _col9
95                             Merge Join Operator [MERGEJOIN_103] (rows=50024305 width=15)
96                               Conds:RS_46._col2=RS_47._col0(Inner),Output:["_col3","_col7","_col8","_col9"]
97                             <-Map 14 [SIMPLE_EDGE]
98                               SHUFFLE [RS_47]
99                                 PartitionCols:_col0
100                                 Select Operator [SEL_11] (rows=27 width=1029)
101                                   Output:["_col0","_col1"]
102                                   Filter Operator [FIL_93] (rows=27 width=1029)
103                                     predicate:w_warehouse_sk is not null
104                                     TableScan [TS_9] (rows=27 width=1029)
105                                       default@warehouse,warehouse,Tbl:COMPLETE,Col:NONE,Output:["w_warehouse_sk","w_warehouse_name"]
106                             <-Reducer 9 [SIMPLE_EDGE]
107                               SHUFFLE [RS_46]
108                                 PartitionCols:_col2
109                                 Merge Join Operator [MERGEJOIN_102] (rows=45476640 width=15)
110                                   Conds:RS_43._col1=RS_44._col0(Inner),Output:["_col2","_col3","_col7"]
111                                 <-Map 13 [SIMPLE_EDGE]
112                                   SHUFFLE [RS_44]
113                                     PartitionCols:_col0
114                                     Select Operator [SEL_8] (rows=462000 width=1436)
115                                       Output:["_col0"]
116                                       Filter Operator [FIL_92] (rows=462000 width=1436)
117                                         predicate:i_item_sk is not null
118                                         TableScan [TS_6] (rows=462000 width=1436)
119                                           default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk"]
120                                 <-Reducer 8 [SIMPLE_EDGE]
121                                   SHUFFLE [RS_43]
122                                     PartitionCols:_col1
123                                     Merge Join Operator [MERGEJOIN_101] (rows=41342400 width=15)
124                                       Conds:RS_40._col0=RS_41._col0(Inner),Output:["_col1","_col2","_col3"]
125                                     <-Map 1 [SIMPLE_EDGE]
126                                       SHUFFLE [RS_40]
127                                         PartitionCols:_col0
128                                         Select Operator [SEL_2] (rows=37584000 width=15)
129                                           Output:["_col0","_col1","_col2","_col3"]
130                                           Filter Operator [FIL_90] (rows=37584000 width=15)
131                                             predicate:(inv_date_sk is not null and inv_item_sk is not null and inv_warehouse_sk is not null)
132                                             TableScan [TS_0] (rows=37584000 width=15)
133                                               default@inventory,inventory,Tbl:COMPLETE,Col:NONE,Output:["inv_date_sk","inv_item_sk","inv_warehouse_sk","inv_quantity_on_hand"]
134                                     <-Map 12 [SIMPLE_EDGE]
135                                       SHUFFLE [RS_41]
136                                         PartitionCols:_col0
137                                         Select Operator [SEL_33] (rows=18262 width=1119)
138                                           Output:["_col0"]
139                                           Filter Operator [FIL_95] (rows=18262 width=1119)
140                                             predicate:((d_moy = 5) and (d_year = 1999) and d_date_sk is not null)
141                                             TableScan [TS_3] (rows=73049 width=1119)
142                                               default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"]
143             <-Reducer 5 [SIMPLE_EDGE]
144               SHUFFLE [RS_56]
145                 PartitionCols:_col0, _col1
146                 Select Operator [SEL_27] (rows=12506076 width=15)
147                   Output:["_col0","_col1","_col2","_col3"]
148                   Filter Operator [FIL_26] (rows=12506076 width=15)
149                     predicate:CASE WHEN ((_col4 = 0.0D)) THEN (false) ELSE (((_col3 / _col4) > 1.0D)) END
150                     Select Operator [SEL_25] (rows=25012152 width=15)
151                       Output:["_col1","_col2","_col3","_col4"]
152                       Group By Operator [GBY_24] (rows=25012152 width=15)
153                         Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["stddev_samp(VALUE._col0)","avg(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2
154                       <-Reducer 4 [SIMPLE_EDGE]
155                         SHUFFLE [RS_23]
156                           PartitionCols:_col0, _col1, _col2
157                           Group By Operator [GBY_22] (rows=50024305 width=15)
158                             Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["stddev_samp(_col3)","avg(_col3)"],keys:_col8, _col7, _col9
159                             Merge Join Operator [MERGEJOIN_100] (rows=50024305 width=15)
160                               Conds:RS_18._col2=RS_19._col0(Inner),Output:["_col3","_col7","_col8","_col9"]
161                             <-Map 14 [SIMPLE_EDGE]
162                               SHUFFLE [RS_19]
163                                 PartitionCols:_col0
164                                  Please refer to the previous Select Operator [SEL_11]
165                             <-Reducer 3 [SIMPLE_EDGE]
166                               SHUFFLE [RS_18]
167                                 PartitionCols:_col2
168                                 Merge Join Operator [MERGEJOIN_99] (rows=45476640 width=15)
169                                   Conds:RS_15._col1=RS_16._col0(Inner),Output:["_col2","_col3","_col7"]
170                                 <-Map 13 [SIMPLE_EDGE]
171                                   SHUFFLE [RS_16]
172                                     PartitionCols:_col0
173                                      Please refer to the previous Select Operator [SEL_8]
174                                 <-Reducer 2 [SIMPLE_EDGE]
175                                   SHUFFLE [RS_15]
176                                     PartitionCols:_col1
177                                     Merge Join Operator [MERGEJOIN_98] (rows=41342400 width=15)
178                                       Conds:RS_12._col0=RS_13._col0(Inner),Output:["_col1","_col2","_col3"]
179                                     <-Map 1 [SIMPLE_EDGE]
180                                       SHUFFLE [RS_12]
181                                         PartitionCols:_col0
182                                          Please refer to the previous Select Operator [SEL_2]
183                                     <-Map 12 [SIMPLE_EDGE]
184                                       SHUFFLE [RS_13]
185                                         PartitionCols:_col0
186                                         Select Operator [SEL_5] (rows=18262 width=1119)
187                                           Output:["_col0"]
188                                           Filter Operator [FIL_91] (rows=18262 width=1119)
189                                             predicate:((d_moy = 4) and (d_year = 1999) and d_date_sk is not null)
190                                              Please refer to the previous TableScan [TS_3]
191
192 PREHOOK: query: with inv as
193 (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
194        ,stdev,mean, case mean when 0 then null else stdev/mean end cov
195  from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
196             ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
197       from inventory
198           ,item
199           ,warehouse
200           ,date_dim
201       where inv_item_sk = i_item_sk
202         and inv_warehouse_sk = w_warehouse_sk
203         and inv_date_sk = d_date_sk
204         and d_year =1999
205       group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
206  where case mean when 0 then 0 else stdev/mean end > 1)
207 select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
208         ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
209 from inv inv1,inv inv2
210 where inv1.i_item_sk = inv2.i_item_sk
211   and inv1.w_warehouse_sk =  inv2.w_warehouse_sk
212   and inv1.d_moy=4
213   and inv2.d_moy=4+1
214   and inv1.cov > 1.5
215 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
216         ,inv2.d_moy,inv2.mean, inv2.cov
217 PREHOOK: type: QUERY
218 PREHOOK: Input: default@date_dim
219 PREHOOK: Input: default@inventory
220 PREHOOK: Input: default@item
221 PREHOOK: Input: default@warehouse
222 PREHOOK: Output: hdfs://### HDFS PATH ###
223 POSTHOOK: query: with inv as
224 (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
225        ,stdev,mean, case mean when 0 then null else stdev/mean end cov
226  from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
227             ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
228       from inventory
229           ,item
230           ,warehouse
231           ,date_dim
232       where inv_item_sk = i_item_sk
233         and inv_warehouse_sk = w_warehouse_sk
234         and inv_date_sk = d_date_sk
235         and d_year =1999
236       group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
237  where case mean when 0 then 0 else stdev/mean end > 1)
238 select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
239         ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
240 from inv inv1,inv inv2
241 where inv1.i_item_sk = inv2.i_item_sk
242   and inv1.w_warehouse_sk =  inv2.w_warehouse_sk
243   and inv1.d_moy=4
244   and inv2.d_moy=4+1
245   and inv1.cov > 1.5
246 order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
247         ,inv2.d_moy,inv2.mean, inv2.cov
248 POSTHOOK: type: QUERY
249 POSTHOOK: Input: default@date_dim
250 POSTHOOK: Input: default@inventory
251 POSTHOOK: Input: default@item
252 POSTHOOK: Input: default@warehouse
253 POSTHOOK: Output: hdfs://### HDFS PATH ###