subquery/in-subquery/in-basic.sql

0001 create temporary view tab_a as select * from values (1, 1) as tab_a(a1, b1);
0002 create temporary view tab_b as select * from values (1, 1) as tab_b(a2, b2);
0003 create temporary view struct_tab as select struct(col1 as a, col2 as b) as record from
0004  values (1, 1), (1, 2), (2, 1), (2, 2);
0005
0006 select 1 from tab_a where (a1, b1) not in (select a2, b2 from tab_b);
0007 -- Invalid query, see SPARK-24341
0008 select 1 from tab_a where (a1, b1) not in (select (a2, b2) from tab_b);
0009
0010 -- Aliasing is needed as a workaround for SPARK-24443
0011 select count(*) from struct_tab where record in
0012   (select (a2 as a, b2 as b) from tab_b);
0013 select count(*) from struct_tab where record not in
0014   (select (a2 as a, b2 as b) from tab_b);