testdata/workloads/functional-query/queries/QueryTest/except.test

====
---- 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 except
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 in (1,2,3)
except
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
except
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
====
---- QUERY
# Only EXCEPT 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 < 2 limit 3
except
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
except
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
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# limits containing arithmetic expressions
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 < 3 order by id limit 3-2+3
except
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
except
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
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# mixed with 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=1
except
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
====
---- QUERY
# 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 < 3
except
select 0,true,0,0,0,0,0,0,'01/01/09','0',cast('2009-01-01 00:00:00' as timestamp), 2009,1
except
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
except
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
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
# except with one nested 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 < 4
except
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
except 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 1
---- 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
====
---- QUERY
# Mixed except and union, 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
except 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
except 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,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
====
---- QUERY
# simple query unnesting tests
select 10 except select 11 except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
====
---- QUERY
# don't unnest
select 10 except (select 11 except select 10)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
====
---- QUERY
# left operand don't unnest
(select 10 except select 11) except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
====
---- QUERY
# middle
select 11 except (select 10 except select 11) except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
11
====
---- QUERY
# if middle were unnested it would return no rows
select 11 except select 10 except select 11 except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
====
---- QUERY
# nested with union distinct
(select 10 except select 11) union select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
====
---- QUERY
# nested with union all
(select 10 except select 11) union all select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
10
====
---- QUERY
# nested with union all
(select 10 except select 11) union all select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
10
====
---- QUERY
# union all followed by except
select 10 union all select 11 except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
11
====
---- QUERY
# multiple union alls followed by except
select 10 union all select 11 union all select 11 except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
11
====
---- QUERY
# nested union outer except
(select 10 union distinct select 11) except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
11
====
---- QUERY
# union distinct followed by except
select 10 union distinct select 11 except select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
11
====
---- QUERY
# except outer nested union distinct
select 10 except (select 11 union select 10)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
====
---- QUERY
# except then union distinct
select 10 except select 11 union select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
====
---- QUERY
# except then union distinct
select 10 except (select 11 union all select 10)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
====
---- QUERY
# except then union distinct
select 10 except select 11 union all select 10
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10
10
====
---- QUERY
# minus distinct as an alias for except distinct
select a+1 from (select id as a from alltypessmall minus distinct select id from alltypestiny) T where T.a > 90
---- RESULTS: VERIFY_IS_EQUAL_SORTED
92
93
94
95
96
97
98
99
100
====
---- QUERY
# minus as an alias for except
select alltypestiny.id+2 as a from alltypestiny left outer join dimtbl on alltypestiny.id = dimtbl.id  minus select id from alltypestiny
---- RESULTS: VERIFY_IS_EQUAL_SORTED
8
9
====
---- QUERY
#  unnesting 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
 except
 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)
except 
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
====
---- QUERY
#  unnesting 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
 except
(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
except 
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
====
---- QUERY
# no unnesting: first operand is nested 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)
 except
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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# no unnesting: second operand is nested unionall
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
except
  (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
====
---- QUERY
# no unnesting: UNION DISTINCT in first operand except in outer
(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)
except
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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# no unnesting: UNION DISTINCT in second operand except in first
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
except
  (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
====
---- QUERY
# UNION ALL absorbs the children but not directly the operands
# of a nested EXCEPT in the first operand
(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 in (1,2)
except
 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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# UNION ALL absorbs the children but not directly the operands
# of a nested EXCEPT in the second operand
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
   except
   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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# results if the except was unnested
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
   except
   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
# no except unnesting: UNION ALL doesn't absorb the children of a nested union
# with mixed except 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
   except
   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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# no except unnesting: UNION DISTINCT doesn't absorb nested EXCEPT
# 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 in (1,2)
   except
   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
# unnested results
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 in (1,2)
   except
   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
3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2
====
---- QUERY
# Complex except no unnesting
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 in (1,2,3)
except
  (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
   except
     (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
      except
        (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
         except
         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
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
# EXCEPT in a 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
   except
   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) x
except
(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
1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1
====
---- QUERY
# EXCEPT 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
   except
   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) 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
# mixed with intersect
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 in (1,2,3,4)
intersect
  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 in (1,2,3)
   except
   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
   except
   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
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
# mixed with intersect with final order by bound to last operand, non-standard behavior see IMPALA-4741
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 in (1,2,3,4)
intersect
  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 in (1,2,3)
   except
   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
   except
   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 desc 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
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
# mixed with intersect with final order by for the whole statement
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 in (1,2,3,4)
intersect
  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 in (1,2,3)
   except
   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
   except
   (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 desc limit 1
---- TYPES
int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int
---- RESULTS: VERIFY_IS_EQUAL_SORTED
5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3
====
---- QUERY
# 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
  except
  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)
  except
  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
  except
  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
7297
====
---- QUERY
# only constant selects
select 1, 'a', NULL, 10.0
except
select 2, 'b', NULL, 20.0
except
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
====
---- QUERY
# except with values statements
values(1, 'a', NULL, 10.0)
except
values(2, 'b', NULL, 20.0)
except
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
====
---- QUERY
# Test EXCEPT with GROUP BY
select id, count(*) from alltypes where id < 3 group by id
except
select 2, 3
---- TYPES
int, bigint
---- RESULTS
0,1
1,1
2,1
====
---- QUERY
# join on string column + except
select count(*) from
(select 1 FROM alltypes AS t1 JOIN alltypestiny AS t2 ON t1.string_col = t2.string_col
EXCEPT SELECT 2 FROM tinytable AS t1) as t3
---- TYPES
bigint
---- RESULTS
1
====
---- QUERY
# Test except where all operands are dropped because of constant conjuncts.
select * from
  (select 1 a, 2 b
   except
   select 3, 4
   except
   select 10, 20) t
where a > b
---- TYPES
tinyint, tinyint
---- RESULTS
====
---- QUERY
# IMPALA-4336: Test proper result expr casting when unnesting operands.
(select 10) union select double_col from alltypestiny except (select 0 union all select 90)
---- RESULTS: VERIFY_IS_EQUAL_SORTED
10.1
10
---- TYPES
DOUBLE
=====
---- QUERY
select bigint_col + 1 from alltypestiny
except
select bigint_col + 1 from alltypestiny
---- RESULTS
---- TYPES
bigint
=====
---- QUERY
# One operand is passed through, the other is not.
select bigint_col + 1 from alltypestiny
except
select bigint_col from alltypestiny
---- RESULTS
11
1
---- TYPES
bigint
=====
---- QUERY
# Test implicit casts. The label comes from the leftmost operands however the return
# types should match the widest compatible types across operands, not just the leftmost
select tinyint_col, float_col, string_col from alltypestiny where year=2009
except
select int_col, double_col, string_col from alltypestiny where year=2009 and month=1
---- LABELS
tinyint_col, float_col, string_col
---- TYPES
int, double, string
---- RESULTS: VERIFY_IS_EQUAL_SORTED
1,1.100000023841858,'1'
====
---- QUERY
select tinyint_col from alltypesagg minus select tinyint_col from alltypes
---- TYPES
TINYINT
---- RESULTS
NULL
====
---- QUERY
select tinyint_col from alltypesagg minus select tinyint_col from alltypesagg where month = 1
---- TYPES
TINYINT
---- RESULTS
====