typeCoercion/native/mapconcat.sql

0001 CREATE TEMPORARY VIEW various_maps AS SELECT * FROM VALUES (
0002   map(true, false), map(false, true),
0003   map(1Y, 2Y), map(3Y, 4Y),
0004   map(1S, 2S), map(3S, 4S),
0005   map(4, 6), map(7, 8),
0006   map(6L, 7L), map(8L, 9L),
0007   map(9223372036854775809, 9223372036854775808), map(9223372036854775808, 9223372036854775809),
0008   map(1.0D, 2.0D), map(3.0D, 4.0D),
0009   map(float(1.0D), float(2.0D)), map(float(3.0D), float(4.0D)),
0010   map(date '2016-03-14', date '2016-03-13'), map(date '2016-03-12', date '2016-03-11'),
0011   map(timestamp '2016-11-15 20:54:00.000', timestamp '2016-11-12 20:54:00.000'),
0012   map(timestamp '2016-11-11 20:54:00.000', timestamp '2016-11-09 20:54:00.000'),
0013   map('a', 'b'), map('c', 'd'),
0014   map(array('a', 'b'), array('c', 'd')), map(array('e'), array('f')),
0015   map(struct('a', 1), struct('b', 2)), map(struct('c', 3), struct('d', 4)),
0016   map('a', 1), map('c', 2),
0017   map(1, 'a'), map(2, 'c')
0018 ) AS various_maps (
0019   boolean_map1, boolean_map2,
0020   tinyint_map1, tinyint_map2,
0021   smallint_map1, smallint_map2,
0022   int_map1, int_map2,
0023   bigint_map1, bigint_map2,
0024   decimal_map1, decimal_map2,
0025   double_map1, double_map2,
0026   float_map1, float_map2,
0027   date_map1, date_map2,
0028   timestamp_map1,
0029   timestamp_map2,
0030   string_map1, string_map2,
0031   array_map1, array_map2,
0032   struct_map1, struct_map2,
0033   string_int_map1, string_int_map2,
0034   int_string_map1, int_string_map2
0035 );
0036
0037 -- Concatenate maps of the same type
0038 SELECT
0039     map_concat(boolean_map1, boolean_map2) boolean_map,
0040     map_concat(tinyint_map1, tinyint_map2) tinyint_map,
0041     map_concat(smallint_map1, smallint_map2) smallint_map,
0042     map_concat(int_map1, int_map2) int_map,
0043     map_concat(bigint_map1, bigint_map2) bigint_map,
0044     map_concat(decimal_map1, decimal_map2) decimal_map,
0045     map_concat(float_map1, float_map2) float_map,
0046     map_concat(double_map1, double_map2) double_map,
0047     map_concat(date_map1, date_map2) date_map,
0048     map_concat(timestamp_map1, timestamp_map2) timestamp_map,
0049     map_concat(string_map1, string_map2) string_map,
0050     map_concat(array_map1, array_map2) array_map,
0051     map_concat(struct_map1, struct_map2) struct_map,
0052     map_concat(string_int_map1, string_int_map2) string_int_map,
0053     map_concat(int_string_map1, int_string_map2) int_string_map
0054 FROM various_maps;
0055
0056 -- Concatenate maps of different types
0057 SELECT
0058     map_concat(tinyint_map1, smallint_map2) ts_map,
0059     map_concat(smallint_map1, int_map2) si_map,
0060     map_concat(int_map1, bigint_map2) ib_map,
0061     map_concat(bigint_map1, decimal_map2) bd_map,
0062     map_concat(decimal_map1, float_map2) df_map,
0063     map_concat(string_map1, date_map2) std_map,
0064     map_concat(timestamp_map1, string_map2) tst_map,
0065     map_concat(string_map1, int_map2) sti_map,
0066     map_concat(int_string_map1, tinyint_map2) istt_map
0067 FROM various_maps;
0068
0069 -- Concatenate map of incompatible types 1
0070 SELECT
0071     map_concat(tinyint_map1, array_map1) tm_map
0072 FROM various_maps;
0073
0074 -- Concatenate map of incompatible types 2
0075 SELECT
0076     map_concat(boolean_map1, int_map2) bi_map
0077 FROM various_maps;
0078
0079 -- Concatenate map of incompatible types 3
0080 SELECT
0081     map_concat(int_map1, struct_map2) is_map
0082 FROM various_maps;
0083
0084 -- Concatenate map of incompatible types 4
0085 SELECT
0086     map_concat(struct_map1, array_map2) ma_map
0087 FROM various_maps;
0088
0089 -- Concatenate map of incompatible types 5
0090 SELECT
0091     map_concat(int_map1, array_map2) ms_map
0092 FROM various_maps;