testdata/workloads/functional-query/queries/QueryTest/nested-array-in-select-list.test

====
---- QUERY
select id, int_array from complextypestbl
---- RESULTS
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
---- TYPES
bigint,string
====
---- QUERY
select id, int_array from complextypestbl where id=1
---- RESULTS
1,'[1,2,3]'
---- TYPES
bigint,string
====
---- QUERY
select id, int_array, int_array_array from complextypestbl
---- RESULTS
8,'[-1]','[[-1,-2],[]]'
1,'[1,2,3]','[[1,2],[3,4]]'
2,'[null,1,2,null,3,null]','[[null,1,2,null],[3,null,4],[],null]'
3,'[]','[null]'
4,'NULL','[]'
5,'NULL','NULL'
6,'NULL','NULL'
7,'NULL','[null,[5,6]]'
---- TYPES
bigint,string,string
====
---- QUERY
# Same collection used twice in a select list.
select id, int_array, int_array from complextypestbl
---- RESULTS
1,'[1,2,3]','[1,2,3]'
2,'[null,1,2,null,3,null]','[null,1,2,null,3,null]'
3,'[]','[]'
4,'NULL','NULL'
5,'NULL','NULL'
6,'NULL','NULL'
7,'NULL','NULL'
8,'[-1]','[-1]'
---- TYPES
bigint,string,string
====
---- QUERY
# Same collection used from two versions of the same table/
select t1.id, t1.int_array, t2.int_array
 from complextypestbl t1 join complextypestbl t2
 on t1.id = t2.id
---- RESULTS
3,'[]','[]'
5,'NULL','NULL'
7,'NULL','NULL'
8,'[-1]','[-1]'
1,'[1,2,3]','[1,2,3]'
2,'[null,1,2,null,3,null]','[null,1,2,null,3,null]'
4,'NULL','NULL'
6,'NULL','NULL'
---- TYPES
bigint,string,string
====
---- QUERY
select id, int_array from complextypestbl union all select id, int_array from complextypestbl
---- RESULTS
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
---- TYPES
bigint,string
====
---- QUERY
# TODO: only UNION ALL, is supported, UNION currently crashes Impala. UNION needs
#       several utility functions in the BE, so for now we should reject it in the FE
select id, int_array from complextypestbl union select id, int_array from complextypestbl;
---- CATCH
IllegalStateException: UNION, EXCEPT and INTERSECT are not supported for collection types
====
---- QUERY
# Changing a column to a different type leads to a "non-pass-through" union that does a
# deepcopy on the tuple.
select id, arr_int_1d, arr_string_3d from collection_tbl
  union all select cast(id as tinyint), arr_int_1d, arr_string_3d from collection_tbl
---- RESULTS
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]'
3,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]'
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]'
3,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]'
---- TYPES
int,string,string
====
---- QUERY
# Constants in the select list of unions also lead to a "non-pass-through" union.
select 1, arr_int_1d, arr_string_3d from collection_tbl
  union all select 2, arr_int_1d, arr_string_3d from collection_tbl
---- RESULTS
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]'
1,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]'
1,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]'
2,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]'
2,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]'
---- TYPES
tinyint,string,string
====
---- QUERY
# Constants in the select list of unions also lead to a "non-pass-through" union and
# structs containing collections are not allowed there yet because we can't materialise
# them, see IMPALA-12160. However, set operations on structs are not supported at all at
# present, and the error reflects that. When both limitations are lifted, the query should
# succeed.
select 1, struct_contains_arr, struct_contains_nested_arr, all_mix from collection_struct_mix
  union all select 2, struct_contains_arr, struct_contains_nested_arr, all_mix from collection_struct_mix
---- CATCH
AnalysisException: Set operations don't support STRUCT types or types containing STRUCT types. STRUCT<arr:ARRAY<INT>> in struct_contains_arr.
====
---- QUERY
select 1 from (select int_array from complextypestbl) s
---- RESULTS
1
1
1
1
1
1
1
1
---- TYPES
tinyint
====
---- QUERY
# Regression test for IMPALA-12695.
with v as (select arr1 from complextypes_arrays
  union all select arr1 from complextypes_arrays)
select am.item from v, v.arr1 am;
---- RESULTS
1
2
3
4
5
1
NULL
3
4
5
10
9
8
10
10
NULL
12
1
2
1
2
3
1
2
3
4
5
1
NULL
3
4
5
10
9
8
10
10
NULL
12
1
2
1
2
3
---- TYPES
int
====
---- QUERY
# Filtering an unnested collection that comes from a UNION [ALL] is not supported, see
# IMPALA-12753.
with v as (select arr1 from complextypes_arrays
  union all select arr1 from complextypes_arrays)
select am.item from v, v.arr1 am where am.item is NULL;
---- CATCH
AnalysisException: Filtering an unnested collection that comes from a UNION [ALL] is not supported yet.
====
---- QUERY
# Regression test for IMPALA-12695 with varlen collection item and an additional inline view layer.
with v0 as (select arr2 from complextypes_arrays
  union all select arr2 from complextypes_arrays),
     v1 as (select am.item from v0, v0.arr2 am)
select item from v1;
---- RESULTS
'one'
'two'
'three'
'four'
'five'
'one'
'two'
'three'
'NULL'
'five'
'ten'
'ten'
'nine'
'eight'
'ten'
'eleven'
'twelve'
'thirteen'
'str1'
'str2'
'str1'
'str2'
'one'
'two'
'three'
'four'
'five'
'one'
'two'
'three'
'NULL'
'five'
'ten'
'ten'
'nine'
'eight'
'ten'
'eleven'
'twelve'
'thirteen'
'str1'
'str2'
'str1'
'str2'
---- TYPES
string
====
---- QUERY
# Filtering an unnested collection that comes from a UNION [ALL] is not supported, see
# IMPALA-12753.
with v0 as (select arr2 from complextypes_arrays
  union all select arr2 from complextypes_arrays),
     v1 as (select am.item from v0, v0.arr2 am)
select item from v1 where item="one";
---- CATCH
AnalysisException: Filtering an unnested collection that comes from a UNION [ALL] is not supported yet.
====
---- QUERY
# Filtering an unnested collection that comes from a UNION [ALL] is not supported, see
# IMPALA-12753. The query contains an extra inline view layer above the union.
with v0 as (select arr2 from complextypes_arrays
  union all select arr2 from complextypes_arrays),
     v1 as (select 1, arr2 from v0),
     v2 as (select am.item from v1, v1.arr2 am)
select item from v2 where item="one";
---- CATCH
AnalysisException: Filtering an unnested collection that comes from a UNION [ALL] is not supported yet.
====
---- QUERY
select id, int_array from (select id, int_array from complextypestbl) s;
---- RESULTS
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
---- TYPES
bigint,string
====
---- QUERY
with s as (select id, t.int_array from complextypestbl t)
select id, int_array from s;
---- RESULTS
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
---- TYPES
bigint,string
====
---- QUERY
select id, int_array from complextypes_arrays_only_view;
---- RESULTS
1,'[1,2,3]'
2,'[null,1,2,null,3,null]'
3,'[]'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'[-1]'
---- TYPES
bigint,string
====
---- QUERY
# Unnesting array returned by view.
select id, a.item from complextypes_arrays_only_view v, v.int_array a;
---- RESULTS
8,-1
1,1
1,2
1,3
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned from WITH clause and predicate in inner query.
with v as (select id, int_array from complextypestbl where id=1)
select v.id, a.item from v, v.int_array a;
---- RESULTS
1,1
1,2
1,3
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned from WITH clause and predicate in outer query.
with v as (select id, int_array from complextypestbl)
select v.id, a.item from v, v.int_array a where id=1;
---- RESULTS
1,1
1,2
1,3
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned from WITH clause on item.
with v as (select id, int_array from complextypestbl)
select v.id, a.item from v, v.int_array a where  a.item=1
---- RESULTS
1,1
2,1
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned by view wrapped in inline view.
select v.id, a.item from
  (select id, int_array from complextypes_arrays_only_view) v, v.int_array a;
---- RESULTS
8,-1
1,1
1,2
1,3
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned by view wrapped in inline view + WITH clause.
with v2 as (select id, int_array from complextypes_arrays_only_view)
select v.id, a.item from (select id, int_array from v2) v, v.int_array a;
---- RESULTS
8,-1
1,1
1,2
1,3
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
---- TYPES
bigint,int
====
---- QUERY
# Unnesting array returned by view wrapped in inline view + WITH clause.
with v2 as (select id, int_array from complextypes_arrays_only_view)
select v.id, a.item from
  (select id, int_array from v2 where id=1) v, v.int_array a
  where a.item=1;
---- RESULTS
1,1
---- TYPES
bigint,int
====
---- QUERY
select item from unnest(complextypestbl.int_array_array)
---- RESULTS
'[-1,-2]'
'[]'
'[1,2]'
'[3,4]'
'[null,1,2,null]'
'[3,null,4]'
'[]'
'NULL'
'NULL'
'NULL'
'[5,6]'
---- TYPES
string
====
---- QUERY
select id, a.item from complextypes_arrays_only_view t left join t.int_array a;
---- RESULTS
1,1
1,2
1,3
2,NULL
2,1
2,2
2,NULL
2,3
2,NULL
3,NULL
4,NULL
5,NULL
6,NULL
7,NULL
8,-1
---- TYPES
BIGINT,INT
====
---- QUERY
select id, a2.item from complextypes_arrays_only_view v, v.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
# IMPALA-11052: allow using collections returned from views as non-relative table refs
select item from complextypes_arrays_only_view.int_array
---- CATCH
AnalysisException: Non-relative collections are currently not supported on collections from views.
====
---- QUERY
# IMPALA-11052: allow using collections returned from views as non-relative table refs
with s as (select int_array a from complextypestbl t)
select item from s.a
---- CATCH
AnalysisException: Could not resolve table reference: 's.a
====
---- QUERY
# IMPALA-11280. There is a join involved here by using the IN operator, and multiple
# arrays are unnested. Checks that the predicate on an unnested array is evaluated
# correctly.
select id, unnested_arr1, unnested_arr2
from (
    select id, unnest(arr1) as unnested_arr1, unnest(arr2) as unnested_arr2
    from complextypes_arrays
    where id % 2 = 1 and id in (select id from alltypestiny)
) a
where a.unnested_arr1 < 5;
---- RESULTS
1,1,'one'
1,2,'two'
1,3,'three'
1,4,'four'
7,1,'NULL'
7,2,'NULL'
---- TYPES
INT,INT,STRING
====
---- QUERY
# Similar as above but here the join is explicitly included in the query string and is not
# a result of a query rewrite.
select a.id, unnested_arr1, unnested_arr2
from (
    select cta.id, unnest(arr1) as unnested_arr1, unnest(arr2) as unnested_arr2
    from functional_parquet.complextypes_arrays cta
        left join functional_parquet.alltypestiny ti on cta.id = ti.id
    where cta.id % 2 = 1) a
where a.unnested_arr1 < 5;
---- RESULTS
1,1,'one'
1,2,'two'
1,3,'three'
1,4,'four'
7,1,'NULL'
7,2,'NULL'
---- TYPES
INT,INT,STRING
====
---- QUERY
# Regression test for:
#   IMPALA-11434: "More than 1 2d arrays in select list causes analysis error"
select id, arr_int_1d, arr_int_2d, arr_int_3d, arr_string_1d, arr_string_2d, arr_string_3d from collection_tbl;
---- RESULTS
1,'[1,2,null]','[[1,2,null],[3]]','[[[1,2,null],[3]],[[4]]]','["1","two wooden boxes",null]','[["one silk glove","2",null],["three pancakes"]]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]'
2,'[1,null,3]','[[null,1,2,null],[5,14,null]]','[[[null,1,2,null],[5,14,null]],[[null,5]]]','["one dinosaur bone",null,"2",null]','[["1","2",null,"four dinosaur bones"],["five dinosaur bones"]]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]'
3,'[null,4679,null,49,null]','[[1,2,null,null,856],[365,855,369,null]]','[[[1,null,2,null],[null,15]],[[null,4]]]','["1",null,"three even-toed ungulates"]','[["one even-toed ungulate","2",null,null],[null,"three even-toed ungulates"]]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]'
---- TYPES
INT,STRING,STRING,STRING,STRING,STRING,STRING
====
---- QUERY
# Regression test for:
#   IMPALA-11567: "Error in left outer join if the right side is a subquery with complex types"
select a.id, b.arr_int_1d, b.arr_int_2d
from alltypestiny a left outer join
  (select id, arr_int_1d, arr_int_2d from collection_tbl) b
on a.id = b.id where a.id < 3;
---- RESULTS
1,'[1,2,null]','[[1,2,null],[3]]'
2,'[1,null,3]','[[null,1,2,null],[5,14,null]]'
0,'NULL','NULL'
---- TYPES
INT,STRING,STRING
====
---- QUERY
select distinct arr1 from complextypes_arrays
---- CATCH
AnalysisException: Complex types are not supported in SELECT DISTINCT clauses.
====
---- QUERY
select count(distinct arr1) from complextypes_arrays
---- CATCH
AnalysisException: Complex types are not supported as DISTINCT parameters of aggregate functions.
====