set hive.cli.print.header=true;
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000;

drop table if exists TJOIN3;
drop table if exists TJOIN4;
create table if not exists TJOIN3 (name string, id int, flag string) STORED AS orc;
create table if not exists TJOIN4 (code_name string, id int) STORED AS orc;
create table if not exists TJOIN3STAGE (name string, id int, flag string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ;
create table if not exists TJOIN4STAGE (code_name string, id int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ;
LOAD DATA LOCAL INPATH '../../data/files/tjoin3.txt' OVERWRITE INTO TABLE TJOIN3STAGE;
LOAD DATA LOCAL INPATH '../../data/files/tjoin4.txt' OVERWRITE INTO TABLE TJOIN4STAGE;
INSERT INTO TABLE TJOIN3 SELECT * from TJOIN3STAGE;
INSERT INTO TABLE TJOIN4 SELECT * from TJOIN4STAGE;

set hive.vectorized.execution.enabled=false;
explain vectorization detail
select TJOIN3.id,TJOIN3.name from TJOIN3 left outer join ( select code_name, id from TJOIN4) s3 on (TJOIN3.name = s3.code_name and TJOIN3.flag='N');

select TJOIN3.id,TJOIN3.name from TJOIN3 left outer join ( select code_name, id from TJOIN4) s3 on (TJOIN3.name = s3.code_name and TJOIN3.flag='N');

explain vectorization detail
select TJOIN3.id,TJOIN3.name from TJOIN3 left outer join ( select code_name, id from TJOIN4) s3 on (TJOIN3.name = s3.code_name and TJOIN3.flag='N') limit 1;

select TJOIN3.id,TJOIN3.name from TJOIN3 left outer join ( select code_name, id from TJOIN4) s3 on (TJOIN3.name = s3.code_name and TJOIN3.flag='N') limit 1;