====
---- QUERY
-- Materialize two instances of the same array
select id, item from complextypestbl t,
(select item from t.int_array where item = 2
 union all
 select item from t.int_array where item != 2
 union all
 select item from t.int_array where item is null) v
---- RESULTS
1,1
1,2
1,3
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
8,-1
---- TYPES
bigint,int
====
---- QUERY
-- Materialize two instances of the same flattened array, with structs in the schema
select id, e, f from complextypestbl t,
(select e, f from t.nested_struct.c.d.item where e = 10
 union all
 select e, f from t.nested_struct.c.d.item where e != 10
 union all
 select e, f from t.nested_struct.c.d.item where e is null) v
---- RESULTS
1,10,'aaa'
1,-10,'bbb'
1,11,'c'
2,NULL,'NULL'
2,10,'aaa'
2,NULL,'NULL'
2,-10,'bbb'
2,NULL,'NULL'
2,11,'c'
2,NULL,'NULL'
7,NULL,'NULL'
8,-1,'nonnullable'
---- TYPES
bigint,int,string
====
---- QUERY
select id, int_array.item, a2.item, a3.item,
nested_struct.a, b.item, d2.e, d2.f, d3.e, d3.f
from complextypestbl t,
t.int_array,
t.int_array_array a1, a1.item a2,
t.int_array_array.item a3,
t.nested_struct.b,
t.nested_struct.c.d, d.item d2,
t.nested_struct.c.d.item d3
where a2.item = 1 and a3.item = 2 and d2.e = 10 and d3.e = -10
---- RESULTS
1,1,1,2,1,1,10,'aaa',-10,'bbb'
1,2,1,2,1,1,10,'aaa',-10,'bbb'
1,3,1,2,1,1,10,'aaa',-10,'bbb'
2,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb'
2,1,1,2,NULL,NULL,10,'aaa',-10,'bbb'
2,2,1,2,NULL,NULL,10,'aaa',-10,'bbb'
2,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb'
2,3,1,2,NULL,NULL,10,'aaa',-10,'bbb'
2,NULL,1,2,NULL,NULL,10,'aaa',-10,'bbb'
---- TYPES
bigint,int,int,int,int,int,int,string,int,string
====