Google Git
Sign in
apache / impala / 578933fe74d8c320431148f9a458a0051afafb17 / . / testdata / workloads / functional-planner / queries / PlannerTest / setoperation-rewrite.test
blob: 8bfd42ebce48f8f683c7e1844070c9e0105b1615 [file] [log] [blame]
# 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
====