set hive.strict.checks.bucketing=false;

set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
set hive.mapjoin.hybridgrace.hashtable=false;
set hive.join.emit.interval=2;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000;
set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ;

-- SORT_QUERY_RESULTS

CREATE TABLE srcbucket_mapjoin_n7(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
CREATE TABLE tab_part_n5 (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
CREATE TABLE srcbucket_mapjoin_part_n8 (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_n7 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin_n7 partition(ds='2008-04-08');

load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part_n8 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part_n8 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part_n8 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part_n8 partition(ds='2008-04-08');


set hive.optimize.bucketingsorting=false;
insert overwrite table tab_part_n5 partition (ds='2008-04-08')
select key,value from srcbucket_mapjoin_part_n8;

CREATE TABLE tab_n4(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
insert overwrite table tab_n4 partition (ds='2008-04-08')
select key,value from srcbucket_mapjoin_n7;

set hive.auto.convert.sortmerge.join = true;

set hive.auto.convert.join.noconditionaltask.size=500;
CREATE TABLE empty_n0(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;

explain
select count(*) from tab_n4 s1 join empty_n0 s3 on s1.key=s3.key;

select count(*) from tab_n4 s1 join empty_n0 s3 on s1.key=s3.key;

explain
select * from tab_n4 s1 left outer join empty_n0 s3 on s1.key=s3.key;

select * from tab_n4 s1 left outer join empty_n0 s3 on s1.key=s3.key;

explain
select count(*) from tab_n4 s1 left outer join tab_n4 s2 on s1.key=s2.key join empty_n0 s3 on s1.key = s3.key;

select count(*) from tab_n4 s1 left outer join tab_n4 s2 on s1.key=s2.key join empty_n0 s3 on s1.key = s3.key;

explain
select count(*) from tab_n4 s1 left outer join empty_n0 s2 on s1.key=s2.key join tab_n4 s3 on s1.key = s3.key;

select count(*) from tab_n4 s1 left outer join empty_n0 s2 on s1.key=s2.key join tab_n4 s3 on s1.key = s3.key;

explain
select count(*) from empty_n0 s1 join empty_n0 s3 on s1.key=s3.key;

select count(*) from empty_n0 s1 join empty_n0 s3 on s1.key=s3.key;

set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hadoop.hive.ql.optimizer.LeftmostBigTableSelectorForAutoSMJ;

explain
select count(*) from empty_n0 s1 join tab_n4 s3 on s1.key=s3.key;

select count(*) from empty_n0 s1 join tab_n4 s3 on s1.key=s3.key;