PREHOOK: query: DROP TABLE part
PREHOOK: type: DROPTABLE
POSTHOOK: query: DROP TABLE part
POSTHOOK: type: DROPTABLE
PREHOOK: query: -- data setup
CREATE TABLE part( 
    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
POSTHOOK: query: -- data setup
CREATE TABLE part( 
    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
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: src
            Statistics: Num rows: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (((key > '9') and key is not null) and value is not null) (type: boolean)
              Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 3 Data size: 601 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: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
          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
              Reduce Output Operator
                key expressions: key1 (type: string), value1 (type: string)
                sort order: ++
                Map-reduce partition columns: key1 (type: string), value1 (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
          condition expressions:
            0 {KEY.reducesinkkey0} {KEY.reducesinkkey1}
            1 
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 3 Data size: 661 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 3 Data size: 661 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 3 Data size: 661 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: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key is not null and value is not null) (type: boolean)
              Statistics: Num rows: 8 Data size: 1603 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: key (type: string), value (type: string)
                sort order: ++
                Map-reduce partition columns: key (type: string), value (type: string)
                Statistics: Num rows: 8 Data size: 1603 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: src
            Statistics: Num rows: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (((key > '9') and key is not null) and value is not null) (type: boolean)
              Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: _col0 (type: string), _col1 (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 3 Data size: 601 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: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          condition expressions:
            0 {KEY.reducesinkkey0} {KEY.reducesinkkey1}
            1 
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 8 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 8 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 8 Data size: 1763 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-1 is a root stage
  Stage-2 depends on stages: Stage-1
  Stage-3 depends on stages: Stage-2
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part2
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Reduce Output Operator
              key expressions: p2_mfgr (type: string), p2_size (type: int)
              sort order: ++
              Map-reduce partition columns: p2_mfgr (type: string)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: p2_mfgr (type: string), p2_size (type: int)
      Reduce Operator Tree:
        Extract
          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 _col2 is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Select Operator
                expressions: _col2 (type: string), _col5 (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-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: 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-3
    Map Reduce
      Map Operator Tree:
          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
          TableScan
            alias: b
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Filter Operator
              predicate: (p_size is not null and p_mfgr is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Reduce Output Operator
                key expressions: p_size (type: int), p_mfgr (type: string)
                sort order: ++
                Map-reduce partition columns: p_size (type: int), p_mfgr (type: string)
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                value expressions: p_name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          condition expressions:
            0 {VALUE._col1} {KEY.reducesinkkey1} {KEY.reducesinkkey0}
            1 
          outputColumnNames: _col1, _col2, _col5
          Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          Select Operator
            expressions: _col2 (type: string), _col1 (type: string), _col5 (type: int)
            outputColumnNames: _col0, _col1, _col2
            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: 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: part
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: p_mfgr (type: string), p_size (type: int)
      Reduce Operator Tree:
        Extract
          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 _col2 is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Select Operator
                expressions: _col2 (type: string), _col5 (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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Filter Operator
              predicate: (p_size is not null and p_mfgr is not null) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Reduce Output Operator
                key expressions: p_size (type: int), p_mfgr (type: string)
                sort order: ++
                Map-reduce partition columns: p_size (type: int), p_mfgr (type: string)
                Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
                value expressions: p_name (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
          condition expressions:
            0 {VALUE._col1} {KEY.reducesinkkey1} {KEY.reducesinkkey0}
            1 
          outputColumnNames: _col1, _col2, _col5
          Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          Select Operator
            expressions: _col2 (type: string), _col1 (type: string), _col5 (type: int)
            outputColumnNames: _col0, _col1, _col2
            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: -- 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: src
            Statistics: Num rows: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (((key > '9') and key is not null) and value is not null) (type: boolean)
              Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: key, value
                Statistics: Num rows: 3 Data size: 601 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  keys: key (type: string), value (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1
                  Statistics: Num rows: 3 Data size: 601 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: 3 Data size: 601 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: 1 Data size: 200 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 1 Data size: 200 Basic stats: COMPLETE Column stats: NONE
            Group By Operator
              keys: _col0 (type: string), _col1 (type: string)
              mode: hash
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 1 Data size: 200 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: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (key is not null and value is not null) (type: boolean)
              Statistics: Num rows: 8 Data size: 1603 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: key (type: string), value (type: string)
                sort order: ++
                Map-reduce partition columns: key (type: string), value (type: string)
                Statistics: Num rows: 8 Data size: 1603 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: 1 Data size: 200 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          condition expressions:
            0 {KEY.reducesinkkey0} {KEY.reducesinkkey1}
            1 
          outputColumnNames: _col0, _col1
          Statistics: Num rows: 8 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col1 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 8 Data size: 1763 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 8 Data size: 1763 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: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: value is not null (type: boolean)
              Statistics: Num rows: 15 Data size: 3006 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: key (type: string), value (type: string)
                outputColumnNames: key, value
                Statistics: Num rows: 15 Data size: 3006 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: key (type: string), value (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 15 Data size: 3006 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: 15 Data size: 3006 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: 7 Data size: 1402 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 7 Data size: 1402 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: 7 Data size: 1402 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: 2 Data size: 400 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Semi Join 0 to 1
          condition expressions:
            0 {VALUE._col0} {KEY.reducesinkkey1} {KEY.reducesinkkey0}
            1 
          outputColumnNames: _col0, _col1, _col2
          Statistics: Num rows: 7 Data size: 1542 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: 7 Data size: 1542 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 7 Data size: 1542 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: 29 Data size: 5812 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((key > '9') and value is not null) (type: boolean)
              Statistics: Num rows: 5 Data size: 1002 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: value (type: string), key (type: string)
                outputColumnNames: value, key
                Statistics: Num rows: 5 Data size: 1002 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count()
                  keys: value (type: string), key (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2
                  Statistics: Num rows: 5 Data size: 1002 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: 5 Data size: 1002 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: 2 Data size: 400 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col2 is not null (type: boolean)
            Statistics: Num rows: 2 Data size: 400 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col2 (type: bigint), _col0 (type: string)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 2 Data size: 400 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint), _col1 (type: string)
                mode: hash
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 400 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[26][tables = [b, sq_1_notin_nullcheck]] 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-2 depends on stages: Stage-1, Stage-3
  Stage-4 is a root stage
  Stage-5 depends on stages: Stage-4
  Stage-1 depends on stages: Stage-5
  Stage-0 depends on stages: Stage-2

STAGE PLANS:
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
      Reduce Operator Tree:
        Extract
          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) (type: boolean)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Select Operator
                expressions: _col1 (type: string), _col2 (type: string)
                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-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col1 (type: string), _col2 (type: string)
              sort order: ++
              Map-reduce partition columns: _col1 (type: string), _col2 (type: string)
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: _col5 (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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          condition expressions:
            0 {KEY.reducesinkkey0} {KEY.reducesinkkey1} {VALUE._col3}
            1 {KEY.reducesinkkey0}
          outputColumnNames: _col1, _col2, _col5, _col12
          Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
          Filter Operator
            predicate: _col12 is null (type: boolean)
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Select Operator
              expressions: _col2 (type: string), _col1 (type: string), _col5 (type: int)
              outputColumnNames: _col0, _col1, _col2
              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-4
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: part
            Statistics: Num rows: 0 Data size: 0 Basic stats: NONE 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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: p_name (type: string), p_mfgr (type: string), p_size (type: int)
      Reduce Operator Tree:
        Extract
          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 (_col1 is null or _col2 is null)) (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
                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-5
    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
              expressions: _col0 (type: bigint)
              outputColumnNames: _col0
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              Group By Operator
                keys: _col0 (type: bigint)
                mode: hash
                outputColumnNames: _col0
                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: 0 Data size: 0 Basic stats: NONE Column stats: NONE
            Reduce Output Operator
              sort order: 
              Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
              value expressions: p_name (type: string), p_mfgr (type: string), p_size (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:
               Left Semi Join 0 to 1
          condition expressions:
            0 {VALUE._col1} {VALUE._col2} {VALUE._col5}
            1 
          outputColumnNames: _col1, _col2, _col5
          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-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink