| ==== |
| ---- QUERY |
| -- this is a test for a comment line above a blank line |
| -- we do not care about the results, just the comment |
| |
| select * from functional.alltypestiny; |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| create table calcite_alltypes as select * from functional.alltypes order by id limit 5; |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: OriginalPlanner.* |
| ==== |
| ---- QUERY |
| select * from calcite_alltypes; |
| ---- 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,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1 |
| 3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1 |
| 4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1 |
| ---- TYPES |
| int,boolean,tinyint,smallint,int,bigint,float,double,string,string,timestamp,int,int |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select string_col, tinyint_col from calcite_alltypes; |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ---- RESULTS |
| '0',0 |
| '1',1 |
| '2',2 |
| '3',3 |
| '4',4 |
| ---- TYPES |
| string,tinyint |
| ==== |
| ---- QUERY |
| select d1,d2,d3,d4,d5,d6 from functional.decimal_tbl; |
| ---- RESULTS |
| 1234,2222,1.2345678900,0.12345678900000000000000000000000000000,12345.78900,1 |
| 12345,333,123.4567890000,0.12345678900000000000000000000000000000,11.22000,1 |
| 12345,333,1234.5678900000,0.12345678900000000000000000000000000000,0.10000,1 |
| 132842,333,12345.6789000000,0.12345678900000000000000000000000000000,0.77889,1 |
| 2345,111,12.3456789000,0.12345678900000000000000000000000000000,3.14100,1 |
| ---- TYPES |
| decimal,decimal,decimal,decimal,decimal,decimal |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select * from functional.chars_tiny; |
| ---- RESULTS |
| '1aaaa','1bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','1cccc' |
| '2aaaa','2bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','2cccccc' |
| '3aaa ','3bbbbb ','3ccc' |
| '4aa ','4bbbb ','4cc' |
| '5a ','5bbb ','5c' |
| '6a ','6b ','6c' |
| '6a ','6b ','6c' |
| 'NULL','NULL','NULL' |
| 'a ','b ','c' |
| ---- TYPES |
| # varchar shows up as string, just as it does in the chars.test file |
| char,char,string |
| ---- HS2_TYPES |
| char,char,varchar |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select * from functional.date_tbl; |
| ---- RESULTS |
| 0,0001-01-01,0001-01-01 |
| 1,0001-12-31,0001-01-01 |
| 10,2017-11-28,1399-06-27 |
| 11,NULL,1399-06-27 |
| 12,2018-12-31,1399-06-27 |
| 2,0002-01-01,0001-01-01 |
| 20,0001-06-21,2017-11-27 |
| 21,0001-06-22,2017-11-27 |
| 22,0001-06-23,2017-11-27 |
| 23,0001-06-24,2017-11-27 |
| 24,0001-06-25,2017-11-27 |
| 25,0001-06-26,2017-11-27 |
| 26,0001-06-27,2017-11-27 |
| 27,0001-06-28,2017-11-27 |
| 28,0001-06-29,2017-11-27 |
| 29,2017-11-28,2017-11-27 |
| 3,1399-12-31,0001-01-01 |
| 30,9999-12-01,9999-12-31 |
| 31,9999-12-31,9999-12-31 |
| 4,2017-11-28,0001-01-01 |
| 5,9999-12-31,0001-01-01 |
| 6,NULL,0001-01-01 |
| ---- TYPES |
| int,date,date |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # creating a new table. We cannot use any functions at this point to |
| # manipulate the binary data (as the binary test currently does), so |
| # this just grabs the rows that can be checked. |
| create table ascii_binary as select * from functional.binary_tbl where id <= 4; |
| select * from ascii_binary; |
| ---- RESULTS |
| 1,'ascii','binary1' |
| 2,'ascii','binary2' |
| 3,'null','NULL' |
| 4,'empty','' |
| ---- TYPES |
| int,string,binary |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Tiny test for Calcite. At the point of this commit, very few functions work. This |
| # is a test that includes one of the functions that does. |
| # The ultimate goal is to include all tests in the testing framework, so there is |
| # no need to be extensive about testing in this file. |
| select bigint_col, abs(cast(-3 as bigint)), abs(-3000000000) from functional.alltypestiny; |
| ---- RESULTS |
| 0,3,3000000000 |
| 10,3,3000000000 |
| 0,3,3000000000 |
| 10,3,3000000000 |
| 0,3,3000000000 |
| 10,3,3000000000 |
| 0,3,3000000000 |
| 10,3,3000000000 |
| ---- TYPES |
| bigint,bigint,bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Tiny test for Calcite. At the point of this commit, very few functions work. This |
| # is a test that includes one of the functions that does. |
| # The ultimate goal is to include all tests in the testing framework, so there is |
| # no need to be extensive about testing in this file. |
| select cast(cast('2005-12-13 08:00:00' as string) AS TIMESTAMP) from functional.alltypestiny; |
| ---- RESULTS |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| 2005-12-13 08:00:00 |
| ---- TYPES |
| timestamp |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select * from calcite_alltypes where bigint_col = 20; |
| ---- RESULTS |
| 2,true,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1 |
| ---- TYPES |
| int,boolean,tinyint,smallint,int,bigint,float,double,string,string,timestamp,int,int |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select tinyint_col from calcite_alltypes where bigint_col = 20; |
| ---- RESULTS |
| 2 |
| ---- TYPES |
| tinyint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Values test |
| select abs(cast(-8 as bigint)); |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select 'hello' |
| ---- RESULTS |
| 'hello' |
| ---- TYPES |
| string |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Union test |
| select 3 union select 4; |
| ---- RESULTS |
| 3 |
| 4 |
| ---- TYPES |
| tinyint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select * from (values (1)) union (values (2), (3)); |
| ---- RESULTS |
| 1 |
| 2 |
| 3 |
| ---- TYPES |
| tinyint |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id > 3 order by abs(bigint_col), id; |
| ---- RESULTS |
| 4,0 |
| 6,0 |
| 5,10 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id >= 3 order by abs(bigint_col), id; |
| ---- RESULTS |
| 4,0 |
| 6,0 |
| 3,10 |
| 5,10 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id < 3 order by abs(bigint_col), id; |
| ---- RESULTS |
| 0,0 |
| 2,0 |
| 1,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id <= 3 order by abs(bigint_col), id; |
| ---- RESULTS |
| 0,0 |
| 2,0 |
| 1,10 |
| 3,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id != 3 order by abs(bigint_col), id; |
| ---- RESULTS |
| 0,0 |
| 2,0 |
| 4,0 |
| 6,0 |
| 1,10 |
| 5,10 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select id, abs(bigint_col) from functional.alltypestiny where id != 3 order by abs(bigint_col) desc, id; |
| ---- RESULTS |
| 1,10 |
| 5,10 |
| 7,10 |
| 0,0 |
| 2,0 |
| 4,0 |
| 6,0 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select group_str, some_nulls from functional.nullrows where group_str = 'a' order by some_nulls nulls first; |
| ---- RESULTS |
| 'a','NULL' |
| 'a','NULL' |
| 'a','NULL' |
| 'a','NULL' |
| 'a','a' |
| ---- TYPES |
| string, string |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sort test |
| select group_str, some_nulls from functional.nullrows where group_str = 'a' order by some_nulls nulls last; |
| ---- RESULTS |
| 'a','a' |
| 'a','NULL' |
| 'a','NULL' |
| 'a','NULL' |
| 'a','NULL' |
| ---- TYPES |
| string, string |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # limit test |
| select bigint_col from functional.alltypestiny where bigint_col = 0 limit 2; |
| ---- RESULTS |
| 0 |
| 0 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # limit test |
| select id, abs(bigint_col) from functional.alltypestiny where id > 2 order by abs(bigint_col), id limit 3; |
| ---- RESULTS |
| 4,0 |
| 6,0 |
| 3,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # aggregation test |
| select sum(bigint_col) from functional.alltypestiny; |
| ---- RESULTS |
| 40 |
| ---- TYPES |
| bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # aggregation test |
| select id, sum(bigint_col) from functional.alltypestiny group by id order by id; |
| ---- RESULTS |
| 0,0 |
| 1,10 |
| 2,0 |
| 3,10 |
| 4,0 |
| 5,10 |
| 6,0 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # aggregation test |
| select id, sum(bigint_col) from functional.alltypestiny group by id |
| having sum(bigint_col) > cast(5 as bigint) order by id; |
| ---- RESULTS |
| 1,10 |
| 3,10 |
| 5,10 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # aggregation test |
| select id, bigint_col, sum(bigint_col) |
| from functional.alltypestiny |
| group by grouping sets((id, bigint_col), (id), (bigint_col)) |
| order by id, bigint_col; |
| ---- RESULTS |
| 0,0,0 |
| 0,NULL,0 |
| 1,10,10 |
| 1,NULL,10 |
| 2,0,0 |
| 2,NULL,0 |
| 3,10,10 |
| 3,NULL,10 |
| 4,0,0 |
| 4,NULL,0 |
| 5,10,10 |
| 5,NULL,10 |
| 6,0,0 |
| 6,NULL,0 |
| 7,10,10 |
| 7,NULL,10 |
| NULL,0,0 |
| NULL,10,40 |
| ---- TYPES |
| int, bigint, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # having test |
| select id, sum(bigint_col) from functional.alltypestiny group by id having sum(bigint_col) > cast(5 as bigint) order by id; |
| ---- RESULTS |
| 1,10 |
| 3,10 |
| 5,10 |
| 7,10 |
| ---- TYPES |
| int, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # join test |
| select a.id, a.bigint_col, b.bigint_col |
| from functional.alltypestiny a |
| join functional.alltypessmall b |
| on (a.id = b.id) order by a.id; |
| ---- RESULTS |
| 0,0,0 |
| 1,10,10 |
| 2,0,20 |
| 3,10,30 |
| 4,0,40 |
| 5,10,50 |
| 6,0,60 |
| 7,10,70 |
| ---- TYPES |
| int, bigint, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # join inequality test |
| select a.id, b.id, a.bigint_col, b.bigint_col |
| from functional.alltypestiny a |
| join functional.alltypessmall b |
| on (b.id < a.id) |
| where b.id = 0 |
| order by a.id; |
| ---- RESULTS |
| 1,0,10,0 |
| 2,0,0,0 |
| 3,0,10,0 |
| 4,0,0,0 |
| 5,0,10,0 |
| 6,0,0,0 |
| 7,0,10,0 |
| ---- TYPES |
| int, int, bigint, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # cross join test |
| select a.id, b.id, a.bigint_col, b.bigint_col |
| from functional.alltypestiny a, functional.alltypessmall b |
| where b.id < 2 |
| order by a.id, b.id; |
| ---- RESULTS |
| 0,0,0,0 |
| 0,1,0,10 |
| 1,0,10,0 |
| 1,1,10,10 |
| 2,0,0,0 |
| 2,1,0,10 |
| 3,0,10,0 |
| 3,1,10,10 |
| 4,0,0,0 |
| 4,1,0,10 |
| 5,0,10,0 |
| 5,1,10,10 |
| 6,0,0,0 |
| 6,1,0,10 |
| 7,0,10,0 |
| 7,1,10,10 |
| ---- TYPES |
| int, int, bigint, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # left outer join test |
| select a.id, a.bigint_col, b.bigint_col |
| from functional.alltypessmall a |
| left outer join functional.alltypestiny b |
| on (a.id = b.id) |
| where a.id < 20 |
| order by a.id |
| ---- RESULTS |
| 0,0,0 |
| 1,10,10 |
| 2,20,0 |
| 3,30,10 |
| 4,40,0 |
| 5,50,10 |
| 6,60,0 |
| 7,70,10 |
| 8,80,NULL |
| 9,90,NULL |
| 10,0,NULL |
| 11,10,NULL |
| 12,20,NULL |
| 13,30,NULL |
| 14,40,NULL |
| 15,50,NULL |
| 16,60,NULL |
| 17,70,NULL |
| 18,80,NULL |
| 19,90,NULL |
| ---- TYPES |
| int, bigint, bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select bigint_col + bigint_col, int_col + int_col, smallint_col + smallint_col, |
| tinyint_col + tinyint_col, smallint_col + tinyint_col |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 20,2,2,2,2 |
| 20,2,2,2,2 |
| 20,2,2,2,2 |
| 20,2,2,2,2 |
| ---- TYPES |
| bigint, bigint, int, smallint, int |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select float_col + int_col, float_col + 3.0, 3.0 + 3.0 |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0.0,3.00000000,6.0 |
| 2.10000002384,4.10000002,6.0 |
| 0.0,3.00000000,6.0 |
| 2.10000002384,4.10000002,6.0 |
| 0.0,3.00000000,6.0 |
| 2.10000002384,4.10000002,6.0 |
| 0.0,3.00000000,6.0 |
| 2.10000002384,4.10000002,6.0 |
| ---- TYPES |
| double, decimal, decimal |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select bigint_col - bigint_col, int_col - int_col, smallint_col - smallint_col, |
| tinyint_col - tinyint_col, smallint_col - tinyint_col |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| 0,0,0,0,0 |
| ---- TYPES |
| bigint, bigint, int, smallint, int |
| ==== |
| ---- QUERY |
| select float_col - int_col, float_col - 3.0, 3.0 - 1.8 |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0.0,-3.00000000,1.2 |
| 0.1000000238418579,-1.89999998,1.2 |
| 0.0,-3.00000000,1.2 |
| 0.1000000238418579,-1.89999998,1.2 |
| 0.0,-3.00000000,1.2 |
| 0.1000000238418579,-1.89999998,1.2 |
| 0.0,-3.00000000,1.2 |
| 0.1000000238418579,-1.89999998,1.2 |
| ---- TYPES |
| double, decimal, decimal |
| ==== |
| ---- QUERY |
| select bigint_col * bigint_col, int_col * int_col, smallint_col * smallint_col, |
| tinyint_col * tinyint_col, smallint_col * tinyint_col |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0,0,0,0,0 |
| 100,1,1,1,1 |
| 0,0,0,0,0 |
| 100,1,1,1,1 |
| 0,0,0,0,0 |
| 100,1,1,1,1 |
| 0,0,0,0,0 |
| 100,1,1,1,1 |
| ---- TYPES |
| bigint, bigint, int, smallint, int |
| ==== |
| ---- QUERY |
| select float_col * int_col, float_col * 3.0, 3.0 * 2.0 |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0,0,6.00 |
| 1.100000023841858,3.300000071525574,6.00 |
| 0,0,6.00 |
| 1.100000023841858,3.300000071525574,6.00 |
| 0,0,6.00 |
| 1.100000023841858,3.300000071525574,6.00 |
| 0,0,6.00 |
| 1.100000023841858,3.300000071525574,6.00 |
| ---- TYPES |
| double, double, decimal |
| ==== |
| ---- QUERY |
| select bigint_col / bigint_col, int_col / int_col, smallint_col / smallint_col, |
| tinyint_col / tinyint_col |
| from functional.alltypestiny; |
| ---- RESULTS |
| 1,1,1,1 |
| NaN,NaN,NaN,NaN |
| 1,1,1,1 |
| NaN,NaN,NaN,NaN |
| 1,1,1,1 |
| NaN,NaN,NaN,NaN |
| 1,1,1,1 |
| NaN,NaN,NaN,NaN |
| ---- TYPES |
| double, double, double, double |
| ==== |
| ---- QUERY |
| select float_col / int_col, float_col / 3.0, 3.0 / 2.0 |
| from functional.alltypestiny; |
| ---- RESULTS |
| 1.100000023841858,0.36666667,1.500000 |
| NaN,0.00000000,1.500000 |
| 1.100000023841858,0.36666667,1.500000 |
| NaN,0.00000000,1.500000 |
| 1.100000023841858,0.36666667,1.500000 |
| NaN,0.00000000,1.500000 |
| 1.100000023841858,0.36666667,1.500000 |
| NaN,0.00000000,1.500000 |
| ---- TYPES |
| double, decimal, decimal |
| ==== |
| ---- QUERY |
| select bigint_col % bigint_col, int_col % int_col, smallint_col % smallint_col, |
| tinyint_col % tinyint_col, smallint_col % tinyint_col |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0,0,0,0,0 |
| NULL,NULL,NULL,NULL,NULL |
| 0,0,0,0,0 |
| NULL,NULL,NULL,NULL,NULL |
| 0,0,0,0,0 |
| NULL,NULL,NULL,NULL,NULL |
| 0,0,0,0,0 |
| NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| bigint, int, smallint, tinyint, smallint |
| ==== |
| ---- QUERY |
| select float_col % 3.0, 3.0 % 2.0 |
| from functional.alltypestiny; |
| ---- RESULTS |
| 0.000000000,1.0 |
| 1.100000024,1.0 |
| 0.000000000,1.0 |
| 1.100000024,1.0 |
| 0.000000000,1.0 |
| 1.100000024,1.0 |
| 0.000000000,1.0 |
| 1.100000024,1.0 |
| ---- TYPES |
| decimal, decimal |
| ==== |
| ---- QUERY |
| # Union test |
| select 3 union select 458; |
| ---- RESULTS |
| 3 |
| 458 |
| ---- TYPES |
| smallint |
| ==== |
| ---- QUERY |
| # Union test |
| select 3 union select 458; |
| ---- RESULTS |
| 3 |
| 458 |
| ---- TYPES |
| smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # case test |
| select tinyint_col, case tinyint_col when 1 then 5 else 458 end from functional.alltypestiny; |
| ---- RESULTS |
| 0,458 |
| 0,458 |
| 0,458 |
| 0,458 |
| 1,5 |
| 1,5 |
| 1,5 |
| 1,5 |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # case test |
| select tinyint_col, case tinyint_col when 1 then 5 when 2 then 7 else 458 end from functional.alltypestiny; |
| ---- RESULTS |
| 0,458 |
| 0,458 |
| 0,458 |
| 0,458 |
| 1,5 |
| 1,5 |
| 1,5 |
| 1,5 |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # case test |
| select tinyint_col, case tinyint_col when 0 then 458 else 5 end from functional.alltypestiny; |
| ---- RESULTS |
| 0,458 |
| 0,458 |
| 0,458 |
| 0,458 |
| 1,5 |
| 1,5 |
| 1,5 |
| 1,5 |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # case test |
| select tinyint_col, case tinyint_col when 0 then 458 end from functional.alltypestiny; |
| ---- RESULTS |
| 0,458 |
| 0,458 |
| 0,458 |
| 0,458 |
| 1,NULL |
| 1,NULL |
| 1,NULL |
| 1,NULL |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # case test other format (the calcite rexnode should be the same) |
| select tinyint_col, case when tinyint_col=00 then 458 else 5 end from functional.alltypestiny; |
| ---- RESULTS |
| 0,458 |
| 0,458 |
| 0,458 |
| 0,458 |
| 1,5 |
| 1,5 |
| 1,5 |
| 1,5 |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # or test |
| select (tinyint_col = 459) or (tinyint_col = 458) or (tinyint_col = 1) from functional.alltypestiny; |
| ---- RESULTS |
| false |
| false |
| false |
| false |
| true |
| true |
| true |
| true |
| ---- TYPES |
| boolean |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # and test |
| select (tinyint_col != 459) and (tinyint_col != 458) and (tinyint_col = 1) from functional.alltypestiny; |
| ---- RESULTS |
| false |
| false |
| false |
| false |
| true |
| true |
| true |
| true |
| ---- TYPES |
| boolean |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # sum cast tinyint agg test |
| select sum(tinyint_col) from functional.alltypestiny; |
| ---- RESULTS |
| 4 |
| ---- TYPES |
| bigint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # duplicate test from analytics-fn.test, delete when it is activated. |
| select date_part, |
| count(date_col) over (partition by date_part), |
| min(date_col) over (partition by date_part), |
| max(date_col) over (partition by date_part) |
| from functional.date_tbl |
| order by date_part; |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 0001-01-01,6,0001-01-01,9999-12-31 |
| 1399-06-27,2,2017-11-28,2018-12-31 |
| 1399-06-27,2,2017-11-28,2018-12-31 |
| 1399-06-27,2,2017-11-28,2018-12-31 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 2017-11-27,10,0001-06-21,2017-11-28 |
| 9999-12-31,2,9999-12-01,9999-12-31 |
| 9999-12-31,2,9999-12-01,9999-12-31 |
| ---- TYPES |
| DATE, BIGINT, DATE, DATE |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Test ROWS windows with start boundaries |
| select id, |
| count(id) over (order by id rows between 3 preceding and 3 preceding) c1, |
| count(id) over (order by id rows between 3 preceding and 2 preceding) c2, |
| count(id) over (order by id rows between 3 preceding and 1 preceding) c3, |
| count(id) over (order by id rows between 3 preceding and current row) c4, |
| count(id) over (order by id rows between 3 preceding and 1 following) c5, |
| count(id) over (order by id rows between 3 preceding and 2 following) c6, |
| count(id) over (order by id rows between 3 preceding and 3 following) c7, |
| count(id) over (order by id rows between 2 preceding and 3 following) c8, |
| count(id) over (order by id rows between 1 preceding and 3 following) c9, |
| count(id) over (order by id rows between current row and 3 following) c10, |
| count(id) over (order by id rows between 1 following and 3 following) c11, |
| count(id) over (order by id rows between 2 following and 3 following) c12, |
| count(id) over (order by id rows between 3 following and 3 following) c13 |
| from functional.alltypes where id < 8 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 0,0,0,0,1,2,3,4,4,4,4,3,2,1 |
| 1,0,0,1,2,3,4,5,5,5,4,3,2,1 |
| 2,0,1,2,3,4,5,6,6,5,4,3,2,1 |
| 3,1,2,3,4,5,6,7,6,5,4,3,2,1 |
| 4,1,2,3,4,5,6,7,6,5,4,3,2,1 |
| 5,1,2,3,4,5,6,6,5,4,3,2,1,0 |
| 6,1,2,3,4,5,5,5,4,3,2,1,0,0 |
| 7,1,2,3,4,4,4,4,3,2,1,0,0,0 |
| ---- TYPES |
| INT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT |
| ==== |
| ---- QUERY |
| # make sure this parses |
| select '1' as 'hello'; |
| ---- RESULTS |
| '1' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # make sure this parses |
| select "1" as "hello"; |
| ---- RESULTS |
| '1' |
| ---- TYPES |
| string |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| explain select * from functional.alltypestiny; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| row_regex:.*01:EXCHANGE.* |
| row_regex:.*00:SCAN.* |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| set explain_level=3; |
| explain select * from functional.alltypestiny; |
| ---- RESULTS: VERIFY_IS_SUBSET |
| row_regex:.*01:EXCHANGE.* |
| row_regex:.*00:SCAN.* |
| row_regex:.*partitions=4/4.* |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select count(*) from functional.alltypes_view; |
| ---- RESULTS |
| 7300 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select * from functional.alltypes_view order by id limit 10; |
| ---- 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,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1 |
| 3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1 |
| 4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1 |
| 5,false,5,5,5,50,5.5,50.5,'01/01/09','5',2009-01-01 00:05:00.100000000,2009,1 |
| 6,true,6,6,6,60,6.599999904632568,60.59999999999999,'01/01/09','6',2009-01-01 00:06:00.150000000,2009,1 |
| 7,false,7,7,7,70,7.699999809265137,70.7,'01/01/09','7',2009-01-01 00:07:00.210000000,2009,1 |
| 8,true,8,8,8,80,8.800000190734863,80.8,'01/01/09','8',2009-01-01 00:08:00.280000000,2009,1 |
| 9,false,9,9,9,90,9.899999618530273,90.89999999999999,'01/01/09','9',2009-01-01 00:09:00.360000000,2009,1 |
| ---- TYPES |
| INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT |
| ==== |
| ---- QUERY |
| select id from functional.alltypes_view order by id limit 1; |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select alltypes_view.id from functional.alltypes_view order by id limit 1; |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select functional.alltypes_view.id from functional.alltypes_view order by id limit 1; |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select count(*) from functional.alltypes_view, functional.alltypestiny |
| where functional.alltypes_view.id = functional.alltypestiny.id; |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select * from functional.alltypes_view, functional.alltypestiny |
| where functional.alltypes_view.id = functional.alltypestiny.id |
| order by functional.alltypes_view.id; |
| ---- 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,2,2,2,20,2.200000047683716,20.2,'01/01/09','2',2009-01-01 00:02:00.100000000,2009,1,2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,3,3,3,30,3.299999952316284,30.3,'01/01/09','3',2009-01-01 00:03:00.300000000,2009,1,3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,4,4,4,40,4.400000095367432,40.4,'01/01/09','4',2009-01-01 00:04:00.600000000,2009,1,4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 5,false,5,5,5,50,5.5,50.5,'01/01/09','5',2009-01-01 00:05:00.100000000,2009,1,5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3 |
| 6,true,6,6,6,60,6.599999904632568,60.59999999999999,'01/01/09','6',2009-01-01 00:06:00.150000000,2009,1,6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| 7,false,7,7,7,70,7.699999809265137,70.7,'01/01/09','7',2009-01-01 00:07:00.210000000,2009,1,7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| ---- TYPES |
| INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT, INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT |
| ==== |
| ---- QUERY |
| select id from functional.alltypes_view, functional.alltypestiny |
| where functional.alltypes_view.id = functional.alltypestiny.id |
| order by functional.alltypes_view.id; |
| ---- CATCH |
| SqlValidatorException: Column 'ID' is ambiguous |
| ==== |
| ---- QUERY |
| select alltypestiny.id from functional.alltypes_view, functional.alltypestiny |
| where functional.alltypes_view.id = functional.alltypestiny.id |
| order by functional.alltypes_view.id; |
| ---- RESULTS |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select functional.alltypestiny.id from functional.alltypes_view, functional.alltypestiny |
| where functional.alltypes_view.id = functional.alltypestiny.id |
| order by id; |
| ---- RESULTS |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # This test case makes sure that the definition of a regular view could be correctly |
| # parsed during view expansion even if database, table, and column names are enclosed in |
| # backticks. |
| select * from functional.alltypes_hive_view where id = 0; |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| ---- TYPES |
| INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT |
| ==== |
| ---- QUERY |
| # should fail at analysis time, not runtime |
| # IMPALA-14199: We can improve on this error message |
| select ds_hll_sketch(smallint_col) from functional_parquet.alltypessmall; |
| ---- CATCH |
| Cannot infer return type for DS_HLL_SKETCH; operand types: [SMALLINT] |
| ===== |
| ---- QUERY |
| # This test case ensures that the TypeCoercion code is in place, which gets kicked |
| # in with the "in" clause |
| select count(*) from functional.alltypesagg |
| where 10.1 not in (tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col) |
| ---- RESULTS |
| 8990 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test for varchar ndv with both 1 and 2 parameters |
| select ndv(varchar_col), ndv(varchar_col, 1) from functional.chars_medium; |
| ---- RESULTS |
| 963,1030 |
| ---- TYPES |
| bigint, bigint |
| ==== |
| ---- QUERY |
| # Test for varchar ndv with out of range ndv for second parameter |
| select ndv(varchar_col, 11) from functional.chars_medium; |
| ---- CATCH |
| Error in NDV function, second parameter needs to be between 1 and 10. |
| ==== |
| ---- QUERY |
| # Test for varchar ndv with out of range ndv for second parameter |
| select ndv(varchar_col, 0) from functional.chars_medium; |
| ---- CATCH |
| Error in NDV function, second parameter needs to be between 1 and 10. |
| ==== |
| ---- QUERY |
| # Labels test |
| select 2, 1 + 1; |
| ---- LABELS |
| 2,1 + 1 |
| ---- RESULTS |
| 2,2 |
| ---- TYPES |
| tinyint,smallint |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # Labels test |
| select length('hello') |
| ---- LABELS |
| length('hello') |
| ---- RESULTS |
| 5 |
| ---- TYPES |
| int |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| # IMPALA-14561: Should not be using MySqlDialect to capture labels |
| select timestamp_col + interval 3 milliseconds from functional.alltypestiny; |
| ---- LABELS |
| expr$0 |
| ---- RUNTIME_PROFILE |
| row_regex: .*PlannerType: CalcitePlanner.* |
| ==== |
| ---- QUERY |
| select cast('nan' as double), cast('inf' as float); |
| ---- RESULTS |
| NaN,Inf |
| ---- TYPES |
| DOUBLE, FLOAT |
| ==== |
| ---- QUERY |
| select count(*) from functional.alltypestiny; |
| ---- RUNTIME_PROFILE |
| row_regex: .*SPOOL_QUERY_RESULTS=0.* |
| ==== |
| ---- QUERY |
| select * from (values(0)); |
| ---- RUNTIME_PROFILE |
| row_regex: .*SPOOL_QUERY_RESULTS=0.* |
| ==== |
| ---- QUERY |
| # IMPALA-14421: Repeat of test in chars.test since Calcite |
| # was returning the wrong types |
| WITH numbered AS ( |
| SELECT *, row_number() over (order by cs) as rn |
| FROM functional.chars_tiny) |
| SELECT * |
| FROM ( |
| SELECT CASE WHEN rn % 2 = 0 THEN cs END cs, |
| CASE WHEN rn % 2 = 1 THEN cl END cl, |
| CASE WHEN rn % 3 = 0 THEN vc END vc |
| FROM numbered |
| UNION ALL |
| SELECT CASE WHEN rn % 2 = 1 THEN cs END cs, |
| CASE WHEN rn % 2 = 0 THEN cl END cl, |
| CASE WHEN rn % 3 = 1 THEN vc END vc |
| FROM numbered) v |
| ---- TYPES |
| char, char, string |
| ---- HS2_TYPES |
| char, char, varchar |
| ---- RESULTS |
| 'NULL','1bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','NULL' |
| '2aaaa','NULL','NULL' |
| 'NULL','3bbbbb ','3ccc' |
| '4aa ','NULL','NULL' |
| 'NULL','5bbb ','NULL' |
| '6a ','NULL','6c' |
| 'NULL','6b ','NULL' |
| 'a ','NULL','NULL' |
| 'NULL','NULL','NULL' |
| '1aaaa','NULL','1cccc' |
| 'NULL','2bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','NULL' |
| '3aaa ','NULL','NULL' |
| 'NULL','4bbbb ','4cc' |
| '5a ','NULL','NULL' |
| 'NULL','6b ','NULL' |
| '6a ','NULL','6c' |
| 'NULL','b ','NULL' |
| 'NULL','NULL','NULL' |
| ==== |
