-- MASK_STATS

set hive.explain.user=false;
create table bug_201_input_a (
       userid                                       int
) clustered by (userid) sorted by (userid) into 64 BUCKETS ;

create table bug_201_input_b (
       userid                  int 
) clustered by (userid) sorted by (userid) into 64 BUCKETS ;

insert overwrite table bug_201_input_a 
select distinct(userid) as userid from (
  select arr as userid from (
     select explode(array (
            12804352        ,251326720,50029057        ,251155969,60217858        ,251995906,78744835        ,250561795,13637380        ,207184132,58189573        ,251988997,
            62314246        ,251565574,63912199        ,250889479,57424648        ,208269832,39819529        ,251811337,39396106        ,250124554,25833739        ,251722507,
            48908812        ,252057100,39894541        ,251633677,20268046        ,251462926,46375183        ,251292175,64902160        ,251879440,40980241        ,204206353,
            50411026        ,251790610,54030355        ,251367187,29097748        ,205968148,12755989        ,250773013,45685270        ,208406038,47788567        ,208235287,
            57724696        ,207559192,13083673        ,206630425,41717530        ,250929946,50642971        ,250759195,43144732        ,250841116,51059485        ,250670365,
            39771166        ,250752286,53244703        ,250834207,43725088        ,207961888,46586401        ,207285793,77241634        ,251837986,19967011        ,251919907,
            37230628        ,251496484,47419429        ,251578405,50786086        ,250144294,48593959        ,251742247,44885800        ,251318824,52295209        ,251400745,
            66274090        ,251988010,28960555        ,208357675,51024940        ,206923564,32156461        ,251981101,47398702        ,252063022,48238639        ,251386927,
            77377840        ,251468848,64068145        ,207333169,16142386        ,251380018,15971635        ,251461939,60018484        ,250027828,43171381        ,250867765,
            47548726        ,250191670,37776439        ,208330039,59588152        ,251871544,75335737        ,251953465,57477946        ,251782714,63623995        ,250348603,
            57641788        ,250935868,42058045        ,250007101,59574334        ,251352382,42474559        ,250928959,52663360        ,252021568,60578113        ,251598145,
            60407362        ,251174722,55941187        ,208302403,65119300        ,251843908,61916485        ,251673157,65535814        ,207790150,62838343        ,208124743,
            26030152        ,251666248,45315145        ,204498505,59799370        ,251577418,72514891        ,250648651,64258636        ,208281676,53475661        ,251823181,
            30564430        ,207940174,32162383        ,250976335,62059600        ,251563600,64920913        ,251392849,50347858        ,250716754,40828243        ,251304019,
            44447572        ,204388948,14966869        ,251973205,59013718        ,250791766,12856663        ,251631703,40227160        ,250450264,20600665        ,252048217,
            48223834        ,251119450,76099675        ,207741787,49145692        ,250019932,50490973        ,207905629,58405726        ,250689118,47370079        ,250013023,
            44419936        ,250347616,36416353        ,250934881,62776162        ,251269474,48455779        ,251098723,43736932        ,251938660,12740197        ,251767909,
            54007654        ,252102502,49794151        ,251931751,63267688        ,252013672,28480873        ,252095593,63684202        ,251419498,76652395        ,252006763,
            64606060        ,251077996,53317741        ,251665261,21310318        ,250989166,41353327        ,208116847,52805488        ,208451440,43033201        ,251992945,
            48673906        ,251064178,15655795        ,207686515,44794996        ,251228020,14303605        ,251815285,48243574        ,251897206,65507191        ,207508855,
            63820408        ,250797688,45457273        ,251637625,62973562        ,251466874,42083707        ,252054139,63642748        ,251630716,40731517        ,251712637,
            29948542        ,252047230,40137343        ,250360447,43756672        ,251200384,13517953        ,251534977,77273218        ,251616898,54867331        ,250435459,
            46105732        ,250517380,48714373        ,208150405,79369606        ,250933894,57216391        ,250257799,20408200        ,250845064,61675657        ,206962057,
            15013258        ,250756234,54512011        ,250585483,70512268        ,251172748,78932365        ,251254669,12814222       , 251841934,49533583       , 251923855,
            44309392       , 208040848,73953937       , 250824337,56601490       , 251664274,35458963       , 208033939,50953876       , 251828116,74281621       , 204155029,
            64509334       , 250223254,44630167       , 252073879,52292248       , 207685528,63997081       , 251732377,46897306       , 250803610,60370843       , 251138203,
            65506204       , 251725468,48406429       , 205821085,15893662       , 250878622,38716063       , 251213215,35007904       , 251295136,66926497       , 251629729,
            56143522       , 251964322,73407139       , 250277539,71720356       , 251117476,80645797       , 205465765,43079590       , 250270630,75756199       , 208156327,
            58656424       , 251445160,57474985       , 251779753,45428650       , 251356330,62186923       , 250680235,54183340       , 250004140,55781293       , 207637165,
            15941038       , 251684014,14254255       , 251513263,42130096       , 251342512,34884529       , 208217521,70087858       , 251253682,50208691       , 250830259,
            54333364       , 250406836,17019829       , 251752117,75468982       , 251834038,17689015       , 208203703,22571704       , 250481848,52974265       , 250816441,
            52803514       , 251656378,61981627       , 251485627,13045180       , 250556860,71494333       , 250133437,81935806       , 251478718,50433727       , 252065983,
            26764480       , 250631872,64747201       , 251977153,64829122       , 206830786,47982019       , 251888323,64992964       , 207499972,52188613       , 251799493,
            39384262       , 251376070,41992903       , 250699975,41822152       , 250781896,39882697       , 250358473,56135626       , 251198410,35751115       , 251785675,
            75249868       , 251867596,55118029       , 207479245,70107598       , 251526094,50481103       , 207895759,55869136       , 251689936,45086161       , 251519185,
            46431442       , 251348434,53335507       , 251683027,39520468       , 251512276,53246677       , 250836181,42211030       , 251928790,56442583       , 251252695,
            75727576       , 251334616,15673561       , 250911193,42286042       , 250993114,63845083       , 251833051,37396444       , 251156956,77653213       , 251744221,
            16335838       , 251068126,61393375       , 250897375,53642464       , 250221280,58525153       , 206843617,56585698       , 207683554,30642403       , 250972387,
            63319012       , 250801636,76287205       , 207929317,44785126       , 251723494,50425831       , 251300071,45706984       , 251634664,13952233       , 251463913,
            77960170       , 251293162,27760363       , 251627755,69280492       , 206734060,13521901       , 208332013,35333614       , 250862830,56892655       , 251702767,
            49394416       , 251532016,79291633       , 250097905,50316274       , 206467570,45344755       , 251525107,17127412       , 250596340,53846773       , 206966005,
            53170678       , 251012854,46430455       , 252105463,59651320       , 251682040,54427129       , 251763961,54509050       , 252098554,65203195       , 251422459,
            40270588       , 250241020,13316605       , 208379389,45235198       , 251668222,60477439       , 251497471,
            101510977       ,114200836       ,156174985       ,60512971        ,181554703       ,
            148365841       ,52703827        ,182530846       ,149341984       ,116153122       ,
            162031843       ,141532840       ,154222699       ,109320121       ,155198842       
                       )) as arr )a )b;


insert overwrite table bug_201_input_b
select distinct(userid) as userid from (
  select arr as userid from (
     select explode(array (
            55632256        ,243051712    ,39037825        ,163984129    ,22443394        ,147389698    ,68322115        ,193268419,
            51727684        ,176673988    ,35133253        ,160079557    ,81011974        ,205958278    ,64417543        ,251836999,
            47823112        ,172769416    ,31228681        ,156174985    ,14634250        ,202053706    ,60512971        ,247932427,
            43918540        ,168864844    ,89797261        ,152270413    ,73202830        ,73202830    ,56608399        ,244027855,
            40013968        ,164960272    ,85892689        ,148365841    ,69298258        ,69298258    ,52703827        ,177650131,
            36109396        ,161055700    ,19514965        ,206934421    ,65393686        ,190339990    ,48799255        ,111272407,
            94677976        ,157151128    ,15610393        ,203029849    ,61489114        ,248908570    ,44894683        ,169840987,
            90773404        ,153246556    ,11705821        ,74178973    ,57584542        ,245003998    ,40990111        ,165936415,
            24395680        ,149341984    ,70274401        ,70274401    ,53679970        ,178626274    ,37085539        ,162031843,
            145437412       ,207910564    ,66369829        ,191316133    ,49775398        ,112248550    ,33180967        ,158127271,
            79059688        ,204005992    ,62465257        ,187411561    ,45870826        ,170817130    ,29276395        ,154222699,
            12681964        ,75155116     ,58560685        ,245980141    ,41966254        ,166912558    ,150318127       ,150318127,
            8777392         ,71250544     ,54656113        ,179602417    ,38061682        ,163007986    ,146413555       ,146413555,
            67345972        ,192292276    ,50751541        ,175697845    ,34157110        ,159103414    ,17562679        ,204982135,
            63441400        ,250860856    ,46846969        ,171793273    ,30252538        ,155198842    ,76131259        ,201077563,
            59536828        ,246956284    ,42942397        ,167888701    ,26347966        ,151294270    ,9753535         ,72226687,
            101510977       ,114200836       ,156174985       ,60512971        ,181554703       ,
            148365841       ,52703827        ,182530846       ,149341984       ,116153122       ,
            162031843       ,141532840       ,154222699       ,109320121       ,155198842       
        )) as arr )a ) b;


explain
select
t1.userid,
  fa.userid   as  fa_userid 
  from bug_201_input_b as t1
  join bug_201_input_a as fa on (t1.userid = fa.userid) ;

select
t1.userid,
  fa.userid   as  fa_userid 
  from bug_201_input_b as t1
  join bug_201_input_a as fa on (t1.userid = fa.userid) order by t1.userid, fa.userid;


set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=5;
set hive.auto.convert.sortmerge.join=true;
set hive.convert.join.bucket.mapjoin.tez = true;
set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ;

explain
select
t1.userid,
  fa.userid   as  fa_userid 
  from bug_201_input_b as t1
  join bug_201_input_a as fa on (t1.userid = fa.userid) ;

select
t1.userid,
  fa.userid   as  fa_userid 
  from bug_201_input_b as t1
  join bug_201_input_a as fa on (t1.userid = fa.userid) order by t1.userid, fa.userid;