sql-tests/inputs/array.sql

0001 -- test cases for array functions
0002
0003 create temporary view data as select * from values
0004   ("one", array(11, 12, 13), array(array(111, 112, 113), array(121, 122, 123))),
0005   ("two", array(21, 22, 23), array(array(211, 212, 213), array(221, 222, 223)))
0006   as data(a, b, c);
0007
0008 select * from data;
0009
0010 -- index into array
0011 select a, b[0], b[0] + b[1] from data;
0012
0013 -- index into array of arrays
0014 select a, c[0][0] + c[0][0 + 1] from data;
0015
0016
0017 create temporary view primitive_arrays as select * from values (
0018   array(true),
0019   array(2Y, 1Y),
0020   array(2S, 1S),
0021   array(2, 1),
0022   array(2L, 1L),
0023   array(9223372036854775809, 9223372036854775808),
0024   array(2.0D, 1.0D),
0025   array(float(2.0), float(1.0)),
0026   array(date '2016-03-14', date '2016-03-13'),
0027   array(timestamp '2016-11-15 20:54:00.000',  timestamp '2016-11-12 20:54:00.000')
0028 ) as primitive_arrays(
0029   boolean_array,
0030   tinyint_array,
0031   smallint_array,
0032   int_array,
0033   bigint_array,
0034   decimal_array,
0035   double_array,
0036   float_array,
0037   date_array,
0038   timestamp_array
0039 );
0040
0041 select * from primitive_arrays;
0042
0043 -- array_contains on all primitive types: result should alternate between true and false
0044 select
0045   array_contains(boolean_array, true), array_contains(boolean_array, false),
0046   array_contains(tinyint_array, 2Y), array_contains(tinyint_array, 0Y),
0047   array_contains(smallint_array, 2S), array_contains(smallint_array, 0S),
0048   array_contains(int_array, 2), array_contains(int_array, 0),
0049   array_contains(bigint_array, 2L), array_contains(bigint_array, 0L),
0050   array_contains(decimal_array, 9223372036854775809), array_contains(decimal_array, 1),
0051   array_contains(double_array, 2.0D), array_contains(double_array, 0.0D),
0052   array_contains(float_array, float(2.0)), array_contains(float_array, float(0.0)),
0053   array_contains(date_array, date '2016-03-14'), array_contains(date_array, date '2016-01-01'),
0054   array_contains(timestamp_array, timestamp '2016-11-15 20:54:00.000'), array_contains(timestamp_array, timestamp '2016-01-01 20:54:00.000')
0055 from primitive_arrays;
0056
0057 -- array_contains on nested arrays
0058 select array_contains(b, 11), array_contains(c, array(111, 112, 113)) from data;
0059
0060 -- sort_array
0061 select
0062   sort_array(boolean_array),
0063   sort_array(tinyint_array),
0064   sort_array(smallint_array),
0065   sort_array(int_array),
0066   sort_array(bigint_array),
0067   sort_array(decimal_array),
0068   sort_array(double_array),
0069   sort_array(float_array),
0070   sort_array(date_array),
0071   sort_array(timestamp_array)
0072 from primitive_arrays;
0073
0074 -- sort_array with an invalid string literal for the argument of sort order.
0075 select sort_array(array('b', 'd'), '1');
0076
0077 -- sort_array with an invalid null literal casted as boolean for the argument of sort order.
0078 select sort_array(array('b', 'd'), cast(NULL as boolean));
0079
0080 -- size
0081 select
0082   size(boolean_array),
0083   size(tinyint_array),
0084   size(smallint_array),
0085   size(int_array),
0086   size(bigint_array),
0087   size(decimal_array),
0088   size(double_array),
0089   size(float_array),
0090   size(date_array),
0091   size(timestamp_array)
0092 from primitive_arrays;