ql/src/test/queries/clientpositive/bucket_map_join_2.q

set hive.strict.checks.bucketing=false;

drop table table1;
drop table table2;

;


create table table1(key string, value string) clustered by (key, value)
sorted by (key desc, value desc) into 1 BUCKETS stored as textfile;
create table table2(key string, value string) clustered by (value, key)
sorted by (value desc, key desc) into 1 BUCKETS stored as textfile;

load data local inpath '../../data/files/SortCol1Col2/000000_0' overwrite into table table1;
load data local inpath '../../data/files/SortCol2Col1/000000_0' overwrite into table table2;

set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.cbo.enable=false;
-- The tables are bucketed in same columns in different order,
-- but sorted in different column orders
-- Neither bucketed map-join, nor sort-merge join should be performed

explain extended
select /*+ mapjoin(b) */ count(*) from table1 a join table2 b on a.key=b.key and a.value=b.value;

select /*+ mapjoin(b) */ count(*) from table1 a join table2 b on a.key=b.key and a.value=b.value;