map.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

# The MAP type has an uncommon OID. If tokio-postres (the driver used
# by sqllogictest) encounters an OID it doesn't recognize (MAP in
# this case), then it queries pg_type (a wrapper around mz_types) for
# information about it. Our MAP type currently doesn't have an entry in
# mz_types, so that query fails and tokio-postgres is unable to execute
# queries with MAPs. As a workaround until MAP is reflected in pg_type,
# we just convert everything to `text`.

# Test basic string to map casts.
query error VALUE TYPE option is required
CREATE TYPE custom AS MAP (KEY TYPE = text)

query error KEY TYPE option is required
CREATE TYPE custom AS MAP (VALUE TYPE = bool)

query error Expected one of KEY or VALUE, found identifier "extra_type"
CREATE TYPE custom AS MAP (KEY TYPE = text, VALUE TYPE = bool, extra_type=customthing)

query error KEY TYPE must be of class type, but received pg_catalog.pg_enum which is of class view
CREATE TYPE tbl_map AS MAP (KEY TYPE = pg_enum, VALUE TYPE = text)

query error VALUE TYPE must be of class type, but received pg_catalog.pg_enum which is of class view
CREATE TYPE tbl_map AS MAP (KEY TYPE = text, VALUE TYPE = pg_enum)

query error CREATE TYPE ... AS MAP option VALUE TYPE can only use named data types, but found unnamed data type pg_catalog.int4 list. Use CREATE TYPE to create a named type first
CREATE TYPE unnamed_element_map AS MAP (KEY TYPE = text, VALUE TYPE = int4 list)

statement ok
CREATE TYPE custom AS MAP (KEY TYPE = text, VALUE TYPE = bool)

query error expected '\{', found a: "a=>1"
SELECT ('a=>1'::map[text=>int])::text

query T
SELECT ('{a=>1}'::map[text=>int])::text
----
{a=>1}

query T
SELECT ('{  c  =>3, a=>     2, a => 1 }'::map[text=>int])::text
----
{a=>1,c=>3}

query error map key type must be text, got integer
SELECT '{1=>true}'::map[int=>bool]

query T
SELECT ('{1=>true}'::map[text=>bool])::text
----
{1=>t}

query T
SELECT ('{}'::map[text=>int])::text
----
{}

query error invalid input syntax for type boolean: "2.0"
SELECT ('{a=>1, b=>false, c=>2.0}'::map[text=>bool])::text

query T
SELECT ('{a\=\>=>2}'::map[text=>int])::text
----
{"a=>"=>2}

query T
SELECT ('{13=>hello \[\=\>     value\], 31=>  normal  }'::map[text=>text])::text
----
{13=>"hello [=>     value]",31=>normal}

query T
SELECT ('{"a"=>"hello there!", b=>"129387123"}'::map[text=>text])::text
----
{a=>"hello there!",b=>129387123}

query T
SELECT ('{key=>"here is a string => with a map operator in it"}'::map[text=>text])::text
----
{key=>"here is a string => with a map operator in it"}

query T
SELECT ('{31=> normal \ }'::map[text=>text])::text
----
{31=>"normal  "}

query T
SELECT ('{31=> \ normal }'::map[text=>text])::text
----
{31=>" normal"}

query error unterminated quoted string
SELECT ('{"a"=>"hello there!}'::map[text=>text])::text

### Can be cast back to text
query T
SELECT '{a=>1}'::map[text=>int]::text
----
{a=>1}

## Nested maps

query error expected '\{', found a: "a": "\{a=>a\}"
SELECT (('{a=>a}'::map[text=>map[text=>text]])::text)::text

query error expected =>: "\{a\}": "\{a=>\{a\}\}"
SELECT ('{a=>{a}}'::map[text=>map[text=>text]])::text

query error expected '\{', found b: "b": "\{a=>\{a=>a\}, b=>b\}"
SELECT ('{a=>{a=>a}, b=>b}'::map[text=>map[text=>text]])::text

query error unterminated embedded element
SELECT ('{hello=>{world=>broken'::map[text=>map[text=>text]])::text

query error unescaped '\{' at beginning of value; perhaps you want a nested map
SELECT ('{hello=>{world=>true}}'::map[text=>bool])::text

query T
SELECT ('{hello=>{world=>nested}}'::map[text=>map[text=>text]])::text
----
{hello=>{world=>nested}}

query error map key type must be text, got integer
SELECT '{hello=>{1=>false}}'::map[text=>map[int=>bool]]

query T
SELECT ('{hello=>{world=>"2020-11-23"}}'::map[text=>map[text=>timestamp]])::text
----
{hello=>{world=>"2020-11-23 00:00:00"}}

query T
SELECT ('{hello=>{\{\}=>\"\"}}'::map[text=>map[text=>text]])::text
----
{hello=>{"{}"=>"\"\""}}

# Test map operators.

## ?

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ? 'a'
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ? 'b'
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ? 'c'
----
false

query error operator does not exist: map\[text=>integer\] \? integer
SELECT '{a=>1, b=>2}'::map[text=>int] ? 1

query T
SELECT '{a=>1}'::map[text=>int] ? ''
----
false

query T
SELECT '{""=>1}'::map[text=>int] ? ''
----
true

query T
SELECT '{"1" => NULL}'::map[text=>text] ? ''
----
false

query T
SELECT '{hello=>{world=>false}}'::map[text=>map[text=>bool]] -> 'hello'::text ? 'world'::text
----
true

query error operator is not unique: unknown \? unknown
SELECT NULL ? 'a'

## ?&
query error invalid input syntax for type array: Array value must start with "\{": "a"
SELECT '{a=>1, b=>2}'::map[text=>int] ?& 'a'

query error operator does not exist: map\[text=>integer\] \?\& integer\[\]
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY[1]

query error cannot determine type of empty array
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY[]

query error could not determine polymorphic type because input has type unknown
SELECT NULL ?& 'a'

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY[NULL]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?& '{a}'
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY['a']
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY['b', 'a']
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?& ARRAY['c', 'b']
----
false

query error operator does not exist: map\[text=>boolean\] \?\& integer\[\]
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?& ARRAY[1]

query T
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?& ARRAY['1']
----
true

query T
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?& ARRAY['']
----
false

query T
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?& ARRAY['']
----
false

query T
SELECT '{hello=>{world=>123.40}}'::map[text=>map[text=>double]] -> 'hello'::text ?& ARRAY['world']
----
true

query T
SELECT '{hello=>{world=>1293}}'::map[text=>map[text=>smallint]] -> 'hello'::text ?& ARRAY['world', 'extra']
----
false

## ?|
query error invalid input syntax for type array: Array value must start with "\{": "a"
SELECT '{a=>1, b=>2}'::map[text=>int] ?| 'a'

query error operator does not exist: map\[text=>integer\] \?\| integer\[\]
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY[1]

query error could not determine polymorphic type because input has type unknown
SELECT NULL ?| 'a'

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY[NULL]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?| '{a}'
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY['a']
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY['c', 'b']
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] ?| ARRAY['c', 'd', '1']
----
false

query error operator does not exist: map\[text=>boolean\] \?\| integer\[\]
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?| ARRAY[1]

query T
SELECT '{1=>t, 2=>f}'::map[text=>bool] ?| ARRAY['1']
----
true

query T
SELECT '{hello=>{world=>63616665-6630-3064-6465-616462656568}}'::map[text=>map[text=>uuid]] -> 'hello'::text ?| ARRAY['world', 'extra']
----
true

query T
SELECT '{hello=>{world=>"2020-11-23"}}'::map[text=>map[text=>date]] -> 'hello'::text ?| ARRAY['missing']
----
false

## @>
query error invalid input syntax for type map: expected '\{', found c: "c"
SELECT '{a=>1, b=>2}'::map[text=>int] @> 'c'

query error operator does not exist: map\[text=>integer\] @> text
SELECT '{a=>1, b=>2}'::map[text=>int] @> 'a'::text

query error operator does not exist: map\[text=>integer\] @> integer\[\]
SELECT '{a=>1, b=>2}'::map[text=>int] @> ARRAY[1]

query error operator does not exist: map\[text=>integer\] @> map\[text=>boolean\]
SELECT '{a=>1, b=>2}'::map[text=>int] @> '{a=>t}'::map[text=>bool]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] @> '{a=>1}'::map[text=>int]
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] @> '{a=>1, b=>2}'::map[text=>int]
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] @> '{a=>10, b=>20}'::map[text=>int]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] @> '{a=>1, b=>2, c=>3}'::map[text=>int]
----
false

query error operator does not exist: map\[text=>map\[text=>bytea\]\] @> map\[text=>text\]
SELECT '{hello=>{world=>nested}}'::map[text=>map[text=>bytea]] @> '{hello=>world}'::map[text=>text]
----
false

query T
SELECT '{hello=>{world=>nested}}'::map[text=>map[text=>text]] @> '{hello=>{world=>nested}}'::map[text=>map[text=>text]]
----
true

query T
SELECT '{hello=>{world=>nested}}'::map[text=>map[text=>text]] @> '{hello=>{world=>nested}, extra=>{elements=>here}}'::map[text=>map[text=>text]]
----
false

## <@
query error operator does not exist: map\[text=>integer\] <@ map\[text=>boolean\]
SELECT '{a=>1, b=>2}'::map[text=>int] <@ '{a=>t}'::map[text=>bool]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] <@ '{a=>1}'::map[text=>int]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] <@ '{a=>1, b=>2}'::map[text=>int]
----
true

query T
SELECT '{a=>1, b=>2}'::map[text=>int] <@ '{a=>10, b=>20}'::map[text=>int]
----
false

query T
SELECT '{a=>1, b=>2}'::map[text=>int] <@ '{a=>1, b=>2, c=>3}'::map[text=>int]
----
true

query error CAST does not support casting from map\[text=>map\[text=>character\]\] to map\[text=>map\[text=>character\]\]
SELECT '{hello=>{world=>a}}'::map[text=>map[text=>char]] <@ '{hello=>c}'::map[text=>char]
----
false

query T
SELECT '{hello=>{world=>16}}'::map[text=>map[text=>oid]] <@ '{hello=>{world=>16}}'::map[text=>map[text=>oid]]
----
true

query T
SELECT '{hello=>{world=>nested}}'::map[text=>map[text=>text]] <@ '{hello=>{world=>nested}, extra=>{elements=>here}}'::map[text=>map[text=>text]]
----
true

## ->
query T
SELECT '{a=>1, b=>2}'::map[text=>int] -> ''
----
NULL

query T
SELECT '{a=>1, b=>2}'::map[text=>int] -> 'a'
----
1

query T
SELECT '{a=>1, b=>2}'::map[text=>int] -> 'b'
----
2

query T
SELECT '{a=>1, b=>2}'::map[text=>int] -> 'c'
----
NULL

query error operator does not exist: map\[text=>integer\] \-> integer
SELECT '{a=>1, b=>2}'::map[text=>int] -> 1

query T
SELECT '{a=>true, b=>false}'::map[text=>bool] -> 'b'
----
false

query T
SELECT ('{hello=>{world=>nested}, another=>{map=>here}}'::map[text=>map[text=>text]] -> 'missing'::text)::text
----
NULL

query T
SELECT ('{hello=>{world=>nested}, another=>{map=>here}}'::map[text=>map[text=>text]] -> 'hello'::text)::text
----
{world=>nested}

query T
SELECT '{hello=>{world=>nested}, another=>{map=>here}}'::map[text=>map[text=>text]] -> 'hello'::text -> 'world'::text
----
nested

query error operator is not unique: unknown \-> text
SELECT NULL -> 'hello'::text

# 🔬 CREATE TYPE .. AS MAP

statement ok
CREATE TYPE int4_map AS MAP (KEY TYPE = text, VALUE TYPE = int4);

query T
SELECT '{a=>1,b=>2}'::int4_map::text;
----
{a=>1,b=>2}

query T
SELECT pg_typeof(NULL::int4_map);
----
int4_map

# 🔬🔬 Check each valid value type

statement ok
CREATE TYPE bool_map_c AS MAP (KEY TYPE = text, VALUE TYPE = bool);

query T
SELECT '{a=>true}'::bool_map_c::text
----
{a=>t}

statement ok
CREATE TYPE int8_map_c AS MAP (KEY TYPE = text, VALUE TYPE = int8);

query T
SELECT '{a=>1}'::int8_map_c::text
----
{a=>1}

statement ok
CREATE TYPE int4_map_c AS MAP (KEY TYPE = text, VALUE TYPE = int4);

query T
SELECT '{a=>1}'::int4_map_c::text
----
{a=>1}

statement ok
CREATE TYPE text_map_c AS MAP (KEY TYPE = text, VALUE TYPE = text);

query T
SELECT '{a=>a}'::text_map_c::text
----
{a=>a}

statement ok
CREATE TYPE float4_map_c AS MAP (KEY TYPE = text, VALUE TYPE = float4);

query T
SELECT '{a=>1.2}'::float4_map_c::text
----
{a=>1.2}

statement ok
CREATE TYPE float8_map_c AS MAP (KEY TYPE = text, VALUE TYPE = float8);

query T
SELECT '{a=>1.2}'::float8_map_c::text
----
{a=>1.2}

statement ok
CREATE TYPE date_map_c AS MAP (KEY TYPE = text, VALUE TYPE = date);

query T
SELECT '{a=>2001-01-01}'::date_map_c::text
----
{a=>2001-01-01}

statement ok
CREATE TYPE time_map_c AS MAP (KEY TYPE = text, VALUE TYPE = time);

query T
SELECT '{a=>12:34:56}'::time_map_c::text
----
{a=>12:34:56}

statement ok
CREATE TYPE timestamp_map_c AS MAP (KEY TYPE = text, VALUE TYPE = timestamp);

query T
SELECT '{a=>2001-01-01 12:34:56}'::timestamp_map_c::text
----
{a=>"2001-01-01 12:34:56"}

statement ok
CREATE TYPE timestamptz_map_c AS MAP (KEY TYPE = text, VALUE TYPE = timestamptz);

query T
SELECT '{a=>2001-01-01 12:34:56}'::timestamptz_map_c::text
----
{a=>"2001-01-01 12:34:56+00"}

statement ok
CREATE TYPE interval_map_c AS MAP (KEY TYPE = text, VALUE TYPE = interval);

query T
SELECT '{a=>1y 2d 3h 4m}'::interval_map_c::text
----
{a=>"1 year 2 days 03:04:00"}

statement ok
CREATE TYPE numeric_map_c AS MAP (KEY TYPE = text, VALUE TYPE = numeric);

query T
SELECT '{a=>1.23}'::numeric_map_c::text
----
{a=>1.23}

statement ok
CREATE TYPE jsonb_map_c AS MAP (KEY TYPE = text, VALUE TYPE = jsonb);

query T
SELECT '{a=>\{\"1\":2\}}'::jsonb_map_c::text;
----
{a=>"{\"1\":2}"}

# 🔬🔬 Check custom type name resolution

statement ok
CREATE TYPE bool AS MAP (KEY TYPE = text, VALUE TYPE = int4)

query error invalid input syntax for type boolean: "\{a=>1\}"
SELECT '{a=>1}'::bool;

query T
SELECT '{a=>1}'::public.bool::text;
----
{a=>1}

# 🔬🔬 Check subtype resolution

# Supports qualified subtypes
statement ok
CREATE TYPE qualified_int4_map AS MAP (KEY TYPE = pg_catalog.text, VALUE TYPE = pg_catalog.int4)

statement ok
CREATE TYPE qualified_qualified_int4_map AS MAP (KEY TYPE = pg_catalog.text, VALUE TYPE = public.qualified_int4_map)

# Supports type aliases
statement ok
CREATE TYPE int_map AS MAP (KEY TYPE = pg_catalog.text, VALUE TYPE = int)

# `map_length`
query T
SELECT map_length('{}'::map[text=>int])
----
0

query T
SELECT map_length('{a=>1}'::map[text=>int])
----
1

query T
SELECT map_length('{a=>1, b=>2}'::map[text=>int])
----
2

query T
SELECT map_length(NULL::map[text=>int])
----
NULL