testdata/workloads/functional-planner/queries/PlannerTest/nested-collections.test

# Scan of a nested array of scalars.
select * from functional.allcomplextypes.int_array_col
where item > 10
---- PLAN
PLAN-ROOT SINK
|
00:SCAN HDFS [functional.allcomplextypes.int_array_col]
   partitions=0/0 files=0 size=0B
   predicates: item > 10
   row-size=4B cardinality=0
====
# Scan of a nested map with scalar key and value.
select * from functional.allcomplextypes.int_map_col
where key = 'test' and value < 10
---- PLAN
PLAN-ROOT SINK
|
00:SCAN HDFS [functional.allcomplextypes.int_map_col]
   partitions=0/0 files=0 size=0B
   predicates: value < 10, `key` = 'test'
   row-size=16B cardinality=0
====
# Scan of a deeply nested collection.
select count(f21) from functional.allcomplextypes.complex_nested_struct_col.f2.f12
where key = 'test'
---- PLAN
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
|  output: count(f21)
|  row-size=8B cardinality=1
|
00:SCAN HDFS [functional.allcomplextypes.complex_nested_struct_col.f2.f12]
   partitions=0/0 files=0 size=0B
   predicates: `key` = 'test'
   row-size=20B cardinality=0
====
# Join on two nested collections with structs.
select count(*) from
functional.allcomplextypes.struct_array_col a
inner join functional.allcomplextypes.struct_map_col b
on (a.f1 = b.f1)
where a.f2 = 'test_a' and b.f2 = 'test_b'
---- PLAN
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
|  output: count(*)
|  row-size=8B cardinality=1
|
02:HASH JOIN [INNER JOIN]
|  hash predicates: a.f1 = b.f1
|  runtime filters: RF000 <- b.f1
|  row-size=40B cardinality=0
|
|--01:SCAN HDFS [functional.allcomplextypes.struct_map_col b]
|     partitions=0/0 files=0 size=0B
|     predicates: b.f2 = 'test_b'
|     row-size=20B cardinality=0
|
00:SCAN HDFS [functional.allcomplextypes.struct_array_col a]
   partitions=0/0 files=0 size=0B
   predicates: a.f2 = 'test_a'
   runtime filters: RF000 -> a.f1
   row-size=20B cardinality=0
====
# Test assignment of equi-join conjuncts and enforcement of
# slot equivalences in a bushy join.
select 1 from
  tpch_nested_parquet.region r,
  r.r_nations n,
  tpch_nested_parquet.customer c,
  tpch_nested_parquet.supplier s
where c_nationkey = n_nationkey and s_nationkey = n_nationkey
  and c_comment = s_comment and n_comment = s_comment
---- PLAN
PLAN-ROOT SINK
|
08:HASH JOIN [INNER JOIN]
|  hash predicates: c_nationkey = n_nationkey, s_comment = n_comment
|  runtime filters: RF000 <- n_nationkey, RF001 <- n_comment
|  row-size=189B cardinality=10.16K
|
|--01:SUBPLAN
|  |  row-size=26B cardinality=50
|  |
|  |--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=26B cardinality=10
|  |  |
|  |  |--02:SINGULAR ROW SRC
|  |  |     row-size=12B cardinality=1
|  |  |
|  |  03:UNNEST [r.r_nations n]
|  |     row-size=0B cardinality=10
|  |
|  00:SCAN HDFS [tpch_nested_parquet.region r]
|     HDFS partitions=1/1 files=1 size=3.59KB
|     predicates: !empty(r.r_nations)
|     row-size=12B cardinality=5
|
07:HASH JOIN [INNER JOIN]
|  hash predicates: c.c_nationkey = s.s_nationkey, c_comment = s_comment
|  runtime filters: RF004 <- s.s_nationkey, RF005 <- s_comment
|  row-size=163B cardinality=10.16K
|
|--06:SCAN HDFS [tpch_nested_parquet.supplier s]
|     HDFS partitions=1/1 files=1 size=41.80MB
|     runtime filters: RF000 -> s.s_nationkey, RF001 -> s_comment
|     row-size=77B cardinality=10.00K
|
05:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   runtime filters: RF000 -> c_nationkey, RF001 -> c.c_comment, RF004 -> c.c_nationkey, RF005 -> c_comment
   row-size=87B cardinality=10.16K(filtered from 150.00K)
====
# Test subplans: Cross join of parent and relative ref.
select a.id, b.item from functional.allcomplextypes a cross join a.int_array_col b
where a.id < 10 and b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, !empty(a.int_array_col)
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Left semi join of parent and relative ref without On-clause.
select a.id from functional.allcomplextypes a left semi join a.int_array_col b
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     limit: 1
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, !empty(a.int_array_col)
   row-size=16B cardinality=0
====
# Test subplans: Right semi join of parent and relative ref without On-clause.
select b.item from functional.allcomplextypes a right semi join a.int_array_col b
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=4B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT SEMI JOIN]
|  |  row-size=4B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=12B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: !empty(a.int_array_col)
   predicates on b: b.item % 2 = 0
   row-size=12B cardinality=0
====
# Test subplans: Left anti join of parent and relative ref without On-clause.
select a.id from functional.allcomplextypes a left anti join a.int_array_col b
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     limit: 1
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   row-size=16B cardinality=0
====
# Test subplans: Same as above but with an equivalent inline view on the rhs.
select a.id from functional.allcomplextypes a
left anti join (select * from a.int_array_col) v
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     limit: 1
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   row-size=16B cardinality=0
====
# Test subplans: Right anti join of parent and relative ref without On-clause.
select b.item from functional.allcomplextypes a right anti join a.int_array_col b
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=4B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT ANTI JOIN]
|  |  row-size=4B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=12B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates on b: b.item % 2 = 0
   row-size=12B cardinality=0
====
# Test subplans: Left outer join of parent and relative ref without On-clause.
select a.id, b.item from functional.allcomplextypes a left outer join a.int_array_col b
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   row-size=16B cardinality=0
====
# Test subplans: Same as above but with an equivalent inline view on the rhs.
select a.id, v.item from functional.allcomplextypes a
left outer join (select * from a.int_array_col) v
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     row-size=4B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   row-size=16B cardinality=0
====
# Test subplans: Right outer join of parent and relative ref without On-clause.
select a.id, b.item from functional.allcomplextypes a right outer join a.int_array_col b
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT OUTER JOIN]
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Full outer join of parent and relative ref without On-clause.
select a.id, b.item from functional.allcomplextypes a full outer join a.int_array_col b
where b.item % 2 = 0 and a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [FULL OUTER JOIN]
|  |  predicates: a.id < 10, b.item % 2 = 0
|  |  row-size=20B cardinality=11
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi inner join of parent and relative ref.
select a.id, b.item from functional.allcomplextypes a, a.int_array_col b
where a.id < 10 and b.item % 2 = 0 and a.id < b.item
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [INNER JOIN]
|  |  predicates: a.id < b.item
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, !empty(a.int_array_col)
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi left semi join of parent and relative ref.
select a.id from functional.allcomplextypes a
  left semi join a.int_array_col b on (a.id < b.item and b.item % 2 = 0)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, !empty(a.int_array_col)
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi right semi join of parent and relative ref.
select b.item from functional.allcomplextypes a
  right semi join a.int_array_col b on (a.id < b.item and a.id < 10)
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=4B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT SEMI JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=4B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, !empty(a.int_array_col)
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi left anti join of parent and relative ref.
select a.id from functional.allcomplextypes a
  left anti join a.int_array_col b on (a.id < b.item and b.item % 2 = 0)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi right anti join of parent and relative ref.
select b.item from functional.allcomplextypes a
  right anti join a.int_array_col b on (a.id < b.item and a.id < 10)
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=4B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT ANTI JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=4B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi left outer join of parent and relative ref.
select a.id, b.item from functional.allcomplextypes a
  left outer join a.int_array_col b on (a.id < b.item and b.item % 2 = 0)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi right outer join of parent and relative ref.
select a.id, b.item from functional.allcomplextypes a
  right outer join a.int_array_col b on (a.id < b.item and a.id < 10)
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT OUTER JOIN]
|  |  join predicates: a.id < b.item
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Non-equi full outer join of parent and relative ref.
select a.id, b.item from functional.allcomplextypes a
  full outer join a.int_array_col b on (a.id < b.item and a.id < 10)
where b.item % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [FULL OUTER JOIN]
|  |  join predicates: a.id < 10, a.id < b.item
|  |  predicates: b.item % 2 = 0
|  |  row-size=20B cardinality=11
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates on b: b.item % 2 = 0
   row-size=16B cardinality=0
====
# Test subplans: Inner equi-join of parent and relative ref.
select a.id, b.f1, b.f2 from functional.allcomplextypes a
  inner join a.struct_array_col b
where a.id < 10 and b.f1 % 2 = 0 and b.f1 = a.id and b.f1 < a.year
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=40B cardinality=0
|
|--04:NESTED LOOP JOIN [INNER JOIN]
|  |  join predicates: b.f1 = a.id
|  |  predicates: b.f1 < a.`year`
|  |  row-size=40B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, a.id % 2 = 0, !empty(a.struct_array_col)
   predicates on b: b.f1 < 10, b.f1 % 2 = 0
   row-size=20B cardinality=0
====
# Test subplans: Left-semi equi-join of parent and relative ref.
select a.id from functional.allcomplextypes a
  left semi join a.struct_array_col b
    on (b.f1 % 2 = 0 and b.f1 = a.id and b.f1 < a.year)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, a.id % 2 = 0, !empty(a.struct_array_col)
   predicates on b: b.f1 % 2 = 0, b.f1 < 10
   row-size=20B cardinality=0
====
# Test subplans: Right-semi equi-join of parent and relative ref.
select b.f1, b.f2 from functional.allcomplextypes a
  right semi join a.struct_array_col b
    on (a.id < 10 and b.f1 = a.id and b.f1 < a.year)
where b.f1 % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT SEMI JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, a.id % 2 = 0, !empty(a.struct_array_col)
   predicates on b: b.f1 < 10, b.f1 % 2 = 0
   row-size=20B cardinality=0
====
# Test subplans: Left-anti equi-join of parent and relative ref.
select a.id from functional.allcomplextypes a
  left anti join a.struct_array_col b
    on (b.f1 % 2 = 0 and b.f1 = a.id and b.f1 < a.year)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.f1 % 2 = 0, b.f1 < 10
   row-size=20B cardinality=0
====
# Test subplans: Right-anti equi-join of parent and relative ref.
select b.f1, b.f2 from functional.allcomplextypes a
  right anti join a.struct_array_col b
    on (a.id < 10 and b.f1 = a.id and b.f1 < a.year)
where b.f1 % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT ANTI JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, a.id % 2 = 0
   predicates on b: b.f1 % 2 = 0
   row-size=20B cardinality=0
====
# Test subplans: Left-outer equi-join of parent and relative ref.
select a.id from functional.allcomplextypes a
  left outer join a.struct_array_col b
    on (b.f1 % 2 = 0 and b.f1 = a.id and b.f1 < a.year)
where a.id < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=28B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=28B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.f1 % 2 = 0, b.f1 < 10
   row-size=20B cardinality=0
====
# Test subplans: Right-outer equi-join of parent and relative ref.
select b.f1, b.f2 from functional.allcomplextypes a
  right outer join a.struct_array_col b
    on (a.id < 10 and b.f1 = a.id and b.f1 < a.year)
where b.f1 % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=40B cardinality=0
|
|--04:NESTED LOOP JOIN [LEFT OUTER JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  row-size=40B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10, a.id % 2 = 0
   predicates on b: b.f1 % 2 = 0
   row-size=20B cardinality=0
====
# Test subplans: Full-outer equi-join of parent and relative ref.
select b.f1, b.f2 from functional.allcomplextypes a
  full outer join a.struct_array_col b
    on (b.f1 = a.id and b.f1 < a.year)
where a.id < 10 and b.f1 % 2 = 0
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=40B cardinality=0
|
|--04:NESTED LOOP JOIN [FULL OUTER JOIN]
|  |  join predicates: b.f1 < a.`year`, b.f1 = a.id
|  |  predicates: a.id < 10, b.f1 % 2 = 0
|  |  row-size=40B cardinality=11
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col b]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: a.id < 10
   predicates on b: b.f1 % 2 = 0
   row-size=20B cardinality=0
====
# Test subplans: Test multiple relative refs, disjunctive predicates,
# and correct join ordering within a subplan.
select 1 from functional.allcomplextypes a
  inner join a.int_array_col b on (a.id < 1 or b.item > 2)
  inner join a.int_map_col c on (a.year = c.value)
  left outer join a.struct_array_col d on (a.month < 4 or d.f1 > 5)
  inner join a.struct_map_col e on (e.f1 = a.id and e.f2 = 'test')
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=96B cardinality=0
|
|--10:HASH JOIN [INNER JOIN]
|  |  hash predicates: a.id = e.f1
|  |  row-size=96B cardinality=1
|  |
|  |--06:UNNEST [a.struct_map_col e]
|  |     row-size=0B cardinality=10
|  |
|  09:NESTED LOOP JOIN [LEFT OUTER JOIN]
|  |  join predicates: (a.`month` < 4 OR d.f1 > 5)
|  |  row-size=76B cardinality=1
|  |
|  |--05:UNNEST [a.struct_array_col d]
|  |     row-size=0B cardinality=10
|  |
|  08:NESTED LOOP JOIN [INNER JOIN]
|  |  predicates: (a.id < 1 OR b.item > 2)
|  |  row-size=68B cardinality=1
|  |
|  |--03:UNNEST [a.int_array_col b]
|  |     row-size=0B cardinality=10
|  |
|  07:NESTED LOOP JOIN [INNER JOIN]
|  |  join predicates: c.value = a.`year`
|  |  row-size=64B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=60B cardinality=1
|  |
|  04:UNNEST [a.int_map_col c]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: !empty(a.int_array_col), !empty(a.int_map_col), !empty(a.struct_map_col)
   predicates on e: e.f2 = 'test'
   row-size=60B cardinality=0
====
# Test subplans: Test that subplans are not re-ordered across semi/outer joins.
# The 'alltypes*' tables are purposely placed with increasing size to test
# join inversion.
select 1
from functional.allcomplextypes a
left outer join functional.alltypestiny b on (a.id = b.id)
left anti join functional.alltypessmall c on (b.int_col = c.int_col)
inner join functional.alltypes d on (b.id = d.id)
inner join a.struct_array_col e
where e.f1 < 10
---- PLAN
PLAN-ROOT SINK
|
12:HASH JOIN [INNER JOIN]
|  hash predicates: d.id = b.id
|  runtime filters: RF000 <- b.id
|  row-size=36B cardinality=0
|
|--11:SUBPLAN
|  |  row-size=32B cardinality=0
|  |
|  |--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=32B cardinality=10
|  |  |
|  |  |--07:SINGULAR ROW SRC
|  |  |     row-size=24B cardinality=1
|  |  |
|  |  08:UNNEST [a.struct_array_col e]
|  |     row-size=0B cardinality=10
|  |
|  10:HASH JOIN [RIGHT ANTI JOIN]
|  |  hash predicates: c.int_col = b.int_col
|  |  row-size=24B cardinality=0
|  |
|  |--05:HASH JOIN [RIGHT OUTER JOIN]
|  |  |  hash predicates: b.id = a.id
|  |  |  runtime filters: RF002 <- a.id
|  |  |  row-size=24B cardinality=0
|  |  |
|  |  |--00:SCAN HDFS [functional.allcomplextypes a]
|  |  |     partitions=0/0 files=0 size=0B
|  |  |     predicates: !empty(a.struct_array_col)
|  |  |     predicates on e: e.f1 < 10
|  |  |     row-size=16B cardinality=0
|  |  |
|  |  01:SCAN HDFS [functional.alltypestiny b]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     runtime filters: RF002 -> b.id
|  |     row-size=8B cardinality=8
|  |
|  06:AGGREGATE [FINALIZE]
|  |  group by: c.int_col
|  |  row-size=4B cardinality=10
|  |
|  02:SCAN HDFS [functional.alltypessmall c]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=4B cardinality=100
|
03:SCAN HDFS [functional.alltypes d]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> d.id
   row-size=4B cardinality=7.30K
====
# Test subplans: Same test as above but the relative table ref is wedged in between
# semi/outer joins, and the join types are different.
select 1
from functional.allcomplextypes a
full outer join functional.alltypestiny b on (a.id = b.id)
inner join a.struct_array_col e
right anti join functional.alltypessmall c on (b.int_col = c.int_col and e.f1 < 10)
inner join functional.alltypes d on (b.id = d.id)
---- PLAN
PLAN-ROOT SINK
|
11:HASH JOIN [INNER JOIN]
|  hash predicates: d.id = b.id
|  row-size=8B cardinality=100
|
|--10:HASH JOIN [RIGHT ANTI JOIN]
|  |  hash predicates: b.int_col = c.int_col
|  |  row-size=4B cardinality=100
|  |
|  |--02:SCAN HDFS [functional.alltypessmall c]
|  |     HDFS partitions=4/4 files=4 size=6.32KB
|  |     row-size=4B cardinality=100
|  |
|  09:SUBPLAN
|  |  row-size=32B cardinality=80
|  |
|  |--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=32B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     row-size=24B cardinality=1
|  |  |
|  |  06:UNNEST [a.struct_array_col e]
|  |     row-size=0B cardinality=10
|  |
|  08:HASH JOIN [FULL OUTER JOIN]
|  |  hash predicates: b.id = a.id
|  |  row-size=24B cardinality=8
|  |
|  |--00:SCAN HDFS [functional.allcomplextypes a]
|  |     partitions=0/0 files=0 size=0B
|  |     predicates on e: e.f1 < 10
|  |     row-size=16B cardinality=0
|  |
|  01:SCAN HDFS [functional.alltypestiny b]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=8B cardinality=8
|
03:SCAN HDFS [functional.alltypes d]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=4B cardinality=7.30K
====
# Test subplans: Test joining relative refs with independent table refs.
# The 'alltypes*' tables are purposely placed with increasing size to test
# join inversion.
select *
from functional.allcomplextypes a
inner join a.int_array_col b
left outer join functional.alltypessmall c on (b.item = c.id)
inner join a.int_map_col d
left semi join functional.alltypes e on (d.value = e.id)
where b.item < 10 and c.int_col > 30
---- PLAN
PLAN-ROOT SINK
|
12:HASH JOIN [RIGHT SEMI JOIN]
|  hash predicates: e.id = d.value
|  runtime filters: RF000 <- d.value
|  row-size=145B cardinality=0
|
|--11:SUBPLAN
|  |  row-size=145B cardinality=0
|  |
|  |--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=145B cardinality=10
|  |  |
|  |  |--07:SINGULAR ROW SRC
|  |  |     row-size=129B cardinality=1
|  |  |
|  |  08:UNNEST [a.int_map_col d]
|  |     row-size=0B cardinality=10
|  |
|  10:HASH JOIN [RIGHT OUTER JOIN]
|  |  hash predicates: c.id = b.item
|  |  other predicates: c.int_col > 30
|  |  runtime filters: RF002 <- b.item
|  |  row-size=129B cardinality=0
|  |
|  |--01:SUBPLAN
|  |  |  row-size=40B cardinality=0
|  |  |
|  |  |--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  |  row-size=40B cardinality=10
|  |  |  |
|  |  |  |--02:SINGULAR ROW SRC
|  |  |  |     row-size=36B cardinality=1
|  |  |  |
|  |  |  03:UNNEST [a.int_array_col b]
|  |  |     row-size=0B cardinality=10
|  |  |
|  |  00:SCAN HDFS [functional.allcomplextypes a]
|  |     partitions=0/0 files=0 size=0B
|  |     predicates: !empty(a.int_array_col), !empty(a.int_map_col)
|  |     predicates on b: b.item < 10
|  |     row-size=36B cardinality=0
|  |
|  05:SCAN HDFS [functional.alltypessmall c]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     predicates: c.id < 10, c.int_col > 30
|     runtime filters: RF002 -> c.id
|     row-size=89B cardinality=10
|
06:SCAN HDFS [functional.alltypes e]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> e.id
   row-size=4B cardinality=7.30K
====
# Test subplans: Same as above but with different join types
select *
from functional.allcomplextypes a
inner join a.int_array_col b
full outer join functional.alltypessmall c on (b.item = c.id)
inner join a.int_map_col d
right anti join functional.alltypestiny e on (d.value = e.id)
where b.item < 10 and c.int_col > 30
---- PLAN
PLAN-ROOT SINK
|
12:HASH JOIN [RIGHT ANTI JOIN]
|  hash predicates: d.value = e.id
|  row-size=89B cardinality=8
|
|--06:SCAN HDFS [functional.alltypestiny e]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=89B cardinality=8
|
11:SUBPLAN
|  row-size=133B cardinality=100
|
|--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=133B cardinality=10
|  |
|  |--07:SINGULAR ROW SRC
|  |     row-size=129B cardinality=1
|  |
|  08:UNNEST [a.int_map_col d]
|     row-size=0B cardinality=10
|
10:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: c.id = b.item
|  other predicates: b.item < 10, c.int_col > 30
|  row-size=129B cardinality=10
|
|--01:SUBPLAN
|  |  row-size=40B cardinality=0
|  |
|  |--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=40B cardinality=10
|  |  |
|  |  |--02:SINGULAR ROW SRC
|  |  |     row-size=36B cardinality=1
|  |  |
|  |  03:UNNEST [a.int_array_col b]
|  |     row-size=0B cardinality=10
|  |
|  00:SCAN HDFS [functional.allcomplextypes a]
|     partitions=0/0 files=0 size=0B
|     predicates on b: b.item < 10
|     row-size=36B cardinality=0
|
05:SCAN HDFS [functional.alltypessmall c]
   HDFS partitions=4/4 files=4 size=6.32KB
   predicates: c.int_col > 30
   row-size=89B cardinality=10
====
# Test subplans: Parent scan and aggregate subplan.
select a.id, v.cnt
from functional.allcomplextypes a,
  (select count(*) cnt from a.int_array_col) v
where v.cnt < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=24B cardinality=0
|
|--05:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=24B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  04:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  having: count(*) < 10
|  |  row-size=8B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and grouping aggregate subplan.
select a.id, v.f1, v.cnt
from functional.allcomplextypes a,
  (select f1, count(*) cnt from a.struct_array_col group by f1) v
where v.cnt < 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--05:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  04:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  group by: f1
|  |  having: count(*) < 10
|  |  row-size=16B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Top-n inside subplan.
select a.id, v.f1, v.f2
from functional.allcomplextypes a,
  (select * from a.struct_array_col order by f1 limit 10) v
where v.f2 = 'test'
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=44B cardinality=0
|
|--06:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=44B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  05:SELECT
|  |  predicates: item.f2 = 'test'
|  |  row-size=28B cardinality=1
|  |
|  04:TOP-N [LIMIT=10]
|  |  order by: f1 ASC
|  |  row-size=28B cardinality=10
|  |
|  03:UNNEST [a.struct_array_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and analytic subplan.
select a.id, v.key, v.rnum
from functional.allcomplextypes a,
  (select key, row_number() over (order by key) rnum from a.int_map_col) v
where v.key != 'bad'
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=36B cardinality=0
|
|--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=36B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  06:SELECT
|  |  predicates: key != 'bad'
|  |  row-size=20B cardinality=1
|  |
|  05:ANALYTIC
|  |  functions: row_number()
|  |  order by: key ASC
|  |  window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|  |  row-size=20B cardinality=10
|  |
|  04:SORT
|  |  order by: key ASC
|  |  row-size=12B cardinality=10
|  |
|  03:UNNEST [a.int_map_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and uncorrelated scalar subquery.
select a.id from functional.allcomplextypes a
where id < (select avg(item) from a.int_array_col)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--05:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: id < avg(item)
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  04:AGGREGATE [FINALIZE]
|  |  output: avg(item)
|  |  row-size=8B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and uncorrelated exists subquery.
select a.id from functional.allcomplextypes a
where exists (select item from a.int_array_col where item > 100)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     limit: 1
|     row-size=4B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates on a.int_array_col: item > 100
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and uncorrelated not exists subquery.
select a.id from functional.allcomplextypes a
where not exists (select item from a.int_array_col)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.int_array_col]
|     limit: 1
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and correlated exists subquery
# without an equi-join condition.
select a.id from functional.allcomplextypes a
where exists (select m.key from a.struct_map_col m where a.id < m.f1)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: a.id < m.f1
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.struct_map_col m]
|     row-size=8B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and correlated not exists subquery
# without an equi-join condition.
select a.id from functional.allcomplextypes a
where not exists (select c.f2 from a.struct_array_col c where a.id < c.f1)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT ANTI JOIN]
|  |  join predicates: a.id < c.f1
|  |  row-size=16B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [a.struct_array_col c]
|     row-size=8B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and a correlated IN subquery.
# Note: The Nested Loop Join here is intentional because there is no
# point in doing a hash join with a single build row.
select a.id
from functional.allcomplextypes a
where id in (select b.item from a.int_array_col b where a.year < b.item)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: a.`year` < b.item, b.item = id
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [a.int_array_col b]
|     row-size=4B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=20B cardinality=0
====
# Test subplans: Parent scan and an uncorrelated NOT IN subquery.
# Note: We must use a hash join because the nested-loop join does not support
# the null-aware anti join mode. Also, we cannot invert the join because the
# null-aware right anti join is not supported.
select a.id
from functional.allcomplextypes a
where id not in (select b.item from a.int_array_col b)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=16B cardinality=0
|
|--04:HASH JOIN [NULL AWARE LEFT ANTI JOIN]
|  |  hash predicates: id = b.item
|  |  row-size=16B cardinality=1
|  |
|  |--03:UNNEST [a.int_array_col b]
|  |     row-size=4B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=16B cardinality=1
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=16B cardinality=0
====
# Test subplans: Parent scan and a correlated NOT IN subquery.
# Note: We must use a hash join because the nested-loop join does not support
# the null-aware anti join mode. Also, we cannot invert the join because the
# null-aware right anti join is not supported.
select a.id
from functional.allcomplextypes a
where id not in (select b.item from a.int_array_col b where a.year < b.item)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:HASH JOIN [NULL AWARE LEFT ANTI JOIN]
|  |  hash predicates: id = b.item
|  |  other join predicates: a.`year` < b.item
|  |  row-size=20B cardinality=1
|  |
|  |--03:UNNEST [a.int_array_col b]
|  |     row-size=4B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=20B cardinality=1
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=20B cardinality=0
====
# Test subplans: Bushy plan with multiple subplans.
select a.id, c.item, d.key, d.value, v.cnt
from functional.allcomplextypes a
inner join functional.allcomplextypes b on a.id = b.id
cross join a.int_array_col c
cross join b.int_map_col d
cross join
  (select count(*) cnt
   from a.struct_array_col x inner join b.struct_map_col y
   on x.f1 = y.f1) v
---- PLAN
PLAN-ROOT SINK
|
17:SUBPLAN
|  row-size=84B cardinality=0
|
|--15:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=84B cardinality=1
|  |
|  |--10:SINGULAR ROW SRC
|  |     row-size=76B cardinality=1
|  |
|  14:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  row-size=8B cardinality=1
|  |
|  13:HASH JOIN [INNER JOIN]
|  |  hash predicates: x.f1 = y.f1
|  |  row-size=16B cardinality=10
|  |
|  |--12:UNNEST [b.struct_map_col y]
|  |     row-size=0B cardinality=10
|  |
|  11:UNNEST [a.struct_array_col x]
|     row-size=0B cardinality=10
|
16:HASH JOIN [INNER JOIN]
|  hash predicates: a.id = b.id
|  runtime filters: RF000 <- b.id
|  row-size=76B cardinality=0
|
|--06:SUBPLAN
|  |  row-size=44B cardinality=0
|  |
|  |--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=44B cardinality=10
|  |  |
|  |  |--07:SINGULAR ROW SRC
|  |  |     row-size=28B cardinality=1
|  |  |
|  |  08:UNNEST [b.int_map_col d]
|  |     row-size=0B cardinality=10
|  |
|  05:SCAN HDFS [functional.allcomplextypes b]
|     partitions=0/0 files=0 size=0B
|     predicates: !empty(b.int_map_col)
|     row-size=28B cardinality=0
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=28B cardinality=1
|  |
|  03:UNNEST [a.int_array_col c]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: !empty(a.int_array_col)
   runtime filters: RF000 -> a.id
   row-size=28B cardinality=0
====
# Test subplans: Same as above but with an INNER JOIN between c and d.
select a.id, c.item, d.key, d.value, v.cnt
from functional.allcomplextypes a
inner join functional.allcomplextypes b on a.id = b.id
cross join a.int_array_col c
inner join b.int_map_col d on (c.item = d.value)
cross join
  (select count(*) cnt
   from a.struct_array_col x inner join b.struct_map_col y
   on x.f1 = y.f1) v
where b.id = d.value
---- PLAN
PLAN-ROOT SINK
|
17:SUBPLAN
|  row-size=84B cardinality=0
|
|--15:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=84B cardinality=1
|  |
|  |--10:SINGULAR ROW SRC
|  |     row-size=76B cardinality=1
|  |
|  14:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  row-size=8B cardinality=1
|  |
|  13:HASH JOIN [INNER JOIN]
|  |  hash predicates: x.f1 = y.f1
|  |  row-size=16B cardinality=10
|  |
|  |--12:UNNEST [b.struct_map_col y]
|  |     row-size=0B cardinality=10
|  |
|  11:UNNEST [a.struct_array_col x]
|     row-size=0B cardinality=10
|
16:HASH JOIN [INNER JOIN]
|  hash predicates: a.id = b.id
|  runtime filters: RF000 <- b.id
|  row-size=76B cardinality=0
|
|--06:SUBPLAN
|  |  row-size=44B cardinality=0
|  |
|  |--09:NESTED LOOP JOIN [INNER JOIN]
|  |  |  join predicates: d.value = b.id
|  |  |  row-size=44B cardinality=10
|  |  |
|  |  |--07:SINGULAR ROW SRC
|  |  |     row-size=28B cardinality=1
|  |  |
|  |  08:UNNEST [b.int_map_col d]
|  |     row-size=0B cardinality=10
|  |
|  05:SCAN HDFS [functional.allcomplextypes b]
|     partitions=0/0 files=0 size=0B
|     predicates: !empty(b.int_map_col)
|     row-size=28B cardinality=0
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:NESTED LOOP JOIN [INNER JOIN]
|  |  join predicates: c.item = a.id
|  |  row-size=32B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=28B cardinality=1
|  |
|  03:UNNEST [a.int_array_col c]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: !empty(a.int_array_col)
   runtime filters: RF000 -> a.id
   row-size=28B cardinality=0
====
# Test subplans: Same as above but with a LEFT OUTER JOIN between c and d.
# The outer join needs to be moved after the join between a and b to
# correctly evaluate the On-clause.
select a.id, c.item, d.key, d.value, v.cnt
from functional.allcomplextypes a
inner join functional.allcomplextypes b on a.id = b.id
cross join a.int_array_col c
left outer join b.int_map_col d on (c.item = d.value)
cross join
  (select count(*) cnt
   from a.struct_array_col x inner join b.struct_map_col y
   on x.f1 = y.f1) v
---- PLAN
PLAN-ROOT SINK
|
15:SUBPLAN
|  row-size=84B cardinality=0
|
|--13:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=84B cardinality=1
|  |
|  |--11:AGGREGATE [FINALIZE]
|  |  |  output: count(*)
|  |  |  row-size=8B cardinality=1
|  |  |
|  |  10:HASH JOIN [INNER JOIN]
|  |  |  hash predicates: x.f1 = y.f1
|  |  |  row-size=16B cardinality=10
|  |  |
|  |  |--09:UNNEST [b.struct_map_col y]
|  |  |     row-size=0B cardinality=10
|  |  |
|  |  08:UNNEST [a.struct_array_col x]
|  |     row-size=0B cardinality=10
|  |
|  12:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  join predicates: d.value = c.item
|  |  row-size=76B cardinality=10
|  |
|  |--06:SINGULAR ROW SRC
|  |     row-size=60B cardinality=1
|  |
|  07:UNNEST [b.int_map_col d]
|     row-size=0B cardinality=10
|
14:HASH JOIN [INNER JOIN]
|  hash predicates: a.id = b.id
|  runtime filters: RF000 <- b.id
|  row-size=60B cardinality=0
|
|--05:SCAN HDFS [functional.allcomplextypes b]
|     partitions=0/0 files=0 size=0B
|     row-size=28B cardinality=0
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=28B cardinality=1
|  |
|  03:UNNEST [a.int_array_col c]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   predicates: !empty(a.int_array_col)
   runtime filters: RF000 -> a.id
   row-size=28B cardinality=0
====
# Test subplans: Nested subplans.
select 1
from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems
where c_custkey < 10 and o_orderkey < 5 and l_linenumber < 3
limit 10
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  limit: 10
|  row-size=44B cardinality=10
|
|--08:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=44B cardinality=100
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  04:SUBPLAN
|  |  row-size=24B cardinality=100
|  |
|  |--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=24B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     row-size=20B cardinality=1
|  |  |
|  |  06:UNNEST [o.o_lineitems]
|  |     row-size=0B cardinality=10
|  |
|  03:UNNEST [c.c_orders o]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   predicates: c_custkey < 10, !empty(c.c_orders)
   predicates on o: !empty(o.o_lineitems), o_orderkey < 5
   predicates on o.o_lineitems: l_linenumber < 3
   row-size=20B cardinality=15.00K
====
# Nested subplans.
select 1
from functional.allcomplextypes a
cross join
  (select m1.key from a.map_map_col m1,
   (select m2.key from m1.value m2) v1) v2
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=24B cardinality=0
|
|--08:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=24B cardinality=100
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=12B cardinality=1
|  |
|  04:SUBPLAN
|  |  row-size=12B cardinality=100
|  |
|  |--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=12B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     row-size=12B cardinality=1
|  |  |
|  |  06:UNNEST [m1.value m2]
|  |     row-size=0B cardinality=10
|  |
|  03:UNNEST [a.map_map_col m1]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   row-size=12B cardinality=0
====
# Test subplans: straight_join should place the Subplan in FROM-clause order
# while coalescing adjacent ones.
select /* +straight_join */ a.id, v1.c, v2.a
from functional.allcomplextypes a
inner join functional.allcomplextypes b on (a.id = b.id)
cross join (select count(*) c from a.int_map_col) v1
cross join (select avg(item) a from b.int_array_col) v2
---- PLAN
PLAN-ROOT SINK
|
10:SUBPLAN
|  row-size=48B cardinality=0
|
|--08:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=48B cardinality=1
|  |
|  |--06:AGGREGATE [FINALIZE]
|  |  |  output: avg(item)
|  |  |  row-size=8B cardinality=1
|  |  |
|  |  05:UNNEST [b.int_array_col]
|  |     row-size=0B cardinality=10
|  |
|  07:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=40B cardinality=1
|  |
|  |--04:AGGREGATE [FINALIZE]
|  |  |  output: count(*)
|  |  |  row-size=8B cardinality=1
|  |  |
|  |  03:UNNEST [a.int_map_col]
|  |     row-size=0B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=32B cardinality=1
|
09:HASH JOIN [INNER JOIN]
|  hash predicates: a.id = b.id
|  runtime filters: RF000 <- b.id
|  row-size=32B cardinality=0
|
|--01:SCAN HDFS [functional.allcomplextypes b]
|     partitions=0/0 files=0 size=0B
|     row-size=16B cardinality=0
|
00:SCAN HDFS [functional.allcomplextypes a]
   partitions=0/0 files=0 size=0B
   runtime filters: RF000 -> a.id
   row-size=16B cardinality=0
====
# Test subplans: Enforcement of slot equivalences and removal
# of redundant predicates.
select 1 from tpch_nested_parquet.customer c, c.c_orders o, o.o_lineitems l
# conjuncts form a single equivalence class
where c.c_custkey = o.o_orderkey and c.c_custkey = o.o_shippriority
  and c.c_custkey = l.l_partkey and o.o_shippriority = l.l_linenumber
  and o.o_orderkey = l.l_suppkey and c.c_nationkey = l.l_linenumber
# redundant predicates
  and o.o_orderkey = l.l_partkey and o.o_shippriority = l.l_suppkey
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=66B cardinality=150.00K
|
|--08:NESTED LOOP JOIN [INNER JOIN]
|  |  join predicates: o.o_orderkey = c.c_custkey
|  |  row-size=66B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=22B cardinality=1
|  |
|  04:SUBPLAN
|  |  row-size=44B cardinality=10
|  |
|  |--07:NESTED LOOP JOIN [INNER JOIN]
|  |  |  join predicates: l.l_linenumber = o.o_shippriority
|  |  |  row-size=44B cardinality=10
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     row-size=24B cardinality=1
|  |  |
|  |  06:UNNEST [o.o_lineitems l]
|  |     row-size=0B cardinality=10
|  |
|  03:UNNEST [c.c_orders o]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   predicates: c.c_custkey = c.c_nationkey, !empty(c.c_orders)
   predicates on o: !empty(o.o_lineitems), o.o_orderkey = o.o_shippriority
   predicates on l: l.l_partkey = l.l_linenumber, l.l_partkey = l.l_suppkey
   row-size=22B cardinality=15.00K
====
# Test assignment of nested conjuncts in the parent scan and its limitations.
select *
from functional.allcomplextypes t,
t.int_array_col a,
(select * from t.int_map_col m where m.key = 'test' and m.value != 30 limit 1) v1,
t.complex_nested_struct_col.f2 c,
(select key, f21 as value, coalesce(key, cast(f21 as string)) x from c.f12) v2
where a.item between 10 and 20
  and c.f11 % 2 = 0
  and a.item < v1.value
  and c.f11 = a.item
  and t.id < 200
  and v2.key = 'test2'
  and v2.x = 'test3'
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=116B cardinality=0
|
|--12:NESTED LOOP JOIN [INNER JOIN]
|  |  predicates: a.item < m.value
|  |  row-size=116B cardinality=100
|  |
|  |--04:UNNEST [t.int_map_col m]
|  |     limit: 1
|  |     row-size=16B cardinality=1
|  |
|  11:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=100B cardinality=100
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=48B cardinality=1
|  |
|  10:HASH JOIN [INNER JOIN]
|  |  hash predicates: c.f11 = a.item
|  |  row-size=52B cardinality=100
|  |
|  |--03:UNNEST [t.int_array_col a]
|  |     row-size=0B cardinality=10
|  |
|  06:SUBPLAN
|  |  row-size=48B cardinality=100
|  |
|  |--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=48B cardinality=10
|  |  |
|  |  |--07:SINGULAR ROW SRC
|  |  |     row-size=28B cardinality=1
|  |  |
|  |  08:UNNEST [c.f12]
|  |     predicates: coalesce(`key`, CAST(f21 AS STRING)) = 'test3'
|  |     row-size=20B cardinality=10
|  |
|  05:UNNEST [t.complex_nested_struct_col.f2 c]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes t]
   partitions=0/0 files=0 size=0B
   predicates: t.id < 200, !empty(t.complex_nested_struct_col.f2), !empty(t.int_array_col)
   predicates on a: a.item >= 10, a.item <= 20, a.item % 2 = 0
   predicates on m: m.`key` = 'test', m.value != 30
   predicates on c: c.f11 >= 10, c.f11 <= 20, c.f11 % 2 = 0
   predicates on c.f12: c.f12.key = 'test2'
   row-size=48B cardinality=0
====
# IMPALA-2358: Test join ordering of relative collection table refs inside
# a subquery.
select 1
from tpch_nested_parquet.supplier s
where s.s_suppkey not in
(select ps1.ps_partkey
 from s.s_partsupps ps1
 left outer join s.s_partsupps ps2
   on ps1.ps_supplycost = ps2.ps_supplycost
# This join has an ordering dependency on ps2, and should be in the same subplan as ps2.
 inner join s.s_partsupps ps3
   on ps2.ps_comment = ps3.ps_comment)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=44B cardinality=10.00K
|
|--08:HASH JOIN [NULL AWARE LEFT ANTI JOIN]
|  |  hash predicates: s.s_suppkey = ps1.ps_partkey
|  |  row-size=44B cardinality=1
|  |
|  |--07:HASH JOIN [INNER JOIN]
|  |  |  hash predicates: ps2.ps_comment = ps3.ps_comment
|  |  |  row-size=48B cardinality=10
|  |  |
|  |  |--05:UNNEST [s.s_partsupps ps3]
|  |  |     row-size=0B cardinality=10
|  |  |
|  |  06:HASH JOIN [LEFT OUTER JOIN]
|  |  |  hash predicates: ps1.ps_supplycost = ps2.ps_supplycost
|  |  |  row-size=36B cardinality=10
|  |  |
|  |  |--04:UNNEST [s.s_partsupps ps2]
|  |  |     row-size=0B cardinality=10
|  |  |
|  |  03:UNNEST [s.s_partsupps ps1]
|  |     row-size=0B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=44B cardinality=1
|
00:SCAN HDFS [tpch_nested_parquet.supplier s]
   HDFS partitions=1/1 files=1 size=41.80MB
   row-size=44B cardinality=10.00K
====
# IMPALA-2383: Test join ordering of relative collection ref after an outer join.
select 1 from functional.allcomplextypes t1
left outer join functional.allcomplextypes t2 ON (t1.id = t2.id)
# The subplan for this table ref must come after the outer join of t1 and t2.
inner join t2.int_array_col
---- PLAN
PLAN-ROOT SINK
|
06:SUBPLAN
|  row-size=20B cardinality=0
|
|--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [t2.int_array_col]
|     row-size=0B cardinality=10
|
05:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.id = t2.id
|  row-size=20B cardinality=0
|
|--01:SCAN HDFS [functional.allcomplextypes t2]
|     partitions=0/0 files=0 size=0B
|     row-size=16B cardinality=0
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   row-size=4B cardinality=0
====
# IMPALA-2401: Test correlated inline view with an analytic function and grouping.
select a from functional.allcomplextypes t,
  (select count(*) over(partition by key) a
   from t.int_map_col group by key) v
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--07:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=12B cardinality=1
|  |
|  06:ANALYTIC
|  |  functions: count(*)
|  |  partition by: `key`
|  |  row-size=20B cardinality=10
|  |
|  05:SORT
|  |  order by: `key` ASC NULLS LAST
|  |  row-size=12B cardinality=10
|  |
|  04:AGGREGATE [FINALIZE]
|  |  group by: `key`
|  |  row-size=12B cardinality=10
|  |
|  03:UNNEST [t.int_map_col]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes t]
   partitions=0/0 files=0 size=0B
   row-size=12B cardinality=0
====
# IMPALA-2349: Test planning of nested subplans with straight_join.
select straight_join 1
from functional.allcomplextypes t1
left outer join functional.allcomplextypes t2 on (t1.id = t2.id)
inner join t1.map_map_col m1
inner join m1.value m2
---- PLAN
PLAN-ROOT SINK
|
10:SUBPLAN
|  row-size=32B cardinality=0
|
|--08:SUBPLAN
|  |  row-size=32B cardinality=100
|  |
|  |--06:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=32B cardinality=10
|  |  |
|  |  |--05:UNNEST [m1.value m2]
|  |  |     row-size=0B cardinality=10
|  |  |
|  |  04:SINGULAR ROW SRC
|  |     row-size=32B cardinality=1
|  |
|  07:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=10
|  |
|  |--03:UNNEST [t1.map_map_col m1]
|  |     row-size=0B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=20B cardinality=1
|
09:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.id = t2.id
|  row-size=20B cardinality=0
|
|--01:SCAN HDFS [functional.allcomplextypes t2]
|     partitions=0/0 files=0 size=0B
|     row-size=4B cardinality=0
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   predicates: !empty(t1.map_map_col)
   predicates on m1: !empty(m1.value)
   row-size=16B cardinality=0
====
# IMPALA-2412: Test join ordering in nested subplans.
select 1
from tpch_nested_parquet.customer c
left outer join c.c_orders o1 on o1.pos = c.c_custkey
where c.c_custkey in
(select o2.pos
 from c.c_orders o2
 left outer join o2.o_lineitems l
# This join has an ordering dependency on l because l is outer joined,
# and this join must be in the same subplan as o1 and o2.
 left outer join c.c_orders o3 on o3.pos = o2.pos
 where c.c_custkey = o2.pos)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=52B cardinality=150.00K
|
|--12:HASH JOIN [RIGHT SEMI JOIN]
|  |  hash predicates: o2.pos = c.c_custkey
|  |  row-size=52B cardinality=1
|  |
|  |--11:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  join predicates: o1.pos = c.c_custkey
|  |  |  row-size=52B cardinality=10
|  |  |
|  |  |--02:SINGULAR ROW SRC
|  |  |     row-size=44B cardinality=1
|  |  |
|  |  03:UNNEST [c.c_orders o1]
|  |     row-size=0B cardinality=10
|  |
|  10:HASH JOIN [LEFT OUTER JOIN]
|  |  hash predicates: o2.pos = o3.pos
|  |  row-size=28B cardinality=10
|  |
|  |--09:UNNEST [c.c_orders o3]
|  |     row-size=0B cardinality=10
|  |
|  05:SUBPLAN
|  |  row-size=20B cardinality=10
|  |
|  |--08:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  row-size=20B cardinality=1
|  |  |
|  |  |--06:SINGULAR ROW SRC
|  |  |     row-size=20B cardinality=1
|  |  |
|  |  07:UNNEST [o2.o_lineitems l]
|  |     row-size=0B cardinality=10
|  |
|  04:UNNEST [c.c_orders o2]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=44B cardinality=150.00K
====
# IMPALA-2412: Test join ordering in nested subplans. Same as above
# but with a few inner joins.
select 1
from tpch_nested_parquet.customer c
inner join c.c_orders o1 on o1.pos = c.c_custkey
where c.c_custkey in
(select o2.pos
 from c.c_orders o2
 left outer join o2.o_lineitems l
# This join has an ordering dependency on l because l is outer joined,
# and this join must be in the same subplan as o1 and o2.
 inner join c.c_orders o3 on o3.pos = o2.pos
 where c.c_custkey = o2.pos)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=52B cardinality=150.00K
|
|--12:HASH JOIN [RIGHT SEMI JOIN]
|  |  hash predicates: o2.pos = c.c_custkey
|  |  row-size=52B cardinality=1
|  |
|  |--11:NESTED LOOP JOIN [INNER JOIN]
|  |  |  join predicates: o1.pos = c.c_custkey
|  |  |  row-size=52B cardinality=10
|  |  |
|  |  |--02:SINGULAR ROW SRC
|  |  |     row-size=44B cardinality=1
|  |  |
|  |  03:UNNEST [c.c_orders o1]
|  |     row-size=0B cardinality=10
|  |
|  10:HASH JOIN [INNER JOIN]
|  |  hash predicates: o2.pos = o3.pos
|  |  row-size=28B cardinality=10
|  |
|  |--09:UNNEST [c.c_orders o3]
|  |     row-size=0B cardinality=10
|  |
|  05:SUBPLAN
|  |  row-size=20B cardinality=10
|  |
|  |--08:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  row-size=20B cardinality=1
|  |  |
|  |  |--06:SINGULAR ROW SRC
|  |  |     row-size=20B cardinality=1
|  |  |
|  |  07:UNNEST [o2.o_lineitems l]
|  |     row-size=0B cardinality=10
|  |
|  04:UNNEST [c.c_orders o2]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   predicates: !empty(c.c_orders)
   row-size=44B cardinality=150.00K
====
# IMPALA-2412: Test join ordering in nested subplans.
select 1
from tpch_nested_parquet.customer c
left outer join c.c_orders o1 on o1.pos = c.c_custkey
where c.c_custkey in
(select o2.pos
 from c.c_orders o2
 left outer join c.c_orders o3 on o3.pos = o2.pos
 left outer join o2.o_lineitems l
 where c.c_custkey = o2.pos)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=52B cardinality=150.00K
|
|--12:HASH JOIN [RIGHT SEMI JOIN]
|  |  hash predicates: o2.pos = c.c_custkey
|  |  row-size=52B cardinality=1
|  |
|  |--11:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  join predicates: o1.pos = c.c_custkey
|  |  |  row-size=52B cardinality=10
|  |  |
|  |  |--02:SINGULAR ROW SRC
|  |  |     row-size=44B cardinality=1
|  |  |
|  |  03:UNNEST [c.c_orders o1]
|  |     row-size=0B cardinality=10
|  |
|  10:SUBPLAN
|  |  row-size=28B cardinality=10
|  |
|  |--08:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  row-size=28B cardinality=1
|  |  |
|  |  |--06:SINGULAR ROW SRC
|  |  |     row-size=28B cardinality=1
|  |  |
|  |  07:UNNEST [o2.o_lineitems l]
|  |     row-size=0B cardinality=10
|  |
|  09:HASH JOIN [LEFT OUTER JOIN]
|  |  hash predicates: o2.pos = o3.pos
|  |  row-size=28B cardinality=10
|  |
|  |--05:UNNEST [c.c_orders o3]
|  |     row-size=0B cardinality=10
|  |
|  04:UNNEST [c.c_orders o2]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=44B cardinality=150.00K
====
# 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 straight_join id from functional.allcomplextypes t1 left outer join t1.int_array_col t2
where t1.id = t2.pos and t1.int_struct_col.f1 = 10 and t2.item = 1
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:HASH JOIN [LEFT OUTER JOIN]
|  |  hash predicates: t1.id = t2.pos
|  |  other predicates: t2.item = 1, t1.id = t2.pos
|  |  row-size=32B cardinality=1
|  |
|  |--03:UNNEST [t1.int_array_col t2]
|  |     row-size=0B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=20B cardinality=1
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   predicates: t1.int_struct_col.f1 = 10
   predicates on t2: t2.item = 1
   row-size=20B cardinality=0
====
# IMPALA-2446: Test predicate assignment when right 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 straight_join id from functional.allcomplextypes t1 right outer join t1.int_array_col t2
where t1.id = t2.pos and t1.int_struct_col.f1 = 10 and t2.item = 1
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:HASH JOIN [RIGHT OUTER JOIN]
|  |  hash predicates: t1.id = t2.pos
|  |  other predicates: t1.int_struct_col.f1 = 10, t1.id = t2.pos
|  |  row-size=32B cardinality=10
|  |
|  |--03:UNNEST [t1.int_array_col t2]
|  |     row-size=0B cardinality=10
|  |
|  02:SINGULAR ROW SRC
|     row-size=20B cardinality=1
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   predicates: t1.int_struct_col.f1 = 10
   predicates on t2: t2.item = 1
   row-size=20B cardinality=0
====
# 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 from functional.allcomplextypes t1 full outer join t1.int_array_col t2
where t1.id = t2.pos and t1.int_struct_col.f1 = 10 and t2.item = 1
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=32B cardinality=0
|
|--04:NESTED LOOP JOIN [FULL OUTER JOIN]
|  |  join predicates: t2.pos = t1.id
|  |  predicates: t1.int_struct_col.f1 = 10, t2.item = 1, t1.id = t2.pos
|  |  row-size=32B cardinality=11
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [t1.int_array_col t2]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   predicates: t1.int_struct_col.f1 = 10
   predicates on t2: t2.item = 1
   row-size=20B cardinality=0
====
# IMPALA-2478: Test assignment of where-clause conjuncts with an outer-joined
# # correlated inline view.
select id from functional.allcomplextypes t1 left outer join
  (select pos, item from t1.int_array_col t2) v
 where t1.id > v.pos and v.item = 1
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=28B cardinality=0
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  predicates: item = 1, t1.id > pos
|  |  row-size=28B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=16B cardinality=1
|  |
|  03:UNNEST [t1.int_array_col t2]
|     predicates: item = 1
|     row-size=12B cardinality=10
|
00:SCAN HDFS [functional.allcomplextypes t1]
   partitions=0/0 files=0 size=0B
   predicates on t2: t2.item = 1
   row-size=16B cardinality=0
====
# IMPALA-2445: Test ordering of nested subplans.
select 1 from tpch_nested_parquet.customer c
left outer join c.c_orders o
# Has an ordering dependency on c and o
inner join o.o_lineitems
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=24B cardinality=1.50M
|
|--08:SUBPLAN
|  |  row-size=24B cardinality=10
|  |
|  |--06:NESTED LOOP JOIN [CROSS JOIN]
|  |  |  row-size=24B cardinality=10
|  |  |
|  |  |--04:SINGULAR ROW SRC
|  |  |     row-size=24B cardinality=1
|  |  |
|  |  05:UNNEST [o.o_lineitems]
|  |     row-size=0B cardinality=10
|  |
|  07:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  row-size=24B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=12B cardinality=1
|  |
|  03:UNNEST [c.c_orders o]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=12B cardinality=150.00K
====
# IMPALA-3065/IMPALA-3062: Test correct assignment of !empty() predicates.
# Predicates should not be generated if the parent tuple is outer joined.
select 1 from tpch_nested_parquet.customer c1
inner join c1.c_orders
right outer join tpch_nested_parquet.customer c2
  on c1.c_custkey = c2.c_custkey
---- PLAN
PLAN-ROOT SINK
|
06:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: c1.c_custkey = c2.c_custkey
|  runtime filters: RF000 <- c2.c_custkey
|  row-size=28B cardinality=1.50M
|
|--05:SCAN HDFS [tpch_nested_parquet.customer c2]
|     HDFS partitions=1/1 files=4 size=289.02MB
|     row-size=8B cardinality=150.00K
|
01:SUBPLAN
|  row-size=20B cardinality=1.50M
|
|--04:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=20B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  03:UNNEST [c1.c_orders]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c1]
   HDFS partitions=1/1 files=4 size=289.02MB
   runtime filters: RF000 -> c1.c_custkey
   row-size=20B cardinality=150.00K
====
# IMPALA-3065/IMPALA-3062: Test correct assignment of !empty() predicates.
# Predicates should not be generated if the parent tuple is outer joined.
select 1 from tpch_nested_parquet.customer c1
full outer join tpch_nested_parquet.customer c2
  on c1.c_custkey = c2.c_custkey
inner join c1.c_orders o1
left semi join c2.c_orders o2
---- PLAN
PLAN-ROOT SINK
|
08:SUBPLAN
|  row-size=40B cardinality=3.00M
|
|--06:NESTED LOOP JOIN [LEFT SEMI JOIN]
|  |  row-size=40B cardinality=10
|  |
|  |--04:UNNEST [c2.c_orders o2]
|  |     limit: 1
|  |     row-size=0B cardinality=10
|  |
|  05:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=40B cardinality=10
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=40B cardinality=1
|  |
|  03:UNNEST [c1.c_orders o1]
|     row-size=0B cardinality=10
|
07:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: c1.c_custkey = c2.c_custkey
|  row-size=40B cardinality=300.00K
|
|--01:SCAN HDFS [tpch_nested_parquet.customer c2]
|     HDFS partitions=1/1 files=4 size=289.02MB
|     row-size=20B cardinality=150.00K
|
00:SCAN HDFS [tpch_nested_parquet.customer c1]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=20B cardinality=150.00K
====
# IMPALA-3084: Test correct assignment of NULL checking predicates
# referencing outer-joined nested collections.
select * from tpch_nested_parquet.customer c
left outer join c.c_orders o
where o.o_orderkey is null and o.o_orderstatus <=> o_orderpriority
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=342B cardinality=150.00K
|
|--04:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  predicates: o.o_orderkey IS NULL, o.o_orderstatus IS NOT DISTINCT FROM o_orderpriority
|  |  row-size=342B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=230B cardinality=1
|  |
|  03:UNNEST [c.c_orders o]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=230B cardinality=150.00K
====
# IMPALA-2540: Complex query mixing joins on base tables and nested collections.
select 1
from tpch_nested_parquet.region.r_nations t1
inner join tpch_nested_parquet.customer t2 on t2.c_nationkey = t1.pos
inner join tpch_nested_parquet.region t3 on t3.r_comment = t2.c_address
left join t2.c_orders t4
inner join tpch_nested_parquet.region t5 on t5.r_regionkey = t2.c_custkey
left join t4.item.o_lineitems t6 on t6.item.l_returnflag = t4.item.o_orderpriority
---- PLAN
PLAN-ROOT SINK
|
14:SUBPLAN
|  row-size=183B cardinality=1
|
|--12:SUBPLAN
|  |  row-size=183B cardinality=1
|  |
|  |--10:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  |  join predicates: t6.item.l_returnflag = t4.item.o_orderpriority
|  |  |  row-size=183B cardinality=10
|  |  |
|  |  |--08:SINGULAR ROW SRC
|  |  |     row-size=171B cardinality=1
|  |  |
|  |  09:UNNEST [t4.item.o_lineitems t6]
|  |     row-size=0B cardinality=10
|  |
|  11:NESTED LOOP JOIN [RIGHT OUTER JOIN]
|  |  row-size=171B cardinality=1
|  |
|  |--06:SINGULAR ROW SRC
|  |     row-size=147B cardinality=1
|  |
|  07:UNNEST [t2.c_orders t4]
|     row-size=0B cardinality=10
|
13:HASH JOIN [INNER JOIN]
|  hash predicates: t1.pos = t2.c_nationkey
|  runtime filters: RF000 <- t2.c_nationkey
|  row-size=147B cardinality=1
|
|--05:HASH JOIN [INNER JOIN]
|  |  hash predicates: t3.r_comment = t2.c_address
|  |  runtime filters: RF002 <- t2.c_address
|  |  row-size=139B cardinality=1
|  |
|  |--04:HASH JOIN [INNER JOIN]
|  |  |  hash predicates: t2.c_custkey = t5.r_regionkey
|  |  |  runtime filters: RF004 <- t5.r_regionkey
|  |  |  row-size=61B cardinality=5
|  |  |
|  |  |--03:SCAN HDFS [tpch_nested_parquet.region t5]
|  |  |     HDFS partitions=1/1 files=1 size=3.59KB
|  |  |     row-size=2B cardinality=5
|  |  |
|  |  01:SCAN HDFS [tpch_nested_parquet.customer t2]
|  |     HDFS partitions=1/1 files=4 size=289.02MB
|  |     runtime filters: RF004 -> t2.c_custkey
|  |     row-size=59B cardinality=5(filtered from 150.00K)
|  |
|  02:SCAN HDFS [tpch_nested_parquet.region t3]
|     HDFS partitions=1/1 files=1 size=3.59KB
|     runtime filters: RF002 -> t3.r_comment
|     row-size=78B cardinality=5
|
00:SCAN HDFS [tpch_nested_parquet.region.r_nations t1]
   HDFS partitions=1/1 files=1 size=3.59KB
   runtime filters: RF000 -> t1.pos
   row-size=8B cardinality=50
====
# Add run time scalar subquery check for uncorrelated subqueries
# Create CardinalityCheckNode inside a subplan
select c_custkey
from tpch_nested_parquet.customer c
where c_custkey < (select o_orderkey
                   from c.c_orders
                   where o_orderkey = 6000000)
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=150.00K
|
|--05:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: c_custkey < o_orderkey
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  04:CARDINALITY CHECK
|  |  limit: 1
|  |  row-size=8B cardinality=1
|  |
|  03:UNNEST [c.c_orders]
|     limit: 2
|     row-size=8B cardinality=2
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   predicates on c.c_orders: o_orderkey = 6000000
   row-size=20B cardinality=150.00K
====
# CardinalityCheckNode in subplan in a subplan
select c_custkey
from tpch_nested_parquet.customer c
where c_custkey < (select o_orderkey
                   from c.c_orders co
                   where o_orderkey = (select li.l_linenumber
                                       from co.o_lineitems li))
---- PLAN
PLAN-ROOT SINK
|
01:SUBPLAN
|  row-size=20B cardinality=150.00K
|
|--10:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  join predicates: c_custkey < o_orderkey
|  |  row-size=20B cardinality=1
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  09:CARDINALITY CHECK
|  |  limit: 1
|  |  row-size=20B cardinality=1
|  |
|  04:SUBPLAN
|  |  limit: 2
|  |  row-size=20B cardinality=2
|  |
|  |--08:NESTED LOOP JOIN [RIGHT SEMI JOIN]
|  |  |  join predicates: li.l_linenumber = o_orderkey
|  |  |  row-size=20B cardinality=1
|  |  |
|  |  |--05:SINGULAR ROW SRC
|  |  |     row-size=20B cardinality=1
|  |  |
|  |  07:CARDINALITY CHECK
|  |  |  limit: 1
|  |  |  row-size=4B cardinality=1
|  |  |
|  |  06:UNNEST [co.o_lineitems li]
|  |     limit: 2
|  |     row-size=4B cardinality=2
|  |
|  03:UNNEST [c.c_orders co]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.02MB
   row-size=20B cardinality=150.00K
====
# IMPALA-1270: SEMI JOIN in subplan with distinct added by planner.
# The single node planner needs to correctly set the table ref ids for
# the left branch of the semi join for subplan generation to work.
select a.id, e.key from functional_parquet.complextypestbl a
left semi join functional.alltypessmall c on (a.id = c.int_col)
inner join a.nested_struct.g e
where length(e.key) > 0
---- PLAN
PLAN-ROOT SINK
|
07:SUBPLAN
|  row-size=32B cardinality=44.00K
|
|--05:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=32B cardinality=10
|  |
|  |--03:SINGULAR ROW SRC
|  |     row-size=20B cardinality=1
|  |
|  04:UNNEST [a.nested_struct.g e]
|     row-size=0B cardinality=10
|
06:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: a.id = c.int_col
|  runtime filters: RF000 <- c.int_col
|  row-size=20B cardinality=4.40K
|
|--02:AGGREGATE [FINALIZE]
|  |  group by: c.int_col
|  |  row-size=4B cardinality=10
|  |
|  01:SCAN HDFS [functional.alltypessmall c]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=4B cardinality=100
|
00:SCAN HDFS [functional_parquet.complextypestbl a]
   HDFS partitions=1/1 files=2 size=6.92KB
   predicates: !empty(a.nested_struct.g)
   predicates on e: length(e.`key`) > 0
   runtime filters: RF000 -> a.id
   row-size=20B cardinality=4.40K
====