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