| ==== |
| ---- 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 |
| ==== |