testdata/workloads/functional-planner/queries/PlannerTest/union.test

# binding predicates of b.month equiv class gets propagated into union
select * from
  (select year, month from functional.alltypes
   union all
   select year, month from functional.alltypes) a
  inner join
  functional.alltypessmall b
  on (a.month = b.month)
where b.month = 1
---- PLAN
PLAN-ROOT SINK
|
04:HASH JOIN [INNER JOIN]
|  hash predicates: month = b.`month`
|  runtime filters: RF000 <- b.`month`
|  row-size=97B cardinality=1.24K
|
|--03:SCAN HDFS [functional.alltypessmall b]
|     partition predicates: b.`month` = 1
|     HDFS partitions=1/4 files=1 size=1.57KB
|     row-size=89B cardinality=25
|
00:UNION
|  pass-through-operands: all
|  row-size=8B cardinality=1.24K
|
|--02:SCAN HDFS [functional.alltypes]
|     partition predicates: functional.alltypes.month = 1
|     HDFS partitions=2/24 files=2 size=40.32KB
|     runtime filters: RF000 -> functional.alltypes.month
|     row-size=8B cardinality=620
|
01:SCAN HDFS [functional.alltypes]
   partition predicates: functional.alltypes.month = 1
   HDFS partitions=2/24 files=2 size=40.32KB
   runtime filters: RF000 -> functional.alltypes.month
   row-size=8B cardinality=620
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
06:EXCHANGE [UNPARTITIONED]
|
04:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: month = b.`month`
|  runtime filters: RF000 <- b.`month`
|  row-size=97B cardinality=1.24K
|
|--05:EXCHANGE [BROADCAST]
|  |
|  03:SCAN HDFS [functional.alltypessmall b]
|     partition predicates: b.`month` = 1
|     HDFS partitions=1/4 files=1 size=1.57KB
|     row-size=89B cardinality=25
|
00:UNION
|  pass-through-operands: all
|  row-size=8B cardinality=1.24K
|
|--02:SCAN HDFS [functional.alltypes]
|     partition predicates: functional.alltypes.month = 1
|     HDFS partitions=2/24 files=2 size=40.32KB
|     runtime filters: RF000 -> functional.alltypes.month
|     row-size=8B cardinality=620
|
01:SCAN HDFS [functional.alltypes]
   partition predicates: functional.alltypes.month = 1
   HDFS partitions=2/24 files=2 size=40.32KB
   runtime filters: RF000 -> functional.alltypes.month
   row-size=8B cardinality=620
====
// Only UNION ALL, no nested unions
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=2
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
04:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Only UNION ALL with limit inside operands. One of the operands also has an order by.
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 all
select * from functional.alltypestiny where year=2009 and month=2 limit 1
---- PLAN
PLAN-ROOT SINK
|
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
|     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
   limit: 1
   row-size=89B cardinality=1
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=3
|
|--07: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
|
|--06: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
|
05:EXCHANGE [UNPARTITIONED]
|  limit: 1
|
01: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
====
// Only UNION DISTINCT, no nested unions
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select * from functional.alltypestiny where year=2009 and month=2
---- PLAN
PLAN-ROOT SINK
|
04: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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
06: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=6
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Only UNION ALL, mixed selects with and without from clauses, no nested unions
select * from functional.alltypestiny where year=2009 and month=1
union all
select 0,true,0,0,0,0,0,0,'01/01/09','0',cast('2009-01-01 00:00:00' as timestamp),2009,1
union all
select * from functional.alltypestiny where year=2009 and month=1
union all
select 1,false,1,1,1,10,1.1,10.1,'01/01/09','1',cast('2009-01-01 00:01:00' as timestamp),2009,1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  constant-operands=2
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
03:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  constant-operands=2
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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
   row-size=89B cardinality=2
====
// Only UNION DISTINCT, mixed selects with and without from clauses, no nested unions
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select 0,true,0,0,0,0,0,0,'01/01/09','0',cast('2009-01-01 00:00:00' as timestamp),2009,1
union distinct
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select 1,false,1,1,1,10,1.1,10.1,'01/01/09','1',cast('2009-01-01 00:01:00' as timestamp),2009,1
---- PLAN
PLAN-ROOT SINK
|
03: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=6
|
00:UNION
|  constant-operands=2
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
06:EXCHANGE [UNPARTITIONED]
|
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=6
|
04:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
03:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  constant-operands=2
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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
   row-size=89B cardinality=2
====
// Mixed UNION ALL/DISTINCT but effectively only UNION DISTINCT, no nested unions,
// with order by and limit
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=2
union distinct
(select * from functional.alltypestiny where year=2009 and month=2)
order by 3 limit 3
---- PLAN
PLAN-ROOT SINK
|
06:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC
|  limit: 3
|
06:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
08: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=8
|
07:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
05:AGGREGATE [STREAMING]
|  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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=2
union all
(select * from functional.alltypestiny where year=2009 and month=2)
order by 3,4 limit 3
---- PLAN
PLAN-ROOT SINK
|
07:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC, smallint_col ASC
|  row-size=89B cardinality=3
|
04:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--03: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=4
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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
|     row-size=89B cardinality=2
|
|--06:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC, smallint_col ASC
|  limit: 3
|
07:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC, smallint_col ASC
|  row-size=89B cardinality=3
|
04:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--09: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=4
|  |
|  08:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  03:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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
|     row-size=89B cardinality=2
|
|--06:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit
select * from functional.alltypestiny where year=2009 and month=1
union all
select * from functional.alltypestiny where year=2009 and month=1
union distinct
select * from functional.alltypestiny where year=2009 and month=2
union all
(select * from functional.alltypestiny where year=2009 and month=2)
order by 3,4 limit 4
---- PLAN
PLAN-ROOT SINK
|
07:TOP-N [LIMIT=4]
|  order by: tinyint_col ASC, smallint_col ASC
|  row-size=89B cardinality=4
|
05:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04: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=6
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=6
|  |
|  |--03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--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
|     row-size=89B cardinality=2
|
06:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC, smallint_col ASC
|  limit: 4
|
07:TOP-N [LIMIT=4]
|  order by: tinyint_col ASC, smallint_col ASC
|  row-size=89B cardinality=4
|
05:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--09: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=6
|  |
|  08:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  04:AGGREGATE [STREAMING]
|  |  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=6
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=6
|  |
|  |--03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--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
|     row-size=89B cardinality=2
|
06:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// Union unnesting: Only UNION ALL, first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union all
 select * from functional.alltypestiny where year=2009 and month=2)
union all
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
04:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: Only UNION ALL, second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
04:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Union unnesting: Only UNION DISTINCT, first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union distinct
 select * from functional.alltypestiny where year=2009 and month=2)
union distinct
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
04: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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
06: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=6
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: Only UNION DISTINCT, second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
04: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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
06: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=6
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL doesn't absorb nested union with DISTINCT,
// first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union distinct
 select * from functional.alltypestiny where year=2009 and month=2)
union all
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
04: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=4
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
07: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=4
|
06:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=4
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL doesn't absorb nested union with DISTINCT,
// second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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=4
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--07: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=4
|  |
|  06:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  05:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL absorbs the children but not directly the operands
// of a nested union with mixed ALL/DISTINCT, first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union distinct
 select * from functional.alltypestiny where year=2009 and month=2
 union all
 select * from functional.alltypestiny where year=2009 and month=2)
union all
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--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=4
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--06:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--08: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=4
|  |
|  07:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  05:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL absorbs the children but not directly the operands
// of a nested union with mixed ALL/DISTINCT, second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=2
   union all
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--06: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=4
|  |
|  03:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--05:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--08: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=4
|  |
|  07:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  06:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  03:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--05:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL doesn't absorb the children of a nested union
// with mixed ALL/DISTINCT and limit, second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=2
   union all
   (select * from functional.alltypestiny where year=2009 and month=2)
   limit 10)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--06:UNION
|  |  pass-through-operands: all
|  |  limit: 10
|  |  row-size=89B cardinality=6
|  |
|  |--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=4
|  |  |
|  |  02:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--04:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 1
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  07:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 7:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
12:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--11:EXCHANGE [RANDOM]
|  |
|  10:EXCHANGE [UNPARTITIONED]
|  |  limit: 10
|  |
|  06:UNION
|  |  pass-through-operands: all
|  |  limit: 10
|  |  row-size=89B cardinality=6
|  |
|  |--09: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=4
|  |  |
|  |  08:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |  |
|  |  05:AGGREGATE [STREAMING]
|  |  |  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=4
|  |  |
|  |  02:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--04:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 2
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 1
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  07:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL doesn't absorb nested union with order by and limit,
// first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union all
 (select * from functional.alltypestiny where year=2009 and month=2)
 order by 3 limit 3)
union all
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
04:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
07:EXCHANGE [RANDOM]
|
06:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC
|  limit: 3
|
04:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// Union unnesting: UNION ALL doesn't absorb nested union with order by and limit,
// second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   (select * from functional.alltypestiny where year=2009 and month=2)
   order by 3 limit 3)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--07:EXCHANGE [RANDOM]
|  |
|  06:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: tinyint_col ASC
|  |  limit: 3
|  |
|  05:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT absorbs nested union with ALL
// first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union all
 select * from functional.alltypestiny where year=2009 and month=2)
union distinct
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
04: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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
06: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=6
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT absorbs nested union with ALL,
// second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
04: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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
06: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=6
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
04:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT absorbs nested union with mixed ALL/DISTINCT,
// first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union distinct
 select * from functional.alltypestiny where year=2009 and month=2
 union all
 select * from functional.alltypestiny where year=2009 and month=2)
union distinct
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
07: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=8
|
06:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
05:AGGREGATE [STREAMING]
|  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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     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
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT absorbs nested union with mixed ALL/DISTINCT,
// second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=2
   union all
   select * from functional.alltypestiny where year=2009 and month=2)
---- PLAN
PLAN-ROOT SINK
|
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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:EXCHANGE [UNPARTITIONED]
|
07: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=8
|
06:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
05:AGGREGATE [STREAMING]
|  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=8
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=8
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT doesn't absorb nested union with order by and limit,
// first operand is nested
(select * from functional.alltypestiny where year=2009 and month=1
 union all
 (select * from functional.alltypestiny where year=2009 and month=2)
 order by 3 limit 3)
union distinct
select * from functional.alltypestiny where year=2009 and month=1
---- PLAN
PLAN-ROOT SINK
|
06: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=5
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
04:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
11:EXCHANGE [UNPARTITIONED]
|
10: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=5
|
09:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
06:AGGREGATE [STREAMING]
|  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=5
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
08:EXCHANGE [RANDOM]
|
07:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC
|  limit: 3
|
04:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
01:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=4
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
02:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// Union unnesting: UNION DISTINCT doesn't absorb nested union with order by and limit
// second operand is nested
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   (select * from functional.alltypestiny where year=2009 and month=2)
   order by 3 limit 3)
---- PLAN
PLAN-ROOT SINK
|
06: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=5
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--05:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
11:EXCHANGE [UNPARTITIONED]
|
10: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=5
|
09:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
06:AGGREGATE [STREAMING]
|  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=5
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=5
|
|--08:EXCHANGE [RANDOM]
|  |
|  07:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: tinyint_col ASC
|  |  limit: 3
|  |
|  05:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
====
// Complex union unnesting: Multiple levels of UNION ALL, fully unnestable
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
     (select * from functional.alltypestiny where year=2009 and month=2
      union all
        (select * from functional.alltypestiny where year=2009 and month=2
         union all
         select * from functional.alltypestiny where year=2009 and month=3)))
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=10
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 3
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
06:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=10
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 3
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Complex union unnesting: Multiple levels of UNION DISTINCT, fully unnestable
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
     (select * from functional.alltypestiny where year=2009 and month=2
      union distinct
        (select * from functional.alltypestiny where year=2009 and month=2
         union distinct
         select * from functional.alltypestiny where year=2009 and month=3)))
---- PLAN
PLAN-ROOT SINK
|
06: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=10
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=10
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 3
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
08: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=10
|
07:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
06:AGGREGATE [STREAMING]
|  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=10
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=10
|
|--05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 3
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--04:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Complex union unnesting: Partially unnestable up to 2nd level
select * from functional.alltypestiny where year=2009 and month=1
union all
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
     (select * from functional.alltypestiny where year=2009 and month=2
      union all
        (select * from functional.alltypestiny where year=2009 and month=2
         union distinct
         (select * from functional.alltypestiny where year=2009 and month=3)
         order by 3 limit 3)))
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=9
|
|--10: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=7
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=7
|  |
|  |--09:TOP-N [LIMIT=3]
|  |  |  order by: tinyint_col ASC
|  |  |  row-size=89B cardinality=3
|  |  |
|  |  08: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=4
|  |  |
|  |  05:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--07:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 3
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  06:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 7:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
17:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=9
|
|--16: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=7
|  |
|  15:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  10:AGGREGATE [STREAMING]
|  |  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=7
|  |
|  02:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=7
|  |
|  |--14:EXCHANGE [RANDOM]
|  |  |
|  |  13:MERGING-EXCHANGE [UNPARTITIONED]
|  |  |  order by: tinyint_col ASC
|  |  |  limit: 3
|  |  |
|  |  09:TOP-N [LIMIT=3]
|  |  |  order by: tinyint_col ASC
|  |  |  row-size=89B cardinality=3
|  |  |
|  |  12: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=4
|  |  |
|  |  11:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |  |
|  |  08:AGGREGATE [STREAMING]
|  |  |  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=4
|  |  |
|  |  05:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--07:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 3
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  06:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--04:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  03: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
   row-size=89B cardinality=2
====
// Complex union unnesting: Partially unnestable up to 1st level
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
     (select * from functional.alltypestiny where year=2009 and month=2
      union all
        (select * from functional.alltypestiny where year=2009 and month=2
         union distinct
         (select * from functional.alltypestiny where year=2009 and month=3)
         order by 3 limit 3)))
---- PLAN
PLAN-ROOT SINK
|
09: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=9
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=9
|
|--08:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  07: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=4
|  |
|  04:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--06:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 3
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
16:EXCHANGE [UNPARTITIONED]
|
15: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=9
|
14:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
09:AGGREGATE [STREAMING]
|  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=9
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=9
|
|--13:EXCHANGE [RANDOM]
|  |
|  12:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: tinyint_col ASC
|  |  limit: 3
|  |
|  08:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=3
|  |
|  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=4
|  |
|  10:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  07:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  04:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--06:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 3
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  05:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--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
   row-size=89B cardinality=2
====
// Complex union unnesting: Multiple nested unions to test all rules in a single query
select * from functional.alltypestiny where year=2009 and month=1
union distinct
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   select * from functional.alltypestiny where year=2009 and month=2)
union distinct
  (select * from functional.alltypestiny where year=2009 and month=2
   union all
   (select * from functional.alltypestiny where year=2009 and month=3)
   order by 3 limit 3)
union all
  (select * from functional.alltypestiny where year=2009 and month=3
   union all
   select * from functional.alltypestiny where year=2009 and month=4)
union all
  (select * from functional.alltypestiny where year=2009 and month=4
   union all
   (select * from functional.alltypestiny where year=2009 and month=5)
   order by 3 limit 3)
---- PLAN
PLAN-ROOT SINK
|
09:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=15
|
|--08: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=9
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=9
|  |
|  |--07:TOP-N [LIMIT=3]
|  |  |  order by: tinyint_col ASC
|  |  |  row-size=89B cardinality=3
|  |  |
|  |  04:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--06:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 3
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  05:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--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
|     row-size=89B cardinality=2
|
|--15:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=2
|  |
|  12:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=2
|  |
|  |--14:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 5
|  |     partitions=0/4 files=0 size=0B
|  |     row-size=89B cardinality=0
|  |
|  13:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 4
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--11:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 4
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
10:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 3
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
NODE 6:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
NODE 10:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=3/090301.txt 0:115
NODE 11:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=4/090401.txt 0:115
NODE 13:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=4/090401.txt 0:115
NODE 14:
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
22:EXCHANGE [UNPARTITIONED]
|
09:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=15
|
|--20: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=9
|  |
|  19:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  08:AGGREGATE [STREAMING]
|  |  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=9
|  |
|  00:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=9
|  |
|  |--18:EXCHANGE [RANDOM]
|  |  |
|  |  17:MERGING-EXCHANGE [UNPARTITIONED]
|  |  |  order by: tinyint_col ASC
|  |  |  limit: 3
|  |  |
|  |  07:TOP-N [LIMIT=3]
|  |  |  order by: tinyint_col ASC
|  |  |  row-size=89B cardinality=3
|  |  |
|  |  04:UNION
|  |  |  pass-through-operands: all
|  |  |  row-size=89B cardinality=4
|  |  |
|  |  |--06:SCAN HDFS [functional.alltypestiny]
|  |  |     partition predicates: `year` = 2009, `month` = 3
|  |  |     HDFS partitions=1/4 files=1 size=115B
|  |  |     row-size=89B cardinality=2
|  |  |
|  |  05:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--03:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 2
|  |     HDFS partitions=1/4 files=1 size=115B
|  |     row-size=89B cardinality=2
|  |
|  |--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
|     row-size=89B cardinality=2
|
|--21:EXCHANGE [RANDOM]
|  |
|  16:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: tinyint_col ASC
|  |  limit: 3
|  |
|  15:TOP-N [LIMIT=3]
|  |  order by: tinyint_col ASC
|  |  row-size=89B cardinality=2
|  |
|  12:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=2
|  |
|  |--14:SCAN HDFS [functional.alltypestiny]
|  |     partition predicates: `year` = 2009, `month` = 5
|  |     partitions=0/4 files=0 size=0B
|  |     row-size=89B cardinality=0
|  |
|  13:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 4
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
|--11:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 4
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
10:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 3
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// UNION ALL in subquery
select x.* from
  (select * from functional.alltypestiny where year=2009 and month=1
   union all
   select * from functional.alltypestiny where year=2009 and month=1) x
union all
(select * from functional.alltypestiny where year=2009 and month=2)
order by 3 limit 5
---- PLAN
PLAN-ROOT SINK
|
05:TOP-N [LIMIT=5]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=5
|
00:UNION
|  pass-through-operands: 04
|  row-size=89B cardinality=6
|
|--01:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
04:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 4:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
06:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC
|  limit: 5
|
05:TOP-N [LIMIT=5]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=5
|
00:UNION
|  pass-through-operands: 04
|  row-size=89B cardinality=6
|
|--01:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
04:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// UNION DISTINCT in subquery
select x.* from
  (select * from functional.alltypestiny where year=2009 and month=1
   union distinct
   select * from functional.alltypestiny where year=2009 and month=1) x
union distinct
(select * from functional.alltypestiny where year=2009 and month=2)
order by 3 limit 3
---- PLAN
PLAN-ROOT SINK
|
07:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
06: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=6
|
00:UNION
|  pass-through-operands: 05
|  row-size=89B cardinality=6
|
|--04: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=4
|  |
|  01:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
---- SCANRANGELOCATIONS
NODE 2:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 5:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=2/090201.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
12:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: tinyint_col ASC
|  limit: 3
|
07:TOP-N [LIMIT=3]
|  order by: tinyint_col ASC
|  row-size=89B cardinality=3
|
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=6
|
10:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
06:AGGREGATE [STREAMING]
|  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=6
|
00:UNION
|  pass-through-operands: 05
|  row-size=89B cardinality=6
|
|--09: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=4
|  |
|  08:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|  |
|  04:AGGREGATE [STREAMING]
|  |  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=4
|  |
|  01:UNION
|  |  pass-through-operands: all
|  |  row-size=89B cardinality=4
|  |
|  |--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:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=89B cardinality=2
|
05:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=89B cardinality=2
====
// UNION ALL in subquery with a WHERE condition in the outer select.
select x.* from
  (select int_col, bool_col, count(*) as count_col
   from functional.alltypestiny where year=2009 and month=1 group by 1, 2
   union all
   select int_col, bool_col, count(*) as count_col
   from functional.alltypestiny where year=2009 and month=1 group by 1, 2) x
where x.int_col < 5 and x.bool_col = false
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=13B cardinality=2
|
|--04:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  group by: int_col, bool_col
|  |  row-size=13B cardinality=1
|  |
|  03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
|     row-size=5B cardinality=1
|
02:AGGREGATE [FINALIZE]
|  output: count(*)
|  group by: int_col, bool_col
|  row-size=13B cardinality=1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
   row-size=5B cardinality=1
---- SCANRANGELOCATIONS
NODE 1:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
NODE 3:
  HDFS SPLIT hdfs://localhost:20500/test-warehouse/alltypestiny/year=2009/month=1/090101.txt 0:115
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
09:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=13B cardinality=2
|
|--08:AGGREGATE [FINALIZE]
|  |  output: count:merge(*)
|  |  group by: int_col, bool_col
|  |  row-size=13B cardinality=1
|  |
|  07:EXCHANGE [HASH(int_col,bool_col)]
|  |
|  04:AGGREGATE [STREAMING]
|  |  output: count(*)
|  |  group by: int_col, bool_col
|  |  row-size=13B cardinality=1
|  |
|  03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
|     row-size=5B cardinality=1
|
06:AGGREGATE [FINALIZE]
|  output: count:merge(*)
|  group by: int_col, bool_col
|  row-size=13B cardinality=1
|
05:EXCHANGE [HASH(int_col,bool_col)]
|
02:AGGREGATE [STREAMING]
|  output: count(*)
|  group by: int_col, bool_col
|  row-size=13B cardinality=1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 1
   HDFS partitions=1/4 files=1 size=115B
   predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
   row-size=5B cardinality=1
====
// UNION ALL with only constant selects
select 1, 'a', NULL, 10.f
union all
select 2, 'b', NULL, 20.f
union all
select 3, 'c', NULL, 30.f
---- PLAN
PLAN-ROOT SINK
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
====
// UNION DISTINCT with only constant selects
select 1, 'a', NULL, 10.0f
union distinct
select 2, 'b', NULL, 20.0f
union distinct
select 3, 'c', NULL, 30.0f
---- PLAN
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
|  group by: 1, 'a', null, f
|  row-size=18B cardinality=3
|
00:UNION
   constant-operands=3
   row-size=18B cardinality=3
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
|  group by: 1, 'a', null, f
|  row-size=18B cardinality=3
|
00:UNION
   constant-operands=3
   row-size=18B cardinality=3
====
// UNION ALL/DISTINCT with mixed constant selects and non-constant selects
select 1, 'a', NULL, 10.f
union all
select int_col, string_col, bool_col, float_col from functional.alltypestiny
union distinct
select 3, 'c', NULL, 30.f
union all
select int_col, string_col, bool_col, float_col from functional.alltypestiny
---- PLAN
PLAN-ROOT SINK
|
03:UNION
|  pass-through-operands: all
|  row-size=21B cardinality=18
|
|--02:AGGREGATE [FINALIZE]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  00:UNION
|  |  constant-operands=2
|  |  pass-through-operands: all
|  |  row-size=21B cardinality=10
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=22B cardinality=8
|
04:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   row-size=22B cardinality=8
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
03:UNION
|  pass-through-operands: all
|  row-size=21B cardinality=18
|
|--06:AGGREGATE [FINALIZE]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  05:EXCHANGE [HASH(1,'a',null,f)]
|  |
|  02:AGGREGATE [STREAMING]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  00:UNION
|  |  constant-operands=2
|  |  pass-through-operands: all
|  |  row-size=21B cardinality=10
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=22B cardinality=8
|
04:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   row-size=22B cardinality=8
====
// UNION ALL/DISTINCT with mixed constant selects and non-constant selects and nested unions
(select 500
 union all
 (select int_col from functional.alltypestiny where year=2009 and month=2
  order by 1 limit 3
  union all
  select 500)
)
union distinct
select int_col from functional.alltypestiny where year=2009 and month=1
union all
select 503
---- PLAN
PLAN-ROOT SINK
|
05:UNION
|  constant-operands=1
|  pass-through-operands: all
|  row-size=4B cardinality=7
|
04:AGGREGATE [FINALIZE]
|  group by: 500
|  row-size=4B cardinality=6
|
00:UNION
|  constant-operands=2
|  pass-through-operands: 02
|  row-size=4B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=4B cardinality=2
|
02:TOP-N [LIMIT=3]
|  order by: int_col ASC
|  row-size=4B cardinality=2
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=4B cardinality=2
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
10:EXCHANGE [UNPARTITIONED]
|
05:UNION
|  constant-operands=1
|  pass-through-operands: all
|  row-size=4B cardinality=7
|
09:AGGREGATE [FINALIZE]
|  group by: 500
|  row-size=4B cardinality=6
|
08:EXCHANGE [HASH(500)]
|
04:AGGREGATE [STREAMING]
|  group by: 500
|  row-size=4B cardinality=6
|
00:UNION
|  constant-operands=2
|  pass-through-operands: 07
|  row-size=4B cardinality=6
|
|--03:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, `month` = 1
|     HDFS partitions=1/4 files=1 size=115B
|     row-size=4B cardinality=2
|
07:EXCHANGE [RANDOM]
|
06:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: int_col ASC
|  limit: 3
|
02:TOP-N [LIMIT=3]
|  order by: int_col ASC
|  row-size=4B cardinality=2
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, `month` = 2
   HDFS partitions=1/4 files=1 size=115B
   row-size=4B cardinality=2
====
// UNION ALL with only values statements
values(1, 'a', NULL, 10.f)
union all
values(2, 'b', NULL, 20.f)
union all
values(3, 'c', NULL, 30.f)
---- PLAN
PLAN-ROOT SINK
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
====
// UNION DISTINCT with only values statements
values(1, 'a', NULL, 10.f)
union distinct
values(2, 'b', NULL, 20.f)
union distinct
values(3, 'c', NULL, 30.f)
---- PLAN
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
|  group by: 1, 'a', null, f
|  row-size=15B cardinality=3
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
|  group by: 1, 'a', null, f
|  row-size=15B cardinality=3
|
00:UNION
   constant-operands=3
   row-size=15B cardinality=3
====
// UNION ALL/DISTINCT with mixed values statements and non-constant selects
values(1, 'a', NULL, 10.f)
union all
select int_col, string_col, bool_col, float_col from functional.alltypestiny
union distinct
values(3, 'c', NULL, 30.f)
union all
select int_col, string_col, bool_col, float_col from functional.alltypessmall
---- PLAN
PLAN-ROOT SINK
|
03:UNION
|  pass-through-operands: all
|  row-size=21B cardinality=110
|
|--02:AGGREGATE [FINALIZE]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  00:UNION
|  |  constant-operands=2
|  |  pass-through-operands: all
|  |  row-size=21B cardinality=10
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=22B cardinality=8
|
04:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   row-size=22B cardinality=100
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
07:EXCHANGE [UNPARTITIONED]
|
03:UNION
|  pass-through-operands: all
|  row-size=21B cardinality=110
|
|--06:AGGREGATE [FINALIZE]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  05:EXCHANGE [HASH(1,'a',null,f)]
|  |
|  02:AGGREGATE [STREAMING]
|  |  group by: 1, 'a', null, f
|  |  row-size=21B cardinality=10
|  |
|  00:UNION
|  |  constant-operands=2
|  |  pass-through-operands: all
|  |  row-size=21B cardinality=10
|  |
|  01:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=22B cardinality=8
|
04:SCAN HDFS [functional.alltypessmall]
   HDFS partitions=4/4 files=4 size=6.32KB
   row-size=22B cardinality=100
====
# all union output slots are being materialized even though none is referenced by
# the enclosing scope
select count(*) from (
  select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col,
      double_col, date_string_col, string_col, timestamp_col, year, month
    from functional.alltypes
  union distinct
  select 0,true,0,0,0,0,cast(0 as float),0,'01/01/09','0',
      cast('2009-01-01 00:00:00' as timestamp),2009,1
  union distinct
  select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col,
      double_col, date_string_col, string_col, timestamp_col, year, month
    from functional.alltypes
  union distinct
  select 1,false,1,1,1,10,cast(1.1 as float),10.1,'01/01/09','1',
      cast('2009-01-01 00:01:00' as timestamp),2009,1
  union distinct
  select 2,true,2,2,2,20,cast(2.2 as float),20.2,'01/01/09','2',
      cast('2009-01-01 00:02:00.10' as timestamp),2009,1
) x
---- PLAN
PLAN-ROOT SINK
|
04:AGGREGATE [FINALIZE]
|  output: count(*)
|  row-size=8B cardinality=1
|
03: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=14.60K
|
00:UNION
|  constant-operands=3
|  pass-through-operands: all
|  row-size=89B cardinality=14.60K
|
|--02:SCAN HDFS [functional.alltypes]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=89B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=89B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
08:AGGREGATE [FINALIZE]
|  output: count:merge(*)
|  row-size=8B cardinality=1
|
07:EXCHANGE [UNPARTITIONED]
|
04:AGGREGATE
|  output: count(*)
|  row-size=8B cardinality=1
|
06: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=14.60K
|
05:EXCHANGE [HASH(id,bool_col,tinyint_col,smallint_col,int_col,bigint_col,float_col,double_col,date_string_col,string_col,timestamp_col,year,month)]
|
03:AGGREGATE [STREAMING]
|  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=14.60K
|
00:UNION
|  constant-operands=3
|  pass-through-operands: all
|  row-size=89B cardinality=14.60K
|
|--02:SCAN HDFS [functional.alltypes]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=89B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=89B cardinality=7.30K
====
# UNION ALL in subquery with a WHERE condition in the outer select;
# Where clause conjuncts are used as scan predicates and for partition pruning.
select x.int_col, x.bool_col, x.month from
  (select * from functional.alltypestiny where year=2009
   union all
   select * from functional.alltypestiny where year=2009) x
where x.int_col < 5 and x.bool_col = false and x.month = 1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=9B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, functional.alltypestiny.month = 1
|     HDFS partitions=1/4 files=1 size=115B
|     predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
|     row-size=9B cardinality=1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, functional.alltypestiny.month = 1
   HDFS partitions=1/4 files=1 size=115B
   predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
   row-size=9B cardinality=1
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
03:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  pass-through-operands: all
|  row-size=9B cardinality=2
|
|--02:SCAN HDFS [functional.alltypestiny]
|     partition predicates: `year` = 2009, functional.alltypestiny.month = 1
|     HDFS partitions=1/4 files=1 size=115B
|     predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
|     row-size=9B cardinality=1
|
01:SCAN HDFS [functional.alltypestiny]
   partition predicates: `year` = 2009, functional.alltypestiny.month = 1
   HDFS partitions=1/4 files=1 size=115B
   predicates: functional.alltypestiny.int_col < 5, functional.alltypestiny.bool_col = FALSE
   row-size=9B cardinality=1
====
# Regression test for IMPALA-888. Tests proper materialization of slots in
# the presence of conjuncts that become constant for union operands.
select 1 from
  (select 1 a, 2 b, int_col c, bigint_col d from functional.alltypestiny
   union all
   select tinyint_col, int_col, smallint_col, bigint_col from functional.alltypes
   union all
   select 1, tinyint_col, 3, bigint_col from functional.alltypessmall) t
where a + b < 100 and c > 20 and d > 50
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  row-size=0B cardinality=731
|
|--02:SCAN HDFS [functional.alltypes]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     predicates: functional.alltypes.bigint_col > 50, functional.alltypes.smallint_col > 20, functional.alltypes.tinyint_col + functional.alltypes.int_col < 100
|     row-size=15B cardinality=730
|
01:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   predicates: functional.alltypestiny.bigint_col > 50, functional.alltypestiny.int_col > 20
   row-size=12B cardinality=1
====
# Test union where all operands are dropped because of constant conjuncts.
select * from
  (select 1 a, 2 b
   union all
   select 3, 4
   union all
   select 10, 20) t
where a > b
---- PLAN
PLAN-ROOT SINK
|
00:UNION
   row-size=2B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
00:UNION
   row-size=2B cardinality=0
====
# Test union merging only partitioned inputs.
select id, bigint_col from functional.alltypestiny
union all
select sum(int_col), bigint_col from functional.alltypes
  where year=2009 and month=2
  group by bigint_col
union all
select a.id, a.bigint_col
  from functional.alltypestiny a inner join functional.alltypestiny b
  on (a.id = b.id)
union all
select 1000, 2000
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=27
|
|--06:HASH JOIN [INNER JOIN]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF000 <- b.id
|  |  row-size=16B cardinality=8
|  |
|  |--05:SCAN HDFS [functional.alltypestiny b]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=4B cardinality=8
|  |
|  04:SCAN HDFS [functional.alltypestiny a]
|     HDFS partitions=4/4 files=4 size=460B
|     runtime filters: RF000 -> a.id
|     row-size=12B cardinality=8
|
|--03:AGGREGATE [FINALIZE]
|  |  output: sum(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  02:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/24 files=1 size=18.12KB
|     row-size=12B cardinality=280
|
01:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   row-size=12B cardinality=8
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
11:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=27
|
|--06:HASH JOIN [INNER JOIN, PARTITIONED]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF000 <- b.id
|  |  row-size=16B cardinality=8
|  |
|  |--10:EXCHANGE [HASH(b.id)]
|  |  |
|  |  05:SCAN HDFS [functional.alltypestiny b]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=4B cardinality=8
|  |
|  09:EXCHANGE [HASH(a.id)]
|  |
|  04:SCAN HDFS [functional.alltypestiny a]
|     HDFS partitions=4/4 files=4 size=460B
|     runtime filters: RF000 -> a.id
|     row-size=12B cardinality=8
|
|--08:AGGREGATE [FINALIZE]
|  |  output: sum:merge(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  07:EXCHANGE [HASH(bigint_col)]
|  |
|  03:AGGREGATE [STREAMING]
|  |  output: sum(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  02:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/24 files=1 size=18.12KB
|     row-size=12B cardinality=280
|
01:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   row-size=12B cardinality=8
====
# Test union merging only unpartitioned inputs.
select count(id), sum(bigint_col) from functional.alltypes
union all
select id, bigint_col from functional.alltypessmall order by id limit 10
union all
select 1000, 2000
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=12
|
|--04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=12B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=12B cardinality=100
|
02:AGGREGATE [FINALIZE]
|  output: count(id), sum(bigint_col)
|  row-size=16B cardinality=1
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=12B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=12
|
|--07:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: id ASC
|  |  limit: 10
|  |
|  04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=12B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=12B cardinality=100
|
06:AGGREGATE [FINALIZE]
|  output: count:merge(id), sum:merge(bigint_col)
|  row-size=16B cardinality=1
|
05:EXCHANGE [UNPARTITIONED]
|
02:AGGREGATE
|  output: count(id), sum(bigint_col)
|  row-size=16B cardinality=1
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=12B cardinality=7.30K
====
# Test union merging mixed unpartitioned and partitioned inputs.
select count(id), sum(bigint_col) from functional.alltypes
union all
select id, bigint_col from functional.alltypessmall order by id limit 10
union all
select id, bigint_col from functional.alltypestiny
union all
select sum(int_col), bigint_col from functional.alltypes
  where year=2009 and month=2
  group by bigint_col
union all
select a.id, a.bigint_col
  from functional.alltypestiny a inner join functional.alltypestiny b
  on (a.id = b.id)
union all
select 1000, 2000
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=38
|
|--10:HASH JOIN [INNER JOIN]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF000 <- b.id
|  |  row-size=16B cardinality=8
|  |
|  |--09:SCAN HDFS [functional.alltypestiny b]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=4B cardinality=8
|  |
|  08:SCAN HDFS [functional.alltypestiny a]
|     HDFS partitions=4/4 files=4 size=460B
|     runtime filters: RF000 -> a.id
|     row-size=12B cardinality=8
|
|--07:AGGREGATE [FINALIZE]
|  |  output: sum(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  06:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/24 files=1 size=18.12KB
|     row-size=12B cardinality=280
|
|--05:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=12B cardinality=8
|
|--04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=12B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=12B cardinality=100
|
02:AGGREGATE [FINALIZE]
|  output: count(id), sum(bigint_col)
|  row-size=16B cardinality=1
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=12B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
20:EXCHANGE [UNPARTITIONED]
|
00:UNION
|  constant-operands=1
|  row-size=16B cardinality=38
|
|--10:HASH JOIN [INNER JOIN, PARTITIONED]
|  |  hash predicates: a.id = b.id
|  |  runtime filters: RF000 <- b.id
|  |  row-size=16B cardinality=8
|  |
|  |--17:EXCHANGE [HASH(b.id)]
|  |  |
|  |  09:SCAN HDFS [functional.alltypestiny b]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=4B cardinality=8
|  |
|  16:EXCHANGE [HASH(a.id)]
|  |
|  08:SCAN HDFS [functional.alltypestiny a]
|     HDFS partitions=4/4 files=4 size=460B
|     runtime filters: RF000 -> a.id
|     row-size=12B cardinality=8
|
|--15:AGGREGATE [FINALIZE]
|  |  output: sum:merge(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  14:EXCHANGE [HASH(bigint_col)]
|  |
|  07:AGGREGATE [STREAMING]
|  |  output: sum(int_col)
|  |  group by: bigint_col
|  |  row-size=16B cardinality=10
|  |
|  06:SCAN HDFS [functional.alltypes]
|     partition predicates: `year` = 2009, `month` = 2
|     HDFS partitions=1/24 files=1 size=18.12KB
|     row-size=12B cardinality=280
|
|--05:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=12B cardinality=8
|
|--19:EXCHANGE [RANDOM]
|  |
|  13:MERGING-EXCHANGE [UNPARTITIONED]
|  |  order by: id ASC
|  |  limit: 10
|  |
|  04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=12B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=12B cardinality=100
|
18:EXCHANGE [RANDOM]
|
12:AGGREGATE [FINALIZE]
|  output: count:merge(id), sum:merge(bigint_col)
|  row-size=16B cardinality=1
|
11:EXCHANGE [UNPARTITIONED]
|
02:AGGREGATE
|  output: count(id), sum(bigint_col)
|  row-size=16B cardinality=1
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=12B cardinality=7.30K
====
# IMPALA-3450: limits on union nodes are reflected in cardinality estimates. The test for
# this is embedded in PlannerTestBase.java and is not visible in these plans, as they only
# have explain_level=1
select * from tpch.lineitem UNION ALL (select * from tpch.lineitem) LIMIT 1
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  limit: 1
|  row-size=231B cardinality=1
|
|--02:SCAN HDFS [tpch.lineitem]
|     HDFS partitions=1/1 files=1 size=718.94MB
|     row-size=231B cardinality=6.00M
|
01:SCAN HDFS [tpch.lineitem]
   HDFS partitions=1/1 files=1 size=718.94MB
   row-size=231B cardinality=6.00M
====
select l_orderkey from tpch.lineitem UNION DISTINCT (select l_orderkey from tpch.lineitem) LIMIT 1
---- PLAN
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
|  group by: l_orderkey
|  limit: 1
|  row-size=8B cardinality=1
|
00:UNION
|  pass-through-operands: all
|  row-size=8B cardinality=12.00M
|
|--02:SCAN HDFS [tpch.lineitem]
|     HDFS partitions=1/1 files=1 size=718.94MB
|     row-size=8B cardinality=6.00M
|
01:SCAN HDFS [tpch.lineitem]
   HDFS partitions=1/1 files=1 size=718.94MB
   row-size=8B cardinality=6.00M
====
# IMPALA-3678: Predicates migrated into a union operand should be placed into
# a SelectNode if that union operand has an order by + limit.
select * from
  ((select * from functional.alltypes)
    union all
   (select * from functional.alltypes order by id)
    union all
   (select * from functional.alltypessmall order by id limit 10)
    union all
   (select * from functional.alltypestiny order by id limit 20 offset 10)) v
where v.id < 10 and v.int_col > 20
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=1.46K
|
|--08:SELECT
|  |  predicates: id < 10, int_col > 20
|  |  row-size=89B cardinality=1
|  |
|  07:TOP-N [LIMIT=20 OFFSET=10]
|  |  order by: id ASC
|  |  row-size=89B cardinality=8
|  |
|  06:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=89B cardinality=8
|
|--05:SELECT
|  |  predicates: id < 10, int_col > 20
|  |  row-size=89B cardinality=1
|  |
|  04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=89B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=89B cardinality=100
|
|--02:SCAN HDFS [functional.alltypes]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     predicates: functional.alltypes.id < 10, functional.alltypes.int_col > 20
|     row-size=89B cardinality=730
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   predicates: functional.alltypes.id < 10, functional.alltypes.int_col > 20
   row-size=89B cardinality=730
====
# IMPALA-3678: Same as above but with union distinct.
select * from
  ((select * from functional.alltypes)
    union distinct
   (select * from functional.alltypes order by id)
    union distinct
   (select * from functional.alltypessmall order by id limit 10)
    union distinct
   (select * from functional.alltypestiny order by id limit 20 offset 10)) v
where v.id < 10 and v.int_col > 20
---- PLAN
PLAN-ROOT SINK
|
09: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.46K
|
00:UNION
|  pass-through-operands: all
|  row-size=89B cardinality=1.46K
|
|--08:SELECT
|  |  predicates: id < 10, int_col > 20
|  |  row-size=89B cardinality=1
|  |
|  07:TOP-N [LIMIT=20 OFFSET=10]
|  |  order by: id ASC
|  |  row-size=89B cardinality=8
|  |
|  06:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=89B cardinality=8
|
|--05:SELECT
|  |  predicates: id < 10, int_col > 20
|  |  row-size=89B cardinality=1
|  |
|  04:TOP-N [LIMIT=10]
|  |  order by: id ASC
|  |  row-size=89B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypessmall]
|     HDFS partitions=4/4 files=4 size=6.32KB
|     row-size=89B cardinality=100
|
|--02:SCAN HDFS [functional.alltypes]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     predicates: functional.alltypes.id < 10, functional.alltypes.int_col > 20
|     row-size=89B cardinality=730
|
01:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   predicates: functional.alltypes.id < 10, functional.alltypes.int_col > 20
   row-size=89B cardinality=730
====
# IMPALA-3678: Both union operands produce rows with non-nullable slots which can be
# passed through.
select kudu_idx from functional_kudu.alltypesagg_idx limit 5
union all
select count(*) from functional.alltypestiny;
---- PLAN
PLAN-ROOT SINK
|
00:UNION
|  pass-through-operands: all
|  row-size=8B cardinality=6
|
|--03:AGGREGATE [FINALIZE]
|  |  output: count(*)
|  |  row-size=8B cardinality=1
|  |
|  02:SCAN HDFS [functional.alltypestiny]
|     HDFS partitions=4/4 files=4 size=460B
|     row-size=0B cardinality=8
|
01:SCAN KUDU [functional_kudu.alltypesagg_idx]
   limit: 5
   row-size=8B cardinality=5
====
# IMPALA-3678: The Union operands that contain a join should not be passed through,
# because the resulting row contains 2 tuples, instead of one. However, a semi join
# outputs a row with only 1 tuple, so it should be passed through.
select bigint_col from functional.alltypestiny
union
select t1.bigint_col from functional.alltypestiny t1 left semi join
  functional.alltypes t2 on t1.bigint_col = t2.bigint_col
union
select t1.bigint_col from functional.alltypestiny t1 left join
  functional.alltypes t2 on t1.bigint_col = t2.bigint_col
union
select t1.bigint_col from functional.alltypestiny t1 inner join
  functional.alltypes t2 on t1.bigint_col = t2.bigint_col
---- PLAN
PLAN-ROOT SINK
|
12:AGGREGATE [FINALIZE]
|  group by: bigint_col
|  row-size=8B cardinality=8
|
00:UNION
|  pass-through-operands: 01,05
|  row-size=8B cardinality=11.70K
|
|--11:HASH JOIN [INNER JOIN]
|  |  hash predicates: t2.bigint_col = t1.bigint_col
|  |  runtime filters: RF004 <- t1.bigint_col
|  |  row-size=16B cardinality=5.84K
|  |
|  |--09:SCAN HDFS [functional.alltypestiny t1]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=8B cardinality=8
|  |
|  10:SCAN HDFS [functional.alltypes t2]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF004 -> t2.bigint_col
|     row-size=8B cardinality=7.30K
|
|--08:HASH JOIN [RIGHT OUTER JOIN]
|  |  hash predicates: t2.bigint_col = t1.bigint_col
|  |  runtime filters: RF002 <- t1.bigint_col
|  |  row-size=16B cardinality=5.84K
|  |
|  |--06:SCAN HDFS [functional.alltypestiny t1]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=8B cardinality=8
|  |
|  07:SCAN HDFS [functional.alltypes t2]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF002 -> t2.bigint_col
|     row-size=8B cardinality=7.30K
|
|--05:HASH JOIN [RIGHT SEMI JOIN]
|  |  hash predicates: t2.bigint_col = t1.bigint_col
|  |  runtime filters: RF000 <- t1.bigint_col
|  |  row-size=8B cardinality=8
|  |
|  |--02:SCAN HDFS [functional.alltypestiny t1]
|  |     HDFS partitions=4/4 files=4 size=460B
|  |     row-size=8B cardinality=8
|  |
|  04:AGGREGATE [FINALIZE]
|  |  group by: t2.bigint_col
|  |  row-size=8B cardinality=10
|  |
|  03:SCAN HDFS [functional.alltypes t2]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     runtime filters: RF000 -> t2.bigint_col
|     row-size=8B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypestiny]
   HDFS partitions=4/4 files=4 size=460B
   row-size=8B cardinality=8
====
# IMPALA-3678: union in a subplan - passthrough should be disabled.
select count(c.c_custkey), count(v.tot_price)
from tpch_nested_parquet.customer c, (
  select sum(o_totalprice) tot_price from c.c_orders
  union
  select sum(o_totalprice) tot_price from c.c_orders) v;
---- PLAN
PLAN-ROOT SINK
|
10:AGGREGATE [FINALIZE]
|  output: count(c.c_custkey), count(tot_price)
|  row-size=16B cardinality=1
|
01:SUBPLAN
|  row-size=48B cardinality=300.00K
|
|--09:NESTED LOOP JOIN [CROSS JOIN]
|  |  row-size=48B cardinality=2
|  |
|  |--02:SINGULAR ROW SRC
|  |     row-size=32B cardinality=1
|  |
|  08:AGGREGATE [FINALIZE]
|  |  group by: tot_price
|  |  row-size=16B cardinality=2
|  |
|  03:UNION
|  |  row-size=16B cardinality=2
|  |
|  |--07:AGGREGATE [FINALIZE]
|  |  |  output: sum(o_totalprice)
|  |  |  row-size=16B cardinality=1
|  |  |
|  |  06:UNNEST [c.c_orders]
|  |     row-size=0B cardinality=10
|  |
|  05:AGGREGATE [FINALIZE]
|  |  output: sum(o_totalprice)
|  |  row-size=16B cardinality=1
|  |
|  04:UNNEST [c.c_orders]
|     row-size=0B cardinality=10
|
00:SCAN HDFS [tpch_nested_parquet.customer c]
   HDFS partitions=1/1 files=4 size=289.08MB
   row-size=32B cardinality=150.00K
====
# IMPALA-6388: Verify that the order of the union operands does not impact the
# number of hosts computation
select f2 from functional.emptytable
union all
select int_col from functional.alltypes
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=33.50KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: f2
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
03:EXCHANGE [UNPARTITIONED]
|  mem-estimate=33.50KB mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=7.30K
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=128.00MB mem-reservation=32.00KB thread-reservation=2
00:UNION
|  pass-through-operands: all
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=7.30K
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypes, RANDOM]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     stored statistics:
|       table: rows=7.30K size=478.45KB
|       partitions: 24/24 rows=7.30K
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=310
|     mem-estimate=128.00MB mem-reservation=32.00KB thread-reservation=1
|     tuple-ids=1 row-size=4B cardinality=7.30K
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.emptytable, RANDOM]
   partitions=0/0 files=0 size=0B
   stored statistics:
     table: rows=unavailable size=unavailable
     partitions: 0/0 rows=0
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=0
   mem-estimate=0B mem-reservation=0B thread-reservation=0
   tuple-ids=0 row-size=4B cardinality=0
   in pipelines: 01(GETNEXT)
====
# IMPALA-6388: Verify that the order of the union operands does not impact the
# number of hosts computation
select int_col from functional.alltypes
union all
select f2 from functional.emptytable
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=33.50KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: int_col
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
03:EXCHANGE [UNPARTITIONED]
|  mem-estimate=33.50KB mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=7.30K
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=128.00MB mem-reservation=32.00KB thread-reservation=2
00:UNION
|  pass-through-operands: all
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=7.30K
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.emptytable, RANDOM]
|     partitions=0/0 files=0 size=0B
|     stored statistics:
|       table: rows=unavailable size=unavailable
|       partitions: 0/0 rows=0
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=0
|     mem-estimate=0B mem-reservation=0B thread-reservation=0
|     tuple-ids=1 row-size=4B cardinality=0
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypes, RANDOM]
   HDFS partitions=24/24 files=24 size=478.45KB
   stored statistics:
     table: rows=7.30K size=478.45KB
     partitions: 24/24 rows=7.30K
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=310
   mem-estimate=128.00MB mem-reservation=32.00KB thread-reservation=1
   tuple-ids=0 row-size=4B cardinality=7.30K
   in pipelines: 01(GETNEXT)
====
#IMPALA-8533: Analytic ordering columns are constant after union substitution
WITH
base_10 AS (SELECT 1 UNION ALL SELECT 1),
base_10k AS (SELECT 2 constant FROM base_10 b1)
SELECT ROW_NUMBER() OVER (ORDER BY b1.constant) row_num
FROM base_10k b1;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=4.00MB mem-reservation=4.00MB thread-reservation=1
PLAN-ROOT SINK
|  output exprs: row_number()
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
01:ANALYTIC
|  functions: row_number()
|  order by: 2 ASC
|  window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|  mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=0,4 row-size=8B cardinality=2
|  in pipelines: <none>
|
00:UNION
   constant-operands=2
   mem-estimate=0B mem-reservation=0B thread-reservation=0
   tuple-ids=0 row-size=0B cardinality=2
   in pipelines: <none>
====
#IMPALA-8533: Ordering on select same constant from all union arms.
# Sort not currently eliminated.
SELECT c1 FROM (SELECT 1 c1 UNION ALL SELECT 1 c1) a ORDER BY a.c1;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=6.00MB mem-reservation=6.00MB thread-reservation=1
PLAN-ROOT SINK
|  output exprs: c1
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
01:SORT
|  order by: c1 ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=2 row-size=1B cardinality=2
|  in pipelines: 01(GETNEXT)
|
00:UNION
   constant-operands=2
   mem-estimate=0B mem-reservation=0B thread-reservation=0
   tuple-ids=0 row-size=1B cardinality=2
   in pipelines: <none>
====
#IMPALA-8533: Ordering on select constant union select same constant from table
# Sort not currently eliminated.
SELECT c1 FROM (SELECT 1 c1 FROM functional.alltypestiny UNION ALL SELECT 1 c1 FROM functional.alltypestiny) a ORDER BY c1;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: c1
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
04:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: c1 ASC
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=4 row-size=1B cardinality=16
|  in pipelines: 03(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=38.00MB mem-reservation=6.01MB thread-reservation=2
03:SORT
|  order by: c1 ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=4 row-size=1B cardinality=16
|  in pipelines: 03(GETNEXT), 01(OPEN), 02(OPEN)
|
00:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=1B cardinality=16
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypestiny, RANDOM]
|     HDFS partitions=4/4 files=4 size=460B
|     stored statistics:
|       table: rows=8 size=460B
|       partitions: 4/4 rows=8
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=2
|     mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
|     tuple-ids=1 row-size=0B cardinality=8
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypestiny, RANDOM]
   HDFS partitions=4/4 files=4 size=460B
   stored statistics:
     table: rows=8 size=460B
     partitions: 4/4 rows=8
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=2
   mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
   tuple-ids=0 row-size=0B cardinality=8
   in pipelines: 01(GETNEXT)
====
#IMPALA-8533: Select constant from unioned query.
# Sort not currently eliminated.
SELECT 1 FROM (SELECT int_col FROM functional.alltypestiny UNION ALL SELECT int_col FROM functional.alltypestiny) a ORDER BY int_col;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: CAST(1 AS TINYINT)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
04:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: int_col ASC
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=4 row-size=4B cardinality=16
|  in pipelines: 03(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=38.00MB mem-reservation=6.01MB thread-reservation=2
03:SORT
|  order by: int_col ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=4 row-size=4B cardinality=16
|  in pipelines: 03(GETNEXT), 01(OPEN), 02(OPEN)
|
00:UNION
|  pass-through-operands: all
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=16
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypestiny, RANDOM]
|     HDFS partitions=4/4 files=4 size=460B
|     stored statistics:
|       table: rows=8 size=460B
|       partitions: 4/4 rows=8
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=2
|     mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
|     tuple-ids=1 row-size=4B cardinality=8
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypestiny, RANDOM]
   HDFS partitions=4/4 files=4 size=460B
   stored statistics:
     table: rows=8 size=460B
     partitions: 4/4 rows=8
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=2
   mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
   tuple-ids=0 row-size=4B cardinality=8
   in pipelines: 01(GETNEXT)
====
#IMPALA-8533: Select constant from select constant union select same constant
# Sort not currently eliminated.
SELECT 1 FROM (SELECT 1 c1 FROM functional.alltypestiny UNION ALL SELECT 1 c1 FROM functional.alltypestiny) a ORDER BY c1;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: CAST(1 AS TINYINT)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
04:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: c1 ASC
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=4 row-size=1B cardinality=16
|  in pipelines: 03(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=38.00MB mem-reservation=6.01MB thread-reservation=2
03:SORT
|  order by: c1 ASC
|  mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
|  tuple-ids=4 row-size=1B cardinality=16
|  in pipelines: 03(GETNEXT), 01(OPEN), 02(OPEN)
|
00:UNION
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=1B cardinality=16
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypestiny, RANDOM]
|     HDFS partitions=4/4 files=4 size=460B
|     stored statistics:
|       table: rows=8 size=460B
|       partitions: 4/4 rows=8
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=2
|     mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
|     tuple-ids=1 row-size=0B cardinality=8
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypestiny, RANDOM]
   HDFS partitions=4/4 files=4 size=460B
   stored statistics:
     table: rows=8 size=460B
     partitions: 4/4 rows=8
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=2
   mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
   tuple-ids=0 row-size=0B cardinality=8
   in pipelines: 01(GETNEXT)
====
#IMPALA-8533: Select constant from union order by 1
# Sort is eliminated.
SELECT 1 FROM (SELECT int_col c1 FROM functional.alltypestiny UNION ALL SELECT int_col c1 FROM functional.alltypestiny) a ORDER BY 1;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: CAST(1 AS TINYINT)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
03:EXCHANGE [UNPARTITIONED]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=0B cardinality=16
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=2
00:UNION
|  pass-through-operands: all
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=0B cardinality=16
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypestiny, RANDOM]
|     HDFS partitions=4/4 files=4 size=460B
|     stored statistics:
|       table: rows=8 size=460B
|       partitions: 4/4 rows=8
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=2
|     mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
|     tuple-ids=1 row-size=0B cardinality=8
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypestiny, RANDOM]
   HDFS partitions=4/4 files=4 size=460B
   stored statistics:
     table: rows=8 size=460B
     partitions: 4/4 rows=8
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=2
   mem-estimate=32.00MB mem-reservation=8.00KB thread-reservation=1
   tuple-ids=0 row-size=0B cardinality=8
   in pipelines: 01(GETNEXT)
====
# IMPALA-9429: Test partition elimination merging inputs with different
# partitioning
select month from (
    select year, month from functional.alltypes
    union all
    select year, 1 month from functional.alltypesagg
  ) a where year = 0 or month = 0;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: month
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
03:EXCHANGE [UNPARTITIONED]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=0
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
Per-Host Resources: mem-estimate=0B mem-reservation=0B thread-reservation=1
00:UNION
|  pass-through-operands: 01
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=0
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypesagg, RANDOM]
|     partition predicates: `year` = CAST(0 AS INT) OR FALSE
|     partitions=0/11 files=0 size=0B
|     stored statistics:
|       table: rows=11.00K size=814.73KB
|       partitions: 0/0 rows=unavailable
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=0
|     mem-estimate=0B mem-reservation=0B thread-reservation=0
|     tuple-ids=1 row-size=0B cardinality=0
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypes, RANDOM]
   partition predicates: functional.alltypes.year = CAST(0 AS INT) OR functional.alltypes.month = CAST(0 AS INT)
   partitions=0/24 files=0 size=0B
   stored statistics:
     table: rows=7.30K size=478.45KB
     partitions: 0/0 rows=unavailable
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=0
   mem-estimate=0B mem-reservation=0B thread-reservation=0
   tuple-ids=0 row-size=4B cardinality=0
   in pipelines: 01(GETNEXT)
====
select month from (
    select year, month from functional.alltypes
    union all
    select year, 1 month from functional.alltypesagg
  ) a where year = NULL or month = NULL;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F03:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=16.00KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: month
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
03:EXCHANGE [UNPARTITIONED]
|  mem-estimate=16.00KB mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=0
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
F02:PLAN FRAGMENT [RANDOM] hosts=1 instances=1
Per-Host Resources: mem-estimate=0B mem-reservation=0B thread-reservation=1
00:UNION
|  pass-through-operands: 01
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|  tuple-ids=2 row-size=4B cardinality=0
|  in pipelines: 01(GETNEXT), 02(GETNEXT)
|
|--02:SCAN HDFS [functional.alltypesagg, RANDOM]
|     partition predicates: `year` = NULL OR NULL
|     partitions=0/11 files=0 size=0B
|     stored statistics:
|       table: rows=11.00K size=814.73KB
|       partitions: 0/0 rows=unavailable
|       columns: all
|     extrapolated-rows=disabled max-scan-range-rows=0
|     mem-estimate=0B mem-reservation=0B thread-reservation=0
|     tuple-ids=1 row-size=0B cardinality=0
|     in pipelines: 02(GETNEXT)
|
01:SCAN HDFS [functional.alltypes, RANDOM]
   partition predicates: functional.alltypes.year = NULL OR functional.alltypes.month = NULL
   partitions=0/24 files=0 size=0B
   stored statistics:
     table: rows=7.30K size=478.45KB
     partitions: 0/0 rows=unavailable
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=0
   mem-estimate=0B mem-reservation=0B thread-reservation=0
   tuple-ids=0 row-size=4B cardinality=0
   in pipelines: 01(GETNEXT)
====
select year, month from (
   select year, 1 month from functional.alltypesagg
 ) t where year = 2010 or month = 0;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F01:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=38.32KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: `year`, CAST(1 AS TINYINT)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
01:EXCHANGE [UNPARTITIONED]
|  mem-estimate=38.32KB mem-reservation=0B thread-reservation=0
|  tuple-ids=0 row-size=4B cardinality=11.00K
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=80.00MB mem-reservation=128.00KB thread-reservation=2
00:SCAN HDFS [functional.alltypesagg, RANDOM]
   partition predicates: `year` = CAST(2010 AS INT) OR FALSE
   HDFS partitions=11/11 files=11 size=814.73KB
   stored statistics:
     table: rows=11.00K size=814.73KB
     partitions: 11/11 rows=11.00K
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=1.00K
   mem-estimate=80.00MB mem-reservation=128.00KB thread-reservation=1
   tuple-ids=0 row-size=4B cardinality=11.00K
   in pipelines: 00(GETNEXT)
====
select year, month from (
   select year, 1 month from functional.alltypesagg
 ) t where year = 2010 or month between 1 and 2;
---- QUERYOPTIONS
explain_level=2
---- DISTRIBUTEDPLAN
F01:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
|  Per-Host Resources: mem-estimate=38.32KB mem-reservation=0B thread-reservation=1
PLAN-ROOT SINK
|  output exprs: `year`, CAST(1 AS TINYINT)
|  mem-estimate=0B mem-reservation=0B thread-reservation=0
|
01:EXCHANGE [UNPARTITIONED]
|  mem-estimate=38.32KB mem-reservation=0B thread-reservation=0
|  tuple-ids=0 row-size=4B cardinality=11.00K
|  in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=3 instances=3
Per-Host Resources: mem-estimate=80.00MB mem-reservation=128.00KB thread-reservation=2
00:SCAN HDFS [functional.alltypesagg, RANDOM]
   partition predicates: `year` = CAST(2010 AS INT) OR TRUE
   HDFS partitions=11/11 files=11 size=814.73KB
   stored statistics:
     table: rows=11.00K size=814.73KB
     partitions: 11/11 rows=11.00K
     columns: all
   extrapolated-rows=disabled max-scan-range-rows=1.00K
   mem-estimate=80.00MB mem-reservation=128.00KB thread-reservation=1
   tuple-ids=0 row-size=4B cardinality=11.00K
   in pipelines: 00(GETNEXT)
====