# name: test/sql/function/list/list_reverse.test
# group: [list]

statement ok
PRAGMA enable_verification

query I
SELECT list_reverse(NULL);
----
NULL

query I
SELECT list_reverse([]);
----
[]

query I
SELECT list_reverse([NULL]);
----
[NULL]

query I
SELECT list_reverse([1, 42, 2]);
----
[2, 42, 1]

query I
SELECT array_reverse([1, 42, 2]);
----
[2, 42, 1]

query I
SELECT list_reverse([1, 42, NULL, 2]);
----
[2, NULL, 42, 1]

query I
SELECT list_reverse(list_reverse([1, 3, 3, 42, 117, 69, NULL]));
----
[1, 3, 3, 42, 117, 69, NULL]

query I
SELECT list_reverse ([[1, 2 ,42], [3, 4]]);
----
[[3, 4], [1, 2, 42]]

query I
prepare q1 as select list_reverse(?);
execute q1([5, 42, 3]);
----
[3, 42, 5]

statement ok
create or replace table tbl_big as select range(5000) as list;

query I
select list_sort((list), 'desc') == list_reverse(list) from tbl_big;
----
true

statement ok
CREATE TABLE tbl (id INTEGER, list INTEGER[]);

statement ok
INSERT INTO tbl VALUES (1, [NULL, 3, 117, 42, 1]), (2, NULL), (3, [1, 8, 9]), (4, NULL), (5, NULL), (6, [NULL]);

query II
SELECT id, list_reverse(list) FROM tbl ORDER BY id;
----
1	[1, 42, 117, 3, NULL]
2	NULL
3	[9, 8, 1]
4	NULL
5	NULL
6	[NULL]

statement ok
DROP TABLE tbl;

statement ok
CREATE TABLE tbl2 (id INTEGER, list INTEGER[]);

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

query II
SELECT id, list_reverse(list) FROM tbl2 ORDER BY id;
----
1	[3, 2, 1]
1	[6, 5, 4]
3	[9, 8, 7]

statement ok
DROP TABLE tbl2;

query IIIIIIII
select list_reverse(list_reverse(columns(['int_array', 'varchar_array', 'nested_int_array', 'array_of_structs', 'timestamp_array', 'double_array', 'date_array', 'timestamptz_array']))) 
    IS NOT DISTINCT FROM
     columns(['int_array', 'varchar_array', 'nested_int_array', 'array_of_structs', 'timestamp_array', 'double_array', 'date_array', 'timestamptz_array']) 
from test_all_types();
----
true	true	true	true	true	true	true	true
true	true	true	true	true	true	true	true
true	true	true	true	true	true	true	true

statement ok
select list_reverse(test_vector) from test_vector_types(null::int[], false);

statement ok
select list_reverse(test_vector) from test_vector_types(null::int[], true);

query I nosort q1
select true from test_vector_types(null::int[], false);
----

query I nosort q1
select list_sort((list), 'desc') IS NOT DISTINCT FROM list 
from (select list_reverse(test_vector) as list from test_vector_types(null::int[], false));
----

query I nosort q1
select list_sort((list), 'desc') IS NOT DISTINCT FROM list 
from (select list_reverse(test_vector) as list from test_vector_types(null::int[], true));
----

statement error
SELECT list_reverse()
----

statement error
SELECT list_reverse(42)
----

statement error
SELECT list_reverse ([1, 3, 2, 42, 117,, NULL])
----
Parser Error: syntax error at or near ","

statement ok
CREATE TABLE palindromes (s VARCHAR);

statement ok
INSERT INTO palindromes VALUES ('racecar'), ('civic'), ('defied'), ('repaper'), ('kayak'), ('rotator'), ('tattarrattat'), ('saippuakivikauppias'), ('malayalam');

query I
SELECT list_aggr(list_reverse(str_split(s, '')), 'string_agg', '') FROM palindromes ORDER BY s;
----
civic
deifed
kayak
malayalam
racecar
repaper
rotator
saippuakivikauppias
tattarrattat

statement ok
DROP TABLE palindromes;

query I
WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  list_reverse(arr) AS reverse_arr
FROM example ORDER BY length(reverse_arr) DESC;
----
[3, 2, 1]
[5, 4]
[]


# test incorrect syntax

statement error
SELECT list_reverse()
----

statement error
SELECT list_reverse(*)
----

statement error
SELECT list_reverse([1, 2], 2)
----

# list constant tests

query I
SELECT list_reverse([1, 42, 39, 58])
----
[58, 39, 42, 1]

query I
SELECT list_reverse([1, NULL, 42, 39, NULL, 58])
----
[58, NULL, 39, 42, NULL, 1]


query I
SELECT list_reverse([1, 42, -39, 58, -1, 18])
----
[18, -1, 58, -39, 42, 1]

query I
SELECT list_reverse(list_reverse([11, -100, 678]))
----
[11, -100, 678]


# list column tests

statement ok
CREATE OR REPLACE TABLE integers AS SELECT LIST(i) AS i FROM range(1, 10, 1) t1(i)

statement ok
INSERT INTO integers VALUES ([NULL]), (NULL), ([])

query I
SELECT list_reverse(i) FROM integers
----
[9, 8, 7, 6, 5, 4, 3, 2, 1]
[NULL]
NULL
[]

query I
SELECT (i).list_reverse() FROM integers
----
[9, 8, 7, 6, 5, 4, 3, 2, 1]
[NULL]
NULL
[]

# Nested list and very Large list
query I
SELECT list_reverse([[1], [1, 2], NULL, [NULL], [], [1, 2, 3]])
----
[[1, 2, 3], [], [NULL], NULL, [1, 2], [1]]

query I
SELECT ([[1], [1, 2], NULL, [NULL], [], [1, 2, 3]]).list_reverse()
----
[[1, 2, 3], [], [NULL], NULL, [1, 2], [1]]


statement ok
CREATE OR REPLACE TABLE lists AS SELECT range % 4 g, list(range) l FROM range(10000) GROUP BY range % 4;

query T
with cte0 as (
  select g, list_reverse(l) l from lists
), cte1 as (
  select g, unnest(l) i from cte0
), cte2 as (
  select g, i, lead(g, 1) over () lg, lead(i, 1) over () li from cte1
)
select count(*)
from cte2
where g = lg
  and lg not null
  and li > i
----
0

# bug found in PR10587
query I
WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  list_reverse(arr) AS reverse_arr
FROM example ORDER BY length(reverse_arr) DESC;
----
[3, 2, 1]
[5, 4]
[]