-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 12
-- !query 0
create temporary view data as select * from values
("one", array(11, 12, 13), array(array(111, 112, 113), array(121, 122, 123))),
("two", array(21, 22, 23), array(array(211, 212, 213), array(221, 222, 223)))
as data(a, b, c)
-- !query 0 schema
struct<>
-- !query 0 output
-- !query 1
select * from data
-- !query 1 schema
struct<a:string,b:array<int>,c:array<array<int>>>
-- !query 1 output
one [11,12,13] [[111,112,113],[121,122,123]]
two [21,22,23] [[211,212,213],[221,222,223]]
-- !query 2
select a, b[0], b[0] + b[1] from data
-- !query 2 schema
struct<a:string,b[0]:int,(b[0] + b[1]):int>
-- !query 2 output
one 11 23
two 21 43
-- !query 3
select a, c[0][0] + c[0][0 + 1] from data
-- !query 3 schema
struct<a:string,(c[0][0] + c[0][(0 + 1)]):int>
-- !query 3 output
one 223
two 423
-- !query 4
create temporary view primitive_arrays as select * from values (
array(true),
array(2Y, 1Y),
array(2S, 1S),
array(2, 1),
array(2L, 1L),
array(9223372036854775809, 9223372036854775808),
array(2.0D, 1.0D),
array(float(2.0), float(1.0)),
array(date '2016-03-14', date '2016-03-13'),
array(timestamp '2016-11-15 20:54:00.000', timestamp '2016-11-12 20:54:00.000')
) as primitive_arrays(
boolean_array,
tinyint_array,
smallint_array,
int_array,
bigint_array,
decimal_array,
double_array,
float_array,
date_array,
timestamp_array
)
-- !query 4 schema
struct<>
-- !query 4 output
-- !query 5
select * from primitive_arrays
-- !query 5 schema
struct<boolean_array:array<boolean>,tinyint_array:array<tinyint>,smallint_array:array<smallint>,int_array:array<int>,bigint_array:array<bigint>,decimal_array:array<decimal(19,0)>,double_array:array<double>,float_array:array<float>,date_array:array<date>,timestamp_array:array<timestamp>>
-- !query 5 output
[true] [2,1] [2,1] [2,1] [2,1] [9223372036854775809,9223372036854775808] [2.0,1.0] [2.0,1.0] [2016-03-14,2016-03-13] [2016-11-15 20:54:00.0,2016-11-12 20:54:00.0]
-- !query 6
select
array_contains(boolean_array, true), array_contains(boolean_array, false),
array_contains(tinyint_array, 2Y), array_contains(tinyint_array, 0Y),
array_contains(smallint_array, 2S), array_contains(smallint_array, 0S),
array_contains(int_array, 2), array_contains(int_array, 0),
array_contains(bigint_array, 2L), array_contains(bigint_array, 0L),
array_contains(decimal_array, 9223372036854775809), array_contains(decimal_array, 1),
array_contains(double_array, 2.0D), array_contains(double_array, 0.0D),
array_contains(float_array, float(2.0)), array_contains(float_array, float(0.0)),
array_contains(date_array, date '2016-03-14'), array_contains(date_array, date '2016-01-01'),
array_contains(timestamp_array, timestamp '2016-11-15 20:54:00.000'), array_contains(timestamp_array, timestamp '2016-01-01 20:54:00.000')
from primitive_arrays
-- !query 6 schema
struct<array_contains(boolean_array, true):boolean,array_contains(boolean_array, false):boolean,array_contains(tinyint_array, 2):boolean,array_contains(tinyint_array, 0):boolean,array_contains(smallint_array, 2):boolean,array_contains(smallint_array, 0):boolean,array_contains(int_array, 2):boolean,array_contains(int_array, 0):boolean,array_contains(bigint_array, 2):boolean,array_contains(bigint_array, 0):boolean,array_contains(decimal_array, 9223372036854775809):boolean,array_contains(decimal_array, CAST(1 AS DECIMAL(19,0))):boolean,array_contains(double_array, 2.0):boolean,array_contains(double_array, 0.0):boolean,array_contains(float_array, CAST(2.0 AS FLOAT)):boolean,array_contains(float_array, CAST(0.0 AS FLOAT)):boolean,array_contains(date_array, DATE '2016-03-14'):boolean,array_contains(date_array, DATE '2016-01-01'):boolean,array_contains(timestamp_array, TIMESTAMP('2016-11-15 20:54:00.0')):boolean,array_contains(timestamp_array, TIMESTAMP('2016-01-01 20:54:00.0')):boolean>
-- !query 6 output
true false true false true false true false true false true false true false true false true false true false
-- !query 7
select array_contains(b, 11), array_contains(c, array(111, 112, 113)) from data
-- !query 7 schema
struct<array_contains(b, 11):boolean,array_contains(c, array(111, 112, 113)):boolean>
-- !query 7 output
false false
true true
-- !query 8
select
sort_array(boolean_array),
sort_array(tinyint_array),
sort_array(smallint_array),
sort_array(int_array),
sort_array(bigint_array),
sort_array(decimal_array),
sort_array(double_array),
sort_array(float_array),
sort_array(date_array),
sort_array(timestamp_array)
from primitive_arrays
-- !query 8 schema
struct<sort_array(boolean_array, true):array<boolean>,sort_array(tinyint_array, true):array<tinyint>,sort_array(smallint_array, true):array<smallint>,sort_array(int_array, true):array<int>,sort_array(bigint_array, true):array<bigint>,sort_array(decimal_array, true):array<decimal(19,0)>,sort_array(double_array, true):array<double>,sort_array(float_array, true):array<float>,sort_array(date_array, true):array<date>,sort_array(timestamp_array, true):array<timestamp>>
-- !query 8 output
[true] [1,2] [1,2] [1,2] [1,2] [9223372036854775808,9223372036854775809] [1.0,2.0] [1.0,2.0] [2016-03-13,2016-03-14] [2016-11-12 20:54:00.0,2016-11-15 20:54:00.0]
-- !query 9
select sort_array(array('b', 'd'), '1')
-- !query 9 schema
struct<>
-- !query 9 output
org.apache.spark.sql.AnalysisException
cannot resolve 'sort_array(array('b', 'd'), '1')' due to data type mismatch: Sort order in second argument requires a boolean literal.; line 1 pos 7
-- !query 10
select sort_array(array('b', 'd'), cast(NULL as boolean))
-- !query 10 schema
struct<>
-- !query 10 output
org.apache.spark.sql.AnalysisException
cannot resolve 'sort_array(array('b', 'd'), CAST(NULL AS BOOLEAN))' due to data type mismatch: Sort order in second argument requires a boolean literal.; line 1 pos 7
-- !query 11
select
size(boolean_array),
size(tinyint_array),
size(smallint_array),
size(int_array),
size(bigint_array),
size(decimal_array),
size(double_array),
size(float_array),
size(date_array),
size(timestamp_array)
from primitive_arrays
-- !query 11 schema
struct<size(boolean_array):int,size(tinyint_array):int,size(smallint_array):int,size(int_array):int,size(bigint_array):int,size(decimal_array):int,size(double_array):int,size(float_array):int,size(date_array):int,size(timestamp_array):int>
-- !query 11 output
1 2 2 2 2 2 2 2 2 2