# intersect
select year, month from functional.alltypes
  intersect
select year, month from functional.alltypes where year=2009
---- PLAN
PLAN-ROOT SINK
|
04: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=24
|
|--03:AGGREGATE [FINALIZE]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  02:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009
|     HDFS partitions=12/24 files=12 size=238.68KB
|     row-size=8B cardinality=3.65K
|
01:AGGREGATE [FINALIZE]
|  group by: `year`, `month`
|  row-size=8B cardinality=24
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> functional.alltypes.month, RF001 -> functional.alltypes.year
   partition key scan
   row-size=8B cardinality=24
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
|  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=24
|
|--09:EXCHANGE [HASH(functional.alltypes.year,functional.alltypes.month)]
|  |
|  08:AGGREGATE [FINALIZE]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  07:EXCHANGE [HASH(functional.alltypes.year,functional.alltypes.month)]
|  |
|  03:AGGREGATE [STREAMING]
|  |  group by: functional.alltypes.year, functional.alltypes.month
|  |  row-size=8B cardinality=24
|  |
|  02:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009
|     HDFS partitions=12/24 files=12 size=238.68KB
|     row-size=8B cardinality=3.65K
|
06:AGGREGATE [FINALIZE]
|  group by: `year`, `month`
|  row-size=8B cardinality=24
|
05:EXCHANGE [HASH(`year`,`month`)]
|
01:AGGREGATE [STREAMING]
|  group by: `year`, `month`
|  row-size=8B cardinality=24
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> functional.alltypes.month, RF001 -> functional.alltypes.year
   partition key scan
   row-size=8B cardinality=24
====
# 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: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: RF000 <- `month`, RF001 <- `year`, RF002 <- id
|  row-size=12B cardinality=2
|
|--08: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: RF006 <- `month`, RF007 <- `year`, RF008 <- id
|  |  row-size=12B cardinality=2
|  |
|  |--07: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
|  |  |
|  |  |--06:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=12B cardinality=2
|  |  |
|  |  05:AGGREGATE [FINALIZE]
|  |  |  group by: id, `year`, `month`
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     runtime filters: RF012 -> functional.alltypestiny.month, RF013 -> functional.alltypestiny.year, RF014 -> functional.alltypestiny.id
|  |     row-size=12B 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
|     runtime filters: RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.year, RF008 -> functional.alltypestiny.id
|     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
|
19:EXCHANGE [UNPARTITIONED]
|
09:HASH JOIN [LEFT SEMI 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=12B cardinality=2
|
|--18:EXCHANGE [HASH(id,`year`,`month`)]
|  |
|  08:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
|  |  hash predicates: `month` IS NOT DISTINCT FROM `month`, `year` IS NOT DISTINCT FROM `year`, id IS NOT DISTINCT FROM id
|  |  runtime filters: RF006 <- `month`, RF007 <- `year`, RF008 <- id
|  |  row-size=12B cardinality=2
|  |
|  |--17:EXCHANGE [HASH(id,`year`,`month`)]
|  |  |
|  |  07: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
|  |  |
|  |  |--16:EXCHANGE [BROADCAST]
|  |  |  |
|  |  |  06:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=12B cardinality=2
|  |  |
|  |  15:AGGREGATE [FINALIZE]
|  |  |  group by: id, `year`, `month`
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  14:EXCHANGE [HASH(id,`year`,`month`)]
|  |  |
|  |  05:AGGREGATE [STREAMING]
|  |  |  group by: id, `year`, `month`
|  |  |  row-size=12B cardinality=2
|  |  |
|  |  04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     runtime filters: RF012 -> functional.alltypestiny.month, RF013 -> functional.alltypestiny.year, RF014 -> functional.alltypestiny.id
|  |     row-size=12B cardinality=2
|  |
|  13:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  12: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
|     runtime filters: RF006 -> functional.alltypestiny.month, RF007 -> functional.alltypestiny.year, RF008 -> functional.alltypestiny.id
|     row-size=12B cardinality=2
|
11:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
10: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
====
# 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: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
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     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` = 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
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
05: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
|
|--09: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
|
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
|
|--08:EXCHANGE [BROADCAST]
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=12B cardinality=2
|
07:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
06: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
   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: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
|
|--05: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
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=12B 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
   row-size=12B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
13:EXCHANGE [UNPARTITIONED]
|
06:HASH JOIN [LEFT ANTI JOIN, PARTITIONED]
|  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
|
|--12:EXCHANGE [HASH(id,`year`,`month`)]
|  |
|  05: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
|  |
|  |--11:EXCHANGE [BROADCAST]
|  |  |
|  |  04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=12B cardinality=2
|  |
|  10:AGGREGATE [FINALIZE]
|  |  group by: id, `year`, `month`
|  |  row-size=12B cardinality=2
|  |
|  09: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
|
08:AGGREGATE [FINALIZE]
|  group by: id, `year`, `month`
|  row-size=12B cardinality=2
|
07: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
   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: 08,10
|  row-size=89B cardinality=3
|
|--07: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
|  |
|  |--05: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
|  |
|  06: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
|  |
|  |--04:TOP-N [LIMIT=1]
|  |  |  order by: int_col ASC
|  |  |  row-size=89B cardinality=1
|  |  |
|  |  03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 1
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  02: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
|  |  limit: 1
|  |  row-size=89B cardinality=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
|     row-size=89B cardinality=2
|
|--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
---- 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: 12,13
|  row-size=89B cardinality=3
|
|--07: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
|  |
|  |--20:EXCHANGE [UNPARTITIONED]
|  |  |
|  |  19:EXCHANGE [UNPARTITIONED]
|  |  |  limit: 1
|  |  |
|  |  05: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
|  |
|  06: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
|  |
|  |--18:EXCHANGE [UNPARTITIONED]
|  |  |
|  |  17:MERGING-EXCHANGE [UNPARTITIONED]
|  |  |  order by: int_col ASC
|  |  |  limit: 1
|  |  |
|  |  04:TOP-N [LIMIT=1]
|  |  |  order by: int_col ASC
|  |  |  row-size=89B cardinality=1
|  |  |
|  |  03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 1
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  16:EXCHANGE [UNPARTITIONED]
|  |  limit: 1
|  |
|  15: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
|  |  limit: 1
|  |  row-size=89B cardinality=1
|  |
|  14:EXCHANGE [HASH(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)]
|  |
|  02:AGGREGATE [STREAMING]
|  |  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=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
|     row-size=89B cardinality=2
|
|--13: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
|
12: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
====
# DISTINCT is pushed down to left-most operand
select id from functional.alltypessmall
intersect
select id from functional.alltypestiny where int_col > 0;
---- PLAN
PLAN-ROOT SINK
|
03:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=4B cardinality=1
|
|--02:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     predicates: int_col > 0
|     row-size=8B cardinality=1
|
01:AGGREGATE [FINALIZE]
|  group by: id
|  row-size=4B cardinality=99
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> functional.alltypessmall.id
   row-size=4B cardinality=100
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
03:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
|  hash predicates: id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=4B cardinality=1
|
|--06:EXCHANGE [HASH(id)]
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     predicates: int_col > 0
|     row-size=8B cardinality=1
|
05:AGGREGATE [FINALIZE]
|  group by: id
|  row-size=4B cardinality=99
|
04:EXCHANGE [HASH(id)]
|
01:AGGREGATE [STREAMING]
|  group by: id
|  row-size=4B cardinality=99
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> functional.alltypessmall.id
   row-size=4B cardinality=100
====
# no pushdown of DISTINCT into operands with analytical functions
select id, count(*) OVER() from functional.alltypessmall
intersect
select id, count(*) OVER() from functional.alltypessmall;
---- PLAN
PLAN-ROOT SINK
|
05:AGGREGATE [FINALIZE]
|  group by: id, count(*)
|  row-size=12B cardinality=99
|
04:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: count(*) IS NOT DISTINCT FROM count(*), id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=12B cardinality=100
|
|--03:ANALYTIC
|  |  functions: count(*)
|  |  row-size=12B cardinality=100
|  |
|  02:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=4B cardinality=100
|
01:ANALYTIC
|  functions: count(*)
|  row-size=12B cardinality=100
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> id
   row-size=4B cardinality=100
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
05:AGGREGATE [FINALIZE]
|  group by: id, count(*)
|  row-size=12B cardinality=99
|
04:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: count(*) IS NOT DISTINCT FROM count(*), id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=12B cardinality=100
|
|--08:EXCHANGE [UNPARTITIONED]
|  |
|  03:ANALYTIC
|  |  functions: count(*)
|  |  row-size=12B cardinality=100
|  |
|  07:EXCHANGE [UNPARTITIONED]
|  |
|  02:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=4B cardinality=100
|
01:ANALYTIC
|  functions: count(*)
|  row-size=12B cardinality=100
|
06:EXCHANGE [UNPARTITIONED]
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> id
   row-size=4B cardinality=100
====
# no DISTINCT needed as leftmost operand has grouping which implies distinct rows
select id, sum(int_col) from functional.alltypessmall group by id
intersect
select id, 5 from functional.alltypestiny where int_col > 0;
---- PLAN
PLAN-ROOT SINK
|
03:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: sum(int_col) IS NOT DISTINCT FROM 5, id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=12B cardinality=1
|
|--02:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     predicates: int_col > 0
|     row-size=8B cardinality=1
|
01:AGGREGATE [FINALIZE]
|  output: sum(int_col)
|  group by: id
|  row-size=12B cardinality=99
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> functional.alltypessmall.id
   row-size=8B cardinality=100
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
03:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: sum(int_col) IS NOT DISTINCT FROM 5, id IS NOT DISTINCT FROM id
|  runtime filters: RF000 <- id
|  row-size=12B cardinality=1
|
|--06:EXCHANGE [BROADCAST]
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     predicates: int_col > 0
|     row-size=8B cardinality=1
|
05:AGGREGATE [FINALIZE]
|  output: sum:merge(int_col)
|  group by: id
|  row-size=12B cardinality=99
|
04:EXCHANGE [HASH(id)]
|
01:AGGREGATE [STREAMING]
|  output: sum(int_col)
|  group by: id
|  row-size=12B cardinality=99
|
00:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   runtime filters: RF000 -> functional.alltypessmall.id
   row-size=8B cardinality=100
====
# no DISTINCT pushdown for valuestmt or single unionstmt without a FROM clause
select 1 intersect select 1 intersect select 2;
---- PLAN
PLAN-ROOT SINK
|
05:AGGREGATE [FINALIZE]
|  group by: $a$1.1
|  row-size=1B cardinality=1
|
04:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: `$a$1`.`1` IS NOT DISTINCT FROM `$a$3`.`$c$2`
|  row-size=1B cardinality=1
|
|--02:UNION
|     constant-operands=1
|     row-size=1B cardinality=1
|
03:HASH JOIN [LEFT SEMI JOIN]
|  hash predicates: `$a$1`.`1` IS NOT DISTINCT FROM `$a$2`.`$c$1`
|  row-size=1B cardinality=1
|
|--01:UNION
|     constant-operands=1
|     row-size=1B cardinality=1
|
00:UNION
   constant-operands=1
   row-size=1B cardinality=1
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
05:AGGREGATE [FINALIZE]
|  group by: $a$1.1
|  row-size=1B cardinality=1
|
04:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: `$a$1`.`1` IS NOT DISTINCT FROM `$a$3`.`$c$2`
|  row-size=1B cardinality=1
|
|--07:EXCHANGE [UNPARTITIONED]
|  |
|  02:UNION
|     constant-operands=1
|     row-size=1B cardinality=1
|
03:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
|  hash predicates: `$a$1`.`1` IS NOT DISTINCT FROM `$a$2`.`$c$1`
|  row-size=1B cardinality=1
|
|--06:EXCHANGE [UNPARTITIONED]
|  |
|  01:UNION
|     constant-operands=1
|     row-size=1B cardinality=1
|
00:UNION
   constant-operands=1
   row-size=1B cardinality=1
====