testdata/workloads/functional-query/queries/QueryTest/sort-complex.test

====
---- QUERY
# Sort collections.
select id, arr_int_1d, arr_string_3d, map_1d, map_3d from collection_tbl order by id desc
---- 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"}}}'
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]','{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",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
---- RESULTS
8,'[-1]','[[-1,-2],[]]'
7,'NULL','[null,[5,6]]'
6,'NULL','NULL'
5,'NULL','NULL'
4,'NULL','[]'
3,'[]','[null]'
2,'[null,1,2,null,3,null]','[[null,1,2,null],[3,null,4],[],null]'
1,'[1,2,3]','[[1,2],[3,4]]'
---- 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
---- 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"}}}'
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]','{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}'
---- TYPES
int,string,string,string,string
====
---- QUERY
# Sort collection from nested query inline view.
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
---- 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"}}}'
1,'[1,2,null]','[[["1","second harmonic",null],["three cities"]],[["four castles"]]]','{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",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;
---- RESULTS
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
1,'[["four castles"]]'
---- TYPES
int,string
====
---- QUERY
# Sort a collection that is join-unnested in a WITH-clause inline view.
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;
---- RESULTS
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
1,'[["four castles"]]'
---- TYPES
int,string
====
---- QUERY
# Sort a collection that is join-unnested in a nested query inline view.
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;
---- RESULTS
3,'[["1","-1","second even-toed ungulate",null],["three even-toed ungulates"]]'
3,'[["four even-toed ungulate"]]'
1,'[["1","second harmonic",null],["three cities"]]'
1,'[["four castles"]]'
---- TYPES
int,string
====
---- QUERY
# Sort a collection of strings.
select id, arr_string_1d from collection_tbl order by id desc
---- RESULTS
3,'["1",null,"three even-toed ungulates"]'
2,'["one dinosaur bone",null,"2",null]'
1,'["1","two wooden boxes",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
---- 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"}'
3,'{30:300,31:310,32:320}','{"aac":"aaaac"}'
2,'{20:200,21:210,22:220}','{"aab":"aaaab"}'
1,'{10:100,11:110,12:120}','{"aaa":"aaaaa"}'
---- 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;
---- RESULTS
2,'NULL','[["2022-12-10","2022-12-11",null,"2022-12-12"],null]'
1,'[1,2,3,4,null,null,5]','[["2022-12-05","2022-12-06",null,"2022-12-07"],["2022-12-08","2022-12-09",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;
---- 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]'
1,'[{"i":1},{"i":2},{"i":3},{"i":4},null,{"i":5},{"i":null}]','[{"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}]'
---- TYPES
INT,STRING,STRING
====
---- QUERY
# Sorting a var-len struct nested in a collection.
select id, arr_contains_nested_struct from collection_struct_mix order by id;
---- 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;
---- RESULTS
1
2
3
4
5
6
---- 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;
---- RESULTS
1,''
2,'a few soju distilleries'
3,'NULL'
4,'NULL'
5,'NULL'
6,'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;
---- RESULTS
1,'four spaceship captains',2
2,'lots of soju distilleries',105
3,'more spaceship captains',20
4,'very few distilleries',104
5,'NULL',NULL
6,'NULL',NULL
---- TYPES
BIGINT,STRING, SMALLINT

====
---- QUERY
# Regression test for # IMPALA-13407
select id, str, alltypes, tiny_struct, small_struct from complextypes_structs order by str;
---- 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"}'
6,'sixth item','{"ti":127,"si":100,"i":234732212,"bi":664233223342,"b":true,"f":34.56000137329102,"do":99523423.33,"da":"1985-11-19","ts":"2020-09-15 03:11:22","s1":"string1","s2":"string2","c1":"z","c2":"   ","vc":"cv","de1":346,"de2":6235.600}','NULL','{"i":null,"s":null}'
3,'third item','{"ti":null,"si":null,"i":null,"bi":null,"b":null,"f":null,"do":null,"da":null,"ts":null,"s1":null,"s2":null,"c1":null,"c2":null,"vc":null,"de1":null,"de2":null}','{"b":true}','{"i":98765,"s":null}'
---- 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
---- 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;
---- 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
---- CATCH
AnalysisException: Sorting is not supported if the select list contains collection(s) nested in struct(s).
====
---- QUERY
# Sorting by collections is not supported. Test with an array of fixed length type.
select id, arr_int_1d from collection_tbl order by arr_int_1d
---- CATCH
AnalysisException: ORDER BY expression 'arr_int_1d' with complex type 'ARRAY<INT>' is not supported.
====
---- QUERY
# Sorting by collections is not supported. Test with an array of variable length type.
select id, arr_string_1d from collection_tbl order by arr_string_1d
---- CATCH
AnalysisException: ORDER BY expression 'arr_string_1d' with complex type 'ARRAY<STRING>' is not supported.
====
---- QUERY
# Sorting by collections is not supported. Test with a map of fixed length type.
select id, map_int_int from map_non_varlen order by map_int_int
---- CATCH
AnalysisException: ORDER BY expression 'map_int_int' with complex type 'MAP<INT,INT>' is not supported.
====
---- QUERY
# Sorting by collections is not supported. Test with a map of variable length type.
select id, map_1d from collection_tbl order by map_1d
---- CATCH
AnalysisException: ORDER BY expression 'map_1d' with complex type 'MAP<INT,STRING>' is not supported.
====