hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hashut...@apache.org
Subject 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/
Date Wed, 23 Oct 2013 14:54:18 GMT
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



Mime
View raw message