http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/autoColumnStats_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/autoColumnStats_2.q b/ql/src/test/queries/clientpositive/autoColumnStats_2.q index 57266af..51f252b 100644 --- a/ql/src/test/queries/clientpositive/autoColumnStats_2.q +++ b/ql/src/test/queries/clientpositive/autoColumnStats_2.q @@ -24,37 +24,37 @@ explain extended select * from src_multi1; describe formatted src_multi1; -drop table a; -drop table b; -create table a like src; -create table b like src; +drop table a_n3; +drop table b_n3; +create table a_n3 like src; +create table b_n3 like src; from src -insert into table a select * -insert into table b select *; +insert into table a_n3 select * +insert into table b_n3 select *; -describe formatted a key; -describe formatted b key; +describe formatted a_n3 key; +describe formatted b_n3 key; from src -insert overwrite table a select * -insert into table b select *; +insert overwrite table a_n3 select * +insert into table b_n3 select *; -describe formatted a; -describe formatted b; +describe formatted a_n3; +describe formatted b_n3; -describe formatted b key; -describe formatted b value; +describe formatted b_n3 key; +describe formatted b_n3 value; -insert into table b select NULL, NULL from src limit 10; +insert into table b_n3 select NULL, NULL from src limit 10; -describe formatted b key; -describe formatted b value; +describe formatted b_n3 key; +describe formatted b_n3 value; -insert into table b(value) select key+100000 from src limit 10; +insert into table b_n3(value) select key+100000 from src limit 10; -describe formatted b key; -describe formatted b value; +describe formatted b_n3 key; +describe formatted b_n3 value; drop table src_multi2; @@ -72,11 +72,11 @@ create table if not exists nzhang_part14 (key string) insert into table nzhang_part14 partition(value) select key, value from ( - select * from (select 'k1' as key, cast(null as string) as value from src limit 2)a + select * from (select 'k1' as key, cast(null as string) as value from src limit 2)a_n3 union all - select * from (select 'k2' as key, '' as value from src limit 2)b + select * from (select 'k2' as key, '' as value from src limit 2)b_n3 union all - select * from (select 'k3' as key, ' ' as value from src limit 2)c + select * from (select 'k3' as key, ' ' as value from src limit 2)c_n1 ) T; explain select key from nzhang_part14; @@ -133,31 +133,31 @@ select * from src1; describe formatted src_stat_part PARTITION(partitionId=2); -drop table srcbucket_mapjoin; -CREATE TABLE srcbucket_mapjoin(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; -drop table tab_part; -CREATE TABLE tab_part (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; -drop table srcbucket_mapjoin_part; -CREATE TABLE srcbucket_mapjoin_part (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; +drop table srcbucket_mapjoin_n2; +CREATE TABLE srcbucket_mapjoin_n2(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; +drop table tab_part_n1; +CREATE TABLE tab_part_n1 (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; +drop table srcbucket_mapjoin_part_n2; +CREATE TABLE srcbucket_mapjoin_part_n2 (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; -load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_n2 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin_n2 partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); -insert into table tab_part partition (ds='2008-04-08') -select key,value from srcbucket_mapjoin_part; +insert into table tab_part_n1 partition (ds='2008-04-08') +select key,value from srcbucket_mapjoin_part_n2; -describe formatted tab_part partition (ds='2008-04-08'); +describe formatted tab_part_n1 partition (ds='2008-04-08'); -CREATE TABLE tab(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; -insert into table tab partition (ds='2008-04-08') -select key,value from srcbucket_mapjoin; +CREATE TABLE tab_n0(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; +insert into table tab_n0 partition (ds='2008-04-08') +select key,value from srcbucket_mapjoin_n2; -describe formatted tab partition (ds='2008-04-08'); +describe formatted tab_n0 partition (ds='2008-04-08'); drop table nzhang_part14; @@ -168,11 +168,11 @@ describe formatted nzhang_part14; insert into table nzhang_part14 partition(ds, hr) select key, value, ds, hr from ( - select * from (select 'k1' as key, cast(null as string) as value, '1' as ds, '2' as hr from src limit 2)a + select * from (select 'k1' as key, cast(null as string) as value, '1' as ds, '2' as hr from src limit 2)a_n3 union all - select * from (select 'k2' as key, '' as value, '1' as ds, '3' as hr from src limit 2)b + select * from (select 'k2' as key, '' as value, '1' as ds, '3' as hr from src limit 2)b_n3 union all - select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c + select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c_n1 ) T; desc formatted nzhang_part14 partition(ds='1', hr='3'); @@ -193,27 +193,27 @@ SELECT key, value, hr FROM srcpart WHERE ds is not null and hr>10; desc formatted nzhang_part14 PARTITION(ds='2010-03-03', hr='12'); -drop table a; -create table a (key string, value string) +drop table a_n3; +create table a_n3 (key string, value string) partitioned by (ds string, hr string); -drop table b; -create table b (key string, value string) +drop table b_n3; +create table b_n3 (key string, value string) partitioned by (ds string, hr string); -drop table c; -create table c (key string, value string) +drop table c_n1; +create table c_n1 (key string, value string) partitioned by (ds string, hr string); FROM srcpart -INSERT into TABLE a PARTITION (ds='2010-03-11', hr) SELECT key, value, hr WHERE ds is not null and hr>10 -INSERT into TABLE b PARTITION (ds='2010-04-11', hr) SELECT key, value, hr WHERE ds is not null and hr>11 -INSERT into TABLE c PARTITION (ds='2010-05-11', hr) SELECT key, value, hr WHERE hr>0; - -explain select key from a; -explain select value from b; -explain select key from b; -explain select value from c; -explain select key from c; +INSERT into TABLE a_n3 PARTITION (ds='2010-03-11', hr) SELECT key, value, hr WHERE ds is not null and hr>10 +INSERT into TABLE b_n3 PARTITION (ds='2010-04-11', hr) SELECT key, value, hr WHERE ds is not null and hr>11 +INSERT into TABLE c_n1 PARTITION (ds='2010-05-11', hr) SELECT key, value, hr WHERE hr>0; + +explain select key from a_n3; +explain select value from b_n3; +explain select key from b_n3; +explain select value from c_n1; +explain select key from c_n1;
http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/autoColumnStats_3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/autoColumnStats_3.q b/ql/src/test/queries/clientpositive/autoColumnStats_3.q index 63f3a94..7a41911 100644 --- a/ql/src/test/queries/clientpositive/autoColumnStats_3.q +++ b/ql/src/test/queries/clientpositive/autoColumnStats_3.q @@ -10,31 +10,31 @@ set hive.auto.convert.join.noconditionaltask.size=10000; set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ; set hive.optimize.bucketingsorting=false; -drop table src_multi1; +drop table src_multi1_n6; -create table src_multi1 like src; +create table src_multi1_n6 like src; -analyze table src_multi1 compute statistics for columns key; +analyze table src_multi1_n6 compute statistics for columns key; -describe formatted src_multi1; +describe formatted src_multi1_n6; set hive.stats.column.autogather=true; -insert into table src_multi1 select * from src; +insert into table src_multi1_n6 select * from src; -describe formatted src_multi1; +describe formatted src_multi1_n6; set hive.stats.column.autogather=false; -drop table nzhang_part14; +drop table nzhang_part14_n2; -create table if not exists nzhang_part14 (key string, value string) +create table if not exists nzhang_part14_n2 (key string, value string) partitioned by (ds string, hr string); -describe formatted nzhang_part14; +describe formatted nzhang_part14_n2; -insert into table nzhang_part14 partition(ds, hr) +insert into table nzhang_part14_n2 partition(ds, hr) select key, value, ds, hr from ( select * from (select 'k1' as key, cast(null as string) as value, '1' as ds, '2' as hr from src limit 2)a union all @@ -43,17 +43,17 @@ select key, value, ds, hr from ( select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c ) T; -desc formatted nzhang_part14 partition(ds='1', hr='3'); +desc formatted nzhang_part14_n2 partition(ds='1', hr='3'); -analyze table nzhang_part14 partition(ds='1', hr='3') compute statistics for columns value; +analyze table nzhang_part14_n2 partition(ds='1', hr='3') compute statistics for columns value; -desc formatted nzhang_part14 partition(ds='1', hr='3'); +desc formatted nzhang_part14_n2 partition(ds='1', hr='3'); -desc formatted nzhang_part14 partition(ds='2', hr='1'); +desc formatted nzhang_part14_n2 partition(ds='2', hr='1'); set hive.stats.column.autogather=true; -insert into table nzhang_part14 partition(ds, hr) +insert into table nzhang_part14_n2 partition(ds, hr) select key, value, ds, hr from ( select * from (select 'k1' as key, cast(null as string) as value, '1' as ds, '2' as hr from src limit 2)a union all @@ -62,7 +62,7 @@ select key, value, ds, hr from ( select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c ) T; -desc formatted nzhang_part14 partition(ds='1', hr='3'); +desc formatted nzhang_part14_n2 partition(ds='1', hr='3'); -desc formatted nzhang_part14 partition(ds='2', hr='1'); +desc formatted nzhang_part14_n2 partition(ds='2', hr='1'); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/autoColumnStats_5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/autoColumnStats_5.q b/ql/src/test/queries/clientpositive/autoColumnStats_5.q index 3da7b38..0cbbea2 100644 --- a/ql/src/test/queries/clientpositive/autoColumnStats_5.q +++ b/ql/src/test/queries/clientpositive/autoColumnStats_5.q @@ -14,35 +14,35 @@ set hive.exec.dynamic.partition.mode=nonstrict; -- -- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... STATIC INSERT --- -CREATE TABLE partitioned1(a INT, b STRING) PARTITIONED BY(part INT) STORED AS TEXTFILE; +CREATE TABLE partitioned1_n1(a INT, b STRING) PARTITIONED BY(part INT) STORED AS TEXTFILE; -explain insert into table partitioned1 partition(part=1) values(1, 'original'),(2, 'original'), (3, 'original'),(4, 'original'); +explain insert into table partitioned1_n1 partition(part=1) values(1, 'original'),(2, 'original'), (3, 'original'),(4, 'original'); -insert into table partitioned1 partition(part=1) values(1, 'original'),(2, 'original'), (3, 'original'),(4, 'original'); +insert into table partitioned1_n1 partition(part=1) values(1, 'original'),(2, 'original'), (3, 'original'),(4, 'original'); -desc formatted partitioned1 partition(part=1); +desc formatted partitioned1_n1 partition(part=1); -desc formatted partitioned1 partition(part=1) a; +desc formatted partitioned1_n1 partition(part=1) a; -- Table-Non-Cascade ADD COLUMNS ... -alter table partitioned1 add columns(c int, d string); +alter table partitioned1_n1 add columns(c int, d string); -desc formatted partitioned1 partition(part=1); +desc formatted partitioned1_n1 partition(part=1); -explain insert into table partitioned1 partition(part=2) values(1, 'new', 10, 'ten'),(2, 'new', 20, 'twenty'), (3, 'new', 30, 'thirty'),(4, 'new', 40, 'forty'); +explain insert into table partitioned1_n1 partition(part=2) values(1, 'new', 10, 'ten'),(2, 'new', 20, 'twenty'), (3, 'new', 30, 'thirty'),(4, 'new', 40, 'forty'); -insert into table partitioned1 partition(part=2) values(1, 'new', 10, 'ten'),(2, 'new', 20, 'twenty'), (3, 'new', 30, 'thirty'),(4, 'new', 40, 'forty'); +insert into table partitioned1_n1 partition(part=2) values(1, 'new', 10, 'ten'),(2, 'new', 20, 'twenty'), (3, 'new', 30, 'thirty'),(4, 'new', 40, 'forty'); -desc formatted partitioned1 partition(part=2); +desc formatted partitioned1_n1 partition(part=2); -desc formatted partitioned1 partition(part=2) c; +desc formatted partitioned1_n1 partition(part=2) c; -explain insert into table partitioned1 partition(part=1) values(5, 'new', 100, 'hundred'),(6, 'new', 200, 'two hundred'); +explain insert into table partitioned1_n1 partition(part=1) values(5, 'new', 100, 'hundred'),(6, 'new', 200, 'two hundred'); -insert into table partitioned1 partition(part=1) values(5, 'new', 100, 'hundred'),(6, 'new', 200, 'two hundred'); +insert into table partitioned1_n1 partition(part=1) values(5, 'new', 100, 'hundred'),(6, 'new', 200, 'two hundred'); -desc formatted partitioned1 partition(part=1); +desc formatted partitioned1_n1 partition(part=1); -desc formatted partitioned1 partition(part=1) a; +desc formatted partitioned1_n1 partition(part=1) a; -desc formatted partitioned1 partition(part=1) c; +desc formatted partitioned1_n1 partition(part=1) c; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/autoColumnStats_7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/autoColumnStats_7.q b/ql/src/test/queries/clientpositive/autoColumnStats_7.q index 8622b23..7868283 100644 --- a/ql/src/test/queries/clientpositive/autoColumnStats_7.q +++ b/ql/src/test/queries/clientpositive/autoColumnStats_7.q @@ -5,16 +5,16 @@ set hive.map.aggr=false; set hive.groupby.skewindata=true; -- Taken from groupby2.q -CREATE TABLE dest_g2(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest_g2_n5(key STRING, c1 INT, c2 STRING) STORED AS TEXTFILE; CREATE TEMPORARY TABLE src_temp AS SELECT * FROM src; explain FROM src_temp -INSERT OVERWRITE TABLE dest_g2 SELECT substr(src_temp.key,1,1), count(DISTINCT substr(src_temp.value,5)), concat(substr(src_temp.key,1,1),sum(substr(src_temp.value,5))) GROUP BY substr(src_temp.key,1,1); +INSERT OVERWRITE TABLE dest_g2_n5 SELECT substr(src_temp.key,1,1), count(DISTINCT substr(src_temp.value,5)), concat(substr(src_temp.key,1,1),sum(substr(src_temp.value,5))) GROUP BY substr(src_temp.key,1,1); FROM src_temp -INSERT OVERWRITE TABLE dest_g2 SELECT substr(src_temp.key,1,1), count(DISTINCT substr(src_temp.value,5)), concat(substr(src_temp.key,1,1),sum(substr(src_temp.value,5))) GROUP BY substr(src_temp.key,1,1); +INSERT OVERWRITE TABLE dest_g2_n5 SELECT substr(src_temp.key,1,1), count(DISTINCT substr(src_temp.value,5)), concat(substr(src_temp.key,1,1),sum(substr(src_temp.value,5))) GROUP BY substr(src_temp.key,1,1); -SELECT dest_g2.* FROM dest_g2; +SELECT dest_g2_n5.* FROM dest_g2_n5; -DROP TABLE dest_g2; +DROP TABLE dest_g2_n5; DROP TABLE src_temp; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/autoColumnStats_9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/autoColumnStats_9.q b/ql/src/test/queries/clientpositive/autoColumnStats_9.q index 944da2c..2b9eb82 100644 --- a/ql/src/test/queries/clientpositive/autoColumnStats_9.q +++ b/ql/src/test/queries/clientpositive/autoColumnStats_9.q @@ -8,17 +8,17 @@ set hive.skewjoin.key = 2; -- SORT_QUERY_RESULTS -CREATE TABLE dest_j1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest_j1_n23(key INT, value STRING) STORED AS TEXTFILE; EXPLAIN FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value; +INSERT OVERWRITE TABLE dest_j1_n23 SELECT src1.key, src2.value; FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value; +INSERT OVERWRITE TABLE dest_j1_n23 SELECT src1.key, src2.value; -desc formatted dest_j1; +desc formatted dest_j1_n23; -desc formatted dest_j1 key; +desc formatted dest_j1_n23 key; -desc formatted dest_j1 value; +desc formatted dest_j1_n23 value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join1.q b/ql/src/test/queries/clientpositive/auto_join1.q index 58d31fb..126ac36 100644 --- a/ql/src/test/queries/clientpositive/auto_join1.q +++ b/ql/src/test/queries/clientpositive/auto_join1.q @@ -6,13 +6,13 @@ set hive.auto.convert.join =true; -- SORT_QUERY_RESULTS -CREATE TABLE dest_j1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest_j1_n3(key INT, value STRING) STORED AS TEXTFILE; explain FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value; +INSERT OVERWRITE TABLE dest_j1_n3 SELECT src1.key, src2.value; FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value; +INSERT OVERWRITE TABLE dest_j1_n3 SELECT src1.key, src2.value; -SELECT sum(hash(dest_j1.key,dest_j1.value)) FROM dest_j1; \ No newline at end of file +SELECT sum(hash(dest_j1_n3.key,dest_j1_n3.value)) FROM dest_j1_n3; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join14.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join14.q b/ql/src/test/queries/clientpositive/auto_join14.q index 2ce606a..11829cc 100644 --- a/ql/src/test/queries/clientpositive/auto_join14.q +++ b/ql/src/test/queries/clientpositive/auto_join14.q @@ -6,7 +6,7 @@ set hive.auto.convert.join = true; -- EXCLUDE_HADOOP_MAJOR_VERSIONS( 0.20S) -CREATE TABLE dest1(c1 INT, c2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n83(c1 INT, c2 STRING) STORED AS TEXTFILE; set mapreduce.framework.name=yarn; set mapreduce.jobtracker.address=localhost:58; @@ -14,9 +14,9 @@ set hive.exec.mode.local.auto=true; explain FROM src JOIN srcpart ON src.key = srcpart.key AND srcpart.ds = '2008-04-08' and src.key > 100 -INSERT OVERWRITE TABLE dest1 SELECT src.key, srcpart.value; +INSERT OVERWRITE TABLE dest1_n83 SELECT src.key, srcpart.value; FROM src JOIN srcpart ON src.key = srcpart.key AND srcpart.ds = '2008-04-08' and src.key > 100 -INSERT OVERWRITE TABLE dest1 SELECT src.key, srcpart.value; +INSERT OVERWRITE TABLE dest1_n83 SELECT src.key, srcpart.value; -SELECT sum(hash(dest1.c1,dest1.c2)) FROM dest1; +SELECT sum(hash(dest1_n83.c1,dest1_n83.c2)) FROM dest1_n83; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join14_hadoop20.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join14_hadoop20.q b/ql/src/test/queries/clientpositive/auto_join14_hadoop20.q index 6825da2..0c6b900 100644 --- a/ql/src/test/queries/clientpositive/auto_join14_hadoop20.q +++ b/ql/src/test/queries/clientpositive/auto_join14_hadoop20.q @@ -5,16 +5,16 @@ set hive.auto.convert.join = true; -- INCLUDE_HADOOP_MAJOR_VERSIONS(0.20S) -CREATE TABLE dest1(c1 INT, c2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n74(c1 INT, c2 STRING) STORED AS TEXTFILE; set mapred.job.tracker=localhost:58; set hive.exec.mode.local.auto=true; explain FROM src JOIN srcpart ON src.key = srcpart.key AND srcpart.ds = '2008-04-08' and src.key > 100 -INSERT OVERWRITE TABLE dest1 SELECT src.key, srcpart.value; +INSERT OVERWRITE TABLE dest1_n74 SELECT src.key, srcpart.value; FROM src JOIN srcpart ON src.key = srcpart.key AND srcpart.ds = '2008-04-08' and src.key > 100 -INSERT OVERWRITE TABLE dest1 SELECT src.key, srcpart.value; +INSERT OVERWRITE TABLE dest1_n74 SELECT src.key, srcpart.value; -SELECT sum(hash(dest1.c1,dest1.c2)) FROM dest1; +SELECT sum(hash(dest1_n74.c1,dest1_n74.c2)) FROM dest1_n74; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join17.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join17.q b/ql/src/test/queries/clientpositive/auto_join17.q index 6b63513..04019b9 100644 --- a/ql/src/test/queries/clientpositive/auto_join17.q +++ b/ql/src/test/queries/clientpositive/auto_join17.q @@ -4,14 +4,14 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(key1 INT, value1 STRING, key2 INT, value2 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n41(key1 INT, value1 STRING, key2 INT, value2 STRING) STORED AS TEXTFILE; explain FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.*, src2.*; +INSERT OVERWRITE TABLE dest1_n41 SELECT src1.*, src2.*; FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.*, src2.*; +INSERT OVERWRITE TABLE dest1_n41 SELECT src1.*, src2.*; -SELECT sum(hash(dest1.key1,dest1.value1,dest1.key2,dest1.value2)) FROM dest1; \ No newline at end of file +SELECT sum(hash(dest1_n41.key1,dest1_n41.value1,dest1_n41.key2,dest1_n41.value2)) FROM dest1_n41; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join19.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join19.q b/ql/src/test/queries/clientpositive/auto_join19.q index f231e07..b75d47c 100644 --- a/ql/src/test/queries/clientpositive/auto_join19.q +++ b/ql/src/test/queries/clientpositive/auto_join19.q @@ -4,17 +4,17 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n18(key INT, value STRING) STORED AS TEXTFILE; explain FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n18 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n18 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); -SELECT sum(hash(dest1.key,dest1.value)) FROM dest1; +SELECT sum(hash(dest1_n18.key,dest1_n18.value)) FROM dest1_n18; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join19_inclause.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join19_inclause.q b/ql/src/test/queries/clientpositive/auto_join19_inclause.q index f8d16b5..1a53897 100644 --- a/ql/src/test/queries/clientpositive/auto_join19_inclause.q +++ b/ql/src/test/queries/clientpositive/auto_join19_inclause.q @@ -5,17 +5,17 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; set hive.optimize.point.lookup.min=2; -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n11(key INT, value STRING) STORED AS TEXTFILE; explain FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n11 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n11 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); -SELECT sum(hash(dest1.key,dest1.value)) FROM dest1; +SELECT sum(hash(dest1_n11.key,dest1_n11.value)) FROM dest1_n11; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join24.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join24.q b/ql/src/test/queries/clientpositive/auto_join24.q index 32d5cf4..9e4f7bc 100644 --- a/ql/src/test/queries/clientpositive/auto_join24.q +++ b/ql/src/test/queries/clientpositive/auto_join24.q @@ -2,14 +2,14 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -create table tst1(key STRING, cnt INT); +create table tst1_n2(key STRING, cnt INT); -INSERT OVERWRITE TABLE tst1 +INSERT OVERWRITE TABLE tst1_n2 SELECT a.key, count(1) FROM src a group by a.key; explain -SELECT sum(a.cnt) FROM tst1 a JOIN tst1 b ON a.key = b.key; +SELECT sum(a.cnt) FROM tst1_n2 a JOIN tst1_n2 b ON a.key = b.key; -SELECT sum(a.cnt) FROM tst1 a JOIN tst1 b ON a.key = b.key; +SELECT sum(a.cnt) FROM tst1_n2 a JOIN tst1_n2 b ON a.key = b.key; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join25.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join25.q b/ql/src/test/queries/clientpositive/auto_join25.q index 8f30095..2882341 100644 --- a/ql/src/test/queries/clientpositive/auto_join25.q +++ b/ql/src/test/queries/clientpositive/auto_join25.q @@ -11,25 +11,25 @@ set hive.mapjoin.check.memory.rows = 2; set hive.auto.convert.join.noconditionaltask = false; -- This test tests the scenario when the mapper dies. So, create a conditional task for the mapjoin -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n62(key INT, value STRING) STORED AS TEXTFILE; FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value +INSERT OVERWRITE TABLE dest1_n62 SELECT src1.key, src2.value where (src1.ds = '2008-04-08' or src1.ds = '2008-04-09' )and (src1.hr = '12' or src1.hr = '11'); -SELECT sum(hash(dest1.key,dest1.value)) FROM dest1; +SELECT sum(hash(dest1_n62.key,dest1_n62.value)) FROM dest1_n62; -CREATE TABLE dest_j2(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest_j2_n0(key INT, value STRING) STORED AS TEXTFILE; FROM src src1 JOIN src src2 ON (src1.key = src2.key) JOIN src src3 ON (src1.key + src2.key = src3.key) -INSERT OVERWRITE TABLE dest_j2 SELECT src1.key, src3.value; +INSERT OVERWRITE TABLE dest_j2_n0 SELECT src1.key, src3.value; -SELECT sum(hash(dest_j2.key,dest_j2.value)) FROM dest_j2; +SELECT sum(hash(dest_j2_n0.key,dest_j2_n0.value)) FROM dest_j2_n0; -CREATE TABLE dest_j1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest_j1_n5(key INT, value STRING) STORED AS TEXTFILE; FROM src src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest_j1 SELECT src1.key, src2.value; +INSERT OVERWRITE TABLE dest_j1_n5 SELECT src1.key, src2.value; -SELECT sum(hash(dest_j1.key,dest_j1.value)) FROM dest_j1; +SELECT sum(hash(dest_j1_n5.key,dest_j1_n5.value)) FROM dest_j1_n5; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join3.q b/ql/src/test/queries/clientpositive/auto_join3.q index bd29c9a..e76861a 100644 --- a/ql/src/test/queries/clientpositive/auto_join3.q +++ b/ql/src/test/queries/clientpositive/auto_join3.q @@ -3,13 +3,13 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n140(key INT, value STRING) STORED AS TEXTFILE; explain FROM src src1 JOIN src src2 ON (src1.key = src2.key) JOIN src src3 ON (src1.key = src3.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src3.value; +INSERT OVERWRITE TABLE dest1_n140 SELECT src1.key, src3.value; FROM src src1 JOIN src src2 ON (src1.key = src2.key) JOIN src src3 ON (src1.key = src3.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src3.value; +INSERT OVERWRITE TABLE dest1_n140 SELECT src1.key, src3.value; -SELECT sum(hash(dest1.key,dest1.value)) FROM dest1; \ No newline at end of file +SELECT sum(hash(dest1_n140.key,dest1_n140.value)) FROM dest1_n140; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join4.q b/ql/src/test/queries/clientpositive/auto_join4.q index 0a8848b..40a82fe 100644 --- a/ql/src/test/queries/clientpositive/auto_join4.q +++ b/ql/src/test/queries/clientpositive/auto_join4.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n115(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; explain FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n115 SELECT c.c1, c.c2, c.c3, c.c4; FROM ( FROM @@ -32,6 +32,6 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n115 SELECT c.c1, c.c2, c.c3, c.c4; -SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1; +SELECT sum(hash(dest1_n115.c1,dest1_n115.c2,dest1_n115.c3,dest1_n115.c4)) FROM dest1_n115; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join5.q b/ql/src/test/queries/clientpositive/auto_join5.q index 5967319..68d459d 100644 --- a/ql/src/test/queries/clientpositive/auto_join5.q +++ b/ql/src/test/queries/clientpositive/auto_join5.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n64(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; explain FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n64 SELECT c.c1, c.c2, c.c3, c.c4; FROM ( FROM @@ -32,6 +32,6 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n64 SELECT c.c1, c.c2, c.c3, c.c4; -SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1; +SELECT sum(hash(dest1_n64.c1,dest1_n64.c2,dest1_n64.c3,dest1_n64.c4)) FROM dest1_n64; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join6.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join6.q b/ql/src/test/queries/clientpositive/auto_join6.q index b356f55..d0a7c5f 100644 --- a/ql/src/test/queries/clientpositive/auto_join6.q +++ b/ql/src/test/queries/clientpositive/auto_join6.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n9(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; explain FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n9 SELECT c.c1, c.c2, c.c3, c.c4; FROM ( FROM @@ -32,7 +32,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4; +INSERT OVERWRITE TABLE dest1_n9 SELECT c.c1, c.c2, c.c3, c.c4; -SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1; +SELECT sum(hash(dest1_n9.c1,dest1_n9.c2,dest1_n9.c3,dest1_n9.c4)) FROM dest1_n9; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join7.q b/ql/src/test/queries/clientpositive/auto_join7.q index bd13519..af03f37 100644 --- a/ql/src/test/queries/clientpositive/auto_join7.q +++ b/ql/src/test/queries/clientpositive/auto_join7.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n147(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE; explain @@ -24,7 +24,7 @@ FROM ( ON (a.c1 = c.c5) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; +INSERT OVERWRITE TABLE dest1_n147 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; FROM ( FROM @@ -43,7 +43,7 @@ FROM ( ON (a.c1 = c.c5) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; +INSERT OVERWRITE TABLE dest1_n147 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6; -SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4,dest1.c5,dest1.c6)) FROM dest1; +SELECT sum(hash(dest1_n147.c1,dest1_n147.c2,dest1_n147.c3,dest1_n147.c4,dest1_n147.c5,dest1_n147.c6)) FROM dest1_n147; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join8.q b/ql/src/test/queries/clientpositive/auto_join8.q index d9d3f91..b5d9f4c 100644 --- a/ql/src/test/queries/clientpositive/auto_join8.q +++ b/ql/src/test/queries/clientpositive/auto_join8.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n3(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE; explain FROM ( @@ -18,7 +18,7 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; +INSERT OVERWRITE TABLE dest1_n3 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; FROM ( FROM @@ -32,6 +32,6 @@ FROM ( ON (a.c1 = b.c3) SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4 ) c -INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; +INSERT OVERWRITE TABLE dest1_n3 SELECT c.c1, c.c2, c.c3, c.c4 where c.c3 IS NULL AND c.c1 IS NOT NULL; -SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1; +SELECT sum(hash(dest1_n3.c1,dest1_n3.c2,dest1_n3.c3,dest1_n3.c4)) FROM dest1_n3; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join9.q b/ql/src/test/queries/clientpositive/auto_join9.q index 72676d4..53e9504 100644 --- a/ql/src/test/queries/clientpositive/auto_join9.q +++ b/ql/src/test/queries/clientpositive/auto_join9.q @@ -4,15 +4,15 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; +CREATE TABLE dest1_n142(key INT, value STRING) STORED AS TEXTFILE; explain FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; +INSERT OVERWRITE TABLE dest1_n142 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; FROM srcpart src1 JOIN src src2 ON (src1.key = src2.key) -INSERT OVERWRITE TABLE dest1 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; +INSERT OVERWRITE TABLE dest1_n142 SELECT src1.key, src2.value where src1.ds = '2008-04-08' and src1.hr = '12'; -SELECT sum(hash(dest1.key,dest1.value)) FROM dest1; +SELECT sum(hash(dest1_n142.key,dest1_n142.value)) FROM dest1_n142; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join_filters.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join_filters.q b/ql/src/test/queries/clientpositive/auto_join_filters.q index a44ffb3..ea028f6 100644 --- a/ql/src/test/queries/clientpositive/auto_join_filters.q +++ b/ql/src/test/queries/clientpositive/auto_join_filters.q @@ -1,81 +1,81 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE myinput1(key int, value int); -LOAD DATA LOCAL INPATH '../../data/files/in3.txt' INTO TABLE myinput1; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; - - -CREATE TABLE smb_input1(key int, value int) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; -CREATE TABLE smb_input2(key int, value int) CLUSTERED BY (value) SORTED BY (value) INTO 2 BUCKETS; -LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input1; -LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input1; -LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input2; -LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input2; +CREATE TABLE myinput1_n5(key int, value int); +LOAD DATA LOCAL INPATH '../../data/files/in3.txt' INTO TABLE myinput1_n5; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n5 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n5 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b RIGHT OUTER JOIN myinput1_n5 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n5 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n5 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b RIGHT OUTER JOIN myinput1_n5 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; + + +CREATE TABLE smb_input1_n0(key int, value int) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; +CREATE TABLE smb_input2_n0(key int, value int) CLUSTERED BY (value) SORTED BY (value) INTO 2 BUCKETS; +LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input1_n0; +LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input1_n0; +LOAD DATA LOCAL INPATH '../../data/files/in/000000_0' into table smb_input2_n0; +LOAD DATA LOCAL INPATH '../../data/files/in/000001_0' into table smb_input2_n0; SET hive.optimize.bucketmapjoin = true; SET hive.optimize.bucketmapjoin.sortedmerge = true; SET hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; - -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a JOIN myinput1_n5 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON a.key = b.key and a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON a.key=b.key and a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.key = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.key = b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.value = b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a FULL OUTER JOIN myinput1_n5 b ON a.value = b.value and a.key=b.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value; + +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n5 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n5 c ON (b.value=c.value AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b RIGHT OUTER JOIN myinput1_n5 c ON a.value = b.value and b.value = c.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) RIGHT OUTER JOIN myinput1_n5 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n5 a RIGHT OUTER JOIN myinput1_n5 b ON (a.value=b.value AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value) LEFT OUTER JOIN myinput1_n5 c ON (b.key=c.key AND c.key > 40 AND c.value > 50 AND c.key = c.value AND b.key > 40 AND b.value > 50 AND b.key = b.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n5 a LEFT OUTER JOIN myinput1_n5 b RIGHT OUTER JOIN myinput1_n5 c ON a.value = b.value and b.key = c.key AND a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value AND c.key > 40 AND c.value > 50 AND c.key = c.value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join_nulls.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join_nulls.q b/ql/src/test/queries/clientpositive/auto_join_nulls.q index 279fd32..4a2b57b 100644 --- a/ql/src/test/queries/clientpositive/auto_join_nulls.q +++ b/ql/src/test/queries/clientpositive/auto_join_nulls.q @@ -1,30 +1,30 @@ set hive.mapred.mode=nonstrict; set hive.auto.convert.join = true; -CREATE TABLE myinput1(key int, value int); -LOAD DATA LOCAL INPATH '../../data/files/in1.txt' INTO TABLE myinput1; +CREATE TABLE myinput1_n2(key int, value int); +LOAD DATA LOCAL INPATH '../../data/files/in1.txt' INTO TABLE myinput1_n2; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.key = b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a JOIN myinput1 b ON a.value = b.value and a.key=b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.value = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b ON a.key = b.key and a.value=b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key = b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.value = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a RIGHT OUTER JOIN myinput1 b ON a.key=b.key and a.value = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.key = b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value; -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a FULL OUTER JOIN myinput1 b ON a.value = b.value and a.key=b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a JOIN myinput1_n2 b; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a JOIN myinput1_n2 b ON a.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a JOIN myinput1_n2 b ON a.key = b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a JOIN myinput1_n2 b ON a.value = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a JOIN myinput1_n2 b ON a.value = b.value and a.key=b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b ON a.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b ON a.value = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b ON a.key = b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b ON a.key = b.key and a.value=b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b ON a.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b ON a.key = b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b ON a.value = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b ON a.key=b.key and a.value = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a FULL OUTER JOIN myinput1_n2 b ON a.key = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a FULL OUTER JOIN myinput1_n2 b ON a.key = b.key; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a FULL OUTER JOIN myinput1_n2 b ON a.value = b.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a FULL OUTER JOIN myinput1_n2 b ON a.value = b.value and a.key=b.key; -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a LEFT OUTER JOIN myinput1 b ON (a.value=b.value) RIGHT OUTER JOIN myinput1 c ON (b.value=c.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1 a RIGHT OUTER JOIN myinput1 b ON (a.value=b.value) LEFT OUTER JOIN myinput1 c ON (b.value=c.value); -SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1 a LEFT OUTER JOIN myinput1 b RIGHT OUTER JOIN myinput1 c ON a.value = b.value and b.value = c.value; +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b ON (a.value=b.value) RIGHT OUTER JOIN myinput1_n2 c ON (b.value=c.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) from myinput1_n2 a RIGHT OUTER JOIN myinput1_n2 b ON (a.value=b.value) LEFT OUTER JOIN myinput1_n2 c ON (b.value=c.value); +SELECT sum(hash(a.key,a.value,b.key,b.value)) FROM myinput1_n2 a LEFT OUTER JOIN myinput1_n2 b RIGHT OUTER JOIN myinput1_n2 c ON a.value = b.value and b.value = c.value; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_join_stats.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_join_stats.q b/ql/src/test/queries/clientpositive/auto_join_stats.q index 7720fdc..8b377bf 100644 --- a/ql/src/test/queries/clientpositive/auto_join_stats.q +++ b/ql/src/test/queries/clientpositive/auto_join_stats.q @@ -3,19 +3,19 @@ set hive.auto.convert.join = true; set hive.auto.convert.join.noconditionaltask.size=2660; --- Setting HTS(src2) < threshold < HTS(src2) + HTS(smalltable). +-- Setting HTS(src2) < threshold < HTS(src2) + HTS(smalltable_n0). -- This query plan should thus not try to combine the mapjoin into a single work. -create table smalltable(key string, value string) stored as textfile; -load data local inpath '../../data/files/T1.txt' into table smalltable; -analyze table smalltable compute statistics; +create table smalltable_n0(key string, value string) stored as textfile; +load data local inpath '../../data/files/T1.txt' into table smalltable_n0; +analyze table smalltable_n0 compute statistics; -explain select src1.key, src2.key, smalltable.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable ON (src1.key + src2.key = smalltable.key); -select src1.key, src2.key, smalltable.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable ON (src1.key + src2.key = smalltable.key); +explain select src1.key, src2.key, smalltable_n0.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable_n0 ON (src1.key + src2.key = smalltable_n0.key); +select src1.key, src2.key, smalltable_n0.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable_n0 ON (src1.key + src2.key = smalltable_n0.key); -create table smalltable2(key string, value string) stored as textfile; -load data local inpath '../../data/files/T1.txt' into table smalltable2; -analyze table smalltable compute statistics; +create table smalltable2_n0(key string, value string) stored as textfile; +load data local inpath '../../data/files/T1.txt' into table smalltable2_n0; +analyze table smalltable_n0 compute statistics; -explain select src1.key, src2.key, smalltable.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable ON (src1.key + src2.key = smalltable.key) JOIN smalltable2 ON (src1.key + src2.key = smalltable2.key); -select src1.key, src2.key, smalltable.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable ON (src1.key + src2.key = smalltable.key) JOIN smalltable2 ON (src1.key + src2.key = smalltable2.key); \ No newline at end of file +explain select src1.key, src2.key, smalltable_n0.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable_n0 ON (src1.key + src2.key = smalltable_n0.key) JOIN smalltable2_n0 ON (src1.key + src2.key = smalltable2_n0.key); +select src1.key, src2.key, smalltable_n0.key from src src1 JOIN src src2 ON (src1.key = src2.key) JOIN smalltable_n0 ON (src1.key + src2.key = smalltable_n0.key) JOIN smalltable2_n0 ON (src1.key + src2.key = smalltable2_n0.key); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_smb_mapjoin_14.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_smb_mapjoin_14.q b/ql/src/test/queries/clientpositive/auto_smb_mapjoin_14.q index f6eb5c5..6472a3b 100644 --- a/ql/src/test/queries/clientpositive/auto_smb_mapjoin_14.q +++ b/ql/src/test/queries/clientpositive/auto_smb_mapjoin_14.q @@ -257,8 +257,8 @@ select count(*) from ( 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); +CREATE TABLE dest1_n2(key int, value string); +CREATE TABLE dest2_n0(key int, val1 string, val2 string); -- The join is followed by a multi-table insert. It should be converted to -- a sort-merge join @@ -266,20 +266,20 @@ 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; +insert overwrite table dest1_n2 select key, val1 +insert overwrite table dest2_n0 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; +insert overwrite table dest1_n2 select key, val1 +insert overwrite table dest2_n0 select key, val1, val2; -select * from dest1; -select * from dest2; +select * from dest1_n2; +select * from dest2_n0; -DROP TABLE dest2; -CREATE TABLE dest2(key int, cnt int); +DROP TABLE dest2_n0; +CREATE TABLE dest2_n0(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 @@ -287,14 +287,14 @@ 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; +insert overwrite table dest1_n2 select key, val1 +insert overwrite table dest2_n0 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; +insert overwrite table dest1_n2 select key, val1 +insert overwrite table dest2_n0 select key, count(*) group by key; -select * from dest1; -select * from dest2; +select * from dest1_n2; +select * from dest2_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_sortmerge_join_1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_sortmerge_join_1.q b/ql/src/test/queries/clientpositive/auto_sortmerge_join_1.q index 6949f8c..1fbe8f7 100644 --- a/ql/src/test/queries/clientpositive/auto_sortmerge_join_1.q +++ b/ql/src/test/queries/clientpositive/auto_sortmerge_join_1.q @@ -4,21 +4,21 @@ set hive.strict.checks.bucketing=false; set hive.mapred.mode=nonstrict; -- small 1 part, 2 bucket & big 2 part, 4 bucket -CREATE TABLE bucket_small (key string, value string) partitioned by (ds string) +CREATE TABLE bucket_small_n1 (key string, value string) partitioned by (ds string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; -load data local inpath '../../data/files/auto_sortmerge_join/small/000000_0' INTO TABLE bucket_small partition(ds='2008-04-08'); -load data local inpath '../../data/files/auto_sortmerge_join/small/000001_0' INTO TABLE bucket_small partition(ds='2008-04-08'); +load data local inpath '../../data/files/auto_sortmerge_join/small/000000_0' INTO TABLE bucket_small_n1 partition(ds='2008-04-08'); +load data local inpath '../../data/files/auto_sortmerge_join/small/000001_0' INTO TABLE bucket_small_n1 partition(ds='2008-04-08'); -CREATE TABLE bucket_big (key string, value string) partitioned by (ds string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; -load data local inpath '../../data/files/auto_sortmerge_join/big/000000_0' INTO TABLE bucket_big partition(ds='2008-04-08'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000001_0' INTO TABLE bucket_big partition(ds='2008-04-08'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000002_0' INTO TABLE bucket_big partition(ds='2008-04-08'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000003_0' INTO TABLE bucket_big partition(ds='2008-04-08'); +CREATE TABLE bucket_big_n1 (key string, value string) partitioned by (ds string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; +load data local inpath '../../data/files/auto_sortmerge_join/big/000000_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-08'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000001_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-08'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000002_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-08'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000003_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-08'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000000_0' INTO TABLE bucket_big partition(ds='2008-04-09'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000001_0' INTO TABLE bucket_big partition(ds='2008-04-09'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000002_0' INTO TABLE bucket_big partition(ds='2008-04-09'); -load data local inpath '../../data/files/auto_sortmerge_join/big/000003_0' INTO TABLE bucket_big partition(ds='2008-04-09'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000000_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-09'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000001_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-09'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000002_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-09'); +load data local inpath '../../data/files/auto_sortmerge_join/big/000003_0' INTO TABLE bucket_big_n1 partition(ds='2008-04-09'); set hive.auto.convert.join=true; set hive.auto.convert.sortmerge.join=true; @@ -31,12 +31,12 @@ set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hado set hive.auto.convert.join.noconditionaltask.size=10; -- Since size is being used to find the big table, the order of the tables in the join does not matter -explain extended select count(*) FROM bucket_small a JOIN bucket_big b ON a.key = b.key; -select count(*) FROM bucket_small a JOIN bucket_big b ON a.key = b.key; +explain extended select count(*) FROM bucket_small_n1 a JOIN bucket_big_n1 b ON a.key = b.key; +select count(*) FROM bucket_small_n1 a JOIN bucket_big_n1 b ON a.key = b.key; -explain extended select count(*) FROM bucket_big a JOIN bucket_small b ON a.key = b.key; -select count(*) FROM bucket_big a JOIN bucket_small b ON a.key = b.key; +explain extended select count(*) FROM bucket_big_n1 a JOIN bucket_small_n1 b ON a.key = b.key; +select count(*) FROM bucket_big_n1 a JOIN bucket_small_n1 b ON a.key = b.key; set hive.auto.convert.sortmerge.join.to.mapjoin=true; -explain extended select count(*) FROM bucket_big a JOIN bucket_small b ON a.key = b.key; -select count(*) FROM bucket_big a JOIN bucket_small b ON a.key = b.key; +explain extended select count(*) FROM bucket_big_n1 a JOIN bucket_small_n1 b ON a.key = b.key; +select count(*) FROM bucket_big_n1 a JOIN bucket_small_n1 b ON a.key = b.key; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/auto_sortmerge_join_10.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/auto_sortmerge_join_10.q b/ql/src/test/queries/clientpositive/auto_sortmerge_join_10.q index 1cbda1f..1b15a74 100644 --- a/ql/src/test/queries/clientpositive/auto_sortmerge_join_10.q +++ b/ql/src/test/queries/clientpositive/auto_sortmerge_join_10.q @@ -5,13 +5,13 @@ set hive.explain.user=false; set hive.exec.reducers.max = 1; -CREATE TABLE tbl1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; -CREATE TABLE tbl2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; +CREATE TABLE tbl1_n5(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; +CREATE TABLE tbl2_n4(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; -insert overwrite table tbl1 +insert overwrite table tbl1_n5 select * from src where key < 10; -insert overwrite table tbl2 +insert overwrite table tbl2_n4 select * from src where key < 10; set hive.auto.convert.join=true; @@ -29,35 +29,35 @@ explain select count(*) from ( select * from - (select a.key as key, a.value as value from tbl1 a where key < 6 + (select a.key as key, a.value as value from tbl1_n5 a where key < 6 union all - select a.key as key, a.value as value from tbl1 a where key < 6 + select a.key as key, a.value as value from tbl1_n5 a where key < 6 ) usubq1 ) subq1 join - (select a.key as key, a.value as value from tbl2 a where key < 6) subq2 + (select a.key as key, a.value as value from tbl2_n4 a where key < 6) subq2 on subq1.key = subq2.key; select count(*) from ( select * from - (select a.key as key, a.value as value from tbl1 a where key < 6 + (select a.key as key, a.value as value from tbl1_n5 a where key < 6 union all - select a.key as key, a.value as value from tbl1 a where key < 6 + select a.key as key, a.value as value from tbl1_n5 a where key < 6 ) usubq1 ) subq1 join - (select a.key as key, a.value as value from tbl2 a where key < 6) subq2 + (select a.key as key, a.value as value from tbl2_n4 a where key < 6) subq2 on subq1.key = subq2.key; -- One of the subqueries contains a groupby, so it should not be converted to a sort-merge join. explain select count(*) from - (select a.key as key, count(*) as value from tbl1 a where key < 6 group by a.key) subq1 + (select a.key as key, count(*) as value from tbl1_n5 a where key < 6 group by a.key) subq1 join - (select a.key as key, a.value as value from tbl2 a where key < 6) subq2 + (select a.key as key, a.value as value from tbl2_n4 a where key < 6) subq2 on subq1.key = subq2.key; select count(*) from - (select a.key as key, count(*) as value from tbl1 a where key < 6 group by a.key) subq1 + (select a.key as key, count(*) as value from tbl1_n5 a where key < 6 group by a.key) subq1 join - (select a.key as key, a.value as value from tbl2 a where key < 6) subq2 + (select a.key as key, a.value as value from tbl2_n4 a where key < 6) subq2 on subq1.key = subq2.key;