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: malformed array literal: missing opening left brace
SELECT ''::int[]

query error invalid input syntax for type array: malformed array literal: junk after closing right brace
SELECT '{1, 2, 3} 4'::int[]

# This ought to be supported one day, but it is a lot of work.
query error parsing multi-dimensional arrays is not supported
SELECT '{{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 Cannot call function array_to_string\(unknown, unknown\): 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 no overload for integer = text: arguments cannot be implicitly cast
SELECT 1 = ANY(ARRAY['1', '2'])

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

query error no overload for integer = text: arguments cannot be implicitly cast
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

# Test ALL

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

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

# 🔬🔬 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 Cannot call function unnest\(unknown\): unable to determine which implementation to use; try providing explicit casts to match parameter types
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;
----
{NULL,1,2}

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
----
{NULL,NULL,1,2,3}

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

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 arguments cannot be implicitly cast to any implementation's parameters
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

# todo(uce): uncomment after #5982
#query BB
#SELECT ARRAY[1] > ARRAY[NULL]::int[], ARRAY[1] >= ARRAY[NULL]::int[]
#----
#false false

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,3] = ARRAY['1','2','3']

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,3] != ARRAY['1','2','3']

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,3] < ARRAY['1','2','3']

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,3] <= ARRAY['1','2','3']

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,3] > ARRAY['1','2','3']

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,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}}