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

====
---- QUERY
# In-memory partitioned top-N containing a collection, with some partitions that hit the
# limit.
with joined as (
  select a.*, b.int_array from alltypesagg a left join complextypestbl b on a.tinyint_col = b.id
),
v as (
  select tinyint_col, id, int_array, row_number() over (partition by tinyint_col order by id) as rn
  from joined where id % 777 = 0 or id % 10 = 7)
select tinyint_col, id, int_array, rn from v
where rn <= 5
order by tinyint_col, rn
---- RESULTS
1,2331,'[1,2,3]',1
2,4662,'[null,1,2,null,3,null]',1
3,6993,'[]',1
4,1554,'NULL',1
4,9324,'NULL',2
5,3885,'NULL',1
6,6216,'NULL',1
7,7,'NULL',1
7,17,'NULL',2
7,27,'NULL',3
7,37,'NULL',4
7,47,'NULL',5
8,3108,'[-1]',1
9,5439,'NULL',1
NULL,0,'NULL',1
NULL,0,'NULL',2
NULL,7770,'NULL',3
NULL,7770,'NULL',4
---- TYPES
TINYINT, INT, STRING, BIGINT
====
---- QUERY
# In-memory partitioned top-N containing a collection with a variable length type, with
# some partitions that hit the limit.
with joined as (
  select a.*, b.arr_int_1d, b.arr_int_2d, b.map_1d, b.map_3d
  from alltypesagg a left join collection_tbl b on a.tinyint_col = b.id
),
v as (
  select tinyint_col, id, arr_int_1d, arr_int_2d, map_1d, map_3d,
    row_number() over (partition by tinyint_col order by id) as rn
  from joined where id % 777 = 0 or id % 10 = 7)
select tinyint_col, id, arr_int_1d, arr_int_2d, map_1d, map_3d, rn from v
where rn <= 5
order by tinyint_col, rn
---- RESULTS
1,2331,'[1,2,null]','[[1,2,null],[3]]','{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"}}}',1
2,4662,'[1,null,3]','[[null,1,2,null],[5,14,null]]','{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
3,6993,'[null,4679,null,49,null]','[[1,2,null,null,856],[365,855,369,null]]','{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"}}}',1
4,1554,'NULL','NULL','NULL','NULL',1
4,9324,'NULL','NULL','NULL','NULL',2
5,3885,'NULL','NULL','NULL','NULL',1
6,6216,'NULL','NULL','NULL','NULL',1
7,7,'NULL','NULL','NULL','NULL',1
7,17,'NULL','NULL','NULL','NULL',2
7,27,'NULL','NULL','NULL','NULL',3
7,37,'NULL','NULL','NULL','NULL',4
7,47,'NULL','NULL','NULL','NULL',5
8,3108,'NULL','NULL','NULL','NULL',1
9,5439,'NULL','NULL','NULL','NULL',1
NULL,0,'NULL','NULL','NULL','NULL',1
NULL,0,'NULL','NULL','NULL','NULL',2
NULL,7770,'NULL','NULL','NULL','NULL',3
NULL,7770,'NULL','NULL','NULL','NULL',4
---- TYPES
TINYINT, INT, STRING, STRING, STRING, STRING, BIGINT
====