join18_multi_distinct.q.out

/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
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0

PREHOOK: query: EXPLAIN
 SELECT a.key, a.value, b.key, b.value1,  b.value2
 FROM 
  (
  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
  ) a
 FULL OUTER JOIN 
 (
  SELECT src2.key as key, count(distinct(src2.value)) AS value1, 
  count(distinct(src2.key)) AS value2
  FROM src1 src2 group by src2.key
 ) b 
 ON (a.key = b.key)
PREHOOK: type: QUERY
POSTHOOK: query: EXPLAIN
 SELECT a.key, a.value, b.key, b.value1,  b.value2
 FROM 
  (
  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
  ) a
 FULL OUTER JOIN 
 (
  SELECT src2.key as key, count(distinct(src2.value)) AS value1, 
  count(distinct(src2.key)) AS value2
  FROM src1 src2 group by src2.key
 ) b 
 ON (a.key = b.key)
POSTHOOK: type: QUERY
ABSTRACT SYNTAX TREE:
  (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)))))

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-2 depends on stages: Stage-1, Stage-3
  Stage-3 is a root stage
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        b:src2 
          TableScan
            alias: src2
            Select Operator
              expressions:
                    expr: key
                    type: string
                    expr: value
                    type: string
              outputColumnNames: key, value
              Group By Operator
                aggregations:
                      expr: count(DISTINCT value)
                      expr: count(DISTINCT key)
                bucketGroup: false
                keys:
                      expr: key
                      type: string
                      expr: value
                      type: string
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3
                Reduce Output Operator
                  key expressions:
                        expr: _col0
                        type: string
                        expr: _col1
                        type: string
                  sort order: ++
                  Map-reduce partition columns:
                        expr: _col0
                        type: string
                  tag: -1
                  value expressions:
                        expr: _col2
                        type: bigint
                        expr: _col3
                        type: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: count(DISTINCT KEY._col1:0._col0)
                expr: count(DISTINCT KEY._col1:1._col0)
          bucketGroup: false
          keys:
                expr: KEY._col1:1._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
                  expr: _col2
                  type: bigint
            outputColumnNames: _col0, _col1, _col2
            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

  Stage: Stage-2
    Map Reduce
      Alias -> Map Operator Tree:
        $INTNAME 
            Reduce Output Operator
              key expressions:
                    expr: _col0
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: _col0
                    type: string
              tag: 1
              value expressions:
                    expr: _col0
                    type: string
                    expr: _col1
                    type: bigint
                    expr: _col2
                    type: bigint
        $INTNAME1 
            Reduce Output Operator
              key expressions:
                    expr: _col0
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: _col0
                    type: string
              tag: 0
              value expressions:
                    expr: _col0
                    type: string
                    expr: _col1
                    type: bigint
      Reduce Operator Tree:
        Join Operator
          condition map:
               Outer Join 0 to 1
          condition expressions:
            0 {VALUE._col0} {VALUE._col1}
            1 {VALUE._col0} {VALUE._col1} {VALUE._col2}
          handleSkewJoin: false
          outputColumnNames: _col0, _col1, _col2, _col3, _col4
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
                  expr: _col2
                  type: string
                  expr: _col3
                  type: bigint
                  expr: _col4
                  type: bigint
            outputColumnNames: _col0, _col1, _col2, _col3, _col4
            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-3
    Map Reduce
      Alias -> Map Operator Tree:
        a:src1 
          TableScan
            alias: src1
            Select Operator
              expressions:
                    expr: key
                    type: string
                    expr: value
                    type: string
              outputColumnNames: key, value
              Group By Operator
                aggregations:
                      expr: count(value)
                bucketGroup: false
                keys:
                      expr: key
                      type: string
                mode: hash
                outputColumnNames: _col0, _col1
                Reduce Output Operator
                  key expressions:
                        expr: _col0
                        type: string
                  sort order: +
                  Map-reduce partition columns:
                        expr: _col0
                        type: string
                  tag: -1
                  value expressions:
                        expr: _col1
                        type: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: count(VALUE._col0)
          bucketGroup: false
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
            outputColumnNames: _col0, _col1
            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

  Stage: Stage-0
    Fetch Operator
      limit: -1


PREHOOK: query: SELECT a.key, a.value, b.key, b.value1,  b.value2
 FROM 
  (
  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
  ) a
 FULL OUTER JOIN 
 (
  SELECT src2.key as key, count(distinct(src2.value)) AS value1,
  count(distinct(src2.key)) AS value2
  FROM src1 src2 group by src2.key
 ) b 
 ON (a.key = b.key)
PREHOOK: type: QUERY
PREHOOK: Input: default@src
PREHOOK: Input: default@src1
PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-02-45_095_1063227124063139638/-mr-10000
POSTHOOK: query: SELECT a.key, a.value, b.key, b.value1,  b.value2
 FROM 
  (
  SELECT src1.key as key, count(src1.value) AS value FROM src src1 group by src1.key
  ) a
 FULL OUTER JOIN 
 (
  SELECT src2.key as key, count(distinct(src2.value)) AS value1,
  count(distinct(src2.key)) AS value2
  FROM src1 src2 group by src2.key
 ) b 
 ON (a.key = b.key)
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
POSTHOOK: Input: default@src1
POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_16-02-45_095_1063227124063139638/-mr-10000
NULL	NULL		1	0
NULL	NULL		1	0
NULL	NULL		1	0
NULL	NULL		1	1
0	3	NULL	NULL	NULL
10	1	NULL	NULL	NULL
100	2	NULL	NULL	NULL
103	2	NULL	NULL	NULL
104	2	NULL	NULL	NULL
105	1	NULL	NULL	NULL
11	1	NULL	NULL	NULL
111	1	NULL	NULL	NULL
113	2	NULL	NULL	NULL
114	1	NULL	NULL	NULL
116	1	NULL	NULL	NULL
118	2	NULL	NULL	NULL
119	3	NULL	NULL	NULL
12	2	NULL	NULL	NULL
120	2	NULL	NULL	NULL
125	2	NULL	NULL	NULL
126	1	NULL	NULL	NULL
128	3	128	0	1
129	2	NULL	NULL	NULL
131	1	NULL	NULL	NULL
133	1	NULL	NULL	NULL
134	2	NULL	NULL	NULL
136	1	NULL	NULL	NULL
137	2	NULL	NULL	NULL
138	4	NULL	NULL	NULL
143	1	NULL	NULL	NULL
145	1	NULL	NULL	NULL
146	2	146	0	1
149	2	NULL	NULL	NULL
15	2	NULL	NULL	NULL
150	1	150	0	1
152	2	NULL	NULL	NULL
153	1	NULL	NULL	NULL
155	1	NULL	NULL	NULL
156	1	NULL	NULL	NULL
157	1	NULL	NULL	NULL
158	1	NULL	NULL	NULL
160	1	NULL	NULL	NULL
162	1	NULL	NULL	NULL
163	1	NULL	NULL	NULL
164	2	NULL	NULL	NULL
165	2	NULL	NULL	NULL
166	1	NULL	NULL	NULL
167	3	NULL	NULL	NULL
168	1	NULL	NULL	NULL
169	4	NULL	NULL	NULL
17	1	NULL	NULL	NULL
170	1	NULL	NULL	NULL
172	2	NULL	NULL	NULL
174	2	NULL	NULL	NULL
175	2	NULL	NULL	NULL
176	2	NULL	NULL	NULL
177	1	NULL	NULL	NULL
178	1	NULL	NULL	NULL
179	2	NULL	NULL	NULL
18	2	NULL	NULL	NULL
180	1	NULL	NULL	NULL
181	1	NULL	NULL	NULL
183	1	NULL	NULL	NULL
186	1	NULL	NULL	NULL
187	3	NULL	NULL	NULL
189	1	NULL	NULL	NULL
19	1	NULL	NULL	NULL
190	1	NULL	NULL	NULL
191	2	NULL	NULL	NULL
192	1	NULL	NULL	NULL
193	3	NULL	NULL	NULL
194	1	NULL	NULL	NULL
195	2	NULL	NULL	NULL
196	1	NULL	NULL	NULL
197	2	NULL	NULL	NULL
199	3	NULL	NULL	NULL
2	1	NULL	NULL	NULL
20	1	NULL	NULL	NULL
200	2	NULL	NULL	NULL
201	1	NULL	NULL	NULL
202	1	NULL	NULL	NULL
203	2	NULL	NULL	NULL
205	2	NULL	NULL	NULL
207	2	NULL	NULL	NULL
208	3	NULL	NULL	NULL
209	2	NULL	NULL	NULL
213	2	213	0	1
214	1	NULL	NULL	NULL
216	2	NULL	NULL	NULL
217	2	NULL	NULL	NULL
218	1	NULL	NULL	NULL
219	2	NULL	NULL	NULL
221	2	NULL	NULL	NULL
222	1	NULL	NULL	NULL
223	2	NULL	NULL	NULL
224	2	224	0	1
226	1	NULL	NULL	NULL
228	1	NULL	NULL	NULL
229	2	NULL	NULL	NULL
230	5	NULL	NULL	NULL
233	2	NULL	NULL	NULL
235	1	NULL	NULL	NULL
237	2	NULL	NULL	NULL
238	2	238	0	1
239	2	NULL	NULL	NULL
24	2	NULL	NULL	NULL
241	1	NULL	NULL	NULL
242	2	NULL	NULL	NULL
244	1	NULL	NULL	NULL
247	1	NULL	NULL	NULL
248	1	NULL	NULL	NULL
249	1	NULL	NULL	NULL
252	1	NULL	NULL	NULL
255	2	255	0	1
256	2	NULL	NULL	NULL
257	1	NULL	NULL	NULL
258	1	NULL	NULL	NULL
26	2	NULL	NULL	NULL
260	1	NULL	NULL	NULL
262	1	NULL	NULL	NULL
263	1	NULL	NULL	NULL
265	2	NULL	NULL	NULL
266	1	NULL	NULL	NULL
27	1	NULL	NULL	NULL
272	2	NULL	NULL	NULL
273	3	273	0	1
274	1	NULL	NULL	NULL
275	1	NULL	NULL	NULL
277	4	NULL	NULL	NULL
278	2	278	0	1
28	1	NULL	NULL	NULL
280	2	NULL	NULL	NULL
281	2	NULL	NULL	NULL
282	2	NULL	NULL	NULL
283	1	NULL	NULL	NULL
284	1	NULL	NULL	NULL
285	1	NULL	NULL	NULL
286	1	NULL	NULL	NULL
287	1	NULL	NULL	NULL
288	2	NULL	NULL	NULL
289	1	NULL	NULL	NULL
291	1	NULL	NULL	NULL
292	1	NULL	NULL	NULL
296	1	NULL	NULL	NULL
298	3	NULL	NULL	NULL
30	1	NULL	NULL	NULL
302	1	NULL	NULL	NULL
305	1	NULL	NULL	NULL
306	1	NULL	NULL	NULL
307	2	NULL	NULL	NULL
308	1	NULL	NULL	NULL
309	2	NULL	NULL	NULL
310	1	NULL	NULL	NULL
311	3	311	0	1
315	1	NULL	NULL	NULL
316	3	NULL	NULL	NULL
317	2	NULL	NULL	NULL
318	3	NULL	NULL	NULL
321	2	NULL	NULL	NULL
322	2	NULL	NULL	NULL
323	1	NULL	NULL	NULL
325	2	NULL	NULL	NULL
327	3	NULL	NULL	NULL
33	1	NULL	NULL	NULL
331	2	NULL	NULL	NULL
332	1	NULL	NULL	NULL
333	2	NULL	NULL	NULL
335	1	NULL	NULL	NULL
336	1	NULL	NULL	NULL
338	1	NULL	NULL	NULL
339	1	NULL	NULL	NULL
34	1	NULL	NULL	NULL
341	1	NULL	NULL	NULL
342	2	NULL	NULL	NULL
344	2	NULL	NULL	NULL
345	1	NULL	NULL	NULL
348	5	NULL	NULL	NULL
35	3	NULL	NULL	NULL
351	1	NULL	NULL	NULL
353	2	NULL	NULL	NULL
356	1	NULL	NULL	NULL
360	1	NULL	NULL	NULL
362	1	NULL	NULL	NULL
364	1	NULL	NULL	NULL
365	1	NULL	NULL	NULL
366	1	NULL	NULL	NULL
367	2	NULL	NULL	NULL
368	1	NULL	NULL	NULL
369	3	369	0	1
37	2	NULL	NULL	NULL
373	1	NULL	NULL	NULL
374	1	NULL	NULL	NULL
375	1	NULL	NULL	NULL
377	1	NULL	NULL	NULL
378	1	NULL	NULL	NULL
379	1	NULL	NULL	NULL
382	2	NULL	NULL	NULL
384	3	NULL	NULL	NULL
386	1	NULL	NULL	NULL
389	1	NULL	NULL	NULL
392	1	NULL	NULL	NULL
393	1	NULL	NULL	NULL
394	1	NULL	NULL	NULL
395	2	NULL	NULL	NULL
396	3	NULL	NULL	NULL
397	2	NULL	NULL	NULL
399	2	NULL	NULL	NULL
4	1	NULL	NULL	NULL
400	1	NULL	NULL	NULL
401	5	401	0	1
402	1	NULL	NULL	NULL
403	3	NULL	NULL	NULL
404	2	NULL	NULL	NULL
406	4	406	0	1
407	1	NULL	NULL	NULL
409	3	NULL	NULL	NULL
41	1	NULL	NULL	NULL
411	1	NULL	NULL	NULL
413	2	NULL	NULL	NULL
414	2	NULL	NULL	NULL
417	3	NULL	NULL	NULL
418	1	NULL	NULL	NULL
419	1	NULL	NULL	NULL
42	2	NULL	NULL	NULL
421	1	NULL	NULL	NULL
424	2	NULL	NULL	NULL
427	1	NULL	NULL	NULL
429	2	NULL	NULL	NULL
43	1	NULL	NULL	NULL
430	3	NULL	NULL	NULL
431	3	NULL	NULL	NULL
432	1	NULL	NULL	NULL
435	1	NULL	NULL	NULL
436	1	NULL	NULL	NULL
437	1	NULL	NULL	NULL
438	3	NULL	NULL	NULL
439	2	NULL	NULL	NULL
44	1	NULL	NULL	NULL
443	1	NULL	NULL	NULL
444	1	NULL	NULL	NULL
446	1	NULL	NULL	NULL
448	1	NULL	NULL	NULL
449	1	NULL	NULL	NULL
452	1	NULL	NULL	NULL
453	1	NULL	NULL	NULL
454	3	NULL	NULL	NULL
455	1	NULL	NULL	NULL
457	1	NULL	NULL	NULL
458	2	NULL	NULL	NULL
459	2	NULL	NULL	NULL
460	1	NULL	NULL	NULL
462	2	NULL	NULL	NULL
463	2	NULL	NULL	NULL
466	3	NULL	NULL	NULL
467	1	NULL	NULL	NULL
468	4	NULL	NULL	NULL
469	5	NULL	NULL	NULL
47	1	NULL	NULL	NULL
470	1	NULL	NULL	NULL
472	1	NULL	NULL	NULL
475	1	NULL	NULL	NULL
477	1	NULL	NULL	NULL
478	2	NULL	NULL	NULL
479	1	NULL	NULL	NULL
480	3	NULL	NULL	NULL
481	1	NULL	NULL	NULL
482	1	NULL	NULL	NULL
483	1	NULL	NULL	NULL
484	1	NULL	NULL	NULL
485	1	NULL	NULL	NULL
487	1	NULL	NULL	NULL
489	4	NULL	NULL	NULL
490	1	NULL	NULL	NULL
491	1	NULL	NULL	NULL
492	2	NULL	NULL	NULL
493	1	NULL	NULL	NULL
494	1	NULL	NULL	NULL
495	1	NULL	NULL	NULL
496	1	NULL	NULL	NULL
497	1	NULL	NULL	NULL
498	3	NULL	NULL	NULL
5	3	NULL	NULL	NULL
51	2	NULL	NULL	NULL
53	1	NULL	NULL	NULL
54	1	NULL	NULL	NULL
57	1	NULL	NULL	NULL
58	2	NULL	NULL	NULL
64	1	NULL	NULL	NULL
65	1	NULL	NULL	NULL
66	1	66	0	1
67	2	NULL	NULL	NULL
69	1	NULL	NULL	NULL
70	3	NULL	NULL	NULL
72	2	NULL	NULL	NULL
74	1	NULL	NULL	NULL
76	2	NULL	NULL	NULL
77	1	NULL	NULL	NULL
78	1	NULL	NULL	NULL
8	1	NULL	NULL	NULL
80	1	NULL	NULL	NULL
82	1	NULL	NULL	NULL
83	2	NULL	NULL	NULL
84	2	NULL	NULL	NULL
85	1	NULL	NULL	NULL
86	1	NULL	NULL	NULL
87	1	NULL	NULL	NULL
9	1	NULL	NULL	NULL
90	3	NULL	NULL	NULL
92	1	NULL	NULL	NULL
95	2	NULL	NULL	NULL
96	1	NULL	NULL	NULL
97	2	NULL	NULL	NULL
98	2	98	0	1
NULL	NULL	val_146	1	0
NULL	NULL	val_150	1	0
NULL	NULL	val_165	1	0
NULL	NULL	val_193	1	0
NULL	NULL	val_213	1	0
NULL	NULL	val_238	1	0
NULL	NULL	val_255	1	0
NULL	NULL	val_265	1	0
NULL	NULL	val_27	1	0
NULL	NULL	val_273	1	0
NULL	NULL	val_278	1	0
NULL	NULL	val_311	1	0
NULL	NULL	val_401	1	0
NULL	NULL	val_406	1	0
NULL	NULL	val_409	1	0
NULL	NULL	val_484	1	0
NULL	NULL	val_66	1	0
NULL	NULL	val_98	1	0