testdata/workloads/functional-query/queries/QueryTest/nested-map-in-select-list.test

====
---- QUERY
select id, int_map from complextypestbl
---- RESULTS
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
---- TYPES
bigint,string
====
---- QUERY
select id, int_map from complextypestbl where id=1
---- RESULTS
1,'{"k1":1,"k2":100}'
---- TYPES
bigint,string
====
---- QUERY
select id, int_map, int_map_array from complextypestbl
---- RESULTS
1,'{"k1":1,"k2":100}','[{"k1":1}]'
2,'{"k1":2,"k2":null}','[{"k3":null,"k1":1},null,{}]'
3,'{}','[null,null]'
4,'{}','[]'
5,'{}','NULL'
6,'NULL','NULL'
7,'{"k1":null,"k3":null}','NULL'
8,'{"k1":-1}','[{},{"k1":1},{},{}]'
---- TYPES
bigint,string,string
====
---- QUERY
# Same collection used twice in a select list.
select id, int_map, int_map from complextypestbl
---- RESULTS
1,'{"k1":1,"k2":100}','{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}','{"k1":2,"k2":null}'
3,'{}','{}'
4,'{}','{}'
5,'{}','{}'
6,'NULL','NULL'
7,'{"k1":null,"k3":null}','{"k1":null,"k3":null}'
8,'{"k1":-1}','{"k1":-1}'
---- TYPES
bigint,string,string
====
---- QUERY
# Same collection used from two versions of the same table/
select t1.id, t1.int_map, t2.int_map
 from complextypestbl t1 join complextypestbl t2
 on t1.id = t2.id
---- RESULTS
1,'{"k1":1,"k2":100}','{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}','{"k1":2,"k2":null}'
3,'{}','{}'
4,'{}','{}'
5,'{}','{}'
6,'NULL','NULL'
7,'{"k1":null,"k3":null}','{"k1":null,"k3":null}'
8,'{"k1":-1}','{"k1":-1}'
---- TYPES
bigint,string,string
====
---- QUERY
select id, int_map from complextypestbl union all select id, int_map from complextypestbl
---- RESULTS
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
---- TYPES
bigint,string
====
---- QUERY
# TODO: only UNION ALL is supported. UNION needs several utility functions in the BE, so
# for now we reject it in the FE.
select id, int_map from complextypestbl union select id, int_map from complextypestbl;
---- CATCH
IllegalStateException: UNION, EXCEPT and INTERSECT are not supported for collection types
====
---- QUERY
# Changing a column to a different type leads to a "non-pass-through" union that does a
# deepcopy on the tuple.
select id, map_int_int from map_non_varlen
  union all select cast(id as tinyint), map_int_int from map_non_varlen
---- RESULTS
1,'{10:100,11:110,12:120}'
2,'{20:200,21:210,22:220}'
3,'{30:300,31:310,32:320}'
4,'{40:400,41:410,42:420}'
5,'{50:500,51:510,52:520}'
6,'{60:600,61:610,62:620}'
7,'{70:700,71:710,72:720}'
8,'{80:800,81:810,82:820}'
9,'{90:900,91:910,92:920}'
10,'{100:1000,101:1010,102:1020}'
1,'{10:100,11:110,12:120}'
2,'{20:200,21:210,22:220}'
3,'{30:300,31:310,32:320}'
4,'{40:400,41:410,42:420}'
5,'{50:500,51:510,52:520}'
6,'{60:600,61:610,62:620}'
7,'{70:700,71:710,72:720}'
8,'{80:800,81:810,82:820}'
9,'{90:900,91:910,92:920}'
10,'{100:1000,101:1010,102:1020}'
---- TYPES
int,string
====
---- QUERY
# Changing a column to a different type leads to a "non-pass-through" union that does a
# deepcopy on the tuple.
select id, map_1d, map_3d from collection_tbl
  union all select cast(id as tinyint), map_1d, map_3d from collection_tbl
---- RESULTS
1,'{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
1,'{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
---- TYPES
int,string,string
====
---- QUERY
# Constants in the select list of unions also lead to "non-pass-through" union.
select 1, map_int_int from map_non_varlen
  union all select 2, map_int_int from map_non_varlen;
---- RESULTS
1,'{10:100,11:110,12:120}'
1,'{20:200,21:210,22:220}'
1,'{30:300,31:310,32:320}'
1,'{40:400,41:410,42:420}'
1,'{50:500,51:510,52:520}'
1,'{60:600,61:610,62:620}'
1,'{70:700,71:710,72:720}'
1,'{80:800,81:810,82:820}'
1,'{90:900,91:910,92:920}'
1,'{100:1000,101:1010,102:1020}'
2,'{10:100,11:110,12:120}'
2,'{20:200,21:210,22:220}'
2,'{30:300,31:310,32:320}'
2,'{40:400,41:410,42:420}'
2,'{50:500,51:510,52:520}'
2,'{60:600,61:610,62:620}'
2,'{70:700,71:710,72:720}'
2,'{80:800,81:810,82:820}'
2,'{90:900,91:910,92:920}'
2,'{100:1000,101:1010,102:1020}'
---- TYPES
tinyint,string
====
---- QUERY
# Constants in the select list of unions also lead to a "non-pass-through" union.
select 1, map_1d, map_3d from collection_tbl
  union all select 2, map_1d, map_3d from collection_tbl
---- RESULTS
1,'{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}'
1,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
1,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
2,'{1:"first automobile",2:"second"}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}'
2,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}'
---- TYPES
tinyint,string,string
====
---- QUERY
# Constants in the select list of unions also lead to a "non-pass-through" union and
# structs containing collections are not allowed there yet because we can't materialise
# them, see IMPALA-12160. However, set operations on structs are not supported at all at
# present, and the error reflects that. When both limitations are lifted, the query should
# succeed.
select 1, struct_contains_map, all_mix from collection_struct_mix
  union all select 2, struct_contains_map, all_mix from collection_struct_mix
---- CATCH
AnalysisException: Set operations don't support STRUCT types or types containing STRUCT types. STRUCT<m:MAP<INT,STRING>> in struct_contains_map.
====
---- QUERY
select 1 from (select int_map from complextypestbl) s
---- RESULTS
1
1
1
1
1
1
1
1
---- TYPES
tinyint
====
---- QUERY
# Regression test for IMPALA-12695.
with v0 as (select int_map from complextypestbl
  union all select int_map from complextypestbl),
     v1 as (select m.key from v0, v0.int_map m)
select key from v1;
---- RESULTS
'k1'
'k1'
'k2'
'k1'
'k2'
'k1'
'k3'
'k1'
'k1'
'k2'
'k1'
'k2'
'k1'
'k3'
---- TYPES
string
====
---- QUERY
# Filtering an unnested collection that comes from a UNION [ALL] is not supported, see
# IMPALA-12753.
with v0 as (select int_map from complextypestbl
  union all select int_map from complextypestbl),
     v1 as (select m.key from v0, v0.int_map m)
select key from v1 where key="k1";
---- CATCH
AnalysisException: Filtering an unnested collection that comes from a UNION [ALL] is not supported yet.
====
---- QUERY
select id, int_map from (select id, int_map from complextypestbl) s;
---- RESULTS
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
---- TYPES
bigint,string
====
---- QUERY
with s as (select id, t.int_map from complextypestbl t)
select id, int_map from s;
---- RESULTS
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
---- TYPES
bigint,string
====
---- QUERY
select id, int_map from complextypes_maps_view;
---- RESULTS
1,'{"k1":1,"k2":100}'
2,'{"k1":2,"k2":null}'
3,'{}'
4,'{}'
5,'{}'
6,'NULL'
7,'{"k1":null,"k3":null}'
8,'{"k1":-1}'
---- TYPES
bigint,string
====
---- QUERY
# Unnesting map returned by view.
select id, m.key, m.value from complextypes_maps_view v, v.int_map m;
---- RESULTS
1,'k1',1
1,'k2',100
2,'k1',2
2,'k2',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned from WITH clause and predicate in inner query.
with v as (select id, int_map from complextypestbl where id=1)
select v.id, a.key, a.value from v, v.int_map a;
---- RESULTS
1,'k1',1
1,'k2',100
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned from WITH clause and predicate in outer query.
with v as (select id, int_map from complextypestbl)
select v.id, a.key, a.value from v, v.int_map a where id=1;
---- RESULTS
1,'k1',1
1,'k2',100
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned from WITH clause on item.
with v as (select id, int_map from complextypestbl)
select v.id, a.key, a.value from v, v.int_map a where a.key='k1'
---- RESULTS
1,'k1',1
2,'k1',2
7,'k1',NULL
8,'k1',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned by view wrapped in inline view.
select v.id, a.key, a.value from
  (select id, int_map from complextypes_maps_view) v, v.int_map a;
---- RESULTS
1,'k1',1
1,'k2',100
2,'k1',2
2,'k2',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned by view wrapped in inline view + WITH clause.
with v2 as (select id, int_map from complextypes_maps_view)
select v.id, a.key, a.value from (select id, int_map from v2) v, v.int_map a;
---- RESULTS
1,'k1',1
1,'k2',100
2,'k1',2
2,'k2',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
bigint,string,int
====
---- QUERY
# Unnesting map returned by view wrapped in inline view + WITH clause.
with v2 as (select id, int_map from complextypes_maps_view)
select v.id, a.key, a.value from
  (select id, int_map from v2 where id=1) v, v.int_map a
  where a.key='k1';
---- RESULTS
1,'k1',1
---- TYPES
bigint,string,int
====
---- QUERY
select item from unnest(complextypestbl.int_map_array)
---- RESULTS
'{"k1":1}'
'{"k3":null,"k1":1}'
'NULL'
'{}'
'NULL'
'NULL'
'{}'
'{"k1":1}'
'{}'
'{}'
---- TYPES
string
====
---- QUERY
select id, a.key, a.value from complextypes_maps_view t left join t.int_map a;
---- RESULTS
1,'k1',1
1,'k2',100
2,'k1',2
2,'k2',NULL
3,'NULL',NULL
4,'NULL',NULL
5,'NULL',NULL
6,'NULL',NULL
7,'k1',NULL
7,'k3',NULL
8,'k1',-1
---- TYPES
BIGINT,STRING,INT
====
---- QUERY
select id, a2.key, a2.value from complextypes_maps_view v, v.int_map_array a1, a1.item a2;
---- RESULTS
1,'k1',1
2,'k3',NULL
2,'k1',1
8,'k1',1
---- TYPES
BIGINT,STRING,INT
====
---- QUERY
# Regression test for:
#   IMPALA-11434: "More than 1 2d arrays in select list causes analysis error"
select id, map_1d, map_2d, map_3d, arr_int_3d, map_map_array from collection_tbl;
---- RESULTS
1,'{1:"first automobile",2:"second"}','{1:{10:"ten",20:"twentieth paragraph"},2:{30:"thirty minutes",40:"forty"}}','{1:{10:{100:"hundred",200:"two hundred pages"},20:{300:"three hundred pages",400:"four hundred"}},2:{30:{500:"five hundred pages",600:"six hundred"},40:{700:"seven hundred pages",800:"eight hundred"}}}','[[[1,2,null],[3]],[[4]]]','{1:{10:[100,200],20:[300,400]},2:{30:[500,600],40:[700,800]}}'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:"ten dinosaur bones",20:"20"},2:{30:"thirty dinosaur bones",40:"forty dinosaur bones"}}','{1:{10:{100:"hundred",200:"two hundred dinosaur bones"},20:{300:"three hundred dinosaur bones",400:"four hundred"}},2:{30:{500:"five hundred dinosaur bones",600:"six hundred"},40:{700:"seven hundred dinosaur bones",800:"eight hundred"}}}','[[[null,1,2,null],[5,14,null]],[[null,5]]]','{1:{10:[100,200],20:[300,400]},2:{30:[500,600],40:[700,800]}}'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:"ten",20:"twentieth even-toed ungulate"},2:{30:"thirty even-toed ungulates",40:"forty"}}','{1:{10:{100:"hundred",200:"two hundred even-toed ungulates"},20:{300:"three hundred even-toed ungulates",400:"four hundred"}},2:{30:{500:"five hundred even-toed ungulates",600:"six hundred"},40:{700:"seven hundred even-toed ungulates",800:"eight hundred"}}}','[[[1,null,2,null],[null,15]],[[null,4]]]','{1:{10:[100,200],20:[300,400]},2:{30:[500,600],40:[700,800]}}'
---- TYPES
INT,STRING,STRING,STRING,STRING,STRING
====
---- QUERY
select id, map_1d, map_2d, mma.value mma_value, ma.value ma_value
from collection_tbl c, c.map_map_array mma, mma.value ma;
---- RESULTS
1,'{1:"first automobile",2:"second"}','{1:{10:"ten",20:"twentieth paragraph"},2:{30:"thirty minutes",40:"forty"}}','{10:[100,200],20:[300,400]}','[100,200]'
1,'{1:"first automobile",2:"second"}','{1:{10:"ten",20:"twentieth paragraph"},2:{30:"thirty minutes",40:"forty"}}','{10:[100,200],20:[300,400]}','[300,400]'
1,'{1:"first automobile",2:"second"}','{1:{10:"ten",20:"twentieth paragraph"},2:{30:"thirty minutes",40:"forty"}}','{30:[500,600],40:[700,800]}','[500,600]'
1,'{1:"first automobile",2:"second"}','{1:{10:"ten",20:"twentieth paragraph"},2:{30:"thirty minutes",40:"forty"}}','{30:[500,600],40:[700,800]}','[700,800]'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:"ten dinosaur bones",20:"20"},2:{30:"thirty dinosaur bones",40:"forty dinosaur bones"}}','{10:[100,200],20:[300,400]}','[100,200]'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:"ten dinosaur bones",20:"20"},2:{30:"thirty dinosaur bones",40:"forty dinosaur bones"}}','{10:[100,200],20:[300,400]}','[300,400]'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:"ten dinosaur bones",20:"20"},2:{30:"thirty dinosaur bones",40:"forty dinosaur bones"}}','{30:[500,600],40:[700,800]}','[500,600]'
2,'{1:"first dinosaur bone",2:"second",3:null}','{1:{10:"ten dinosaur bones",20:"20"},2:{30:"thirty dinosaur bones",40:"forty dinosaur bones"}}','{30:[500,600],40:[700,800]}','[700,800]'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:"ten",20:"twentieth even-toed ungulate"},2:{30:"thirty even-toed ungulates",40:"forty"}}','{10:[100,200],20:[300,400]}','[100,200]'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:"ten",20:"twentieth even-toed ungulate"},2:{30:"thirty even-toed ungulates",40:"forty"}}','{10:[100,200],20:[300,400]}','[300,400]'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:"ten",20:"twentieth even-toed ungulate"},2:{30:"thirty even-toed ungulates",40:"forty"}}','{30:[500,600],40:[700,800]}','[500,600]'
3,'{645:"fourth even-toed ungulate",5:"fifth"}','{1:{10:"ten",20:"twentieth even-toed ungulate"},2:{30:"thirty even-toed ungulates",40:"forty"}}','{30:[500,600],40:[700,800]}','[700,800]'
---- TYPES
INT,STRING,STRING,STRING,STRING
====
---- QUERY
-- Test that map keys are printed correctly.
set CONVERT_LEGACY_HIVE_PARQUET_UTC_TIMESTAMPS=1;
select
 map_bool_key,
 map_tinyint_key,
 map_smallint_key,
 map_bigint_key,
 map_float_key,
 map_double_key,
 map_decimal_key,
 map_string_key,
 map_char_key,
 map_varchar_key,
 map_timestamp_key,
 map_date_key
from collection_tbl;
---- RESULTS
'{true:"true",false:"false statement"}','{-1:"a nice sunny day",0:"best day in my life",1:"c"}','{-1:"a nice sunny day",0:"best day in my life",1:"c"}','{-1:"a nice sunny day",0:"best day in my life",1:"c"}','{-1.5:"a nice sunny day",0.25:"best day in my life",1.75:"c"}','{-1.5:"a nice sunny day",0.25:"best day in my life",1.75:"c"}','{-1.8:"a nice sunny day",0.2:"best day in my life",1.2:"c"}','{"one":1,"two":2,"three distinct values":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 hours","2022-12-09":"Friday"}'
'{true:"true",false:"false dinosaur bones"}','{-1:"a nice dinosaur bone",0:"best dinosaur bone",1:"c"}','{-1:"a nice dinosaur bone",0:"best dinosaur bone",1:"c"}','{-1:"a nice dinosaur bone",0:"best dinosaur bone",1:"c"}','{-1.5:"a nice dinosaur bone",0.25:"best dinosaur bone",1.75:"c"}','{-1.5:"a nice dinosaur bone",0.25:"best dinosaur bone",1.75:"c"}','{-1.8:"a nice dinosaur bone",0.2:"best dinosaur bone",1.2:"c"}','{"one":1,"two":2,"three distinct dinosaur bones":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 dinosaur bones","2022-12-09":"Friday"}'
'{true:"true even-toed ungulate",false:"false"}','{-1:"a nice even-toed ungulate",0:"best even-toed ungulate",1:"c"}','{-1:"a nice even-toed ungulate",0:"best even-toed ungulate",1:"c"}','{-1:"a nice even-toed ungulate",0:"best even-toed ungulate",1:"c"}','{-1.5:"a nice even-toed ungulate",0.25:"best even-toed ungulate",1.75:"c"}','{-1.5:"a nice even-toed ungulate",0.25:"best even-toed ungulate",1.75:"c"}','{-1.8:"a nice even-toed ungulate",0.2:"best even-toed ungulate",1.2:"c"}','{"one":1,"two":2,"three distinct even-toed ungulates":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 even-toed ungulates","2022-12-09":"Friday"}'
---- TYPES
STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING
====
---- QUERY
-- Test that map keys are printed correctly with STRINGIFY_MAP_KEYS=true.
set CONVERT_LEGACY_HIVE_PARQUET_UTC_TIMESTAMPS=1;
set STRINGIFY_MAP_KEYS=1;
select
 map_bool_key,
 map_tinyint_key,
 map_smallint_key,
 map_bigint_key,
 map_float_key,
 map_double_key,
 map_decimal_key,
 map_string_key,
 map_char_key,
 map_varchar_key,
 map_timestamp_key,
 map_date_key
from collection_tbl;
---- RESULTS
'{"true":"true","false":"false statement"}','{"-1":"a nice sunny day","0":"best day in my life","1":"c"}','{"-1":"a nice sunny day","0":"best day in my life","1":"c"}','{"-1":"a nice sunny day","0":"best day in my life","1":"c"}','{"-1.5":"a nice sunny day","0.25":"best day in my life","1.75":"c"}','{"-1.5":"a nice sunny day","0.25":"best day in my life","1.75":"c"}','{"-1.8":"a nice sunny day","0.2":"best day in my life","1.2":"c"}','{"one":1,"two":2,"three distinct values":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 hours","2022-12-09":"Friday"}'
'{"true":"true","false":"false dinosaur bones"}','{"-1":"a nice dinosaur bone","0":"best dinosaur bone","1":"c"}','{"-1":"a nice dinosaur bone","0":"best dinosaur bone","1":"c"}','{"-1":"a nice dinosaur bone","0":"best dinosaur bone","1":"c"}','{"-1.5":"a nice dinosaur bone","0.25":"best dinosaur bone","1.75":"c"}','{"-1.5":"a nice dinosaur bone","0.25":"best dinosaur bone","1.75":"c"}','{"-1.8":"a nice dinosaur bone","0.2":"best dinosaur bone","1.2":"c"}','{"one":1,"two":2,"three distinct dinosaur bones":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 dinosaur bones","2022-12-09":"Friday"}'
'{"true":"true even-toed ungulate","false":"false"}','{"-1":"a nice even-toed ungulate","0":"best even-toed ungulate","1":"c"}','{"-1":"a nice even-toed ungulate","0":"best even-toed ungulate","1":"c"}','{"-1":"a nice even-toed ungulate","0":"best even-toed ungulate","1":"c"}','{"-1.5":"a nice even-toed ungulate","0.25":"best even-toed ungulate","1.75":"c"}','{"-1.5":"a nice even-toed ungulate","0.25":"best even-toed ungulate","1.75":"c"}','{"-1.8":"a nice even-toed ungulate","0.2":"best even-toed ungulate","1.2":"c"}','{"one":1,"two":2,"three distinct even-toed ungulates":3}','{"Mon":1,"Tue":2,"Wed":3,"Thu":4,"Fri":5,"Sat":6,"Sun":7}','{"a":"A","ab":"AB","abc":"ABC"}','{"2022-12-10 08:15:12":"Saturday morning","2022-12-09 18:15:12":"Friday evening"}','{"2022-12-10":"Saturday 24 even-toed ungulates","2022-12-09":"Friday"}'
---- TYPES
STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING,STRING
====
---- QUERY
select distinct int_map from complextypestbl
---- CATCH
AnalysisException: Complex types are not supported in SELECT DISTINCT clauses.
====
---- QUERY
select count(distinct int_map) from complextypestbl;
---- CATCH
AnalysisException: Complex types are not supported as DISTINCT parameters of aggregate functions.
====