====
---- QUERY
# Showing contents of alltypestiny for convenience
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 alltypestiny
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3
6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4
7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4
====
---- QUERY
# Only UNION ALL, no nested unions
# IMPALA-3586: In this test, all union nodes are passed through.
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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=2
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Only UNION ALL with limit inside operands. One of the operands also has an order by.
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 alltypestiny where year=2009 and month=1 and id = 0 limit 1
union all
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 alltypestiny where year=2009 and month=1 order by int_col limit 1
union all
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 alltypestiny where year=2009 and month=2 and id = 2 limit 1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Same UNION ALL with limits containing arithmetic expressions (that all evaluate to 1).
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 alltypestiny where year=2009 and month=1 order by id limit 3-2
union all
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 alltypestiny where year=2009 and month=1 order by int_col limit 1*1*1
union all
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 alltypestiny where year=2009 and month=2 order by id limit 1&1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Only UNION DISTINCT, no nested unions
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Only UNION ALL, mixed selects with and without from clauses, no nested unions
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 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 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 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
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.1,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Only UNION DISTINCT, mixed selects with and without from clauses, no nested unions
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 alltypestiny where year=2009 and month=1
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 alltypestiny where year=2009 and month=1
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
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Mixed UNION ALL/DISTINCT but effectively only UNION DISTINCT, no nested unions,
# with order by and limit
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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=2
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 alltypestiny where year=2009 and month=2)
order by 1 limit 3
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=2
union all
(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 alltypestiny where year=2009 and month=2)
order by 1,2 limit 3
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit
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 alltypestiny where year=2009 and month=1
union all
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
union all
(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 alltypestiny where year=2009 and month=2)
order by 1,2 limit 4
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Union unnesting: Only UNION ALL, first operand is nested
(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 alltypestiny where year=2009 and month=1
 union all
 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 alltypestiny where year=2009 and month=2)
union all
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: Only UNION ALL, second operand is nested
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=1
   union all
   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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: Only UNION DISTINCT, first operand is nested
(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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2)
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: Only UNION DISTINCT, second operand is nested
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION ALL doesn't absorb nested union with DISTINCT,
# first operand is nested
(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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2)
union all
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION ALL doesn't absorb nested union with DISTINCT,
# second operand is nested
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# 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 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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
 union all
 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 alltypestiny where year=2009 and month=2)
union all
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# 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 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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
   union all
   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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION ALL doesn't absorb the children of a nested union
# with mixed ALL/DISTINCT and limit, second operand is nested
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
   union all
   (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 alltypestiny where year=2009 and month=2)
   limit 10)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION ALL doesn't absorb nested union with order by and limit,
# first operand is nested
(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 alltypestiny where year=2009 and month=1
 union all
 (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 alltypestiny where year=2009 and month=2)
 order by 1 limit 3)
union all
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Union unnesting: UNION ALL doesn't absorb nested union with order by and limit,
# second operand is nested
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=1
   union all
   (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 alltypestiny where year=2009 and month=2)
   order by 1 limit 3)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# Union unnesting: UNION DISTINCT absorbs nested union with ALL
# first operand is nested
(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 alltypestiny where year=2009 and month=1
 union all
 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 alltypestiny where year=2009 and month=2)
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION DISTINCT absorbs nested union with ALL,
# second operand is nested
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=1
   union all
   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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION DISTINCT absorbs nested union with mixed ALL/DISTINCT,
# first operand is nested
(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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
 union all
 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 alltypestiny where year=2009 and month=2)
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION DISTINCT absorbs nested union with mixed ALL/DISTINCT,
# second operand is nested
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
   union all
   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 alltypestiny where year=2009 and month=2)
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Union unnesting: UNION DISTINCT doesn't absorb nested union with order by and limit,
# first operand is nested
(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 alltypestiny where year=2009 and month=1
 union all
 (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 alltypestiny where year=2009 and month=2)
 order by 1 limit 3)
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Union unnesting: UNION DISTINCT doesn't absorb nested union with order by and limit,
# first operand is nested
(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 alltypestiny where year=2009 and month=1
 union all
 (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 alltypestiny where year=2009 and month=2)
 order by 1 limit 3)
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 alltypestiny where year=2009 and month=1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Complex union unnesting: Multiple levels of UNION ALL, fully unnestable
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=1
   union all
     (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 alltypestiny where year=2009 and month=2
      union all
        (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 alltypestiny where year=2009 and month=2
         union all
         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 alltypestiny where year=2009 and month=3)))
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3
====
---- QUERY
# Complex union unnesting: Multiple levels of UNION DISTINCT, fully unnestable
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
      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 alltypestiny where year=2009 and month=2
         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 alltypestiny where year=2009 and month=3)))
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3
====
---- QUERY
# Complex union unnesting: Partially unnestable up to 2nd level
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 alltypestiny where year=2009 and month=1
union all
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
      union all
        (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 alltypestiny where year=2009 and month=2
         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 alltypestiny where year=2009 and month=3)
         order by 1 limit 3)))
order by 12, 13, 1
limit 20
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
====
---- QUERY
# Complex union unnesting: Partially unnestable up to 1st level
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=2
      union all
        (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 alltypestiny where year=2009 and month=2
         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 alltypestiny where year=2009 and month=3)
         order by 1 limit 3)))
order by 12, 13, 1
limit 20
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
====
---- QUERY
# Complex union unnesting: Multiple nested unions to test all rules in a single query
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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=1
   union all
   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 alltypestiny where year=2009 and month=2)
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 alltypestiny where year=2009 and month=2
   union all
   (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 alltypestiny where year=2009 and month=3)
   order by 1 limit 3)
union all
  (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 alltypestiny where year=2009 and month=3
   union all
   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 alltypestiny where year=2009 and month=4)
union all
  (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 alltypestiny where year=2009 and month=4
   union all
   (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 alltypestiny where year=2009 and month=5)
   order by 1 limit 3)
order by 12, 13, 1
limit 20
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3
5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3
6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4
6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4
7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4
7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4
====
---- QUERY
# UNION ALL in subquery
select x.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
  (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 alltypestiny where year=2009 and month=1
   union all
   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 alltypestiny where year=2009 and month=1) x
union all
(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 alltypestiny where year=2009 and month=2)
order by 1 limit 5
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# UNION DISTINCT in subquery
select x.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
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=1) x
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 alltypestiny where year=2009 and month=2)
order by 1 limit 3
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2
====
---- QUERY
# UNION ALL in subquery with a WHERE condition in the outer select.
select x.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
  (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 alltypestiny where year=2009 and month=1
   union all
   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 alltypestiny where year=2009 and month=1) x
where x.int_col < 5 and x.bool_col = false
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# UNION DISTINCT in subquery with a WHERE condition in the outer select.
select x.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
  (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 alltypestiny where year=2009 and month=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 alltypestiny where year=2009 and month=1) x
where x.int_col < 5 and x.bool_col = false
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# Test UNION ALL with mixed constant and non-constant selects. Also tests implicit casts.
select 10, 10.0, "abcde"
union all
select int_col, float_col, string_col
from alltypestiny where year=2009 and month=1
union all
select 20, 20.0, "fghijkl"
union all
select tinyint_col, double_col, string_col
from alltypestiny where year=2009 and month=1
---- TYPES
int, double, string
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0,'0'
0,0,'0'
1,1.100000023841858,'1'
1,10.1,'1'
10,10,'abcde'
20,20,'fghijkl'
====
---- QUERY
# Test UNION ALL on large tables with a few constant selects to excercise backend logic.
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 alltypes
  union all
  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 all
  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 alltypes
  union all
  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 all
  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
---- TYPES
bigint
---- RESULTS
14603
====
---- QUERY
# Test UNION DISTINCT on large tables with a few constant selects to excercise backend logic.
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 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 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
---- TYPES
bigint
---- RESULTS
7300
====
---- QUERY
# Test UNION DISTINCT on large table constant selects and values statements
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 alltypes
  union distinct
  values(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 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
  values(2,true,2,2,2,20,cast(2.2 as float),cast(20.2 as double),'01/01/09','2',cast('2009-01-01 00:02:00.10' as timestamp),2009,1)
) x
---- TYPES
bigint
---- RESULTS
7300
====
---- QUERY
# UNION ALL with only constant selects
select 1, 'a', NULL, 10.0
union all
select 2, 'b', NULL, 20.0
union all
select 3, 'c', NULL, 30.0
---- TYPES
tinyint, string, null, decimal
---- HS2_TYPES
tinyint, string, boolean, decimal
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,'a',NULL,10.0
2,'b',NULL,20.0
3,'c',NULL,30.0
====
---- QUERY
# UNION DISTINCT with only constant selects
select 1, 'a', NULL, 10.0
union distinct
select 2, 'b', NULL, 20.0
union distinct
select 1, 'a', NULL, 10.0
---- TYPES
tinyint, string, null, decimal
---- HS2_TYPES
tinyint, string, boolean, decimal
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,'a',NULL,10.0
2,'b',NULL,20.0
====
---- QUERY
# UNION ALL with values statements
values(1, 'a', NULL, 10.0)
union all
values(2, 'b', NULL, 20.0)
union all
values(3, 'c', NULL, 30.0)
---- TYPES
tinyint, string, null, decimal
---- HS2_TYPES
tinyint, string, boolean, decimal
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,'a',NULL,10.0
2,'b',NULL,20.0
3,'c',NULL,30.0
====
---- QUERY
# UNION DISTINCT with values statements
values(1, 'a', NULL, 10.0)
union distinct
values(2, 'b', NULL, 20.0)
union distinct
values(1, 'a', NULL, 10.0)
---- TYPES
tinyint, string, null, decimal
---- HS2_TYPES
tinyint, string, boolean, decimal
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,'a',NULL,10.0
2,'b',NULL,20.0
====
---- QUERY
# Test UNION DISTINCT with GROUP BY
select id, count(*) from alltypes where id = 1 group by id
union distinct
select 2, 3
---- TYPES
int, bigint
---- RESULTS
1,1
2,3
====
---- QUERY
# IMPALA-843: Impalads crash while running join on string column + union
select count(*) from
(select 1 FROM alltypes AS t1 JOIN alltypestiny AS t2 ON t1.string_col = t2.string_col
UNION ALL SELECT 1 FROM tinytable AS t1) as t3
---- TYPES
bigint
---- RESULTS
5843
====
---- QUERY
# 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
---- TYPES
tinyint, tinyint
---- RESULTS
====
---- QUERY
# Test union merging only partitioned inputs.
select id, bigint_col from alltypestiny
union all
select sum(int_col), bigint_col from alltypes
  where year=2009 and month=2
  group by bigint_col
union all
select a.id, a.bigint_col
  from alltypestiny a inner join alltypestiny b
  on (a.id = b.id)
union all
(select 1000, 2000)
order by 1, 2
---- TYPES
bigint, bigint
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0
0,0
0,0
1,10
1,10
2,0
2,0
3,10
3,10
4,0
4,0
5,10
5,10
6,0
6,0
7,10
7,10
28,10
56,20
84,30
112,40
140,50
168,60
196,70
224,80
252,90
1000,2000
====
---- QUERY
# Test union merging only unpartitioned inputs.
select id, bigint_col from alltypestiny
union all
select sum(int_col), bigint_col from alltypes
  where year=2009 and month=2
  group by bigint_col
union all
select a.id, a.bigint_col
  from alltypestiny a inner join alltypestiny b
  on (a.id = b.id)
union all
select 1000, 2000
---- TYPES
bigint, bigint
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0
0,0
0,0
1,10
1,10
2,0
2,0
3,10
3,10
4,0
4,0
5,10
5,10
6,0
6,0
7,10
7,10
28,10
56,20
84,30
112,40
140,50
168,60
196,70
224,80
252,90
1000,2000
====
---- QUERY
# Test union merging only unpartitioned inputs.
select count(id), sum(bigint_col) from alltypes
union all
select id, bigint_col from alltypessmall order by id limit 10
union all
select id, bigint_col from alltypestiny
union all
select sum(int_col), bigint_col from alltypes
  where year=2009 and month=2
  group by bigint_col
union all
select a.id, a.bigint_col
  from alltypestiny a inner join alltypestiny b
  on (a.id = b.id)
union all
select 1000, 2000
---- TYPES
bigint, bigint
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,0
0,0
0,0
0,0
1,10
1,10
1,10
2,0
2,0
2,20
3,10
3,10
3,30
4,0
4,0
4,40
5,10
5,10
5,50
6,0
6,0
6,60
7,10
7,10
7,70
8,80
9,90
28,10
56,20
84,30
112,40
140,50
168,60
196,70
224,80
252,90
1000,2000
7300,328500
====
---- QUERY
# IMPALA-1340 regression test
select cast(1 as bigint)
union all select t1.tinyint_col from alltypestiny t1
inner join alltypestiny t2 on t2.string_col = t1.string_col
---- TYPES
bigint
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0
0
0
0
1
1
1
1
0
0
0
0
1
1
1
1
0
0
0
0
1
1
1
1
0
0
0
0
1
1
1
1
1
====
---- QUERY
# IMPALA-4336: Test proper result expr casting when unnesting union operands.
select double_col from alltypestiny union all (select 80 union all select 90)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0
0
0
0
10.1
10.1
10.1
10.1
80
90
---- TYPES
DOUBLE
=====
---- QUERY
# IMPALA-4336: Test proper result expr casting when unnesting union operands.
select double_col from alltypestiny union distinct (select 80 union all select 90)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
0
10.1
80
90
---- TYPES
DOUBLE
=====
---- QUERY
# Regression test for IMPALA-2948. When fast partition key scan is enabled,
# make sure union nodes formed contain correct constant expressions even if
# there are unmaterialized slots.
set optimize_partition_key_scans=1;
select t1.int_col
from alltypestiny t1 inner join
  (select count(t2.tinyint_col) = 1337 as boolean_col,
          min(t2.year) as int_col from alltypestiny t2) t3
on (t1.int_col = t3.int_col)
---- RESULTS
---- TYPES
INT
=====
---- QUERY
# IMPALA-3586: Verify that limit is obeyed in the passthrough case. This test
# is relevant if the execution is on a single node (if exec_single_node_rows_threshold
# large enough) because there is no exchange node above the union node to impose the
# limit.
select bigint_col from alltypestiny where bigint_col > 0
union all
(select bigint_col from alltypestiny where bigint_col > 0)
limit 1
---- RESULTS
10
---- TYPES
bigint
=====
---- QUERY
# IMPALA-3586: Input tuples that have non-nullable slots. Both operands should be passed
# through by the union node.
select count(*) from alltypestiny
union all
select count(*) from alltypestiny
---- RESULTS
8
8
---- TYPES
bigint
=====
---- QUERY
# IMPALA-3586: Both operands are not passed through.
select bigint_col + 1 from alltypestiny
union distinct
select bigint_col + 1 from alltypestiny
---- RESULTS
1
11
---- TYPES
bigint
=====
---- QUERY
# IMPALA-3586: One operand is passed through, the other is not.
select bigint_col + 1 from alltypestiny
union distinct
select bigint_col from alltypestiny
---- RESULTS
11
10
0
1
---- TYPES
bigint
=====
---- QUERY
# 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 alltypestiny
union
select t1.bigint_col from alltypestiny t1 left semi join
  alltypes t2 on t1.bigint_col = t2.bigint_col
union
select t1.bigint_col from alltypestiny t1 left join
  alltypes t2 on t1.bigint_col = t2.bigint_col
union
select t1.bigint_col from alltypestiny t1 inner join
  alltypes t2 on t1.bigint_col = t2.bigint_col
---- RESULTS
10
0
---- TYPES
bigint
=====
---- QUERY
# IMPALA-4883: The second union operand references a char column, which causes codegen
# to fail and be disabled for that operand and all operands that follow it. For this query
# codegen is enabled only for the first operand.
select count(s) from (
  select cast(id as string) as s from alltypestiny
  union all
  select cast(cl as string) as s from functional.chars_tiny
  union all
  select cast(id as string) as s from alltypestiny
) t
---- RESULTS
24
---- TYPES
bigint
=====
---- QUERY
# IMPALA-4883: Verify that the union limit is enforced correctly
select count(b) from (
  select bigint_col as b from alltypestiny limit 4
  union all
  (select bigint_col as b from alltypestiny limit 4)
  limit 7
) t
---- RESULTS
7
---- TYPES
bigint
=====
---- QUERY
# IMPALA-5188: The slot descriptors might be ordered differently for the operands and the
# union tuple. (Regression test).
select 1, 1
union all
select avg(id), id
from alltypestiny
group by id
---- RESULTS
0,0
1,1
1,1
2,2
3,3
4,4
5,5
6,6
7,7
---- TYPES
double, int
=====