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