PageRenderTime 12ms CodeModel.GetById 2ms app.highlight 3ms RepoModel.GetById 1ms app.codeStats 0ms

/tags/release-0.2.0-rc0/hive/external/ql/src/test/results/clientpositive/mapjoin_subquery.q.out

#
text | 535 lines | 522 code | 13 blank | 0 comment | 0 complexity | 7177a40f61e2bebde11338005ec57fb5 MD5 | raw file
  1PREHOOK: query: EXPLAIN
  2SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
  3FROM
  4(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
  5 FROM src1 x JOIN src y ON (x.key = y.key)) subq
  6 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
  7PREHOOK: type: QUERY
  8POSTHOOK: query: EXPLAIN
  9SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
 10FROM
 11(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
 12 FROM src1 x JOIN src y ON (x.key = y.key)) subq
 13 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
 14POSTHOOK: type: QUERY
 15ABSTRACT SYNTAX TREE:
 16  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME src1) x) (TOK_TABREF (TOK_TABNAME src) y) (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL y) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST x))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key) key1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) value) value1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) key) key2) (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) value) value2)))) subq) (TOK_TABREF (TOK_TABNAME srcpart) z) (and (and (= (. (TOK_TABLE_OR_COL subq) key1) (. (TOK_TABLE_OR_COL z) key)) (= (. (TOK_TABLE_OR_COL z) ds) '2008-04-08')) (= (. (TOK_TABLE_OR_COL z) hr) 11)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST z))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq) key1)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL z) value)))))
 17
 18STAGE DEPENDENCIES:
 19  Stage-4 is a root stage
 20  Stage-1 depends on stages: Stage-4
 21  Stage-0 is a root stage
 22
 23STAGE PLANS:
 24  Stage: Stage-4
 25    Map Reduce Local Work
 26      Alias -> Map Local Tables:
 27        subq:x 
 28          Fetch Operator
 29            limit: -1
 30        z 
 31          Fetch Operator
 32            limit: -1
 33      Alias -> Map Local Operator Tree:
 34        subq:x 
 35          TableScan
 36            alias: x
 37            HashTable Sink Operator
 38              condition expressions:
 39                0 {key}
 40                1 
 41              handleSkewJoin: false
 42              keys:
 43                0 [Column[key]]
 44                1 [Column[key]]
 45              Position of Big Table: 1
 46        z 
 47          TableScan
 48            alias: z
 49            HashTable Sink Operator
 50              condition expressions:
 51                0 {_col0}
 52                1 {value}
 53              handleSkewJoin: false
 54              keys:
 55                0 [Column[_col0]]
 56                1 [Column[key]]
 57              Position of Big Table: 0
 58
 59  Stage: Stage-1
 60    Map Reduce
 61      Alias -> Map Operator Tree:
 62        subq:y 
 63          TableScan
 64            alias: y
 65            Map Join Operator
 66              condition map:
 67                   Inner Join 0 to 1
 68              condition expressions:
 69                0 {key}
 70                1 
 71              handleSkewJoin: false
 72              keys:
 73                0 [Column[key]]
 74                1 [Column[key]]
 75              outputColumnNames: _col0
 76              Position of Big Table: 1
 77              Select Operator
 78                expressions:
 79                      expr: _col0
 80                      type: string
 81                outputColumnNames: _col0
 82                Select Operator
 83                  expressions:
 84                        expr: _col0
 85                        type: string
 86                  outputColumnNames: _col0
 87                  Map Join Operator
 88                    condition map:
 89                         Inner Join 0 to 1
 90                    condition expressions:
 91                      0 {_col0}
 92                      1 {value}
 93                    handleSkewJoin: false
 94                    keys:
 95                      0 [Column[_col0]]
 96                      1 [Column[key]]
 97                    outputColumnNames: _col0, _col5
 98                    Position of Big Table: 0
 99                    Select Operator
100                      expressions:
101                            expr: _col0
102                            type: string
103                            expr: _col5
104                            type: string
105                      outputColumnNames: _col0, _col5
106                      Select Operator
107                        expressions:
108                              expr: _col0
109                              type: string
110                              expr: _col5
111                              type: string
112                        outputColumnNames: _col0, _col1
113                        File Output Operator
114                          compressed: false
115                          GlobalTableId: 0
116                          table:
117                              input format: org.apache.hadoop.mapred.TextInputFormat
118                              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
119      Local Work:
120        Map Reduce Local Work
121
122  Stage: Stage-0
123    Fetch Operator
124      limit: -1
125
126
127PREHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
128FROM
129(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
130 FROM src1 x JOIN src y ON (x.key = y.key)) subq
131 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
132PREHOOK: type: QUERY
133PREHOOK: Input: default@src
134PREHOOK: Input: default@src1
135PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
136PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-29_731_509300544483919463/-mr-10000
137POSTHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
138FROM
139(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
140 FROM src1 x JOIN src y ON (x.key = y.key)) subq
141 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
142POSTHOOK: type: QUERY
143POSTHOOK: Input: default@src
144POSTHOOK: Input: default@src1
145POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
146POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-29_731_509300544483919463/-mr-10000
147238	val_238
148238	val_238
149311	val_311
150311	val_311
151311	val_311
152255	val_255
153255	val_255
154278	val_278
155278	val_278
15698	val_98
15798	val_98
158401	val_401
159401	val_401
160401	val_401
161401	val_401
162401	val_401
163150	val_150
164273	val_273
165273	val_273
166273	val_273
167224	val_224
168224	val_224
169369	val_369
170369	val_369
171369	val_369
17266	val_66
173128	val_128
174128	val_128
175128	val_128
176213	val_213
177213	val_213
178146	val_146
179146	val_146
180406	val_406
181406	val_406
182406	val_406
183406	val_406
184128	val_128
185128	val_128
186128	val_128
187311	val_311
188311	val_311
189311	val_311
190213	val_213
191213	val_213
192278	val_278
193278	val_278
194311	val_311
195311	val_311
196311	val_311
19798	val_98
19898	val_98
199369	val_369
200369	val_369
201369	val_369
202238	val_238
203238	val_238
204273	val_273
205273	val_273
206273	val_273
207224	val_224
208224	val_224
209369	val_369
210369	val_369
211369	val_369
212401	val_401
213401	val_401
214401	val_401
215401	val_401
216401	val_401
217401	val_401
218401	val_401
219401	val_401
220401	val_401
221401	val_401
222128	val_128
223128	val_128
224128	val_128
225406	val_406
226406	val_406
227406	val_406
228406	val_406
229406	val_406
230406	val_406
231406	val_406
232406	val_406
233401	val_401
234401	val_401
235401	val_401
236401	val_401
237401	val_401
238255	val_255
239255	val_255
240406	val_406
241406	val_406
242406	val_406
243406	val_406
244401	val_401
245401	val_401
246401	val_401
247401	val_401
248401	val_401
249146	val_146
250146	val_146
251273	val_273
252273	val_273
253273	val_273
254PREHOOK: query: EXPLAIN 
255SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
256FROM
257(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
258 FROM src1 x JOIN src y ON (x.key = y.key)) subq
259 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11) 
260 order by subq.key1
261PREHOOK: type: QUERY
262POSTHOOK: query: EXPLAIN 
263SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
264FROM
265(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
266 FROM src1 x JOIN src y ON (x.key = y.key)) subq
267 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11) 
268 order by subq.key1
269POSTHOOK: type: QUERY
270ABSTRACT SYNTAX TREE:
271  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME src1) x) (TOK_TABREF (TOK_TABNAME src) y) (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL y) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST x))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key) key1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) value) value1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) key) key2) (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) value) value2)))) subq) (TOK_TABREF (TOK_TABNAME srcpart) z) (and (and (= (. (TOK_TABLE_OR_COL subq) key1) (. (TOK_TABLE_OR_COL z) key)) (= (. (TOK_TABLE_OR_COL z) ds) '2008-04-08')) (= (. (TOK_TABLE_OR_COL z) hr) 11)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST z))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL subq) key1)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL z) value))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (. (TOK_TABLE_OR_COL subq) key1)))))
272
273STAGE DEPENDENCIES:
274  Stage-5 is a root stage
275  Stage-1 depends on stages: Stage-5
276  Stage-2 depends on stages: Stage-1
277  Stage-0 is a root stage
278
279STAGE PLANS:
280  Stage: Stage-5
281    Map Reduce Local Work
282      Alias -> Map Local Tables:
283        subq:x 
284          Fetch Operator
285            limit: -1
286        z 
287          Fetch Operator
288            limit: -1
289      Alias -> Map Local Operator Tree:
290        subq:x 
291          TableScan
292            alias: x
293            HashTable Sink Operator
294              condition expressions:
295                0 {key}
296                1 
297              handleSkewJoin: false
298              keys:
299                0 [Column[key]]
300                1 [Column[key]]
301              Position of Big Table: 1
302        z 
303          TableScan
304            alias: z
305            HashTable Sink Operator
306              condition expressions:
307                0 {_col0}
308                1 {value}
309              handleSkewJoin: false
310              keys:
311                0 [Column[_col0]]
312                1 [Column[key]]
313              Position of Big Table: 0
314
315  Stage: Stage-1
316    Map Reduce
317      Alias -> Map Operator Tree:
318        subq:y 
319          TableScan
320            alias: y
321            Map Join Operator
322              condition map:
323                   Inner Join 0 to 1
324              condition expressions:
325                0 {key}
326                1 
327              handleSkewJoin: false
328              keys:
329                0 [Column[key]]
330                1 [Column[key]]
331              outputColumnNames: _col0
332              Position of Big Table: 1
333              Select Operator
334                expressions:
335                      expr: _col0
336                      type: string
337                outputColumnNames: _col0
338                Select Operator
339                  expressions:
340                        expr: _col0
341                        type: string
342                  outputColumnNames: _col0
343                  Map Join Operator
344                    condition map:
345                         Inner Join 0 to 1
346                    condition expressions:
347                      0 {_col0}
348                      1 {value}
349                    handleSkewJoin: false
350                    keys:
351                      0 [Column[_col0]]
352                      1 [Column[key]]
353                    outputColumnNames: _col0, _col5
354                    Position of Big Table: 0
355                    File Output Operator
356                      compressed: false
357                      GlobalTableId: 0
358                      table:
359                          input format: org.apache.hadoop.mapred.SequenceFileInputFormat
360                          output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
361      Local Work:
362        Map Reduce Local Work
363
364  Stage: Stage-2
365    Map Reduce
366      Alias -> Map Operator Tree:
367        file:/tmp/sdong/hive_2011-02-10_16-39-35_519_3270478772330421829/-mr-10002 
368          Select Operator
369            expressions:
370                  expr: _col0
371                  type: string
372                  expr: _col5
373                  type: string
374            outputColumnNames: _col0, _col5
375            Select Operator
376              expressions:
377                    expr: _col0
378                    type: string
379                    expr: _col5
380                    type: string
381              outputColumnNames: _col0, _col1
382              Reduce Output Operator
383                key expressions:
384                      expr: _col0
385                      type: string
386                sort order: +
387                tag: -1
388                value expressions:
389                      expr: _col0
390                      type: string
391                      expr: _col1
392                      type: string
393      Reduce Operator Tree:
394        Extract
395          File Output Operator
396            compressed: false
397            GlobalTableId: 0
398            table:
399                input format: org.apache.hadoop.mapred.TextInputFormat
400                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
401
402  Stage: Stage-0
403    Fetch Operator
404      limit: -1
405
406
407PREHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
408FROM
409(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2  
410 FROM src1 x JOIN src y ON (x.key = y.key)) subq 
411 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)  
412 order by subq.key1
413PREHOOK: type: QUERY
414PREHOOK: Input: default@src
415PREHOOK: Input: default@src1
416PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
417PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-35_670_1415465364269978204/-mr-10000
418POSTHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
419FROM
420(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2  
421 FROM src1 x JOIN src y ON (x.key = y.key)) subq 
422 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)  
423 order by subq.key1
424POSTHOOK: type: QUERY
425POSTHOOK: Input: default@src
426POSTHOOK: Input: default@src1
427POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
428POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-35_670_1415465364269978204/-mr-10000
429128	val_128
430128	val_128
431128	val_128
432128	val_128
433128	val_128
434128	val_128
435128	val_128
436128	val_128
437128	val_128
438146	val_146
439146	val_146
440146	val_146
441146	val_146
442150	val_150
443213	val_213
444213	val_213
445213	val_213
446213	val_213
447224	val_224
448224	val_224
449224	val_224
450224	val_224
451238	val_238
452238	val_238
453238	val_238
454238	val_238
455255	val_255
456255	val_255
457255	val_255
458255	val_255
459273	val_273
460273	val_273
461273	val_273
462273	val_273
463273	val_273
464273	val_273
465273	val_273
466273	val_273
467273	val_273
468278	val_278
469278	val_278
470278	val_278
471278	val_278
472311	val_311
473311	val_311
474311	val_311
475311	val_311
476311	val_311
477311	val_311
478311	val_311
479311	val_311
480311	val_311
481369	val_369
482369	val_369
483369	val_369
484369	val_369
485369	val_369
486369	val_369
487369	val_369
488369	val_369
489369	val_369
490401	val_401
491401	val_401
492401	val_401
493401	val_401
494401	val_401
495401	val_401
496401	val_401
497401	val_401
498401	val_401
499401	val_401
500401	val_401
501401	val_401
502401	val_401
503401	val_401
504401	val_401
505401	val_401
506401	val_401
507401	val_401
508401	val_401
509401	val_401
510401	val_401
511401	val_401
512401	val_401
513401	val_401
514401	val_401
515406	val_406
516406	val_406
517406	val_406
518406	val_406
519406	val_406
520406	val_406
521406	val_406
522406	val_406
523406	val_406
524406	val_406
525406	val_406
526406	val_406
527406	val_406
528406	val_406
529406	val_406
530406	val_406
53166	val_66
53298	val_98
53398	val_98
53498	val_98
53598	val_98