You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ha...@apache.org on 2013/10/23 16:54:18 UTC
svn commit: r1535040 [3/3] - in /hive/trunk/ql/src:
java/org/apache/hadoop/hive/ql/ java/org/apache/hadoop/hive/ql/parse/
test/queries/clientnegative/ test/queries/clientpositive/
test/results/clientnegative/ test/results/clientpositive/
Added: hive/trunk/ql/src/test/results/clientpositive/subquery_notin.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/subquery_notin.q.out?rev=1535040&view=auto
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/subquery_notin.q.out (added)
+++ hive/trunk/ql/src/test/results/clientpositive/subquery_notin.q.out Wed Oct 23 14:54:17 2013
@@ -0,0 +1,1147 @@
+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
+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: default@part
+PREHOOK: query: LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part
+PREHOOK: type: LOAD
+PREHOOK: Output: default@part
+POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part
+POSTHOOK: type: LOAD
+POSTHOOK: Output: default@part
+PREHOOK: query: DROP TABLE lineitem
+PREHOOK: type: DROPTABLE
+POSTHOOK: query: DROP TABLE lineitem
+POSTHOOK: type: DROPTABLE
+PREHOOK: query: CREATE TABLE lineitem (L_ORDERKEY INT,
+ L_PARTKEY INT,
+ L_SUPPKEY INT,
+ L_LINENUMBER INT,
+ L_QUANTITY DOUBLE,
+ L_EXTENDEDPRICE DOUBLE,
+ L_DISCOUNT DOUBLE,
+ L_TAX DOUBLE,
+ L_RETURNFLAG STRING,
+ L_LINESTATUS STRING,
+ l_shipdate STRING,
+ L_COMMITDATE STRING,
+ L_RECEIPTDATE STRING,
+ L_SHIPINSTRUCT STRING,
+ L_SHIPMODE STRING,
+ L_COMMENT STRING)
+ROW FORMAT DELIMITED
+FIELDS TERMINATED BY '|'
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: CREATE TABLE lineitem (L_ORDERKEY INT,
+ L_PARTKEY INT,
+ L_SUPPKEY INT,
+ L_LINENUMBER INT,
+ L_QUANTITY DOUBLE,
+ L_EXTENDEDPRICE DOUBLE,
+ L_DISCOUNT DOUBLE,
+ L_TAX DOUBLE,
+ L_RETURNFLAG STRING,
+ L_LINESTATUS STRING,
+ l_shipdate STRING,
+ L_COMMITDATE STRING,
+ L_RECEIPTDATE STRING,
+ L_SHIPINSTRUCT STRING,
+ L_SHIPMODE STRING,
+ L_COMMENT STRING)
+ROW FORMAT DELIMITED
+FIELDS TERMINATED BY '|'
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@lineitem
+PREHOOK: query: LOAD DATA LOCAL INPATH '../data/files/lineitem.txt' OVERWRITE INTO TABLE lineitem
+PREHOOK: type: LOAD
+PREHOOK: Output: default@lineitem
+POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/lineitem.txt' OVERWRITE INTO TABLE lineitem
+POSTHOOK: type: LOAD
+POSTHOOK: Output: default@lineitem
+PREHOOK: query: -- non agg, non corr
+explain
+select *
+from src
+where src.key not in
+ ( select key from src s1
+ where s1.key > '2'
+ )
+PREHOOK: type: QUERY
+POSTHOOK: query: -- non agg, non corr
+explain
+select *
+from src
+where src.key not in
+ ( select key from src s1
+ where s1.key > '2'
+ )
+POSTHOOK: type: QUERY
+ABSTRACT SYNTAX TREE:
+ (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (not (TOK_SUBQUERY_EXPR (TOK_SUBQUERY_OP in) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) s1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_WHERE (> (. (TOK_TABLE_OR_COL s1) key) '2')))) (. (TOK_TABLE_OR_COL src) key))))))
+
+STAGE DEPENDENCIES:
+ Stage-1 is a root stage
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-1
+ Map Reduce
+ Alias -> Map Operator Tree:
+ sq_1:s1
+ TableScan
+ alias: s1
+ Filter Operator
+ predicate:
+ expr: (key > '2')
+ type: boolean
+ Select Operator
+ expressions:
+ expr: key
+ type: string
+ outputColumnNames: _col0
+ Reduce Output Operator
+ key expressions:
+ expr: _col0
+ type: string
+ sort order: +
+ Map-reduce partition columns:
+ expr: _col0
+ type: string
+ tag: 1
+ value expressions:
+ expr: _col0
+ type: string
+ src
+ TableScan
+ alias: src
+ Reduce Output Operator
+ key expressions:
+ expr: key
+ type: string
+ sort order: +
+ Map-reduce partition columns:
+ expr: key
+ type: string
+ tag: 0
+ value expressions:
+ expr: key
+ type: string
+ expr: value
+ type: string
+ Reduce Operator Tree:
+ Join Operator
+ condition map:
+ Left Outer Join0 to 1
+ condition expressions:
+ 0 {VALUE._col0} {VALUE._col1}
+ 1 {VALUE._col0}
+ handleSkewJoin: false
+ outputColumnNames: _col0, _col1, _col4
+ Filter Operator
+ predicate:
+ expr: ((1 = 1) and _col4 is null)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col0
+ type: string
+ expr: _col1
+ type: string
+ outputColumnNames: _col0, _col1
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+
+
+PREHOOK: query: select *
+from src
+where src.key not in ( select key from src s1 where s1.key > '2')
+order by key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+#### A masked pattern was here ####
+POSTHOOK: query: select *
+from src
+where src.key not in ( select key from src s1 where s1.key > '2')
+order by key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+#### A masked pattern was here ####
+0 val_0
+0 val_0
+0 val_0
+10 val_10
+100 val_100
+100 val_100
+103 val_103
+103 val_103
+104 val_104
+104 val_104
+105 val_105
+11 val_11
+111 val_111
+113 val_113
+113 val_113
+114 val_114
+116 val_116
+118 val_118
+118 val_118
+119 val_119
+119 val_119
+119 val_119
+12 val_12
+12 val_12
+120 val_120
+120 val_120
+125 val_125
+125 val_125
+126 val_126
+128 val_128
+128 val_128
+128 val_128
+129 val_129
+129 val_129
+131 val_131
+133 val_133
+134 val_134
+134 val_134
+136 val_136
+137 val_137
+137 val_137
+138 val_138
+138 val_138
+138 val_138
+138 val_138
+143 val_143
+145 val_145
+146 val_146
+146 val_146
+149 val_149
+149 val_149
+15 val_15
+15 val_15
+150 val_150
+152 val_152
+152 val_152
+153 val_153
+155 val_155
+156 val_156
+157 val_157
+158 val_158
+160 val_160
+162 val_162
+163 val_163
+164 val_164
+164 val_164
+165 val_165
+165 val_165
+166 val_166
+167 val_167
+167 val_167
+167 val_167
+168 val_168
+169 val_169
+169 val_169
+169 val_169
+169 val_169
+17 val_17
+170 val_170
+172 val_172
+172 val_172
+174 val_174
+174 val_174
+175 val_175
+175 val_175
+176 val_176
+176 val_176
+177 val_177
+178 val_178
+179 val_179
+179 val_179
+18 val_18
+18 val_18
+180 val_180
+181 val_181
+183 val_183
+186 val_186
+187 val_187
+187 val_187
+187 val_187
+189 val_189
+19 val_19
+190 val_190
+191 val_191
+191 val_191
+192 val_192
+193 val_193
+193 val_193
+193 val_193
+194 val_194
+195 val_195
+195 val_195
+196 val_196
+197 val_197
+197 val_197
+199 val_199
+199 val_199
+199 val_199
+2 val_2
+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 = a.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 = a.p_mfgr
+ )
+POSTHOOK: type: QUERY
+ABSTRACT SYNTAX TREE:
+ (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part) b)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_mfgr)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) p_name)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_size))) (TOK_WHERE (not (TOK_SUBQUERY_EXPR (TOK_SUBQUERY_OP in) (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_mfgr)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_name)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_size)) (TOK_SELEXPR (TOK_FUNCTION rank (TOK_WINDOWSPEC (TOK_PARTITIONINGSPEC (TOK_DISTRIBUTEBY (TOK_TABLE_OR_COL p_mfgr)) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL p_size)))))) r)))) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_name))) (TOK_WHERE (and (<= (TOK_TABLE_OR_COL r) 2) (= (. (TOK_TABLE_OR_COL b) p_mfgr) (. (TOK_TABLE_OR_COL a) p_mfgr)))))) (.
(TOK_TABLE_OR_COL b) p_name))))))
+
+STAGE DEPENDENCIES:
+ Stage-2 is a root stage
+ Stage-1 depends on stages: Stage-2
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-2
+ Map Reduce
+ Alias -> Map Operator Tree:
+ sq_1:a:part
+ TableScan
+ alias: part
+ Reduce Output Operator
+ key expressions:
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ sort order: ++
+ Map-reduce partition columns:
+ expr: p_mfgr
+ type: string
+ tag: -1
+ value expressions:
+ expr: p_name
+ type: string
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Extract
+ PTF Operator
+ Filter Operator
+ predicate:
+ expr: (_wcol0 <= 2)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col1
+ type: string
+ expr: _col2
+ type: string
+ outputColumnNames: _col0, _col1
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ table:
+ input format: org.apache.hadoop.mapred.SequenceFileInputFormat
+ output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+ serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+ Stage: Stage-1
+ Map Reduce
+ Alias -> Map Operator Tree:
+ $INTNAME
+ TableScan
+ Reduce Output Operator
+ key expressions:
+ expr: _col0
+ type: string
+ expr: _col1
+ type: string
+ sort order: ++
+ Map-reduce partition columns:
+ expr: _col0
+ type: string
+ expr: _col1
+ type: string
+ tag: 1
+ value expressions:
+ expr: _col0
+ type: string
+ b
+ TableScan
+ alias: b
+ Reduce Output Operator
+ key expressions:
+ expr: p_name
+ type: string
+ expr: p_mfgr
+ type: string
+ sort order: ++
+ Map-reduce partition columns:
+ expr: p_name
+ type: string
+ expr: p_mfgr
+ type: string
+ tag: 0
+ value expressions:
+ expr: p_name
+ type: string
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Join Operator
+ condition map:
+ Left Outer Join0 to 1
+ condition expressions:
+ 0 {VALUE._col1} {VALUE._col2} {VALUE._col5}
+ 1 {VALUE._col0}
+ handleSkewJoin: false
+ outputColumnNames: _col1, _col2, _col5, _col11
+ Filter Operator
+ predicate:
+ expr: ((1 = 1) and _col11 is null)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col2
+ type: string
+ expr: _col1
+ type: string
+ expr: _col5
+ type: int
+ outputColumnNames: _col0, _col1, _col2
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+
+
+PREHOOK: query: 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 = a.p_mfgr
+ )
+order by p_mfgr, b.p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: 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 = a.p_mfgr
+ )
+order by p_mfgr, b.p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique chartreuse lavender yellow 34
+Manufacturer#1 almond antique salmon chartreuse burlywood 6
+Manufacturer#1 almond aquamarine burnished black steel 28
+Manufacturer#1 almond aquamarine pink moccasin thistle 42
+Manufacturer#2 almond antique violet turquoise frosted 40
+Manufacturer#2 almond aquamarine rose maroon antique 25
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18
+Manufacturer#3 almond antique chartreuse khaki white 17
+Manufacturer#3 almond antique metallic orange dim 19
+Manufacturer#3 almond antique olive coral navajo 45
+Manufacturer#4 almond antique violet mint lemon 39
+Manufacturer#4 almond aquamarine floral ivory bisque 27
+Manufacturer#4 almond azure aquamarine papaya violet 12
+Manufacturer#5 almond antique blue firebrick mint 31
+Manufacturer#5 almond aquamarine dodger light gainsboro 46
+Manufacturer#5 almond azure blanched chiffon midnight 23
+PREHOOK: query: -- agg, non corr
+explain
+select p_name, p_size
+from
+part where part.p_size not in
+ (select avg(p_size)
+ from (select p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
+ where r <= 2
+ )
+PREHOOK: type: QUERY
+POSTHOOK: query: -- agg, non corr
+explain
+select p_name, p_size
+from
+part where part.p_size not in
+ (select avg(p_size)
+ from (select p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
+ where r <= 2
+ )
+POSTHOOK: type: QUERY
+ABSTRACT SYNTAX TREE:
+ (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_name)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_size))) (TOK_WHERE (not (TOK_SUBQUERY_EXPR (TOK_SUBQUERY_OP in) (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_size)) (TOK_SELEXPR (TOK_FUNCTION rank (TOK_WINDOWSPEC (TOK_PARTITIONINGSPEC (TOK_DISTRIBUTEBY (TOK_TABLE_OR_COL p_mfgr)) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL p_size)))))) r)))) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION avg (TOK_TABLE_OR_COL p_size)))) (TOK_WHERE (<= (TOK_TABLE_OR_COL r) 2)))) (. (TOK_TABLE_OR_COL part) p_size))))))
+
+STAGE DEPENDENCIES:
+ Stage-1 is a root stage
+ Stage-2 depends on stages: Stage-1
+ Stage-3 depends on stages: Stage-2
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-1
+ Map Reduce
+ Alias -> Map Operator Tree:
+ sq_1:a:part
+ TableScan
+ alias: part
+ Reduce Output Operator
+ key expressions:
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ sort order: ++
+ Map-reduce partition columns:
+ expr: p_mfgr
+ type: string
+ tag: -1
+ value expressions:
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Extract
+ PTF Operator
+ Filter Operator
+ predicate:
+ expr: (_wcol0 <= 2)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col5
+ type: int
+ outputColumnNames: _col0
+ Group By Operator
+ aggregations:
+ expr: avg(_col0)
+ bucketGroup: false
+ mode: hash
+ outputColumnNames: _col0
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+ Alias -> Map Operator Tree:
+#### A masked pattern was here ####
+ TableScan
+ Reduce Output Operator
+ sort order:
+ tag: -1
+ value expressions:
+ expr: _col0
+ type: struct<count:bigint,sum:double>
+ Reduce Operator Tree:
+ Group By Operator
+ aggregations:
+ expr: avg(VALUE._col0)
+ bucketGroup: false
+ mode: mergepartial
+ outputColumnNames: _col0
+ Select Operator
+ expressions:
+ expr: _col0
+ type: double
+ outputColumnNames: _col0
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+ Alias -> Map Operator Tree:
+ $INTNAME
+ TableScan
+ Reduce Output Operator
+ key expressions:
+ expr: _col0
+ type: double
+ sort order: +
+ Map-reduce partition columns:
+ expr: _col0
+ type: double
+ tag: 1
+ value expressions:
+ expr: _col0
+ type: double
+ part
+ TableScan
+ alias: part
+ Reduce Output Operator
+ key expressions:
+ expr: UDFToDouble(p_size)
+ type: double
+ sort order: +
+ Map-reduce partition columns:
+ expr: UDFToDouble(p_size)
+ type: double
+ tag: 0
+ value expressions:
+ expr: p_name
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Join Operator
+ condition map:
+ Left Outer Join0 to 1
+ condition expressions:
+ 0 {VALUE._col1} {VALUE._col5}
+ 1 {VALUE._col0}
+ handleSkewJoin: false
+ outputColumnNames: _col1, _col5, _col11
+ Filter Operator
+ predicate:
+ expr: ((1 = 1) and _col11 is null)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col1
+ type: string
+ expr: _col5
+ type: int
+ outputColumnNames: _col0, _col1
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+
+
+PREHOOK: query: select p_name, p_size
+from
+part where part.p_size not in
+ (select avg(p_size)
+ from (select p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
+ where r <= 2
+ )
+order by p_name, p_size
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: select p_name, p_size
+from
+part where part.p_size not in
+ (select avg(p_size)
+ from (select p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
+ where r <= 2
+ )
+order by p_name, p_size
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+almond antique blue firebrick mint 31
+almond antique burnished rose metallic 2
+almond antique burnished rose metallic 2
+almond antique chartreuse khaki white 17
+almond antique chartreuse lavender yellow 34
+almond antique forest lavender goldenrod 14
+almond antique gainsboro frosted violet 10
+almond antique metallic orange dim 19
+almond antique misty red olive 1
+almond antique olive coral navajo 45
+almond antique sky peru orange 2
+almond antique violet chocolate turquoise 14
+almond antique violet mint lemon 39
+almond antique violet turquoise frosted 40
+almond aquamarine burnished black steel 28
+almond aquamarine dodger light gainsboro 46
+almond aquamarine floral ivory bisque 27
+almond aquamarine midnight light salmon 2
+almond aquamarine pink moccasin thistle 42
+almond aquamarine rose maroon antique 25
+almond aquamarine sandy cyan gainsboro 18
+almond aquamarine yellow dodger mint 7
+almond azure aquamarine papaya violet 12
+almond azure blanched chiffon midnight 23
+PREHOOK: query: -- agg, corr
+explain
+select p_mfgr, p_name, p_size
+from part b where b.p_size not 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 = a.p_mfgr
+ )
+PREHOOK: type: QUERY
+POSTHOOK: query: -- agg, corr
+explain
+select p_mfgr, p_name, p_size
+from part b where b.p_size not 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 = a.p_mfgr
+ )
+POSTHOOK: type: QUERY
+ABSTRACT SYNTAX TREE:
+ (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part) b)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_mfgr)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_name)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_size))) (TOK_WHERE (not (TOK_SUBQUERY_EXPR (TOK_SUBQUERY_OP in) (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME part))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL p_mfgr)) (TOK_SELEXPR (TOK_TABLE_OR_COL p_size)) (TOK_SELEXPR (TOK_FUNCTION rank (TOK_WINDOWSPEC (TOK_PARTITIONINGSPEC (TOK_DISTRIBUTEBY (TOK_TABLE_OR_COL p_mfgr)) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL p_size)))))) r)))) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION min (TOK_TABLE_OR_COL p_size)))) (TOK_WHERE (and (<= (TOK_TABLE_OR_COL r) 2) (= (. (TOK_TABLE_OR_COL b) p_mfgr) (. (TOK_TABLE_OR_COL a) p_mfgr)))))) (. (TOK_TABLE_OR_COL b) p_size
))))))
+
+STAGE DEPENDENCIES:
+ Stage-2 is a root stage
+ Stage-3 depends on stages: Stage-2
+ Stage-1 depends on stages: Stage-3
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-2
+ Map Reduce
+ Alias -> Map Operator Tree:
+ sq_1:a:part
+ TableScan
+ alias: part
+ Reduce Output Operator
+ key expressions:
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ sort order: ++
+ Map-reduce partition columns:
+ expr: p_mfgr
+ type: string
+ tag: -1
+ value expressions:
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Extract
+ PTF Operator
+ Filter Operator
+ predicate:
+ expr: (_wcol0 <= 2)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col2
+ type: string
+ expr: _col5
+ type: int
+ outputColumnNames: _col0, _col1
+ Group By Operator
+ aggregations:
+ expr: min(_col1)
+ bucketGroup: false
+ keys:
+ expr: _col0
+ type: string
+ mode: hash
+ outputColumnNames: _col0, _col1
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ table:
+ input format: org.apache.hadoop.mapred.SequenceFileInputFormat
+ output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+ serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+ Stage: Stage-3
+ Map Reduce
+ Alias -> Map Operator Tree:
+#### A masked pattern was here ####
+ TableScan
+ Reduce Output Operator
+ key expressions:
+ expr: _col0
+ type: string
+ sort order: +
+ Map-reduce partition columns:
+ expr: _col0
+ type: string
+ tag: -1
+ value expressions:
+ expr: _col1
+ type: int
+ Reduce Operator Tree:
+ Group By Operator
+ aggregations:
+ expr: min(VALUE._col0)
+ bucketGroup: false
+ keys:
+ expr: KEY._col0
+ type: string
+ mode: mergepartial
+ outputColumnNames: _col0, _col1
+ Select Operator
+ expressions:
+ expr: _col1
+ type: int
+ expr: _col0
+ type: string
+ outputColumnNames: _col0, _col1
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ table:
+ input format: org.apache.hadoop.mapred.SequenceFileInputFormat
+ output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+ serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
+
+ Stage: Stage-1
+ Map Reduce
+ Alias -> Map Operator Tree:
+ $INTNAME
+ TableScan
+ Reduce Output Operator
+ key expressions:
+ expr: _col0
+ type: int
+ expr: _col1
+ type: string
+ sort order: ++
+ Map-reduce partition columns:
+ expr: _col0
+ type: int
+ expr: _col1
+ type: string
+ tag: 1
+ value expressions:
+ expr: _col0
+ type: int
+ b
+ TableScan
+ alias: b
+ Reduce Output Operator
+ key expressions:
+ expr: p_size
+ type: int
+ expr: p_mfgr
+ type: string
+ sort order: ++
+ Map-reduce partition columns:
+ expr: p_size
+ type: int
+ expr: p_mfgr
+ type: string
+ tag: 0
+ value expressions:
+ expr: p_name
+ type: string
+ expr: p_mfgr
+ type: string
+ expr: p_size
+ type: int
+ Reduce Operator Tree:
+ Join Operator
+ condition map:
+ Left Outer Join0 to 1
+ condition expressions:
+ 0 {VALUE._col1} {VALUE._col2} {VALUE._col5}
+ 1 {VALUE._col0}
+ handleSkewJoin: false
+ outputColumnNames: _col1, _col2, _col5, _col11
+ Filter Operator
+ predicate:
+ expr: ((1 = 1) and _col11 is null)
+ type: boolean
+ Select Operator
+ expressions:
+ expr: _col2
+ type: string
+ expr: _col1
+ type: string
+ expr: _col5
+ type: int
+ outputColumnNames: _col0, _col1, _col2
+ File Output Operator
+ compressed: false
+ GlobalTableId: 0
+ 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
+
+
+PREHOOK: query: select p_mfgr, p_name, p_size
+from part b where b.p_size not 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 = a.p_mfgr
+ )
+order by p_mfgr, p_size
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: select p_mfgr, p_name, p_size
+from part b where b.p_size not 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 = a.p_mfgr
+ )
+order by p_mfgr, p_size
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique salmon chartreuse burlywood 6
+Manufacturer#1 almond aquamarine burnished black steel 28
+Manufacturer#1 almond antique chartreuse lavender yellow 34
+Manufacturer#1 almond aquamarine pink moccasin thistle 42
+Manufacturer#2 almond antique violet chocolate turquoise 14
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18
+Manufacturer#2 almond aquamarine rose maroon antique 25
+Manufacturer#2 almond antique violet turquoise frosted 40
+Manufacturer#3 almond antique forest lavender goldenrod 14
+Manufacturer#3 almond antique chartreuse khaki white 17
+Manufacturer#3 almond antique metallic orange dim 19
+Manufacturer#3 almond antique olive coral navajo 45
+Manufacturer#4 almond antique gainsboro frosted violet 10
+Manufacturer#4 almond azure aquamarine papaya violet 12
+Manufacturer#4 almond aquamarine floral ivory bisque 27
+Manufacturer#4 almond antique violet mint lemon 39
+Manufacturer#5 almond antique medium spring khaki 6
+Manufacturer#5 almond azure blanched chiffon midnight 23
+Manufacturer#5 almond antique blue firebrick mint 31
+Manufacturer#5 almond aquamarine dodger light gainsboro 46
+PREHOOK: query: -- non agg, non corr, Group By in Parent Query
+select li.l_partkey, count(*)
+from lineitem li
+where li.l_linenumber = 1 and
+ li.l_orderkey not in (select l_orderkey from lineitem where l_shipmode = 'AIR')
+group by li.l_partkey
+PREHOOK: type: QUERY
+PREHOOK: Input: default@lineitem
+#### A masked pattern was here ####
+POSTHOOK: query: -- non agg, non corr, Group By in Parent Query
+select li.l_partkey, count(*)
+from lineitem li
+where li.l_linenumber = 1 and
+ li.l_orderkey not in (select l_orderkey from lineitem where l_shipmode = 'AIR')
+group by li.l_partkey
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@lineitem
+#### A masked pattern was here ####
+450 1
+7068 1
+21636 1
+22630 1
+59694 1
+61931 1
+85951 1
+88035 1
+88362 1
+106170 1
+119477 1
+119767 1
+123076 1
+139636 1
+175839 1
+182052 1
+PREHOOK: query: -- alternate not in syntax
+select *
+from src
+where not src.key in ( select key from src s1 where s1.key > '2')
+order by key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+#### A masked pattern was here ####
+POSTHOOK: query: -- alternate not in syntax
+select *
+from src
+where not src.key in ( select key from src s1 where s1.key > '2')
+order by key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+#### A masked pattern was here ####
+0 val_0
+0 val_0
+0 val_0
+10 val_10
+100 val_100
+100 val_100
+103 val_103
+103 val_103
+104 val_104
+104 val_104
+105 val_105
+11 val_11
+111 val_111
+113 val_113
+113 val_113
+114 val_114
+116 val_116
+118 val_118
+118 val_118
+119 val_119
+119 val_119
+119 val_119
+12 val_12
+12 val_12
+120 val_120
+120 val_120
+125 val_125
+125 val_125
+126 val_126
+128 val_128
+128 val_128
+128 val_128
+129 val_129
+129 val_129
+131 val_131
+133 val_133
+134 val_134
+134 val_134
+136 val_136
+137 val_137
+137 val_137
+138 val_138
+138 val_138
+138 val_138
+138 val_138
+143 val_143
+145 val_145
+146 val_146
+146 val_146
+149 val_149
+149 val_149
+15 val_15
+15 val_15
+150 val_150
+152 val_152
+152 val_152
+153 val_153
+155 val_155
+156 val_156
+157 val_157
+158 val_158
+160 val_160
+162 val_162
+163 val_163
+164 val_164
+164 val_164
+165 val_165
+165 val_165
+166 val_166
+167 val_167
+167 val_167
+167 val_167
+168 val_168
+169 val_169
+169 val_169
+169 val_169
+169 val_169
+17 val_17
+170 val_170
+172 val_172
+172 val_172
+174 val_174
+174 val_174
+175 val_175
+175 val_175
+176 val_176
+176 val_176
+177 val_177
+178 val_178
+179 val_179
+179 val_179
+18 val_18
+18 val_18
+180 val_180
+181 val_181
+183 val_183
+186 val_186
+187 val_187
+187 val_187
+187 val_187
+189 val_189
+19 val_19
+190 val_190
+191 val_191
+191 val_191
+192 val_192
+193 val_193
+193 val_193
+193 val_193
+194 val_194
+195 val_195
+195 val_195
+196 val_196
+197 val_197
+197 val_197
+199 val_199
+199 val_199
+199 val_199
+2 val_2