--! qt:dataset:src
set hive.mapred.mode=nonstrict;

select * from src where (key, value) in (('238','val_238'));

drop table emps_n7;

create table emps_n7 (empno int, deptno int, empname string);

insert into table emps_n7 values (1,2,"11"),(1,2,"11"),(3,4,"33"),(1,3,"11"),(2,5,"22"),(2,5,"22");

select * from emps_n7;

select * from emps_n7 where (int(empno+deptno/2), int(deptno/3)) in ((3,2));

select * from emps_n7 where (int(empno+deptno/2), int(deptno/3)) not in ((3,2));

select * from emps_n7 where (empno,deptno) in ((3,2));

select * from emps_n7 where (empno,deptno) not in ((3,2));

select * from emps_n7 where (empno,deptno) in ((1,3));

select * from emps_n7 where (empno,deptno) not in ((1,3));

explain
select * from emps_n7 where (empno+1,deptno) in ((3,2));

explain 
select * from emps_n7 where (empno+1,deptno) not in ((3,2));

explain select * from emps_n7 where ((empno*2)|1,deptno) in ((empno+2,2));

select * from emps_n7 where ((empno*2)|1,deptno) in ((empno+2,2));

select (empno*2)|1,substr(empname,1,1) from emps_n7;

select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+2,'2'));

select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) not in ((empno+2,'2'));

select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+3,'2'));

select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) not in ((empno+3,'2'));


select sum(empno), empname from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+3,'2'))
group by empname;

select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+3,'2'))
union
select * from emps_n7 where (empno,deptno) in ((3,2));

drop view v_n11;

create view v_n11 as 
select * from(
select * from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+3,'2'))
union
select * from emps_n7 where (empno,deptno) in ((3,2)))subq order by empno desc;

select * from v_n11;

select subq.e1 from 
(select (empno*2)|1 as e1, substr(empname,1,1) as n1 from emps_n7)subq
join
(select empno as e2 from emps_n7 where ((empno*2)|1,substr(empname,1,1)) in ((empno+3,'2')))subq2
on e1=e2+1;