PageRenderTime 15ms CodeModel.GetById 7ms app.highlight 4ms RepoModel.GetById 1ms app.codeStats 0ms

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

#
text | 551 lines | 538 code | 13 blank | 0 comment | 0 complexity | 65e697372ea2f7b9a7aadaf84dc3cde7 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            Filter Operator
 50              predicate:
 51                  expr: (ds = '2008-04-08')
 52                  type: boolean
 53              Filter Operator
 54                predicate:
 55                    expr: (hr = 11)
 56                    type: boolean
 57                HashTable Sink Operator
 58                  condition expressions:
 59                    0 {_col0}
 60                    1 {value}
 61                  handleSkewJoin: false
 62                  keys:
 63                    0 [Column[_col0]]
 64                    1 [Column[key]]
 65                  Position of Big Table: 0
 66
 67  Stage: Stage-1
 68    Map Reduce
 69      Alias -> Map Operator Tree:
 70        subq:y 
 71          TableScan
 72            alias: y
 73            Map Join Operator
 74              condition map:
 75                   Inner Join 0 to 1
 76              condition expressions:
 77                0 {key}
 78                1 
 79              handleSkewJoin: false
 80              keys:
 81                0 [Column[key]]
 82                1 [Column[key]]
 83              outputColumnNames: _col0
 84              Position of Big Table: 1
 85              Select Operator
 86                expressions:
 87                      expr: _col0
 88                      type: string
 89                outputColumnNames: _col0
 90                Select Operator
 91                  expressions:
 92                        expr: _col0
 93                        type: string
 94                  outputColumnNames: _col0
 95                  Map Join Operator
 96                    condition map:
 97                         Inner Join 0 to 1
 98                    condition expressions:
 99                      0 {_col0}
100                      1 {value}
101                    handleSkewJoin: false
102                    keys:
103                      0 [Column[_col0]]
104                      1 [Column[key]]
105                    outputColumnNames: _col0, _col5
106                    Position of Big Table: 0
107                    Select Operator
108                      expressions:
109                            expr: _col0
110                            type: string
111                            expr: _col5
112                            type: string
113                      outputColumnNames: _col0, _col5
114                      Select Operator
115                        expressions:
116                              expr: _col0
117                              type: string
118                              expr: _col5
119                              type: string
120                        outputColumnNames: _col0, _col1
121                        File Output Operator
122                          compressed: false
123                          GlobalTableId: 0
124                          table:
125                              input format: org.apache.hadoop.mapred.TextInputFormat
126                              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
127      Local Work:
128        Map Reduce Local Work
129
130  Stage: Stage-0
131    Fetch Operator
132      limit: -1
133
134
135PREHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
136FROM
137(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
138 FROM src1 x JOIN src y ON (x.key = y.key)) subq
139 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
140PREHOOK: type: QUERY
141PREHOOK: Input: default@src
142PREHOOK: Input: default@src1
143PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
144PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-29_731_509300544483919463/-mr-10000
145POSTHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
146FROM
147(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
148 FROM src1 x JOIN src y ON (x.key = y.key)) subq
149 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)
150POSTHOOK: type: QUERY
151POSTHOOK: Input: default@src
152POSTHOOK: Input: default@src1
153POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
154POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-29_731_509300544483919463/-mr-10000
155238	val_238
156238	val_238
157311	val_311
158311	val_311
159311	val_311
160255	val_255
161255	val_255
162278	val_278
163278	val_278
16498	val_98
16598	val_98
166401	val_401
167401	val_401
168401	val_401
169401	val_401
170401	val_401
171150	val_150
172273	val_273
173273	val_273
174273	val_273
175224	val_224
176224	val_224
177369	val_369
178369	val_369
179369	val_369
18066	val_66
181128	val_128
182128	val_128
183128	val_128
184213	val_213
185213	val_213
186146	val_146
187146	val_146
188406	val_406
189406	val_406
190406	val_406
191406	val_406
192128	val_128
193128	val_128
194128	val_128
195311	val_311
196311	val_311
197311	val_311
198213	val_213
199213	val_213
200278	val_278
201278	val_278
202311	val_311
203311	val_311
204311	val_311
20598	val_98
20698	val_98
207369	val_369
208369	val_369
209369	val_369
210238	val_238
211238	val_238
212273	val_273
213273	val_273
214273	val_273
215224	val_224
216224	val_224
217369	val_369
218369	val_369
219369	val_369
220401	val_401
221401	val_401
222401	val_401
223401	val_401
224401	val_401
225401	val_401
226401	val_401
227401	val_401
228401	val_401
229401	val_401
230128	val_128
231128	val_128
232128	val_128
233406	val_406
234406	val_406
235406	val_406
236406	val_406
237406	val_406
238406	val_406
239406	val_406
240406	val_406
241401	val_401
242401	val_401
243401	val_401
244401	val_401
245401	val_401
246255	val_255
247255	val_255
248406	val_406
249406	val_406
250406	val_406
251406	val_406
252401	val_401
253401	val_401
254401	val_401
255401	val_401
256401	val_401
257146	val_146
258146	val_146
259273	val_273
260273	val_273
261273	val_273
262PREHOOK: 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
269PREHOOK: type: QUERY
270POSTHOOK: query: EXPLAIN 
271SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
272FROM
273(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2 
274 FROM src1 x JOIN src y ON (x.key = y.key)) subq
275 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11) 
276 order by subq.key1
277POSTHOOK: type: QUERY
278ABSTRACT SYNTAX TREE:
279  (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)))))
280
281STAGE DEPENDENCIES:
282  Stage-5 is a root stage
283  Stage-1 depends on stages: Stage-5
284  Stage-2 depends on stages: Stage-1
285  Stage-0 is a root stage
286
287STAGE PLANS:
288  Stage: Stage-5
289    Map Reduce Local Work
290      Alias -> Map Local Tables:
291        subq:x 
292          Fetch Operator
293            limit: -1
294        z 
295          Fetch Operator
296            limit: -1
297      Alias -> Map Local Operator Tree:
298        subq:x 
299          TableScan
300            alias: x
301            HashTable Sink Operator
302              condition expressions:
303                0 {key}
304                1 
305              handleSkewJoin: false
306              keys:
307                0 [Column[key]]
308                1 [Column[key]]
309              Position of Big Table: 1
310        z 
311          TableScan
312            alias: z
313            Filter Operator
314              predicate:
315                  expr: (ds = '2008-04-08')
316                  type: boolean
317              Filter Operator
318                predicate:
319                    expr: (hr = 11)
320                    type: boolean
321                HashTable Sink Operator
322                  condition expressions:
323                    0 {_col0}
324                    1 {value}
325                  handleSkewJoin: false
326                  keys:
327                    0 [Column[_col0]]
328                    1 [Column[key]]
329                  Position of Big Table: 0
330
331  Stage: Stage-1
332    Map Reduce
333      Alias -> Map Operator Tree:
334        subq:y 
335          TableScan
336            alias: y
337            Map Join Operator
338              condition map:
339                   Inner Join 0 to 1
340              condition expressions:
341                0 {key}
342                1 
343              handleSkewJoin: false
344              keys:
345                0 [Column[key]]
346                1 [Column[key]]
347              outputColumnNames: _col0
348              Position of Big Table: 1
349              Select Operator
350                expressions:
351                      expr: _col0
352                      type: string
353                outputColumnNames: _col0
354                Select Operator
355                  expressions:
356                        expr: _col0
357                        type: string
358                  outputColumnNames: _col0
359                  Map Join Operator
360                    condition map:
361                         Inner Join 0 to 1
362                    condition expressions:
363                      0 {_col0}
364                      1 {value}
365                    handleSkewJoin: false
366                    keys:
367                      0 [Column[_col0]]
368                      1 [Column[key]]
369                    outputColumnNames: _col0, _col5
370                    Position of Big Table: 0
371                    File Output Operator
372                      compressed: false
373                      GlobalTableId: 0
374                      table:
375                          input format: org.apache.hadoop.mapred.SequenceFileInputFormat
376                          output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
377      Local Work:
378        Map Reduce Local Work
379
380  Stage: Stage-2
381    Map Reduce
382      Alias -> Map Operator Tree:
383        file:/tmp/sdong/hive_2011-02-10_16-39-35_519_3270478772330421829/-mr-10002 
384          Select Operator
385            expressions:
386                  expr: _col0
387                  type: string
388                  expr: _col5
389                  type: string
390            outputColumnNames: _col0, _col5
391            Select Operator
392              expressions:
393                    expr: _col0
394                    type: string
395                    expr: _col5
396                    type: string
397              outputColumnNames: _col0, _col1
398              Reduce Output Operator
399                key expressions:
400                      expr: _col0
401                      type: string
402                sort order: +
403                tag: -1
404                value expressions:
405                      expr: _col0
406                      type: string
407                      expr: _col1
408                      type: string
409      Reduce Operator Tree:
410        Extract
411          File Output Operator
412            compressed: false
413            GlobalTableId: 0
414            table:
415                input format: org.apache.hadoop.mapred.TextInputFormat
416                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
417
418  Stage: Stage-0
419    Fetch Operator
420      limit: -1
421
422
423PREHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
424FROM
425(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2  
426 FROM src1 x JOIN src y ON (x.key = y.key)) subq 
427 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)  
428 order by subq.key1
429PREHOOK: type: QUERY
430PREHOOK: Input: default@src
431PREHOOK: Input: default@src1
432PREHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
433PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-35_670_1415465364269978204/-mr-10000
434POSTHOOK: query: SELECT /*+ MAPJOIN(z) */ subq.key1, z.value
435FROM
436(SELECT /*+ MAPJOIN(x) */ x.key as key1, x.value as value1, y.key as key2, y.value as value2  
437 FROM src1 x JOIN src y ON (x.key = y.key)) subq 
438 JOIN srcpart z ON (subq.key1 = z.key and z.ds='2008-04-08' and z.hr=11)  
439 order by subq.key1
440POSTHOOK: type: QUERY
441POSTHOOK: Input: default@src
442POSTHOOK: Input: default@src1
443POSTHOOK: Input: default@srcpart@ds=2008-04-08/hr=11
444POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-39-35_670_1415465364269978204/-mr-10000
445128	val_128
446128	val_128
447128	val_128
448128	val_128
449128	val_128
450128	val_128
451128	val_128
452128	val_128
453128	val_128
454146	val_146
455146	val_146
456146	val_146
457146	val_146
458150	val_150
459213	val_213
460213	val_213
461213	val_213
462213	val_213
463224	val_224
464224	val_224
465224	val_224
466224	val_224
467238	val_238
468238	val_238
469238	val_238
470238	val_238
471255	val_255
472255	val_255
473255	val_255
474255	val_255
475273	val_273
476273	val_273
477273	val_273
478273	val_273
479273	val_273
480273	val_273
481273	val_273
482273	val_273
483273	val_273
484278	val_278
485278	val_278
486278	val_278
487278	val_278
488311	val_311
489311	val_311
490311	val_311
491311	val_311
492311	val_311
493311	val_311
494311	val_311
495311	val_311
496311	val_311
497369	val_369
498369	val_369
499369	val_369
500369	val_369
501369	val_369
502369	val_369
503369	val_369
504369	val_369
505369	val_369
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
515401	val_401
516401	val_401
517401	val_401
518401	val_401
519401	val_401
520401	val_401
521401	val_401
522401	val_401
523401	val_401
524401	val_401
525401	val_401
526401	val_401
527401	val_401
528401	val_401
529401	val_401
530401	val_401
531406	val_406
532406	val_406
533406	val_406
534406	val_406
535406	val_406
536406	val_406
537406	val_406
538406	val_406
539406	val_406
540406	val_406
541406	val_406
542406	val_406
543406	val_406
544406	val_406
545406	val_406
546406	val_406
54766	val_66
54898	val_98
54998	val_98
55098	val_98
55198	val_98