====
---- QUERY
-- Materialize scalar and array
select id, a.item from complextypestbl t, t.int_array a
---- 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 array (for now, may be optimized away someday)
select a.item from complextypestbl t, t.int_array a
---- RESULTS
1
2
3
NULL
1
2
NULL
3
NULL
-1
---- TYPES
int
====
---- QUERY
-- Materialize scalar and array
select id, cnt from complextypestbl t, (select count(item) cnt from t.int_array) v
---- RESULTS
1,3
2,3
3,0
4,0
5,0
6,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array
select cnt from complextypestbl t, (select count(item) cnt from t.int_array) v
---- RESULTS
3
3
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array with no slots
select id, cnt from complextypestbl t, (select count(*) cnt from t.int_array) v
---- RESULTS
1,3
2,6
3,0
4,0
5,0
6,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array with no slots
select cnt from complextypestbl t, (select count(*) cnt from t.int_array) v
---- RESULTS
3
6
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array that is flattened version of nested arrays
select id, a.item from complextypestbl t, t.int_array_array.item a
---- RESULTS
1,1
1,2
1,3
1,4
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
2,4
7,5
7,6
8,-1
8,-2
---- TYPES
bigint,int
====
---- QUERY
-- Materialize array that is flattened version of nested arrays
select a.item from complextypestbl t, t.int_array_array.item a
---- RESULTS
1
2
3
4
NULL
1
2
NULL
3
NULL
4
5
6
-1
-2
---- TYPES
int
====
---- QUERY
-- Materialize scalar and array that is flattened version of nested arrays
select id, cnt from complextypestbl t,
(select count(item) cnt from t.int_array_array.item) v
---- RESULTS
1,4
2,4
3,0
4,0
5,0
6,0
7,2
8,2
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array that is flattened version of nested arrays
select cnt from complextypestbl t,
(select count(item) cnt from t.int_array_array.item) v
---- RESULTS
4
4
0
0
0
0
2
2
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array with no slots that is flattened version of nested
-- arrays
select id, cnt from complextypestbl t,
(select count(*) cnt from t.int_array_array.item) v
---- RESULTS
1,4
2,7
3,0
4,0
5,0
6,0
7,2
8,2
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array with no slots that is flattened version of nested arrays
select cnt from complextypestbl t,
(select count(*) cnt from t.int_array_array.item) v
---- RESULTS
4
7
0
0
0
0
2
2
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array with no slots that is count of nested arrays
select id, cnt from complextypestbl t,
(select count(*) cnt from t.int_array_array) v
---- RESULTS
1,2
2,4
3,1
4,0
5,0
6,0
7,2
8,2
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array with no slots that is count of nested arrays
select cnt from complextypestbl t,
(select count(*) cnt from t.int_array_array) v
---- RESULTS
2
4
1
0
0
0
2
2
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array of arrays
select id, a2.item from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1,1
1,2
1,3
1,4
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
2,4
7,5
7,6
8,-1
8,-2
---- TYPES
bigint,int
====
---- QUERY
-- Materialize array of arrays
select a2.item from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1
2
3
4
NULL
1
2
NULL
3
NULL
4
5
6
-1
-2
---- TYPES
int
====
---- QUERY
-- Materialize scalar and array of arrays
select id, cnt from complextypestbl t, t.int_array_array a1,
(select count(a2.item) cnt from a1.item a2) v
---- RESULTS
1,2
1,2
2,2
2,2
2,0
2,0
3,0
7,0
7,2
8,2
8,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of arrays
select cnt from complextypestbl t, t.int_array_array a1,
(select count(a2.item) cnt from a1.item a2) v
---- RESULTS
2
2
2
2
0
0
0
0
2
2
0
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array of arrays with no slots
select id, cnt from complextypestbl t, t.int_array_array a1,
(select count(*) cnt from a1.item a2) v
---- RESULTS
1,2
1,2
2,4
2,3
2,0
2,0
3,0
7,0
7,2
8,2
8,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of arrays with no slots
select cnt from complextypestbl t, t.int_array_array a1,
(select count(*) cnt from a1.item a2) v
---- RESULTS
2
2
4
3
0
0
0
0
2
2
0
---- TYPES
bigint
====
---- QUERY
-- Materialize nested array
select cnt from complextypestbl.int_array_array a1,
(select count(a2.item) cnt from a1.item a2) v
---- RESULTS
2
2
2
2
0
0
0
0
2
2
0
---- TYPES
bigint
====
---- QUERY
-- Materialize nested array with no slots
select cnt from complextypestbl.int_array_array a1,
(select count(*) cnt from a1.item a2) v
---- RESULTS
2
2
4
3
0
0
0
0
2
2
0
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar, array, and array of arrays
select id, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(item) cnt2 from a1.item a2) v2
---- RESULTS
1,3,2
1,3,2
2,3,2
2,3,2
2,3,0
2,3,0
3,0,0
7,0,0
7,0,2
8,1,2
8,1,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize scalar, array of no slots, and array of arrays of no slots
select id, cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(*) cnt2 from a1.item a2) v2
---- RESULTS
1,3,2
1,3,2
2,6,4
2,6,3
2,6,0
2,6,0
3,0,0
7,0,0
7,0,2
8,1,2
8,1,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize scalar, array, and array of arrays of no slots
select id, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(*) cnt2 from a1.item a2) v2
---- RESULTS
1,3,2
1,3,2
2,3,4
2,3,3
2,3,0
2,3,0
3,0,0
7,0,0
7,0,2
8,1,2
8,1,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize scalar, array of no slots, and array of arrays
select id, cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(item) cnt2 from a1.item a2) v2
---- RESULTS
1,3,2
1,3,2
2,6,2
2,6,2
2,6,0
2,6,0
3,0,0
7,0,0
7,0,2
8,1,2
8,1,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize array, and array of arrays
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(item) cnt2 from a1.item a2) v2
---- RESULTS
3,2
3,2
3,2
3,2
3,0
3,0
0,0
0,0
0,2
1,2
1,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of no slots, and array of arrays of no slots
select cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(*) cnt2 from a1.item a2) v2
---- RESULTS
3,2
3,2
6,4
6,3
6,0
6,0
0,0
0,0
0,2
1,2
1,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array, and array of arrays of no slots
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(*) cnt2 from a1.item a2) v2
---- RESULTS
3,2
3,2
3,4
3,3
3,0
3,0
0,0
0,0
0,2
1,2
1,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of no slots, and array of arrays
select cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.int_array) v1,
t.int_array_array a1, (select count(item) cnt2 from a1.item a2) v2
---- RESULTS
3,2
3,2
6,2
6,2
6,0
6,0
0,0
0,0
0,2
1,2
1,0
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalar, array, and flattened version of nested arrays
select id, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
(select count(item) cnt2 from t.int_array_array.item) v2
---- RESULTS
1,3,4
2,3,4
3,0,0
4,0,0
5,0,0
6,0,0
7,0,2
8,1,2
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize array and flattened version of nested arrays
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
(select count(item) cnt2 from t.int_array_array.item) v2
---- RESULTS
3,4
3,4
0,0
0,0
0,0
0,0
0,2
1,2
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalar, array, and flattened version of nested arrays with no slots
select id, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
(select count(*) cnt2 from t.int_array_array.item) v2
---- RESULTS
1,3,4
2,3,7
3,0,0
4,0,0
5,0,0
6,0,0
7,0,2
8,1,2
---- TYPES
bigint,bigint,bigint
====
---- QUERY
-- Materialize array and flattened version of nested arrays with no slots
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.int_array) v1,
(select count(*) cnt2 from t.int_array_array.item) v2
---- RESULTS
3,4
3,7
0,0
0,0
0,0
0,0
0,2
1,2
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalar and deeply nested array
select id, e, f from complextypestbl t, t.nested_struct.c.d.item
---- 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
-- Materialize deeply nested array
select e, f from complextypestbl t, t.nested_struct.c.d.item
---- RESULTS
10,'aaa'
-10,'bbb'
11,'c'
NULL,'NULL'
10,'aaa'
NULL,'NULL'
-10,'bbb'
NULL,'NULL'
11,'c'
NULL,'NULL'
NULL,'NULL'
-1,'nonnullable'
---- TYPES
int,string
====
---- QUERY
-- Materialize scalar and complicated nested array (may be optimized away someday)
select id, arr.item.e, arr.item.f from complextypestbl t, t.nested_struct.c.d, d.item arr
---- 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
-- Materialize complicated nested array (may be optimized away someday)
select arr.item.e, arr.item.f from complextypestbl t, t.nested_struct.c.d, d.item arr
---- RESULTS
10,'aaa'
-10,'bbb'
11,'c'
NULL,'NULL'
10,'aaa'
NULL,'NULL'
-10,'bbb'
NULL,'NULL'
11,'c'
NULL,'NULL'
NULL,'NULL'
-1,'nonnullable'
---- TYPES
int,string
====
---- QUERY
-- Materialize scalar and array of arrays, with structs in schema
select id, cnt from complextypestbl t, t.nested_struct.c.d,
(select count(arr.item.e) cnt from d.item arr) v
---- RESULTS
1,2
1,1
2,2
2,1
2,0
2,0
7,0
7,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of arrays, with structs in schema
select cnt from complextypestbl t, t.nested_struct.c.d,
(select count(arr.item.e) cnt from d.item arr) v
---- RESULTS
2
1
2
1
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array of arrays of no slots, with structs in schema
select id, cnt from complextypestbl t, t.nested_struct.c.d,
(select count(*) cnt from d.item arr) v
---- RESULTS
1,2
1,1
2,5
2,2
2,0
2,0
7,0
7,1
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of arrays of no slots, with structs in schema
select cnt from complextypestbl t, t.nested_struct.c.d,
(select count(*) cnt from d.item arr) v
---- RESULTS
2
1
5
2
0
0
0
1
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array of no slots counting nested arrays, with structs in schema
select id, cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d) v
---- RESULTS
1,2
2,4
3,0
4,0
5,0
6,0
7,3
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of no slots counting nested arrays, with structs in schema
select cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d) v
---- RESULTS
2
4
0
0
0
0
3
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalars, array, and array of arrays, with structs in schema
select id, t.nested_struct.a, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(arr.item.e) cnt2 from d.item arr) v2
---- RESULTS
1,1,1,2
1,1,1,1
2,NULL,0,2
2,NULL,0,1
2,NULL,0,0
2,NULL,0,0
7,7,2,0
7,7,2,0
7,7,2,0
8,-1,1,1
---- TYPES
bigint,int,bigint,bigint
====
---- QUERY
-- Materialize array and array of arrays, with structs in schema
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(arr.item.e) cnt2 from d.item arr) v2
---- RESULTS
1,2
1,1
0,2
0,1
0,0
0,0
2,0
2,0
2,0
1,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalars, array with no slots, and array of arrays with no slots, with
-- structs in schema
select id, t.nested_struct.a, cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(*) cnt2 from d.item arr) v2
---- RESULTS
1,1,1,2
1,1,1,1
2,NULL,1,5
2,NULL,1,2
2,NULL,1,0
2,NULL,1,0
7,7,3,0
7,7,3,1
7,7,3,0
8,-1,1,1
---- TYPES
bigint,int,bigint,bigint
====
---- QUERY
-- Materialize array with no slots and array of arrays with no slots, with structs in
-- schema
select cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(*) cnt2 from d.item arr) v2
---- RESULTS
1,2
1,1
1,5
1,2
1,0
1,0
3,0
3,1
3,0
1,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalars, array, and array of arrays with no slots, with structs in schema
select id, t.nested_struct.a, cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(*) cnt2 from d.item arr) v2
---- RESULTS
1,1,1,2
1,1,1,1
2,NULL,0,5
2,NULL,0,2
2,NULL,0,0
2,NULL,0,0
7,7,2,0
7,7,2,1
7,7,2,0
8,-1,1,1
---- TYPES
bigint,int,bigint,bigint
====
---- QUERY
-- Materialize array and array of arrays with no slots, with structs in schema
select cnt1, cnt2 from complextypestbl t,
(select count(item) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(*) cnt2 from d.item arr) v2
---- RESULTS
1,2
1,1
0,5
0,2
0,0
0,0
2,0
2,1
2,0
1,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalars, array with no slots, and array of arrays, with structs in schema
select id, t.nested_struct.a, cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(arr.item.e) cnt2 from d.item arr) v2
---- RESULTS
1,1,1,2
1,1,1,1
2,NULL,1,2
2,NULL,1,1
2,NULL,1,0
2,NULL,1,0
7,7,3,0
7,7,3,0
7,7,3,0
8,-1,1,1
---- TYPES
bigint,int,bigint,bigint
====
---- QUERY
-- Materialize array with no slots and array of arrays, with structs in schema
select cnt1, cnt2 from complextypestbl t,
(select count(*) cnt1 from t.nested_struct.b) v1,
t.nested_struct.c.d, (select count(arr.item.e) cnt2 from d.item arr) v2
---- RESULTS
1,2
1,1
1,2
1,1
1,0
1,0
3,0
3,0
3,0
1,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize scalar and complicated nested array
select id, cnt from complextypestbl t,
(select count(e) cnt from t.nested_struct.c.d.item) v
---- RESULTS
1,3
2,3
3,0
4,0
5,0
6,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize complicated nested array
select cnt from complextypestbl t,
(select count(e) cnt from t.nested_struct.c.d.item) v
---- RESULTS
3
3
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and complicated nested array with no slots
select id, cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d.item) v
---- RESULTS
1,3
2,7
3,0
4,0
5,0
6,0
7,1
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize complicated nested array with no slots
select cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d.item) v
---- RESULTS
3
7
0
0
0
0
1
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and array of arrays, with structs in schema
select id, cnt from complextypestbl t, t.nested_struct.c.d,
(select count(f) cnt from d.item) v
---- RESULTS
1,2
1,1
2,2
2,1
2,0
2,0
7,0
7,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize array of arrays, with structs in schema
select cnt from complextypestbl t, t.nested_struct.c.d,
(select count(f) cnt from d.item) v
---- RESULTS
2
1
2
1
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and nested array from struct
select nested_struct.a, f from complextypestbl t, t.nested_struct.c.d.item
---- RESULTS
1,'aaa'
1,'bbb'
1,'c'
NULL,'NULL'
NULL,'aaa'
NULL,'NULL'
NULL,'bbb'
NULL,'NULL'
NULL,'c'
NULL,'NULL'
7,'NULL'
-1,'nonnullable'
---- TYPES
int,string
====
---- QUERY
-- Materialize scalar and nested array from struct
select id, cnt from complextypestbl t,
(select count(f) cnt from t.nested_struct.c.d.item) v;
---- RESULTS
1,3
2,3
3,0
4,0
5,0
6,0
7,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize nested array from struct
select cnt from complextypestbl t,
(select count(f) cnt from t.nested_struct.c.d.item) v;
---- RESULTS
3
3
0
0
0
0
0
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and nested array with no slots, with structs in schema
select id, cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d.item) v;
---- RESULTS
1,3
2,7
3,0
4,0
5,0
6,0
7,1
8,1
---- TYPES
bigint,bigint
====
---- QUERY
-- Materialize nested array with no slots, with structs in schema
select cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d.item) v;
---- RESULTS
3
7
0
0
0
0
1
1
---- TYPES
bigint
====
---- QUERY
-- Materialize scalar and flattened array with no slots from struct
select nested_struct.a, cnt from complextypestbl t,
(select count(*) cnt from t.nested_struct.c.d.item) v
---- RESULTS
1,3
NULL,7
NULL,0
NULL,0
NULL,0
NULL,0
7,1
-1,1
---- TYPES
int,bigint
====
---- QUERY
-- Materialize array and nested array from struct
select a1.item, a2.f
from complextypestbl t, t.nested_struct.b a1, t.nested_struct.c.d.item a2
---- RESULTS
1,'aaa'
1,'bbb'
1,'c'
NULL,'NULL'
NULL,'aaa'
NULL,'NULL'
NULL,'bbb'
NULL,'NULL'
NULL,'c'
NULL,'NULL'
2,'NULL'
3,'NULL'
NULL,'NULL'
-1,'nonnullable'
---- TYPES
int,string
====
---- QUERY
-- Materialize scalar, array, and nested array from struct
select t.nested_struct.a, a1.item, a2.f
from complextypestbl t, t.nested_struct.b a1, t.nested_struct.c.d.item a2
---- RESULTS
1,1,'aaa'
1,1,'bbb'
1,1,'c'
NULL,NULL,'NULL'
NULL,NULL,'aaa'
NULL,NULL,'NULL'
NULL,NULL,'bbb'
NULL,NULL,'NULL'
NULL,NULL,'c'
NULL,NULL,'NULL'
7,2,'NULL'
7,3,'NULL'
7,NULL,'NULL'
-1,-1,'nonnullable'
---- TYPES
int,int,string
====