You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by pr...@apache.org on 2015/09/25 20:42:29 UTC
[19/25] hive git commit: HIVE-11517 Vectorized auto_smb_mapjoin_14.q
produces different results (Matt McCline, reviewed by Vikram Dixit K)
HIVE-11517 Vectorized auto_smb_mapjoin_14.q produces different results (Matt McCline, reviewed by Vikram Dixit K)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/461e38ec
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/461e38ec
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/461e38ec
Branch: refs/heads/llap
Commit: 461e38ecee8b9fd1d829ff0884f78c1a75013bd3
Parents: 68d6cfd
Author: Matt McCline <mm...@hortonworks.com>
Authored: Thu Sep 24 15:23:50 2015 -0700
Committer: Matt McCline <mm...@hortonworks.com>
Committed: Thu Sep 24 15:23:50 2015 -0700
----------------------------------------------------------------------
.../test/resources/testconfiguration.properties | 1 +
.../exec/vector/VectorSMBMapJoinOperator.java | 15 +-
.../clientpositive/vector_auto_smb_mapjoin_14.q | 297 +++
.../tez/vector_auto_smb_mapjoin_14.q.out | 1576 +++++++++++++++
.../vector_auto_smb_mapjoin_14.q.out | 1792 ++++++++++++++++++
5 files changed, 3679 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/461e38ec/itests/src/test/resources/testconfiguration.properties
----------------------------------------------------------------------
diff --git a/itests/src/test/resources/testconfiguration.properties b/itests/src/test/resources/testconfiguration.properties
index b47d1b5..4f7b25f 100644
--- a/itests/src/test/resources/testconfiguration.properties
+++ b/itests/src/test/resources/testconfiguration.properties
@@ -190,6 +190,7 @@ minitez.query.files.shared=alter_merge_2_orc.q,\
update_two_cols.q,\
vector_acid3.q,\
vector_aggregate_9.q,\
+ vector_auto_smb_mapjoin_14.q,\
vector_between_in.q,\
vector_binary_join_groupby.q,\
vector_bucket.q,\
http://git-wip-us.apache.org/repos/asf/hive/blob/461e38ec/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorSMBMapJoinOperator.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorSMBMapJoinOperator.java b/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorSMBMapJoinOperator.java
index a2f8091..804ba17 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorSMBMapJoinOperator.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorSMBMapJoinOperator.java
@@ -38,7 +38,9 @@ import org.apache.hadoop.hive.ql.plan.ExprNodeDesc;
import org.apache.hadoop.hive.ql.plan.OperatorDesc;
import org.apache.hadoop.hive.ql.plan.SMBJoinDesc;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
+import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
+import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils.ObjectInspectorCopyOption;
/**
* VectorSMBJoinOperator.
@@ -123,8 +125,17 @@ public class VectorSMBMapJoinOperator extends SMBMapJoinOperator implements Vect
@Override
protected List<Object> smbJoinComputeKeys(Object row, byte alias) throws HiveException {
if (alias == this.posBigTable) {
- VectorizedRowBatch inBatch = (VectorizedRowBatch) row;
- return keyEvaluator.evaluate(keyValues[batchIndex]);
+
+ // The keyEvaluate reuses storage. That doesn't work with SMB MapJoin because it
+ // holds references to keys as it is merging.
+ List<Object> singletonListAndObjects = keyEvaluator.evaluate(keyValues[batchIndex]);
+ ArrayList<Object> result = new ArrayList<Object>(singletonListAndObjects.size());
+ for (int i = 0; i < singletonListAndObjects.size(); i++) {
+ result.add(ObjectInspectorUtils.copyToStandardObject(singletonListAndObjects.get(i),
+ joinKeysObjectInspectors[alias].get(i),
+ ObjectInspectorCopyOption.WRITABLE));
+ }
+ return result;
} else {
return super.smbJoinComputeKeys(row, alias);
}
http://git-wip-us.apache.org/repos/asf/hive/blob/461e38ec/ql/src/test/queries/clientpositive/vector_auto_smb_mapjoin_14.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/vector_auto_smb_mapjoin_14.q b/ql/src/test/queries/clientpositive/vector_auto_smb_mapjoin_14.q
new file mode 100644
index 0000000..32be5ee
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/vector_auto_smb_mapjoin_14.q
@@ -0,0 +1,297 @@
+SET hive.vectorized.execution.enabled=true;
+set hive.enforce.bucketing = true;
+set hive.enforce.sorting = true;
+set hive.exec.reducers.max = 1;
+
+-- SORT_QUERY_RESULTS
+
+CREATE TABLE tbl1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC;
+CREATE TABLE tbl2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC;
+
+insert overwrite table tbl1
+select * from src where key < 10;
+
+insert overwrite table tbl2
+select * from src where key < 10;
+
+set hive.optimize.bucketmapjoin = true;
+set hive.optimize.bucketmapjoin.sortedmerge = true;
+set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
+
+set hive.auto.convert.sortmerge.join=true;
+
+-- The join is being performed as part of sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1;
+
+select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1;
+
+-- The join is being performed as part of more than one sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2;
+
+select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2;
+
+-- A join is being performed across different sub-queries, where a join is being performed in each of them.
+-- Each sub-query should be converted to a sort-merge join.
+explain
+select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key;
+
+select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key;
+
+-- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key;
+
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key;
+
+-- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join, although there is more than one level of sub-query
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key;
+
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key;
+
+-- Both the tables are nested sub-queries i.e more then 1 level of sub-query.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key;
+
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key;
+
+-- The subquery itself is being joined. Since the sub-query only contains selects and filters and the join key
+-- is not getting modified, it should be converted to a sort-merge join. Note that the sub-query modifies one
+-- item, but that is not part of the join key.
+explain
+select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key;
+
+select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key;
+
+-- Since the join key is modified by the sub-query, neither sort-merge join not bucketized map-side
+-- join should be performed
+explain
+select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key;
+
+select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key;
+
+-- One of the tables is a sub-query and the other is not.
+-- It should be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key;
+
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key;
+
+-- There are more than 2 inputs to the join, all of them being sub-queries.
+-- It should be converted to to a sort-merge join
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on (subq1.key = subq2.key)
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key);
+
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key);
+
+-- The join is being performed on a nested sub-query, and an aggregation is performed after that.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a;
+
+select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a;
+
+CREATE TABLE dest1(key int, value string);
+CREATE TABLE dest2(key int, val1 string, val2 string);
+
+-- The join is followed by a multi-table insert. It should be converted to
+-- a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2;
+
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2;
+
+select * from dest1;
+select * from dest2;
+
+DROP TABLE dest2;
+CREATE TABLE dest2(key int, cnt int);
+
+-- The join is followed by a multi-table insert, and one of the inserts involves a reducer.
+-- It should be converted to a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key;
+
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key;
+
+select * from dest1;
+select * from dest2;
http://git-wip-us.apache.org/repos/asf/hive/blob/461e38ec/ql/src/test/results/clientpositive/tez/vector_auto_smb_mapjoin_14.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/tez/vector_auto_smb_mapjoin_14.q.out b/ql/src/test/results/clientpositive/tez/vector_auto_smb_mapjoin_14.q.out
new file mode 100644
index 0000000..480c4e1
--- /dev/null
+++ b/ql/src/test/results/clientpositive/tez/vector_auto_smb_mapjoin_14.q.out
@@ -0,0 +1,1576 @@
+PREHOOK: query: -- SORT_QUERY_RESULTS
+
+CREATE TABLE tbl1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@tbl1
+POSTHOOK: query: -- SORT_QUERY_RESULTS
+
+CREATE TABLE tbl1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@tbl1
+PREHOOK: query: CREATE TABLE tbl2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@tbl2
+POSTHOOK: query: CREATE TABLE tbl2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORC
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@tbl2
+PREHOOK: query: insert overwrite table tbl1
+select * from src where key < 10
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+PREHOOK: Output: default@tbl1
+POSTHOOK: query: insert overwrite table tbl1
+select * from src where key < 10
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+POSTHOOK: Output: default@tbl1
+POSTHOOK: Lineage: tbl1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
+POSTHOOK: Lineage: tbl1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
+PREHOOK: query: insert overwrite table tbl2
+select * from src where key < 10
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+PREHOOK: Output: default@tbl2
+POSTHOOK: query: insert overwrite table tbl2
+select * from src where key < 10
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+POSTHOOK: Output: default@tbl2
+POSTHOOK: Lineage: tbl2.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
+POSTHOOK: Lineage: tbl2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
+PREHOOK: query: -- The join is being performed as part of sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The join is being performed as part of sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_13]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_20]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_10]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_9]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_18]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_17]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_1]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Filter Operator [FIL_16]
+ predicate:key is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+22
+PREHOOK: query: -- The join is being performed as part of more than one sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The join is being performed as part of more than one sub-query. It should be converted to a sort-merge join
+explain
+select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 3
+ File Output Operator [FS_18]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 16 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"}
+ Group By Operator [OP_28]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_15]
+ sort order:
+ Statistics:Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [OP_27]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [OP_26]
+ Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [OP_25]
+ | aggregations:["count(VALUE._col0)"]
+ | keys:KEY._col0 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_10]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: bigint)
+ Group By Operator [GBY_9]
+ aggregations:["count()"]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_23]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_22]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_1]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Filter Operator [FIL_21]
+ predicate:key is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+(
+ select key, count(*) from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1
+ group by key
+) subq2
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+6
+PREHOOK: query: -- A join is being performed across different sub-queries, where a join is being performed in each of them.
+-- Each sub-query should be converted to a sort-merge join.
+explain
+select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- A join is being performed across different sub-queries, where a join is being performed in each of them.
+-- Each sub-query should be converted to a sort-merge join.
+explain
+select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
+Reducer 6 <- Map 5 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 3
+ File Output Operator [FS_32]
+ compressed:false
+ Statistics:Num rows: 2 Data size: 224 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"}
+ Select Operator [SEL_31]
+ outputColumnNames:["_col0","_col1","_col2"]
+ Statistics:Num rows: 2 Data size: 224 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_49]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col3"]
+ | Statistics:Num rows: 2 Data size: 224 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_51]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: bigint)
+ | Group By Operator [OP_50]
+ | | aggregations:["count(VALUE._col0)"]
+ | | keys:KEY._col0 (type: int)
+ | | outputColumnNames:["_col0","_col1"]
+ | | Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ | |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_10]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: bigint)
+ | Group By Operator [GBY_9]
+ | aggregations:["count()"]
+ | keys:_col0 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ | Merge Join Operator [MERGEJOIN_45]
+ | | condition map:[{"":"Inner Join 0 to 1"}]
+ | | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | | outputColumnNames:["_col0"]
+ | | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ | |
+ | |<-Filter Operator [FIL_42]
+ | | predicate:key is not null (type: boolean)
+ | | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | | TableScan [TS_1]
+ | | alias:b
+ | | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ | |<-Filter Operator [FIL_41]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_0]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 6 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_53]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: bigint)
+ Group By Operator [OP_52]
+ | aggregations:["count(VALUE._col0)"]
+ | keys:KEY._col0 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_23]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: bigint)
+ Group By Operator [GBY_22]
+ aggregations:["count()"]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_47]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_44]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_14]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Filter Operator [FIL_43]
+ predicate:key is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_13]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select src1.key, src1.cnt1, src2.cnt1 from
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq1 group by key
+) src1
+join
+(
+ select key, count(*) as cnt1 from
+ (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+ ) subq2 group by key
+) src2
+on src1.key = src2.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+0 9 9
+2 1 1
+4 1 1
+5 9 9
+8 1 1
+9 1 1
+PREHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_16]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_23]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_13]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_12]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_21]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | Statistics:Num rows: 3 Data size: 306 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Select Operator [SEL_5]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_20]
+ | predicate:(key < 6) (type: boolean)
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_3]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_19]
+ predicate:(key < 6) (type: boolean)
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+20
+PREHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join, although there is more than one level of sub-query
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join, although there is more than one level of sub-query
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_16]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_23]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_13]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_12]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_21]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"key (type: int)"}
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_20]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_5]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_19]
+ predicate:(((key < 8) and (key < 6)) and key is not null) (type: boolean)
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join tbl2 b
+ on subq2.key = b.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+20
+PREHOOK: query: -- Both the tables are nested sub-queries i.e more then 1 level of sub-query.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- Both the tables are nested sub-queries i.e more then 1 level of sub-query.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_20]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_27]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_17]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_16]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_25]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | Statistics:Num rows: 1 Data size: 102 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Select Operator [SEL_7]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_24]
+ | predicate:(((key < 8) and (key < 6)) and key is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_5]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_23]
+ predicate:(((key < 8) and (key < 6)) and key is not null) (type: boolean)
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+ join
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq3
+ where key < 6
+ ) subq4
+ on subq2.key = subq4.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+#### A masked pattern was here ####
+20
+PREHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters and the join key
+-- is not getting modified, it should be converted to a sort-merge join. Note that the sub-query modifies one
+-- item, but that is not part of the join key.
+explain
+select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The subquery itself is being joined. Since the sub-query only contains selects and filters and the join key
+-- is not getting modified, it should be converted to a sort-merge join. Note that the sub-query modifies one
+-- item, but that is not part of the join key.
+explain
+select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_16]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_23]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_13]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_12]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_21]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | Statistics:Num rows: 3 Data size: 306 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Select Operator [SEL_5]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_20]
+ | predicate:(key < 8) (type: boolean)
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_3]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_19]
+ predicate:(key < 8) (type: boolean)
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
+ join
+ (select a.key as key, concat(a.value, a.value) as value from tbl2 a where key < 8) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+20
+PREHOOK: query: -- Since the join key is modified by the sub-query, neither sort-merge join not bucketized map-side
+-- join should be performed
+explain
+select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- Since the join key is modified by the sub-query, neither sort-merge join not bucketized map-side
+-- join should be performed
+explain
+select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 4 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 3
+ File Output Operator [FS_14]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_26]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_11]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_10]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_19]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_22]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_21]
+ | predicate:_col0 is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [OP_20]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_0]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_25]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_24]
+ predicate:_col0 is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [OP_23]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_2]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
+ join
+ (select a.key +1 as key, concat(a.value, a.value) as value from tbl2 a) subq2
+ on subq1.key = subq2.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+22
+PREHOOK: query: -- One of the tables is a sub-query and the other is not.
+-- It should be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- One of the tables is a sub-query and the other is not.
+-- It should be converted to a sort-merge join.
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_14]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_21]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_11]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_10]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_19]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"key (type: int)"}
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_18]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_3]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_17]
+ predicate:(key < 6) (type: boolean)
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join tbl2 a on subq1.key = a.key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+20
+PREHOOK: query: -- There are more than 2 inputs to the join, all of them being sub-queries.
+-- It should be converted to to a sort-merge join
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on (subq1.key = subq2.key)
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key)
+PREHOOK: type: QUERY
+POSTHOOK: query: -- There are more than 2 inputs to the join, all of them being sub-queries.
+-- It should be converted to to a sort-merge join
+explain
+select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on (subq1.key = subq2.key)
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key)
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_21]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_34]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_18]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_17]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_31]
+ | condition map:[{"":"Inner Join 0 to 1"},{"":"Inner Join 0 to 2"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)","2":"_col0 (type: int)"}
+ | Statistics:Num rows: 6 Data size: 613 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Select Operator [SEL_5]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_29]
+ | predicate:(key < 6) (type: boolean)
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_3]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Select Operator [SEL_8]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_30]
+ | predicate:(key < 6) (type: boolean)
+ | Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_6]
+ | alias:a
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_28]
+ predicate:(key < 6) (type: boolean)
+ Statistics:Num rows: 3 Data size: 279 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from
+ (select a.key as key, a.value as value from tbl1 a where key < 6) subq1
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq2
+ on subq1.key = subq2.key
+ join
+ (select a.key as key, a.value as value from tbl2 a where key < 6) subq3
+ on (subq1.key = subq3.key)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+56
+PREHOOK: query: -- The join is being performed on a nested sub-query, and an aggregation is performed after that.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The join is being performed on a nested sub-query, and an aggregation is performed after that.
+-- The join should be converted to a sort-merge join
+explain
+select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a
+POSTHOOK: type: QUERY
+Plan not optimized by CBO due to missing statistics. Please check log for more details.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 2
+ File Output Operator [FS_17]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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"}
+ Group By Operator [OP_24]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_14]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_13]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_22]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"key (type: int)"}
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_21]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_5]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Select Operator [SEL_2]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_20]
+ predicate:(((key < 8) and (key < 6)) and key is not null) (type: boolean)
+ Statistics:Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+
+PREHOOK: query: select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+POSTHOOK: query: select count(*) from (
+ select subq2.key as key, subq2.value as value1, b.value as value2 from
+ (
+ select * from
+ (
+ select a.key as key, a.value as value from tbl1 a where key < 8
+ ) subq1
+ where key < 6
+ ) subq2
+join tbl2 b
+on subq2.key = b.key) a
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+#### A masked pattern was here ####
+20
+PREHOOK: query: CREATE TABLE dest1(key int, value string)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@dest1
+POSTHOOK: query: CREATE TABLE dest1(key int, value string)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@dest1
+PREHOOK: query: CREATE TABLE dest2(key int, val1 string, val2 string)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@dest2
+POSTHOOK: query: CREATE TABLE dest2(key int, val1 string, val2 string)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@dest2
+PREHOOK: query: -- The join is followed by a multi-table insert. It should be converted to
+-- a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The join is followed by a multi-table insert. It should be converted to
+-- a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2
+POSTHOOK: type: QUERY
+Plan not optimized by CBO.
+
+Stage-4
+ Stats-Aggr Operator
+ Stage-0
+ Move Operator
+ 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","name:":"default.dest1"}
+ Stage-3
+ Dependency Collection{}
+ Stage-2
+ Map 1
+ File Output Operator [FS_9]
+ compressed:false
+ Statistics:Num rows: 5 Data size: 511 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","name:":"default.dest1"}
+ Select Operator [SEL_8]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_7]
+ outputColumnNames:["_col0","_col1","_col2"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_16]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col6"]
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_15]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_1]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Filter Operator [FIL_14]
+ predicate:key is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ File Output Operator [FS_11]
+ compressed:false
+ Statistics:Num rows: 5 Data size: 511 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","name:":"default.dest2"}
+ Please refer to the previous Select Operator [SEL_7]
+Stage-5
+ Stats-Aggr Operator
+ Stage-1
+ Move Operator
+ 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","name:":"default.dest2"}
+ Please refer to the previous Stage-3
+
+PREHOOK: query: from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+PREHOOK: Output: default@dest1
+PREHOOK: Output: default@dest2
+POSTHOOK: query: from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, val1, val2
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+POSTHOOK: Output: default@dest1
+POSTHOOK: Output: default@dest2
+POSTHOOK: Lineage: dest1.key SIMPLE [(tbl1)a.FieldSchema(name:key, type:int, comment:null), ]
+POSTHOOK: Lineage: dest1.value SIMPLE [(tbl1)a.FieldSchema(name:value, type:string, comment:null), ]
+POSTHOOK: Lineage: dest2.key SIMPLE [(tbl1)a.FieldSchema(name:key, type:int, comment:null), ]
+POSTHOOK: Lineage: dest2.val1 SIMPLE [(tbl1)a.FieldSchema(name:value, type:string, comment:null), ]
+POSTHOOK: Lineage: dest2.val2 SIMPLE [(tbl2)b.FieldSchema(name:value, type:string, comment:null), ]
+PREHOOK: query: select * from dest1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@dest1
+#### A masked pattern was here ####
+POSTHOOK: query: select * from dest1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@dest1
+#### A masked pattern was here ####
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+2 val_2
+4 val_4
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+8 val_8
+9 val_9
+PREHOOK: query: select * from dest2
+PREHOOK: type: QUERY
+PREHOOK: Input: default@dest2
+#### A masked pattern was here ####
+POSTHOOK: query: select * from dest2
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@dest2
+#### A masked pattern was here ####
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+0 val_0 val_0
+2 val_2 val_2
+4 val_4 val_4
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+5 val_5 val_5
+8 val_8 val_8
+9 val_9 val_9
+PREHOOK: query: DROP TABLE dest2
+PREHOOK: type: DROPTABLE
+PREHOOK: Input: default@dest2
+PREHOOK: Output: default@dest2
+POSTHOOK: query: DROP TABLE dest2
+POSTHOOK: type: DROPTABLE
+POSTHOOK: Input: default@dest2
+POSTHOOK: Output: default@dest2
+PREHOOK: query: CREATE TABLE dest2(key int, cnt int)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@dest2
+POSTHOOK: query: CREATE TABLE dest2(key int, cnt int)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@dest2
+PREHOOK: query: -- The join is followed by a multi-table insert, and one of the inserts involves a reducer.
+-- It should be converted to a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key
+PREHOOK: type: QUERY
+POSTHOOK: query: -- The join is followed by a multi-table insert, and one of the inserts involves a reducer.
+-- It should be converted to a sort-merge join
+explain
+from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key
+POSTHOOK: type: QUERY
+Plan not optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+
+Stage-4
+ Stats-Aggr Operator
+ Stage-0
+ Move Operator
+ 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","name:":"default.dest1"}
+ Stage-3
+ Dependency Collection{}
+ Stage-2
+ Reducer 2
+ File Output Operator [FS_25]
+ compressed:false
+ Statistics:Num rows: 2 Data size: 204 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","name:":"default.dest2"}
+ Select Operator [OP_24]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [OP_23]
+ | aggregations:["count(VALUE._col0)"]
+ | keys:KEY._col0 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ File Output Operator [FS_9]
+ compressed:false
+ Statistics:Num rows: 5 Data size: 511 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","name:":"default.dest1"}
+ Merge Join Operator [MERGEJOIN_21]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"key (type: int)","1":"key (type: int)"}
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ |
+ |<-Filter Operator [FIL_20]
+ | predicate:key is not null (type: boolean)
+ | Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_1]
+ | alias:b
+ | Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ |<-Filter Operator [FIL_19]
+ predicate:key is not null (type: boolean)
+ Statistics:Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_0]
+ alias:a
+ Statistics:Num rows: 10 Data size: 930 Basic stats: COMPLETE Column stats: NONE
+ Reduce Output Operator [RS_12]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: bigint)
+ Group By Operator [GBY_11]
+ aggregations:["count()"]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_10]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 5 Data size: 511 Basic stats: COMPLETE Column stats: NONE
+ Please refer to the previous Merge Join Operator [MERGEJOIN_21]
+Stage-5
+ Stats-Aggr Operator
+ Stage-1
+ Move Operator
+ 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","name:":"default.dest2"}
+ Please refer to the previous Stage-3
+
+PREHOOK: query: from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key
+PREHOOK: type: QUERY
+PREHOOK: Input: default@tbl1
+PREHOOK: Input: default@tbl2
+PREHOOK: Output: default@dest1
+PREHOOK: Output: default@dest2
+POSTHOOK: query: from (
+ select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
+) subq1
+insert overwrite table dest1 select key, val1
+insert overwrite table dest2 select key, count(*) group by key
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@tbl1
+POSTHOOK: Input: default@tbl2
+POSTHOOK: Output: default@dest1
+POSTHOOK: Output: default@dest2
+POSTHOOK: Lineage: dest1.key SIMPLE [(tbl1)a.FieldSchema(name:key, type:int, comment:null), ]
+POSTHOOK: Lineage: dest1.value SIMPLE [(tbl1)a.FieldSchema(name:value, type:string, comment:null), ]
+POSTHOOK: Lineage: dest2.cnt EXPRESSION [(tbl1)a.null, (tbl2)b.null, ]
+POSTHOOK: Lineage: dest2.key SIMPLE [(tbl1)a.FieldSchema(name:key, type:int, comment:null), ]
+PREHOOK: query: select * from dest1
+PREHOOK: type: QUERY
+PREHOOK: Input: default@dest1
+#### A masked pattern was here ####
+POSTHOOK: query: select * from dest1
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@dest1
+#### A masked pattern was here ####
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+0 val_0
+2 val_2
+4 val_4
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+5 val_5
+8 val_8
+9 val_9
+PREHOOK: query: select * from dest2
+PREHOOK: type: QUERY
+PREHOOK: Input: default@dest2
+#### A masked pattern was here ####
+POSTHOOK: query: select * from dest2
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@dest2
+#### A masked pattern was here ####
+0 9
+2 1
+4 1
+5 9
+8 1
+9 1