Materialize/test/sqllogictest/arrays.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

# Test parsing arrays from strings.

query T
SELECT '{o, oOOo, "oOOo", "}"}'::text[]
----
{o,oOOo,oOOo,"}"}

query T
SELECT '{1, 2, 3, null, NULL, nULL}'::int[]
----
{1,2,3,NULL,NULL,NULL}

query T
SELECT '{  1,   2  , 3 }'::int[]
----
{1,2,3}

query error invalid input syntax for type array: Array value must start with "\{": ""
SELECT ''::int[]

query error invalid input syntax for type array: Junk after closing right brace\.: "\{1, 2, 3\} 4"
SELECT '{1, 2, 3} 4'::int[]

query T
SELECT '{{1}, {2}}'::int[]
----
{{1},{2}}

query error invalid input syntax for type array: Specifying array lower bounds is not supported: "\[1:2\]=\{1,2\}"
SELECT '[1:2]={1,2}'::int[]

# Test coercion behavior of multidimensional arrays.

query error ARRAY could not convert type text\[\] to integer\[\]
SELECT ARRAY[ARRAY[1, 2], ARRAY['3', '4']]

query T
SELECT ARRAY[ARRAY[1, 2], ARRAY['3', '4']]::int[]
----
{{1,2},{3,4}}

# Test array_to_string.

query TT
SELECT array_to_string(ARRAY['a', 'b,', NULL, 'c'], ','), array_to_string(ARRAY['a', 'b,', NULL, 'c'], ',', NULL)
----
a,b,,c  a,b,,c

query TT
SELECT array_to_string(ARRAY['a', 'b,', 'c'], NULL), array_to_string(ARRAY['a', 'b,', NULL, 'c'], 'foo', 'zerp')
----
NULL  afoob,foozerpfooc

query TT
SELECT array_to_string(NULL::text[], ','), array_to_string(NULL::text[], 'foo', 'zerp')
----
NULL  NULL

query error could not determine polymorphic type because input has type unknown
SELECT array_to_string(NULL, ','), array_to_string(NULL, 'foo', 'zerp')

# Handle empty arrays as an input
query T
SELECT array_to_string('{}'::text[], '')
----
(empty)

# Test ANY/SOME/ALL.

query B
SELECT 1 = ANY(ARRAY[1, 2])
----
true

query B
SELECT 1 = SOME(ARRAY[1, 2])
----
true

query B
SELECT 1 = ANY(ARRAY[2])
----
false

query error operator does not exist: integer = text
SELECT 1 = ANY(ARRAY['1', '2'])

query B
SELECT 3 = ANY(ARRAY[ARRAY[1, 2], ARRAY[3,4]])
----
true

query error operator does not exist: integer = text
SELECT 1 = ANY(ARRAY['hi'::text])

query error invalid input syntax for type integer: invalid digit found in string: "hi"
select 'hi' = any(array[1]);

query error cannot determine type of empty array
SELECT 'hi'::text = ANY(ARRAY[])

query B
SELECT 'hi'::text = ANY(ARRAY[]::text[])
----
false

query error ARRAY types integer and boolean cannot be matched
SELECT 123.4 = ANY(ARRAY[1, true, 'hi'::text])

query B
SELECT 1 != ANY(ARRAY[1])
----
false

query B
select 'hello'::text != ANY(ARRAY['there'::text])
----
true

query B
select 'hello'::text <= ANY(ARRAY['there'::text])
----
true

query B
select 'apple' like any (VALUES('a%'), ('b%'));
----
true

query B
select 'apple' not like any (VALUES('a%'), ('b%'));
----
true

query B
select 'apple' ilike any (VALUES('A%'), ('B%'));
----
true

query B
select 'apple' like any (VALUES('A%'), ('B%'));
----
false

query B
select 'apple' ~~ any (VALUES('a%'), ('b%'));
----
true

query B
select 'apple' !~~ any (VALUES('a%'), ('b%'));
----
true

query B
select 'apple' ~~* any (VALUES('A%'), ('B%'));
----
true

# Test ALL

query B
SELECT 1 = ALL(ARRAY[1, 2])
----
false

query B
SELECT 5 <> ALL(ARRAY[ARRAY[1, 2], ARRAY[3,4]])
----
true

query B
select 'apple' like all (VALUES('a%'), ('b%'));
----
false

query B
select 'apple' like all (VALUES('a%'), ('appl%'));
----
true

query B
select 'apple' not like all (VALUES('a%'), ('b%'));
----
false

query B
select 'apple' ilike all (VALUES('A%'), ('B%'));
----
false

query B
select 'apple' like all (VALUES('A%'), ('B%'));
----
false

# 🔬🔬 unnest

query I rowsort
SELECT unnest FROM unnest(ARRAY[1,2,3])
----
1
2
3

query T rowsort
SELECT unnest::text FROM unnest(ARRAY[[1,2],[3,4]])
----
1
2
3
4

query T rowsort
SELECT unnest::text FROM unnest(ARRAY[NULL])
----
NULL

query I rowsort
SELECT unnest FROM unnest(ARRAY[NULL,1])
----
NULL
1

query T rowsort
SELECT unnest::text FROM unnest(NULL::int[])
----

query error function unnest\(unknown\) is not unique
SELECT * FROM unnest(NULL)

# array_agg

query T
SELECT array_agg(a) FROM (SELECT 1 AS a WHERE false)
----
NULL

query T
SELECT array_agg(1)
----
{1}

query T
select array_agg(unnest) FROM (SELECT NULL) x JOIN LATERAL unnest(ARRAY[1,2,NULL]) ON true;
----
{1,2,NULL}

statement ok
CREATE TABLE t1 (a int)

statement ok
INSERT INTO t1 VALUES (1), (2), (3), (NULL), (NULL)

query T
SELECT array_agg(a) FROM (select a from t1 where a IS NOT NULL)
----
{1,2,3}

query T
SELECT array_agg(a) FROM (select a from t1 where a IS NULL)
----
{NULL,NULL}

query T
SELECT array_agg(a) FROM t1
----
{1,2,3,NULL,NULL}

query T
SELECT array_agg(a::text) FROM t1
----
{1,2,3,NULL,NULL}

query T
SELECT array_agg(a) FILTER (WHERE a IS NOT NULL) FROM t1
----
{1,2,3}

query T
SELECT array_agg(a) FILTER (WHERE a IS NULL) FROM t1
----
{NULL,NULL}

query error function array_agg\(integer, integer\) does not exist
SELECT array_agg(1, 2)

statement ok
CREATE TABLE t2 (a int, b date)

statement ok
INSERT INTO t2 VALUES (1, date '2020-01-01'), (NULL, date '2020-01-02')

query T
SELECT array_agg((a, b) ORDER BY a DESC)::text FROM t2;
----
{"(,2020-01-02)","(1,2020-01-01)"}

query TTT
SELECT array_agg((a, b) ORDER BY a DESC)::text, array_agg(a ORDER BY a DESC), array_agg(b ORDER BY b DESC) FROM t2;
----
{"(,2020-01-02)","(1,2020-01-01)"} {NULL,1} {2020-01-02,2020-01-01}

query error arrays not yet supported
SELECT array_agg(ARRAY[1])

query error array_agg on char
SELECT array_agg('a'::char)

query error array_agg on char
SELECT array_agg('a'::char(2))

# array_agg with nested arrays
statement ok
CREATE TABLE t3 (a int[])

statement ok
INSERT INTO t3 VALUES (ARRAY[1]), (ARRAY[2]), (ARRAY[3])

query error arrays not yet supported
SELECT array_agg(a) FROM t3

# Duplicates of cockroach/array.slt. todo@jldlaughlin: Remove when we support that file.
# array subscript access

query T
SELECT ARRAY['a', 'b', 'c'][-1]
----
NULL

query T
SELECT ARRAY['a', 'b', 'c'][0]
----
NULL

query T
SELECT (ARRAY['a', 'b', 'c'])[2]
----
b

query T
SELECT ARRAY['a', 'b', 'c'][2]
----
b

query T
SELECT ARRAY['a', 'b', 'c'][4]
----
NULL

query T
SELECT ARRAY['a', 'b', 'c'][1.5 + 1.5]
----
c

query I
SELECT ARRAY[1, 2, 3][-1]
----
NULL

query I
SELECT ARRAY[1, 2, 3][0]
----
NULL

query I
SELECT ARRAY[1, 2, 3][2]
----
2

query I
SELECT ARRAY[1, 2, 3][4]
----
NULL

query I
SELECT ARRAY[1, 2, 3][1.5 + 1.5]
----
3

query T
SELECT ARRAY['a', 'b', 'c'][4][2]
----
NULL

query T
SELECT ARRAY[['a'], ['b'], ['c']][2][1]
----
b

query T
SELECT ARRAY[['a'], ['b'], ['c']][2]
----
NULL

statement ok
CREATE TABLE array_t (a int[]);

statement ok
INSERT INTO array_t VALUES (ARRAY[[[1,2],[3,4]],[[5,6],[7,8]]]);

query TTTTTTTT
SELECT
    a[1][1][1],
	a[1][1][2],
	a[1][2][1],
	a[1][2][2],
	a[2][1][1],
	a[2][1][2],
	a[2][2][1],
	a[2][2][2]
FROM array_t;
----
1 2 3 4 5 6 7 8

query T
SELECT ARRAY[[1,2,3], [4,5,6]][2][-1]
----
NULL

query T
SELECT ARRAY[1][null];
----
NULL

query T
SELECT ARRAY[[1]][1][null];
----
NULL

# This differs from Cockroach, but matches Postgres.
query T
SELECT ARRAY['a', 'b', 'c'][3.5]
----
NULL

# Array equality

query B
SELECT ARRAY[1,2,3] = ARRAY[1,2,3]
----
true

query B
SELECT ARRAY[1,2,4] = ARRAY[1,2,3]
----
false

query B
SELECT ARRAY[1,2,3] != ARRAY[1,2,3]
----
false

query B
SELECT ARRAY[1,2,4] != ARRAY[1,2,3]
----
true

query B
SELECT ARRAY[1,2,4] = NULL
----
NULL

# This behavior is surprising (one might expect that the result would be
# NULL), but it's how Postgres behaves.
query B
SELECT ARRAY[1,2,NULL] = ARRAY[1,2,3]
----
false

query BB
SELECT ARRAY[1] < ARRAY[1], ARRAY[1] <= ARRAY[1]
----
false true

query BB
SELECT ARRAY[1] < ARRAY[2], ARRAY[1] <= ARRAY[2]
----
true true

query BB
SELECT ARRAY[1] < ARRAY[[1]], ARRAY[1] <= ARRAY[[1]]
----
true true

query BB
SELECT ARRAY[2] < ARRAY[1, 2], ARRAY[2] <= ARRAY[1, 2]
----
false false

query BB
SELECT ARRAY[1] < ARRAY[NULL]::int[], ARRAY[1] <= ARRAY[NULL]::int[]
----
true true

query BB
SELECT ARRAY[1] > ARRAY[1], ARRAY[1] >= ARRAY[1]
----
false true

query BB
SELECT ARRAY[1] > ARRAY[2], ARRAY[1] >= ARRAY[2]
----
false false

query BB
SELECT ARRAY[1] > ARRAY[[1]], ARRAY[1] >= ARRAY[[1]]
----
false false

query BB
SELECT ARRAY[2] > ARRAY[1, 2], ARRAY[2] >= ARRAY[1, 2]
----
true true

query BB
SELECT ARRAY[1] > ARRAY[NULL]::int[], ARRAY[1] >= ARRAY[NULL]::int[]
----
false false

query error operator does not exist: integer\[\] = text\[\]
SELECT ARRAY[1,2,3] = ARRAY['1','2','3']

query error operator does not exist: integer\[\] <> text\[\]
SELECT ARRAY[1,2,3] != ARRAY['1','2','3']

query error operator does not exist: integer\[\] < text\[\]
SELECT ARRAY[1,2,3] < ARRAY['1','2','3']

query error operator does not exist: integer\[\] <= text\[\]
SELECT ARRAY[1,2,3] <= ARRAY['1','2','3']

query error operator does not exist: integer\[\] > text\[\]
SELECT ARRAY[1,2,3] > ARRAY['1','2','3']

query error operator does not exist: integer\[\] >= text\[\]
SELECT ARRAY[1,2,3] >= ARRAY['1','2','3']

query T
SELECT array_remove(ARRAY[1,2,3,2], 2)
----
{1,3}

query T
SELECT array_remove(ARRAY[1,2,3,2], 5)
----
{1,2,3,2}

query T
SELECT array_remove(ARRAY[1,2,3,NULL::INT], NULL::INT)
----
{1,2,3}

query T
SELECT array_remove(ARRAY[1,NULL::INT,2,3,NULL::INT], NULL::INT)
----
{1,2,3}

query T
SELECT array_remove(NULL::INT[], NULL::INT)
----
NULL

query T
SELECT array_remove(NULL::INT[], 1)
----
NULL

query T
SELECT array_remove(ARRAY[1,1,1], 1)
----
{}

query error removing elements from multidimensional arrays is not supported
SELECT array_remove(ARRAY[[1]], 1)

# array_cat

query T
SELECT array_cat(ARRAY[1, 2], ARRAY[3, 4])
----
{1,2,3,4}

query T
SELECT array_cat(ARRAY[1, 2], ARRAY[3])
----
{1,2,3}

query T
SELECT array_cat(ARRAY[1], ARRAY[2, 3])
----
{1,2,3}

query T
SELECT array_cat(ARRAY[]::INT[], ARRAY[]::INT[])
----
{}

query T
SELECT array_cat(ARRAY[[]]::INT[], ARRAY[[]]::INT[])
----
{}

query T
SELECT array_cat(ARRAY[[]]::INT[], ARRAY[[[[]]]]::INT[])
----
{}

query T
SELECT array_cat(ARRAY[[[[]]]]::INT[], ARRAY[[]]::INT[])
----
{}

query T
SELECT array_cat(ARRAY[1, 2], ARRAY[]::INT[])
----
{1,2}

query T
SELECT array_cat(ARRAY[1, 2], ARRAY[[]]::INT[])
----
{1,2}

query T
SELECT array_cat(ARRAY[1, 2], ARRAY[[[[[]]]]]::INT[])
----
{1,2}

query T
SELECT array_cat(ARRAY[[1, 2]], ARRAY[]::INT[])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[[1, 2]], ARRAY[[]]::INT[])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[[1, 2]], ARRAY[[[[[]]]]]::INT[])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[]::INT[], ARRAY[1,2])
----
{1,2}

query T
SELECT array_cat(ARRAY[[]]::INT[], ARRAY[1,2])
----
{1,2}

query T
SELECT array_cat(ARRAY[[[[[[]]]]]]::INT[], ARRAY[1,2])
----
{1,2}

query T
SELECT array_cat(ARRAY[]::INT[], ARRAY[[1,2]])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[[]]::INT[], ARRAY[[1,2]])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[[[[[[]]]]]]::INT[], ARRAY[[1,2]])
----
{{1,2}}

query T
SELECT array_cat(ARRAY[1,2], NULL::INT[])
----
{1,2}

query T
SELECT array_cat(NULL::INT[], ARRAY[1,2])
----
{1,2}

query T
SELECT array_cat(NULL::INT[], NULL::INT[])
----
NULL

query T
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[[5,6]])
----
{{1,2},{3,4},{5,6}}

query T
SELECT array_cat(ARRAY[[1,2]], ARRAY[[3,4],[5,6]])
----
{{1,2},{3,4},{5,6}}

query T
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6])
----
{{1,2},{3,4},{5,6}}

query T
SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]])
----
{{1,2},{3,4},{5,6}}

simple
SELECT array_cat(ARRAY[[1,2]], ARRAY[[3,4,5]]);
----
db error: ERROR: cannot concatenate incompatible arrays
DETAIL: Arrays with differing dimensions are not compatible for concatenation.

simple
SELECT array_cat(ARRAY[[[1,2]]], ARRAY[3,4]);
----
db error: ERROR: cannot concatenate incompatible arrays
DETAIL: Arrays of 3 and 1 dimensions are not compatible for concatenation.

query error
SELECT array_cat(ARRAY[1,2], ARRAY['3'])

# array concatenation operator

query T
SELECT ARRAY[1, 2] || ARRAY[3, 4]
----
{1,2,3,4}

query T
SELECT ARRAY[1, 2] || ARRAY[3]
----
{1,2,3}

query T
SELECT ARRAY[1] || ARRAY[2, 3]
----
{1,2,3}

query T
SELECT ARRAY[]::INT[] || ARRAY[]::INT[]
----
{}

query T
SELECT ARRAY[[]]::INT[] || ARRAY[[]]::INT[]
----
{}

query T
SELECT ARRAY[[]]::INT[] || ARRAY[[[[]]]]::INT[]
----
{}

query T
SELECT ARRAY[[[[]]]]::INT[] || ARRAY[[]]::INT[]
----
{}

query T
SELECT ARRAY[1, 2] || ARRAY[]::INT[]
----
{1,2}

query T
SELECT ARRAY[1, 2] || ARRAY[[]]::INT[]
----
{1,2}

query T
SELECT ARRAY[1, 2] || ARRAY[[[[[]]]]]::INT[]
----
{1,2}

query T
SELECT ARRAY[[1, 2]] || ARRAY[]::INT[]
----
{{1,2}}

query T
SELECT ARRAY[[1, 2]] || ARRAY[[]]::INT[]
----
{{1,2}}

query T
SELECT ARRAY[[1, 2]] || ARRAY[[[[[]]]]]::INT[]
----
{{1,2}}

query T
SELECT ARRAY[]::INT[] || ARRAY[1,2]
----
{1,2}

query T
SELECT ARRAY[[]]::INT[] || ARRAY[1,2]
----
{1,2}

query T
SELECT ARRAY[[[[[[]]]]]]::INT[] || ARRAY[1,2]
----
{1,2}

query T
SELECT ARRAY[]::INT[] || ARRAY[[1,2]]
----
{{1,2}}

query T
SELECT ARRAY[[]]::INT[] || ARRAY[[1,2]]
----
{{1,2}}

query T
SELECT ARRAY[[[[[[]]]]]]::INT[] || ARRAY[[1,2]]
----
{{1,2}}

query T
SELECT ARRAY[1,2] || NULL::INT[]
----
{1,2}

query T
SELECT NULL::INT[] || ARRAY[1,2]
----
{1,2}

query T
SELECT NULL::INT[] || NULL::INT[]
----
NULL

query T
SELECT ARRAY[[1,2],[3,4]] || ARRAY[[5,6]]
----
{{1,2},{3,4},{5,6}}

query T
SELECT ARRAY[[1,2]] || ARRAY[[3,4],[5,6]]
----
{{1,2},{3,4},{5,6}}

query T
SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6]
----
{{1,2},{3,4},{5,6}}

query T
SELECT ARRAY[1,2] || ARRAY[[3,4],[5,6]]
----
{{1,2},{3,4},{5,6}}

simple
SELECT ARRAY[[1,2]] || ARRAY[[3,4,5]];
----
db error: ERROR: cannot concatenate incompatible arrays
DETAIL: Arrays with differing dimensions are not compatible for concatenation.

simple
SELECT ARRAY[[[1,2]]] || ARRAY[3,4];
----
db error: ERROR: cannot concatenate incompatible arrays
DETAIL: Arrays of 3 and 1 dimensions are not compatible for concatenation.

query error no overload for integer\[\] || text\[\]: arguments cannot be implicitly cast to any implementation's parameters; try providing explicit casts
SELECT ARRAY[1,2] || ARRAY['3'])

# array_agg with maps
statement ok
CREATE TABLE t4 (a MAP[text=>int])

statement ok
INSERT INTO t4 VALUES ('{a=>123}'), ('{b=>456}')

query error array_agg on map\[text=>integer\] not yet supported
SELECT array_agg(a) FROM t4

# Verify nested arrays
statement ok
CREATE TABLE users (id int not null, other_field int not null)

statement ok
CREATE TABLE customer (id int not null, first_name string not null, last_name string not null, zip string not null)

statement ok
INSERT INTO users VALUES (1, 10), (2, 5), (3, 8);

statement ok
INSERT INTO customer VALUES (1, 'alice', 'lasta', '10003'::text), (2, 'bob', 'lastb', '10013'::text), (3, 'charlie', 'lastc', '11217'::text);

query error ARRAY could not convert type integer\[\] to text\[\]
SELECT ARRAY[ARRAY[customer.first_name, customer.last_name], ARRAY[customer.zip], ARRAY[customer.id]]::text FROM customer JOIN users ON customer.id = users.id ORDER BY users.other_field DESC LIMIT 2

query T
SELECT ARRAY[ARRAY[customer.first_name], ARRAY[customer.zip], ARRAY[customer.id::text]]::text FROM customer JOIN users ON customer.id = users.id ORDER BY users.other_field DESC LIMIT 2
----
{{alice},{10003},{1}}
{{charlie},{11217},{3}}

# Regression for database-issues#5326
query error array_in not yet supported
SELECT array_in('', 0, 0)
----

# Array casting for column expressions

statement ok
CREATE TABLE array_t2 (
    a int[],
    b string[][],
    c varchar[],
    d int[][]
);

statement ok
INSERT INTO array_t2 VALUES (
    array[1, 2, 3, 4, 5],
    array[['t1', 't2', 't3']],
    array['test1', 'test2', 'test3', 'test4']::varchar[],
    array[[0, 1, 2], [3, 4, 5], [6, 7, 8]]
);

query T
SELECT pg_typeof(a::string[]::int[]::text[]::float8[]) FROM array_t2;
----
double precision[]

query T
SELECT pg_typeof(a::string[]) FROM array_t2;
----
text[]

query T
SELECT pg_typeof(a::string[]::int[]) FROM array_t2;
----
integer[]

query error Evaluation error: invalid input syntax for type integer: invalid digit found in string: "t1"
SELECT b::int[] FROM array_t2;

query error CAST does not support casting from integer\[\] to date\[\]
SELECT a::date[] FROM array_t2;

query T
SELECT a::string[]::int[]::text[]::float8[] FROM array_t2;
----
{1,2,3,4,5}

query T
SELECT b::text[] FROM array_t2;
----
{{t1,t2,t3}}

query T
SELECT d::text[][1][1] FROM array_t2;
----
{{0,1,2},{3,4,5},{6,7,8}}

query T
SELECT (d::text[])[1][1] FROM array_t2;
----
0

query T
SELECT (d::text[])[1] FROM array_t2;
----
NULL

query T
SELECT CAST(a as text[]) FROM array_t2;
----
{1,2,3,4,5}

statement ok
UPDATE array_t2 SET c = NULL;

query T
SELECT c::int[] FROM array_t2;
----
NULL

query error CAST does not support casting from mz_aclitem\[\] to text\[\]
SELECT privileges::text[] FROM mz_views;

query error CAST does not support casting from regproc list to text list
SELECT (LIST[1299::regproc]::regproc list)::text list

# Array position

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'sun')
----
1

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'sun', 1)
----
1

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'sun', 2)
----
8

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'sun', 3)
----
8

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'mon')
----
2

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'mon', 1)
----
2

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'mon', 2)
----
2

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','sun'], 'mon', 3)
----
NULL

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','mon'], 'mon', -3)
----
2

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','mon'], 'x')
----
NULL

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','mon'], 'x', 1)
----
NULL

query I
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','mon'], null)
----
NULL

query I
SELECT array_position(null::text[], 'abc')
----
NULL

query I
SELECT array_position(null::text[], 'abc', null)
----
NULL

query I
SELECT array_position(ARRAY['sun'], null, null)
----
NULL

query error initial position must not be null
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat','mon'], 'mon', null)

query error searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY[['mon']]::text[], 'mon')

query error searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY[[null]]::text[], 'mon')

query error searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY[['mon']]::text[], 'mon', 1)

query error searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY[['mon']]::text[], null, 1)

query error searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY[['mon']]::text[], 'mon', null)

# multi-dimensional arrays
# how else can we handle whitespace terminals in strings?
query T rowsort
SELECT
    concat_ws(
        E'\t',
        v,
        array_length(v, 1),
        array_length(v, 2),
        array_length(v, 3),
        array_length(v, 4),
        v[1],
        v[1][1],
        v[1][1][1],
        v[1][1][1][1],
        'end'
    )
FROM (
    SELECT a::text[] AS v FROM (
        VALUES (null),
        ('{a}'),
        ('{""}'),
        ('{''}'),
        ('{" "}'),
        ('{" 🌍 "}'),
        ('{\\}'),
        ('{"{",\\}'),
        ('{\"\", \"\\\"\"}'),
        ('{null}'),
        ('{b,b}'),
        ('{{c},{c},{c}}'),
        ('{{a}}'),
        ('{{a},{a}}'),
        ('{{a,b,c},{a,b,c}}'),
        ('{{{b},{b}},{{b},{b}}}'),
        ('{{{c},{c},{c}}}'),
        ('{{"c", d},{"c", d},{"c", d}}'),
        ('{{c, null},{null, d},{"null", null}}'),
        ('{{{{b},{b}}},{{{b},{b}}},{{{b},{b}}}}'),
        ('{{{a}}}'),
        ('{{{a}},{{a}}}'),
        ('{{{a}},{{null}}}'),
        ('{}'),
        ('{{},{}}'),
        ('{{{}},{{}}}'),
        ('{{{null}},{{null}}}'),
        -- Can exceed max dims with empty array which consolidates down
        ('{{{{{{{},{}}}}}}}')
    ) AS x (a)
) AS x (v);
----
end
{}	end
{}	end
{}	end
{}	end
{""}	1		end
{'}	1	'	end
{a}	1	a	end
{NULL}	1	end
{" "}	1	 	end
{b,b}	2	b	end
{"\\"}	1	\	end
{{a}}	1	1	a	end
{"{","\\"}	2	{	end
{{a},{a}}	2	1	a	end
{{{a}}}	1	1	1	a	end
{" 🌍 "}	1	 🌍 	end
{{c},{c},{c}}	3	1	c	end
{{{a}},{{a}}}	2	1	1	a	end
{{a,b,c},{a,b,c}}	2	3	a	end
{{{c},{c},{c}}}	1	3	1	c	end
{"\"\"","\"\\\"\""}	2	""	end
{{{a}},{{NULL}}}	2	1	1	a	end
{{c,d},{c,d},{c,d}}	3	2	c	end
{{{NULL}},{{NULL}}}	2	1	1	end
{{{b},{b}},{{b},{b}}}	2	2	1	b	end
{{c,NULL},{NULL,d},{null,NULL}}	3	2	c	end
{{{{b},{b}}},{{{b},{b}}},{{{b},{b}}}}	3	1	2	1	b	end

# Test that whitespace produces same results
query T rowsort
SELECT
    concat_ws(
        E'\t',
        v,
        array_length(v, 1),
        array_length(v, 2),
        array_length(v, 3),
        array_length(v, 4),
        v[1],
        v[1][1],
        v[1][1][1],
        v[1][1][1][1]
    )
FROM (
    SELECT a::text[] AS v FROM (
        VALUES (null),
        ('       {a}'),
        ('{null}        '),
        ('{      b,b}'),
        ('{{c      },{c},{c}}'),
        ('{{a       }}'),
        ('{{a},        {a}}'),
        ('{{a,b,c},{    a,b,c}}'),
        ('{{{b},{b}},{{b},{b}}      }'),
        ('{{{c},{c},{c}}}         '),
        ('{  {  c  ,  d  }  ,  {  c  ,  d  }  ,  {  c  ,  d  }  }  ')
    ) AS x (a)
) AS x (v);
----
(empty)
{a}	1	a
{NULL}	1
{b,b}	2	b
{{a}}	1	1	a
{{a},{a}}	2	1	a
{{c},{c},{c}}	3	1	c
{{a,b,c},{a,b,c}}	2	3	a
{{{c},{c},{c}}}	1	3	1	c
{{c,d},{c,d},{c,d}}	3	2	c
{{{b},{b}},{{b},{b}}}	2	2	1	b

#
# Empty
query error invalid input syntax for type array: Array value must start with "\{": ""
SELECT ''::int[];

query error invalid input syntax for type array: Array value must start with "\{": "          "
SELECT '          '::int[];

#
# Missing elems
query error invalid input syntax for type array: Unexpected "\}" character\.: "\{1,\}"
SELECT '{1,}'::int[];

query error invalid input syntax for type array: Unexpected "," character\.: "\{,1\}"
SELECT '{,1}'::int[];

query error invalid input syntax for type array: Unexpected "," character\.: "\{,\}"
SELECT '{,}'::int[];

query error invalid input syntax for type array: Unexpected "\}" character\.: "\{\\" \\",\}"
SELECT '{" ",}'::int[];

query error invalid input syntax for type array: Unexpected "," character\.: "\{,\\" \\"\}"
SELECT '{," "}'::int[];

query error invalid input syntax for type array: Unexpected "\}" character\.: "\{',\}"
SELECT '{'',}'::int[];

query error invalid input syntax for type array: Unexpected "," character\.: "\{,'\}"
SELECT '{,''}'::int[];

#
# Escapes
query error invalid input syntax for type array: unterminated element: "\{\\\}"
SELECT '{\}'::text[];

#
# Single chars
query error invalid input syntax for type array: Unexpected end of input\.: "\{"
SELECT '{'::int[];

query error invalid input syntax for type array: Array value must start with "\{": "\}"
SELECT '}'::int[];

query error invalid input syntax for type array: Array value must start with "\{": ","
SELECT ','::int[];

query error invalid input syntax for type array: Array value must start with "\{": "a"
SELECT 'a'::int[];

query error invalid input syntax for type array: Array value must start with "\{": "'"
SELECT ''''::int[];

query error invalid input syntax for type array: Array value must start with "\{": "\\""
SELECT '"'::int[];

#
# Lopsided brackets
query error invalid input syntax for type array: Unexpected end of input\.: "\{\{a\}"
SELECT '{{a}'::int[];

query error invalid input syntax for type array: Junk after closing right brace\.: "\{a\}\}"
SELECT '{a}}'::int[];

query error invalid input syntax for type array: Unexpected end of input\.: "\{\{\}"
SELECT '{{}'::int[];

query error invalid input syntax for type array: Junk after closing right brace\.: "\{\}\}"
SELECT '{}}'::int[];

query error invalid input syntax for type array: Unexpected end of input\.: "\{  \{a\}"
SELECT '{  {a}'::int[];

query error invalid input syntax for type array: Junk after closing right brace\.: "\{  a\}\}"
SELECT '{  a}}'::int[];

query error invalid input syntax for type array: Unexpected end of input\.: "\{\{  \}"
SELECT '{{  }'::int[];

query error invalid input syntax for type array: Junk after closing right brace\.: "\{\}  \}"
SELECT '{}  }'::int[];

#
# Missing commas++
query error invalid input syntax for type array: Junk after closing right brace\.: "\{1\}\{1\}"
SELECT '{1}{1}'::text[];

query error invalid input syntax for type array: Unexpected "\{" character\.: "\{\{1\}\{1\}\}"
SELECT '{{1}{1}}'::text[];

query error invalid input syntax for type array: Junk after closing right brace\.: "\{\}\{\}"
SELECT '{}{}'::text[];

query error invalid input syntax for type array: Unexpected "\{" character\.: "\{\{\}\{\}\}"
SELECT '{{}{}}'::text[];

#
# Manged seps
query error invalid input syntax for type array: Unexpected array element\.: "\{\{1,2\},\\\{2,3\}\}"
SELECT E'{{1,2},\\{2,3}}'::text[];

query error invalid input syntax for type array: Unexpected array element\.: "\{\{\\"1 2\\" x\},\{3\}\}"
SELECT '{{"1 2" x},{3}}'::text[];

#
# Non-rectilinear
query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{1,\{2\}\},\{2,3\}\}"
SELECT '{{1,{2}},{2,3}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{1\},\{\{2\}\}\}"
SELECT '{{1},{{2}}}'::text[];

query error invalid input syntax for type array: Unexpected array element\.: "\{\{\{1\}\},\{2\}\}"
SELECT '{{{1}},{2}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{\},\{\{\}\}\}"
SELECT '{{},{{}}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{\{\}\},\{\}\}"
SELECT '{{{}},{}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{1\},\{\}\}"
SELECT '{{1},{}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{\},\{1\}\}"
SELECT '{{},{1}}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{1,2\},\{1\}\}"
SELECT '{{1,2},{1}}'::text[];

#
# Non-rectilinear w/ null
query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{null, \{1\}\}"
SELECT '{null, {1}}'::text[];

query error invalid input syntax for type array: Unexpected array element\.: "\{\{1\}, null\}"
SELECT '{{1}, null}'::text[];

query error invalid input syntax for type array: Multidimensional arrays must have sub\-arrays with matching dimensions\.: "\{\{\{null\}\},\{\{\}\}\}"
SELECT '{{{null}},{{}}}'::text[];

# Exceeded dimensions
query error number of array dimensions \(7\) exceeds the maximum allowed \(6\)
SELECT '{{{{{{{7}}}}}}}'::int[];

# We check max depth only after successfully parsing
query error invalid input syntax for type array: Unexpected end of input\.: "\{\{\{\{\{\{\{7\}\}\}\}\}\}"
SELECT '{{{{{{{7}}}}}}'::int[];


query T
SELECT ARRAY[1,4,3] @> ARRAY[3,1] AS contains
----
true

query T
SELECT ARRAY[1,4,3] <@ ARRAY[3,1] AS contains
----
false

# array containment in Postgres does NOT account for duplicates
query T
SELECT ARRAY[1,4,3] @> ARRAY[3,1,1,1,1,1,1,1,1,1,1,1,3,3,1,3,3,3,3,3,1,1,3,3,3] AS contains
----
true

query T
SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6] AS is_contained_by
----
true

query T
SELECT ARRAY[2,7] @> ARRAY[1,7,4,2,6] AS is_contained_by
----
false

# verify fix for issue database-issues#8223
query T
SELECT '{}'::TEXT[][] @> '{{a, b}, {a, A}}'::TEXT[][];
----
false

statement ok
CREATE TABLE t5 (row_index int, multi_dim_text_array_empty TEXT[][], multi_dim_text_array_two_elem TEXT[][]);

statement ok
INSERT INTO t5 SELECT 0, '{{}}'::TEXT[][], '{{a, b}, {a, A}}'::TEXT[][];

query T
SELECT ((multi_dim_text_array_empty) @> (multi_dim_text_array_two_elem)) FROM t5;
----
false


query T
SELECT '{}'::numeric[] @> '{}'::numeric[];
----
true

query T
SELECT '{1,2}'::numeric[] @> '{}'::numeric[];
----
true

query T
SELECT '{}'::numeric[] @> '{1,2}'::numeric[];
----
false

query T
SELECT '{NULL}'::numeric[] @> '{NULL}'::numeric[];
----
false

query T
SELECT '{NULL, 1}'::numeric[] @> '{1}'::numeric[];
----
true

query T
SELECT '{1}'::numeric[] @> '{1, NULL}'::numeric[];
----
false

query T
SELECT '{1, 2, 3, NULL}'::numeric[] @> '{1, NULL}'::numeric[];
----
false

query T
SELECT ARRAY[1,3,7,NULL] @> ARRAY[1,3,7,NULL] AS contains;
----
false

# Make sure we can index into a CAST-ed array.

statement ok
CREATE TABLE jsons (payload jsonb, random_index int, random_id uuid);

statement ok
CREATE MATERIALIZED VIEW json_mv AS (
    SELECT * FROM jsons WHERE random_id = CAST(payload->>'my_field' AS uuid[])[random_index]
)