testdata/workloads/functional-planner/queries/PlannerTest/outer-joins.test

# correct placement of predicates with left outer joins; t2 and t3 are nullable
select *
from functional.testtbl t1 left outer join functional.testtbl t2 on (
    t1.id - 1 = t2.id + 1
# lhs predicate is join predicate
    and t1.zip = 94611
# rhs predicate applied by rhs scan
    and t2.zip = 94104)
  left outer join functional.testtbl t3 on (
    t1.id = t3.id
# predicate on t2 is join predicate, not applied by t2 scan
    and t2.id = 15
# predicate on t1 and t2 is join predicate
    and t1.id - t2.id = 0
# rhs predicate applied by rhs scan
    and t3.zip = 94720
  )
where
# t1 predicate in where clause is applied by scans (t1; also propagated to t3)
t1.id > 0
# t2 predicate in where clause is applied by join and scan
and t2.id is null
# t3 predicate in where clause is applied by join and scan
and t3.id is not null
# join predicate between t1 and t2 applied after t2 join
and t1.zip + t2.zip = 10
# join predicate between t1, t2 and t3 applied after last join
and t1.zip + t2.zip + t3.zip= 20
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.id = t3.id
|  other join predicates: t2.id = 15, t1.id - t2.id = 0
|  other predicates: t3.id IS NOT NULL, t1.zip + t2.zip + t3.zip = 20
|  row-size=72B cardinality=0
|
|--02:SCAN HDFS [functional.testtbl t3]
|     partitions=1/1 files=0 size=0B
|     predicates: t3.id IS NOT NULL, t3.id > 0, t3.zip = 94720
|     row-size=24B cardinality=0
|
03:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.id - 1 = t2.id + 1
|  other join predicates: t1.zip = 94611
|  other predicates: t2.id IS NULL, t1.zip + t2.zip = 10
|  row-size=48B cardinality=0
|
|--01:SCAN HDFS [functional.testtbl t2]
|     partitions=1/1 files=0 size=0B
|     predicates: t2.zip = 94104
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl t1]
   partitions=1/1 files=0 size=0B
   predicates: t1.id > 0
   row-size=24B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [LEFT OUTER JOIN, BROADCAST]
|  hash predicates: t1.id = t3.id
|  other join predicates: t2.id = 15, t1.id - t2.id = 0
|  other predicates: t3.id IS NOT NULL, t1.zip + t2.zip + t3.zip = 20
|  row-size=72B cardinality=0
|
|--06:EXCHANGE [BROADCAST]
|  |
|  02:SCAN HDFS [functional.testtbl t3]
|     partitions=1/1 files=0 size=0B
|     predicates: t3.id IS NOT NULL, t3.id > 0, t3.zip = 94720
|     row-size=24B cardinality=0
|
03:HASH JOIN [LEFT OUTER JOIN, BROADCAST]
|  hash predicates: t1.id - 1 = t2.id + 1
|  other join predicates: t1.zip = 94611
|  other predicates: t2.id IS NULL, t1.zip + t2.zip = 10
|  row-size=48B cardinality=0
|
|--05:EXCHANGE [BROADCAST]
|  |
|  01:SCAN HDFS [functional.testtbl t2]
|     partitions=1/1 files=0 size=0B
|     predicates: t2.zip = 94104
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl t1]
   partitions=1/1 files=0 size=0B
   predicates: t1.id > 0
   row-size=24B cardinality=0
====
# the same thing with subqueries; should produce the same result
select *
from (select * from functional.testtbl a1) t1
  left outer join (select * from functional.testtbl a2) t2 on (
    t1.id - 1 = t2.id + 1 and t1.zip = 94611 and t2.zip = 94104)
  left outer join (select * from functional.testtbl a3) t3 on (
    t1.id = t3.id and t2.id = 15 and t1.id - t2.id = 0 and t3.zip = 94720)
where t1.id > 0 and t2.id is null and t3.id is not null
and t1.zip + t2.zip = 10 and t1.zip + t2.zip + t3.zip= 20
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: a1.id = a3.id
|  other join predicates: a2.id = 15, a1.id - a2.id = 0
|  other predicates: a3.id IS NOT NULL, a1.zip + a2.zip + a3.zip = 20
|  row-size=72B cardinality=0
|
|--02:SCAN HDFS [functional.testtbl a3]
|     partitions=1/1 files=0 size=0B
|     predicates: a3.id IS NOT NULL, a3.id > 0, a3.zip = 94720
|     row-size=24B cardinality=0
|
03:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: a1.id - 1 = a2.id + 1
|  other join predicates: a1.zip = 94611
|  other predicates: a2.id IS NULL, a1.zip + a2.zip = 10
|  row-size=48B cardinality=0
|
|--01:SCAN HDFS [functional.testtbl a2]
|     partitions=1/1 files=0 size=0B
|     predicates: a2.zip = 94104
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl a1]
   partitions=1/1 files=0 size=0B
   predicates: a1.id > 0
   row-size=24B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [LEFT OUTER JOIN, BROADCAST]
|  hash predicates: a1.id = a3.id
|  other join predicates: a2.id = 15, a1.id - a2.id = 0
|  other predicates: a3.id IS NOT NULL, a1.zip + a2.zip + a3.zip = 20
|  row-size=72B cardinality=0
|
|--06:EXCHANGE [BROADCAST]
|  |
|  02:SCAN HDFS [functional.testtbl a3]
|     partitions=1/1 files=0 size=0B
|     predicates: a3.id IS NOT NULL, a3.id > 0, a3.zip = 94720
|     row-size=24B cardinality=0
|
03:HASH JOIN [LEFT OUTER JOIN, BROADCAST]
|  hash predicates: a1.id - 1 = a2.id + 1
|  other join predicates: a1.zip = 94611
|  other predicates: a2.id IS NULL, a1.zip + a2.zip = 10
|  row-size=48B cardinality=0
|
|--05:EXCHANGE [BROADCAST]
|  |
|  01:SCAN HDFS [functional.testtbl a2]
|     partitions=1/1 files=0 size=0B
|     predicates: a2.zip = 94104
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl a1]
   partitions=1/1 files=0 size=0B
   predicates: a1.id > 0
   row-size=24B cardinality=0
====
# correct propagation of scan predicates in OJ On clauses:
# id = 17 must not be applied by the t1 and t3 scans
select *
from functional.testtbl t1
  left outer join functional.testtbl t2 on (
    t1.id = t2.id and t1.id = 17)
  join functional.testtbl t3 on (t1.id = t3.id)
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [INNER JOIN]
|  hash predicates: t1.id = t3.id
|  runtime filters: RF000 <- t3.id
|  row-size=72B cardinality=0
|
|--02:SCAN HDFS [functional.testtbl t3]
|     partitions=1/1 files=0 size=0B
|     row-size=24B cardinality=0
|
03:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.id = t2.id
|  other join predicates: t1.id = 17
|  row-size=48B cardinality=0
|
|--01:SCAN HDFS [functional.testtbl t2]
|     partitions=1/1 files=0 size=0B
|     predicates: t2.id = 17
|     runtime filters: RF000 -> t2.id
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl t1]
   partitions=1/1 files=0 size=0B
   runtime filters: RF000 -> t1.id
   row-size=24B cardinality=0
====
# correct placement of predicates with right outer joins; t1 and t2 are nullable
select *
from functional.testtbl t1 right outer join functional.testtbl t2 on (
    t1.id - 1 = t2.id + 1
# lhs predicate is scan predicate
    and t1.zip = 94611
# rhs predicate is join predicate
    and t2.zip = 94104)
  right outer join functional.testtbl t3 on (
    t1.id = t3.id
# predicate on t2 is scan predicate
    and t2.id = 15
# predicate on t1 and t2 could be applied by previous join
# but the planner doesn't recognize that case and makes it a join predicate
    and t1.id - t2.id = 0
# rhs predicate is join predicate
    and t3.zip = 94720
  )
where
# t1 predicate in where clause is applied by scan and last join
t1.id > 0
# t2 predicate in where clause is applied by last join
and t2.id is null
# t3 predicate in where clause is applied by scan
and t3.id is not null
# join predicate between t1 and t2 applied after last join
and t1.zip + t2.zip = 10
# join predicate between t1, t2 and t3 applied after last join
and t1.zip + t2.zip + t3.zip= 20
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: t1.id = t3.id
|  other join predicates: t3.zip = 94720, t1.id - t2.id = 0
|  other predicates: t2.id IS NULL, t1.id > 0, t1.zip + t2.zip = 10, t1.zip + t2.zip + t3.zip = 20
|  runtime filters: RF000 <- t3.id
|  row-size=72B cardinality=0
|
|--02:SCAN HDFS [functional.testtbl t3]
|     partitions=1/1 files=0 size=0B
|     predicates: t3.id IS NOT NULL
|     row-size=24B cardinality=0
|
03:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: t1.id - 1 = t2.id + 1
|  other join predicates: t1.zip = 94611, t2.zip = 94104
|  runtime filters: RF002 <- t2.id + 1
|  row-size=48B cardinality=0
|
|--01:SCAN HDFS [functional.testtbl t2]
|     partitions=1/1 files=0 size=0B
|     predicates: t2.id = 15
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl t1]
   partitions=1/1 files=0 size=0B
   predicates: t1.id IS NOT NULL, t1.id > 0
   runtime filters: RF000 -> t1.id, RF002 -> t1.id - 1
   row-size=24B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: t1.id = t3.id
|  other join predicates: t3.zip = 94720, t1.id - t2.id = 0
|  other predicates: t2.id IS NULL, t1.id > 0, t1.zip + t2.zip = 10, t1.zip + t2.zip + t3.zip = 20
|  runtime filters: RF000 <- t3.id
|  row-size=72B cardinality=0
|
|--08:EXCHANGE [HASH(t3.id)]
|  |
|  02:SCAN HDFS [functional.testtbl t3]
|     partitions=1/1 files=0 size=0B
|     predicates: t3.id IS NOT NULL
|     row-size=24B cardinality=0
|
07:EXCHANGE [HASH(t1.id)]
|
03:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: t1.id - 1 = t2.id + 1
|  other join predicates: t1.zip = 94611, t2.zip = 94104
|  runtime filters: RF002 <- t2.id + 1
|  row-size=48B cardinality=0
|
|--06:EXCHANGE [HASH(t2.id + 1)]
|  |
|  01:SCAN HDFS [functional.testtbl t2]
|     partitions=1/1 files=0 size=0B
|     predicates: t2.id = 15
|     row-size=24B cardinality=0
|
05:EXCHANGE [HASH(t1.id - 1)]
|
00:SCAN HDFS [functional.testtbl t1]
   partitions=1/1 files=0 size=0B
   predicates: t1.id IS NOT NULL, t1.id > 0
   runtime filters: RF000 -> t1.id, RF002 -> t1.id - 1
   row-size=24B cardinality=0
====
# the same thing with subqueries; should produce the same result
select *
from (select * from functional.testtbl a1) t1 right outer join (select * from functional.testtbl a2) t2 on (
    t1.id - 1 = t2.id + 1 and t1.zip = 94611 and t2.zip = 94104)
  right outer join (select * from functional.testtbl a3) t3 on (
    t1.id = t3.id and t2.id = 15 and t1.id - t2.id = 0 and t3.zip = 94720 )
where t1.id > 0 and t2.id is null and t3.id is not null
and t1.zip + t2.zip = 10 and t1.zip + t2.zip + t3.zip= 20
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: a1.id = a3.id
|  other join predicates: a3.zip = 94720, a1.id - a2.id = 0
|  other predicates: a2.id IS NULL, a1.id > 0, a1.zip + a2.zip = 10, a1.zip + a2.zip + a3.zip = 20
|  runtime filters: RF000 <- a3.id
|  row-size=72B cardinality=0
|
|--02:SCAN HDFS [functional.testtbl a3]
|     partitions=1/1 files=0 size=0B
|     predicates: a3.id IS NOT NULL
|     row-size=24B cardinality=0
|
03:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: a1.id - 1 = a2.id + 1
|  other join predicates: a1.zip = 94611, a2.zip = 94104
|  runtime filters: RF002 <- a2.id + 1
|  row-size=48B cardinality=0
|
|--01:SCAN HDFS [functional.testtbl a2]
|     partitions=1/1 files=0 size=0B
|     predicates: a2.id = 15
|     row-size=24B cardinality=0
|
00:SCAN HDFS [functional.testtbl a1]
   partitions=1/1 files=0 size=0B
   predicates: a1.id IS NOT NULL, a1.id > 0
   runtime filters: RF000 -> a1.id, RF002 -> a1.id - 1
   row-size=24B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: a1.id = a3.id
|  other join predicates: a3.zip = 94720, a1.id - a2.id = 0
|  other predicates: a2.id IS NULL, a1.id > 0, a1.zip + a2.zip = 10, a1.zip + a2.zip + a3.zip = 20
|  runtime filters: RF000 <- a3.id
|  row-size=72B cardinality=0
|
|--08:EXCHANGE [HASH(a3.id)]
|  |
|  02:SCAN HDFS [functional.testtbl a3]
|     partitions=1/1 files=0 size=0B
|     predicates: a3.id IS NOT NULL
|     row-size=24B cardinality=0
|
07:EXCHANGE [HASH(a1.id)]
|
03:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: a1.id - 1 = a2.id + 1
|  other join predicates: a1.zip = 94611, a2.zip = 94104
|  runtime filters: RF002 <- a2.id + 1
|  row-size=48B cardinality=0
|
|--06:EXCHANGE [HASH(a2.id + 1)]
|  |
|  01:SCAN HDFS [functional.testtbl a2]
|     partitions=1/1 files=0 size=0B
|     predicates: a2.id = 15
|     row-size=24B cardinality=0
|
05:EXCHANGE [HASH(a1.id - 1)]
|
00:SCAN HDFS [functional.testtbl a1]
   partitions=1/1 files=0 size=0B
   predicates: a1.id IS NOT NULL, a1.id > 0
   runtime filters: RF000 -> a1.id, RF002 -> a1.id - 1
   row-size=24B cardinality=0
====
# right outer join requires the join op to be partitioned, otherwise non-matches cause
# duplicates
select a.tinyint_col, b.id
from functional.alltypesagg a
right outer join functional.alltypestiny b on (a.tinyint_col = b.id)
where a.tinyint_col is null
---- PLAN
PLAN-ROOT SINK
|
02:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: a.tinyint_col = b.id
|  other predicates: a.tinyint_col IS NULL
|  runtime filters: RF000 <- b.id
|  row-size=5B cardinality=9.78K
|
|--01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
00:SCAN HDFS [functional.alltypesagg a]
   partitions=11/11 files=11 size=814.73KB
   runtime filters: RF000 -> a.tinyint_col
   row-size=1B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
05:EXCHANGE [UNPARTITIONED]
|
02:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: a.tinyint_col = b.id
|  other predicates: a.tinyint_col IS NULL
|  runtime filters: RF000 <- b.id
|  row-size=5B cardinality=9.78K
|
|--04:EXCHANGE [HASH(b.id)]
|  |
|  01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
03:EXCHANGE [HASH(a.tinyint_col)]
|
00:SCAN HDFS [functional.alltypesagg a]
   partitions=11/11 files=11 size=814.73KB
   runtime filters: RF000 -> a.tinyint_col
   row-size=1B cardinality=11.00K
====
# same for full outer joins
select a.tinyint_col, b.id
from functional.alltypesagg a
full outer join functional.alltypestiny b on (a.tinyint_col = b.id)
where a.tinyint_col is null
---- PLAN
PLAN-ROOT SINK
|
02:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.tinyint_col = b.id
|  other predicates: a.tinyint_col IS NULL
|  row-size=5B cardinality=11.01K
|
|--01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
00:SCAN HDFS [functional.alltypesagg a]
   partitions=11/11 files=11 size=814.73KB
   row-size=1B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
05:EXCHANGE [UNPARTITIONED]
|
02:HASH JOIN [FULL OUTER JOIN, PARTITIONED]
|  hash predicates: a.tinyint_col = b.id
|  other predicates: a.tinyint_col IS NULL
|  row-size=5B cardinality=11.01K
|
|--04:EXCHANGE [HASH(b.id)]
|  |
|  01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
03:EXCHANGE [HASH(a.tinyint_col)]
|
00:SCAN HDFS [functional.alltypesagg a]
   partitions=11/11 files=11 size=814.73KB
   row-size=1B cardinality=11.00K
====
# Predicate assignment when query has a full outer join (IMPALA-1371)
select *
from functional.alltypes a full outer join functional.alltypes b
# predicate on a from the on clause is not assigned to the scan node
  on (a.id = b.id and a.int_col < 10 and b.tinyint_col != 5)
# predicate on b from the where clause is assigned to the scan and
# also to the full outer join
where b.bigint_col > 10
---- PLAN
PLAN-ROOT SINK
|
02:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.id = b.id
|  other join predicates: a.int_col < 10, b.tinyint_col != 5
|  other predicates: b.bigint_col > 10
|  row-size=178B cardinality=8.03K
|
|--01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: b.bigint_col > 10
|     row-size=89B cardinality=730
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   row-size=89B cardinality=7.30K
====
# Predicate assignment when query has a full outer followed by an inner join
select *
from functional.alltypes a full outer join functional.alltypes b
  on (a.id = b.id)
inner join functional.alltypes c
  on (a.int_col = c.int_col and a.tinyint_col < 10)
# predicate on b from the where clause is assigned to the
# first full outer join
where b.tinyint_col > 20
---- PLAN
PLAN-ROOT SINK
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: c.int_col = a.int_col
|  other predicates: a.tinyint_col < 10
|  runtime filters: RF000 <- a.int_col
|  row-size=267B cardinality=1.07M
|
|--04:HASH JOIN [FULL OUTER JOIN]
|  |  hash predicates: a.id = b.id
|  |  other predicates: b.tinyint_col > 20
|  |  row-size=178B cardinality=1.46K
|  |
|  |--01:SCAN HDFS [functional.alltypes b]
|  |     partitions=24/24 files=24 size=478.45KB
|  |     predicates: b.tinyint_col > 20
|  |     row-size=89B cardinality=730
|  |
|  00:SCAN HDFS [functional.alltypes a]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: a.tinyint_col < 10
|     row-size=89B cardinality=730
|
02:SCAN HDFS [functional.alltypes c]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> c.int_col
   row-size=89B cardinality=7.30K
====
# Predicate assignment when query has an inner join followed by a full outer join
select *
from functional.alltypes a inner join functional.alltypes b
  on (a.id = b.id)
full outer join functional.alltypes c
  on (a.int_col = c.int_col and a.tinyint_col < b.tinyint_col and a.bigint_col < 10)
# predicate on a is assigned to the scan and the full outer join, predicate on a and b
# is assigned to the full outer join
# TODO Ideally, the predicate on a and b should be assigned to the inner join and then
# re-assigned to the full outer join.
where a.smallint_col = 100 and a.float_col > b.float_col
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: c.int_col = a.int_col
|  other join predicates: a.bigint_col < 10, a.tinyint_col < b.tinyint_col
|  other predicates: a.smallint_col = 100, a.float_col > b.float_col
|  row-size=267B cardinality=532.90K
|
|--03:HASH JOIN [INNER JOIN]
|  |  hash predicates: b.id = a.id
|  |  runtime filters: RF000 <- a.id
|  |  row-size=178B cardinality=730
|  |
|  |--00:SCAN HDFS [functional.alltypes a]
|  |     partitions=24/24 files=24 size=478.45KB
|  |     predicates: a.smallint_col = 100
|  |     row-size=89B cardinality=730
|  |
|  01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF000 -> b.id
|     row-size=89B cardinality=7.30K
|
02:SCAN HDFS [functional.alltypes c]
   partitions=24/24 files=24 size=478.45KB
   row-size=89B cardinality=7.30K
====
# Predicate assignment when query has a sequence of full outer joins interleaved with
# an inner join
select *
from functional.alltypes a full outer join functional.alltypes b
  on (a.id = b.id and a.int_col < 10)
inner join functional.alltypes c
  on (a.tinyint_col = c.tinyint_col and b.int_col > 10)
full outer join functional.alltypes d
# predicate on b from the on clause is assigned to the scan and is also re-applied
# in the full outer join
  on (a.tinyint_col = d.tinyint_col and b.int_col < 20)
# predicate on b from the where clause is assigned to the first full outer join
where a.bool_col = false and a.float_col < b.float_col
---- PLAN
PLAN-ROOT SINK
|
07:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.tinyint_col = d.tinyint_col
|  other join predicates: b.int_col < 20
|  other predicates: a.float_col < b.float_col, a.bool_col = FALSE
|  row-size=356B cardinality=2.33G
|
|--03:SCAN HDFS [functional.alltypes d]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=89B cardinality=7.30K
|
06:HASH JOIN [INNER JOIN]
|  hash predicates: a.tinyint_col = c.tinyint_col
|  other predicates: b.int_col > 10
|  runtime filters: RF000 <- c.tinyint_col
|  row-size=267B cardinality=3.20M
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=89B cardinality=7.30K
|
05:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.id = b.id
|  other join predicates: a.int_col < 10
|  row-size=178B cardinality=4.38K
|
|--01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: b.int_col > 10
|     row-size=89B cardinality=730
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   predicates: a.bool_col = FALSE
   runtime filters: RF000 -> a.tinyint_col
   row-size=89B cardinality=3.65K
====
# Predicate assignment through an inline-view that has a full outer join
select 1
from (
  select a.tinyint_col x, b.tinyint_col y
  from functional.alltypes a
  full outer join functional.alltypes b
    on (a.id = b.id)
  inner join functional.alltypes c
# predicate on a and b from the on clause of the inner join is assigned to
# the first full outer join
    on (b.smallint_col = c.smallint_col and a.int_col < b.int_col and b.id < 10)
  full outer join functional.alltypes d
# predicate on a and b from the on clause of the full outer join is assigned
# to the full outer join
    on (c.id = d.id and a.bigint_col > b.bigint_col)) v
# predicate not specified in the scope of the inline view is assigned to the last
# full outer join of the inline view that materializes the corresponding
# tuple ids
where x != y
---- PLAN
PLAN-ROOT SINK
|
06:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: c.id = d.id
|  other join predicates: a.bigint_col > b.bigint_col
|  other predicates: a.tinyint_col != b.tinyint_col
|  row-size=46B cardinality=5.87M
|
|--03:SCAN HDFS [functional.alltypes d]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=4B cardinality=7.30K
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: b.smallint_col = c.smallint_col
|  other predicates: a.int_col < b.int_col, b.id < 10
|  runtime filters: RF000 <- c.smallint_col
|  row-size=42B cardinality=5.86M
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=6B cardinality=7.30K
|
04:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=36B cardinality=8.03K
|
|--01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: b.id < 10
|     runtime filters: RF000 -> b.smallint_col
|     row-size=19B cardinality=730
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
====
# Predicate assignment when query has an inner join followed by full outer join
select 1
from functional.alltypes a
inner join functional.alltypes b
  on (a.id = b.id)
full outer join functional.alltypes c
  on (b.int_col = c.int_col and c.int_col < 10)
# predicates on a and b from the where clause are assigned to the last full outer join
# that materializes the corresponding tuple ids
where a.bigint_col = b.bigint_col and a.tinyint_col < b.tinyint_col
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: b.int_col = c.int_col
|  other join predicates: c.int_col < 10
|  other predicates: a.bigint_col = b.bigint_col, a.tinyint_col < b.tinyint_col
|  row-size=34B cardinality=5.33M
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=4B cardinality=7.30K
|
03:HASH JOIN [INNER JOIN]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=30B cardinality=7.30K
|
|--00:SCAN HDFS [functional.alltypes a]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=13B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes b]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=17B cardinality=7.30K
====
# Predicate assignment when query has a sequence of inner, outer and full
# outer joins
select 1
from functional.alltypes a
inner join functional.alltypes b
  on (a.id = b.id)
left outer join functional.alltypes c
  on (a.int_col = c.int_col and c.tinyint_col = 10)
full outer join functional.alltypes d
  on (b.string_col = d.string_col and a.tinyint_col < b.tinyint_col)
where a.float_col = b.float_col and b.smallint_col = 1 and d.tinyint_col < 10
---- PLAN
PLAN-ROOT SINK
|
06:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: b.string_col = d.string_col
|  other join predicates: a.tinyint_col < b.tinyint_col
|  other predicates: b.smallint_col = 1, a.float_col = b.float_col, d.tinyint_col < 10
|  row-size=56B cardinality=38.90M
|
|--03:SCAN HDFS [functional.alltypes d]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: d.tinyint_col < 10
|     row-size=14B cardinality=730
|
05:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: c.int_col = a.int_col
|  other join predicates: c.tinyint_col = 10
|  runtime filters: RF000 <- a.int_col
|  row-size=42B cardinality=532.90K
|
|--04:HASH JOIN [INNER JOIN]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF002 <- b.id
|  |  row-size=37B cardinality=730
|  |
|  |--01:SCAN HDFS [functional.alltypes b]
|  |     partitions=24/24 files=24 size=478.45KB
|  |     predicates: b.smallint_col = 1
|  |     row-size=24B cardinality=730
|  |
|  00:SCAN HDFS [functional.alltypes a]
|     partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF002 -> a.id
|     row-size=13B cardinality=7.30K
|
02:SCAN HDFS [functional.alltypes c]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> c.int_col
   row-size=5B cardinality=7.30K
====
# IMPALA-2144: Test correct assignment of Having-clause predicates
# referencing a grouping column coming from an outer-joined table ref.
select b.int_col, sum(b.double_col)
from functional.alltypes a left outer join functional.alltypestiny b
  on (a.id = b.id)
group by a.bool_col, a.int_col, b.bool_col, b.int_col
having a.bool_col is null and a.int_col is not null
  and b.bool_col is null and b.int_col is not null
---- PLAN
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
|  output: sum(b.double_col)
|  group by: a.bool_col, a.int_col, b.bool_col, b.int_col
|  having: b.bool_col IS NULL, b.int_col IS NOT NULL
|  row-size=18B cardinality=8
|
02:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=26B cardinality=730
|
|--01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     predicates: b.int_col IS NOT NULL
|     row-size=17B cardinality=1
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   predicates: a.bool_col IS NULL, a.int_col IS NOT NULL
   row-size=9B cardinality=730
====
# IMPALA-2144: Same as above but with a full outer join.
select b.int_col, sum(b.double_col)
from functional.alltypes a full outer join functional.alltypestiny b
  on (a.id = b.id)
group by a.bool_col, a.int_col, b.bool_col, b.int_col
having a.bool_col is null and a.int_col is not null
  and b.bool_col is null and b.int_col is not null
---- PLAN
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
|  output: sum(b.double_col)
|  group by: a.bool_col, a.int_col, b.bool_col, b.int_col
|  having: a.bool_col IS NULL, a.int_col IS NOT NULL, b.bool_col IS NULL, b.int_col IS NOT NULL
|  row-size=18B cardinality=8
|
02:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=26B cardinality=731
|
|--01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     predicates: b.int_col IS NOT NULL
|     row-size=17B cardinality=1
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   predicates: a.int_col IS NOT NULL
   row-size=9B cardinality=730
====
# IMPALA-2765: Predicate assignment when outer joins have case expressions as predicates
select *
from functional.alltypes a
left outer join functional.alltypes b
  on (a.id = b.id)
where (
  case when a.id > 100 then
    a.timestamp_col
  else
    a.timestamp_col
  end) >= cast('2001-01-01 00:00:00' as timestamp);
---- PLAN
PLAN-ROOT SINK
|
02:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=178B cardinality=730
|
|--00:SCAN HDFS [functional.alltypes a]
|     partitions=24/24 files=24 size=478.45KB
|     predicates: (CASE WHEN a.id > 100 THEN a.timestamp_col ELSE a.timestamp_col END) >= TIMESTAMP '2001-01-01 00:00:00'
|     row-size=89B cardinality=730
|
01:SCAN HDFS [functional.alltypes b]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=89B cardinality=7.30K
====
# IMPALA-3071: Test correct assignment of non-join conjuncts belonging to the On-clause
# of an inner join that follows an outer join.
select 1 from functional.alltypestiny a
left outer join functional.alltypestiny b
  on a.id = b.id
inner join functional.alltypestiny c
  on b.id = c.id and b.int_col < 0 and a.int_col > 10
---- PLAN
PLAN-ROOT SINK
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: c.id = b.id
|  other predicates: b.int_col < 0
|  runtime filters: RF000 <- b.id
|  row-size=20B cardinality=1
|
|--04:HASH JOIN [LEFT OUTER JOIN]
|  |  hash predicates: a.id = b.id
|  |  row-size=16B cardinality=1
|  |
|  |--01:SCAN HDFS [functional.alltypestiny b]
|  |     partitions=4/4 files=4 size=460B
|  |     predicates: b.int_col < 0
|  |     row-size=8B cardinality=1
|  |
|  00:SCAN HDFS [functional.alltypestiny a]
|     partitions=4/4 files=4 size=460B
|     predicates: a.int_col > 10
|     row-size=8B cardinality=1
|
02:SCAN HDFS [functional.alltypestiny c]
   partitions=4/4 files=4 size=460B
   runtime filters: RF000 -> c.id
   row-size=4B cardinality=8
====
# IMPALA-3071: Same as above but with a right outer join.
select 1 from functional.alltypestiny a
right outer join functional.alltypestiny b
  on a.id = b.id
inner join functional.alltypestiny c
  on b.id = c.id and b.int_col < 0 and a.int_col > 10
---- PLAN
PLAN-ROOT SINK
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: c.id = b.id
|  other predicates: a.int_col > 10
|  runtime filters: RF000 <- b.id
|  row-size=20B cardinality=1
|
|--04:HASH JOIN [RIGHT OUTER JOIN]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF002 <- b.id
|  |  row-size=16B cardinality=1
|  |
|  |--01:SCAN HDFS [functional.alltypestiny b]
|  |     partitions=4/4 files=4 size=460B
|  |     predicates: b.int_col < 0
|  |     row-size=8B cardinality=1
|  |
|  00:SCAN HDFS [functional.alltypestiny a]
|     partitions=4/4 files=4 size=460B
|     predicates: a.int_col > 10
|     runtime filters: RF002 -> a.id
|     row-size=8B cardinality=1
|
02:SCAN HDFS [functional.alltypestiny c]
   partitions=4/4 files=4 size=460B
   runtime filters: RF000 -> c.id
   row-size=4B cardinality=8
====
# IMPALA-3071: Same as above but with a full outer join.
select 1 from functional.alltypestiny a
full outer join functional.alltypestiny b
  on a.id = b.id
inner join functional.alltypestiny c
  on b.id = c.id and b.int_col < 0 and a.int_col > 10
---- PLAN
PLAN-ROOT SINK
|
05:HASH JOIN [INNER JOIN]
|  hash predicates: b.id = c.id
|  other predicates: a.int_col > 10, b.int_col < 0
|  runtime filters: RF000 <- c.id
|  row-size=20B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny c]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
04:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=16B cardinality=2
|
|--01:SCAN HDFS [functional.alltypestiny b]
|     partitions=4/4 files=4 size=460B
|     predicates: b.int_col < 0
|     runtime filters: RF000 -> b.id
|     row-size=8B cardinality=1
|
00:SCAN HDFS [functional.alltypestiny a]
   partitions=4/4 files=4 size=460B
   predicates: a.int_col > 10
   row-size=8B cardinality=1
====
# IMPALA-3071: Test that the inner join On-clause predicate is placed
# precisely at the correct outer join (not before or after).
select 1 from functional.alltypestiny a
left outer join functional.alltypestiny b
  on a.id = b.id
left outer join functional.alltypestiny c
  on b.id = c.id
inner join functional.alltypestiny d
  on b.int_col < 0 and a.int_col > 10
full outer join functional.alltypestiny e
  on d.id = e.id
---- PLAN
PLAN-ROOT SINK
|
09:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: e.id = d.id
|  row-size=28B cardinality=9
|
|--08:NESTED LOOP JOIN [INNER JOIN]
|  |  predicates: a.int_col > 10, b.int_col < 0
|  |  row-size=24B cardinality=1
|  |
|  |--07:HASH JOIN [RIGHT OUTER JOIN]
|  |  |  hash predicates: c.id = b.id
|  |  |  runtime filters: RF000 <- b.id
|  |  |  row-size=20B cardinality=1
|  |  |
|  |  |--06:HASH JOIN [LEFT OUTER JOIN]
|  |  |  |  hash predicates: a.id = b.id
|  |  |  |  row-size=16B cardinality=1
|  |  |  |
|  |  |  |--01:SCAN HDFS [functional.alltypestiny b]
|  |  |  |     partitions=4/4 files=4 size=460B
|  |  |  |     predicates: b.int_col < 0
|  |  |  |     row-size=8B cardinality=1
|  |  |  |
|  |  |  00:SCAN HDFS [functional.alltypestiny a]
|  |  |     partitions=4/4 files=4 size=460B
|  |  |     predicates: a.int_col > 10
|  |  |     row-size=8B cardinality=1
|  |  |
|  |  02:SCAN HDFS [functional.alltypestiny c]
|  |     partitions=4/4 files=4 size=460B
|  |     runtime filters: RF000 -> c.id
|  |     row-size=4B cardinality=8
|  |
|  03:SCAN HDFS [functional.alltypestiny d]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
04:SCAN HDFS [functional.alltypestiny e]
   partitions=4/4 files=4 size=460B
   row-size=4B cardinality=8
====
# IMPALA-3125: Test that the On-clause predicates from an outer join are assigned to the
# corresponding outer-join node, even if the predicates do not reference the join rhs.
select a.id aid, b.id bid, a.int_col aint, b.int_col bint
from functional.alltypes a
inner join functional.alltypes b
  on a.int_col = b.int_col
left outer join functional.alltypes c
  on a.id = b.id and b.bigint_col = c.bigint_col
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: b.bigint_col = c.bigint_col
|  other join predicates: a.id = b.id
|  row-size=32B cardinality=3.89G
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=8B cardinality=7.30K
|
03:HASH JOIN [INNER JOIN]
|  hash predicates: b.int_col = a.int_col
|  runtime filters: RF000 <- a.int_col
|  row-size=24B cardinality=5.33M
|
|--00:SCAN HDFS [functional.alltypes a]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=8B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes b]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.int_col
   row-size=16B cardinality=7.30K
====
# IMPALA-3167: Test correct assignment of a WHERE-clause predicate through an inline view
# that has a grouping aggregation and an outer join. The predicate can be assigned at the
# scan on the nullable side of the outer join, but it must also be evaluated after the join.
select v2.id, v2.s
from (select v1.id, sum(bigint_col) s
      from functional.alltypes t1
      left outer join (select t2.int_col, t2.id
                       from functional.alltypessmall t2) v1
      on t1.int_col = v1.int_col
      group by v1.id) v2
where v2.id < 10
---- PLAN
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
|  output: sum(bigint_col)
|  group by: t2.id
|  having: v1.id < 10
|  row-size=12B cardinality=10
|
02:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: t1.int_col = t2.int_col
|  row-size=20B cardinality=7.30K
|
|--01:SCAN HDFS [functional.alltypessmall t2]
|     partitions=4/4 files=4 size=6.32KB
|     predicates: t2.id < 10
|     row-size=8B cardinality=10
|
00:SCAN HDFS [functional.alltypes t1]
   partitions=24/24 files=24 size=478.45KB
   row-size=12B cardinality=7.30K
====
# IMPALA-3126: Test assignment of an inner join On-clause predicate. The predicate
# may not be assigned below the join materializing 'd'.
select 1 from functional.alltypes a
left outer join functional.alltypes b
  on a.id = b.id
right outer join functional.alltypes c
  on b.id = c.id
inner join functional.alltypes d
  on a.int_col = b.int_col
---- PLAN
PLAN-ROOT SINK
|
06:NESTED LOOP JOIN [INNER JOIN]
|  predicates: a.int_col = b.int_col
|  row-size=20B cardinality=7.30K
|
|--03:SCAN HDFS [functional.alltypes d]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=0B cardinality=7.30K
|
05:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: b.id = c.id
|  runtime filters: RF000 <- c.id
|  row-size=20B cardinality=7.30K
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=4B cardinality=7.30K
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=16B cardinality=7.30K
|
|--01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF000 -> b.id
|     row-size=8B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   row-size=8B cardinality=7.30K
====
# IMPALA-3126: Same as above but with a semi join at the end.
select 1 from functional.alltypes a
left outer join functional.alltypes b
  on a.id = b.id
right outer join functional.alltypes c
  on b.id = c.id
left semi join functional.alltypes d
  on a.int_col = b.int_col
---- PLAN
PLAN-ROOT SINK
|
06:NESTED LOOP JOIN [LEFT SEMI JOIN]
|  join predicates: a.int_col = b.int_col
|  row-size=20B cardinality=7.30K
|
|--03:SCAN HDFS [functional.alltypes d]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=0B cardinality=7.30K
|
05:HASH JOIN [RIGHT OUTER JOIN]
|  hash predicates: b.id = c.id
|  runtime filters: RF000 <- c.id
|  row-size=20B cardinality=7.30K
|
|--02:SCAN HDFS [functional.alltypes c]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=4B cardinality=7.30K
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: a.id = b.id
|  row-size=16B cardinality=7.30K
|
|--01:SCAN HDFS [functional.alltypes b]
|     partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF000 -> b.id
|     row-size=8B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes a]
   partitions=24/24 files=24 size=478.45KB
   row-size=8B cardinality=7.30K
====
# IMPALA-5856: Test correct assignment of a join predicate with the following properties:
# - from the On-clause of a left outer join
# - only references the left-hand side tuples (not the right hand side tuple)
# - references full-outer joined tuples; the full outer join appears on the left
select * from functional.alltypes t1
full outer join functional.alltypessmall t2
  on t1.id = t2.id
left outer join functional.alltypestiny t3
  on coalesce(t1.id, t2.id) = t3.id and coalesce(t1.int_col, t2.int_col) = 2
     and t1.bigint_col > 10 and t2.bigint_col > 30
where concat(t1.string_col, t2.string_col) = 'test1' and t3.string_col = 'test2'
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [LEFT OUTER JOIN]
|  hash predicates: coalesce(t1.id, t2.id) = t3.id
|  other join predicates: t1.bigint_col > 10, t2.bigint_col > 30, coalesce(t1.int_col, t2.int_col) = 2
|  other predicates: t3.string_col = 'test2'
|  row-size=267B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypestiny t3]
|     partitions=4/4 files=4 size=460B
|     predicates: t3.string_col = 'test2'
|     row-size=89B cardinality=4
|
03:HASH JOIN [FULL OUTER JOIN]
|  hash predicates: t1.id = t2.id
|  other predicates: concat(t1.string_col, t2.string_col) = 'test1'
|  row-size=178B cardinality=7.40K
|
|--01:SCAN HDFS [functional.alltypessmall t2]
|     partitions=4/4 files=4 size=6.32KB
|     row-size=89B cardinality=100
|
00:SCAN HDFS [functional.alltypes t1]
   partitions=24/24 files=24 size=478.45KB
   row-size=89B cardinality=7.30K
====
# IMPALA-6123: Test the partition compatibility check with consecutive outer joins.
# In this query the output partition of the right outer join should be the rhs partition
# t3.id, which is not compatible with the aggregation partition t2.id. There should be
# an exchange node in the aggregation.
# The first left outer join is used to ensure 't2' is made nullable before the fragment
# executing the right outer join.
select /* +straight_join */ t2.id, count(*)
from functional.alltypes t1
left outer join /* +shuffle */ functional.alltypessmall t2
  on t1.int_col = t2.int_col
right outer join /* +shuffle */ functional.alltypestiny t3
  on t2.id = t3.id
group by t2.id
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
12:EXCHANGE [UNPARTITIONED]
|
11:AGGREGATE [FINALIZE]
|  output: count:merge(*)
|  group by: t2.id
|  row-size=12B cardinality=99
|
10:EXCHANGE [HASH(t2.id)]
|
05:AGGREGATE [STREAMING]
|  output: count(*)
|  group by: t2.id
|  row-size=12B cardinality=99
|
04:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: t2.id = t3.id
|  runtime filters: RF000 <- t3.id
|  row-size=16B cardinality=5.90K
|
|--09:EXCHANGE [HASH(t3.id)]
|  |
|  02:SCAN HDFS [functional.alltypestiny t3]
|     partitions=4/4 files=4 size=460B
|     row-size=4B cardinality=8
|
08:EXCHANGE [HASH(t2.id)]
|
03:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]
|  hash predicates: t1.int_col = t2.int_col
|  row-size=12B cardinality=73.00K
|
|--07:EXCHANGE [HASH(t2.int_col)]
|  |
|  01:SCAN HDFS [functional.alltypessmall t2]
|     partitions=4/4 files=4 size=6.32KB
|     runtime filters: RF000 -> t2.id
|     row-size=8B cardinality=100
|
06:EXCHANGE [HASH(t1.int_col)]
|
00:SCAN HDFS [functional.alltypes t1]
   partitions=24/24 files=24 size=478.45KB
   row-size=4B cardinality=7.30K
====
# IMPALA-6123: Test the partition compatibility check with consecutive outer joins.
# In this query the output partition of the full outer join should be random. There should
# be an exchange node in the aggregation.
# The first right outer join is used to ensure 't1' is made nullable before the fragment
# executing the full outer join.
select /* +straight_join */ t1.int_col, count(*)
from functional.alltypes t1
right outer join /* +shuffle */ functional.alltypes t2
  on t1.smallint_col = t2.smallint_col
full outer join /* +shuffle */ functional.alltypes t3
  on t1.int_col = t3.int_col
group by t1.int_col
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
12:EXCHANGE [UNPARTITIONED]
|
11:AGGREGATE [FINALIZE]
|  output: count:merge(*)
|  group by: t1.int_col
|  row-size=12B cardinality=10
|
10:EXCHANGE [HASH(t1.int_col)]
|
05:AGGREGATE [STREAMING]
|  output: count(*)
|  group by: t1.int_col
|  row-size=12B cardinality=10
|
04:HASH JOIN [FULL OUTER JOIN, PARTITIONED]
|  hash predicates: t1.int_col = t3.int_col
|  row-size=12B cardinality=3.89G
|
|--09:EXCHANGE [HASH(t3.int_col)]
|  |
|  02:SCAN HDFS [functional.alltypes t3]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=4B cardinality=7.30K
|
08:EXCHANGE [HASH(t1.int_col)]
|
03:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
|  hash predicates: t1.smallint_col = t2.smallint_col
|  runtime filters: RF000 <- t2.smallint_col
|  row-size=8B cardinality=5.33M
|
|--07:EXCHANGE [HASH(t2.smallint_col)]
|  |
|  01:SCAN HDFS [functional.alltypes t2]
|     partitions=24/24 files=24 size=478.45KB
|     row-size=2B cardinality=7.30K
|
06:EXCHANGE [HASH(t1.smallint_col)]
|
00:SCAN HDFS [functional.alltypes t1]
   partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> t1.smallint_col
   row-size=6B cardinality=7.30K
====