| # 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 |
| ==== |