sql-tests/inputs/higher-order-functions.sql

0001 -- Test higher order functions with codegen on and off.
0002 --CONFIG_DIM1 spark.sql.codegen.wholeStage=true
0003 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY
0004 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN
0005
0006 create or replace temporary view nested as values
0007   (1, array(32, 97), array(array(12, 99), array(123, 42), array(1))),
0008   (2, array(77, -76), array(array(6, 96, 65), array(-1, -2))),
0009   (3, array(12), array(array(17)))
0010   as t(x, ys, zs);
0011
0012 -- Only allow lambda's in higher order functions.
0013 select upper(x -> x) as v;
0014
0015 -- Identity transform an array
0016 select transform(zs, z -> z) as v from nested;
0017
0018 -- Transform an array
0019 select transform(ys, y -> y * y) as v from nested;
0020
0021 -- Transform an array with index
0022 select transform(ys, (y, i) -> y + i) as v from nested;
0023
0024 -- Transform an array with reference
0025 select transform(zs, z -> concat(ys, z)) as v from nested;
0026
0027 -- Transform an array to an array of 0's
0028 select transform(ys, 0) as v from nested;
0029
0030 -- Transform a null array
0031 select transform(cast(null as array<int>), x -> x + 1) as v;
0032
0033 -- Filter.
0034 select filter(ys, y -> y > 30) as v from nested;
0035
0036 -- Filter a null array
0037 select filter(cast(null as array<int>), y -> true) as v;
0038
0039 -- Filter nested arrays
0040 select transform(zs, z -> filter(z, zz -> zz > 50)) as v from nested;
0041
0042 -- Aggregate.
0043 select aggregate(ys, 0, (y, a) -> y + a + x) as v from nested;
0044
0045 -- Aggregate average.
0046 select aggregate(ys, (0 as sum, 0 as n), (acc, x) -> (acc.sum + x, acc.n + 1), acc -> acc.sum / acc.n) as v from nested;
0047
0048 -- Aggregate nested arrays
0049 select transform(zs, z -> aggregate(z, 1, (acc, val) -> acc * val * size(z))) as v from nested;
0050
0051 -- Aggregate a null array
0052 select aggregate(cast(null as array<int>), 0, (a, y) -> a + y + 1, a -> a + 2) as v;
0053
0054 -- Check for element existence
0055 select exists(ys, y -> y > 30) as v from nested;
0056
0057 -- Check for element existence in a null array
0058 select exists(cast(null as array<int>), y -> y > 30) as v;
0059
0060 -- Zip with array
0061 select zip_with(ys, zs, (a, b) -> a + size(b)) as v from nested;
0062
0063 -- Zip with array with concat
0064 select zip_with(array('a', 'b', 'c'), array('d', 'e', 'f'), (x, y) -> concat(x, y)) as v;
0065
0066 -- Zip with array coalesce
0067 select zip_with(array('a'), array('d', null, 'f'), (x, y) -> coalesce(x, y)) as v;
0068
0069 create or replace temporary view nested as values
0070   (1, map(1, 1, 2, 2, 3, 3)),
0071   (2, map(4, 4, 5, 5, 6, 6))
0072   as t(x, ys);
0073
0074 -- Identity Transform Keys in a map
0075 select transform_keys(ys, (k, v) -> k) as v from nested;
0076
0077 -- Transform Keys in a map by adding constant
0078 select transform_keys(ys, (k, v) -> k + 1) as v from nested;
0079
0080 -- Transform Keys in a map using values
0081 select transform_keys(ys, (k, v) -> k + v) as v from nested;
0082
0083 -- Identity Transform values in a map
0084 select transform_values(ys, (k, v) -> v) as v from nested;
0085
0086 -- Transform values in a map by adding constant
0087 select transform_values(ys, (k, v) -> v + 1) as v from nested;
0088
0089 -- Transform values in a map using values
0090 select transform_values(ys, (k, v) -> k + v) as v from nested;
0091
0092 -- use non reversed keywords: all is non reversed only if !ansi
0093 select transform(ys, all -> all * all) as v from values (array(32, 97)) as t(ys);
0094 select transform(ys, (all, i) -> all + i) as v from values (array(32, 97)) as t(ys);