PREHOOK: query: -- SORT_QUERY_RESULTS

-- data setup
DROP TABLE IF EXISTS part_subq
PREHOOK: type: DROPTABLE
POSTHOOK: query: -- SORT_QUERY_RESULTS

-- data setup
DROP TABLE IF EXISTS part_subq
POSTHOOK: type: DROPTABLE
PREHOOK: query: CREATE TABLE part_subq( 
    p_partkey INT,
    p_name STRING,
    p_mfgr STRING,
    p_brand STRING,
    p_type STRING,
    p_size INT,
    p_container STRING,
    p_retailprice DOUBLE,
    p_comment STRING
)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@part_subq
POSTHOOK: query: CREATE TABLE part_subq( 
    p_partkey INT,
    p_name STRING,
    p_mfgr STRING,
    p_brand STRING,
    p_type STRING,
    p_size INT,
    p_container STRING,
    p_retailprice DOUBLE,
    p_comment STRING
)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@part_subq
PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/part_tiny.txt' overwrite into table part_subq
PREHOOK: type: LOAD
#### A masked pattern was here ####
PREHOOK: Output: default@part_subq
POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/part_tiny.txt' overwrite into table part_subq
POSTHOOK: type: LOAD
#### A masked pattern was here ####
POSTHOOK: Output: default@part_subq
PREHOOK: query: -- non agg, non corr
explain
 select key, count(*) 
from src 
group by key
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, non corr
explain
 select key, count(*) 
from src 
group by key
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
POSTHOOK: type: QUERY
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 depends on stages: Stage-2

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: src
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: key (type: string)
              outputColumnNames: _col0
              Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count()
                keys: _col0 (type: string)
                mode: hash
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: string)
                  sort order: +
                  Map-reduce partition columns: _col0 (type: string)
                  Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col1 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col1 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col1 (type: bigint)
              Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col0 (type: bigint)
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col1 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: src
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key > '9') (type: boolean)
              Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col1 (type: bigint)
              outputColumnNames: _col0
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: select s1.key, count(*) from src s1 where s1.key > '9' group by s1.key
PREHOOK: type: QUERY
PREHOOK: Input: default@src
#### A masked pattern was here ####
POSTHOOK: query: select s1.key, count(*) from src s1 where s1.key > '9' group by s1.key
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
90	3
92	1
95	2
96	1
97	2
98	2
PREHOOK: query: select key, count(*) 
from src 
group by key
having count(*) in (select count(*) from src s1 where s1.key = '90' group by s1.key )
PREHOOK: type: QUERY
PREHOOK: Input: default@src
#### A masked pattern was here ####
POSTHOOK: query: select key, count(*) 
from src 
group by key
having count(*) in (select count(*) from src s1 where s1.key = '90' group by s1.key )
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
0	3
119	3
128	3
167	3
187	3
193	3
199	3
208	3
273	3
298	3
311	3
316	3
318	3
327	3
35	3
369	3
384	3
396	3
403	3
409	3
417	3
430	3
431	3
438	3
454	3
466	3
480	3
498	3
5	3
70	3
90	3
PREHOOK: query: -- non agg, corr
explain
 select key, value, count(*) 
from src b
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9'  and s1.value = b.value group by s1.key )
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, corr
explain
 select key, value, count(*) 
from src b
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9'  and s1.value = b.value group by s1.key )
POSTHOOK: type: QUERY
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 depends on stages: Stage-2

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: value is not null (type: boolean)
              Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string), _col1 (type: string)
                    sort order: ++
                    Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
                    Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col2 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col2 (type: bigint), _col1 (type: string)
              sort order: ++
              Map-reduce partition columns: _col2 (type: bigint), _col1 (type: string)
              Statistics: Num rows: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint), _col1 (type: string)
              sort order: ++
              Map-reduce partition columns: _col0 (type: bigint), _col1 (type: string)
              Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col2 (type: bigint), _col1 (type: string)
            1 _col0 (type: bigint), _col1 (type: string)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 137 Data size: 1460 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 137 Data size: 1460 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((key > '9') and value is not null) (type: boolean)
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: value (type: string), key (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string), _col1 (type: string)
                    sort order: ++
                    Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
                    Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col2 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col2 (type: bigint), _col0 (type: string)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint), _col1 (type: string)
                mode: hash
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- agg, non corr
explain
select p_mfgr, avg(p_size)
from part_subq b
group by b.p_mfgr
having b.p_mfgr in 
   (select p_mfgr 
    from part_subq
    group by p_mfgr
    having max(p_size) - min(p_size) < 20
   )
PREHOOK: type: QUERY
POSTHOOK: query: -- agg, non corr
explain
select p_mfgr, avg(p_size)
from part_subq b
group by b.p_mfgr
having b.p_mfgr in 
   (select p_mfgr 
    from part_subq
    group by p_mfgr
    having max(p_size) - min(p_size) < 20
   )
POSTHOOK: type: QUERY
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 depends on stages: Stage-2

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 30 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: p_mfgr is not null (type: boolean)
              Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: avg(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: struct<count:bigint,sum:double,input:int>)
      Reduce Operator Tree:
        Group By Operator
          aggregations: avg(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 7 Data size: 740 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 7 Data size: 740 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col1 (type: double)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 2 Data size: 211 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col0 (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 7 Data size: 814 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 7 Data size: 814 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 30 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: p_mfgr is not null (type: boolean)
              Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: max(_col1), min(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: int), _col2 (type: int)
      Reduce Operator Tree:
        Group By Operator
          aggregations: max(VALUE._col0), min(VALUE._col1)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 7 Data size: 740 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: ((_col1 - _col2) < 20) (type: boolean)
            Statistics: Num rows: 2 Data size: 211 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: string)
              outputColumnNames: _col0
              Statistics: Num rows: 2 Data size: 211 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: string)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 2 Data size: 211 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- agg, non corr
explain
select p_mfgr, avg(p_size)
from part_subq b
group by b.p_mfgr
having b.p_mfgr in
   (select p_mfgr
    from part_subq
    group by p_mfgr
    having max(p_size) - min(p_size) < 20
   )
PREHOOK: type: QUERY
POSTHOOK: query: -- agg, non corr
explain
select p_mfgr, avg(p_size)
from part_subq b
group by b.p_mfgr
having b.p_mfgr in
   (select p_mfgr
    from part_subq
    group by p_mfgr
    having max(p_size) - min(p_size) < 20
   )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 30 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: p_mfgr is not null (type: boolean)
              Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: avg(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: struct<count:bigint,sum:double,input:int>)
          TableScan
            alias: b
            Statistics: Num rows: 30 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: p_mfgr is not null (type: boolean)
              Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: max(_col1), min(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: int), _col2 (type: int)
      Reduce Operator Tree:
        Demux Operator
          Statistics: Num rows: 30 Data size: 3172 Basic stats: COMPLETE Column stats: NONE
          Group By Operator
            aggregations: avg(VALUE._col0)
            keys: KEY._col0 (type: string)
            mode: mergepartial
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
            Mux Operator
              Statistics: Num rows: 20 Data size: 2114 Basic stats: COMPLETE Column stats: NONE
              Join Operator
                condition map:
                     Left Semi Join 0 to 1
                keys:
                  0 _col0 (type: string)
                  1 _col0 (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                  table:
                      input format: org.apache.hadoop.mapred.TextInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
          Group By Operator
            aggregations: max(VALUE._col0), min(VALUE._col1)
            keys: KEY._col0 (type: string)
            mode: mergepartial
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 15 Data size: 1586 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((_col1 - _col2) < 20) (type: boolean)
              Statistics: Num rows: 5 Data size: 528 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 5 Data size: 528 Basic stats: COMPLETE Column stats: NONE
                Mux Operator
                  Statistics: Num rows: 20 Data size: 2114 Basic stats: COMPLETE Column stats: NONE
                  Join Operator
                    condition map:
                         Left Semi Join 0 to 1
                    keys:
                      0 _col0 (type: string)
                      1 _col0 (type: string)
                    outputColumnNames: _col0, _col1
                    Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.TextInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- join on agg
select b.key, min(b.value)
from src b
group by b.key
having b.key in ( select a.key
                from src a
                where a.value > 'val_9' and a.value = min(b.value)
                )
PREHOOK: type: QUERY
PREHOOK: Input: default@src
#### A masked pattern was here ####
POSTHOOK: query: -- join on agg
select b.key, min(b.value)
from src b
group by b.key
having b.key in ( select a.key
                from src a
                where a.value > 'val_9' and a.value = min(b.value)
                )
POSTHOOK: type: QUERY
POSTHOOK: Input: default@src
#### A masked pattern was here ####
90	val_90
92	val_92
95	val_95
96	val_96
97	val_97
98	val_98
PREHOOK: query: -- where and having
-- Plan is:
-- Stage 1: b semijoin sq1:src (subquery in where)
-- Stage 2: group by Stage 1 o/p
-- Stage 5: group by on sq2:src (subquery in having)
-- Stage 6: Stage 2 o/p semijoin Stage 5
explain
select key, value, count(*) 
from src b
where b.key in (select key from src where src.key > '8')
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
PREHOOK: type: QUERY
POSTHOOK: query: -- where and having
-- Plan is:
-- Stage 1: b semijoin sq1:src (subquery in where)
-- Stage 2: group by Stage 1 o/p
-- Stage 5: group by on sq2:src (subquery in having)
-- Stage 6: Stage 2 o/p semijoin Stage 5
explain
select key, value, count(*) 
from src b
where b.key in (select key from src where src.key > '8')
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-2 depends on stages: Stage-1
  Stage-3 depends on stages: Stage-2, Stage-5
  Stage-5 is a root stage
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: key is not null (type: boolean)
              Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: string)
                  sort order: +
                  Map-reduce partition columns: _col0 (type: string)
                  Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col1 (type: string)
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key > '8') (type: boolean)
              Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0
                  Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col0 (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
          Group By Operator
            aggregations: count()
            keys: _col0 (type: string), _col1 (type: string)
            mode: hash
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: string), _col1 (type: string)
              sort order: ++
              Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
              Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col2 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col2 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col2 (type: bigint)
              Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: string), _col1 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col0 (type: bigint)
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col2 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 150 Data size: 1600 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 150 Data size: 1600 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-5
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key > '9') (type: boolean)
              Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col1 (type: bigint)
              outputColumnNames: _col0
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- Plan is:
-- Stage  5: group by on sq2:src (subquery in having)
-- Stage 10: hashtable for sq1:src (subquery in where)
-- Stage  2: b map-side semijoin Stage 10 o/p
-- Stage  3: Stage 2 semijoin Stage 5
-- Stage  9: construct hastable for Stage 5 o/p
-- Stage  6: Stage 2 map-side semijoin Stage 9
explain
select key, value, count(*) 
from src b
where b.key in (select key from src where src.key > '8')
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
PREHOOK: type: QUERY
POSTHOOK: query: -- Plan is:
-- Stage  5: group by on sq2:src (subquery in having)
-- Stage 10: hashtable for sq1:src (subquery in where)
-- Stage  2: b map-side semijoin Stage 10 o/p
-- Stage  3: Stage 2 semijoin Stage 5
-- Stage  9: construct hastable for Stage 5 o/p
-- Stage  6: Stage 2 map-side semijoin Stage 9
explain
select key, value, count(*) 
from src b
where b.key in (select key from src where src.key > '8')
group by key, value
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-5 is a root stage
  Stage-7 depends on stages: Stage-2, Stage-5 , consists of Stage-9, Stage-3
  Stage-9 has a backup stage: Stage-3
  Stage-6 depends on stages: Stage-9
  Stage-3
  Stage-10 is a root stage
  Stage-2 depends on stages: Stage-10
  Stage-0 depends on stages: Stage-6, Stage-3

STAGE PLANS:
  Stage: Stage-5
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key > '9') (type: boolean)
              Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string)
                    sort order: +
                    Map-reduce partition columns: _col0 (type: string)
                    Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col1 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col1 (type: bigint)
              outputColumnNames: _col0
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-7
    Conditional Operator

  Stage: Stage-9
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $INTNAME1 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $INTNAME1 
          TableScan
            HashTable Sink Operator
              keys:
                0 _col2 (type: bigint)
                1 _col0 (type: bigint)

  Stage: Stage-6
    Map Reduce
      Map Operator Tree:
          TableScan
            Map Join Operator
              condition map:
                   Left Semi Join 0 to 1
              keys:
                0 _col2 (type: bigint)
                1 _col0 (type: bigint)
              outputColumnNames: _col0, _col1, _col2
              File Output Operator
                compressed: false
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col2 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col2 (type: bigint)
              Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: string), _col1 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint)
              sort order: +
              Map-reduce partition columns: _col0 (type: bigint)
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col2 (type: bigint)
            1 _col0 (type: bigint)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 150 Data size: 1600 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 150 Data size: 1600 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-10
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:$hdt$_1:b 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:$hdt$_1:b 
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key > '8') (type: boolean)
              Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0
                  Statistics: Num rows: 166 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
                  HashTable Sink Operator
                    keys:
                      0 _col0 (type: string)
                      1 _col0 (type: string)

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: key is not null (type: boolean)
              Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 250 Data size: 2656 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Left Semi Join 0 to 1
                  keys:
                    0 _col0 (type: string)
                    1 _col0 (type: string)
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
                  Group By Operator
                    aggregations: count()
                    keys: _col0 (type: string), _col1 (type: string)
                    mode: hash
                    outputColumnNames: _col0, _col1, _col2
                    Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
                    Reduce Output Operator
                      key expressions: _col0 (type: string), _col1 (type: string)
                      sort order: ++
                      Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
                      Statistics: Num rows: 275 Data size: 2921 Basic stats: COMPLETE Column stats: NONE
                      value expressions: _col2 (type: bigint)
      Local Work:
        Map Reduce Local Work
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 137 Data size: 1455 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- non agg, non corr, windowing
explain
select p_mfgr, p_name, avg(p_size) 
from part_subq 
group by p_mfgr, p_name
having p_name in 
  (select first_value(p_name) over(partition by p_mfgr order by p_size) from part_subq)
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, non corr, windowing
explain
select p_mfgr, p_name, avg(p_size) 
from part_subq 
group by p_mfgr, p_name
having p_name in 
  (select first_value(p_name) over(partition by p_mfgr order by p_size) from part_subq)
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-5 depends on stages: Stage-1, Stage-3 , consists of Stage-6, Stage-2
  Stage-6 has a backup stage: Stage-2
  Stage-4 depends on stages: Stage-6
  Stage-2
  Stage-3 is a root stage
  Stage-0 depends on stages: Stage-4, Stage-2

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part_subq
            Statistics: Num rows: 15 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: p_name is not null (type: boolean)
              Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_mfgr (type: string), p_name (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: avg(_col2)
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: string), _col1 (type: string)
                    sort order: ++
                    Map-reduce partition columns: _col0 (type: string), _col1 (type: string)
                    Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
                    value expressions: _col2 (type: struct<count:bigint,sum:double,input:int>)
      Reduce Operator Tree:
        Group By Operator
          aggregations: avg(VALUE._col0)
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 4 Data size: 846 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-5
    Conditional Operator

  Stage: Stage-6
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $INTNAME1 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $INTNAME1 
          TableScan
            HashTable Sink Operator
              keys:
                0 _col1 (type: string)
                1 _col0 (type: string)

  Stage: Stage-4
    Map Reduce
      Map Operator Tree:
          TableScan
            Map Join Operator
              condition map:
                   Left Semi Join 0 to 1
              keys:
                0 _col1 (type: string)
                1 _col0 (type: string)
              outputColumnNames: _col0, _col1, _col2
              File Output Operator
                compressed: false
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work

  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col1 (type: string)
              sort order: +
              Map-reduce partition columns: _col1 (type: string)
              Statistics: Num rows: 4 Data size: 846 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: string), _col2 (type: double)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col1 (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 8 Data size: 1861 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 8 Data size: 1861 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part_subq
            Statistics: Num rows: 15 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: p_mfgr (type: string), p_size (type: int)
              sort order: ++
              Map-reduce partition columns: p_mfgr (type: string)
              Statistics: Num rows: 15 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
              value expressions: p_name (type: string)
      Reduce Operator Tree:
        Select Operator
          expressions: VALUE._col1 (type: string), KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: int)
          outputColumnNames: _col1, _col2, _col5
          Statistics: Num rows: 15 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Function definitions:
                Input definition
                  input alias: ptf_0
                  output shape: _col1: string, _col2: string, _col5: int
                  type: WINDOWING
                Windowing table definition
                  input alias: ptf_1
                  name: windowingtablefunction
                  order by: _col5
                  partition by: _col2
                  raw input shape:
                  window functions:
                      window function definition
                        alias: first_value_window_0
                        arguments: _col1
                        name: first_value
                        window function: GenericUDAFFirstValueEvaluator
                        window frame: PRECEDING(MAX)~
            Statistics: Num rows: 15 Data size: 3173 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: first_value_window_0 is not null (type: boolean)
              Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: first_value_window_0 (type: string)
                outputColumnNames: _col0
                Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0
                  Statistics: Num rows: 8 Data size: 1692 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    table:
                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                        serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: DROP TABLE part_subq
PREHOOK: type: DROPTABLE
PREHOOK: Input: default@part_subq
PREHOOK: Output: default@part_subq
POSTHOOK: query: DROP TABLE part_subq
POSTHOOK: type: DROPTABLE
POSTHOOK: Input: default@part_subq
POSTHOOK: Output: default@part_subq