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

# Misc. types

query T
SELECT array_fill(5, array[3])
----
{5,5,5}

query T
SELECT array_fill(5.1::double, array[3])
----
{5.1,5.1,5.1}

query T
SELECT array_fill(5.1, array[3])
----
{5.1,5.1,5.1}

query T
SELECT array_fill('z'::text, array[3])
----
{z,z,z}

query T
SELECT array_fill(INTERVAL '1d', array[3])
----
{"1 day","1 day","1 day"}

query T
SELECT array_fill('[1,)'::int4range, array[3])
----
{"[1,)","[1,)","[1,)"}

query T
SELECT (array_fill(5, array[3]))[1]
----
5

# Lower bound adjustment works
query T
SELECT array_fill(5, array[3], array[2])
----
[2:4]={5,5,5}

query T
SELECT array_fill(5, array[3, 1], array[2, 4])
----
[2:4][4:4]={{5},{5},{5}}

query T
SELECT (array_fill(5, array[3], array[2]))[1]
----
NULL

query T
SELECT (array_fill(5, array[3], array[2]))[2]
----
5

query error wrong number of array subscripts
SELECT array_fill(5, array[3], array[2, 1])

query error wrong number of array subscripts
SELECT array_fill(5, array[3, 1], array[2])

query error wrong number of array subscripts
SELECT array_fill(5, array[]::int[], array[2, 1])

query error wrong number of array subscripts
SELECT array_fill(5, array[3, 1], array[]::int[])

# Reveal structure of array
query T
SELECT
    concat_ws(' ', o, i, COALESCE(((array_fill(5, ARRAY[3, 2]))[o][i])::text, 'null'))
FROM generate_series(1, 4) AS o, generate_series(1, 4) AS i
ORDER BY 1;
----
1 1 5
1 2 5
1 3 null
1 4 null
2 1 5
2 2 5
2 3 null
2 4 null
3 1 5
3 2 5
3 3 null
3 4 null
4 1 null
4 2 null
4 3 null
4 4 null

query T
SELECT array_fill(5, array[3, 2], array[2, 3])
----
[2:4][3:4]={{5,5},{5,5},{5,5}}

# Reveal structure of 2D array
query T
SELECT
    concat_ws(' ', o, i, COALESCE(((array_fill(5, ARRAY[3, 2], ARRAY[2, 3]))[o][i])::text, 'null'))
FROM generate_series(1, 4) AS o, generate_series(1, 4) AS i
ORDER BY 1;
----
1 1 null
1 2 null
1 3 null
1 4 null
2 1 null
2 2 null
2 3 5
2 4 5
3 1 null
3 2 null
3 3 5
3 4 5
4 1 null
4 2 null
4 3 5
4 4 5

# Reveal structure of 3D array
query T
SELECT
    concat_ws(' ', a, b, c, COALESCE(((array_fill(5, ARRAY[3, 2, 1], ARRAY[1, 2, 3]))[a][b][c])::text, 'null'))
FROM
    generate_series(1, 3) AS a,
    generate_series(1, 3) AS b,
    generate_series(1, 3) AS c
ORDER BY 1;
----
1 1 1 null
1 1 2 null
1 1 3 null
1 2 1 null
1 2 2 null
1 2 3 5
1 3 1 null
1 3 2 null
1 3 3 5
2 1 1 null
2 1 2 null
2 1 3 null
2 2 1 null
2 2 2 null
2 2 3 5
2 3 1 null
2 3 2 null
2 3 3 5
3 1 1 null
3 1 2 null
3 1 3 null
3 2 1 null
3 2 2 null
3 2 3 5
3 3 1 null
3 3 2 null
3 3 3 5

# Polymorphic solution

query error db error: ERROR: could not determine polymorphic type because input has type unknown
SELECT array_fill(null, array[3])

query error db error: ERROR: could not determine polymorphic type because input has type unknown
SELECT array_fill(null, null)

query T
SELECT array_fill(null::int, array[3])
----
{NULL,NULL,NULL}

# Prohibited types

query error array_fill with arrays not yet supported
SELECT array_fill(ARRAY[1], array[3, 2])

query error array_fill with arrays not yet supported
SELECT array_fill(ARRAY[1], array[3, 2], array[2, 3])

query error array_fill on integer list not yet supported
SELECT array_fill(LIST[1], array[3, 2])

query error array_fill on integer list not yet supported
SELECT array_fill(LIST[1], array[3, 2], array[2, 3])

query error db error: ERROR: array_fill on char\(1\) not yet supported
SELECT array_fill('c'::char, array[3, 2])

query error db error: ERROR: array_fill on char\(1\) not yet supported
SELECT array_fill('c'::char, array[3, 2], array[2, 3])

query error array_fill on map\[text=>integer\] not yet supported
SELECT array_fill('{}'::map[text=>int], array[3, 2])

query error array_fill on map\[text=>integer\] not yet supported
SELECT array_fill('{}'::map[text=>int], array[3, 2], array[2, 3])

# Null errors

query error dimension array or low bound array must not be null
SELECT array_fill(1, null);

query error dimension array or low bound array must not be null
SELECT array_fill(null::int, null);

query error dimension array or low bound array must not be null
SELECT array_fill(1, ARRAY[8], null);

query error dimension array or low bound array must not be null
SELECT array_fill(1, null, ARRAY[8]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[null::int]);

query error dimension values must not be null
SELECT array_fill(null::int, ARRAY[null::int], ARRAY[8]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[8], ARRAY[null::int]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[null::int], ARRAY[null::int]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[6, null::int]);

query error dimension values must not be null
SELECT array_fill(null::int, ARRAY[6, null::int], ARRAY[8]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[8], ARRAY[6, null::int]);

query error dimension values must not be null
SELECT array_fill(1, ARRAY[6, null::int], ARRAY[6, null::int]);

# Multi-dimensional w/ 0
query T
SELECT array_fill(1, ARRAY[0]);
----
{}

query T
SELECT array_fill(1, ARRAY[4, 0]);
----
{}

query T
SELECT array_fill(1, ARRAY[0, 4]);
----
{}

query T
SELECT array_fill(1, ARRAY[4, 3, 0]);
----
{}

query T
SELECT array_fill(1, ARRAY[4, 0, 3]);
----
{}

# Too large of array

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(2, ARRAY[-1]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(2, ARRAY[-1]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(1, ARRAY[4, -199]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(1, ARRAY[-199, 4]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(1, ARRAY[4, 3, -199]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(1, ARRAY[4, -199, 3]);

query error array size exceeds the maximum allowed \(134217728 bytes\)
SELECT array_fill(8, ARRAY[-1, -1, -1, -1]);

query error number of array dimensions \(10\) exceeds the maximum allowed \(6\)
SELECT array_fill(1, ARRAY[1,1,1,1,1,1,1,1,1,1]);

query error number of array dimensions \(10\) exceeds the maximum allowed \(6\)
SELECT array_fill(1, ARRAY[1,1,1,1,1,1,1,1,1,1], ARRAY[1,1,1,1,1,1,1,1,1,1]);

# But large arrays are still ok

query II
SELECT array_length(a, 1), array_length(a, 2) FROM (
    SELECT array_fill(1, ARRAY[99, 101]) AS a
);
----
99
101

query II
SELECT array_length(a, 1), array_length(a, 2) FROM (
    SELECT array_fill(1, ARRAY[99, 101], ARRAY[2, 3]) AS a
);
----
99
101

# Concatenating arrays

query error cannot concatenate incompatible arrays
SELECT array_fill(1, ARRAY[2], ARRAY[1] || array_fill(1, ARRAY[3, 2], ARRAY[4, 1]));

# RHS is element of LHS

query T
SELECT array_fill(6, ARRAY[3, 2], ARRAY[4, 3]) || array_fill(7, ARRAY[2], ARRAY[3]);
----
[4:7][3:4]={{6,6},{6,6},{6,6},{7,7}}

# LHS is element of RHS
query T
SELECT array_fill(6, ARRAY[2], ARRAY[3]) || array_fill(7, ARRAY[3, 2], ARRAY[4, 3]);
----
[4:7][3:4]={{6,6},{7,7},{7,7},{7,7}}

# Array || Array
query T
SELECT array_fill(6, ARRAY[3, 2], ARRAY[4, 3]) || array_fill(7, ARRAY[3, 2], ARRAY[4, 3]);
----
[4:9][3:4]={{6,6},{6,6},{6,6},{7,7},{7,7},{7,7}}

# Negative lower bounds

query T
SELECT array_fill(3, ARRAY[2], ARRAY[-3]);
----
[-3:-2]={3,3}

query I
SELECT (array_fill(3, ARRAY[2], ARRAY[-3]))[-3];
----
3

query T
SELECT
    concat_ws(E'\t', o, i, COALESCE(((array_fill(5, ARRAY[2, 2], ARRAY[-2, -1]))[o][i])::text, 'null'))
FROM generate_series(-3, 0) AS o, generate_series(-2, 0) AS i
ORDER BY o, i;
----
-3	-2	null
-3	-1	null
-3	0	null
-2	-2	null
-2	-1	5
-2	0	5
-1	-2	null
-1	-1	5
-1	0	5
0	-2	null
0	-1	null
0	0	null