testdata/workloads/functional-query/queries/QueryTest/top-n-complex.test

====
---- QUERY
# Sort collections with limit.
select id, arr_int_1d, arr_string_3d, map_1d, map_3d from collection_tbl order by id desc limit 2
---- RESULTS
3,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]','{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]','{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
---- TYPES
int,string,string,string,string
====
---- QUERY
# Sort collections from HMS view.
select id, int_array, int_array_array from complextypes_arrays_only_view order by id desc limit 4
---- RESULTS
8,'[-1]','[[-1,-2],[]]'
7,'NULL','[null,[5,6]]'
6,'NULL','NULL'
5,'NULL','NULL'
---- TYPES
bigint,string,string
====
---- QUERY
# Sort collection from WITH-clause inline view.
with v as (
  select id, arr_int_1d, arr_string_3d, map_1d, map_3d from collection_tbl
)
select id, arr_int_1d, arr_string_3d, map_1d, map_3d from collection_tbl order by id desc limit 2;
---- RESULTS
3,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]','{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]','{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
---- TYPES
int,string,string,string,string
====
---- QUERY
# Sort collections from nested query inline view with limit.
select id, arr_int_1d, arr_string_3d, map_1d, map_3d
from (select id, arr_int_1d, arr_string_3d, map_1d, map_3d from collection_tbl) v
order by id desc limit 2
---- RESULTS
3,'[null,4679,null,49,null]','[[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]],[["four even-toed ungulate"]]]','{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
2,'[1,null,3]','[[["second dinosaur bone",null,null],["three dinosaur bones"]],[["one",null,"four dinosaur bones"]]]','{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
---- TYPES
int,string,string,string,string
====
---- QUERY
# Sort a collection that is join-unnested.
select id, a.item
from collection_tbl t, t.arr_string_3d a
where id != 2
order by id desc limit 3
# Adding VERIFY_IS_EQUAL_SORTED because collections cannot be sorted and the order of the
# rows with the same 'id' is different for different files.
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
---- TYPES
int,string
====
---- QUERY
# Sort a collection that is join-unnested in a WITH-clause inline view.
with v as (
  select id, a.item m
  from complextypestbl t, t.int_map_array a
  where id != 8 and id != 2
)
select id, m from v order by id desc limit 3;

with v as (
  select id, a.item arr
  from collection_tbl t, t.arr_string_3d a
  where id != 2
)
select id, arr from v order by id desc limit 3
# Adding VERIFY_IS_EQUAL_SORTED because collections cannot be sorted and the order of the
# rows with the same 'id' is different for different files.
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
---- TYPES
int,string
====
---- QUERY
# Sort a collection that is join-unnested in a nested query inline view with limit.
select id, arr
from (
    select id, a.item arr
    from collection_tbl t, t.arr_string_3d a
  ) v
where id != 2
order by id desc limit 3
# Adding VERIFY_IS_EQUAL_SORTED because collections cannot be sorted and the order of the
# rows with the same 'id' is different for different files.
---- RESULTS: VERIFY_IS_EQUAL_SORTED
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
---- TYPES
int,string
====
---- QUERY
# Sorting a collection of strings.
select id, arr_string_1d from collection_tbl order by id desc limit 2
---- RESULTS
3,'["1",null,"three even-toed ungulates"]'
2,'["one dinosaur bone",null,"2",null]'
---- TYPES
int,string
====
---- QUERY
# Sort maps containing only fixed length types.
select id, map_int_int, map_char3_char5 from map_non_varlen order by id desc limit 7
---- RESULTS
10,'{100:1000,101:1010,102:1020}','{"aaj":"aaaaj"}'
9,'{90:900,91:910,92:920}','{"aai":"aaaai"}'
8,'{80:800,81:810,82:820}','{"aah":"aaaah"}'
7,'{70:700,71:710,72:720}','{"aag":"aaaag"}'
6,'{60:600,61:610,62:620}','{"aaf":"aaaaf"}'
5,'{50:500,51:510,52:520}','{"aae":"aaaae"}'
4,'{40:400,41:410,42:420}','{"aad":"aaaad"}'
---- TYPES
int,string,string
====
---- QUERY
# Sort collections selected from within a struct.
select id, struct_contains_arr.arr, struct_contains_nested_arr.arr from collection_struct_mix order by id desc limit 1;
---- RESULTS
2,'NULL','[["2022-12-10","2022-12-11",null,"2022-12-12"],null]'
---- TYPES
INT,STRING,STRING
====
---- QUERY
# Sort collections containing structs, also containing var-len data.
select id, arr_contains_struct, arr_contains_nested_struct from collection_struct_mix order by id desc limit 1;
---- RESULTS
2,'[{"i":100},{"i":8},{"i":35},{"i":45},null,{"i":193},{"i":null}]','[{"inner_struct1":null,"inner_struct2":{"str":"very few distilleries","l":128},"small":104},{"inner_struct1":{"str":"a few soju distilleries","l":28},"inner_struct2":{"str":"lots of soju distilleries","l":228},"small":105},null]'
---- TYPES
INT,STRING,STRING
====
---- QUERY
# Sorting a var-len struct nested in a collection. Regression test for IMPALA-12781.
select id, arr_contains_nested_struct from collection_struct_mix order by id limit 2;
---- RESULTS
1,'[{"inner_struct1":{"str":"","l":0},"inner_struct2":{"str":"four spaceship captains","l":2},"small":2},null,{"inner_struct1":{"str":null,"l":5},"inner_struct2":{"str":"more spaceship captains","l":8},"small":20}]'
2,'[{"inner_struct1":null,"inner_struct2":{"str":"very few distilleries","l":128},"small":104},{"inner_struct1":{"str":"a few soju distilleries","l":28},"inner_struct2":{"str":"lots of soju distilleries","l":228},"small":105},null]'
---- TYPES
INT,STRING

====
---- QUERY
# Regression test for IMPALA-13272
select
  row_no
from (
  select
    arr.small,
    row_number() over (order by arr.inner_struct1.str) as row_no
  from collection_struct_mix t, t.arr_contains_nested_struct arr
) res limit 4;
---- RESULTS
1
2
3
4
---- TYPES
BIGINT
====
---- QUERY
# Regression test for IMPALA-13272
select
  row_no, str
from (
  select
    arr.small,
    arr.inner_struct1.str str,
    row_number() over (order by arr.inner_struct1.str) as row_no
  from collection_struct_mix t, t.arr_contains_nested_struct arr
) res limit 4;
---- RESULTS
1,''
2,'a few soju distilleries'
3,'NULL'
4,'NULL'
---- TYPES
BIGINT,STRING
====
---- QUERY
# Regression test for IMPALA-13272
select
  row_no, str, small
from (
  select
    arr.small,
    arr.inner_struct2.str str,
    row_number() over (order by arr.inner_struct2.str) as row_no
  from collection_struct_mix t, t.arr_contains_nested_struct arr
) res limit 4;
---- RESULTS
1,'four spaceship captains',2
2,'lots of soju distilleries',105
3,'more spaceship captains',20
4,'very few distilleries',104
---- TYPES
BIGINT,STRING, SMALLINT

====
---- QUERY
# Regression test for IMPALA-13407
select id, str, alltypes, tiny_struct, small_struct from complextypes_structs order by str limit 4;
---- RESULTS
5,'fifth item','NULL','{"b":false}','{"i":98765,"s":"abcde f"}'
1,'first item','{"ti":100,"si":12348,"i":156789012,"bi":163234345342,"b":true,"f":1234.56005859375,"do":65323423.33,"da":"2021-05-30","ts":"2021-06-01 10:19:04","s1":"some string","s2":"another str","c1":"x","c2":"xyz","vc":"somevarcha","de1":12345,"de2":null}','{"b":true}','NULL'
4,'fourth item','{"ti":90,"si":30482,"i":1664336,"bi":23567459873,"b":true,"f":0.5600000023841858,"do":NaN,"da":"2000-12-31","ts":"2024-01-01 00:00:00.123400000","s1":"random string","s2":"","c1":"c","c2":"d  ","vc":"addsdrr","de1":33357,"de2":null}','{"b":null}','{"i":null,"s":"str"}'
2,'second item','{"ti":123,"si":4567,"i":1562322212,"bi":334333345342,"b":false,"f":NaN,"do":23233423.099,"da":null,"ts":"2020-06-11 12:10:04","s1":null,"s2":"NULL","c1":"a","c2":"ab ","vc":"varchar","de1":11223,"de2":null}','{"b":false}','{"i":19191,"s":"small_struct_str"}'
---- TYPES
INT,STRING,STRING,STRING,STRING

====
---- QUERY
# Sorting is not supported yet for collections within structs: IMPALA-12160. Test with a
# struct that contains an array.
select id, struct_contains_arr from collection_struct_mix order by id limit 5
---- CATCH
AnalysisException: Sorting is not supported if the select list contains collection(s) nested in struct(s).
====
---- QUERY
# Sorting is not supported yet for collections within structs: IMPALA-12160. Test with a
# struct that contains a map.
select id, struct_contains_map from collection_struct_mix order by id limit 5
---- CATCH
AnalysisException: Sorting is not supported if the select list contains collection(s) nested in struct(s).
====
---- QUERY
# Sorting is not supported yet for collections within structs: IMPALA-12160. Test with an
# array that contains a struct that contains an array.
select id, maps.key, maps.value.big.arr from collection_struct_mix t, t.all_mix maps order by id limit 7
---- CATCH
AnalysisException: Sorting is not supported if the select list contains collection(s) nested in struct(s).
====