PageRenderTime 23ms CodeModel.GetById 14ms app.highlight 6ms RepoModel.GetById 1ms app.codeStats 0ms

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

#
text | 596 lines | 589 code | 7 blank | 0 comment | 0 complexity | 55f48f72dcadc2feae67cfe9f6c2c5ec MD5 | raw file
  1PREHOOK: query: EXPLAIN
  2 SELECT a.key, a.value, b.key, b.value1,  b.value2
  3 FROM 
  4  (
  5  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
  6  ) a
  7 FULL OUTER JOIN 
  8 (
  9  SELECT src2.key as key, count(distinct(src2.value)) AS value1, 
 10  count(distinct(src2.key)) AS value2
 11  FROM src1 src2 group by src2.key
 12 ) b 
 13 ON (a.key = b.key)
 14PREHOOK: type: QUERY
 15POSTHOOK: query: EXPLAIN
 16 SELECT a.key, a.value, b.key, b.value1,  b.value2
 17 FROM 
 18  (
 19  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
 20  ) a
 21 FULL OUTER JOIN 
 22 (
 23  SELECT src2.key as key, count(distinct(src2.value)) AS value1, 
 24  count(distinct(src2.key)) AS value2
 25  FROM src1 src2 group by src2.key
 26 ) b 
 27 ON (a.key = b.key)
 28POSTHOOK: type: QUERY
 29ABSTRACT SYNTAX TREE:
 30  (TOK_QUERY (TOK_FROM (TOK_FULLOUTERJOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) key) key) (TOK_SELEXPR (TOK_FUNCTION count (. (TOK_TABLE_OR_COL src1) value)) value)) (TOK_GROUPBY (. (TOK_TABLE_OR_COL src1) key)))) a) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src1) src2)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) key) key) (TOK_SELEXPR (TOK_FUNCTIONDI count (. (TOK_TABLE_OR_COL src2) value)) value1) (TOK_SELEXPR (TOK_FUNCTIONDI count (. (TOK_TABLE_OR_COL src2) key)) value2)) (TOK_GROUPBY (. (TOK_TABLE_OR_COL src2) key)))) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) value)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) value1)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) value2)))))
 31
 32STAGE DEPENDENCIES:
 33  Stage-1 is a root stage
 34  Stage-2 depends on stages: Stage-1, Stage-3
 35  Stage-3 is a root stage
 36  Stage-0 is a root stage
 37
 38STAGE PLANS:
 39  Stage: Stage-1
 40    Map Reduce
 41      Alias -> Map Operator Tree:
 42        b:src2 
 43          TableScan
 44            alias: src2
 45            Select Operator
 46              expressions:
 47                    expr: key
 48                    type: string
 49                    expr: value
 50                    type: string
 51              outputColumnNames: key, value
 52              Group By Operator
 53                aggregations:
 54                      expr: count(DISTINCT value)
 55                      expr: count(DISTINCT key)
 56                bucketGroup: false
 57                keys:
 58                      expr: key
 59                      type: string
 60                      expr: value
 61                      type: string
 62                mode: hash
 63                outputColumnNames: _col0, _col1, _col2, _col3
 64                Reduce Output Operator
 65                  key expressions:
 66                        expr: _col0
 67                        type: string
 68                        expr: _col1
 69                        type: string
 70                  sort order: ++
 71                  Map-reduce partition columns:
 72                        expr: _col0
 73                        type: string
 74                  tag: -1
 75                  value expressions:
 76                        expr: _col2
 77                        type: bigint
 78                        expr: _col3
 79                        type: bigint
 80      Reduce Operator Tree:
 81        Group By Operator
 82          aggregations:
 83                expr: count(DISTINCT KEY._col1:0._col0)
 84                expr: count(DISTINCT KEY._col1:1._col0)
 85          bucketGroup: false
 86          keys:
 87                expr: KEY._col1:1._col0
 88                type: string
 89          mode: mergepartial
 90          outputColumnNames: _col0, _col1, _col2
 91          Select Operator
 92            expressions:
 93                  expr: _col0
 94                  type: string
 95                  expr: _col1
 96                  type: bigint
 97                  expr: _col2
 98                  type: bigint
 99            outputColumnNames: _col0, _col1, _col2
100            File Output Operator
101              compressed: false
102              GlobalTableId: 0
103              table:
104                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
105                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
106
107  Stage: Stage-2
108    Map Reduce
109      Alias -> Map Operator Tree:
110        $INTNAME 
111            Reduce Output Operator
112              key expressions:
113                    expr: _col0
114                    type: string
115              sort order: +
116              Map-reduce partition columns:
117                    expr: _col0
118                    type: string
119              tag: 1
120              value expressions:
121                    expr: _col0
122                    type: string
123                    expr: _col1
124                    type: bigint
125                    expr: _col2
126                    type: bigint
127        $INTNAME1 
128            Reduce Output Operator
129              key expressions:
130                    expr: _col0
131                    type: string
132              sort order: +
133              Map-reduce partition columns:
134                    expr: _col0
135                    type: string
136              tag: 0
137              value expressions:
138                    expr: _col0
139                    type: string
140                    expr: _col1
141                    type: bigint
142      Reduce Operator Tree:
143        Join Operator
144          condition map:
145               Outer Join 0 to 1
146          condition expressions:
147            0 {VALUE._col0} {VALUE._col1}
148            1 {VALUE._col0} {VALUE._col1} {VALUE._col2}
149          handleSkewJoin: false
150          outputColumnNames: _col0, _col1, _col2, _col3, _col4
151          Select Operator
152            expressions:
153                  expr: _col0
154                  type: string
155                  expr: _col1
156                  type: bigint
157                  expr: _col2
158                  type: string
159                  expr: _col3
160                  type: bigint
161                  expr: _col4
162                  type: bigint
163            outputColumnNames: _col0, _col1, _col2, _col3, _col4
164            File Output Operator
165              compressed: false
166              GlobalTableId: 0
167              table:
168                  input format: org.apache.hadoop.mapred.TextInputFormat
169                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
170
171  Stage: Stage-3
172    Map Reduce
173      Alias -> Map Operator Tree:
174        a:src1 
175          TableScan
176            alias: src1
177            Select Operator
178              expressions:
179                    expr: key
180                    type: string
181                    expr: value
182                    type: string
183              outputColumnNames: key, value
184              Group By Operator
185                aggregations:
186                      expr: count(value)
187                bucketGroup: false
188                keys:
189                      expr: key
190                      type: string
191                mode: hash
192                outputColumnNames: _col0, _col1
193                Reduce Output Operator
194                  key expressions:
195                        expr: _col0
196                        type: string
197                  sort order: +
198                  Map-reduce partition columns:
199                        expr: _col0
200                        type: string
201                  tag: -1
202                  value expressions:
203                        expr: _col1
204                        type: bigint
205      Reduce Operator Tree:
206        Group By Operator
207          aggregations:
208                expr: count(VALUE._col0)
209          bucketGroup: false
210          keys:
211                expr: KEY._col0
212                type: string
213          mode: mergepartial
214          outputColumnNames: _col0, _col1
215          Select Operator
216            expressions:
217                  expr: _col0
218                  type: string
219                  expr: _col1
220                  type: bigint
221            outputColumnNames: _col0, _col1
222            File Output Operator
223              compressed: false
224              GlobalTableId: 0
225              table:
226                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
227                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
228
229  Stage: Stage-0
230    Fetch Operator
231      limit: -1
232
233
234PREHOOK: query: SELECT a.key, a.value, b.key, b.value1,  b.value2
235 FROM 
236  (
237  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
238  ) a
239 FULL OUTER JOIN 
240 (
241  SELECT src2.key as key, count(distinct(src2.value)) AS value1,
242  count(distinct(src2.key)) AS value2
243  FROM src1 src2 group by src2.key
244 ) b 
245 ON (a.key = b.key)
246PREHOOK: type: QUERY
247PREHOOK: Input: default@src
248PREHOOK: Input: default@src1
249PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-02-45_095_1063227124063139638/-mr-10000
250POSTHOOK: query: SELECT a.key, a.value, b.key, b.value1,  b.value2
251 FROM 
252  (
253  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
254  ) a
255 FULL OUTER JOIN 
256 (
257  SELECT src2.key as key, count(distinct(src2.value)) AS value1,
258  count(distinct(src2.key)) AS value2
259  FROM src1 src2 group by src2.key
260 ) b 
261 ON (a.key = b.key)
262POSTHOOK: type: QUERY
263POSTHOOK: Input: default@src
264POSTHOOK: Input: default@src1
265POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-02-45_095_1063227124063139638/-mr-10000
266NULL	NULL		1	0
267NULL	NULL		1	0
268NULL	NULL		1	0
269NULL	NULL		1	1
2700	3	NULL	NULL	NULL
27110	1	NULL	NULL	NULL
272100	2	NULL	NULL	NULL
273103	2	NULL	NULL	NULL
274104	2	NULL	NULL	NULL
275105	1	NULL	NULL	NULL
27611	1	NULL	NULL	NULL
277111	1	NULL	NULL	NULL
278113	2	NULL	NULL	NULL
279114	1	NULL	NULL	NULL
280116	1	NULL	NULL	NULL
281118	2	NULL	NULL	NULL
282119	3	NULL	NULL	NULL
28312	2	NULL	NULL	NULL
284120	2	NULL	NULL	NULL
285125	2	NULL	NULL	NULL
286126	1	NULL	NULL	NULL
287128	3	128	0	1
288129	2	NULL	NULL	NULL
289131	1	NULL	NULL	NULL
290133	1	NULL	NULL	NULL
291134	2	NULL	NULL	NULL
292136	1	NULL	NULL	NULL
293137	2	NULL	NULL	NULL
294138	4	NULL	NULL	NULL
295143	1	NULL	NULL	NULL
296145	1	NULL	NULL	NULL
297146	2	146	0	1
298149	2	NULL	NULL	NULL
29915	2	NULL	NULL	NULL
300150	1	150	0	1
301152	2	NULL	NULL	NULL
302153	1	NULL	NULL	NULL
303155	1	NULL	NULL	NULL
304156	1	NULL	NULL	NULL
305157	1	NULL	NULL	NULL
306158	1	NULL	NULL	NULL
307160	1	NULL	NULL	NULL
308162	1	NULL	NULL	NULL
309163	1	NULL	NULL	NULL
310164	2	NULL	NULL	NULL
311165	2	NULL	NULL	NULL
312166	1	NULL	NULL	NULL
313167	3	NULL	NULL	NULL
314168	1	NULL	NULL	NULL
315169	4	NULL	NULL	NULL
31617	1	NULL	NULL	NULL
317170	1	NULL	NULL	NULL
318172	2	NULL	NULL	NULL
319174	2	NULL	NULL	NULL
320175	2	NULL	NULL	NULL
321176	2	NULL	NULL	NULL
322177	1	NULL	NULL	NULL
323178	1	NULL	NULL	NULL
324179	2	NULL	NULL	NULL
32518	2	NULL	NULL	NULL
326180	1	NULL	NULL	NULL
327181	1	NULL	NULL	NULL
328183	1	NULL	NULL	NULL
329186	1	NULL	NULL	NULL
330187	3	NULL	NULL	NULL
331189	1	NULL	NULL	NULL
33219	1	NULL	NULL	NULL
333190	1	NULL	NULL	NULL
334191	2	NULL	NULL	NULL
335192	1	NULL	NULL	NULL
336193	3	NULL	NULL	NULL
337194	1	NULL	NULL	NULL
338195	2	NULL	NULL	NULL
339196	1	NULL	NULL	NULL
340197	2	NULL	NULL	NULL
341199	3	NULL	NULL	NULL
3422	1	NULL	NULL	NULL
34320	1	NULL	NULL	NULL
344200	2	NULL	NULL	NULL
345201	1	NULL	NULL	NULL
346202	1	NULL	NULL	NULL
347203	2	NULL	NULL	NULL
348205	2	NULL	NULL	NULL
349207	2	NULL	NULL	NULL
350208	3	NULL	NULL	NULL
351209	2	NULL	NULL	NULL
352213	2	213	0	1
353214	1	NULL	NULL	NULL
354216	2	NULL	NULL	NULL
355217	2	NULL	NULL	NULL
356218	1	NULL	NULL	NULL
357219	2	NULL	NULL	NULL
358221	2	NULL	NULL	NULL
359222	1	NULL	NULL	NULL
360223	2	NULL	NULL	NULL
361224	2	224	0	1
362226	1	NULL	NULL	NULL
363228	1	NULL	NULL	NULL
364229	2	NULL	NULL	NULL
365230	5	NULL	NULL	NULL
366233	2	NULL	NULL	NULL
367235	1	NULL	NULL	NULL
368237	2	NULL	NULL	NULL
369238	2	238	0	1
370239	2	NULL	NULL	NULL
37124	2	NULL	NULL	NULL
372241	1	NULL	NULL	NULL
373242	2	NULL	NULL	NULL
374244	1	NULL	NULL	NULL
375247	1	NULL	NULL	NULL
376248	1	NULL	NULL	NULL
377249	1	NULL	NULL	NULL
378252	1	NULL	NULL	NULL
379255	2	255	0	1
380256	2	NULL	NULL	NULL
381257	1	NULL	NULL	NULL
382258	1	NULL	NULL	NULL
38326	2	NULL	NULL	NULL
384260	1	NULL	NULL	NULL
385262	1	NULL	NULL	NULL
386263	1	NULL	NULL	NULL
387265	2	NULL	NULL	NULL
388266	1	NULL	NULL	NULL
38927	1	NULL	NULL	NULL
390272	2	NULL	NULL	NULL
391273	3	273	0	1
392274	1	NULL	NULL	NULL
393275	1	NULL	NULL	NULL
394277	4	NULL	NULL	NULL
395278	2	278	0	1
39628	1	NULL	NULL	NULL
397280	2	NULL	NULL	NULL
398281	2	NULL	NULL	NULL
399282	2	NULL	NULL	NULL
400283	1	NULL	NULL	NULL
401284	1	NULL	NULL	NULL
402285	1	NULL	NULL	NULL
403286	1	NULL	NULL	NULL
404287	1	NULL	NULL	NULL
405288	2	NULL	NULL	NULL
406289	1	NULL	NULL	NULL
407291	1	NULL	NULL	NULL
408292	1	NULL	NULL	NULL
409296	1	NULL	NULL	NULL
410298	3	NULL	NULL	NULL
41130	1	NULL	NULL	NULL
412302	1	NULL	NULL	NULL
413305	1	NULL	NULL	NULL
414306	1	NULL	NULL	NULL
415307	2	NULL	NULL	NULL
416308	1	NULL	NULL	NULL
417309	2	NULL	NULL	NULL
418310	1	NULL	NULL	NULL
419311	3	311	0	1
420315	1	NULL	NULL	NULL
421316	3	NULL	NULL	NULL
422317	2	NULL	NULL	NULL
423318	3	NULL	NULL	NULL
424321	2	NULL	NULL	NULL
425322	2	NULL	NULL	NULL
426323	1	NULL	NULL	NULL
427325	2	NULL	NULL	NULL
428327	3	NULL	NULL	NULL
42933	1	NULL	NULL	NULL
430331	2	NULL	NULL	NULL
431332	1	NULL	NULL	NULL
432333	2	NULL	NULL	NULL
433335	1	NULL	NULL	NULL
434336	1	NULL	NULL	NULL
435338	1	NULL	NULL	NULL
436339	1	NULL	NULL	NULL
43734	1	NULL	NULL	NULL
438341	1	NULL	NULL	NULL
439342	2	NULL	NULL	NULL
440344	2	NULL	NULL	NULL
441345	1	NULL	NULL	NULL
442348	5	NULL	NULL	NULL
44335	3	NULL	NULL	NULL
444351	1	NULL	NULL	NULL
445353	2	NULL	NULL	NULL
446356	1	NULL	NULL	NULL
447360	1	NULL	NULL	NULL
448362	1	NULL	NULL	NULL
449364	1	NULL	NULL	NULL
450365	1	NULL	NULL	NULL
451366	1	NULL	NULL	NULL
452367	2	NULL	NULL	NULL
453368	1	NULL	NULL	NULL
454369	3	369	0	1
45537	2	NULL	NULL	NULL
456373	1	NULL	NULL	NULL
457374	1	NULL	NULL	NULL
458375	1	NULL	NULL	NULL
459377	1	NULL	NULL	NULL
460378	1	NULL	NULL	NULL
461379	1	NULL	NULL	NULL
462382	2	NULL	NULL	NULL
463384	3	NULL	NULL	NULL
464386	1	NULL	NULL	NULL
465389	1	NULL	NULL	NULL
466392	1	NULL	NULL	NULL
467393	1	NULL	NULL	NULL
468394	1	NULL	NULL	NULL
469395	2	NULL	NULL	NULL
470396	3	NULL	NULL	NULL
471397	2	NULL	NULL	NULL
472399	2	NULL	NULL	NULL
4734	1	NULL	NULL	NULL
474400	1	NULL	NULL	NULL
475401	5	401	0	1
476402	1	NULL	NULL	NULL
477403	3	NULL	NULL	NULL
478404	2	NULL	NULL	NULL
479406	4	406	0	1
480407	1	NULL	NULL	NULL
481409	3	NULL	NULL	NULL
48241	1	NULL	NULL	NULL
483411	1	NULL	NULL	NULL
484413	2	NULL	NULL	NULL
485414	2	NULL	NULL	NULL
486417	3	NULL	NULL	NULL
487418	1	NULL	NULL	NULL
488419	1	NULL	NULL	NULL
48942	2	NULL	NULL	NULL
490421	1	NULL	NULL	NULL
491424	2	NULL	NULL	NULL
492427	1	NULL	NULL	NULL
493429	2	NULL	NULL	NULL
49443	1	NULL	NULL	NULL
495430	3	NULL	NULL	NULL
496431	3	NULL	NULL	NULL
497432	1	NULL	NULL	NULL
498435	1	NULL	NULL	NULL
499436	1	NULL	NULL	NULL
500437	1	NULL	NULL	NULL
501438	3	NULL	NULL	NULL
502439	2	NULL	NULL	NULL
50344	1	NULL	NULL	NULL
504443	1	NULL	NULL	NULL
505444	1	NULL	NULL	NULL
506446	1	NULL	NULL	NULL
507448	1	NULL	NULL	NULL
508449	1	NULL	NULL	NULL
509452	1	NULL	NULL	NULL
510453	1	NULL	NULL	NULL
511454	3	NULL	NULL	NULL
512455	1	NULL	NULL	NULL
513457	1	NULL	NULL	NULL
514458	2	NULL	NULL	NULL
515459	2	NULL	NULL	NULL
516460	1	NULL	NULL	NULL
517462	2	NULL	NULL	NULL
518463	2	NULL	NULL	NULL
519466	3	NULL	NULL	NULL
520467	1	NULL	NULL	NULL
521468	4	NULL	NULL	NULL
522469	5	NULL	NULL	NULL
52347	1	NULL	NULL	NULL
524470	1	NULL	NULL	NULL
525472	1	NULL	NULL	NULL
526475	1	NULL	NULL	NULL
527477	1	NULL	NULL	NULL
528478	2	NULL	NULL	NULL
529479	1	NULL	NULL	NULL
530480	3	NULL	NULL	NULL
531481	1	NULL	NULL	NULL
532482	1	NULL	NULL	NULL
533483	1	NULL	NULL	NULL
534484	1	NULL	NULL	NULL
535485	1	NULL	NULL	NULL
536487	1	NULL	NULL	NULL
537489	4	NULL	NULL	NULL
538490	1	NULL	NULL	NULL
539491	1	NULL	NULL	NULL
540492	2	NULL	NULL	NULL
541493	1	NULL	NULL	NULL
542494	1	NULL	NULL	NULL
543495	1	NULL	NULL	NULL
544496	1	NULL	NULL	NULL
545497	1	NULL	NULL	NULL
546498	3	NULL	NULL	NULL
5475	3	NULL	NULL	NULL
54851	2	NULL	NULL	NULL
54953	1	NULL	NULL	NULL
55054	1	NULL	NULL	NULL
55157	1	NULL	NULL	NULL
55258	2	NULL	NULL	NULL
55364	1	NULL	NULL	NULL
55465	1	NULL	NULL	NULL
55566	1	66	0	1
55667	2	NULL	NULL	NULL
55769	1	NULL	NULL	NULL
55870	3	NULL	NULL	NULL
55972	2	NULL	NULL	NULL
56074	1	NULL	NULL	NULL
56176	2	NULL	NULL	NULL
56277	1	NULL	NULL	NULL
56378	1	NULL	NULL	NULL
5648	1	NULL	NULL	NULL
56580	1	NULL	NULL	NULL
56682	1	NULL	NULL	NULL
56783	2	NULL	NULL	NULL
56884	2	NULL	NULL	NULL
56985	1	NULL	NULL	NULL
57086	1	NULL	NULL	NULL
57187	1	NULL	NULL	NULL
5729	1	NULL	NULL	NULL
57390	3	NULL	NULL	NULL
57492	1	NULL	NULL	NULL
57595	2	NULL	NULL	NULL
57696	1	NULL	NULL	NULL
57797	2	NULL	NULL	NULL
57898	2	98	0	1
579NULL	NULL	val_146	1	0
580NULL	NULL	val_150	1	0
581NULL	NULL	val_165	1	0
582NULL	NULL	val_193	1	0
583NULL	NULL	val_213	1	0
584NULL	NULL	val_238	1	0
585NULL	NULL	val_255	1	0
586NULL	NULL	val_265	1	0
587NULL	NULL	val_27	1	0
588NULL	NULL	val_273	1	0
589NULL	NULL	val_278	1	0
590NULL	NULL	val_311	1	0
591NULL	NULL	val_401	1	0
592NULL	NULL	val_406	1	0
593NULL	NULL	val_409	1	0
594NULL	NULL	val_484	1	0
595NULL	NULL	val_66	1	0
596NULL	NULL	val_98	1	0