# name: test/sql/cast/string_to_list_cast.test
# description: cast strings into (nested) list structures
# group: [cast]

statement ok
PRAGMA enable_verification

#               Basic tests:
# ---------------------------------------------------

query I
SELECT '[12,13,14]'::INT[];
----
[12, 13, 14]

query I
SELECT '["hello", "world", "!"]'::VARCHAR[];
----
[hello, world, !]

query I
SELECT CAST('[Hello World!]' AS VARCHAR[]);
----
[Hello World!]

query I
SELECT CAST('[[Hello World!], hello, universe]' AS VARCHAR[]);
----
['[Hello World!]', hello, universe]

query I
SELECT '[Cast like this]':: VARCHAR[];
----
[Cast like this]

# Issue 8797
query I
select UNNEST('[NULL, , NULL]'::varchar[]);
----
NULL
(empty)
NULL

query I
select UNNEST('[NULL,, NULL]'::varchar[]);
----
NULL
(empty)
NULL

query I
select UNNEST('[NULL,   , NULL]'::varchar[]);
----
NULL
(empty)
NULL

query I
SELECT UNNEST('[NULL, NULL , ]'::varchar[]);
----
NULL
NULL
(empty)

query I
SELECT UNNEST('[NULL, NULL ,]'::varchar[]);
----
NULL
NULL
(empty)

query I
SELECT UNNEST('[NULL, NULL,]'::varchar[]);
----
NULL
NULL
(empty)

#               Nested lists
# ---------------------------------------------------
query III
SELECT CAST('[ [12,13,14], [8, 9], [4], [2, 1, 0] ]' AS INT[][]) a, a::VARCHAR::INT[][] b, a == b;
----
[[12, 13, 14], [8, 9], [4], [2, 1, 0]]	[[12, 13, 14], [8, 9], [4], [2, 1, 0]]	true

query I
SELECT CAST('[ [[12,13,14], [8, 9]], [[4]], [[2, 1, 0], [99]] ]' AS INT[][][]);
----
[[[12, 13, 14], [8, 9]], [[4]], [[2, 1, 0], [99]]]

query I
SELECT CAST('[ [12,13,14], [8, 9], [4], [2,  1,  0] ]' AS VARCHAR[]);
----
['[12,13,14]', '[8, 9]', '[4]', '[2,  1,  0]']

query I
SELECT CAST('[[ [🦆, 🦆, 🦆]], [[duck, db, 🦆], [🦆]], [[🦆, duck, db]]]' AS VARCHAR[][][]);
----
[[[🦆, 🦆, 🦆]], [[duck, db, 🦆], [🦆]], [[🦆, duck, db]]]


#               Cast from table
# ---------------------------------------------------
statement  ok
CREATE TABLE stringList (col1 VARCHAR)

statement ok
INSERT INTO stringList VALUES ('["hello","world","!"]'), ('["Amazing","text"]'), ('[Hello World!]');

query I
SELECT col1::VARCHAR[] FROM stringList;
----
[hello, world, !]
[Amazing, text]
[Hello World!]

# ---------------------------------------------------
statement  ok
CREATE TABLE nestedStrings (col1 VARCHAR)

statement ok
INSERT INTO nestedStrings VALUES ('[["hello"], ["world"],["!"]]'), ('[["Amazing"],["text"]]'), ('[[Hello World!]]');

query I
SELECT col1::VARCHAR[][] FROM nestedStrings;
----
[[hello], [world], [!]]
[[Amazing], [text]]
[[Hello World!]]

# ---------------------------------------------------
statement  ok
CREATE TABLE superNestedStrings (col1 VARCHAR)

statement ok
INSERT INTO superNestedStrings VALUES ('[[[[["hello"]]], [[["world"],["!"]]]]]'), ('[[[[["Amazing"]],[["text"]]]]]'), ('[[[[[Hello World!]]]]]');

query I
SELECT col1::VARCHAR[][][][][] FROM superNestedStrings;
----
[[[[[hello]]], [[[world], [!]]]]]
[[[[[Amazing]], [[text]]]]]
[[[[[Hello World!]]]]]

# ---------------------------------------------------
statement ok
CREATE TABLE tbl (col1 VARCHAR);

statement ok
INSERT INTO tbl VALUES ('[1,2,2]'), ('[345,67865,44,4]'), ('[5,6,7]');

query I
SELECT col1::INT[] FROM tbl;
----
[1, 2, 2]
[345, 67865, 44, 4]
[5, 6, 7]


# ---------------------------------------------------
statement ok
CREATE TABLE doubleNested (col1 VARCHAR);

statement ok
INSERT INTO doubleNested VALUES ('[[1,2],[2]]'), ('[[345],[67865,44,4]]'), ('[[5],[6,7]]');

query I
SELECT col1::INT[][] FROM doubleNested;
----
[[1, 2], [2]]
[[345], [67865, 44, 4]]
[[5], [6, 7]]


# ---------------------------------------------------
statement  ok
CREATE TABLE tripleNested (col1 VARCHAR)

statement ok
INSERT INTO tripleNested VALUES ('[[[1,2],[3]]]'), ('[[[4]]]');

query I
SELECT col1::INT[][][] FROM tripleNested;
----
[[[1, 2], [3]]]
[[[4]]]

# ---------------------------------------------------
statement  ok
CREATE TABLE crazyNested (col1 VARCHAR)

statement ok
INSERT INTO crazyNested VALUES ('[[[[[[1]],[[3,4,9]]],[[[0,1]]]]]]'), ('[[[[[[4]]]]]]');

query I
SELECT col1::INT[][][][][][] FROM crazyNested;
----
[[[[[[1]], [[3, 4, 9]]], [[[0, 1]]]]]]
[[[[[[4]]]]]]

#               Quote handling
# ---------------------------------------------------
query I
SELECT CAST($$['hello','world', '!']$$ AS VARCHAR[]);
----
[hello, world, !]

query I
SELECT CAST($$[\'hello\',\'world\', \'!\']$$ AS VARCHAR[]);
----
['\'hello\'', '\'world\'', '\'!\'']

query I
SELECT CAST($$[[ ['🦆, 🦆, 🦆']], [[duck, db, '🦆'] ]]$$ AS VARCHAR[][][]);
----
[[['🦆, 🦆, 🦆']], [[duck, db, 🦆]]]

query I
SELECT CAST($$[can\'t, you\'re, i\'m]$$ AS VARCHAR[]);
----
['can\'t', 'you\'re', 'i\'m']

query I
SELECT CAST($$["]", "hello", "world"]$$ AS VARCHAR[]);
----
[']', hello, world]

query I
SELECT CAST($$[']', "hello", "world"]$$ AS VARCHAR[]);
----
[']', hello, world]


#               Test for whitespaces
# ---------------------------------------------------
query I
SELECT CAST('[ [12,     13,14], [8, 9         ], [ 4    ], [    2, 1,     0] ]  ' AS INT[][]);
----
[[12, 13, 14], [8, 9], [4], [2, 1, 0]]

query I
SELECT CAST('[          [ [12,     13,14], [8, 9         ]  ],[[ 4    ]   ],        [[    2, 1,     0 ]         ] ]  ' AS INT[][][]);
----
[[[12, 13, 14], [8, 9]], [[4]], [[2, 1, 0]]]

query I
SELECT CAST($$["   hello","          \"'  world", "!         "]$$ AS VARCHAR[]);
----
['   hello', '          "\'  world', '!         ']

query I
SELECT CAST('[   hello     ,   world      , !         ]' AS VARCHAR[]);
----
[hello, world, !]

query I
SELECT CAST($$[    [ "   hello"]  ,["            world"        ],[ "!        "           ]      ]$$ AS VARCHAR[][]);
----
[['   hello'], ['            world'], ['!        ']]


#               Empty list
# ---------------------------------------------------

query I
SELECT '[]'::VARCHAR[];
----
[]

query I
SELECT '[]'::INT[];
----
[]

query I
SELECT '[]'::INT[][][][];
----
[]

query I
SELECT '[[1, 2, 3], [], [	], [  ]]'::INT[][];
----
[[1, 2, 3], [], [], []]

query I
SELECT '[[1, 2, 3], [], NULL, [NULL], [4, NULL]]'::INT[][];
----
[[1, 2, 3], [], NULL, [NULL], [4, NULL]]


#               NULL values
# ------------------------------67---------------------
statement ok
CREATE TABLE null_tbl(col1 VARCHAR);

statement ok
INSERT INTO null_tbl VALUES(NULL), (NULL), ('[NULL]');

query I
SELECT col1::INT[] FROM null_tbl;
----
NULL
NULL
[NULL]

query I
SELECT CAST(NULL AS INT[]);
----
NULL

query I
SELECT CAST('[NULL]' AS INT[]);
----
[NULL]

query I
SELECT CAST('[NULL]' AS INT[][]);
----
[NULL]

query I
SELECT CAST('[[12,13,14], [8, 9], NULL, [2, 1]]' AS INT[][]);
----
[[12, 13, 14], [8, 9], NULL, [2, 1]]

query I
SELECT CAST('[[12,13,14], [8, 9], [2, NULL, 1]]' AS INT[][]);
----
[[12, 13, 14], [8, 9], [2, NULL, 1]]

query I
SELECT CAST('[ [[12,13,14], NULL], [[4]], NULL, [[2, NULL, 1, 0], [99]] ]' AS INT[][][]);
----
[[[12, 13, 14], NULL], [[4]], NULL, [[2, NULL, 1, 0], [99]]]


#               Try Cast
# ---------------------------------------------------
query I
SELECT TRY_CAST('Hello World' AS INT[]);
----
NULL

statement ok
CREATE TABLE try_cast_tbl (col1 VARCHAR);

statement ok
INSERT INTO try_cast_tbl VALUES ('[1,2,X,2]'), ('[hello DuckDB]'), ('[345,oops,44,4.0]'), ('[12345678901]'), ('[5,6,7]'), ('[3 0, 1]');

query I
SELECT TRY_CAST(col1 AS INT[]) FROM try_cast_tbl;
----
[1, 2, NULL, 2]
[NULL]
[345, NULL, 44, 4]
[NULL]
[5, 6, 7]
[NULL, 1]

#               Syntax error checking
# ---------------------------------------------------

statement error
SELECT CAST('{[3]}' AS INT[]);
----

statement error
SELECT CAST('Hello World' AS INT[]);
----

statement error
SELECT CAST('[3]]' AS INT[]);
----

statement error
SELECT CAST('[3],[[]' AS INT[][]);
----

statement error
SELECT CAST('[3], [[1]]' AS INT[][]);
----

statement error
SELECT CAST('[[3 1]]' AS INT[][]);
----

statement error
SELECT CAST('[[3,, 1]]' AS INT[][]);
----

statement error
SELECT CAST('[[3], [[5], [4]]' AS INT[][]);
----

statement error
SELECT CAST('][3]' AS INT[]);
----

statement error
SELECT CAST('[[[[[]][3][[]][][[[][]]]]]' AS INT[][][][]);
----


#               Test WHERE clause
# ---------------------------------------------------
query I
SELECT * FROM tbl WHERE cast(col1 as int[]) = [1, 2, 2];
----
[1,2,2]

query I
SELECT col1 FROM tbl WHERE LEN(cast(col1 as int[])) < 4;
----
[1,2,2]
[5,6,7]

query I
SELECT cast(col1 as int[]) FROM tbl WHERE LEN(cast(col1 as int[])) < 4;
----
[1, 2, 2]
[5, 6, 7]


#               Lists of structs:
# ---------------------------------------------------

statement ok
CREATE TABLE struct_tbl1(col VARCHAR);

statement ok
INSERT INTO struct_tbl1 VALUES('[{a: "hii"}, {a: "hellooo"}]');

query I
SELECT col::STRUCT(a VARCHAR)[] FROM struct_tbl1;
----
[{'a': hii}, {'a': hellooo}]

query I
SELECT CAST('[ [{a:[12,13,14], b:"🦆"}], [{a:[12,13,14], b:"🦆", c:100}] ]' AS STRUCT(a INT[], b VARCHAR, c FLOAT)[][]);
----
[[{'a': [12, 13, 14], 'b': 🦆, 'c': NULL}], [{'a': [12, 13, 14], 'b': 🦆, 'c': 100.0}]]

query I
SELECT CAST('[{a:[12,13,14], b:"🦆", c:{a:[[a], [b, c]], b:[123]}}]' AS STRUCT(a INT[], b VARCHAR, c STRUCT(a VARCHAR[][], b INT[]))[]);
----
[{'a': [12, 13, 14], 'b': 🦆, 'c': {'a': [[a], [b, c]], 'b': [123]}}]

query I
SELECT '[{a: hii}, {a: "{" }]'::STRUCT(a VARCHAR)[] FROM struct_tbl1;
----
[{'a': hii}, {'a': '{'}]


# ---------------------------------------------------
statement ok
CREATE TABLE struct_tbl2(col VARCHAR);

statement ok
INSERT INTO struct_tbl2 VALUES('[{a: 7, b:"Duck"}, {a: 7000, b: "🦆🦆🦆🦆🦆🦆"}]');

query I
SELECT col::STRUCT(a INT, b VARCHAR)[] FROM struct_tbl2;
----
[{'a': 7, 'b': Duck}, {'a': 7000, 'b': 🦆🦆🦆🦆🦆🦆}]

#               CSV reader
# ---------------------------------------------------
statement ok
CREATE TABLE int_list(col INT[]);

statement ok
COPY (SELECT [1,2,3]) TO '__TEST_DIR__/int_list.csv';

statement ok
COPY int_list FROM '__TEST_DIR__/int_list.csv';

query I
SELECT col FROM int_list;
----
[1, 2, 3]

statement ok
CREATE TABLE assorted_lists(col1 INT[], col2 VARCHAR[], col3 DATE[]);

statement ok
COPY (SELECT [8,7,6], $$[hello, Duck\\'DB]$$, '[2022-12-2, 1929-01-25]') TO '__TEST_DIR__/assorted_lists.csv' (Header 0);

statement ok
COPY assorted_lists FROM '__TEST_DIR__/assorted_lists.csv' (ESCAPE '\');

query III
SELECT * FROM assorted_lists;
----
[8, 7, 6]	[hello, 'Duck\'DB']	[2022-12-02, 1929-01-25]

#              Escape tests
#---------------------------------------------------

# regular escape
# FIXME: this is weird behavior
query I
select '[{"bar":"\""}]'::VARCHAR[];
----
['{"bar":"\\""}']

statement error
select '[{"bar":"\\""}]'::VARCHAR[];
----

# Unescaped doublequote ends the quote early, leaving an uneven amount of `"`, causing an error
statement error
select '[{"bar":"\\""}]'::STRUCT(bar VARCHAR)[];
----
can't be cast to the destination type STRUCT(bar VARCHAR)[]

# uneven amount of escapes does escape the "
query I
select '[{"bar":"\\\""}]'::STRUCT(bar VARCHAR)[];
----
[{'bar': '\\"'}]

# all are escaped except for the last one
query III
select '[{"bar":"\"\"\\\"\"\"\\"}]'::STRUCT(bar VARCHAR)[] a, CAST(a::VARCHAR AS STRUCT(bar VARCHAR)[]) b, a == b;
----
[{'bar': '""\\"""\\'}]	[{'bar': '""\\"""\\'}]	true

query III
select $$[\  \\abc\\ \ , def, ghi]$$::VARCHAR[] a, a[1], len(a[1])
----
[\  \\abc\\ \, def, ghi]	\  \\abc\\ \	12

query III
select $$["\  \\abc\\ \ ", def, ghi]$$::VARCHAR[] a, a[1], len(a[1])
----
['  \\abc\\  ', def, ghi]	  \abc\  	9