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