Materialize/test/sqllogictest/list.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 LIST type has an uncommon OID. If tokio-postres (the driver used
# by sqllogictest) encounters an OID it doesn't recognize (LIST in
# this case), then it queries pg_type (a wrapper around mz_types) for
# information about it. Our LIST type currently doesn't have an entry in
# mz_types, so that query fails and tokio-postgres is unable to execute
# queries with LISTs. As a workaround until LIST is reflected in pg_type,
# we just convert everything to `text`.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_list_n_layers = true
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_list_length_max = true
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_list_remove = true
----
COMPLETE 0


query T
SELECT (LIST[1,2,3])::text
----
{1,2,3}

query error LIST types integer and integer list cannot be matched
SELECT LIST[1,LIST[2,3]]

query T
SELECT (LIST[[1],[2,3]])::text
----
{{1},{2,3}}

query error cannot determine type of empty list
SELECT LIST[]

query T
SELECT (LIST[] :: INT LIST)::text
----
{}

query T
SELECT (LIST[null])::text
----
{NULL}

query T
SELECT (LIST[1, null])::text
----
{1,NULL}

query T
SELECT (LIST[1, null] :: INT LIST)::text
----
{1,NULL}

query T
SELECT (LIST[[1, null], []] :: INT LIST LIST)::text
----
{{1,NULL},{}}

# Lists support arbitrarily deep nesting
query T
SELECT (LIST[[[[1], [2]]], [[[3]]]])::text
----
{{{{1},{2}}},{{{3}}}}

# List(Int) cannot be cast to List(List(Int))
query error LIST could not convert type integer to integer list
SELECT LIST[1, null] :: INT LIST LIST

query T
SELECT (LIST[1, null] :: TEXT LIST)::text
----
{1,NULL}

query T
SELECT (LIST['foo', 'f}o', '"\', null, 'null', 'NULL'])::text
----
{foo,"f}o","\"\\",NULL,null,"NULL"}

query T
SELECT (list[list[list['"']]])::text
----
{{{"\""}}}

query T
SELECT (list['{1}'])::text
----
{"{1}"}

query T
SELECT (LIST[LIST[]::text list, LIST['a', 'b'], LIST['z']])::text
----
{{},{a,b},{z}}

# 🔬 list subscripts

# 🔬🔬 list indexes

query R
SELECT LIST [1, 2, 3][2]
----
2

# exceeds maximum index
query R
SELECT LIST [1, 2, 3][100]
----
NULL

# negative
query R
SELECT LIST [1, 2, 3][-1]
----
NULL

# exceeds maximum layer
query error cannot index into
SELECT LIST [1, 2, 3][1][1]

# exceeds maximum layer
query error cannot subscript
SELECT LIST[1,2,3][1:1][1][1:1];

query error cannot subscript
SELECT LIST[1,2,3][1][1:1][1];

# 🔬🔬 list slices

query T
SELECT (LIST [1, 2, 3][2:3])::text
----
{2,3}

query T
SELECT (LIST [1, 2, 3][2:])::text
----
{2,3}

query T
SELECT (LIST [1, 2, 3][:2])::text
----
{1,2}

query T
SELECT (LIST [1, 2, 3][:])::text
----
{1,2,3}

# start exceeds maximum index
query T
SELECT (LIST [1, 2, 3][100:])::text
----
{}

# end exceeds maximum index
query T
SELECT (LIST [1, 2, 3][:100])::text
----
{1,2,3}

# 🔬🔬 list repeated slices

query T
SELECT (LIST [1, 2, 3, 4, 5][3:5][1:2][:])::text
----
{3,4}

# 🔬🔬 list slices + index

query T
SELECT (LIST [[1],[2],[3]][2:3])[2]::text
----
{3}

# 🔬 list list subscripts

# 🔬🔬 list list indexes

query T
SELECT (LIST [[1, 2, 3], [4, 5]][1])::text
----
{1,2,3}

query R
SELECT LIST [[1, 2, 3], [4, 5]][1][3]
----
3

# exceeds maximum index
query T
SELECT (LIST [[1, 2, 3], [4, 5]][100])::text
----
NULL

query T
SELECT (LIST [[1, 2, 3], [4, 5]][1][100])::text
----
NULL

query T
SELECT (LIST [[1, 2, 3], [4, 5]][100][1])::text
----
NULL

# exceeds maximum layer
query error cannot index into
SELECT LIST [[1, 2, 3], [4, 5]][1][1][1]

# 🔬🔬 list list slices

query T
SELECT (LIST [[1, 2, 3], [4, 5]][2:2])::text
----
{{4,5}}

query T
SELECT (LIST [[1, 2, 3], [4, 5]][2:])::text
----
{{4,5}}

# end exceeds maximum index
query T
SELECT (LIST [[1, 2, 3], [4, 5]][:100][:])::text
----
{{1,2,3},{4,5}}

# 🔬🔬🔬 index + slice

query T
SELECT list[list[1,2], list[3,4]][2][2:2]::text;
----
{4}

query T
SELECT list[[1,2], list[3,4]][2][2:]::text;
----
{4}

query T
SELECT list[[1,2], list[3,4]][2][:2]::text;
----
{3,4}

# 🔬🔬🔬 slice + index

query T
SELECT list[list[1,2], list[3,4]][2:2][2]::text;
----
NULL

query T
SELECT list[list[1,2], list[3,4]][:2][2]::text;
----
{3,4}

query T
SELECT list[[1,2], list[3,4]][2:][2]::text;
----
NULL

# 🔬🔬🔬 layered list slices patterns, linear

query T
SELECT list[list[1,2], list[3,4]][1:2][2:2]::text;
----
{{3,4}}

query T
SELECT list[list[1,2], list[3,4]][:2][2:2]::text;
----
{{3,4}}

query T
SELECT list[list[1,2], list[3,4]][:2][2:]::text;
----
{{3,4}}

query T
SELECT list[list[1,2], list[3,4]][:][2:]::text;
----
{{3,4}}

# 🔬🔬 list list slices + index

query T
SELECT (LIST [[1, 2, 3], [4, 5]][2:2])[1][2]::text
----
5

# 🔬 list list list

# 🔬🔬 list list list indexes

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1])::text
----
{{1,2},{3,4,5}}

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][2])::text
----
{3,4,5}

query R
SELECT LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][2][3]
----
5

# exceeds maximum index
query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][100])::text
----
NULL

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][100])::text
----
NULL

query R
SELECT LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][2][100]
----
NULL

query R
SELECT LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][100][2][3]
----
NULL

query R
SELECT LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][100][3]
----
NULL

# exceeds maximum layer
query error cannot index into
SELECT LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1][2][3][1]

# 🔬🔬 list list list slices

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1:2])::text
----
{{{1,2},{3,4,5}},{{6}}}

# start exceeds maximum index
query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][100:100])::text
----
{}

# end exceeds maximum index
query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][:100])::text
----
{{{1,2},{3,4,5}},{{6}},{{7,8},{9}}}

# 🔬🔬🔬 layered list repeated slices

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][2:3][:][2:2])::text
----
{{{7,8},{9}}}

# 🔬🔬 list list list slices + index

query T
SELECT (LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][2:3])[2]::text
----
{{7,8},{9}}

query T
SELECT (LIST[1, 2, 3][NULL])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][NULL:NULL])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][1:NULL])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][NULL:1])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][NULL:])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][:NULL])::text
----
NULL

query T
SELECT (LIST[NULL][:])::text
----
{NULL}

query T
SELECT (LIST[1, NULL, 3][:NULL])::text
----
NULL

query T
SELECT ((LIST [[1, NULL, 3], NULL, [4, NULL, 6]]::INT LIST LIST)[2:])::text
----
{NULL,{4,NULL,6}}

# 🔬🔬 Slices and NULLs

# NULL literals are not touched by slice operations
query T
SELECT ((LIST[NULL]::INT LIST)[1:1])::text
----
{NULL}

# 🔬 Empty lists expressions

query T
SELECT ((LIST[]::INT LIST)[1])::text
----
NULL

query T
SELECT ((LIST[]::INT LIST)[:])::text
----
{}

query T
SELECT ((LIST[]::INT LIST)[1:1])::text
----
{}

# 🔬 Other subscript values

# 🔬🔬 end > start

query T
SELECT (LIST[1, 2, 3][2:1])::text
----
{}

# 🔬🔬 Negative values

query T
SELECT (LIST[1, 2, 3][-100])::text
----
NULL

query T
SELECT (LIST[1, 2, 3][-100:])::text
----
{1,2,3}

query T
SELECT (LIST[1, 2, 3][-100:99])::text
----
{1,2,3}

query T
SELECT (LIST[1, 2, 3][-100:-99])::text
----
{}

query T
SELECT (LIST[1, 2, 3][-100:-101])::text
----
{}

query T
SELECT (LIST[1, 2, 3][:-100])::text
----
{}

# 🔬🔬 min/max

query R
SELECT LIST[1][9223372036854775807::bigint]
----
NULL

query R
SELECT LIST[1][-9223372036854775807::bigint]
----
NULL

query T
SELECT (LIST[1][9223372036854775807::bigint:9223372036854775807::bigint])::text
----
{}

query T
SELECT (LIST[1][9223372036854775807::bigint:-9223372036854775807::bigint])::text
----
{}

query T
SELECT (LIST[1][-9223372036854775807::bigint:9223372036854775807::bigint])::text
----
{1}

query T
SELECT (LIST[1][-9223372036854775807::bigint:-9223372036854775807::bigint])::text
----
{}

# 🔬 Non-int subscript values

# 🔬🔬 Ok

query R
SELECT LIST[1,2,3][1.4]
----
1

query R
SELECT LIST[1,2,3][1.5]
----
2

query R
SELECT LIST[1,2,3][1.5::real]
----
2

query R
SELECT LIST[1,2,3][1.5::float]
----
2

query R
SELECT LIST[1,2,3][1.5 + 1.6]
----
3

query T
SELECT (LIST[1,2,3][0.1 * 2 : 0.5 + 1.6])::text
----
{1,2}

query T
SELECT (LIST[1,2,3][LIST[1][2.0 / 2]])::text
----
1

# 🔬🔬 Err

query error invalid input syntax for type bigint: invalid digit found in string: "dog"
SELECT LIST[1,2,3]['dog']

query error subscripting does not support casting from date to bigint
SELECT LIST [[1, 2, 3], [4, 5]][DATE '2001-01-01']

query error subscripting does not support casting from timestamp without time zone to bigint
SELECT LIST [[1, 2, 3], [4, 5]][TIMESTAMP '2001-01-01']

query error invalid input syntax for type bigint: invalid digit found in string: "dog"
SELECT (LIST[1,2,3][1:'dog'])::text

query error subscripting does not support casting from date to bigint
SELECT LIST [[1, 2, 3], [4, 5]][1:DATE '2001-01-01']

query error subscripting does not support casting from timestamp without time zone to bigint
SELECT LIST [[1, 2, 3], [4, 5]][1:TIMESTAMP '2001-01-01']

# 🔬 Built-in functions

# 🔬🔬 list_append

# 🔬🔬🔬 list + element

query T
SELECT (list_append(LIST[1, 2], 3))::text
----
{1,2,3}

# 🔬🔬🔬 list list + list (list + element)

query T
SELECT (list_append(LIST[[1], [2]], LIST[3]))::text
----
{{1},{2},{3}}

# 🔬🔬🔬🔬 polymorphism

query T
SELECT pg_typeof(list_append(LIST[1::int2], 1::int8))
----
bigint list

query T
SELECT pg_typeof(list_append(LIST[1::int8], 1::int2))
----
bigint list

# 🔬🔬🔬 NULL elements

# 🔬🔬🔬🔬 list + element

query T
SELECT (list_append(LIST[1], NULL))::text
----
{1,NULL}

query T
SELECT (list_append(NULL, 1))::text
----
{1}

query T
SELECT (list_append(LIST[1], NULL))::text
----
{1,NULL}

query T
SELECT (list_append(NULL, NULL::INT))::text
----
{NULL}

query T
SELECT (list_append(NULL::INT LIST, NULL))::text
----
{NULL}

# 🔬🔬🔬🔬 list list + list (list + element)

query T
SELECT (list_append(LIST[[1]], NULL))::text
----
{{1},NULL}

query T
SELECT (list_append(NULL, LIST[1]))::text
----
{{1}}

query T
SELECT (list_append(NULL::INT LIST LIST, NULL))::text
----
{NULL}

query T
SELECT (list_append(NULL, NULL::INT LIST))::text
----
{NULL}

# 🔬🔬🔬 errors

query T
SELECT list_append(NULL, NULL)::text
----
{NULL}

query error invalid input syntax for type integer
SELECT list_append(LIST[1], 'a')::text

query error db error: ERROR: function list_append\(integer list, integer list\) does not exist
SELECT list_append(LIST[1], LIST[2])

query error db error: ERROR: function list_append\(integer, integer list\) does not exist
SELECT list_append(1, LIST[1])

# 🔬🔬 list_cat

# 🔬🔬🔬 list + list

query T
SELECT (list_cat(LIST[1, 2], LIST[3, 4]))::text
----
{1,2,3,4}

query T
SELECT (list_cat(LIST[[1]], LIST[[2]]))::text
----
{{1},{2}}

# 🔬🔬🔬 NULL elements

# 🔬🔬🔬🔬 list + list

query T
SELECT (list_cat(LIST[1], NULL))::text
----
{1}

query T
SELECT (list_cat(LIST[1], NULL))::text
----
{1}

query T
SELECT (list_cat(NULL, LIST[1]))::text
----
{1}

query T
SELECT (list_cat(NULL::INT LIST, NULL))::text
----
NULL

query T
SELECT (list_cat(NULL, NULL::INT LIST))::text
----
NULL

# 🔬🔬🔬🔬 polymorphism

query T
SELECT pg_typeof(list_cat(LIST[1::int2], LIST[1::int8]))
----
bigint list

query T
SELECT pg_typeof(list_cat(LIST[1::int8], LIST[1::int2]))
----
bigint list

# 🔬🔬🔬 errors

query error
SELECT list_cat(NULL, NULL)

query error
SELECT list_cat('a', LIST[1])

query error
SELECT list_cat(LIST[1], LIST[[2]])

# 🔬🔬 list_n_layers

query R
SELECT list_n_layers(LIST [1, 2, 3])
----
1

query R
SELECT list_n_layers(LIST []::INT LIST)
----
1

query R
SELECT list_n_layers(LIST[[1],[1]])
----
2

query R
SELECT list_n_layers(LIST [[[1], [1]], [[1]]])
----
3

# 🔬🔬🔬 slices

query R
SELECT list_n_layers(LIST[[1],[1]][1:1])
----
2

query R
SELECT list_n_layers(LIST [[[1], [1]], [[1]]][1:2])
----
3

# 🔬🔬🔬 interior lists

query R
SELECT list_n_layers(LIST[[1],[1]][1])
----
1

query R
SELECT list_n_layers(LIST [[[1], [1]], [[1]]][1])
----
2

query R
SELECT list_n_layers(LIST [[[1], [1]], [[1]]][1][1])
----
1

# 🔬🔬🔬 NULL elements

query R
SELECT list_n_layers(LIST[NULL]::INT LIST)
----
1

query R
SELECT list_n_layers(LIST[NULL]::INT LIST LIST)
----
2

query R
SELECT list_n_layers(LIST[[1], NULL]::INT LIST LIST)
----
2

query R
SELECT list_n_layers((LIST[[1],NULL]::INT LIST LIST)[2])
----
1

query error could not determine polymorphic type because input has type unknown
SELECT list_n_layers(NULL)

# 🔬🔬 list_length

query R
SELECT list_length(LIST [1])
----
1

query R
SELECT list_length(LIST [1, 1])
----
2

query R
SELECT list_length(LIST[[1],[1]])
----
2

query R
SELECT list_length(LIST [[[1], [1]], [[1]]])
----
2

query R
SELECT list_length(LIST []::INT LIST)
----
0

# 🔬🔬🔬 slices

query R
SELECT list_length(LIST[[1],[1]][1:1])
----
1

query R
SELECT list_length(LIST [[[1, 2], [3, 4, 5]], [[6]], [[7, 8], [9]]][1:2])
----
2

# 🔬🔬🔬 indexing operations

query R
SELECT list_length(LIST [[[1], [1]], [[1]]][1])
----
2

query R
SELECT list_length(LIST [[[1], [1]], [[1]]][1][1])
----
1

# 🔬🔬🔬 NULL elements

query R
SELECT list_length(LIST[1, NULL]::INT LIST)
----
2

query R
SELECT list_length(LIST[[1],NULL]::INT LIST LIST)
----
2

query R
SELECT list_length((LIST[[1],NULL]::INT LIST LIST)[2])
----
NULL

query error could not determine polymorphic type because input has type unknown
SELECT list_length(NULL)

# 🔬🔬 list_length_max

query R
SELECT list_length_max(LIST [1, 2, 3], 1)
----
3

query R
SELECT list_length_max(LIST [1, 2, 3, 4], 1)
----
4

query R
SELECT list_length_max(LIST[[1],[2,3]], 2)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]], 2)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]], 3)
----
3

query R
SELECT list_length_max(LIST []::INT LIST, 1)
----
0

query R
SELECT list_length_max(LIST [[]]::INT LIST LIST, 2)
----
0

# 🔬🔬🔬 slices

query R
SELECT list_length_max(LIST[[1], [2]][1:1], 1)
----
1

query R
SELECT list_length_max(LIST[[1], [2, 3]][2:2], 2)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1:2], 1)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1:1], 2)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1:1], 3)
----
3

# 🔬🔬🔬 indexing operations

query R
SELECT list_length_max(LIST[[1], [2, 3]][2], 1)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1], 1)
----
2

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1], 2)
----
3

query R
SELECT list_length_max(LIST [[[1, 2, 3], [4]], [[5]]][1][2], 1)
----
1

# 🔬🔬🔬 NULL elements

query R
SELECT list_length_max(NULL::INT LIST, 1)
----
NULL

query R
SELECT list_length_max(LIST[1, NULL, 3]::INT LIST, 1)
----
3

query R
SELECT list_length_max(LIST[[1],NULL]::INT LIST LIST, 1)
----
2

query R
SELECT list_length_max((LIST[[1],NULL]::INT LIST LIST), 2)
----
1

query T
SELECT (list_length_max((LIST[NULL]::INT LIST LIST), 2))::text
----
NULL

# 🔬🔬🔬 errors

query error invalid layer: 2; must use value within \[1, 1\]
SELECT list_length_max((LIST[1]::INT LIST), 2)

query error invalid layer: 2; must use value within \[1, 1\]
SELECT list_length_max((LIST[NULL]::INT LIST), 2)

query error invalid layer: 3; must use value within \[1, 2\]
SELECT list_length_max((LIST[NULL]::INT LIST LIST), 3)

query error invalid layer: 0; must use value within \[1, 1\]
SELECT list_length_max((LIST[1]::INT LIST), 0)

query error invalid layer: -1; must use value within \[1, 1\]
SELECT list_length_max((LIST[1]::INT LIST), -1)

query error invalid layer: -1; must use value within \[1, 1\]
SELECT list_length_max((LIST[1]::INT LIST), LIST[-1][1])

query error could not determine polymorphic type because input has type unknown
SELECT list_length_max(NULL, 1)

# 🔬🔬 list_prepend

# 🔬🔬🔬 element + list

query T
SELECT (list_prepend(1, LIST[2, 3]))::text
----
{1,2,3}

# 🔬🔬🔬 list + list list (element + list)

query T
SELECT (list_prepend(LIST[1], LIST[[2], [3]]))::text
----
{{1},{2},{3}}

# 🔬🔬🔬🔬 polymorphism

query T
SELECT pg_typeof(list_prepend(1::int2, LIST[1::int8]))
----
bigint list

query T
SELECT pg_typeof(list_prepend(1::int8, LIST[1::int2]))
----
bigint list

# 🔬🔬🔬 NULL elements

# 🔬🔬🔬🔬 element + list

query T
SELECT (list_prepend(NULL, LIST[1]))::text
----
{NULL,1}

query T
SELECT (list_prepend(NULL::INT, LIST[1]))::text
----
{NULL,1}

query T
SELECT (list_prepend(1, NULL::INT LIST))::text
----
{1}

query T
SELECT (list_prepend(NULL::INT, NULL::INT LIST))::text
----
{NULL}

# 🔬🔬🔬🔬 list + list list (element + list)

query T
SELECT (list_prepend(NULL, LIST[[1]]))::text
----
{NULL,{1}}

query T
SELECT (list_prepend(LIST[1], NULL))::text
----
{{1}}

query T
SELECT (list_prepend(NULL::INT LIST, NULL))::text
----
{NULL}

query T
SELECT (list_prepend(NULL, NULL::INT LIST LIST))::text
----
{NULL}

# 🔬🔬🔬 errors

query error
SELECT list_prepend(NULL, NULL)

query error
SELECT list_prepend('a', LIST[1])

query error
SELECT list_prepend(LIST[1], LIST[2])

query error
SELECT list_prepend(LIST[1], 1)

# 🔬🔬 unnest

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

query T rowsort
SELECT unnest::text FROM unnest(LIST[[1,2],[3]])
----
{1,2}
{3}

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

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

query T
SELECT unnest::text FROM unnest(NULL::int list)
----

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

# 🔬 List casts

# 🔬🔬 Between lists

# 🔬🔬🔬 Unlayered lists

query T
SELECT (LIST['1']::int list)::text
----
{1}

query T
SELECT (LIST[NULL]::int list)::text
----
{NULL}

query T
SELECT (LIST[NULL, '1']::int list)::text
----
{NULL,1}

query T
SELECT (LIST[1.4, 1.5]::int list)::text
----
{1,2}

query T
SELECT (LIST[1.4::float, -1.5::float]::int list)::text
----
{1,-2}

query T
SELECT (NULL::int list)::text
----
NULL

# 🔬🔬🔬🔬 Errors

query error invalid input syntax for type integer: invalid digit found in string: "dog"
SELECT (LIST['1', 'dog']::int list)::text

query error LIST could not convert type date to integer
SELECT LIST[DATE '2008-02-01']::int list

# 🔬🔬🔬 Layered and jagged lists

query T
SELECT (LIST[[1.4::float], [-1.5::float, 2.5::float]]::int list list)::text
----
{{1},{-2,2}}

query T
SELECT (LIST[[1.4::float], [-1.5::float, 2.5::float], NULL::float list]::int list list)::text
----
{{1},{-2,2},NULL}

# 🔬🔬🔬 Non-numeric types

query T
SELECT (LIST[TIMESTAMP '2008-02-01 15:04:05', TIMESTAMP '2007-02-01 15:04:05']::date list)::text
----
{2008-02-01,2007-02-01}

query T
SELECT (LIST[DATE '2008-02-01', DATE '2007-02-01']::timestamp list)::text
----
{"2008-02-01 00:00:00","2007-02-01 00:00:00"}

# 🔬🔬 list to text

query T
SELECT (LIST['1']::text)::text
----
{1}

query T
SELECT (LIST[NULL]::text)::text
----
{NULL}

query T
SELECT (LIST[NULL, '1']::text)::text
----
{NULL,1}

query T
SELECT (LIST[NULL, 1]::text)::text
----
{NULL,1}

query T
SELECT (LIST[NULL, '1']::text)::text
----
{NULL,1}

query T
SELECT (LIST[NULL, 1]::text)::text
----
{NULL,1}

query T
SELECT (LIST[1.4, -1.5]::text)::text
----
{1.4,-1.5}

query T
SELECT (LIST[[1.4], [-1.5]]::text)::text
----
{{1.4},{-1.5}}

query T
SELECT (LIST[[1.4::float], [-1.5::float, 2.5::float]]::text)::text
----
{{1.4},{-1.5,2.5}}

# Show that these are actually strings
query I
SELECT length(LIST['1','2']::text)
----
5

query error db error: ERROR: function length\(text list\) does not exist
SELECT length(LIST['1','2'])

# 🔬🔬 text to list

# 🔬🔬🔬 text to int list

query I
SELECT * FROM unnest('{1,2,3}'::int list)
----
1
2
3

query T rowsort
SELECT unnest::text FROM unnest('{{1,2},{3}}'::int list list)
----
{1,2}
{3}

query T rowsort
SELECT unnest::text FROM unnest('{{{1},{2}},{{3}}}'::int list list list)
----
{{1},{2}}
{{3}}

query T
SELECT ('{NULL}'::int list)::text
----
{NULL}

query T
SELECT ('{}'::int list)::text
----
{}

# 🔬🔬🔬 text to text list

query T
SELECT ('{"a"," b","c ", d,e }'::text list)::text
----
{a," b","c ",d,e}

query T
SELECT ('{{"a"," b","c "},{ d,e }}'::text list list)::text
----
{{a," b","c "},{d,e}}

# NULL-esque string handling
query TI rowsort
SELECT unnest, length(unnest) from  unnest('{NULL,nUlL,"NULL"}'::text list) ORDER BY length
----
NULL  4
NULL  NULL
NULL  NULL

# Test some escape values
query TI rowsort
SELECT unnest, length(unnest) from  unnest('{"a,b","\\","a\\b\"c\\d\""}'::text list) ORDER BY length
----
\  1
a,b  3
a\b"c\d"  8

query T
SELECT ('{NULL}'::text list)::text
----
{NULL}

query T
SELECT ('{}'::text list)::text
----
{}

# 🔬🔬🔬🔬 Quote escapes

# Preserves leading and trailing spaces within escape, but not outside of escape
query T
SELECT ('{ " a " }'::text list)::text
----
{" a "}

# Escapes content
query T
SELECT ('{"} \""}'::text list)::text
----
{"} \""}

query T
SELECT ('{"{a}"}'::text list)::text
----
{"{a}"}

query T
SELECT ('{"",""}'::text list)::text
----
{"",""}

# This demos that the double quotes from the input are actually stripped
query TI
SELECT unnest, length(unnest) FROM unnest('{"",""}'::text list)
----
(empty) 0
(empty) 0

# Mixture of escape and non-escaped elements
query T
SELECT ('{"{",a}'::text list)::text
----
{"{",a}

# Escape gets reset after each element
query T
SELECT ('{"{",\\}'::text list)::text
----
{"{","\\"}

# Elements within double quotes gets unescaped before being cast
query T
SELECT ('{{a, "", "\""}, "{a, \"\", \"\\\"\"}"}'::text list list)::text
----
{{a,"","\""},{a,"","\""}}

# Unquoted elements cannot have special characters interleaved within them
query error invalid input syntax for type list: malformed literal; must escape special character '"'
SELECT ('{a"b"}'::text list)::text

query error invalid input syntax for type list: malformed literal; must escape special character '\{'
SELECT ('{a{b}'::text list)::text

query error invalid input syntax for type list: malformed array literal; contains 'b' after terminal '\}'
SELECT ('{a}b}'::text list)::text

# No non-whitespace characters after the escape
query error invalid input syntax for type list: expected ',' or '\}', got 'b'
SELECT ('{"a"b}'::text list)::text

query error invalid input syntax for type list: expected ',' or '\}', got '"'
SELECT ('{""""}'::text list)::text

query error invalid input syntax for type list: expected ',' or '\}', got '"'
SELECT ('{"""}'::text list)::text

# 🔬🔬🔬🔬 Unquoted escapes

# Escapes work on special characters
query T
SELECT ('{\{, \}, \", \,}'::text list)::text
----
{"{","}","\"",","}

# Preserves escaped white space
query T
SELECT ('{\ a}'::text list)::text
----
{" a"}

query T
SELECT ('{a \ }'::text list)::text
----
{"a  "}

query T
SELECT ('{\ a \ }'::text list)::text
----
{" a  "}

query T
SELECT ('{a \ \ }'::text list)::text
----
{"a   "}

# list lists are only escaped once; if they were escaped at each level, they
# would no longer escape leading or trailing whitespace
query T
SELECT ('{{\ a}}'::text list list)::text
----
{{" a"}}

query T
SELECT ('{{a \ }}'::text list list)::text
----
{{"a  "}}

query T
SELECT ('{{\ a \ }}'::text list list)::text
----
{{" a  "}}

query T
SELECT ('{{a \ \ }}'::text list list)::text
----
{{"a   "}}

# Unescaped space is trimmed
query T
SELECT ('{\ \ a     ,     b \  }'::text list)::text
----
{"  a","b  "}

query T
SELECT ('{     a \ \ , \ b     }'::text list)::text
----
{"a   "," b"}

query T
SELECT ('{a \ \     ,   \  b   }'::text list)::text
----
{"a   ","  b"}

query T
SELECT ('{ {     a \ }     }'::text list list)::text
----
{{"a  "}}

query T
SELECT ('{     {a \     } }'::text list list)::text
----
{{"a  "}}

query T
SELECT ('{     \ a}'::text list)::text
----
{" a"}

# Leading/trailing escaped special characters are honored
query T
SELECT ('{\"a\"}'::text list)::text
----
{"\"a\""}

query T
SELECT ('{\{a\}}'::text list)::text
----
{"{a}"}

# Escaping backslashes works
query T
SELECT ('{ \\\\a\\\\ }'::text list)::text
----
{"\\\\a\\\\"}

# Escaping a non-special character has no visible effect on the output; normal
# whitespacing rules apply
query T
SELECT ('{ \a }'::text list)::text
----
{a}

# Escaping a character in NULL turns it into the string "NULL"
query T
SELECT ('{N\ULL}'::text list)::text
----
{"NULL"}

# Escaping terminal character means it isn't available to close the list
query error invalid input syntax for type list: unterminated element
SELECT ('{\}'::text list)::text

query error invalid input syntax for type list: unexpected end of input
SELECT ('{{\}}'::text list list)::text

# 🔬🔬🔬 text to other lists

query T
SELECT ('{1y 2d 3h, "4y 5d 6h"}'::interval list)::text
----
{"1 year 2 days 03:00:00","4 years 5 days 06:00:00"}

query T
SELECT ('{2001-02-03, "2004-05-06"}'::date list)::text
----
{2001-02-03,2004-05-06}

query T
SELECT ('{NULL}'::date list)::text
----
{NULL}

# 🔬🔬🔬 misc. checks

# Elements within double quotes have their double quotes before being cast to
# their element type
query T
SELECT ('{"1"}'::int list)::text
----
{1}

query T
SELECT ('{"1"}'::text list)::text
----
{1}

query T
SELECT ('{"{}"}'::int list list)::text
----
{{}}

query T
SELECT ('{"{}"}'::text list list)::text
----
{{}}

# Empty lists
query T
SELECT ('{  }'::text list)::text
----
{}

query T
SELECT ('{ {   } }'::text list list)::text
----
{{}}

# 🔬🔬🔬 errors

# Empty string is invalid
query error invalid input syntax for type list: expected '\{', found empty string: ""
SELECT (''::text list)::text

# Not a list
query error invalid input syntax for type list: expected '\{', found 1: "1"
SELECT ('1'::int list)::text

# Invalid element
query error invalid input syntax for type list: invalid input syntax for type integer: invalid digit found in string: "a": "\{a\}"
SELECT ('{a}'::int list)::text

# 'NULL' isn't a valid string for a list; just use unescaped NULL
query error invalid input syntax for type list: expected '\{', found N: "NULL"
SELECT ('NULL'::int list)::text

# Too many leading brackets
query error invalid input syntax for type list: unescaped '\{' at beginning of element
SELECT ('{{1}}'::int list)::text

# Too many leading brackets
query error invalid input syntax for type list: unescaped '\{' at beginning of element
SELECT ('{{1}'::int list)::text

query error invalid input syntax for type list: unexpected end of input: "\{\{1\}"
SELECT ('{{1}'::int list list)::text

# Too many following brackets
query error invalid input syntax for type list: malformed array literal; contains '\}' after terminal '\}': "\{1\}\}"
SELECT ('{1}}'::int list)::text

query error invalid input syntax for type list: invalid input syntax for type list: expected '\{', found 1: "1": "\{1\}\}"
SELECT ('{1}}'::int list list)::text

# Cannot have commas followed or preceded by empty elements
query error invalid input syntax for type list: malformed literal; missing element: "\{a,  \}"
SELECT ('{a,  }'::text list)::text

query error invalid input syntax for type list: malformed literal; missing element: "\{  ,a\}"
SELECT ('{  ,a}'::text list)::text

# 🔬🔬🔬🔬 homogeneous text lists
query error char list not yet supported
SELECT (LIST['ab'::char, 'cd'::varchar, 'ef'::text])::text

query T
SELECT (LIST['cd'::varchar, 'ef'::text])::text
----
{cd,ef}

# 🔬🔬 char lists
# ensures that the list type does not pick up an elements' typmod

query error char list not yet supported
SELECT LIST['abc'::char(1), 'abc'::char(2), 'abc'::char(3)]::text;

query error char list not yet supported
SELECT LIST['abc'::char(3), 'abc'::char(2), 'abc'::char(1)]::text;

query error char list not yet supported
SELECT pg_typeof(LIST['abc'::char(1), 'abc'::char(2), 'abc'::char(3)]);

# 🔬🔬 varchar lists
# ensures that the list type does not pick up an elements' typmod

query T
SELECT LIST['abc'::varchar(1), 'abc'::varchar(2), 'abc'::varchar(3)]::text;
----
{a,ab,abc}

query T
SELECT LIST['abc'::varchar(3), 'abc'::varchar(2), 'abc'::varchar(1)]::text;
----
{abc,ab,a}

query T
SELECT pg_typeof(LIST['abc'::varchar(1), 'abc'::varchar(2), 'abc'::varchar(3)]);
----
character varying list

# 🔬 Built-in operations

# 🔬🔬 concatenation (||)

# 🔬🔬🔬 list + list

query T
SELECT (LIST[1, 2] || LIST[3, 4])::text
----
{1,2,3,4}

query T
SELECT (LIST[[1], [2]] || LIST[[3], [4]])::text
----
{{1},{2},{3},{4}}

# Concatenation properly casts text to appropriate list type
query T
SELECT (LIST[1] || '{2}')::text
----
{1,2}

# Differently scaled numerics are implicitly castable to one another
query T
SELECT ('{1.2}'::numeric(38,5) list || '{2.3}'::numeric(38,0) list)::text;
----
{1.2,2}

# ...including on multiple layers
query T
SELECT ('{{1.2}}'::numeric(38,5) list list || '{{2.3}}'::numeric(38,0) list list)::text;
----
{{1.2},{2}}

# Determining common list element type for numerics does not rescale values
query T
SELECT LIST[1.234::numeric(39,2), 2.345]::text;
----
{1.23,2.345}

query T
SELECT LIST[1.234::numeric(39,2), 2.345]::numeric(39, 2) list::text;
----
{1.23,2.35}

# 🔬🔬🔬🔬 polymorphic

query T
SELECT pg_typeof(LIST[1::int2] || LIST[2::int8])
----
bigint list

query T
SELECT pg_typeof(LIST[1::int8] || LIST[2::int2])
----
bigint list

# 🔬🔬🔬 list + element

query T
SELECT (LIST[1, 2] || 3)::text
----
{1,2,3}

query T
SELECT ('{1.2}'::numeric(38,5) list || '2.3'::numeric(38,0))::text;
----
{1.2,2}

# 🔬🔬🔬🔬 polymorphic

query T
SELECT pg_typeof(LIST[1::int2] || 2::int8)
----
bigint list

query T
SELECT pg_typeof(LIST[1::int8] || 2::int2)
----
bigint list

# 🔬🔬🔬 element + list

query T
SELECT (1 || LIST[2, 3])::text
----
{1,2,3}

query T
SELECT ('1.2'::numeric(38,5) || '{2.3}'::numeric(38,0) list)::text;
----
{1.2,2}

# 🔬🔬🔬🔬 polymorphic

query T
SELECT pg_typeof(1::int2 || LIST[2::int8])
----
bigint list

query T
SELECT pg_typeof(1::int8 || LIST[2::int2])
----
bigint list

# 🔬🔬🔬 list list + list (list + element)

query T
SELECT (LIST[[1], [2]] || LIST[3])::text
----
{{1},{2},{3}}

query T
SELECT ('{{1.2}}'::numeric(38,5) list list || '{2.3}'::numeric(38,0) list)::text;
----
{{1.2},{2}}

# 🔬🔬🔬 list + list list (element + list)

query T
SELECT (LIST[1] || LIST[[2], [3]])::text
----
{{1},{2},{3}}

query T
SELECT ('{1.2}'::numeric(38,5) list || '{{2.3}}'::numeric(38,0) list list)::text;
----
{{1.2},{2}}

# 🔬🔬🔬 NULL elements

# 🔬🔬🔬🔬 list + list

# Our type system resolves `list || NULL` as a form of `list || list`
query T
SELECT (LIST[1] || NULL)::text
----
{1}

query T
SELECT (NULL || LIST[1])::text
----
{1}

query T
SELECT (LIST[1] || NULL::INT LIST)::text
----
{1}

query T
SELECT ( NULL::INT LIST || LIST[1])::text
----
{1}

query T
SELECT (NULL::INT LIST || NULL::INT LIST)::text
----
NULL

# 🔬🔬🔬🔬 list + element

query T
SELECT (LIST[1] || NULL::INT)::text
----
{1,NULL}

query T
SELECT (NULL::INT LIST || 1)::text
----
{1}

query T
SELECT (NULL::INT LIST || NULL::INT)::text
----
{NULL}

# 🔬🔬🔬🔬 element + list

query T
SELECT (NULL::INT || LIST[1])::text
----
{NULL,1}

query T
SELECT (1 || NULL::INT LIST)::text
----
{1}

query T
SELECT (NULL::INT || NULL::INT LIST)::text
----
{NULL}

# 🔬🔬🔬🔬 list list + list (list + element)

query T
SELECT (LIST[[1]] || NULL::INT LIST)::text
----
{{1},NULL}

query T
SELECT (NULL::INT LIST LIST || LIST[1])::text
----
{{1}}

query T
SELECT (NULL::INT LIST LIST || NULL::INT LIST)::text
----
{NULL}

# 🔬🔬🔬🔬 list + list list (element + list)

query T
SELECT (NULL::INT LIST || LIST[[1]])::text
----
{NULL,{1}}

query T
SELECT (LIST[1] || NULL::INT LIST LIST)::text
----
{{1}}

query T
SELECT (NULL::INT LIST || NULL::INT LIST LIST)::text
----
{NULL}

# 🔬🔬🔬 errors

query error no overload for int4 list || unknown: Cannot concatenate int4 list and string list
SELECT LIST[1] || LIST['a']

query error  no overload for int4 list || unknown: Cannot concatenate int4 list and string
SELECT LIST[1] || 'a'

query error no overload for unknown || int4 list: Cannot concatenate string list and int4 list
SELECT LIST[NULL] || LIST[1]

query error no overload for unknown || int4 list: Cannot concatenate string list list and int4 list
SELECT LIST[[NULL]] || LIST[1]

query error no overload for int4 list list list || int4 list: Cannot concatenate int4 list list list and int4 list
SELECT LIST[[[1]]] || LIST[2]

# Literal text cannot be implicitly cast to list
query error no overload for int4 list || string: arguments cannot be implicitly cast to any implementation's parameters; try providing explicit casts
SELECT LIST[1] || '{2}'::text

# Two lists containing implicitly castable element types are not implicitly castable to one another
query error no overload for f32 list || f64 list: arguments cannot be implicitly cast to any implementation's parameters; try providing explicit casts
SELECT '{1}'::float4 list || '{2}'::float8 list

# 🔬🔬 equality (=)

# 🔬🔬🔬 list = list

query T
SELECT LIST[1, 2] = LIST[1, 2]
----
true

query T
SELECT LIST[1, 2] = LIST[1, 3]
----
false

query T
SELECT LIST[[1], [2]] = LIST[[1], [2]]
----
true

query T
SELECT LIST[[1], [2]] = LIST[[1], [3]]
----
false

# Equality properly casts text to appropriate list type
query T
SELECT LIST[1] = '{1}'
----
true

query T
SELECT LIST[1] = '{2}'
----
false

# 🔬🔬🔬 errors

query error db error: ERROR: operator does not exist: integer list = text list
SELECT LIST[1] = LIST['a']

query error db error: ERROR: operator does not exist: text list = integer list
SELECT LIST[NULL] = LIST[1]

query error db error: ERROR: operator does not exist: text list list = integer list
SELECT LIST[[NULL]] = LIST[1]

query error db error: ERROR: operator does not exist: integer list list list = integer list
SELECT LIST[[[1]]] = LIST[2]

# Literal text cannot be implicitly cast to list
query error db error: ERROR: operator does not exist: integer list = text
SELECT LIST[1] = '{2}'::text

# Two lists containing implicitly castable element types are not implicitly castable to one another
query error db error: ERROR: operator does not exist: real list = double precision list
SELECT '{1}'::float4 list = '{2}'::float8 list

# 🔬 CREATE TYPE .. AS LIST

query error type "pg_enum" does not exist
CREATE TYPE tbl_list AS LIST (ELEMENT TYPE=pg_enum)

query error CREATE TYPE ... AS LIST option ELEMENT 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_list AS LIST (ELEMENT TYPE=int4 list)

statement ok
CREATE TYPE int4_list_c AS LIST (ELEMENT TYPE = int4);

query T
SELECT '{1,2}'::int4_list_c::text;
----
{1,2}

query T
SELECT oid >= 20000 FROM pg_type WHERE typname = 'int4_list_c';
----
true

query T
SELECT '{{1,2}}'::int4_list_c list::text
----
{{1,2}}

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

statement ok
CREATE TYPE int4_list_list_c AS LIST (ELEMENT TYPE = int4_list_c);

query T
SELECT '{{1,2}}'::int4_list_list_c::text;
----
{{1,2}}

query error type "bool list" does not exist
CREATE TYPE nested_list AS LIST (ELEMENT TYPE = "bool list")

query error db error: ERROR: cannot reference pseudo type mz_catalog\.list
CREATE TYPE nested_list AS LIST (ELEMENT TYPE = list)

# 🔬🔬 Check each valid non-array element type

statement ok
CREATE TYPE bool_list_c AS LIST (ELEMENT TYPE=bool);

query T
SELECT '{true}'::bool_list_c::text
----
{t}

statement ok
CREATE TYPE int8_list_c AS LIST (ELEMENT TYPE=int8);

query T
SELECT '{1,2}'::int8_list_c::text
----
{1,2}

query T
SELECT '{1,2}'::int4_list_c::text
----
{1,2}

statement ok
CREATE TYPE text_list_c AS LIST (ELEMENT TYPE=text);

query T
SELECT '{a,b}'::text_list_c::text
----
{a,b}

statement ok
CREATE TYPE float4_list_c AS LIST (ELEMENT TYPE=float4);

query T
SELECT '{1.2,2.3}'::float4_list_c::text
----
{1.2,2.3}

statement ok
CREATE TYPE float8_list_c AS LIST (ELEMENT TYPE=float8);

query T
SELECT '{1.2,2.3}'::float8_list_c::text
----
{1.2,2.3}

statement ok
CREATE TYPE date_list_c AS LIST (ELEMENT TYPE=date);

query T
SELECT '{2001-01-01}'::date_list_c::text
----
{2001-01-01}

statement ok
CREATE TYPE time_list_c AS LIST (ELEMENT TYPE=time);

query T
SELECT '{12:34:56}'::time_list_c::text
----
{12:34:56}

statement ok
CREATE TYPE timestamp_list_c AS LIST (ELEMENT TYPE=timestamp);

query T
SELECT '{2001-01-01 12:34:56}'::timestamp_list_c::text
----
{"2001-01-01 12:34:56"}

statement ok
CREATE TYPE timestamptz_list_c AS LIST (ELEMENT TYPE=timestamptz);

query T
SELECT '{2001-01-01 12:34:56}'::timestamptz_list_c::text
----
{"2001-01-01 12:34:56+00"}

statement ok
CREATE TYPE interval_list_c AS LIST (ELEMENT TYPE=interval);

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

statement ok
CREATE TYPE numeric_list_c AS LIST (ELEMENT TYPE=numeric);

query T
SELECT '{1.23,2.34}'::numeric_list_c::text
----
{1.23,2.34}

statement ok
CREATE TYPE jsonb_list_c AS LIST (ELEMENT TYPE=jsonb);

query T
SELECT '{\{\"1\":2\}}'::jsonb_list_c::text;
----
{"{\"1\":2}"}

# 🔬🔬 Check custom type name resolution

statement ok
CREATE TYPE bool AS LIST (ELEMENT TYPE=int4)

query error invalid input syntax for type boolean: "\{1,2\}"
SELECT '{1,2}'::bool;

query T
SELECT '{1,2}'::public.bool::text;
----
{1,2}

# 🔬🔬 Check subtype resolution

# Supports qualified subtypes
statement ok
CREATE TYPE qualified_int4_list AS LIST (ELEMENT TYPE=pg_catalog.int4)

statement ok
CREATE TYPE qualified_qualified_int4_list AS LIST (ELEMENT TYPE=public.qualified_int4_list)

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

# 🔬🔬 Built-in operations

query T
SELECT ('{1}'::int4_list_c || 2)::text;
----
{1,2}

query T
SELECT (1 || '{2}'::int4_list_c)::text;
----
{1,2}

# 🔬 Explicit casts w/ custom types

query T
SELECT ('{1.2,2.3}'::numeric_list_c)::text;
----
{1.2,2.3}

query T
SELECT ('{1.2,2.34567890}'::numeric_list_c::numeric(38,5) list)::text;
----
{1.2,2.34568}

query T
SELECT ('{1.2,2.3}'::numeric(38,5) list::numeric_list_c)::text;
----
{1.2,2.3}

# 🔬 Implicit casts between custom types

# 🔬🔬 1-D casts

statement ok
CREATE TYPE int4_list AS LIST (ELEMENT TYPE = int4)

statement ok
CREATE TYPE int4_list_too AS LIST (ELEMENT TYPE = int4)

query T
SELECT ('{1}'::int4_list || '{2}'::int list)::text;
----
{1,2}

query error
SELECT '{1}'::int4_list || '{2}'::int4_list_too;

# Anonymous type cast to custom type, which is not interoperable with a
# different custom type
query error
SELECT '{1}'::int4_list || '{2}'::int list || '{3}'::int4_list_too

query T
SELECT ('{1}'::int4_list_too || '{2}'::int4_list::int4_list_too)::text;
----
{1,2}

query T
SELECT ('{1}'::int4_list_too || '{2}'::int4_list::int list)::text;
----
{1,2}

query T
SELECT ('{1}'::int4_list || 2)::text;
----
{1,2}

# 🔬🔬 2-D casts

statement ok
CREATE TYPE int4_list_list AS LIST (ELEMENT TYPE = int4_list)

statement ok
CREATE TYPE int4_list_list_too AS LIST (ELEMENT TYPE = int4_list_too)

# Custom type interoperable with anonymous type
query T
SELECT ('{{1}}'::int4_list_list || '{{2}}'::int list list)::text;
----
{{1},{2}}

# Other custom types cast to same custom type
query T
SELECT ('{{1}}'::int4_list_list_too || '{{2}}'::int4_list_list::int4_list_list_too)::text;
----
{{1},{2}}

# Other custom type cast to anonymous type
query T
SELECT ('{{1}}'::int4_list_list_too || '{{2}}'::int4_list_list::int list list)::text;
----
{{1},{2}}

# Different custom types
query error
SELECT '{{1}}'::int4_list_list || '{{2}}'::int4_list_list_too;

# Different custom types as element types
query error
SELECT '{{1}}'::int4_list list || '{{2}}'::int4_list_list_too list;

# Custom element type
query T
SELECT ('{{1}}'::int4_list_list || '{2}'::int4_list)::text;
----
{{1},{2}}

# Anonymous element type
query T
SELECT ('{{1}}'::int4_list_list || '{2}'::int4 list)::text;
----
{{1},{2}}

# Non-matching element type
query error
SELECT '{{1}}'::int4_list_list || '{2}'::int4_list_too;

query error
SELECT '{1}'::int4_list_too || '{{2}}'::int4_list_list

# Element types match, but "head" type does not
query error
SELECT '{{1}}'::int4_list_list || '{{2}}'::int4_list list

query error
SELECT '{{1}}'::int4_list list || '{{2}}'::int4_list_list

# Custom element type w/ anonymous complex type
query T
SELECT ('{{1}}'::int4_list list || '{{2}}'::int4_list list)::text
----
{{1},{2}}

# Custom element exactly matches
query T
SELECT ('{{1}}'::int4_list list || '{2}'::int4_list)::text
----
{{1},{2}}

# Custom element + anonymous element
query T
SELECT ('{{1}}'::int4_list list || '{2}'::int4 list)::text
----
{{1},{2}}

# list_agg

query T
SELECT list_agg(a)::text FROM (SELECT 1 AS a WHERE false)
----
NULL

query T
SELECT list_agg(1)::text
----
{1}

query T
select list_agg(unnest)::text 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 list_agg(a)::text FROM (select a from t1 where a IS NOT NULL)
----
{1,2,3}

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

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

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

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

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

query error db error: ERROR: function list_agg\(integer, integer\) does not exist
SELECT list_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 list_agg((a, b))::text FROM t2
----
{"(1,2020-01-01)","(,2020-01-02)"}

query TTT
SELECT list_agg((a, b))::text, list_agg(a)::text, list_agg(b)::text FROM t2
----
{"(1,2020-01-01)","(,2020-01-02)"}  {1,NULL}  {2020-01-01,2020-01-02}

query T
SELECT list_agg(ARRAY[1])::text
----
{{1}}

query error list_agg on char
SELECT list_agg('a'::char)::text

query error list_agg on char
SELECT list_agg('a'::char(2))::text

query T
SELECT list_agg(a)::text FROM (VALUES ('{1,2}'::int list), ('{3}'), (NULL)) v(a);
----
{{1,2},{3},NULL}

query T
SELECT list_agg(a)::text FROM (VALUES (array[1,2]), (array[3]), (NULL)) v(a);
----
{{1,2},{3},NULL}

statement ok
INSERT INTO t2 VALUES (3, date '2020-01-03')

query T
SELECT list_agg(a ORDER BY b)::text FROM t2
----
{1,NULL,3}

query T
SELECT list_agg(a ORDER BY a DESC)::text FROM t2
----
{NULL,3,1}

query T
SELECT list_agg(a ORDER BY a)::text FROM t2
----
{1,3,NULL}

query T
SELECT (list_agg(a ORDER BY a) FILTER (WHERE b IS NOT NULL))::text FROM t2
----
{1,3,NULL}

query T
SELECT (list_agg(a ORDER BY a) FILTER (WHERE b > '2050-01-01'))::text FROM t2
----
{}

query T
SELECT list_agg(a ORDER BY b)::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{3,2,1}

query T
SELECT list_agg(a ORDER BY abs(b))::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{1,2,3}

query T
SELECT list_agg(a ORDER BY a > 10, -a)::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{3,2,1}

query T
SELECT list_agg(a ORDER BY (SELECT abs(-b)))::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{1,2,3}

query T
SELECT list_agg(a ORDER BY a+1 DESC)::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{3,2,1}

query T
SELECT list_agg(a ORDER BY -a DESC)::text FROM (VALUES (1, -1), (2, -2), (3, -3)) _ (a, b)
----
{1,2,3}

query error column "no_such_column" does not exist
SELECT array_agg(a ORDER BY no_such_column)::text FROM (VALUES (1, -1)) AS a(a);

query error division by zero
SELECT array_agg(a ORDER BY a/0)::text FROM (VALUES (1, -1)) AS a(a);

query error more than one record produced in subquery
SELECT list_agg(a ORDER BY (SELECT 'a' FROM t2))::text FROM t2;

query error Expected subselect to return 1 column, got 2 columns
SELECT list_agg(a ORDER BY (SELECT * FROM t2)) FROM t2

query T
SELECT array_agg(column1 ORDER BY 1234)::text FROM (VALUES (1)) _;
----
{1}

# Test that a function that does not support ORDER BY is correct.
query T
SELECT sum(a ORDER BY b) FROM t2
----
4

# list_remove

query T
SELECT list_remove(LIST[1,2,3,2], 2)::text
----
{1,3}

query T
SELECT list_remove(LIST[1,2,3,2], 5)::text
----
{1,2,3,2}

query T
SELECT list_remove(LIST[1,2,3,NULL::INT], NULL::INT)::text
----
{1,2,3}

query T
SELECT list_remove(LIST[1,NULL::INT,2,3,NULL::INT], NULL::INT)::text
----
{1,2,3}

query T
SELECT list_remove(NULL::integer list, NULL::INT)::text
----
NULL

query T
SELECT list_remove(NULL::integer list, 1)::text
----
NULL

query T
SELECT list_remove(LIST[1,1,1], 1)::text
----
{}

query T
SELECT list_remove(LIST[[1,2],[1],[1,2,3], LIST[1]], LIST[1])::text
----
{{1,2},{1,2,3}}

query T
SELECT list_remove(LIST[[1,2],[1],[1,2,3],LIST[1]], LIST[1,2,3])::text
----
{{1,2},{1},{1}}

## polymorphism

query T
SELECT pg_typeof(list_remove(LIST[1::int8,2,3,2], 2::int2))
----
bigint list

query T
SELECT pg_typeof(list_remove(LIST[1::int2,2,3,2], 2::int8))
----
bigint list

# array to list
query T
SELECT '{1,2,3}'::int[]::int list::text;
----
{1,2,3}

query T
SELECT pg_typeof('{1,2,3}'::int[]::int list);
----
integer list

query T
SELECT '{}'::int[]::int list::text;
----
{}

query T
SELECT pg_typeof('{1,2,3}'::int[]::int list);
----
integer list

query T
SELECT NULL::int[]::int list::text;
----
NULL

query T
SELECT pg_typeof(NULL::int[]::int list);
----
integer list

query error casting multi-dimensional array to list; got array with 2 dimensions not yet supported
SELECT '{{1},{2},{3}}'::int[]::int list::text;

# Verify nested lists
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 LIST could not convert type integer list to text list
SELECT LIST[LIST[customer.first_name, customer.last_name], LIST[customer.zip], LIST[customer.id]]::text FROM customer JOIN users ON customer.id = users.id ORDER BY users.other_field DESC LIMIT 2

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

## Optimization: Reducing ListIndex(ListCreate, literal) and multidimensional variations
# Most of these tests could also be in src/expr/tests/testdata/reduce, but writing these big ListCreate expressions is a
# bit cumbersome in that format.

statement ok
CREATE TABLE t3(f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int, n int, m int, l int list)

statement ok
CREATE VIEW m3 AS SELECT * FROM t3

statement ok
CREATE DEFAULT INDEX ON m3

statement ok
INSERT INTO t3 VALUES (1, 2, 3, 4, 5, 6, 7, 8, 1, 2, list[42, 43])

statement ok
INSERT INTO t3 VALUES (11, 12, 13, 14, 15, 16, 17, 18, 11, 12, list[82, 83])

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[f1, f2, f3, f4, f5][3] from m3
----
Explained Query (fast path):
  Project (#2{f3})
    ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[f1, f2, f3, f4, f5][3] from m3
----
3
13

# Reducing multidimensional ListIndex when all indexes are literals

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[[f1, f2], [f3, f4]][2][1] from m3
----
Explained Query (fast path):
  Project (#2{f3})
    ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[f1, f2], [f3, f4]][2][1] from m3
----
3
13

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][1][2] from m3
----
Explained Query (fast path):
  Project (#5{f6})
    ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][1][2] from m3
----
6
16

# Reducing multidimensional ListIndex when some of the indexes are not literals, and therefore can't be removed.
# We use `types, no fast path` to be able to check that the type is not changing.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f7, f8, f3, f4]], [[f5, f6], [f7, f8]]] [n][m][n] from m3
----
Explained Query:
  Project (#12) // { arity: 1, types: "(integer?)" }
    Map (integer_to_bigint(#8{n}), list_index(list[list[list[#0{f1}, #1{f2}], list[#6{f7}, #7{f8}, #2{f3}, #3{f4}]], list[list[#4{f5}, #5{f6}], list[#6{f7}, #7{f8}]]], #11, integer_to_bigint(#9{m}), #11)) // { arity: 13, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, bigint?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f7, f8, f3, f4]], [[f5, f6], [f7, f8]]] [n][m][n] from m3
----
7
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][m][1] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[list[#0{f1}, #2{f3}], list[#4{f5}, #6{f7}]], integer_to_bigint(#8{n}), integer_to_bigint(#9{m}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][m][1] from m3
----
3
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][2][m] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[list[#2{f3}, #3{f4}], list[#6{f7}, #7{f8}]], integer_to_bigint(#8{n}), integer_to_bigint(#9{m}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][2][m] from m3
----
4
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [1][n][m] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[list[#0{f1}, #1{f2}], list[#2{f3}, #3{f4}]], integer_to_bigint(#8{n}), integer_to_bigint(#9{m}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [1][n][m] from m3
----
2
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][1][n] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[#4{f5}, #5{f6}], integer_to_bigint(#8{n}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][1][n] from m3
----
5
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][n][2] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[#5{f6}, #7{f8}], integer_to_bigint(#8{n}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [2][n][2] from m3
----
6
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations, types, no fast path) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][2][2] from m3
----
Explained Query:
  Project (#11) // { arity: 1, types: "(integer?)" }
    Map (list_index(list[#3{f4}, #7{f8}], integer_to_bigint(#8{n}))) // { arity: 12, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?, integer?)" }
      ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11, types: "(integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer?, integer list?)" }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [[f5, f6], [f7, f8]]] [n][2][2] from m3
----
4
NULL

# Reducing ListIndex when a literal index is out of bounds

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[f1, f2, f3, f4, f5][6] from m3
----
Explained Query (fast path):
  Project (#11)
    Map (null)
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[f1, f2, f3, f4, f5][0] from m3
----
Explained Query (fast path):
  Project (#11)
    Map (null)
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[f1, f2, f3, f4, f5][-1] from m3
----
Explained Query (fast path):
  Project (#11)
    Map (null)
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query T
SELECT LIST[f1, f2, f3, f4, f5][6] from m3
----
NULL
NULL

# Reducing multidimensional ListIndex when a literal index is out of bounds.
# Also, one of the indexes is `1+1`, which must be evaluated to make it a literal, and then the reduction can take place.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [l, [f7, f8]]] [1+1][-1][2] from m3;
----
Explained Query (fast path):
  Project (#11)
    Map (null)
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Reducing multidimensional ListIndex when the list doesn't have enough complete layers,
# but has a NULL instead of a ListCreate.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [NULL, [f7, f8]]] [1+1][1][2] from m3
----
Explained Query (fast path):
  Project (#11)
    Map (null)
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Reducing multidimensional ListIndex when the list doesn't have enough complete layers,
# but has a column reference instead of a ListCreate.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT LIST[[[f1, f2], [f3, f4]], [l, [f7, f8]]] [1+1][1][2] from m3
----
Explained Query (fast path):
  Project (#11)
    Map (list_index(#10{l}, 2))
      ReadIndex on=materialize.public.m3 m3_primary_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

query I rowsort
SELECT LIST[[[f1, f2], [f3, f4]], [l, [f7, f8]]] [1+1][1][2] from m3
----
43
83

# Reducing ListIndex(ListCreate, literal) when this pattern appears after some other optimization (reduce_elision)
# already took place.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT row_number() over () from (select f1 from m3 limit 1)
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (record_get[0](#1)) // { arity: 3 }
      FlatMap unnest_list(#0) // { arity: 2 }
        Project (#1) // { arity: 1 }
          Map (list[row(1, row(#0{f1}))]) // { arity: 2 }
            TopK limit=1 // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                ReadIndex on=m3 m3_primary_idx=[*** full scan ***] // { arity: 11 }

Used Indexes:
  - materialize.public.m3_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF


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

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

# array containment in Postgres does NOT account for duplicates
query T
SELECT LIST[1,4,3] @> LIST[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 LIST[1,4,3] @> LIST[]::INT LIST AS contains
----
true

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

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


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

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

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

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

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

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

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

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

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

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 list)[random_index]
)