testdata/workloads/functional-query/queries/QueryTest/nested-types-scanner-position.test

====
---- QUERY
select id, pos, item from complextypestbl t, t.int_array
---- RESULTS
1,0,1
1,1,2
1,2,3
2,0,NULL
2,1,1
2,2,2
2,3,NULL
2,4,3
2,5,NULL
8,0,-1
---- TYPES
bigint,bigint,int
====
---- QUERY
select id, pos from complextypestbl t, t.int_array
---- RESULTS
1,0
1,1
1,2
2,0
2,1
2,2
2,3
2,4
2,5
8,0
---- TYPES
bigint,bigint
====
---- QUERY
select pos, item from complextypestbl.int_array
---- RESULTS
0,1
1,2
2,3
0,NULL
1,1
2,2
3,NULL
4,3
5,NULL
0,-1
---- TYPES
bigint,int
====
---- QUERY
select pos from complextypestbl.int_array
---- RESULTS
0
1
2
0
1
2
3
4
5
0
---- TYPES
bigint
====
---- QUERY
select id, pos, item from complextypestbl t, t.int_array_array.item
---- RESULTS
1,0,1
1,1,2
1,0,3
1,1,4
2,0,NULL
2,1,1
2,2,2
2,3,NULL
2,0,3
2,1,NULL
2,2,4
7,0,5
7,1,6
8,0,-1
8,1,-2
---- TYPES
bigint,bigint,int
====
---- QUERY
select id, pos from complextypestbl t, t.int_array_array.item
---- RESULTS
1,0
1,1
1,0
1,1
2,0
2,1
2,2
2,3
2,0
2,1
2,2
7,0
7,1
8,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
select pos, item from complextypestbl.int_array_array.item
---- RESULTS
0,1
1,2
0,3
1,4
0,NULL
1,1
2,2
3,NULL
0,3
1,NULL
2,4
0,5
1,6
0,-1
1,-2
---- TYPES
bigint,int
====
---- QUERY
select pos, item from complextypestbl.int_array_array.item where pos > 1
---- RESULTS
2,2
3,NULL
2,4
---- TYPES
bigint,int
====
---- QUERY
select pos from complextypestbl.int_array_array.item
---- RESULTS
0
1
0
1
0
1
2
3
0
1
2
0
1
0
1
---- TYPES
bigint
====
---- QUERY
select pos from complextypestbl.int_array_array
---- RESULTS
0
1
0
1
2
3
0
0
1
0
1
---- TYPES
bigint
====
---- QUERY
select a1.pos, a2.item from complextypestbl.int_array_array a1, a1.item a2
---- RESULTS
0,1
0,2
1,3
1,4
0,NULL
0,1
0,2
0,NULL
1,3
1,NULL
1,4
1,5
1,6
0,-1
0,-2
---- TYPES
bigint,int
====
---- QUERY
select a1.pos, a2.pos from complextypestbl.int_array_array a1, a1.item a2
---- RESULTS
0,0
0,1
1,0
1,1
0,0
0,1
0,2
0,3
1,0
1,1
1,2
1,0
1,1
0,0
0,1
---- TYPES
bigint,bigint
====
---- QUERY
select a1.pos, a2.pos, a2.item from complextypestbl.int_array_array a1, a1.item a2
---- RESULTS
0,0,1
0,1,2
1,0,3
1,1,4
0,0,NULL
0,1,1
0,2,2
0,3,NULL
1,0,3
1,1,NULL
1,2,4
1,0,5
1,1,6
0,0,-1
0,1,-2
---- TYPES
bigint,bigint,int
====
---- QUERY
select id, a1.pos, a2.item
from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1,0,1
1,0,2
1,1,3
1,1,4
2,0,NULL
2,0,1
2,0,2
2,0,NULL
2,1,3
2,1,NULL
2,1,4
7,1,5
7,1,6
8,0,-1
8,0,-2
---- TYPES
bigint,bigint,int
====
---- QUERY
select id, a1.pos, a2.pos, a2.item
from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1,0,0,1
1,0,1,2
1,1,0,3
1,1,1,4
2,0,0,NULL
2,0,1,1
2,0,2,2
2,0,3,NULL
2,1,0,3
2,1,1,NULL
2,1,2,4
7,1,0,5
7,1,1,6
8,0,0,-1
8,0,1,-2
---- TYPES
bigint,bigint,bigint,int
====
---- QUERY
select id, a1.pos, a2.pos
from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1,0,0
1,0,1
1,1,0
1,1,1
2,0,0
2,0,1
2,0,2
2,0,3
2,1,0
2,1,1
2,1,2
7,1,0
7,1,1
8,0,0
8,0,1
---- TYPES
bigint,bigint,bigint
====
---- QUERY
select id, a1.pos from complextypestbl t, t.int_array_array a1, a1.item a2
---- RESULTS
1,0
1,0
1,1
1,1
2,0
2,0
2,0
2,0
2,1
2,1
2,1
7,1
7,1
8,0
8,0
---- TYPES
bigint,bigint
====
---- QUERY
select id, a1.pos, cnt from complextypestbl t, t.int_array_array a1,
(select count(*) cnt from a1.item) v
---- RESULTS
1,0,2
1,1,2
2,0,4
2,1,3
2,2,0
2,3,0
3,0,0
7,0,0
7,1,2
8,0,2
8,1,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
select id, a1.pos from complextypestbl t, t.int_array_array a1
---- RESULTS
1,0
1,1
2,0
2,1
2,2
2,3
3,0
7,0
7,1
8,0
8,1
---- TYPES
bigint,bigint
====
---- QUERY
select id, pos from complextypestbl t, t.nested_struct.c.d
---- RESULTS
1,0
1,1
2,0
2,1
2,2
2,3
7,0
7,1
7,2
8,0
---- TYPES
bigint,bigint
====
---- QUERY
select pos from complextypestbl t, t.nested_struct.c.d
---- RESULTS
0
1
0
1
2
3
0
1
2
0
---- TYPES
bigint
====
---- QUERY
select nested_struct.a, pos from complextypestbl t, t.nested_struct.c.d
---- RESULTS
1,0
1,1
NULL,0
NULL,1
NULL,2
NULL,3
7,0
7,1
7,2
-1,0
---- TYPES
int,bigint
====
---- QUERY
select nested_struct.a, b.pos, d.pos
from complextypestbl t, t.nested_struct.b, t.nested_struct.c.d
---- RESULTS
1,0,0
1,0,1
NULL,0,0
NULL,0,1
NULL,0,2
NULL,0,3
7,0,0
7,0,1
7,0,2
7,1,0
7,1,1
7,1,2
7,2,0
7,2,1
7,2,2
-1,0,0
---- TYPES
int,bigint,bigint
====
---- QUERY
select pos from complextypestbl.nested_struct.c.d
---- RESULTS
0
1
0
1
2
3
0
1
2
0
---- TYPES
bigint
====
---- QUERY
select id, d.pos, i.pos from complextypestbl t, t.nested_struct.c.d, d.item i
---- RESULTS
1,0,0
1,0,1
1,1,0
2,0,0
2,0,1
2,0,2
2,0,3
2,0,4
2,1,0
2,1,1
7,1,0
8,0,0
---- TYPES
bigint,bigint,bigint
====
---- QUERY
select id, d.pos, i.pos, i.f from complextypestbl t, t.nested_struct.c.d, d.item i
---- RESULTS
1,0,0,'aaa'
1,0,1,'bbb'
1,1,0,'c'
2,0,0,'NULL'
2,0,1,'aaa'
2,0,2,'NULL'
2,0,3,'bbb'
2,0,4,'NULL'
2,1,0,'c'
2,1,1,'NULL'
7,1,0,'NULL'
8,0,0,'nonnullable'
---- TYPES
bigint,bigint,bigint,string
====
---- QUERY
select id, d.pos, i.f from complextypestbl t, t.nested_struct.c.d, d.item i
---- RESULTS
1,0,'aaa'
1,0,'bbb'
1,1,'c'
2,0,'NULL'
2,0,'aaa'
2,0,'NULL'
2,0,'bbb'
2,0,'NULL'
2,1,'c'
2,1,'NULL'
7,1,'NULL'
8,0,'nonnullable'
---- TYPES
bigint,bigint,string
====
---- QUERY
select id, nested_struct.a, d1.pos, d2.pos
from complextypestbl t, t.nested_struct.c.d d1, t.nested_struct.c.d d2
---- RESULTS
1,1,0,0
1,1,0,1
1,1,1,0
1,1,1,1
2,NULL,0,0
2,NULL,0,1
2,NULL,0,2
2,NULL,0,3
2,NULL,1,0
2,NULL,1,1
2,NULL,1,2
2,NULL,1,3
2,NULL,2,0
2,NULL,2,1
2,NULL,2,2
2,NULL,2,3
2,NULL,3,0
2,NULL,3,1
2,NULL,3,2
2,NULL,3,3
7,7,0,0
7,7,0,1
7,7,0,2
7,7,1,0
7,7,1,1
7,7,1,2
7,7,2,0
7,7,2,1
7,7,2,2
8,-1,0,0
---- TYPES
bigint,int,bigint,bigint
====
---- QUERY
select nested_struct.a, d1.pos, i1.e, i1.f, d2.pos, i2.e, i2.f
from complextypestbl t, t.nested_struct.c.d d1, d1.item i1,
     t.nested_struct.c.d d2, d2.item i2
where i1.e = 10 or i2.e = 10
---- RESULTS
1,0,10,'aaa',0,10,'aaa'
1,0,10,'aaa',0,-10,'bbb'
1,0,10,'aaa',1,11,'c'
1,0,-10,'bbb',0,10,'aaa'
1,1,11,'c',0,10,'aaa'
NULL,0,10,'aaa',0,NULL,'NULL'
NULL,0,10,'aaa',0,10,'aaa'
NULL,0,10,'aaa',0,NULL,'NULL'
NULL,0,10,'aaa',0,-10,'bbb'
NULL,0,10,'aaa',0,NULL,'NULL'
NULL,0,10,'aaa',1,11,'c'
NULL,0,10,'aaa',1,NULL,'NULL'
NULL,0,NULL,'NULL',0,10,'aaa'
NULL,0,NULL,'NULL',0,10,'aaa'
NULL,0,-10,'bbb',0,10,'aaa'
NULL,0,NULL,'NULL',0,10,'aaa'
NULL,1,11,'c',0,10,'aaa'
NULL,1,NULL,'NULL',0,10,'aaa'
---- TYPES
int,bigint,int,string,bigint,int,string
====
---- QUERY
select nested_struct.a, d1.pos, i1.pos, i1.e, i1.f, d2.pos, i2.pos, i2.e, i2.f
from complextypestbl t, t.nested_struct.c.d d1, d1.item i1,
     t.nested_struct.c.d d2, d2.item i2
where i1.e = 10 or i2.e = 10
---- RESULTS
1,0,0,10,'aaa',0,0,10,'aaa'
1,0,0,10,'aaa',0,1,-10,'bbb'
1,0,0,10,'aaa',1,0,11,'c'
1,0,1,-10,'bbb',0,0,10,'aaa'
1,1,0,11,'c',0,0,10,'aaa'
NULL,0,1,10,'aaa',0,0,NULL,'NULL'
NULL,0,1,10,'aaa',0,1,10,'aaa'
NULL,0,1,10,'aaa',0,2,NULL,'NULL'
NULL,0,1,10,'aaa',0,3,-10,'bbb'
NULL,0,1,10,'aaa',0,4,NULL,'NULL'
NULL,0,1,10,'aaa',1,0,11,'c'
NULL,0,1,10,'aaa',1,1,NULL,'NULL'
NULL,0,0,NULL,'NULL',0,1,10,'aaa'
NULL,0,2,NULL,'NULL',0,1,10,'aaa'
NULL,0,3,-10,'bbb',0,1,10,'aaa'
NULL,0,4,NULL,'NULL',0,1,10,'aaa'
NULL,1,0,11,'c',0,1,10,'aaa'
NULL,1,1,NULL,'NULL',0,1,10,'aaa'
---- TYPES
int,bigint,bigint,int,string,bigint,bigint,int,string
====
---- QUERY
select nested_struct.a, d1.pos, i1.pos, d2.pos, i2.pos
from complextypestbl t, t.nested_struct.c.d d1, d1.item i1,
     t.nested_struct.c.d d2, d2.item i2
where i1.e = 10 or i2.e = 10
---- RESULTS
1,0,0,0,0
1,0,0,0,1
1,0,0,1,0
1,0,1,0,0
1,1,0,0,0
NULL,0,1,0,0
NULL,0,1,0,1
NULL,0,1,0,2
NULL,0,1,0,3
NULL,0,1,0,4
NULL,0,1,1,0
NULL,0,1,1,1
NULL,0,0,0,1
NULL,0,2,0,1
NULL,0,3,0,1
NULL,0,4,0,1
NULL,1,0,0,1
NULL,1,1,0,1
---- TYPES
int,bigint,bigint,bigint,bigint
====
---- QUERY
select d1.pos, i1.pos, d2.pos, i2.pos
from complextypestbl t, t.nested_struct.c.d d1, d1.item i1,
     t.nested_struct.c.d d2, d2.item i2
where i1.e = 10 or i2.e = 10
---- RESULTS
0,0,0,0
0,0,0,1
0,0,1,0
0,1,0,0
1,0,0,0
0,1,0,0
0,1,0,1
0,1,0,2
0,1,0,3
0,1,0,4
0,1,1,0
0,1,1,1
0,0,0,1
0,2,0,1
0,3,0,1
0,4,0,1
1,0,0,1
1,1,0,1
---- TYPES
bigint,bigint,bigint,bigint
====
---- QUERY
# Test materialising position slot in repeated nested collection to exercise
# code path with repeated definition levels.
select pos, item, count(*)
from complextypestbl_medium.int_array
group by 1, 2
---- RESULTS
0,-1,7300
0,1,7300
0,NULL,7300
1,1,7300
1,2,7300
2,2,7300
2,3,7300
3,NULL,7300
4,3,7300
5,NULL,7300
---- TYPES
BIGINT, INT, BIGINT
====