# name: test/sql/function/list/list_contains.test
# description: Test list_contains function
# group: [list]

statement ok
PRAGMA enable_verification

statement ok
create table TEST2 (i int[], j int);

statement ok
insert into TEST2 values ([2,1,3], 2), ([2,3,4], 5), ([1], NULL);

query T
select list_contains(i, j) from TEST2
----
1
0
NULL

statement ok
create table TEST (i int[]);

statement ok
insert into TEST values ([2,1,3]), ([2,3,4]), ([1]);

query TT
SELECT i, list_contains(i,1) from TEST;
----
[2, 1, 3]	1
[2, 3, 4]	0
[1]	1

query TT
SELECT i, list_contains(i,4.0) from TEST;
----
[2, 1, 3]	0
[2, 3, 4]	1
[1]	0

statement ok
DROP table TEST;

statement ok
create table STR_TEST (i string[]);

statement ok
insert into STR_TEST values (['a','b','c']), (['d','a','e']), (['b']), (['aaaaaaaaaaaaaaaaaaaaaaaa']);

query TT
SELECT i, list_contains(i,'a') from STR_TEST;
----
[a, b, c]	1
[d, a, e]	1
[b]	0
[aaaaaaaaaaaaaaaaaaaaaaaa]	0

query TT
SELECT i, list_contains(i,'aaaaaaaaaaaaaaaaaaaaaaaa') from STR_TEST;
----
[a, b, c]	0
[d, a, e]	0
[b]	0
[aaaaaaaaaaaaaaaaaaaaaaaa]	1

query TT
SELECT i, list_contains(i, '0') from STR_TEST;
----
[a, b, c]	0
[d, a, e]	0
[b]	0
[aaaaaaaaaaaaaaaaaaaaaaaa]	0


query TT
SELECT i, list_contains(i,NULL) from STR_TEST;
----
[a, b, c]	NULL
[d, a, e]	NULL
[b]	NULL
[aaaaaaaaaaaaaaaaaaaaaaaa]	NULL

statement ok
DROP table STR_TEST;


# basic functionality
query T
SELECT list_contains([7,2,5], 7)
----
1

# Empty list
query T
SELECT list_contains([], 7)
----
0

# Mixed data types
query T
SELECT list_contains([1,2,3],1.0)
----
1

query T
SELECT list_contains([1.0,2.0,3.0,4.0],1)
----
1

query T
SELECT list_contains([1,2,3],4.0)
----
0

query T
SELECT list_contains([1.0,2.0,3.0],4)
----
0


statement error
SELECT list_contains([1.0,2.0,3.0], 'a')
----
Conversion Error: Could not convert string "a" to DECIMAL(2,1)

# Not a list as input
statement error
SELECT list_contains('a', 'a')
----

query T
SELECT list_contains(NULL,NULL)
----
NULL


query T
SELECT list_contains([7], 5)
----
0

query T
SELECT list_contains([1,2,3,4],4)
----
1

query T
SELECT list_contains([1,2,3,4],5)
----
0

query T
SELECT list_contains([1.0,2.0,3.0,4.0],5.0)
----
0

query T
SELECT list_contains([1.0,2.0,3.0,4.0],4.0)
----
1

query T
SELECT list_contains([true, false],true)
----
1

query T
SELECT list_contains([true, true],false)
----
0

query T
SELECT list_contains(['test', 'notest'],'notest')
----
1

query T
SELECT list_contains(['test', 'notest'],'a')
----
0

# Null types
query T
SELECT list_contains(NULL,1)
----
NULL

# Null types
query T
SELECT list_contains([1],NULL)
----
NULL

query T
SELECT list_contains([NULL],NULL)
----
NULL

query T
SELECT list_contains([NULL, 1],NULL)
----
NULL

query T
SELECT list_contains([NULL, 1],1)
----
1

query T
SELECT list_contains([NULL, 0],1)
----
0

query T
SELECT list_contains([],NULL)
----
NULL

query T
SELECT list_contains([NULL,7], 7)
----
1

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

statement error
SELECT list_contains([[1,2,3],[1],[1,2,3]])
----

statement error
SELECT list_contains(1)
----

statement error
SELECT list_contains(1,1)
----

statement ok
PRAGMA debug_force_external=true

loop i 0 2

foreach type <integral>

# list tests
statement ok
CREATE TABLE test0 (i ${type}[])

statement ok
INSERT INTO test0 VALUES ([2]), ([1]), ([1, 2]), ([]), ([2, 2]), ([NULL]), ([2, 3])

query T
SELECT list_contains(i,1) from test0
----
0
1
1
0
0
0
0

statement ok
DROP TABLE test0

statement ok
PRAGMA debug_force_external=false

endloop

endloop

query T
SELECT list_contains([[1,2,3],[1]],[1])
----
1

query T
SELECT list_contains([[1,2,3],[1]],[2])
----
0

query T
SELECT list_contains([[1,2,3],[1]],[1,2,3])
----
1

query T
SELECT list_contains([[1,3],[1]],[1,2,3])
----
0

query T
SELECT list_contains([[1,3],[1], [1,2,3]],[1,2,3])
----
1

query T
SELECT list_contains([[NULL],[1], [1,2,3]],NULL)
----
NULL

query T
SELECT list_contains([[NULL],[1], [1,2,3]],[NULL])
----
1

query T
SELECT list_contains([[1,NULL],[1], [1,2,3]],[1,NULL])
----
1

query T
SELECT list_contains([[1,NULL],[1], [1,2,3]],[0,NULL])
----
0

# nested types
query T
SELECT list_contains([{a: 1}, {a: 2}], {a: 2})
----
1

query T
SELECT list_contains([{a: 1}, {a: 2}], {a: 3})
----
0

query T
SELECT list_contains([{a: 1, b: 3}, {a: 2, b: 2}], {a: 2, b: 2})
----
1

query T
SELECT list_contains([{a: 1, b: 3}, {a: 2, b: 2}], {a: 1, b: 2})
----
0

query T
SELECT list_contains([MAP([1], [2])], MAP([1], [2]))
----
1

query T
SELECT list_contains([MAP([1], [2])], MAP([1], [3]))
----
0


query T
SELECT list_contains([MAP([1], [2]), NULL], NULL)
----
NULL

query T
SELECT list_contains([MAP([1], [2]), NULL], MAP([1], [2]))
----
1

query T
SELECT list_contains([[1,2,3],NULL],NULL)
----
NULL

statement ok
PRAGMA debug_force_external=true

loop i 0 2

foreach type float double

# list tests
statement ok
CREATE TABLE test0 (i ${type}[])

statement ok
INSERT INTO test0 VALUES ([2.0]), ([1.0]), ([1.0, 2.0]), ([]), ([2.0, 2.0]), ([NULL]), ([2.0, 3.0])

query T
SELECT list_contains(i,1.0) from test0
----
0
1
1
0
0
0
0

statement ok
DROP TABLE test0

statement ok
PRAGMA debug_force_external=false

endloop

endloop

# issue 3450 test case

statement ok
CREATE TABLE functions (function_name varchar, function_type varchar, parameter_types varchar[]);

statement ok
INSERT INTO functions VALUES ('last_day', 'scalar', ['TIMESTAMP']), ('dayname', 'else', ['TIMESTAMP']),
	('date_part', 'scalar', ['DATE', 'VARCHAR']), ('scalar_part', 'scalar', ['VARCHAR', 'TIMESTAMP']);

query I
SELECT function_name FROM functions
WHERE function_type = 'scalar' AND list_has(parameter_types, 'TIMESTAMP');
----
last_day
scalar_part

# issue 3457 test case

statement ok
CREATE TABLE test (id int, name text[]);

statement ok
INSERT INTO test VALUES (1, ['U2']), (2, ['Blur','Rock']), (3, ['Oasis','2Pac']);

query II
SELECT id, name FROM test WHERE ARRAY_CONTAINS(name, '2Pac');
----
3	[Oasis, 2Pac]

query II
SELECT id, name FROM test WHERE id IN (2,3) AND ARRAY_CONTAINS(name, '2Pac');
----
3	[Oasis, 2Pac]

query II
WITH cte AS (SELECT id, name FROM test WHERE id IN (2,3))
SELECT id, name FROM cte WHERE ARRAY_CONTAINS(name, '2Pac');
----
3	[Oasis, 2Pac]

query II
SELECT id, name FROM test WHERE id IN (2,3) AND name::text ILIKE '%2Pac%';
----
3	[Oasis, 2Pac]

# Test list_contains with NULLs inside list_filter.

statement ok
CREATE TABLE list_of_list(l1 int[][], l2 int[][]);

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

query I
SELECT list_filter(l1, lambda x: list_contains(l2, x)) FROM list_of_list;
----
[]