1-- test cases for array functions 2 3create temporary view data as select * from values 4 ("one", array(11, 12, 13), array(array(111, 112, 113), array(121, 122, 123))), 5 ("two", array(21, 22, 23), array(array(211, 212, 213), array(221, 222, 223))) 6 as data(a, b, c); 7 8select * from data; 9 10-- index into array 11select a, b[0], b[0] + b[1] from data; 12 13-- index into array of arrays 14select a, c[0][0] + c[0][0 + 1] from data; 15 16 17create temporary view primitive_arrays as select * from values ( 18 array(true), 19 array(2Y, 1Y), 20 array(2S, 1S), 21 array(2, 1), 22 array(2L, 1L), 23 array(9223372036854775809, 9223372036854775808), 24 array(2.0D, 1.0D), 25 array(float(2.0), float(1.0)), 26 array(date '2016-03-14', date '2016-03-13'), 27 array(timestamp '2016-11-15 20:54:00.000', timestamp '2016-11-12 20:54:00.000') 28) as primitive_arrays( 29 boolean_array, 30 tinyint_array, 31 smallint_array, 32 int_array, 33 bigint_array, 34 decimal_array, 35 double_array, 36 float_array, 37 date_array, 38 timestamp_array 39); 40 41select * from primitive_arrays; 42 43-- array_contains on all primitive types: result should alternate between true and false 44select 45 array_contains(boolean_array, true), array_contains(boolean_array, false), 46 array_contains(tinyint_array, 2Y), array_contains(tinyint_array, 0Y), 47 array_contains(smallint_array, 2S), array_contains(smallint_array, 0S), 48 array_contains(int_array, 2), array_contains(int_array, 0), 49 array_contains(bigint_array, 2L), array_contains(bigint_array, 0L), 50 array_contains(decimal_array, 9223372036854775809), array_contains(decimal_array, 1), 51 array_contains(double_array, 2.0D), array_contains(double_array, 0.0D), 52 array_contains(float_array, float(2.0)), array_contains(float_array, float(0.0)), 53 array_contains(date_array, date '2016-03-14'), array_contains(date_array, date '2016-01-01'), 54 array_contains(timestamp_array, timestamp '2016-11-15 20:54:00.000'), array_contains(timestamp_array, timestamp '2016-01-01 20:54:00.000') 55from primitive_arrays; 56 57-- array_contains on nested arrays 58select array_contains(b, 11), array_contains(c, array(111, 112, 113)) from data; 59 60-- sort_array 61select 62 sort_array(boolean_array), 63 sort_array(tinyint_array), 64 sort_array(smallint_array), 65 sort_array(int_array), 66 sort_array(bigint_array), 67 sort_array(decimal_array), 68 sort_array(double_array), 69 sort_array(float_array), 70 sort_array(date_array), 71 sort_array(timestamp_array) 72from primitive_arrays; 73 74-- sort_array with an invalid string literal for the argument of sort order. 75select sort_array(array('b', 'd'), '1'); 76 77-- sort_array with an invalid null literal casted as boolean for the argument of sort order. 78select sort_array(array('b', 'd'), cast(NULL as boolean)); 79 80-- size 81select 82 size(boolean_array), 83 size(tinyint_array), 84 size(smallint_array), 85 size(int_array), 86 size(bigint_array), 87 size(decimal_array), 88 size(double_array), 89 size(float_array), 90 size(date_array), 91 size(timestamp_array) 92from primitive_arrays; 93