# intersect
select year, month from functional.alltypes
  intersect
select year, month from functional.alltypes where year=2009
---- PLAN
PLAN-ROOT SINK
|
04:AGGREGATE [FINALIZE]
|  group by: `year`, `month`
|  row-size=8B cardinality=24
|
03:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM functional.alltypes.month, `year` IS NOT DISTINCT FROM functional.alltypes.year
|  runtime filters: RF000 <- functional.alltypes.month, RF001 <- functional.alltypes.year
|  row-size=8B cardinality=7.30K
|
|--02:AGGREGATE [FINALIZE]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  01:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009
|     HDFS partitions=12/24 files=12 size=238.68KB
|     row-size=8B cardinality=3.65K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> `month`, RF001 -> `year`
   row-size=8B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
09:AGGREGATE [FINALIZE]
|  group by: $a$1.year, $a$1.month
|  row-size=8B cardinality=24
|
08:EXCHANGE [HASH($a$1.year,$a$1.month)]
|
04:AGGREGATE [STREAMING]
|  group by: `year`, `month`
|  row-size=8B cardinality=24
|
03:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: `month` IS NOT DISTINCT FROM functional.alltypes.month, `year` IS NOT DISTINCT FROM functional.alltypes.year
|  runtime filters: RF000 <- functional.alltypes.month, RF001 <- functional.alltypes.year
|  row-size=8B cardinality=7.30K
|
|--07:EXCHANGE [BROADCAST]
|  |
|  06:AGGREGATE [FINALIZE]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  05:EXCHANGE [HASH(functional.alltypes.year,functional.alltypes.month)]
|  |
|  02:AGGREGATE [STREAMING]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  01:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009
|     HDFS partitions=12/24 files=12 size=238.68KB
|     row-size=8B cardinality=3.65K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> `month`, RF001 -> `year`
   row-size=8B cardinality=7.30K
====
# intersect unnesting
select id, year, month from functional.alltypestiny where year=2009 and month=1
intersect
(select id, year, month from functional.alltypestiny where year=2009 and month=1
intersect
 (select id, year, month from functional.alltypestiny where year=2009 and month=2
  intersect
 select id, year, month from functional.alltypestiny where year=2009 and month=2))
---- PLAN
PLAN-ROOT SINK
|
09:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
08:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  runtime filters: RF000 <- $a$1.month, RF001 <- $a$1.year, RF002 <- $a$1.id
|  row-size=12B cardinality=2
|
|--07:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  06:HASH JOIN [LEFT SEMI JOIN]
|  |  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  |  runtime filters: RF006 <- $a$1.month, RF007 <- $a$1.year, RF008 <- $a$1.id
|  |  row-size=12B cardinality=2
|  |
|  |--05:AGGREGATE [FINALIZE]
|  |  |  group by: id, `year`, `month`
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  04:HASH JOIN [LEFT SEMI JOIN]
|  |  |  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  |  |  runtime filters: RF012 <- `month`, RF013 <- `year`, RF014 <- id
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  |--03:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=12B cardinality=2
|  |  |
|  |  02:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     runtime filters: RF012 -> `month`, RF013 -> `year`, RF014 -> id
|  |     row-size=12B cardinality=2
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     runtime filters: RF006 -> `month`, RF007 -> `year`, RF008 -> id
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> `month`, RF001 -> `year`, RF002 -> id
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
19:EXCHANGE [UNPARTITIONED]
|
18:AGGREGATE [FINALIZE]
|  group by: $a$1.id, $a$1.year, $a$1.month
|  row-size=12B cardinality=2
|
17:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|
09:AGGREGATE [STREAMING]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
08:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  runtime filters: RF000 <- $a$1.month, RF001 <- $a$1.year, RF002 <- $a$1.id
|  row-size=12B cardinality=2
|
|--16:EXCHANGE [BROADCAST]
|  |
|  15:AGGREGATE [FINALIZE]
|  |  group by: $a$1.id, $a$1.year, $a$1.month
|  |  row-size=12B cardinality=2
|  |
|  14:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|  |
|  07:AGGREGATE [STREAMING]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  06:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  |  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  |  runtime filters: RF006 <- $a$1.month, RF007 <- $a$1.year, RF008 <- $a$1.id
|  |  row-size=12B cardinality=2
|  |
|  |--13:EXCHANGE [BROADCAST]
|  |  |
|  |  12:AGGREGATE [FINALIZE]
|  |  |  group by: $a$1.id, $a$1.year, $a$1.month
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  11:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|  |  |
|  |  05:AGGREGATE [STREAMING]
|  |  |  group by: id, `year`, `month`
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  04:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  |  |  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  |  |  runtime filters: RF012 <- `month`, RF013 <- `year`, RF014 <- id
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  |--10:EXCHANGE [BROADCAST]
|  |  |  |
|  |  |  03:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=12B cardinality=2
|  |  |
|  |  02:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     runtime filters: RF012 -> `month`, RF013 -> `year`, RF014 -> id
|  |     row-size=12B cardinality=2
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     runtime filters: RF006 -> `month`, RF007 -> `year`, RF008 -> id
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> `month`, RF001 -> `year`, RF002 -> id
   row-size=12B cardinality=2
====
# intersect uses inner join if distinct on both sides
select distinct id, year, month from functional.alltypestiny where year=2009 and month=1
intersect
select distinct id, year, month from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [INNER JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- `month`, RF001 <- `year`, RF002 <- id
|  row-size=24B cardinality=2
|
|--03:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
01:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.month, RF001 -> functional.alltypestiny.year, RF002 -> functional.alltypestiny.id
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [INNER JOIN, PARTITIONED]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- `month`, RF001 <- `year`, RF002 <- id
|  row-size=24B cardinality=2
|
|--09:EXCHANGE [HASH(id,`year`,`month`)]
|  |
|  08:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  07:EXCHANGE [HASH(id,`year`,`month`)]
|  |
|  03:AGGREGATE [STREAMING]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
06:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
05:EXCHANGE [HASH(id,`year`,`month`)]
|
01:AGGREGATE [STREAMING]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.month, RF001 -> functional.alltypestiny.year, RF002 -> functional.alltypestiny.id
   row-size=12B cardinality=2
====
# except
select id, year, month from functional.alltypestiny where year=2009 and month=1
except
select id, year, month from functional.alltypestiny where year=2009 and month=1
except
select id, year, month from functional.alltypestiny where year=2009 and month=2
---- PLAN
PLAN-ROOT SINK
|
05:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
04:HASH JOIN [LEFT ANTI JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  row-size=12B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
03:HASH JOIN [LEFT ANTI JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  row-size=12B cardinality=2
|
|--01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
09:AGGREGATE [FINALIZE]
|  group by: $a$1.id, $a$1.year, $a$1.month
|  row-size=12B cardinality=2
|
08:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|
05:AGGREGATE [STREAMING]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
04:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  row-size=12B cardinality=2
|
|--07:EXCHANGE [BROADCAST]
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
03:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  row-size=12B cardinality=2
|
|--06:EXCHANGE [BROADCAST]
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=12B cardinality=2
====
# nested except, shouldn't be unnested, if it had been the results would be incorrect
# note the difference from the plan above with has all the operands at the same level
select id, year, month from functional.alltypestiny where year=2009 and month=1
except
(select id, year, month from functional.alltypestiny where year=2009 and month=1
except
select id, year, month from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
06:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
05:HASH JOIN [LEFT ANTI JOIN]
|  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  row-size=12B cardinality=2
|
|--04:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  03:HASH JOIN [LEFT ANTI JOIN]
|  |  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  |  row-size=12B cardinality=2
|  |
|  |--02:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=12B cardinality=2
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
13:EXCHANGE [UNPARTITIONED]
|
12:AGGREGATE [FINALIZE]
|  group by: $a$1.id, $a$1.year, $a$1.month
|  row-size=12B cardinality=2
|
11:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|
06:AGGREGATE [STREAMING]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
05:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  hash predicates: `month` IS NOT DISTINCT FROM $a$1.month, `year` IS NOT DISTINCT FROM $a$1.year, id IS NOT DISTINCT FROM $a$1.id
|  row-size=12B cardinality=2
|
|--10:EXCHANGE [BROADCAST]
|  |
|  09:AGGREGATE [FINALIZE]
|  |  group by: $a$1.id, $a$1.year, $a$1.month
|  |  row-size=12B cardinality=2
|  |
|  08:EXCHANGE [HASH($a$1.id,$a$1.year,$a$1.month)]
|  |
|  04:AGGREGATE [STREAMING]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  03:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  |  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  |  row-size=12B cardinality=2
|  |
|  |--07:EXCHANGE [BROADCAST]
|  |  |
|  |  02:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=12B cardinality=2
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=12B cardinality=2
====
# mixed intersect / except with unions first
select * from functional.alltypestiny where year=2009 and month=1 limit 1
union all
select * from functional.alltypestiny where year=2009 and month=1 order by int_col limit 1
union distinct
select * from functional.alltypestiny where year=2009 and month=2 limit 1
except
select * from functional.alltypestiny where year=2009 and month=1 order by int_col limit 1
intersect
select * from functional.alltypestiny where year=2009 and month=2 limit 1
---- PLAN
PLAN-ROOT SINK
|
11:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=1
|
10:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: bigint_col IS NOT DISTINCT FROM functional.alltypestiny.bigint_col, bool_col IS NOT DISTINCT FROM functional.alltypestiny.bool_col, double_col IS NOT DISTINCT FROM functional.alltypestiny.double_col, float_col IS NOT DISTINCT FROM functional.alltypestiny.float_col, id IS NOT DISTINCT FROM functional.alltypestiny.id, int_col IS NOT DISTINCT FROM functional.alltypestiny.int_col, month IS NOT DISTINCT FROM functional.alltypestiny.month, smallint_col IS NOT DISTINCT FROM functional.alltypestiny.smallint_col, timestamp_col IS NOT DISTINCT FROM functional.alltypestiny.timestamp_col, tinyint_col IS NOT DISTINCT FROM functional.alltypestiny.tinyint_col, year IS NOT DISTINCT FROM functional.alltypestiny.year, string_col IS NOT DISTINCT FROM functional.alltypestiny.string_col, date_string_col IS NOT DISTINCT FROM functional.alltypestiny.date_string_col
|  runtime filters: RF000 <- functional.alltypestiny.bigint_col, RF001 <- functional.alltypestiny.bool_col, RF002 <- functional.alltypestiny.double_col, RF003 <- functional.alltypestiny.float_col, RF004 <- functional.alltypestiny.id, RF005 <- functional.alltypestiny.int_col, RF006 <- functional.alltypestiny.month, RF007 <- functional.alltypestiny.smallint_col, RF008 <- functional.alltypestiny.timestamp_col, RF010 <- functional.alltypestiny.tinyint_col
|  row-size=89B cardinality=1
|
|--08:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
09:HASH JOIN [LEFT ANTI JOIN]
|  hash predicates: bigint_col IS NOT DISTINCT FROM bigint_col, bool_col IS NOT DISTINCT FROM bool_col, double_col IS NOT DISTINCT FROM double_col, float_col IS NOT DISTINCT FROM float_col, id IS NOT DISTINCT FROM id, int_col IS NOT DISTINCT FROM int_col, month IS NOT DISTINCT FROM month, smallint_col IS NOT DISTINCT FROM smallint_col, timestamp_col IS NOT DISTINCT FROM timestamp_col, tinyint_col IS NOT DISTINCT FROM tinyint_col, year IS NOT DISTINCT FROM year, string_col IS NOT DISTINCT FROM string_col, date_string_col IS NOT DISTINCT FROM date_string_col
|  row-size=89B cardinality=3
|
|--07:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  06:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=3
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=3
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
|     limit: 1
|     row-size=89B cardinality=1
|
|--03:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
   limit: 1
   row-size=89B cardinality=1
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
11:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=1
|
10:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: bigint_col IS NOT DISTINCT FROM functional.alltypestiny.bigint_col, bool_col IS NOT DISTINCT FROM functional.alltypestiny.bool_col, double_col IS NOT DISTINCT FROM functional.alltypestiny.double_col, float_col IS NOT DISTINCT FROM functional.alltypestiny.float_col, id IS NOT DISTINCT FROM functional.alltypestiny.id, int_col IS NOT DISTINCT FROM functional.alltypestiny.int_col, month IS NOT DISTINCT FROM functional.alltypestiny.month, smallint_col IS NOT DISTINCT FROM functional.alltypestiny.smallint_col, timestamp_col IS NOT DISTINCT FROM functional.alltypestiny.timestamp_col, tinyint_col IS NOT DISTINCT FROM functional.alltypestiny.tinyint_col, year IS NOT DISTINCT FROM functional.alltypestiny.year, string_col IS NOT DISTINCT FROM functional.alltypestiny.string_col, date_string_col IS NOT DISTINCT FROM functional.alltypestiny.date_string_col
|  runtime filters: RF000 <- functional.alltypestiny.bigint_col, RF001 <- functional.alltypestiny.bool_col, RF002 <- functional.alltypestiny.double_col, RF003 <- functional.alltypestiny.float_col, RF004 <- functional.alltypestiny.id, RF005 <- functional.alltypestiny.int_col, RF006 <- functional.alltypestiny.month, RF007 <- functional.alltypestiny.smallint_col, RF008 <- functional.alltypestiny.timestamp_col, RF010 <- functional.alltypestiny.tinyint_col
|  row-size=89B cardinality=1
|
|--18:EXCHANGE [UNPARTITIONED]
|  |
|  17:EXCHANGE [UNPARTITIONED]
|  |  limit: 1
|  |
|  08:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
09:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  hash predicates: bigint_col IS NOT DISTINCT FROM bigint_col, bool_col IS NOT DISTINCT FROM bool_col, double_col IS NOT DISTINCT FROM double_col, float_col IS NOT DISTINCT FROM float_col, id IS NOT DISTINCT FROM id, int_col IS NOT DISTINCT FROM int_col, month IS NOT DISTINCT FROM month, smallint_col IS NOT DISTINCT FROM smallint_col, timestamp_col IS NOT DISTINCT FROM timestamp_col, tinyint_col IS NOT DISTINCT FROM tinyint_col, year IS NOT DISTINCT FROM year, string_col IS NOT DISTINCT FROM string_col, date_string_col IS NOT DISTINCT FROM date_string_col
|  row-size=89B cardinality=3
|
|--16:EXCHANGE [UNPARTITIONED]
|  |
|  15:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: int_col ASC
|  |  limit: 1
|  |
|  07:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  06:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=3
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=3
|
|--14:EXCHANGE [UNPARTITIONED]
|  |  limit: 1
|  |
|  04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
|     limit: 1
|     row-size=89B cardinality=1
|
|--13:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: int_col ASC
|  |  limit: 1
|  |
|  03:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
12:EXCHANGE [UNPARTITIONED]
|  limit: 1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
   limit: 1
   row-size=89B cardinality=1
====
# mixed intersect / except with unions second
select * from functional.alltypestiny where year=2009 and month=2 limit 1
except
select * from functional.alltypestiny where year=2009 and month=1 order by int_col limit 1
intersect
select * from functional.alltypestiny where year=2009 and month=2 limit 1
union all
select * from functional.alltypestiny where year=2009 and month=1 limit 1
union distinct
select * from functional.alltypestiny where year=2009 and month=1 order by int_col limit 1
---- PLAN
PLAN-ROOT SINK
|
11:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=3
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=3
|
|--10:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  09:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--08:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
07:AGGREGATE [FINALIZE]
|  group by: functional.alltypestiny.id, functional.alltypestiny.bool_col, functional.alltypestiny.tinyint_col, functional.alltypestiny.smallint_col, functional.alltypestiny.int_col, functional.alltypestiny.bigint_col, functional.alltypestiny.float_col, functional.alltypestiny.double_col, functional.alltypestiny.date_string_col, functional.alltypestiny.string_col, functional.alltypestiny.timestamp_col, functional.alltypestiny.year, functional.alltypestiny.month
|  row-size=89B cardinality=1
|
06:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: functional.alltypestiny.bigint_col IS NOT DISTINCT FROM functional.alltypestiny.bigint_col, functional.alltypestiny.bool_col IS NOT DISTINCT FROM functional.alltypestiny.bool_col, functional.alltypestiny.double_col IS NOT DISTINCT FROM functional.alltypestiny.double_col, functional.alltypestiny.float_col IS NOT DISTINCT FROM functional.alltypestiny.float_col, functional.alltypestiny.id IS NOT DISTINCT FROM functional.alltypestiny.id, functional.alltypestiny.int_col IS NOT DISTINCT FROM functional.alltypestiny.int_col, functional.alltypestiny.month IS NOT DISTINCT FROM functional.alltypestiny.month, functional.alltypestiny.smallint_col IS NOT DISTINCT FROM functional.alltypestiny.smallint_col, functional.alltypestiny.timestamp_col IS NOT DISTINCT FROM functional.alltypestiny.timestamp_col, functional.alltypestiny.tinyint_col IS NOT DISTINCT FROM functional.alltypestiny.tinyint_col, functional.alltypestiny.year IS NOT DISTINCT FROM functional.alltypestiny.year, functional.alltypestiny.string_col IS NOT DISTINCT FROM functional.alltypestiny.string_col, functional.alltypestiny.date_string_col IS NOT DISTINCT FROM functional.alltypestiny.date_string_col
|  runtime filters: RF000 <- functional.alltypestiny.bigint_col, RF001 <- functional.alltypestiny.bool_col, RF002 <- functional.alltypestiny.double_col, RF003 <- functional.alltypestiny.float_col, RF004 <- functional.alltypestiny.id, RF005 <- functional.alltypestiny.int_col, RF006 <- functional.alltypestiny.month, RF007 <- functional.alltypestiny.smallint_col, RF008 <- functional.alltypestiny.timestamp_col, RF010 <- functional.alltypestiny.tinyint_col
|  row-size=89B cardinality=1
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
05:HASH JOIN [LEFT ANTI JOIN]
|  hash predicates: functional.alltypestiny.bigint_col IS NOT DISTINCT FROM bigint_col, functional.alltypestiny.bool_col IS NOT DISTINCT FROM bool_col, functional.alltypestiny.double_col IS NOT DISTINCT FROM double_col, functional.alltypestiny.float_col IS NOT DISTINCT FROM float_col, functional.alltypestiny.id IS NOT DISTINCT FROM id, functional.alltypestiny.int_col IS NOT DISTINCT FROM int_col, functional.alltypestiny.month IS NOT DISTINCT FROM month, functional.alltypestiny.smallint_col IS NOT DISTINCT FROM smallint_col, functional.alltypestiny.timestamp_col IS NOT DISTINCT FROM timestamp_col, functional.alltypestiny.tinyint_col IS NOT DISTINCT FROM tinyint_col, functional.alltypestiny.year IS NOT DISTINCT FROM year, functional.alltypestiny.string_col IS NOT DISTINCT FROM string_col, functional.alltypestiny.date_string_col IS NOT DISTINCT FROM date_string_col
|  row-size=89B cardinality=1
|
|--03:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
   limit: 1
   row-size=89B cardinality=1
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
11:AGGREGATE [FINALIZE]
|  group by: id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month
|  row-size=89B cardinality=3
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=3
|
|--18:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: int_col ASC
|  |  limit: 1
|  |
|  10:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  09:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--17:EXCHANGE [UNPARTITIONED]
|  |  limit: 1
|  |
|  08:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
07:AGGREGATE [FINALIZE]
|  group by: functional.alltypestiny.id, functional.alltypestiny.bool_col, functional.alltypestiny.tinyint_col, functional.alltypestiny.smallint_col, functional.alltypestiny.int_col, functional.alltypestiny.bigint_col, functional.alltypestiny.float_col, functional.alltypestiny.double_col, functional.alltypestiny.date_string_col, functional.alltypestiny.string_col, functional.alltypestiny.timestamp_col, functional.alltypestiny.year, functional.alltypestiny.month
|  row-size=89B cardinality=1
|
06:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: functional.alltypestiny.bigint_col IS NOT DISTINCT FROM functional.alltypestiny.bigint_col, functional.alltypestiny.bool_col IS NOT DISTINCT FROM functional.alltypestiny.bool_col, functional.alltypestiny.double_col IS NOT DISTINCT FROM functional.alltypestiny.double_col, functional.alltypestiny.float_col IS NOT DISTINCT FROM functional.alltypestiny.float_col, functional.alltypestiny.id IS NOT DISTINCT FROM functional.alltypestiny.id, functional.alltypestiny.int_col IS NOT DISTINCT FROM functional.alltypestiny.int_col, functional.alltypestiny.month IS NOT DISTINCT FROM functional.alltypestiny.month, functional.alltypestiny.smallint_col IS NOT DISTINCT FROM functional.alltypestiny.smallint_col, functional.alltypestiny.timestamp_col IS NOT DISTINCT FROM functional.alltypestiny.timestamp_col, functional.alltypestiny.tinyint_col IS NOT DISTINCT FROM functional.alltypestiny.tinyint_col, functional.alltypestiny.year IS NOT DISTINCT FROM functional.alltypestiny.year, functional.alltypestiny.string_col IS NOT DISTINCT FROM functional.alltypestiny.string_col, functional.alltypestiny.date_string_col IS NOT DISTINCT FROM functional.alltypestiny.date_string_col
|  runtime filters: RF000 <- functional.alltypestiny.bigint_col, RF001 <- functional.alltypestiny.bool_col, RF002 <- functional.alltypestiny.double_col, RF003 <- functional.alltypestiny.float_col, RF004 <- functional.alltypestiny.id, RF005 <- functional.alltypestiny.int_col, RF006 <- functional.alltypestiny.month, RF007 <- functional.alltypestiny.smallint_col, RF008 <- functional.alltypestiny.timestamp_col, RF010 <- functional.alltypestiny.tinyint_col
|  row-size=89B cardinality=1
|
|--16:EXCHANGE [UNPARTITIONED]
|  |
|  15:EXCHANGE [UNPARTITIONED]
|  |  limit: 1
|  |
|  04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     limit: 1
|     row-size=89B cardinality=1
|
05:HASH JOIN [LEFT ANTI JOIN, BROADCAST]
|  hash predicates: functional.alltypestiny.bigint_col IS NOT DISTINCT FROM bigint_col, functional.alltypestiny.bool_col IS NOT DISTINCT FROM bool_col, functional.alltypestiny.double_col IS NOT DISTINCT FROM double_col, functional.alltypestiny.float_col IS NOT DISTINCT FROM float_col, functional.alltypestiny.id IS NOT DISTINCT FROM id, functional.alltypestiny.int_col IS NOT DISTINCT FROM int_col, functional.alltypestiny.month IS NOT DISTINCT FROM month, functional.alltypestiny.smallint_col IS NOT DISTINCT FROM smallint_col, functional.alltypestiny.timestamp_col IS NOT DISTINCT FROM timestamp_col, functional.alltypestiny.tinyint_col IS NOT DISTINCT FROM tinyint_col, functional.alltypestiny.year IS NOT DISTINCT FROM year, functional.alltypestiny.string_col IS NOT DISTINCT FROM string_col, functional.alltypestiny.date_string_col IS NOT DISTINCT FROM date_string_col
|  row-size=89B cardinality=1
|
|--14:EXCHANGE [UNPARTITIONED]
|  |
|  13:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: int_col ASC
|  |  limit: 1
|  |
|  03:TOP-N [LIMIT=1]
|  |  order by: int_col ASC
|  |  row-size=89B cardinality=1
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
12:EXCHANGE [UNPARTITIONED]
|  limit: 1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   runtime filters: RF000 -> functional.alltypestiny.bigint_col, RF001 -> functional.alltypestiny.bool_col, RF002 -> functional.alltypestiny.double_col, RF003 -> functional.alltypestiny.float_col, RF004 -> functional.alltypestiny.id, RF005 -> functional.alltypestiny.int_col, RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.smallint_col, RF008 -> functional.alltypestiny.timestamp_col, RF010 -> functional.alltypestiny.tinyint_col
   limit: 1
   row-size=89B cardinality=1
====