PREHOOK: query: create table src11 (key1 string, value1 string)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@src11
POSTHOOK: query: create table src11 (key1 string, value1 string)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@src11
PREHOOK: query: create table part2( 
    p2_partkey INT,
    p2_name STRING,
    p2_mfgr STRING,
    p2_brand STRING,
    p2_type STRING,
    p2_size INT,
    p2_container STRING,
    p2_retailprice DOUBLE,
    p2_comment STRING
)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@part2
POSTHOOK: query: create table part2( 
    p2_partkey INT,
    p2_name STRING,
    p2_mfgr STRING,
    p2_brand STRING,
    p2_type STRING,
    p2_size INT,
    p2_container STRING,
    p2_retailprice DOUBLE,
    p2_comment STRING
)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@part2
PREHOOK: query: -- non agg, corr
explain select * from src11 where src11.key1 in (select key from src where src11.value1 = value and key > '9')
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, corr
explain select * from src11 where src11.key1 in (select key from src where src11.value1 = value and key > '9')
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: src11
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Filter Operator
              predicate: (key1 is not null and value1 is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Select Operator
                expressions: key1 (type: string), value1 (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          TableScan
            alias: src
            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: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  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
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col0 (type: string), _col1 (type: string)
            1 _col0 (type: string), _col1 (type: string)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 91 Data size: 969 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 91 Data size: 969 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 91 Data size: 969 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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: explain select * from src a where a.key in (select key from src where a.value = value and key > '9')
PREHOOK: type: QUERY
POSTHOOK: query: explain select * from src a where a.key in (select key from src where a.value = value and key > '9')
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: a
            Statistics: Num rows: 500 Data size: 5312 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key is not null and value is not null) (type: boolean)
              Statistics: Num rows: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 125 Data size: 1328 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: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: a
            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: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  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
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col0 (type: string), _col1 (type: string)
            1 _col0 (type: string), _col1 (type: string)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 137 Data size: 1460 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- agg, corr
explain
select p_mfgr, p_name, p_size 
from part b where b.p_size in 
  (select min(p2_size) 
    from (select p2_mfgr, p2_size, rank() over(partition by p2_mfgr order by p2_size) as r from part2) a 
    where r <= 2 and b.p_mfgr = p2_mfgr
  )
PREHOOK: type: QUERY
POSTHOOK: query: -- agg, corr
explain
select p_mfgr, p_name, p_size 
from part b where b.p_size in 
  (select min(p2_size) 
    from (select p2_mfgr, p2_size, rank() over(partition by p2_mfgr order by p2_size) as r from part2) a 
    where r <= 2 and b.p_mfgr = p2_mfgr
  )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-2 is a root stage
  Stage-3 depends on stages: Stage-2
  Stage-1 depends on stages: Stage-3
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part2
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Select Operator
              expressions: p2_mfgr (type: string), p2_size (type: int)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string), _col1 (type: int)
                sort order: ++
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
      Reduce Operator Tree:
        Select Operator
          expressions: KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: int)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Filter Operator
              predicate: ((_wcol0 <= 2) and _col0 is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Select Operator
                expressions: _col0 (type: string), _col1 (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                Group By Operator
                  aggregations: min(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: _col1 (type: int)
      Reduce Operator Tree:
        Group By Operator
          aggregations: min(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Select Operator
              expressions: _col1 (type: int), _col0 (type: string)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Group By Operator
                keys: _col0 (type: int), _col1 (type: string)
                mode: hash
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (p_size is not null and p_mfgr is not null) (type: boolean)
              Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col2 (type: int), _col1 (type: string)
                  sort order: ++
                  Map-reduce partition columns: _col2 (type: int), _col1 (type: string)
                  Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: int), _col1 (type: string)
              sort order: ++
              Map-reduce partition columns: _col0 (type: int), _col1 (type: string)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col2 (type: int), _col1 (type: string)
            1 _col0 (type: int), _col1 (type: string)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 7 Data size: 931 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col1 (type: string), _col0 (type: string), _col2 (type: int)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 7 Data size: 931 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 7 Data size: 931 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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: explain
select p_mfgr, p_name, p_size 
from part b where b.p_size in 
  (select min(p_size) 
   from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a 
   where r <= 2 and b.p_mfgr = p_mfgr
  )
PREHOOK: type: QUERY
POSTHOOK: query: explain
select p_mfgr, p_name, p_size 
from part b where b.p_size in 
  (select min(p_size) 
   from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a 
   where r <= 2 and b.p_mfgr = p_mfgr
  )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-2 is a root stage
  Stage-3 depends on stages: Stage-2
  Stage-1 depends on stages: Stage-3
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: p_mfgr (type: string), p_size (type: int)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string), _col1 (type: int)
                sort order: ++
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Select Operator
          expressions: KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: int)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((_wcol0 <= 2) and _col0 is not null) (type: boolean)
              Statistics: Num rows: 4 Data size: 484 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: string), _col1 (type: int)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 4 Data size: 484 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: min(_col1)
                  keys: _col0 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 4 Data size: 484 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: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 4 Data size: 484 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col1 (type: int)
      Reduce Operator Tree:
        Group By Operator
          aggregations: min(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 2 Data size: 242 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col1 is not null (type: boolean)
            Statistics: Num rows: 2 Data size: 242 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col1 (type: int), _col0 (type: string)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 2 Data size: 242 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: int), _col1 (type: string)
                mode: hash
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 242 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-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (p_size is not null and p_mfgr is not null) (type: boolean)
              Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
                outputColumnNames: _col0, _col1, _col2
                Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col2 (type: int), _col1 (type: string)
                  sort order: ++
                  Map-reduce partition columns: _col2 (type: int), _col1 (type: string)
                  Statistics: Num rows: 7 Data size: 847 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: string)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: int), _col1 (type: string)
              sort order: ++
              Map-reduce partition columns: _col0 (type: int), _col1 (type: string)
              Statistics: Num rows: 2 Data size: 242 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          keys:
            0 _col2 (type: int), _col1 (type: string)
            1 _col0 (type: int), _col1 (type: string)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 7 Data size: 931 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col1 (type: string), _col0 (type: string), _col2 (type: int)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 7 Data size: 931 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 7 Data size: 931 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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- distinct, corr
explain 
select * 
from src b 
where b.key in
        (select distinct key 
         from src 
         where b.value = value and key > '9'
        )
PREHOOK: type: QUERY
POSTHOOK: query: -- distinct, corr
explain 
select * 
from src b 
where b.key in
        (select distinct key 
         from src 
         where b.value = value and key > '9'
        )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-2 is a root stage
  Stage-1 depends on stages: Stage-2
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  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 > '9') and value is not null) (type: boolean)
              Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 83 Data size: 881 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  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
      Reduce Operator Tree:
        Group By Operator
          keys: KEY._col0 (type: string), KEY._col1 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 41 Data size: 435 Basic stats: COMPLETE Column stats: NONE
          Group By Operator
            keys: _col0 (type: string), _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-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 and value is not null) (type: boolean)
              Statistics: Num rows: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 125 Data size: 1328 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: 125 Data size: 1328 Basic stats: COMPLETE Column stats: NONE
          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: 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 _col0 (type: string), _col1 (type: string)
            1 _col0 (type: string), _col1 (type: string)
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 137 Data size: 1460 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

PREHOOK: query: -- non agg, corr, having
explain
 select key, value, count(*) 
from src b
group by key, value
having count(*) in (select count(*) from src where src.key > '9'  and src.value = b.value group by key )
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, corr, having
explain
 select key, value, count(*) 
from src b
group by key, value
having count(*) in (select count(*) from src where src.key > '9'  and src.value = b.value group by 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
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string), _col2 (type: bigint)
            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

Warning: Shuffle Join JOIN[30][tables = [$hdt$_0, $hdt$_1]] in Stage 'Stage-1:MAPRED' is a cross product
PREHOOK: query: -- non agg, corr
explain
select p_mfgr, b.p_name, p_size 
from part b 
where b.p_name not in 
  (select p_name 
  from (select p_mfgr, p_name, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a 
  where r <= 2 and b.p_mfgr = p_mfgr 
  )
PREHOOK: type: QUERY
POSTHOOK: query: -- non agg, corr
explain
select p_mfgr, b.p_name, p_size 
from part b 
where b.p_name not in 
  (select p_name 
  from (select p_mfgr, p_name, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a 
  where r <= 2 and b.p_mfgr = p_mfgr 
  )
POSTHOOK: type: QUERY
STAGE DEPENDENCIES:
  Stage-3 is a root stage
  Stage-4 depends on stages: Stage-3
  Stage-1 depends on stages: Stage-4
  Stage-2 depends on stages: Stage-1, Stage-5
  Stage-5 is a root stage
  Stage-0 depends on stages: Stage-2

STAGE PLANS:
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col1 (type: string), _col2 (type: int)
                sort order: ++
                Map-reduce partition columns: _col1 (type: string)
                Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
                value expressions: _col0 (type: string)
      Reduce Operator Tree:
        Select Operator
          expressions: VALUE._col0 (type: string), KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: int)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((_wcol0 <= 2) and (_col0 is null or _col1 is null)) (type: boolean)
              Statistics: Num rows: 8 Data size: 968 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                Statistics: Num rows: 8 Data size: 968 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  mode: hash
                  outputColumnNames: _col0
                  Statistics: Num rows: 1 Data size: 8 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-4
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              sort order: 
              Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: (_col0 = 0) (type: boolean)
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Select Operator
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                sort order: 
                Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
                value expressions: _col0 (type: string), _col1 (type: string), _col2 (type: int)
          TableScan
            Reduce Output Operator
              sort order: 
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          keys:
            0 
            1 
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 28 Data size: 3461 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: 28 Data size: 3461 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col2 (type: int)
          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: 8 Data size: 968 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 _col0 (type: string), _col1 (type: string)
            1 _col0 (type: string), _col1 (type: string)
          outputColumnNames: _col0, _col1, _col2, _col4
          Statistics: Num rows: 30 Data size: 3807 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col4 is null (type: boolean)
            Statistics: Num rows: 15 Data size: 1903 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col1 (type: string), _col0 (type: string), _col2 (type: int)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 15 Data size: 1903 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 15 Data size: 1903 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: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col1 (type: string), _col2 (type: int)
                sort order: ++
                Map-reduce partition columns: _col1 (type: string)
                Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
                value expressions: _col0 (type: string)
      Reduce Operator Tree:
        Select Operator
          expressions: VALUE._col0 (type: string), KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: int)
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 26 Data size: 3147 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (_wcol0 <= 2) (type: boolean)
              Statistics: Num rows: 8 Data size: 968 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: string), _col1 (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 8 Data size: 968 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