typeCoercion/native/concat.sql

0001 -- Concatenate mixed inputs (output type is string)
0002 SELECT (col1 || col2 || col3) col
0003 FROM (
0004   SELECT
0005     id col1,
0006     string(id + 1) col2,
0007     encode(string(id + 2), 'utf-8') col3
0008   FROM range(10)
0009 );
0010
0011 SELECT ((col1 || col2) || (col3 || col4) || col5) col
0012 FROM (
0013   SELECT
0014     'prefix_' col1,
0015     id col2,
0016     string(id + 1) col3,
0017     encode(string(id + 2), 'utf-8') col4,
0018     CAST(id AS DOUBLE) col5
0019   FROM range(10)
0020 );
0021
0022 SELECT ((col1 || col2) || (col3 || col4)) col
0023 FROM (
0024   SELECT
0025     string(id) col1,
0026     string(id + 1) col2,
0027     encode(string(id + 2), 'utf-8') col3,
0028     encode(string(id + 3), 'utf-8') col4
0029   FROM range(10)
0030 );
0031
0032 -- turn on concatBinaryAsString
0033 set spark.sql.function.concatBinaryAsString=true;
0034
0035 SELECT (col1 || col2) col
0036 FROM (
0037   SELECT
0038     encode(string(id), 'utf-8') col1,
0039     encode(string(id + 1), 'utf-8') col2
0040   FROM range(10)
0041 );
0042
0043 SELECT (col1 || col2 || col3 || col4) col
0044 FROM (
0045   SELECT
0046     encode(string(id), 'utf-8') col1,
0047     encode(string(id + 1), 'utf-8') col2,
0048     encode(string(id + 2), 'utf-8') col3,
0049     encode(string(id + 3), 'utf-8') col4
0050   FROM range(10)
0051 );
0052
0053 SELECT ((col1 || col2) || (col3 || col4)) col
0054 FROM (
0055   SELECT
0056     encode(string(id), 'utf-8') col1,
0057     encode(string(id + 1), 'utf-8') col2,
0058     encode(string(id + 2), 'utf-8') col3,
0059     encode(string(id + 3), 'utf-8') col4
0060   FROM range(10)
0061 );
0062
0063 -- turn off concatBinaryAsString
0064 set spark.sql.function.concatBinaryAsString=false;
0065
0066 -- Concatenate binary inputs (output type is binary)
0067 SELECT (col1 || col2) col
0068 FROM (
0069   SELECT
0070     encode(string(id), 'utf-8') col1,
0071     encode(string(id + 1), 'utf-8') col2
0072   FROM range(10)
0073 );
0074
0075 SELECT (col1 || col2 || col3 || col4) col
0076 FROM (
0077   SELECT
0078     encode(string(id), 'utf-8') col1,
0079     encode(string(id + 1), 'utf-8') col2,
0080     encode(string(id + 2), 'utf-8') col3,
0081     encode(string(id + 3), 'utf-8') col4
0082   FROM range(10)
0083 );
0084
0085 SELECT ((col1 || col2) || (col3 || col4)) col
0086 FROM (
0087   SELECT
0088     encode(string(id), 'utf-8') col1,
0089     encode(string(id + 1), 'utf-8') col2,
0090     encode(string(id + 2), 'utf-8') col3,
0091     encode(string(id + 3), 'utf-8') col4
0092   FROM range(10)
0093 );
0094
0095 CREATE TEMPORARY VIEW various_arrays AS SELECT * FROM VALUES (
0096   array(true, false), array(true),
0097   array(2Y, 1Y), array(3Y, 4Y),
0098   array(2S, 1S), array(3S, 4S),
0099   array(2, 1), array(3, 4),
0100   array(2L, 1L), array(3L, 4L),
0101   array(9223372036854775809, 9223372036854775808), array(9223372036854775808, 9223372036854775809),
0102   array(2.0D, 1.0D), array(3.0D, 4.0D),
0103   array(float(2.0), float(1.0)), array(float(3.0), float(4.0)),
0104   array(date '2016-03-14', date '2016-03-13'), array(date '2016-03-12', date '2016-03-11'),
0105   array(timestamp '2016-11-15 20:54:00.000', timestamp '2016-11-12 20:54:00.000'),
0106   array(timestamp '2016-11-11 20:54:00.000'),
0107   array('a', 'b'), array('c', 'd'),
0108   array(array('a', 'b'), array('c', 'd')), array(array('e'), array('f')),
0109   array(struct('a', 1), struct('b', 2)), array(struct('c', 3), struct('d', 4)),
0110   array(map('a', 1), map('b', 2)), array(map('c', 3), map('d', 4))
0111 ) AS various_arrays(
0112   boolean_array1, boolean_array2,
0113   tinyint_array1, tinyint_array2,
0114   smallint_array1, smallint_array2,
0115   int_array1, int_array2,
0116   bigint_array1, bigint_array2,
0117   decimal_array1, decimal_array2,
0118   double_array1, double_array2,
0119   float_array1, float_array2,
0120   date_array1, data_array2,
0121   timestamp_array1, timestamp_array2,
0122   string_array1, string_array2,
0123   array_array1, array_array2,
0124   struct_array1, struct_array2,
0125   map_array1, map_array2
0126 );
0127
0128 -- Concatenate arrays of the same type
0129 SELECT
0130     (boolean_array1 || boolean_array2) boolean_array,
0131     (tinyint_array1 || tinyint_array2) tinyint_array,
0132     (smallint_array1 || smallint_array2) smallint_array,
0133     (int_array1 || int_array2) int_array,
0134     (bigint_array1 || bigint_array2) bigint_array,
0135     (decimal_array1 || decimal_array2) decimal_array,
0136     (double_array1 || double_array2) double_array,
0137     (float_array1 || float_array2) float_array,
0138     (date_array1 || data_array2) data_array,
0139     (timestamp_array1 || timestamp_array2) timestamp_array,
0140     (string_array1 || string_array2) string_array,
0141     (array_array1 || array_array2) array_array,
0142     (struct_array1 || struct_array2) struct_array,
0143     (map_array1 || map_array2) map_array
0144 FROM various_arrays;
0145
0146 -- Concatenate arrays of different types
0147 SELECT
0148     (tinyint_array1 || smallint_array2) ts_array,
0149     (smallint_array1 || int_array2) si_array,
0150     (int_array1 || bigint_array2) ib_array,
0151     (bigint_array1 || decimal_array2) bd_array,
0152     (decimal_array1 || double_array2) dd_array,
0153     (double_array1 || float_array2) df_array,
0154     (string_array1 || data_array2) std_array,
0155     (timestamp_array1 || string_array2) tst_array,
0156     (string_array1 || int_array2) sti_array
0157 FROM various_arrays;