You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ha...@apache.org on 2013/04/10 02:07:09 UTC
svn commit: r1466300 [10/13] - in /hive/trunk:
cli/src/java/org/apache/hadoop/hive/cli/
ql/src/test/queries/clientpositive/ ql/src/test/results/clientpositive/
Modified: hive/trunk/ql/src/test/results/clientpositive/skewjoinopt6.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/skewjoinopt6.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/skewjoinopt6.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/skewjoinopt6.q.out Wed Apr 10 00:06:55 2013
@@ -24,10 +24,20 @@ PREHOOK: Output: default@t2
POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/T2.txt' INTO TABLE T2
POSTHOOK: type: LOAD
POSTHOOK: Output: default@t2
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Both the join tables are skewed by 2 keys, and one of the skewed values
+-- is common to both the tables. The join key is a subset of the skewed key set:
+-- it only contains the first skewed key for both the tables
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.* FROM T1 a JOIN T2 b ON a.key = b.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Both the join tables are skewed by 2 keys, and one of the skewed values
+-- is common to both the tables. The join key is a subset of the skewed key set:
+-- it only contains the first skewed key for both the tables
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.* FROM T1 a JOIN T2 b ON a.key = b.key
POSTHOOK: type: QUERY
ABSTRACT SYNTAX TREE:
Modified: hive/trunk/ql/src/test/results/clientpositive/skewjoinopt7.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/skewjoinopt7.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/skewjoinopt7.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/skewjoinopt7.q.out Wed Apr 10 00:06:55 2013
@@ -35,10 +35,20 @@ PREHOOK: Output: default@t3
POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/T3.txt' INTO TABLE T3
POSTHOOK: type: LOAD
POSTHOOK: Output: default@t3
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- This test is for validating skewed join compile time optimization for more than
+-- 2 tables. The join key is the same, and so a 3-way join would be performed.
+-- 2 of the 3 tables are skewed on the join key
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.*, c.* FROM T1 a JOIN T2 b ON a.key = b.key JOIN T3 c on a.key = c.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- This test is for validating skewed join compile time optimization for more than
+-- 2 tables. The join key is the same, and so a 3-way join would be performed.
+-- 2 of the 3 tables are skewed on the join key
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.*, c.* FROM T1 a JOIN T2 b ON a.key = b.key JOIN T3 c on a.key = c.key
POSTHOOK: type: QUERY
ABSTRACT SYNTAX TREE:
Modified: hive/trunk/ql/src/test/results/clientpositive/skewjoinopt8.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/skewjoinopt8.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/skewjoinopt8.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/skewjoinopt8.q.out Wed Apr 10 00:06:55 2013
@@ -33,10 +33,20 @@ PREHOOK: Output: default@t3
POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/T3.txt' INTO TABLE T3
POSTHOOK: type: LOAD
POSTHOOK: Output: default@t3
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- This test is for validating skewed join compile time optimization for more than
+-- 2 tables. The join key is the same, and so a 3-way join would be performed.
+-- 1 of the 3 tables are skewed on the join key
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.*, c.* FROM T1 a JOIN T2 b ON a.key = b.key JOIN T3 c on a.key = c.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- This test is for validating skewed join compile time optimization for more than
+-- 2 tables. The join key is the same, and so a 3-way join would be performed.
+-- 1 of the 3 tables are skewed on the join key
+-- adding a order by at the end to make the results deterministic
+
+EXPLAIN
SELECT a.*, b.*, c.* FROM T1 a JOIN T2 b ON a.key = b.key JOIN T3 c on a.key = c.key
POSTHOOK: type: QUERY
ABSTRACT SYNTAX TREE:
Modified: hive/trunk/ql/src/test/results/clientpositive/skewjoinopt9.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/skewjoinopt9.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/skewjoinopt9.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/skewjoinopt9.q.out Wed Apr 10 00:06:55 2013
@@ -22,7 +22,10 @@ PREHOOK: Output: default@t2
POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/T2.txt' INTO TABLE T2
POSTHOOK: type: LOAD
POSTHOOK: Output: default@t2
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- no skew join compile time optimization would be performed if one of the
+-- join sources is a sub-query consisting of a union all
+-- adding a order by at the end to make the results deterministic
+EXPLAIN
select * from
(
select key, val from T1
@@ -31,7 +34,10 @@ select key, val from T1
) subq1
join T2 b on subq1.key = b.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- no skew join compile time optimization would be performed if one of the
+-- join sources is a sub-query consisting of a union all
+-- adding a order by at the end to make the results deterministic
+EXPLAIN
select * from
(
select key, val from T1
@@ -186,14 +192,18 @@ POSTHOOK: Input: default@t2
8 28 8 18
8 28 8 18
8 28 8 18
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- no skew join compile time optimization would be performed if one of the
+-- join sources is a sub-query consisting of a group by
+EXPLAIN
select * from
(
select key, count(1) as cnt from T1 group by key
) subq1
join T2 b on subq1.key = b.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- no skew join compile time optimization would be performed if one of the
+-- join sources is a sub-query consisting of a group by
+EXPLAIN
select * from
(
select key, count(1) as cnt from T1 group by key
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin9.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin9.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin9.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin9.q.out Wed Apr 10 00:06:55 2013
@@ -8,13 +8,15 @@ PREHOOK: type: CREATETABLE
POSTHOOK: query: create table hive_test_smb_bucket2 (key int, value string) partitioned by (ds string) clustered by (key) sorted by (key) into 2 buckets
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@hive_test_smb_bucket2
-PREHOOK: query: explain extended
+PREHOOK: query: -- empty partitions (HIVE-3205)
+explain extended
SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2
FROM hive_test_smb_bucket1 a JOIN
hive_test_smb_bucket2 b
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and b.key IS NOT NULL
PREHOOK: type: QUERY
-POSTHOOK: query: explain extended
+POSTHOOK: query: -- empty partitions (HIVE-3205)
+explain extended
SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2
FROM hive_test_smb_bucket1 a JOIN
hive_test_smb_bucket2 b
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_10.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_10.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_10.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_10.q.out Wed Apr 10 00:06:55 2013
@@ -17,10 +17,14 @@ POSTHOOK: query: alter table tmp_smb_buc
POSTHOOK: type: ALTERTABLE_ADDPARTS
POSTHOOK: Input: default@tmp_smb_bucket_10
POSTHOOK: Output: default@tmp_smb_bucket_10@ds=2
-PREHOOK: query: load data local inpath '../data/files/smbbucket_1.rc' INTO TABLE tmp_smb_bucket_10 partition(ds='1')
+PREHOOK: query: -- add dummy files to make sure that the number of files in each partition is same as number of buckets
+
+load data local inpath '../data/files/smbbucket_1.rc' INTO TABLE tmp_smb_bucket_10 partition(ds='1')
PREHOOK: type: LOAD
PREHOOK: Output: default@tmp_smb_bucket_10@ds=1
-POSTHOOK: query: load data local inpath '../data/files/smbbucket_1.rc' INTO TABLE tmp_smb_bucket_10 partition(ds='1')
+POSTHOOK: query: -- add dummy files to make sure that the number of files in each partition is same as number of buckets
+
+load data local inpath '../data/files/smbbucket_1.rc' INTO TABLE tmp_smb_bucket_10 partition(ds='1')
POSTHOOK: type: LOAD
POSTHOOK: Output: default@tmp_smb_bucket_10@ds=1
PREHOOK: query: load data local inpath '../data/files/smbbucket_2.rc' INTO TABLE tmp_smb_bucket_10 partition(ds='1')
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_11.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_11.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_11.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_11.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,12 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: query: -- This test verifies that the output of a sort merge join on 2 partitions (one on each side of the join) is bucketed
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: query: -- This test verifies that the output of a sort merge join on 2 partitions (one on each side of the join) is bucketed
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
@@ -26,19 +32,23 @@ POSTHOOK: Lineage: test_table1 PARTITION
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) INTO 16 BUCKETS
+PREHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) INTO 16 BUCKETS
+POSTHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table3
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted tables, bucketing is not enforced
+EXPLAIN EXTENDED
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted tables, bucketing is not enforced
+EXPLAIN EXTENDED
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds = '1'
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -213,14 +223,16 @@ POSTHOOK: Lineage: test_table2 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value, type:string, comment:null), ]
-PREHOOK: query: SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+PREHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
PREHOOK: type: QUERY
PREHOOK: Input: default@test_table1
PREHOOK: Input: default@test_table1@ds=1
PREHOOK: Input: default@test_table3
PREHOOK: Input: default@test_table3@ds=1
#### A masked pattern was here ####
-POSTHOOK: query: SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+POSTHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
POSTHOOK: type: QUERY
POSTHOOK: Input: default@test_table1
POSTHOOK: Input: default@test_table1@ds=1
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,12 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: query: -- This test verifies that the output of a sort merge join on 1 big partition with multiple small partitions is bucketed and sorted
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: query: -- This test verifies that the output of a sort merge join on 1 big partition with multiple small partitions is bucketed and sorted
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
@@ -38,9 +44,11 @@ POSTHOOK: Lineage: test_table2 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table3
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -51,10 +59,12 @@ POSTHOOK: Lineage: test_table2 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted tables, bucketing is not enforced
+EXPLAIN EXTENDED
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted tables, bucketing is not enforced
+EXPLAIN EXTENDED
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1'
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -243,14 +253,16 @@ POSTHOOK: Lineage: test_table2 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value, type:string, comment:null), ]
-PREHOOK: query: SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+PREHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
PREHOOK: type: QUERY
PREHOOK: Input: default@test_table1
PREHOOK: Input: default@test_table1@ds=1
PREHOOK: Input: default@test_table3
PREHOOK: Input: default@test_table3@ds=1
#### A masked pattern was here ####
-POSTHOOK: query: SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+POSTHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET 2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
POSTHOOK: type: QUERY
POSTHOOK: Input: default@test_table1
POSTHOOK: Input: default@test_table1@ds=1
@@ -268,11 +280,13 @@ POSTHOOK: Lineage: test_table2 PARTITION
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value, type:string, comment:null), ]
879
-PREHOOK: query: explain extended
+PREHOOK: query: -- Join data from the sampled buckets of 2 tables to verify the data is bucketed and sorted
+explain extended
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2')
SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1 b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
PREHOOK: type: QUERY
-POSTHOOK: query: explain extended
+POSTHOOK: query: -- Join data from the sampled buckets of 2 tables to verify the data is bucketed and sorted
+explain extended
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2')
SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1 b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_13.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_13.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_13.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_13.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,12 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC) INTO 16 BUCKETS
+PREHOOK: query: -- This test verifies that the sort merge join optimizer works when the tables are joined on columns with different names
+
+-- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC) INTO 16 BUCKETS
+POSTHOOK: query: -- This test verifies that the sort merge join optimizer works when the tables are joined on columns with different names
+
+-- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (value INT, key STRING) CLUSTERED BY (value) SORTED BY (value ASC) INTO 16 BUCKETS
@@ -48,10 +54,14 @@ POSTHOOK: Lineage: test_table3.key EXPRE
POSTHOOK: Lineage: test_table3.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table4.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table4.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- Join data from 2 tables on their respective sorted columns (one each, with different names) and
+-- verify sort merge join is used
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.value ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- Join data from 2 tables on their respective sorted columns (one each, with different names) and
+-- verify sort merge join is used
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.value ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -221,10 +231,14 @@ POSTHOOK: Lineage: test_table4.value SIM
0 val_0 0 val_0
0 val_0 0 val_0
2 val_2 2 val_2
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- Join data from 2 tables on their respective columns (two each, with the same names but sorted
+-- with different priorities) and verify sort merge join is not used
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table3 a JOIN test_table4 b ON a.key = b.value ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- Join data from 2 tables on their respective columns (two each, with the same names but sorted
+-- with different priorities) and verify sort merge join is not used
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table3 a JOIN test_table4 b ON a.key = b.value ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out Wed Apr 10 00:06:55 2013
@@ -34,12 +34,14 @@ POSTHOOK: Lineage: tbl1.key EXPRESSION [
POSTHOOK: Lineage: tbl1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
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: explain
+PREHOOK: query: -- The mapjoin is being performed as part of sub-query. It should be converted to a sort-merge join
+explain
select count(*) from (
select /*+mapjoin(a)*/ 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: explain
+POSTHOOK: query: -- The mapjoin is being performed as part of sub-query. It should be converted to a sort-merge join
+explain
select count(*) from (
select /*+mapjoin(a)*/ a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
) subq1
@@ -129,7 +131,9 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
22
-PREHOOK: query: explain
+PREHOOK: query: -- The mapjoin is being performed as part of sub-query. It should be converted to a sort-merge join
+-- Add a order by at the end to make the results deterministic.
+explain
select key, count(*) from
(
select /*+mapjoin(a)*/ a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
@@ -137,7 +141,9 @@ select key, count(*) from
group by key
order by key
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The mapjoin is being performed as part of sub-query. It should be converted to a sort-merge join
+-- Add a order by at the end to make the results deterministic.
+explain
select key, count(*) from
(
select /*+mapjoin(a)*/ a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
@@ -285,7 +291,8 @@ POSTHOOK: Lineage: tbl2.value SIMPLE [(s
5 9
8 1
9 1
-PREHOOK: query: explain
+PREHOOK: query: -- The mapjoin 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
@@ -295,7 +302,8 @@ select count(*) from
group by key
) subq2
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The mapjoin 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
@@ -449,14 +457,18 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
6
-PREHOOK: query: explain
+PREHOOK: query: -- The subquery itself is being map-joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join.
+explain
select /*+mapjoin(subq1)*/ 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: explain
+POSTHOOK: query: -- The subquery itself is being map-joined. Since the sub-query only contains selects and filters, it should
+-- be converted to a sort-merge join.
+explain
select /*+mapjoin(subq1)*/ count(*) from
(select a.key as key, a.value as value from tbl1 a where key < 6) subq1
join
@@ -561,7 +573,9 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+PREHOOK: query: -- The subquery itself is being map-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 /*+mapjoin(subq2)*/ count(*) from
(
select * from
@@ -573,7 +587,9 @@ select /*+mapjoin(subq2)*/ count(*) from
join tbl2 b
on subq2.key = b.key
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The subquery itself is being map-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 /*+mapjoin(subq2)*/ count(*) from
(
select * from
@@ -684,7 +700,9 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+PREHOOK: query: -- Both the big table and the small table 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 /*+mapjoin(subq2)*/ count(*) from
(
select * from
@@ -703,7 +721,9 @@ select /*+mapjoin(subq2)*/ count(*) from
) subq4
on subq2.key = subq4.key
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- Both the big table and the small table 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 /*+mapjoin(subq2)*/ count(*) from
(
select * from
@@ -842,14 +862,20 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+PREHOOK: query: -- The subquery itself is being map-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 /*+mapjoin(subq1)*/ 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: explain
+POSTHOOK: query: -- The subquery itself is being map-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 /*+mapjoin(subq1)*/ count(*) from
(select a.key as key, concat(a.value, a.value) as value from tbl1 a where key < 8) subq1
join
@@ -954,14 +980,18 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+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 /*+mapjoin(subq1)*/ 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: explain
+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 /*+mapjoin(subq1)*/ count(*) from
(select a.key +1 as key, concat(a.value, a.value) as value from tbl1 a) subq1
join
@@ -1090,12 +1120,16 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
22
-PREHOOK: query: explain
+PREHOOK: query: -- The small table is a sub-query and the big table is not.
+-- It should be converted to a sort-merge join.
+explain
select /*+mapjoin(subq1)*/ 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: explain
+POSTHOOK: query: -- The small table is a sub-query and the big table is not.
+-- It should be converted to a sort-merge join.
+explain
select /*+mapjoin(subq1)*/ 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
@@ -1185,12 +1219,16 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+PREHOOK: query: -- The big table is a sub-query and the small table is not.
+-- It should be converted to a sort-merge join.
+explain
select /*+mapjoin(a)*/ 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: explain
+POSTHOOK: query: -- The big table is a sub-query and the small table is not.
+-- It should be converted to a sort-merge join.
+explain
select /*+mapjoin(a)*/ 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
@@ -1289,7 +1327,9 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
20
-PREHOOK: query: explain
+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 /*+mapjoin(subq1, subq2)*/ count(*) from
(select a.key as key, a.value as value from tbl1 a where key < 6) subq1
join
@@ -1299,7 +1339,9 @@ select /*+mapjoin(subq1, subq2)*/ count(
(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: explain
+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 /*+mapjoin(subq1, subq2)*/ count(*) from
(select a.key as key, a.value as value from tbl1 a where key < 6) subq1
join
@@ -1416,7 +1458,9 @@ POSTHOOK: Lineage: tbl1.value SIMPLE [(s
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), ]
56
-PREHOOK: query: explain
+PREHOOK: query: -- The mapjoin 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 /*+mapjoin(subq2)*/ subq2.key as key, subq2.value as value1, b.value as value2 from
(
@@ -1429,7 +1473,9 @@ select count(*) from (
join tbl2 b
on subq2.key = b.key) a
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The mapjoin 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 /*+mapjoin(subq2)*/ subq2.key as key, subq2.value as value1, b.value as value2 from
(
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_15.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_15.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_15.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_15.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,14 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, value ASC) INTO 16 BUCKETS
+PREHOOK: query: -- This test verifies that the sort merge join optimizer works when the tables are sorted on columns which is a superset
+-- of join columns
+
+-- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, value ASC) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, value ASC) INTO 16 BUCKETS
+POSTHOOK: query: -- This test verifies that the sort merge join optimizer works when the tables are sorted on columns which is a superset
+-- of join columns
+
+-- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, value ASC) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, value ASC) INTO 16 BUCKETS
@@ -26,10 +34,12 @@ POSTHOOK: Lineage: test_table1.key EXPRE
POSTHOOK: Lineage: test_table1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- it should be converted to a sort-merge join, since the first sort column (#join columns = 1) contains the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- it should be converted to a sort-merge join, since the first sort column (#join columns = 1) contains the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -215,9 +225,11 @@ POSTHOOK: Lineage: test_table1.key EXPRE
POSTHOOK: Lineage: test_table1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: CREATE TABLE test_table1 (key INT, key2 INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, key2 ASC, value ASC) INTO 16 BUCKETS
+PREHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, key2 INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, key2 ASC, value ASC) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, key2 INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, key2 ASC, value ASC) INTO 16 BUCKETS
+POSTHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, key2 INT, value STRING) CLUSTERED BY (key) SORTED BY (key ASC, key2 ASC, value ASC) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -257,10 +269,12 @@ POSTHOOK: Lineage: test_table2.key EXPRE
POSTHOOK: Lineage: test_table2.key2 EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- it should be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) contain the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key and a.key2 = b.key2 ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- it should be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) contain the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key and a.key2 = b.key2 ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -442,10 +456,14 @@ POSTHOOK: Lineage: test_table2.value SIM
0 0 val_0 0 0 val_0
0 0 val_0 0 0 val_0
2 2 val_2 2 2 val_2
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- it should be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) contain the join columns
+-- even if the order is not the same
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key2 = b.key2 and a.key = b.key ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- it should be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) contain the join columns
+-- even if the order is not the same
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key2 = b.key2 and a.key = b.key ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -627,10 +645,14 @@ POSTHOOK: Lineage: test_table2.value SIM
0 0 val_0 0 0 val_0
0 0 val_0 0 0 val_0
2 2 val_2 2 2 val_2
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- it should not be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) do not contain all
+-- the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key and a.value = b.value ORDER BY a.key LIMIT 10
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- it should not be converted to a sort-merge join, since the first 2 sort columns (#join columns = 2) do not contain all
+-- the join columns
+EXPLAIN EXTENDED
SELECT /*+mapjoin(b)*/ * FROM test_table1 a JOIN test_table2 b ON a.key = b.key and a.value = b.value ORDER BY a.key LIMIT 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_16.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_16.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_16.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_16.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,8 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
+PREHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
+POSTHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
@@ -26,10 +28,12 @@ POSTHOOK: Lineage: test_table1.key EXPRE
POSTHOOK: Lineage: test_table1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Mapjoin followed by a aggregation should be performed in a single MR job
+EXPLAIN
SELECT /*+mapjoin(b)*/ count(*) FROM test_table1 a JOIN test_table2 b ON a.key = b.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Mapjoin followed by a aggregation should be performed in a single MR job
+EXPLAIN
SELECT /*+mapjoin(b)*/ count(*) FROM test_table1 a JOIN test_table2 b ON a.key = b.key
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_17.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_17.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_17.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_17.q.out Wed Apr 10 00:06:55 2013
@@ -1,6 +1,8 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
+PREHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
+POSTHOOK: query: -- Create bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
@@ -190,7 +192,8 @@ POSTHOOK: Lineage: test_table7.key EXPRE
POSTHOOK: Lineage: test_table7.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table8.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table8.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Mapjoin followed by a aggregation should be performed in a single MR job upto 7 tables
+EXPLAIN
SELECT /*+ mapjoin(b, c, d, e, f, g) */ count(*)
FROM test_table1 a JOIN test_table2 b ON a.key = b.key
JOIN test_table3 c ON a.key = c.key
@@ -199,7 +202,8 @@ JOIN test_table5 e ON a.key = e.key
JOIN test_table6 f ON a.key = f.key
JOIN test_table7 g ON a.key = g.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Mapjoin followed by a aggregation should be performed in a single MR job upto 7 tables
+EXPLAIN
SELECT /*+ mapjoin(b, c, d, e, f, g) */ count(*)
FROM test_table1 a JOIN test_table2 b ON a.key = b.key
JOIN test_table3 c ON a.key = c.key
@@ -350,7 +354,9 @@ POSTHOOK: Lineage: test_table7.value SIM
POSTHOOK: Lineage: test_table8.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table8.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
4378
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- It should be automatically converted to a sort-merge join followed by a groupby in
+-- a single MR job
+EXPLAIN
SELECT count(*)
FROM test_table1 a LEFT OUTER JOIN test_table2 b ON a.key = b.key
LEFT OUTER JOIN test_table3 c ON a.key = c.key
@@ -359,7 +365,9 @@ LEFT OUTER JOIN test_table5 e ON a.key =
LEFT OUTER JOIN test_table6 f ON a.key = f.key
LEFT OUTER JOIN test_table7 g ON a.key = g.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- It should be automatically converted to a sort-merge join followed by a groupby in
+-- a single MR job
+EXPLAIN
SELECT count(*)
FROM test_table1 a LEFT OUTER JOIN test_table2 b ON a.key = b.key
LEFT OUTER JOIN test_table3 c ON a.key = c.key
@@ -679,7 +687,8 @@ POSTHOOK: Lineage: test_table7.value SIM
POSTHOOK: Lineage: test_table8.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table8.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
13126
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- outer join with max 16 aliases
+EXPLAIN
SELECT a.*
FROM test_table1 a
LEFT OUTER JOIN test_table2 b ON a.key = b.key
@@ -702,7 +711,8 @@ LEFT OUTER JOIN test_table6 r ON a.key =
LEFT OUTER JOIN test_table7 s ON a.key = s.key
LEFT OUTER JOIN test_table8 t ON a.key = t.key
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- outer join with max 16 aliases
+EXPLAIN
SELECT a.*
FROM test_table1 a
LEFT OUTER JOIN test_table2 b ON a.key = b.key
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_18.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_18.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_18.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_18.q.out Wed Apr 10 00:06:55 2013
@@ -1,7 +1,9 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+PREHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+POSTHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
@@ -24,11 +26,15 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@test_table1@ds=1
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -247,11 +253,15 @@ POSTHOOK: Lineage: test_table1 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(test_table1)a.FieldSchema(name:value, type:string, comment:null), ]
253
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation, one of the buckets should be empty
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1' and a.key = 238
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation, one of the buckets should be empty
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1' and a.key = 238
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_19.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_19.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_19.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_19.q.out Wed Apr 10 00:06:55 2013
@@ -1,7 +1,9 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+PREHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+POSTHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
@@ -24,11 +26,15 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@test_table1@ds=1
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_20.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_20.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_20.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_20.q.out Wed Apr 10 00:06:55 2013
@@ -1,7 +1,9 @@
-PREHOOK: query: CREATE TABLE test_table1 (key int, value STRING) PARTITIONED BY (ds STRING)
+PREHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key int, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key int, value STRING) PARTITIONED BY (ds STRING)
+POSTHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key int, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
@@ -24,11 +26,15 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@test_table1@ds=1
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- with different datatypes. This should be a map-reduce operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- with different datatypes. This should be a map-reduce operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -178,11 +184,15 @@ POSTHOOK: Lineage: test_table1 PARTITION
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value1 SIMPLE [(test_table1)a.FieldSchema(name:value, type:string, comment:null), ]
POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value2 SIMPLE [(test_table1)a.FieldSchema(name:value, type:string, comment:null), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation, although the bucketing positions dont match
+EXPLAIN
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1')
SELECT a.value, a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation, although the bucketing positions dont match
+EXPLAIN
INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1')
SELECT a.value, a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -355,11 +365,15 @@ POSTHOOK: Lineage: test_table3 PARTITION
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value1 SIMPLE [(test_table1)a.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value2 SIMPLE [(test_table1)a.FieldSchema(name:value, type:string, comment:null), ]
253
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- However, since an expression is being selected, it should involve a reducer
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2')
SELECT a.key+a.key, a.value, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- However, since an expression is being selected, it should involve a reducer
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2')
SELECT a.key+a.key, a.value, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_21.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_21.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_21.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_21.q.out Wed Apr 10 00:06:55 2013
@@ -1,7 +1,9 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+PREHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
+POSTHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
@@ -24,11 +26,15 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@test_table1@ds=1
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -100,11 +106,15 @@ POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table2
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort orders does not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort orders does not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -192,11 +202,15 @@ POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table2
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -286,11 +300,15 @@ POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table2
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -378,11 +396,15 @@ POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table2
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the number of buckets do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since the number of buckets do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
@@ -470,11 +492,15 @@ POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table2
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-reduce operation since sort columns do not match
+EXPLAIN
INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1')
SELECT a.key, a.value FROM test_table1 a WHERE a.ds = '1'
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_22.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_22.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_22.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_22.q.out Wed Apr 10 00:06:55 2013
@@ -1,7 +1,9 @@
-PREHOOK: query: CREATE TABLE test_table1 (key INT, value STRING)
+PREHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
PREHOOK: type: CREATETABLE
-POSTHOOK: query: CREATE TABLE test_table1 (key INT, value STRING)
+POSTHOOK: query: -- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: default@test_table1
@@ -24,10 +26,14 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@test_table1
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: test_table1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN INSERT OVERWRITE TABLE test_table2
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN INSERT OVERWRITE TABLE test_table2
SELECT * FROM test_table1
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN INSERT OVERWRITE TABLE test_table2
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN INSERT OVERWRITE TABLE test_table2
SELECT * FROM test_table1
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
@@ -205,10 +211,14 @@ POSTHOOK: Lineage: test_table1.value SIM
POSTHOOK: Lineage: test_table1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: test_table2.key SIMPLE [(test_table1)test_table1.FieldSchema(name:key, type:int, comment:null), ]
POSTHOOK: Lineage: test_table2.value SIMPLE [(test_table1)test_table1.FieldSchema(name:value, type:string, comment:null), ]
-PREHOOK: query: EXPLAIN INSERT OVERWRITE TABLE test_table2
+PREHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN INSERT OVERWRITE TABLE test_table2
SELECT * FROM test_table1
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN INSERT OVERWRITE TABLE test_table2
+POSTHOOK: query: -- Insert data into the bucketed table by selecting from another bucketed table
+-- This should be a map-only operation
+EXPLAIN INSERT OVERWRITE TABLE test_table2
SELECT * FROM test_table1
POSTHOOK: type: QUERY
POSTHOOK: Lineage: test_table1.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_1.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_1.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_1.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_1.q.out Wed Apr 10 00:06:55 2013
@@ -38,10 +38,16 @@ POSTHOOK: Lineage: table_desc1.key SIMPL
POSTHOOK: Lineage: table_desc1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: explain
+PREHOOK: query: -- The columns of the tables above are sorted in same descending order.
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b on a.key=b.key where a.key < 10
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The columns of the tables above are sorted in same descending order.
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b on a.key=b.key where a.key < 10
POSTHOOK: type: QUERY
POSTHOOK: Lineage: table_desc1.key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_2.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_2.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_2.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_2.q.out Wed Apr 10 00:06:55 2013
@@ -42,11 +42,19 @@ POSTHOOK: Lineage: table_desc1.key SIMPL
POSTHOOK: Lineage: table_desc1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: explain
+PREHOOK: query: -- The columns of the tables above are sorted in same order.
+-- descending followed by descending
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The columns of the tables above are sorted in same order.
+-- descending followed by descending
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_3.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_3.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_3.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_3.q.out Wed Apr 10 00:06:55 2013
@@ -42,11 +42,19 @@ POSTHOOK: Lineage: table_desc1.key SIMPL
POSTHOOK: Lineage: table_desc1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: explain
+PREHOOK: query: -- The columns of the tables above are sorted in same orders.
+-- descending followed by ascending
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The columns of the tables above are sorted in same orders.
+-- descending followed by ascending
+-- So, sort merge join should be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_4.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_4.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_4.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_4.q.out Wed Apr 10 00:06:55 2013
@@ -42,11 +42,17 @@ POSTHOOK: Lineage: table_desc1.key SIMPL
POSTHOOK: Lineage: table_desc1.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: table_desc2.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: explain
+PREHOOK: query: -- The columns of the tables above are sorted in different orders.
+-- So, sort merge join should not be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
PREHOOK: type: QUERY
-POSTHOOK: query: explain
+POSTHOOK: query: -- The columns of the tables above are sorted in different orders.
+-- So, sort merge join should not be performed
+
+explain
select /*+ mapjoin(b) */ count(*) from table_desc1 a join table_desc2 b
on a.key=b.key and a.value=b.value where a.key < 10
POSTHOOK: type: QUERY
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_5.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_5.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_5.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_5.q.out Wed Apr 10 00:06:55 2013
@@ -48,12 +48,16 @@ POSTHOOK: Lineage: srcbucket_mapjoin_par
POSTHOOK: Lineage: srcbucket_mapjoin_part_1 PARTITION(part=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- The partition sorting metadata matches but the table metadata does not, sorted merge join should still be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part = '1' AND b.part = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- The partition sorting metadata matches but the table metadata does not, sorted merge join should still be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part = '1' AND b.part = '1'
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_6.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_6.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_6.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_6.q.out Wed Apr 10 00:06:55 2013
@@ -48,12 +48,16 @@ POSTHOOK: Lineage: srcbucket_mapjoin_par
POSTHOOK: Lineage: srcbucket_mapjoin_part_1 PARTITION(part=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=1).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- The table sorting metadata matches but the partition metadata does not, sorted merge join should not be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part = '1' AND b.part = '1'
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- The table sorting metadata matches but the partition metadata does not, sorted merge join should not be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part = '1' AND b.part = '1'
Modified: hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_7.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_7.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_7.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/sort_merge_join_desc_7.q.out Wed Apr 10 00:06:55 2013
@@ -108,12 +108,16 @@ POSTHOOK: Lineage: srcbucket_mapjoin_par
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=1).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=2).key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: srcbucket_mapjoin_part_2 PARTITION(part=2).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: EXPLAIN EXTENDED
+PREHOOK: query: -- The table sorting metadata matches but the partition metadata does not, sorted merge join should not be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part IS NOT NULL AND b.part IS NOT NULL
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN EXTENDED
+POSTHOOK: query: -- The table sorting metadata matches but the partition metadata does not, sorted merge join should not be used
+
+EXPLAIN EXTENDED
SELECT /*+ MAPJOIN(b) */ count(*)
FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b
ON a.key = b.key AND a.part IS NOT NULL AND b.part IS NOT NULL
Modified: hive/trunk/ql/src/test/results/clientpositive/stats1.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/stats1.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/stats1.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/stats1.q.out Wed Apr 10 00:06:55 2013
@@ -221,10 +221,16 @@ Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
-PREHOOK: query: load data local inpath '../data/files/srcbucket20.txt' INTO TABLE tmptable
+PREHOOK: query: -- Load a file into a existing table
+-- Some stats (numFiles, totalSize) should be updated correctly
+-- Some other stats (numRows, rawDataSize) should be cleared
+load data local inpath '../data/files/srcbucket20.txt' INTO TABLE tmptable
PREHOOK: type: LOAD
PREHOOK: Output: default@tmptable
-POSTHOOK: query: load data local inpath '../data/files/srcbucket20.txt' INTO TABLE tmptable
+POSTHOOK: query: -- Load a file into a existing table
+-- Some stats (numFiles, totalSize) should be updated correctly
+-- Some other stats (numRows, rawDataSize) should be cleared
+load data local inpath '../data/files/srcbucket20.txt' INTO TABLE tmptable
POSTHOOK: type: LOAD
POSTHOOK: Output: default@tmptable
POSTHOOK: Lineage: tmptable.key EXPRESSION [(src1)s2.FieldSchema(name:key, type:string, comment:default), ]
Modified: hive/trunk/ql/src/test/results/clientpositive/stats18.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/stats18.q.out?rev=1466300&r1=1466299&r2=1466300&view=diff
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/stats18.q.out (original)
+++ hive/trunk/ql/src/test/results/clientpositive/stats18.q.out Wed Apr 10 00:06:55 2013
@@ -13,9 +13,15 @@ POSTHOOK: Input: default@src
POSTHOOK: Output: default@stats_part@ds=2010-04-08/hr=13
POSTHOOK: Lineage: stats_part PARTITION(ds=2010-04-08,hr=13).key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: stats_part PARTITION(ds=2010-04-08,hr=13).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]
-PREHOOK: query: desc formatted stats_part partition (ds='2010-04-08', hr='13')
+PREHOOK: query: -- Load a file into a existing partition
+-- Some stats (numFiles, totalSize) should be updated correctly
+-- Some other stats (numRows, rawDataSize) should be cleared
+desc formatted stats_part partition (ds='2010-04-08', hr='13')
PREHOOK: type: DESCTABLE
-POSTHOOK: query: desc formatted stats_part partition (ds='2010-04-08', hr='13')
+POSTHOOK: query: -- Load a file into a existing partition
+-- Some stats (numFiles, totalSize) should be updated correctly
+-- Some other stats (numRows, rawDataSize) should be cleared
+desc formatted stats_part partition (ds='2010-04-08', hr='13')
POSTHOOK: type: DESCTABLE
POSTHOOK: Lineage: stats_part PARTITION(ds=2010-04-08,hr=13).key SIMPLE [(src)src.FieldSchema(name:key, type:string, comment:default), ]
POSTHOOK: Lineage: stats_part PARTITION(ds=2010-04-08,hr=13).value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ]