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

====
---- QUERY
# Test sending collections through a broadcast exchange as the build of a hash join.
select straight_join t1.id, m.key
from complextypestbl t1 join [broadcast] complextypestbl t2, t2.int_map m
where t1.id = t2.id
---- RESULTS
1,'k1'
1,'k2'
2,'k1'
2,'k2'
7,'k1'
7,'k3'
8,'k1'
---- TYPES
bigint,string
====
---- QUERY
# Test sending collections through a hash exchange as the build of a hash join.
select straight_join t1.id, m.key
from complextypestbl t1 join [shuffle] complextypestbl t2, t2.int_map m
where t1.id = t2.id
---- RESULTS
1,'k1'
1,'k2'
2,'k1'
2,'k2'
7,'k1'
7,'k3'
8,'k1'
---- TYPES
bigint,string
====
---- QUERY
# Test top-n after a subplan flattening an array.
select id, item from complextypestbl t, t.int_array
order by id, item limit 5
---- RESULTS
1,1
1,2
1,3
2,1
2,2
---- TYPES
bigint,int
====
---- QUERY
# Test top-n after a subplan flattening a map.
select id, key, value from complextypestbl t, t.int_map_array.item
order by id, key limit 3
---- RESULTS
1,'k1',1
2,'k1',1
2,'k3',NULL
---- TYPES
bigint,string,int
====
---- QUERY
# Test sort after a subplan that flattens a map.
select id, key, value from complextypestbl t, t.int_map
order by id, key
---- RESULTS
1,'k1',1
1,'k2',100
2,'k1',2
2,'k2',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Test non-grouping aggregation after a subplan that flattens a map.
select count(id), max(key), avg(value) from complextypestbl t, t.int_map
---- RESULTS
7,'k3',25.5
---- TYPES
bigint,string,double
====
---- QUERY
# Test grouping aggregation after a subplan that flattens a map.
select key, count(id), avg(value) from complextypestbl t, t.int_map
group by key
---- RESULTS
'k1',4,0.6666666666666666
'k2',2,100
'k3',1,NULL
---- TYPES
string,bigint,double
====
---- QUERY
# Test analytic function without partition by or order by on top of a subplan
# that flattens a map.
select id, avg(value) over (), max(key) over() from complextypestbl t, t.int_map
---- RESULTS
1,25.5,'k3'
1,25.5,'k3'
2,25.5,'k3'
2,25.5,'k3'
7,25.5,'k3'
7,25.5,'k3'
8,25.5,'k3'
---- TYPES
bigint,double,string
====
---- QUERY
# Test analytic function with partition by on top of a subplan that flattens an array.
select id, sum(item) over (partition by id) from complextypestbl t, t.int_array
---- RESULTS
1,6
1,6
1,6
2,6
2,6
2,6
2,6
2,6
2,6
8,-1
---- TYPES
bigint,bigint
====
---- QUERY
# Test analytic function with partition by on top of a subplan that flattens a map.
select id, max(key) over(partition by id), sum(value) over (partition by id)
from complextypestbl t, t.int_map
---- RESULTS
1,'k2',101
1,'k2',101
2,'k2',2
2,'k2',2
7,'k3',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
bigint,string,bigint
====
---- QUERY
# Test several analytic functions with incompatible partition by and order by clauses
# on top of a subplan that flattens a map.
select id, key, value, max(key) over(partition by id), row_number()
over (order by value, key) from complextypestbl t, t.int_map
---- RESULTS: VERIFY_IS_EQUAL_SORTED
8,'k1',-1,'k1',1
1,'k1',1,'k2',2
2,'k1',2,'k2',3
1,'k2',100,'k2',4
7,'k1',NULL,'k3',5
2,'k2',NULL,'k2',6
7,'k3',NULL,'k3',7
---- TYPES
bigint,string,int,string,bigint
====
---- QUERY
# Test union of subplans.
select id, "test" key, item from complextypestbl t, t.int_array
union all
select id, key, value from complextypestbl t, t.int_map
---- RESULTS
1,'k1',1
1,'k2',100
1,'test',1
1,'test',2
1,'test',3
2,'k1',2
2,'k2',NULL
2,'test',1
2,'test',2
2,'test',3
2,'test',NULL
2,'test',NULL
2,'test',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
8,'test',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Tesh union distinct of subplans.
select id, "test" key, item from complextypestbl t, t.int_array
union distinct
select id, key, value from complextypestbl t, t.int_map
---- RESULTS
1,'k1',1
1,'k2',100
1,'test',1
1,'test',2
1,'test',3
2,'k1',2
2,'k2',NULL
2,'test',1
2,'test',2
2,'test',3
2,'test',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
8,'test',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Tests that unnested collection-typed slots are set to NULL. Relies on DCHECKs in the
# coordinator to assert that all collection-typed slots in the final output batches
# are set to NULL.
select id, item from complextypestbl t, t.int_array
---- RESULTS
1,1
1,2
1,3
2,1
2,2
2,3
2,NULL
2,NULL
2,NULL
8,-1
---- TYPES
bigint,int
====
---- QUERY
# Tests that unnested collection-typed slots are set to NULL with nested subplans.
select id, a.pos, m.key, m.value from complextypestbl t, t.int_map_array a, a.item m
---- RESULTS
1,0,'k1',1
2,0,'k1',1
2,0,'k3',NULL
8,1,'k1',1
---- TYPES
bigint,bigint,string,int
====
---- QUERY
# Tests that unnested collection-typed slots are set to NULL after an outer-join.
# This tests asserts proper handling of NULL tuples.
# The use of straight_join ensures that the subplan node is placed after the outer join.
select straight_join t1.id, a.item
from complextypestbl t1 left outer join complextypestbl t2 on (t1.id = t2.id + 10)
  inner join t2.int_array a
---- RESULTS
---- TYPES
bigint,int
====
---- QUERY
# Tests that unnested collection-typed slots are set to NULL even if the same
# nested collection is referenced multiple times.
select straight_join t1.id, t2.id, a1.item, a2.item
from complextypestbl t1 inner join t1.int_array a1
  left outer join [shuffle] complextypestbl t2 on t1.id = t2.id
  inner join t1.int_array a2
where t1.id = 1
---- RESULTS
1,1,1,1
1,1,1,2
1,1,1,3
1,1,2,1
1,1,2,2
1,1,2,3
1,1,3,1
1,1,3,2
1,1,3,3
---- TYPES
bigint,bigint,int,int
====
---- QUERY
# Test unnested collection from an outer-joined parent table.
select t1.id, t2.id, a.item from complextypestbl t1
left outer join complextypestbl t2
  on (t1.id = t2.id + 4)
inner join t2.int_array a
---- RESULTS
5,1,1
5,1,2
5,1,3
6,2,1
6,2,2
6,2,3
6,2,NULL
6,2,NULL
6,2,NULL
---- TYPES
bigint,bigint,int
====
---- QUERY
# IMPALA-2326: Test that tuple nullability information is preserved through subplan nodes.
# This test assumes that codegen optimizes the grouping aggregation node based on tuple
# nullability. With codegen enabled, this test will cause a SEGV if the tuple nullability
# information is incorrect at the aggregation node.
select t2.id, count(a.item)
from complextypestbl t1
left outer join complextypestbl t2
  on (t1.id = t2.id + 2)
inner join t1.int_array a
group by t2.id
---- RESULTS
NULL,6
6,1
---- TYPES
bigint,bigint
====
---- QUERY
# Test flattening an array that was first duplicated through a join. This test
# checks that the projection of collection-typed slots does not affect the
# result after duplicating the array.
select straight_join c, id, item from complextypestbl t1, (values('A' c),('B')) v, t1.int_array
---- RESULTS
'A',1,1
'A',1,2
'A',1,3
'A',2,1
'A',2,2
'A',2,3
'A',2,NULL
'A',2,NULL
'A',2,NULL
'A',8,-1
'B',1,1
'B',1,2
'B',1,3
'B',2,1
'B',2,2
'B',2,3
'B',2,NULL
'B',2,NULL
'B',2,NULL
'B',8,-1
---- TYPES
string,bigint,int
====
---- QUERY
# IMPALA-2434: Test union all with a subplan operand. The tables and predicates below
# are carefully chosen so that the subplan is executed as the second operand.
select count(*) from (
  select c_custkey from tpch_nested_parquet.customer c, c.c_orders
  where c_custkey < 1000
  union all
  select a.id from functional.alltypes a, functional.alltypes b
  where a.int_col < 0 and b.int_col < 0 and a.string_col = "" and b.string_col = ""
) v
---- RESULTS
9869
---- TYPES
bigint
====
---- QUERY
# IMPALA-2430: Test empty blocks containing relative table refs.
select c_custkey, v1.cnt, v2.o_orderkey, v3.l_linenumber, v4.cnt
from tpch_nested_parquet.customer c
left outer join
  (select count(*) cnt from c.c_orders
   where false) v1
left outer join
  (select o_orderkey from c.c_orders
   where 20 < 10) v2
left outer join
  (select l_linenumber from c.c_orders.o_lineitems
   where "a" in ("b", "c")) v3
left outer join
  (select count(*) cnt from c.c_orders o left outer join
     (select l_linenumber from o.o_lineitems
      where null) nv) v4
where c_custkey < 10
---- RESULTS
1,0,NULL,NULL,6
2,0,NULL,NULL,7
3,0,NULL,NULL,0
4,0,NULL,NULL,20
5,0,NULL,NULL,4
6,0,NULL,NULL,0
7,0,NULL,NULL,16
8,0,NULL,NULL,13
9,0,NULL,NULL,0
---- TYPES
BIGINT,BIGINT,BIGINT,INT,BIGINT
====
---- QUERY
# IMPALA-2456: Test subplan that contains a hash join with unnest nodes on both the build
# and probe sides of the join. Tests projection of collection-typed slots on both sides
# of the join.
select c_custkey, o_orderkey
from tpch_nested_parquet.customer c
inner join c.c_orders o1
left anti join
  (select o2.o_orderkey x
   from c.c_orders o2, c.c_orders o3) v
on c.c_custkey = v.x
where c_custkey < 2
---- RESULTS
1,454791
1,579908
1,3868359
1,4273923
1,4808192
1,5133509
---- TYPES
bigint,bigint
====
---- QUERY
# IMPALA-2446: Test predicate assignment when outer join has no conjuncts in
# the ON clause and there are predicates in the WHERE clause that can be assigned to
# the join node.
select id, pos from complextypestbl t1 left outer join t1.int_array t2
  where t1.id = t2.pos
---- RESULTS
1,1
2,2
---- TYPES
bigint,bigint
====
---- QUERY
# IMPALA-2446: Test predicate assignment when full outer join has no conjuncts in
# the ON clause and there are predicates in the WHERE clause that can be assigned to
# the join node.
select id, pos from complextypestbl t1 full outer join t1.int_array t2
  where t1.id = t2.pos
---- RESULTS
1,1
2,2
---- TYPES
bigint,bigint
====
---- QUERY
# IMPALA-3065/IMPALA-3062: Test a join on a nested collection whose
# parent tuple is outer joined. This test covers the case where the
# outer joined collection is on the probe side of the outer join.
# To reliably reproduce one of the problematic cases, we need
# > batch_size matches for at least one probe row.
select straight_join count(o.pos) from tpch_nested_parquet.customer c1
right outer join tpch_nested_parquet.customer c2
  on c1.c_custkey % 2 = c2.c_custkey % 2
inner join c1.c_orders o
where c1.c_custkey < 10 and c2.c_custkey < 10000
---- RESULTS
329960
---- TYPES
bigint
====
---- QUERY
# IMPALA-3065/IMPALA-3062: Test a join on a nested collection whose
# parent tuple is outer joined. This test covers the case where the
# outer joined collection is on the build side of the outer join.
select count(a.pos) from complextypestbl t1
full outer join complextypestbl t2
  on t1.id = t2.id
inner join t2.int_array a
---- RESULTS
10
---- TYPES
bigint
====
---- QUERY
# IMPALA-3311: test string data coming out of an agg in a subplan
select id, m from complextypestbl t,
(select min(cast(item as string)) m from t.int_array) v
---- RESULTS
1,'1'
2,'1'
3,'NULL'
4,'NULL'
5,'NULL'
6,'NULL'
7,'NULL'
8,'-1'
---- TYPES
BIGINT,STRING
====
---- QUERY
# IMPALA-8718: Test collection slots are not passed outside the analytic node.
select leftSide.id, rnk
from complextypestbl leftSide
left outer join (
    select t.id, rank() over (order by t.id) as rnk
    from complextypestbl t left outer join t.int_array
) rightSide
on leftSide.id = rightSide.id;
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,1
1,1
1,1
2,4
2,4
2,4
2,4
2,4
2,4
3,10
4,11
5,12
6,13
7,14
8,15
---- TYPES
BIGINT,BIGINT
====