array_position.slt

# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.

include ./init_data.slt.part

## array_position (aliases: `list_position`, `array_indexof`, `list_indexof`)

## array_position with NULL (follow PostgreSQL)
query II
select array_position([1, 2, 3, 4, 5], arrow_cast(NULL, 'Int64')), array_position(arrow_cast(NULL, 'List(Int64)'), 1);
----
NULL NULL

# array_position with no match (incl. empty array) returns NULL
query II
select array_position([], 1), array_position([2], 1);
----
NULL NULL

# array_position scalar function #1
query III
select array_position(['h', 'e', 'l', 'l', 'o'], 'l'), array_position([1, 2, 3, 4, 5], 5), array_position([1, 1, 1], 1);
----
3 5 1

query III
select array_position(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), array_position(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), array_position(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
3 5 1

query III
select array_position(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'FixedSizeList(5, Utf8)'), 'l'), array_position(arrow_cast([1, 2, 3, 4, 5], 'FixedSizeList(5, Int64)'), 5), array_position(arrow_cast([1, 1, 1], 'FixedSizeList(3, Int64)'), 1);
----
3 5 1

# array_position scalar function #2 (with optional argument)
query III
select array_position(['h', 'e', 'l', 'l', 'o'], 'l', 4), array_position([1, 2, 5, 4, 5], 5, 4), array_position([1, 1, 1], 1, 2);
----
4 5 2

query III
select array_position(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l', 4), array_position(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5, 4), array_position(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1, 2);
----
4 5 2

query III
select array_position(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'FixedSizeList(5, Utf8)'), 'l', 4), array_position(arrow_cast([1, 2, 3, 4, 5], 'FixedSizeList(5, Int64)'), 5, 4), array_position(arrow_cast([1, 1, 1], 'FixedSizeList(3, Int64)'), 1, 2);
----
4 5 2

# array_position scalar function #3 (element is list)
query II
select array_position(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6], [7, 8, 9]), [4, 5, 6]), array_position(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), [2, 3, 4]);
----
2 2

# array_position scalar function #4 (element in list; with optional argument)
query II
select array_position(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6], [7, 8, 9]), [4, 5, 6], 3), array_position(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), [2, 3, 4], 3);
----
4 3

query II
select array_position(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6], [7, 8, 9]), 'LargeList(List(Int64))'), [4, 5, 6]), array_position(arrow_cast(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), 'LargeList(List(Int64))'), [2, 3, 4]);
----
2 2

query I
SELECT array_position(arrow_cast([5, 2, 3, 4, 5], 'List(Int32)'), 5)
----
1

query I
SELECT array_position(arrow_cast([5, 2, 3, 4, 5], 'List(Int32)'), 5, 2)
----
5

query I
SELECT array_position(arrow_cast([1, 1, 100, 1, 1], 'LargeList(Int32)'), 100)
----
3

query error DataFusion error: Error during planning: Failed to coerce arguments to satisfy a call to 'array_position' function: coercion from
SELECT array_position([1, 2, 3], 'foo')

query error DataFusion error: Error during planning: Failed to coerce arguments to satisfy a call to 'array_position' function: coercion from
SELECT array_position([1, 2, 3], 'foo', 2)

# list_position scalar function #5 (function alias `array_position`)
query III
select list_position(['h', 'e', 'l', 'l', 'o'], 'l'), list_position([1, 2, 3, 4, 5], 5), list_position([1, 1, 1], 1);
----
3 5 1

query III
select list_position(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), list_position(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), list_position(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
3 5 1

# array_indexof scalar function #6 (function alias `array_position`)
query III
select array_indexof(['h', 'e', 'l', 'l', 'o'], 'l'), array_indexof([1, 2, 3, 4, 5], 5), array_indexof([1, 1, 1], 1);
----
3 5 1

query III
select array_indexof(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), array_indexof(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), array_indexof(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
3 5 1

# list_indexof scalar function #7 (function alias `array_position`)
query III
select list_indexof(['h', 'e', 'l', 'l', 'o'], 'l'), list_indexof([1, 2, 3, 4, 5], 5), list_indexof([1, 1, 1], 1);
----
3 5 1

query III
select list_indexof(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), list_indexof(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), list_indexof(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
3 5 1

# array_position with columns #1
query II
select array_position(column1, column2), array_position(column1, column2, column3) from arrays_values_without_nulls;
----
1 1
2 2
3 3
4 4

query II
select array_position(column1, column2), array_position(column1, column2, column3) from large_arrays_values_without_nulls;
----
1 1
2 2
3 3
4 4

# array_position with columns #2 (element is list)
query II
select array_position(column1, column2), array_position(column1, column2, column3) from nested_arrays;
----
3 3
2 5

query II
select array_position(column1, column2), array_position(column1, column2, column3) from nested_arrays;
----
3 3
2 5

# array_position with columns and scalars #1
query III
select array_position(make_array(1, 2, 3, 4, 5), column2), array_position(column1, 3), array_position(column1, 3, 5) from arrays_values_without_nulls;
----
1 3 NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL

query III
select array_position(arrow_cast(make_array(1, 2, 3, 4, 5), 'LargeList(Int64)'), column2), array_position(column1, 3), array_position(column1, 3, 5) from large_arrays_values_without_nulls;
----
1 3 NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL

# array_position with columns and scalars #2 (element is list)
query III
select array_position(make_array([1, 2, 3], [4, 5, 6], [11, 12, 13]), column2), array_position(column1, make_array(4, 5, 6)), array_position(column1, make_array(1, 2, 3), 2) from nested_arrays;
----
NULL 6 4
NULL 1 NULL

query III
select array_position(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [11, 12, 13]), 'LargeList(LargeList(Int64))'), column2), array_position(column1, arrow_cast(make_array(4, 5, 6), 'LargeList(Int64)')), array_position(column1, arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 2) from large_nested_arrays;
----
NULL 6 4
NULL 1 NULL

# array_position with NULL element in haystack array (NULL = NULL semantics)
query III
select array_position([1, NULL, 3], arrow_cast(NULL, 'Int64')), array_position([NULL, 2, 3], arrow_cast(NULL, 'Int64')), array_position([1, 2, NULL], arrow_cast(NULL, 'Int64'));
----
2 1 3

query I
select array_position(arrow_cast([1, NULL, 3], 'LargeList(Int64)'), arrow_cast(NULL, 'Int64'));
----
2

# array_position with NULL element in array and start_from
query II
select array_position([NULL, 1, NULL, 2], arrow_cast(NULL, 'Int64'), 2), array_position([NULL, 1, NULL, 2], arrow_cast(NULL, 'Int64'), 1);
----
3 1

# array_position with column array and scalar element
query IIII
select array_position(column1, 3), array_position(column1, 10), array_position(column1, 20), array_position(column1, 999) from arrays_values_without_nulls;
----
3 10 NULL NULL
NULL NULL 10 NULL
NULL NULL NULL NULL
NULL NULL NULL NULL

query II
select array_position(column1, 3), array_position(column1, 20) from large_arrays_values_without_nulls;
----
3 NULL
NULL 10
NULL NULL
NULL NULL

query II
select array_position(column1, 3), array_position(column1, 20) from fixed_size_arrays_values_without_nulls;
----
3 NULL
NULL 10
NULL NULL
NULL NULL

# array_position with column array, scalar element, and scalar start_from
query II
select array_position(column1, 3, 1), array_position(column1, 3, 4) from arrays_values_without_nulls;
----
3 NULL
NULL NULL
NULL NULL
NULL NULL

query II
select array_position(column1, 3, 1), array_position(column1, 3, 4) from large_arrays_values_without_nulls;
----
3 NULL
NULL NULL
NULL NULL
NULL NULL

# array_position with column array, scalar element, and column start_from
query I
select array_position(column1, 3, column3) from arrays_values_without_nulls;
----
3
NULL
NULL
NULL

# array_position with scalar haystack, scalar element, and column start_from
query I
select array_position([1, 2, 1, 2], 2, column3) from arrays_values_without_nulls;
----
2
2
4
4

# array_position start_from boundary cases
query IIII
select array_position([1, 2, 3], 3, 3), array_position([1, 2, 3], 1, 2), array_position([1, 2, 3], 1, 1), array_position([1, 2, 3], 3, 4);
----
3 NULL 1 NULL

query II
select array_position([1, 2, 3], 3, 4), array_position([1], 1, 2);
----
NULL NULL

query error DataFusion error: Execution error: start_from out of bounds: -9223372036854775808
SELECT array_position([1], 1, -9223372036854775808);

# array_position with empty array in various contexts
query II
select array_position(arrow_cast(make_array(), 'List(Int64)'), 1), array_position(arrow_cast(make_array(), 'LargeList(Int64)'), 1);
----
NULL NULL

# FixedSizeList with start_from
query II
select array_position(arrow_cast([1, 2, 3, 1, 2], 'FixedSizeList(5, Int64)'), 1, 2), array_position(arrow_cast([1, 2, 3, 1, 2], 'FixedSizeList(5, Int64)'), 2, 4);
----
4 5

query I
select array_position(arrow_cast(['a', 'b', 'c', 'b'], 'FixedSizeList(4, Utf8)'), 'b', 3);
----
4

## array_positions (aliases: `list_positions`)

# array_positions with empty array
query ?
select array_positions(arrow_cast(make_array(), 'List(Int64)'), 1);
----
[]

query ?
select array_positions([1, 2, 3, 4, 5], null);
----
[]

#TODO: https://github.com/apache/datafusion/issues/7142
# array_positions with NULL (follow PostgreSQL)
#query ?
#select array_positions(null, 1);
#----
#NULL

# array_positions scalar function #1
query ???
select array_positions(['h', 'e', 'l', 'l', 'o'], 'l'), array_positions([1, 2, 3, 4, 5], 5), array_positions([1, 1, 1], 1);
----
[3, 4] [5] [1, 2, 3]

query ???
select array_positions(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), array_positions(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), array_positions(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
[3, 4] [5] [1, 2, 3]

query ???
select array_positions(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'FixedSizeList(5, Utf8)'), 'l'), array_positions(arrow_cast([1, 2, 3, 4, 5], 'FixedSizeList(5, Int64)'), 5), array_positions(arrow_cast([1, 1, 1], 'FixedSizeList(3, Int64)'), 1);
----
[3, 4] [5] [1, 2, 3]

# array_positions scalar function #2 (element is list)
query ?
select array_positions(make_array([1, 2, 3], [2, 1, 3], [1, 5, 6], [2, 1, 3], [4, 5, 6]), [2, 1, 3]);
----
[2, 4]

query ?
select array_positions(arrow_cast(make_array([1, 2, 3], [2, 1, 3], [1, 5, 6], [2, 1, 3], [4, 5, 6]), 'LargeList(List(Int64))'), [2, 1, 3]);
----
[2, 4]

query ?
select array_positions(arrow_cast(make_array([1, 2, 3], [2, 1, 3], [1, 5, 6], [2, 1, 3], [4, 5, 6]), 'FixedSizeList(5, List(Int64))'), [2, 1, 3]);
----
[2, 4]

# list_positions scalar function #3 (function alias `array_positions`)
query ???
select list_positions(['h', 'e', 'l', 'l', 'o'], 'l'), list_positions([1, 2, 3, 4, 5], 5), list_positions([1, 1, 1], 1);
----
[3, 4] [5] [1, 2, 3]

query ???
select list_positions(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'LargeList(Utf8)'), 'l'), list_positions(arrow_cast([1, 2, 3, 4, 5], 'LargeList(Int64)'), 5), list_positions(arrow_cast([1, 1, 1], 'LargeList(Int64)'), 1);
----
[3, 4] [5] [1, 2, 3]

query ???
select list_positions(arrow_cast(['h', 'e', 'l', 'l', 'o'], 'FixedSizeList(5, Utf8)'), 'l'),
       list_positions(arrow_cast([1, 2, 3, 4, 5], 'FixedSizeList(5, Int64)'), 5),
       list_positions(arrow_cast([1, 1, 1], 'FixedSizeList(3, Int64)'), 1);
----
[3, 4] [5] [1, 2, 3]

# array_positions with columns #1
query ?
select array_positions(column1, column2) from arrays_values_without_nulls;
----
[1]
[2]
[3]
[4]

query ?
select array_positions(arrow_cast(column1, 'LargeList(Int64)'), column2) from arrays_values_without_nulls;
----
[1]
[2]
[3]
[4]

query ?
select array_positions(arrow_cast(column1, 'LargeList(Int64)'), column2) from fixed_size_arrays_values_without_nulls;
----
[1]
[2]
[3]
[4]

# array_positions with columns #2 (element is list)
query ?
select array_positions(column1, column2) from nested_arrays;
----
[3]
[2, 5]

query ?
select array_positions(arrow_cast(column1, 'LargeList(List(Int64))'), column2) from nested_arrays;
----
[3]
[2, 5]

query ?
select array_positions(column1, column2) from fixed_size_nested_arrays;
----
[3]
[2, 5]

# array_positions with columns and scalars #1
query ??
select array_positions(column1, 4), array_positions(array[1, 2, 23, 13, 33, 45], column2) from arrays_values_without_nulls;
----
[4] [1]
[] []
[] [3]
[] []

query ??
select array_positions(arrow_cast(column1, 'LargeList(Int64)'), 4), array_positions(array[1, 2, 23, 13, 33, 45], column2) from arrays_values_without_nulls;
----
[4] [1]
[] []
[] [3]
[] []

query ??
select array_positions(column1, 4), array_positions(array[1, 2, 23, 13, 33, 45], column2) from fixed_size_arrays_values_without_nulls;
----
[4] [1]
[] []
[] [3]
[] []

# array_positions with columns and scalars #2 (element is list)
query ??
select array_positions(column1, make_array(4, 5, 6)), array_positions(make_array([1, 2, 3], [11, 12, 13], [4, 5, 6]), column2) from nested_arrays;
----
[6] []
[1] []

query ??
select array_positions(arrow_cast(column1, 'LargeList(List(Int64))'), make_array(4, 5, 6)), array_positions(arrow_cast(make_array([1, 2, 3], [11, 12, 13], [4, 5, 6]), 'LargeList(List(Int64))'), column2) from nested_arrays;
----
[6] []
[1] []

query ??
select array_positions(column1, make_array(4, 5, 6)), array_positions(make_array([1, 2, 3], [11, 12, 13], [4, 5, 6]), column2) from fixed_size_nested_arrays;
----
[6] []
[1] []


include ./cleanup.slt.part