This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 038763c7db Support `FixedSizeList` type coercion (#8902)
038763c7db is described below
commit 038763c7db0961e183b481f0d890abe8da008562
Author: Alex Huang <[email protected]>
AuthorDate: Fri Feb 2 00:42:41 2024 +0800
Support `FixedSizeList` type coercion (#8902)
* Add support for parsing FixedSizeList type
* support cast fixedsizelist from list
* support FixedSizeList type coercion
* fix conflict
* add test for []
* support fixedsizelist for functinos with array-element or element-array
argument
* add tests for array_element
* add tests for array_remove
* add tests for array_remove_n
* add tests for array_has
* fix comment
* fix comment
* add tests for array_positoins
* test chore
* test chore
* remove useless logic
* refatctor coerced_type_with_base_type_only function
* add null test for array_has
* refactor: put fixedsizelist in coerce_arguments_for_fun
* chore
* refactor type signature
* add array_and_index function
* put all type coercion in coerce_arguments_for_signature
* add comment
Co-authored-by: Andrew Lamb <[email protected]>
---------
Co-authored-by: Andrew Lamb <[email protected]>
---
datafusion/common/src/scalar.rs | 5 +-
datafusion/common/src/utils.rs | 54 +-
datafusion/expr/src/built_in_function.rs | 46 +-
datafusion/expr/src/signature.rs | 57 +-
datafusion/expr/src/type_coercion/functions.rs | 62 ++-
datafusion/optimizer/src/analyzer/type_coercion.rs | 1 -
datafusion/sqllogictest/test_files/array.slt | 604 ++++++++++++++++++++-
.../sqllogictest/test_files/arrow_typeof.slt | 2 +-
8 files changed, 741 insertions(+), 90 deletions(-)
diff --git a/datafusion/common/src/scalar.rs b/datafusion/common/src/scalar.rs
index 2f9e374bd7..36b00b65e2 100644
--- a/datafusion/common/src/scalar.rs
+++ b/datafusion/common/src/scalar.rs
@@ -1483,7 +1483,9 @@ impl ScalarValue {
DataType::Interval(IntervalUnit::MonthDayNano) => {
build_array_primitive!(IntervalMonthDayNanoArray,
IntervalMonthDayNano)
}
- DataType::List(_) | DataType::LargeList(_) =>
build_list_array(scalars)?,
+ DataType::List(_)
+ | DataType::LargeList(_)
+ | DataType::FixedSizeList(_, _) => build_list_array(scalars)?,
DataType::Struct(fields) => {
// Initialize a Vector to store the ScalarValues for each
column
let mut columns: Vec<Vec<ScalarValue>> =
@@ -1595,7 +1597,6 @@ impl ScalarValue {
| DataType::Time64(TimeUnit::Second)
| DataType::Time64(TimeUnit::Millisecond)
| DataType::Duration(_)
- | DataType::FixedSizeList(_, _)
| DataType::Union(_, _)
| DataType::Map(_, _)
| DataType::RunEndEncoded(_, _) => {
diff --git a/datafusion/common/src/utils.rs b/datafusion/common/src/utils.rs
index d21bd464f8..a12b71c17d 100644
--- a/datafusion/common/src/utils.rs
+++ b/datafusion/common/src/utils.rs
@@ -440,9 +440,9 @@ pub fn arrays_into_list_array(
/// ```
pub fn base_type(data_type: &DataType) -> DataType {
match data_type {
- DataType::List(field) | DataType::LargeList(field) => {
- base_type(field.data_type())
- }
+ DataType::List(field)
+ | DataType::LargeList(field)
+ | DataType::FixedSizeList(field, _) => base_type(field.data_type()),
_ => data_type.to_owned(),
}
}
@@ -464,31 +464,23 @@ pub fn coerced_type_with_base_type_only(
base_type: &DataType,
) -> DataType {
match data_type {
- DataType::List(field) => {
- let data_type = match field.data_type() {
- DataType::List(_) => {
- coerced_type_with_base_type_only(field.data_type(),
base_type)
- }
- _ => base_type.to_owned(),
- };
+ DataType::List(field) | DataType::FixedSizeList(field, _) => {
+ let field_type =
+ coerced_type_with_base_type_only(field.data_type(), base_type);
DataType::List(Arc::new(Field::new(
field.name(),
- data_type,
+ field_type,
field.is_nullable(),
)))
}
DataType::LargeList(field) => {
- let data_type = match field.data_type() {
- DataType::LargeList(_) => {
- coerced_type_with_base_type_only(field.data_type(),
base_type)
- }
- _ => base_type.to_owned(),
- };
+ let field_type =
+ coerced_type_with_base_type_only(field.data_type(), base_type);
DataType::LargeList(Arc::new(Field::new(
field.name(),
- data_type,
+ field_type,
field.is_nullable(),
)))
}
@@ -497,6 +489,32 @@ pub fn coerced_type_with_base_type_only(
}
}
+/// Recursively coerce and `FixedSizeList` elements to `List`
+pub fn coerced_fixed_size_list_to_list(data_type: &DataType) -> DataType {
+ match data_type {
+ DataType::List(field) | DataType::FixedSizeList(field, _) => {
+ let field_type =
coerced_fixed_size_list_to_list(field.data_type());
+
+ DataType::List(Arc::new(Field::new(
+ field.name(),
+ field_type,
+ field.is_nullable(),
+ )))
+ }
+ DataType::LargeList(field) => {
+ let field_type =
coerced_fixed_size_list_to_list(field.data_type());
+
+ DataType::LargeList(Arc::new(Field::new(
+ field.name(),
+ field_type,
+ field.is_nullable(),
+ )))
+ }
+
+ _ => data_type.clone(),
+ }
+}
+
/// Compute the number of dimensions in a list data type.
pub fn list_ndims(data_type: &DataType) -> u64 {
match data_type {
diff --git a/datafusion/expr/src/built_in_function.rs
b/datafusion/expr/src/built_in_function.rs
index b7bb17c86b..20b7df46e3 100644
--- a/datafusion/expr/src/built_in_function.rs
+++ b/datafusion/expr/src/built_in_function.rs
@@ -599,10 +599,11 @@ impl BuiltinScalarFunction {
}
BuiltinScalarFunction::ArrayDistinct =>
Ok(input_expr_types[0].clone()),
BuiltinScalarFunction::ArrayElement => match &input_expr_types[0] {
- List(field) => Ok(field.data_type().clone()),
- LargeList(field) => Ok(field.data_type().clone()),
+ List(field)
+ | LargeList(field)
+ | FixedSizeList(field, _) => Ok(field.data_type().clone()),
_ => plan_err!(
- "The {self} function can only accept list or largelist as
the first argument"
+ "The {self} function can only accept List, LargeList or
FixedSizeList as the first argument"
),
},
BuiltinScalarFunction::ArrayLength => Ok(UInt64),
@@ -922,10 +923,9 @@ impl BuiltinScalarFunction {
BuiltinScalarFunction::ArraySort => {
Signature::variadic_any(self.volatility())
}
- BuiltinScalarFunction::ArrayAppend => Signature {
- type_signature: ArrayAndElement,
- volatility: self.volatility(),
- },
+ BuiltinScalarFunction::ArrayAppend => {
+ Signature::array_and_element(self.volatility())
+ }
BuiltinScalarFunction::MakeArray => {
// 0 or more arguments of arbitrary type
Signature::one_of(vec![VariadicEqual, Any(0)],
self.volatility())
@@ -937,12 +937,17 @@ impl BuiltinScalarFunction {
}
BuiltinScalarFunction::ArrayDims => Signature::any(1,
self.volatility()),
BuiltinScalarFunction::ArrayEmpty => Signature::any(1,
self.volatility()),
- BuiltinScalarFunction::ArrayElement => Signature::any(2,
self.volatility()),
+ BuiltinScalarFunction::ArrayElement => {
+ Signature::array_and_index(self.volatility())
+ }
BuiltinScalarFunction::ArrayExcept => Signature::any(2,
self.volatility()),
BuiltinScalarFunction::Flatten => Signature::any(1,
self.volatility()),
- BuiltinScalarFunction::ArrayHasAll
- | BuiltinScalarFunction::ArrayHasAny
- | BuiltinScalarFunction::ArrayHas => Signature::any(2,
self.volatility()),
+ BuiltinScalarFunction::ArrayHasAll |
BuiltinScalarFunction::ArrayHasAny => {
+ Signature::any(2, self.volatility())
+ }
+ BuiltinScalarFunction::ArrayHas => {
+ Signature::array_and_element(self.volatility())
+ }
BuiltinScalarFunction::ArrayLength => {
Signature::variadic_any(self.volatility())
}
@@ -951,15 +956,20 @@ impl BuiltinScalarFunction {
BuiltinScalarFunction::ArrayPosition => {
Signature::variadic_any(self.volatility())
}
- BuiltinScalarFunction::ArrayPositions => Signature::any(2,
self.volatility()),
- BuiltinScalarFunction::ArrayPrepend => Signature {
- type_signature: ElementAndArray,
- volatility: self.volatility(),
- },
+ BuiltinScalarFunction::ArrayPositions => {
+ Signature::array_and_element(self.volatility())
+ }
+ BuiltinScalarFunction::ArrayPrepend => {
+ Signature::element_and_array(self.volatility())
+ }
BuiltinScalarFunction::ArrayRepeat => Signature::any(2,
self.volatility()),
- BuiltinScalarFunction::ArrayRemove => Signature::any(2,
self.volatility()),
+ BuiltinScalarFunction::ArrayRemove => {
+ Signature::array_and_element(self.volatility())
+ }
BuiltinScalarFunction::ArrayRemoveN => Signature::any(3,
self.volatility()),
- BuiltinScalarFunction::ArrayRemoveAll => Signature::any(2,
self.volatility()),
+ BuiltinScalarFunction::ArrayRemoveAll => {
+ Signature::array_and_element(self.volatility())
+ }
BuiltinScalarFunction::ArrayReplace => Signature::any(3,
self.volatility()),
BuiltinScalarFunction::ArrayReplaceN => Signature::any(4,
self.volatility()),
BuiltinScalarFunction::ArrayReplaceAll => {
diff --git a/datafusion/expr/src/signature.rs b/datafusion/expr/src/signature.rs
index 729131bd95..48f4c996cb 100644
--- a/datafusion/expr/src/signature.rs
+++ b/datafusion/expr/src/signature.rs
@@ -116,6 +116,12 @@ pub enum TypeSignature {
/// Function `make_array` takes 0 or more arguments with arbitrary types,
its `TypeSignature`
/// is `OneOf(vec![Any(0), VariadicAny])`.
OneOf(Vec<TypeSignature>),
+ /// Specifies Signatures for array functions
+ ArraySignature(ArrayFunctionSignature),
+}
+
+#[derive(Debug, Clone, PartialEq, Eq, Hash)]
+pub enum ArrayFunctionSignature {
/// Specialized Signature for ArrayAppend and similar functions
/// The first argument should be List/LargeList, and the second argument
should be non-list or list.
/// The second argument's list dimension should be one dimension less than
the first argument's list dimension.
@@ -126,6 +132,23 @@ pub enum TypeSignature {
/// The first argument should be non-list or list, and the second argument
should be List/LargeList.
/// The first argument's list dimension should be one dimension less than
the second argument's list dimension.
ElementAndArray,
+ ArrayAndIndex,
+}
+
+impl std::fmt::Display for ArrayFunctionSignature {
+ fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+ match self {
+ ArrayFunctionSignature::ArrayAndElement => {
+ write!(f, "array, element")
+ }
+ ArrayFunctionSignature::ElementAndArray => {
+ write!(f, "element, array")
+ }
+ ArrayFunctionSignature::ArrayAndIndex => {
+ write!(f, "array, index")
+ }
+ }
+ }
}
impl TypeSignature {
@@ -156,11 +179,8 @@ impl TypeSignature {
TypeSignature::OneOf(sigs) => {
sigs.iter().flat_map(|s| s.to_string_repr()).collect()
}
- TypeSignature::ArrayAndElement => {
- vec!["ArrayAndElement(List<T>, T)".to_string()]
- }
- TypeSignature::ElementAndArray => {
- vec!["ElementAndArray(T, List<T>)".to_string()]
+ TypeSignature::ArraySignature(array_signature) => {
+ vec![array_signature.to_string()]
}
}
}
@@ -263,6 +283,33 @@ impl Signature {
volatility,
}
}
+ /// Specialized Signature for ArrayAppend and similar functions
+ pub fn array_and_element(volatility: Volatility) -> Self {
+ Signature {
+ type_signature: TypeSignature::ArraySignature(
+ ArrayFunctionSignature::ArrayAndElement,
+ ),
+ volatility,
+ }
+ }
+ /// Specialized Signature for ArrayPrepend and similar functions
+ pub fn element_and_array(volatility: Volatility) -> Self {
+ Signature {
+ type_signature: TypeSignature::ArraySignature(
+ ArrayFunctionSignature::ElementAndArray,
+ ),
+ volatility,
+ }
+ }
+ /// Specialized Signature for ArrayElement and similar functions
+ pub fn array_and_index(volatility: Volatility) -> Self {
+ Signature {
+ type_signature: TypeSignature::ArraySignature(
+ ArrayFunctionSignature::ArrayAndIndex,
+ ),
+ volatility,
+ }
+ }
}
/// Monotonicity of the `ScalarFunctionExpr` with respect to its arguments.
diff --git a/datafusion/expr/src/type_coercion/functions.rs
b/datafusion/expr/src/type_coercion/functions.rs
index 63908d539b..806fdaaa52 100644
--- a/datafusion/expr/src/type_coercion/functions.rs
+++ b/datafusion/expr/src/type_coercion/functions.rs
@@ -15,14 +15,16 @@
// specific language governing permissions and limitations
// under the License.
-use crate::signature::TIMEZONE_WILDCARD;
+use crate::signature::{ArrayFunctionSignature, TIMEZONE_WILDCARD};
use crate::{Signature, TypeSignature};
use arrow::{
compute::can_cast_types,
datatypes::{DataType, TimeUnit},
};
-use datafusion_common::utils::list_ndims;
-use datafusion_common::{internal_err, plan_err, DataFusionError, Result};
+use datafusion_common::utils::{coerced_fixed_size_list_to_list, list_ndims};
+use datafusion_common::{
+ internal_datafusion_err, internal_err, plan_err, DataFusionError, Result,
+};
use super::binary::comparison_coercion;
@@ -48,7 +50,6 @@ pub fn data_types(
);
}
}
-
let valid_types = get_valid_types(&signature.type_signature,
current_types)?;
if valid_types
@@ -104,12 +105,11 @@ fn get_valid_types(
let elem_base_type = datafusion_common::utils::base_type(elem_type);
let new_base_type = comparison_coercion(&array_base_type,
&elem_base_type);
- if new_base_type.is_none() {
- return internal_err!(
+ let new_base_type = new_base_type.ok_or_else(|| {
+ internal_datafusion_err!(
"Coercion from {array_base_type:?} to {elem_base_type:?} not
supported."
- );
- }
- let new_base_type = new_base_type.unwrap();
+ )
+ })?;
let array_type =
datafusion_common::utils::coerced_type_with_base_type_only(
array_type,
@@ -117,10 +117,12 @@ fn get_valid_types(
);
match array_type {
- DataType::List(ref field) | DataType::LargeList(ref field) => {
+ DataType::List(ref field)
+ | DataType::LargeList(ref field)
+ | DataType::FixedSizeList(ref field, _) => {
let elem_type = field.data_type();
if is_append {
- Ok(vec![vec![array_type.clone(), elem_type.to_owned()]])
+ Ok(vec![vec![array_type.clone(), elem_type.clone()]])
} else {
Ok(vec![vec![elem_type.to_owned(), array_type.clone()]])
}
@@ -128,6 +130,23 @@ fn get_valid_types(
_ => Ok(vec![vec![]]),
}
}
+ fn array_and_index(current_types: &[DataType]) ->
Result<Vec<Vec<DataType>>> {
+ if current_types.len() != 2 {
+ return Ok(vec![vec![]]);
+ }
+
+ let array_type = ¤t_types[0];
+
+ match array_type {
+ DataType::List(_)
+ | DataType::LargeList(_)
+ | DataType::FixedSizeList(_, _) => {
+ let array_type = coerced_fixed_size_list_to_list(array_type);
+ Ok(vec![vec![array_type, DataType::Int64]])
+ }
+ _ => Ok(vec![vec![]]),
+ }
+ }
let valid_types = match signature {
TypeSignature::Variadic(valid_types) => valid_types
.iter()
@@ -160,12 +179,19 @@ fn get_valid_types(
}
TypeSignature::Exact(valid_types) => vec![valid_types.clone()],
- TypeSignature::ArrayAndElement => {
- return array_append_or_prepend_valid_types(current_types, true)
- }
- TypeSignature::ElementAndArray => {
- return array_append_or_prepend_valid_types(current_types, false)
- }
+ TypeSignature::ArraySignature(ref function_signature) => match
function_signature
+ {
+ ArrayFunctionSignature::ArrayAndElement => {
+ return array_append_or_prepend_valid_types(current_types, true)
+ }
+ ArrayFunctionSignature::ArrayAndIndex => {
+ return array_and_index(current_types)
+ }
+ ArrayFunctionSignature::ElementAndArray => {
+ return array_append_or_prepend_valid_types(current_types,
false)
+ }
+ },
+
TypeSignature::Any(number) => {
if current_types.len() != *number {
return plan_err!(
@@ -311,6 +337,8 @@ fn coerced_from<'a>(
Utf8 | LargeUtf8 => Some(type_into.clone()),
Null if can_cast_types(type_from, type_into) =>
Some(type_into.clone()),
+ List(_) if matches!(type_from, FixedSizeList(_, _)) =>
Some(type_into.clone()),
+
// Only accept list and largelist with the same number of dimensions
unless the type is Null.
// List or LargeList with different dimensions should be handled in
TypeSignature or other places before this.
List(_) | LargeList(_)
diff --git a/datafusion/optimizer/src/analyzer/type_coercion.rs
b/datafusion/optimizer/src/analyzer/type_coercion.rs
index 8710249e12..d804edb0c5 100644
--- a/datafusion/optimizer/src/analyzer/type_coercion.rs
+++ b/datafusion/optimizer/src/analyzer/type_coercion.rs
@@ -589,7 +589,6 @@ fn coerce_arguments_for_fun(
if expressions.is_empty() {
return Ok(vec![]);
}
-
let mut expressions: Vec<Expr> = expressions.to_vec();
// Cast Fixedsizelist to List for array functions
diff --git a/datafusion/sqllogictest/test_files/array.slt
b/datafusion/sqllogictest/test_files/array.slt
index e6a8181be1..4fdc428d7a 100644
--- a/datafusion/sqllogictest/test_files/array.slt
+++ b/datafusion/sqllogictest/test_files/array.slt
@@ -77,6 +77,19 @@ AS
FROM arrays
;
+#TODO: create FixedSizeList with NULL column
+statement ok
+CREATE TABLE fixed_size_arrays
+AS VALUES
+ (arrow_cast(make_array(make_array(NULL, 2),make_array(3, NULL)),
'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(1.1, 2.2, 3.3),
'FixedSizeList(3, Float64)'), arrow_cast(make_array('L', 'o', 'r', 'e', 'm'),
'FixedSizeList(5, Utf8)')),
+ (arrow_cast(make_array(make_array(3, 4),make_array(5, 6)), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array(NULL, 5.5, 6.6), 'FixedSizeList(3,
Float64)'), arrow_cast(make_array('i', 'p', NULL, 'u', 'm'), 'FixedSizeList(5,
Utf8)')),
+ (arrow_cast(make_array(make_array(5, 6),make_array(7, 8)), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array(7.7, 8.8, 9.9), 'FixedSizeList(3,
Float64)'), arrow_cast(make_array('d', NULL, 'l', 'o', 'r'), 'FixedSizeList(5,
Utf8)')),
+ (arrow_cast(make_array(make_array(7, NULL),make_array(9, 10)),
'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(10.1, NULL, 12.2),
'FixedSizeList(3, Float64)'), arrow_cast(make_array('s', 'i', 't', 'a', 'b'),
'FixedSizeList(5, Utf8)')),
+ (arrow_cast(make_array(make_array(7, NULL),make_array(9, 10)),
'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(13.3, 14.4, 15.5),
'FixedSizeList(3, Float64)'), arrow_cast(make_array('a', 'm', 'e', 't', 'x'),
'FixedSizeList(5, Utf8)')),
+ (arrow_cast(make_array(make_array(11, 12),make_array(13, 14)),
'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(13.3, 14.4, 15.5),
'FixedSizeList(3, Float64)'), arrow_cast(make_array(',','a','b','c','d'),
'FixedSizeList(5, Utf8)')),
+ (arrow_cast(make_array(make_array(15, 16),make_array(NULL, 18)),
'FixedSizeList(2, List(Int64))'), arrow_cast(make_array(16.6, 17.7, 18.8),
'FixedSizeList(3, Float64)'), arrow_cast(make_array(',','a','b','c','d'),
'FixedSizeList(5, Utf8)'))
+;
+
statement ok
CREATE TABLE slices
AS VALUES
@@ -89,6 +102,17 @@ AS VALUES
(make_array(51, 52, NULL, 54, 55, 56, 57, 58, 59, 60), 5, NULL)
;
+statement ok
+CREATE TABLE fixed_slices
+AS VALUES
+ (arrow_cast(make_array(NULL, 2, 3, 4, 5, 6, 7, 8, 9, 10), 'FixedSizeList(10,
Int64)'), 1, 1),
+ (arrow_cast(make_array(11, 12, 13, 14, 15, 16, 17, 18, NULL, 20),
'FixedSizeList(10, Int64)'), 2, -4),
+ (arrow_cast(make_array(21, 22, 23, NULL, 25, 26, 27, 28, 29, 30),
'FixedSizeList(10, Int64)'), 0, 0),
+ (arrow_cast(make_array(31, 32, 33, 34, 35, NULL, 37, 38, 39, 40),
'FixedSizeList(10, Int64)'), -4, -7),
+ (arrow_cast(make_array(41, 42, 43, 44, 45, 46, 47, 48, 49, 50),
'FixedSizeList(10, Int64)'), NULL, 6),
+ (arrow_cast(make_array(51, 52, NULL, 54, 55, 56, 57, 58, 59,
60),'FixedSizeList(10, Int64)'), 5, NULL)
+;
+
statement ok
CREATE TABLE arrayspop
AS VALUES
@@ -119,6 +143,13 @@ AS
FROM nested_arrays
;
+statement ok
+CREATE TABLE fixed_size_nested_arrays
+AS VALUES
+ (arrow_cast(make_array(make_array(1, 2, 3), make_array(2, 9, 1),
make_array(7, 8, 9), make_array(1, 2, 3), make_array(1, 7, 4), make_array(4, 5,
6)), 'FixedSizeList(6, List(Int64))'), arrow_cast(make_array(7, 8, 9),
'FixedSizeList(3, Int64)'), 2, arrow_cast(make_array([[1, 2, 3], [4, 5, 6]],
[[7, 8, 9], [10, 11, 12]]), 'FixedSizeList(2, List(List(Int64)))'),
arrow_cast(make_array(11, 12, 13), 'FixedSizeList(3, Int64)')),
+ (arrow_cast(make_array(make_array(4, 5, 6), make_array(10, 11, 12),
make_array(4, 9, 8), make_array(7, 8, 9), make_array(10, 11, 12), make_array(1,
8, 7)), 'FixedSizeList(6, List(Int64))'), arrow_cast(make_array(10, 11, 12),
'FixedSizeList(3, Int64)'), 3, arrow_cast(make_array([[11, 12, 13], [14, 15,
16]], [[17, 18, 19], [20, 21, 22]]), 'FixedSizeList(2, List(List(Int64)))'),
arrow_cast(make_array(121, 131, 141), 'FixedSizeList(3, Int64)'))
+;
+
statement ok
CREATE TABLE arrays_values
AS VALUES
@@ -178,6 +209,13 @@ AS VALUES
(make_array(3, 4, 5), 2, make_array(1,2,3,4), make_array(2,5),
make_array(2,4,6), make_array(1,3,5))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_1D
+AS VALUES
+ (arrow_cast(make_array(1, 2, 3), 'FixedSizeList(3, Int64)'), 1,
arrow_cast(make_array(1, 2, 3, 4), 'FixedSizeList(4, Int64)'),
arrow_cast(make_array(1,3), 'FixedSizeList(2, Int64)'),
arrow_cast(make_array(1,3,5), 'FixedSizeList(3, Int64)'),
arrow_cast(make_array(2, 4, 6, 8, 1, 3, 5), 'FixedSizeList(7, Int64)')),
+ (arrow_cast(make_array(3, 4, 5), 'FixedSizeList(3, Int64)'), 2,
arrow_cast(make_array(1, 2, 3, 4), 'FixedSizeList(4, Int64)'),
arrow_cast(make_array(2,5), 'FixedSizeList(2, Int64)'),
arrow_cast(make_array(2,4,6), 'FixedSizeList(3, Int64)'),
arrow_cast(make_array(1, 3, 5, 7, 9, 11, 13), 'FixedSizeList(7, Int64)'))
+;
+
statement ok
CREATE TABLE array_has_table_1D_Float
AS VALUES
@@ -185,6 +223,13 @@ AS VALUES
(make_array(3.0, 4.0, 5.0), 2.0, make_array(1.0,2.0,3.0,4.0),
make_array(2.0,5.0), make_array(2.22, 1.11), make_array(1.11, 3.33))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_1D_Float
+AS VALUES
+ (arrow_cast(make_array(1.0, 2.0, 3.0), 'FixedSizeList(3, Float64)'), 1.0,
arrow_cast(make_array(1.0, 2.0, 3.0, 4.0), 'FixedSizeList(4, Float64)'),
arrow_cast(make_array(1.0,3.0), 'FixedSizeList(2, Float64)'),
arrow_cast(make_array(1.11, 2.22), 'FixedSizeList(2, Float64)'),
arrow_cast(make_array(2.22, 3.33), 'FixedSizeList(2, Float64)')),
+ (arrow_cast(make_array(3.0, 4.0, 5.0), 'FixedSizeList(3, Float64)'), 2.0,
arrow_cast(make_array(1.0, 2.0, 3.0, 4.0), 'FixedSizeList(4, Float64)'),
arrow_cast(make_array(2.0,5.0), 'FixedSizeList(2, Float64)'),
arrow_cast(make_array(2.22, 1.11), 'FixedSizeList(2, Float64)'),
arrow_cast(make_array(1.11, 3.33), 'FixedSizeList(2, Float64)'))
+;
+
statement ok
CREATE TABLE array_has_table_1D_Boolean
AS VALUES
@@ -192,6 +237,13 @@ AS VALUES
(make_array(false, false, false), false, make_array(true, false, true),
make_array(true, true), make_array(true, true), make_array(false,false,true))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_1D_Boolean
+AS VALUES
+ (arrow_cast(make_array(true, true, true), 'FixedSizeList(3, Boolean)'),
false, arrow_cast(make_array(true, true, false, true, false), 'FixedSizeList(5,
Boolean)'), arrow_cast(make_array(true, false, true), 'FixedSizeList(3,
Boolean)'), arrow_cast(make_array(false, true), 'FixedSizeList(2, Boolean)'),
arrow_cast(make_array(true, false, true), 'FixedSizeList(3, Boolean)')),
+ (arrow_cast(make_array(false, false, false), 'FixedSizeList(3, Boolean)'),
false, arrow_cast(make_array(true, false, true, true, false), 'FixedSizeList(5,
Boolean)'), arrow_cast(make_array(true, true, false), 'FixedSizeList(3,
Boolean)'), arrow_cast(make_array(true, true), 'FixedSizeList(2, Boolean)'),
arrow_cast(make_array(false,false,true), 'FixedSizeList(3, Boolean)'))
+;
+
statement ok
CREATE TABLE array_has_table_1D_UTF8
AS VALUES
@@ -199,6 +251,13 @@ AS VALUES
(make_array('a', 'bc', 'def'), 'defg', make_array('datafusion', 'rust',
'arrow'), make_array('datafusion', 'rust', 'arrow', 'python'),
make_array('rust', 'arrow'), make_array('datafusion', 'rust', 'arrow'))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_1D_UTF8
+AS VALUES
+ (arrow_cast(make_array('a', 'bc', 'def'), 'FixedSizeList(3, Utf8)'), 'bc',
arrow_cast(make_array('datafusion', 'rust', 'arrow'), 'FixedSizeList(3,
Utf8)'), arrow_cast(make_array('rust', 'arrow', 'datafusion', 'rust'),
'FixedSizeList(4, Utf8)'), arrow_cast(make_array('rust', 'arrow', 'python'),
'FixedSizeList(3, Utf8)'), arrow_cast(make_array('data', 'fusion', 'rust'),
'FixedSizeList(3, Utf8)')),
+ (arrow_cast(make_array('a', 'bc', 'def'), 'FixedSizeList(3, Utf8)'), 'defg',
arrow_cast(make_array('datafusion', 'rust', 'arrow'), 'FixedSizeList(3,
Utf8)'), arrow_cast(make_array('datafusion', 'rust', 'arrow', 'python'),
'FixedSizeList(4, Utf8)'), arrow_cast(make_array('rust', 'arrow', 'python'),
'FixedSizeList(3, Utf8)'), arrow_cast(make_array('datafusion', 'rust',
'arrow'), 'FixedSizeList(3, Utf8)'))
+;
+
statement ok
CREATE TABLE array_has_table_2D
AS VALUES
@@ -206,6 +265,13 @@ AS VALUES
(make_array([3,4], [5]), make_array(5), make_array([1,2,3,4], [5,6,7],
[8,9,10]), make_array([1,2,3], [5,6,7], [8,9,10]))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_2D
+AS VALUES
+ (arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2, List(Int64))'),
arrow_cast(make_array(1,3), 'FixedSizeList(2, Int64)'),
arrow_cast(make_array([1,2,3], [4,5], [6,7]), 'FixedSizeList(3, List(Int64))'),
arrow_cast(make_array([4,5], [6,7], [1,2]), 'FixedSizeList(3, List(Int64))')),
+ (arrow_cast(make_array([3,4], [5]), 'FixedSizeList(2, List(Int64))'),
arrow_cast(make_array(5, 3), 'FixedSizeList(2, Int64)'),
arrow_cast(make_array([1,2,3,4], [5,6,7], [8,9,10]), 'FixedSizeList(3,
List(Int64))'), arrow_cast(make_array([1,2,3], [5,6,7], [8,9,10]),
'FixedSizeList(3, List(Int64))'))
+;
+
statement ok
CREATE TABLE array_has_table_2D_float
AS VALUES
@@ -213,6 +279,13 @@ AS VALUES
(make_array([1.0, 2.0, 3.0], [1.1, 2.2], [3.3]), make_array([1.0], [1.1,
2.2], [3.3]))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_2D_Float
+AS VALUES
+ (arrow_cast(make_array([1.0, 2.0, 3.0], [1.1, 2.2], [3.3]),
'FixedSizeList(3, List(Float64))'), arrow_cast(make_array([1.1, 2.2], [3.3],
[4.4]), 'FixedSizeList(3, List(Float64))')),
+ (arrow_cast(make_array([1.0, 2.0, 3.0], [1.1, 2.2], [3.3]),
'FixedSizeList(3, List(Float64))'), arrow_cast(make_array([1.0], [1.1, 2.2],
[3.3]), 'FixedSizeList(3, List(Float64))'))
+;
+
statement ok
CREATE TABLE array_has_table_3D
AS VALUES
@@ -225,6 +298,18 @@ AS VALUES
(make_array([[1], [2]], [[2], [3]]), make_array([1], [2]))
;
+statement ok
+CREATE TABLE fixed_size_array_has_table_3D
+AS VALUES
+ (arrow_cast(make_array([[1,2]], [[3, 4]]), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1], [2]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1,2]], [[4, 4]]), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1,2], [3, 4]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1,2]], [[4, 4]]), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1,2,3], [1]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1], [2]], []), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([2], [3]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1], [2]], []), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1], [2]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1], [2]], [[2], [3]]), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1], [2]), 'FixedSizeList(2,
List(Int64))')),
+ (arrow_cast(make_array([[1], [2]], [[2], [3]]), 'FixedSizeList(2,
List(List(Int64)))'), arrow_cast(make_array([1], [2]), 'FixedSizeList(2,
List(Int64))'))
+;
+
statement ok
CREATE TABLE array_distinct_table_1D
AS VALUES
@@ -407,6 +492,15 @@ AS SELECT
FROM arrays_values_without_nulls
;
+statement ok
+CREATE TABLE fixed_size_arrays_values_without_nulls
+AS VALUES
+ (arrow_cast(make_array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), 'FixedSizeList(10,
Int64)'), 1, 1, ',', [2,3]),
+ (arrow_cast(make_array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20),
'FixedSizeList(10, Int64)'), 12, 2, '.', [4,5]),
+ (arrow_cast(make_array(21, 22, 23, 24, 25, 26, 27, 28, 29, 30),
'FixedSizeList(10, Int64)'), 23, 3, '-', [6,7]),
+ (arrow_cast(make_array(31, 32, 33, 34, 35, 26, 37, 38, 39, 40),
'FixedSizeList(10, Int64)'), 34, 4, 'ok', [8,9])
+;
+
statement ok
CREATE TABLE arrays_range
AS VALUES
@@ -434,6 +528,15 @@ AS
FROM arrays_with_repeating_elements
;
+statement ok
+CREATE TABLE fixed_arrays_with_repeating_elements
+AS VALUES
+ (arrow_cast(make_array(1, 2, 1, 3, 2, 2, 1, 3, 2, 3), 'FixedSizeList(10,
Int64)'), 2, 4, 3),
+ (arrow_cast(make_array(4, 4, 5, 5, 6, 5, 5, 5, 4, 4), 'FixedSizeList(10,
Int64)'), 4, 7, 2),
+ (arrow_cast(make_array(7, 7, 7, 8, 7, 9, 7, 8, 7, 7), 'FixedSizeList(10,
Int64)'), 7, 10, 5),
+ (arrow_cast(make_array(10, 11, 12, 10, 11, 12, 10, 11, 12, 10),
'FixedSizeList(10, Int64)'), 10, 13, 10)
+;
+
statement ok
CREATE TABLE nested_arrays_with_repeating_elements
AS VALUES
@@ -454,6 +557,15 @@ AS
FROM nested_arrays_with_repeating_elements
;
+statement ok
+CREATE TABLE fixed_size_nested_arrays_with_repeating_elements
+AS VALUES
+ (arrow_cast(make_array([1, 2, 3], [4, 5, 6], [1, 2, 3], [7, 8, 9], [4, 5,
6], [4, 5, 6], [1, 2, 3], [7, 8, 9], [4, 5, 6], [7, 8, 9]), 'FixedSizeList(10,
List(Int64))'), [4, 5, 6], [10, 11, 12], 3),
+ (arrow_cast(make_array([10, 11, 12], [10, 11, 12], [13, 14, 15], [13, 14,
15], [16, 17, 18], [13, 14, 15], [13, 14, 15], [13, 14, 15], [10, 11, 12], [10,
11, 12]), 'FixedSizeList(10, List(Int64))'), [10, 11, 12], [19, 20, 21], 2),
+ (arrow_cast(make_array([19, 20, 21], [19, 20, 21], [19, 20, 21], [22, 23,
24], [19, 20, 21], [25, 26, 27], [19, 20, 21], [22, 23, 24], [19, 20, 21], [19,
20, 21]), 'FixedSizeList(10, List(Int64))'), [19, 20, 21], [28, 29, 30], 5),
+ (arrow_cast(make_array([28, 29, 30], [31, 32, 33], [34, 35, 36], [28, 29,
30], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30]), 'FixedSizeList(10, List(Int64))'), [28, 29, 30], [28, 29, 30], 10)
+;
+
# Array literal
## boolean coercion is not supported
@@ -941,9 +1053,17 @@ from arrays_values_without_nulls;
## array_element (aliases: array_extract, list_extract, list_element)
# array_element error
-query error DataFusion error: Error during planning: The array_element
function can only accept list or largelist as the first argument
+query error DataFusion error: Error during planning: No function matches the
given name and argument types 'array_element\(Int64, Int64\)'. You might need
to add explicit type casts.\n\tCandidate functions:\n\tarray_element\(array,
index\)
select array_element(1, 2);
+# array_element with null
+query I
+select array_element([1, 2], NULL);
+----
+NULL
+
+query error
+select array_element(NULL, 2);
# array_element scalar function #1 (with positive index)
query IT
@@ -956,6 +1076,11 @@ select array_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
2 l
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), 2), array_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 3);
+----
+2 l
+
# array_element scalar function #2 (with positive index; out of bounds)
query IT
select array_element(make_array(1, 2, 3, 4, 5), 7),
array_element(make_array('h', 'e', 'l', 'l', 'o'), 11);
@@ -967,6 +1092,11 @@ select array_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
NULL NULL
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5),
'LargeList(Int64)'), 7), array_element(arrow_cast(make_array('h', 'e', 'l',
'l', 'o'), 'LargeList(Utf8)'), 11);
+----
+NULL NULL
+
# array_element scalar function #3 (with zero)
query IT
select array_element(make_array(1, 2, 3, 4, 5), 0),
array_element(make_array('h', 'e', 'l', 'l', 'o'), 0);
@@ -978,12 +1108,26 @@ select array_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
NULL NULL
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), 0), array_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 0);
+----
+NULL NULL
+
# array_element scalar function #4 (with NULL)
-query error
+query IT
select array_element(make_array(1, 2, 3, 4, 5), NULL),
array_element(make_array('h', 'e', 'l', 'l', 'o'), NULL);
+----
+NULL NULL
-query error
+query IT
select array_element(arrow_cast(make_array(1, 2, 3, 4, 5),
'LargeList(Int64)'), NULL), array_element(arrow_cast(make_array('h', 'e', 'l',
'l', 'o'), 'LargeList(Utf8)'), NULL);
+----
+NULL NULL
+
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), NULL), array_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), NULL);
+----
+NULL NULL
# array_element scalar function #5 (with negative index)
query IT
@@ -996,6 +1140,11 @@ select array_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
4 l
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), -2), array_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), -3);
+----
+4 l
+
# array_element scalar function #6 (with negative index; out of bounds)
query IT
select array_element(make_array(1, 2, 3, 4, 5), -11),
array_element(make_array('h', 'e', 'l', 'l', 'o'), -7);
@@ -1007,6 +1156,11 @@ select array_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
NULL NULL
+query IT
+select array_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), -11), array_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), -7);
+----
+NULL NULL
+
# array_element scalar function #7 (nested array)
query ?
select array_element(make_array(make_array(1, 2, 3, 4, 5), make_array(6, 7, 8,
9, 10)), 1);
@@ -1018,6 +1172,11 @@ select array_element(arrow_cast(make_array(make_array(1,
2, 3, 4, 5), make_array
----
[1, 2, 3, 4, 5]
+query ?
+select array_element(arrow_cast(make_array(make_array(1, 2, 3, 4, 5),
make_array(6, 7, 8, 9, 10)), 'FixedSizeList(2, List(Int64))'), 1);
+----
+[1, 2, 3, 4, 5]
+
# array_extract scalar function #8 (function alias `array_element`)
query IT
select array_extract(make_array(1, 2, 3, 4, 5), 2),
array_extract(make_array('h', 'e', 'l', 'l', 'o'), 3);
@@ -1029,6 +1188,11 @@ select array_extract(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'),
----
2 l
+query IT
+select array_extract(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), 2), array_extract(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 3);
+----
+2 l
+
# list_element scalar function #9 (function alias `array_element`)
query IT
select list_element(make_array(1, 2, 3, 4, 5), 2),
list_element(make_array('h', 'e', 'l', 'l', 'o'), 3);
@@ -1040,6 +1204,11 @@ select list_element(arrow_cast(make_array(1, 2, 3, 4,
5), 'LargeList(Int64)'), 2
----
2 l
+query IT
+select list_element(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), 2), list_element(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 3);
+----
+2 l
+
# list_extract scalar function #10 (function alias `array_element`)
query IT
select list_extract(make_array(1, 2, 3, 4, 5), 2),
list_extract(make_array('h', 'e', 'l', 'l', 'o'), 3);
@@ -1047,7 +1216,12 @@ select list_extract(make_array(1, 2, 3, 4, 5), 2),
list_extract(make_array('h',
2 l
query IT
-select list_extract(arrow_cast(make_array(1, 2, 3, 4, 5), 'LargeList(Int64)'),
2), array_extract(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'LargeList(Utf8)'), 3);
+select list_extract(arrow_cast(make_array(1, 2, 3, 4, 5), 'LargeList(Int64)'),
2), list_extract(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'LargeList(Utf8)'), 3);
+----
+2 l
+
+query IT
+select list_extract(arrow_cast(make_array(1, 2, 3, 4, 5), 'FixedSizeList(5,
Int64)'), 2), list_extract(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 3);
----
2 l
@@ -1074,6 +1248,16 @@ NULL
NULL
55
+query I
+select array_element(column1, column2) from fixed_slices;
+----
+NULL
+12
+NULL
+37
+NULL
+55
+
# array_element with columns and scalars
query II
select array_element(make_array(1, 2, 3, 4, 5), column2),
array_element(column1, 3) from slices;
@@ -1097,6 +1281,16 @@ NULL 23
NULL 43
5 NULL
+query II
+select array_element(make_array(1, 2, 3, 4, 5), column2),
array_element(column1, 3) from fixed_slices;
+----
+1 3
+2 13
+NULL 23
+2 33
+NULL 43
+5 NULL
+
## array_pop_back (aliases: `list_pop_back`)
# array_pop_back scalar function #1
@@ -2354,6 +2548,12 @@ select array_concat(make_array(column3), column1,
column2) from arrays_values_v2
## array_position (aliases: `list_position`, `array_indexof`, `list_indexof`)
+## array_position with NULL (follow PostgreSQL)
+#query I
+#select array_position([1, 2, 3, 4, 5], null), array_position(NULL, 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);
@@ -2488,6 +2688,12 @@ NULL 1 NULL
## array_positions (aliases: `list_positions`)
+# array_position with NULL (follow PostgreSQL)
+query ?
+select array_positions([1, 2, 3, 4, 5], 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);
@@ -2499,6 +2705,11 @@ select array_positions(arrow_cast(['h', 'e', 'l', 'l',
'o'], 'LargeList(Utf8)'),
----
[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]);
@@ -2510,6 +2721,11 @@ select array_positions(arrow_cast(make_array([1, 2, 3],
[2, 1, 3], [1, 5, 6], [2
----
[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);
@@ -2521,6 +2737,13 @@ select list_positions(arrow_cast(['h', 'e', 'l', 'l',
'o'], 'LargeList(Utf8)'),
----
[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;
@@ -2538,6 +2761,14 @@ select array_positions(arrow_cast(column1,
'LargeList(Int64)'), column2) from ar
[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;
@@ -2551,6 +2782,12 @@ select array_positions(arrow_cast(column1,
'LargeList(List(Int64))'), column2) f
[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;
@@ -2568,6 +2805,14 @@ select array_positions(arrow_cast(column1,
'LargeList(Int64)'), 4), array_positi
[] [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;
@@ -2581,6 +2826,12 @@ select array_positions(arrow_cast(column1,
'LargeList(List(Int64))'), make_array
[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] []
+
## array_replace (aliases: `list_replace`)
# array_replace scalar function #1
@@ -3515,6 +3766,13 @@ select array_remove(make_array(1, 2, 2, 1, 1), 2),
array_remove(make_array(1.0,
----
[1, 2, 1, 1] [2.0, 2.0, 1.0, 1.0] [h, e, l, o]
+query ???
+select array_remove(arrow_cast(make_array(1, 2, 2, 1, 1), 'FixedSizeList(5,
Int64)'), 2),
+ array_remove(arrow_cast(make_array(1.0, 2.0, 2.0, 1.0, 1.0),
'FixedSizeList(5, Float64)'), 1.0),
+ array_remove(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 'l');
+----
+[1, 2, 1, 1] [2.0, 2.0, 1.0, 1.0] [h, e, l, o]
+
query ???
select
array_remove(make_array(1, null, 2, 3), 2),
@@ -3523,11 +3781,20 @@ select
----
[1, , 3] [, 2.2, 3.3] [, bc]
-# TODO: https://github.com/apache/arrow-datafusion/issues/7142
-# query
-# select
-# array_remove(make_array(1, null, 2), null),
-# array_remove(make_array(1, null, 2, null), null);
+query ???
+select
+ array_remove(arrow_cast(make_array(1, null, 2, 3), 'FixedSizeList(4,
Int64)'), 2),
+ array_remove(arrow_cast(make_array(1.1, null, 2.2, 3.3), 'FixedSizeList(4,
Float64)'), 1.1),
+ array_remove(arrow_cast(make_array('a', null, 'bc'), 'FixedSizeList(3,
Utf8)'), 'a');
+----
+[1, , 3] [, 2.2, 3.3] [, bc]
+
+query ??
+select
+ array_remove(make_array(1, null, 2), null),
+ array_remove(make_array(1, null, 2, null), null);
+----
+[1, 2] [1, 2, ]
# array_remove scalar function #2 (element is list)
query ??
@@ -3535,12 +3802,24 @@ select array_remove(make_array([1, 2, 3], [4, 5, 6],
[5, 5, 5], [4, 5, 6], [7, 8
----
[[1, 2, 3], [5, 5, 5], [4, 5, 6], [7, 8, 9]] [[1, 3, 2], [2, 3, 4], [5, 3, 1],
[1, 3, 2]]
+query ??
+select array_remove(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4,
5, 6], [7, 8, 9]), 'FixedSizeList(5, List(Int64))'), [4, 5, 6]),
+ array_remove(arrow_cast(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4], [5,
3, 1], [1, 3, 2]), 'FixedSizeList(5, List(Int64))'), [2, 3, 4]);
+----
+[[1, 2, 3], [5, 5, 5], [4, 5, 6], [7, 8, 9]] [[1, 3, 2], [2, 3, 4], [5, 3, 1],
[1, 3, 2]]
+
# list_remove scalar function #3 (function alias `array_remove`)
query ???
select list_remove(make_array(1, 2, 2, 1, 1), 2), list_remove(make_array(1.0,
2.0, 2.0, 1.0, 1.0), 1.0), list_remove(make_array('h', 'e', 'l', 'l', 'o'),
'l');
----
[1, 2, 1, 1] [2.0, 2.0, 1.0, 1.0] [h, e, l, o]
+query ??
+select list_remove(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4,
5, 6], [7, 8, 9]), 'FixedSizeList(5, List(Int64))'), [4, 5, 6]),
+ list_remove(arrow_cast(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4], [5,
3, 1], [1, 3, 2]), 'FixedSizeList(5, List(Int64))'), [2, 3, 4]);
+----
+[[1, 2, 3], [5, 5, 5], [4, 5, 6], [7, 8, 9]] [[1, 3, 2], [2, 3, 4], [5, 3, 1],
[1, 3, 2]]
+
# array_remove scalar function with columns #1
query ?
select array_remove(column1, column2) from arrays_with_repeating_elements;
@@ -3550,6 +3829,14 @@ select array_remove(column1, column2) from
arrays_with_repeating_elements;
[7, 7, 8, 7, 9, 7, 8, 7, 7]
[11, 12, 10, 11, 12, 10, 11, 12, 10]
+query ?
+select array_remove(column1, column2) from
fixed_arrays_with_repeating_elements;
+----
+[1, 1, 3, 2, 2, 1, 3, 2, 3]
+[4, 5, 5, 6, 5, 5, 5, 4, 4]
+[7, 7, 8, 7, 9, 7, 8, 7, 7]
+[11, 12, 10, 11, 12, 10, 11, 12, 10]
+
# array_remove scalar function with columns #2 (element is list)
query ?
select array_remove(column1, column2) from
nested_arrays_with_repeating_elements;
@@ -3559,6 +3846,14 @@ select array_remove(column1, column2) from
nested_arrays_with_repeating_elements
[[19, 20, 21], [19, 20, 21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19,
20, 21], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
[[31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30], [31, 32, 33], [34, 35, 36], [28, 29, 30]]
+query ?
+select array_remove(column1, column2) from
fixed_size_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [1, 2, 3], [7, 8, 9], [4, 5, 6], [4, 5, 6], [1, 2, 3], [7, 8, 9],
[4, 5, 6], [7, 8, 9]]
+[[10, 11, 12], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13,
14, 15], [13, 14, 15], [10, 11, 12], [10, 11, 12]]
+[[19, 20, 21], [19, 20, 21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19,
20, 21], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
+[[31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30], [31, 32, 33], [34, 35, 36], [28, 29, 30]]
+
# array_remove scalar function with columns and scalars #1
query ??
select array_remove(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8), column2),
array_remove(column1, 1) from arrays_with_repeating_elements;
@@ -3568,9 +3863,27 @@ select array_remove(make_array(1, 2, 2, 4, 5, 4, 4, 7,
7, 10, 7, 8), column2), a
[1, 2, 2, 4, 5, 4, 4, 7, 10, 7, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7]
[1, 2, 2, 4, 5, 4, 4, 7, 7, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+query ??
+select array_remove(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8), column2),
array_remove(column1, 1) from fixed_arrays_with_repeating_elements;
+----
+[1, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8] [2, 1, 3, 2, 2, 1, 3, 2, 3]
+[1, 2, 2, 5, 4, 4, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 7, 10, 7, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7]
+[1, 2, 2, 4, 5, 4, 4, 7, 7, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+
# array_remove scalar function with columns and scalars #2 (element is list)
query ??
-select array_remove(make_array([1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12],
[13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20, 21], [19, 20, 21], [28, 29,
30], [19, 20, 21], [22, 23, 24]), column2), array_remove(column1, make_array(1,
2, 3)) from nested_arrays_with_repeating_elements;
+select array_remove(make_array([1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12],
[13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20, 21], [19, 20, 21], [28, 29,
30], [19, 20, 21], [22, 23, 24]), column2),
+ array_remove(column1, make_array(1, 2, 3)) from
nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12], [10, 11, 12],
[19, 20, 21], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[4, 5,
6], [1, 2, 3], [7, 8, 9], [4, 5, 6], [4, 5, 6], [1, 2, 3], [7, 8, 9], [4, 5,
6], [7, 8, 9]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [13, 14, 15], [10, 11, 12], [10, 11, 12],
[19, 20, 21], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[10, 11,
12], [10, 11, 12], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13,
14, 15], [13, 14, 15], [10, 11, 12], [10, 11, 12]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[19, 20,
21], [19, 20, 21], [19, 20, 21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19,
20, 21], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [19, 20, 21], [19, 20, 21], [19, 20, 21], [22, 23, 24]] [[28, 29,
30], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30], [31, 32, 33], [34, 35, 36], [28, 29, 30]]
+
+query ??
+select array_remove(make_array([1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12],
[13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20, 21], [19, 20, 21], [28, 29,
30], [19, 20, 21], [22, 23, 24]), column2),
+ array_remove(column1, make_array(1, 2, 3)) from
fixed_size_nested_arrays_with_repeating_elements;
----
[[1, 2, 3], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12], [10, 11, 12],
[19, 20, 21], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[4, 5,
6], [1, 2, 3], [7, 8, 9], [4, 5, 6], [4, 5, 6], [1, 2, 3], [7, 8, 9], [4, 5,
6], [7, 8, 9]]
[[1, 2, 3], [4, 5, 6], [4, 5, 6], [13, 14, 15], [10, 11, 12], [10, 11, 12],
[19, 20, 21], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[10, 11,
12], [10, 11, 12], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13,
14, 15], [13, 14, 15], [10, 11, 12], [10, 11, 12]]
@@ -3635,24 +3948,47 @@ select array_remove_n(make_array([1, 2, 3], [4, 5, 6],
[4, 5, 6], [10, 11, 12],
## array_remove_all (aliases: `list_removes`)
+# array_remove_all with NULL elements
+query ?
+select array_remove_all(make_array(1, 2, 2, 1, 1), NULL);
+----
+[1, 2, 2, 1, 1]
+
# array_remove_all scalar function #1
query ???
select array_remove_all(make_array(1, 2, 2, 1, 1), 2),
array_remove_all(make_array(1.0, 2.0, 2.0, 1.0, 1.0), 1.0),
array_remove_all(make_array('h', 'e', 'l', 'l', 'o'), 'l');
----
[1, 1, 1] [2.0, 2.0] [h, e, o]
+query ???
+select array_remove_all(arrow_cast(make_array(1, 2, 2, 1, 1),
'FixedSizeList(5, Int64)'), 2), array_remove_all(arrow_cast(make_array(1.0,
2.0, 2.0, 1.0, 1.0), 'FixedSizeList(5, Float64)'), 1.0),
array_remove_all(arrow_cast(make_array('h', 'e', 'l', 'l', 'o'),
'FixedSizeList(5, Utf8)'), 'l');
+----
+[1, 1, 1] [2.0, 2.0] [h, e, o]
+
# array_remove_all scalar function #2 (element is list)
query ??
select array_remove_all(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6],
[7, 8, 9]), [4, 5, 6]), array_remove_all(make_array([1, 3, 2], [2, 3, 4], [2,
3, 4], [5, 3, 1], [1, 3, 2]), [2, 3, 4]);
----
[[1, 2, 3], [5, 5, 5], [7, 8, 9]] [[1, 3, 2], [5, 3, 1], [1, 3, 2]]
+query ??
+select array_remove_all(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5],
[4, 5, 6], [7, 8, 9]), 'FixedSizeList(5, List(Int64))'), [4, 5, 6]),
+ array_remove_all(arrow_cast(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4],
[5, 3, 1], [1, 3, 2]), 'FixedSizeList(5, List(Int64))'), [2, 3, 4]);
+----
+[[1, 2, 3], [5, 5, 5], [7, 8, 9]] [[1, 3, 2], [5, 3, 1], [1, 3, 2]]
+
# list_remove_all scalar function #3 (function alias `array_remove_all`)
query ???
select list_remove_all(make_array(1, 2, 2, 1, 1), 2),
list_remove_all(make_array(1.0, 2.0, 2.0, 1.0, 1.0), 1.0),
list_remove_all(make_array('h', 'e', 'l', 'l', 'o'), 'l');
----
[1, 1, 1] [2.0, 2.0] [h, e, o]
+query ??
+select list_remove_all(arrow_cast(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5],
[4, 5, 6], [7, 8, 9]), 'FixedSizeList(5, List(Int64))'), [4, 5, 6]),
+ list_remove_all(arrow_cast(make_array([1, 3, 2], [2, 3, 4], [2, 3, 4],
[5, 3, 1], [1, 3, 2]), 'FixedSizeList(5, List(Int64))'), [2, 3, 4]);
+----
+[[1, 2, 3], [5, 5, 5], [7, 8, 9]] [[1, 3, 2], [5, 3, 1], [1, 3, 2]]
+
# array_remove_all scalar function with columns #1
query ?
select array_remove_all(column1, column2) from arrays_with_repeating_elements;
@@ -3662,6 +3998,14 @@ select array_remove_all(column1, column2) from
arrays_with_repeating_elements;
[8, 9, 8]
[11, 12, 11, 12, 11, 12]
+query ?
+select array_remove_all(column1, column2) from
fixed_arrays_with_repeating_elements;
+----
+[1, 1, 3, 1, 3, 3]
+[5, 5, 6, 5, 5, 5]
+[8, 9, 8]
+[11, 12, 11, 12, 11, 12]
+
# array_remove_all scalar function with columns #2 (element is list)
query ?
select array_remove_all(column1, column2) from
nested_arrays_with_repeating_elements;
@@ -3671,6 +4015,14 @@ select array_remove_all(column1, column2) from
nested_arrays_with_repeating_elem
[[22, 23, 24], [25, 26, 27], [22, 23, 24]]
[[31, 32, 33], [34, 35, 36], [31, 32, 33], [34, 35, 36], [31, 32, 33], [34,
35, 36]]
+query ?
+select array_remove_all(column1, column2) from
fixed_size_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [1, 2, 3], [7, 8, 9], [1, 2, 3], [7, 8, 9], [7, 8, 9]]
+[[13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13, 14, 15], [13,
14, 15]]
+[[22, 23, 24], [25, 26, 27], [22, 23, 24]]
+[[31, 32, 33], [34, 35, 36], [31, 32, 33], [34, 35, 36], [31, 32, 33], [34,
35, 36]]
+
# array_remove_all scalar function with columns and scalars #1
query ??
select array_remove_all(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
column2), array_remove_all(column1, 1) from arrays_with_repeating_elements;
@@ -3680,6 +4032,14 @@ select array_remove_all(make_array(1, 2, 2, 4, 5, 4, 4,
7, 7, 10, 7, 8), column2
[1, 2, 2, 4, 5, 4, 4, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7]
[1, 2, 2, 4, 5, 4, 4, 7, 7, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+query ??
+select array_remove_all(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
column2), array_remove_all(column1, 1) from
fixed_arrays_with_repeating_elements;
+----
+[1, 4, 5, 4, 4, 7, 7, 10, 7, 8] [2, 3, 2, 2, 3, 2, 3]
+[1, 2, 2, 5, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7]
+[1, 2, 2, 4, 5, 4, 4, 7, 7, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+
# array_remove_all scalar function with columns and scalars #2 (element is
list)
query ??
select array_remove_all(make_array([1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11,
12], [13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20, 21], [19, 20, 21], [28,
29, 30], [19, 20, 21], [22, 23, 24]), column2), array_remove_all(column1,
make_array(1, 2, 3)) from nested_arrays_with_repeating_elements;
@@ -3689,6 +4049,15 @@ select array_remove_all(make_array([1, 2, 3], [4, 5, 6],
[4, 5, 6], [10, 11, 12]
[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [28, 29, 30], [22, 23, 24]] [[19, 20, 21], [19, 20, 21], [19, 20,
21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19, 20, 21], [22, 23, 24], [19,
20, 21], [19, 20, 21]]
[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [19, 20, 21], [19, 20, 21], [19, 20, 21], [22, 23, 24]] [[28, 29,
30], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30], [31, 32, 33], [34, 35, 36], [28, 29, 30]]
+query ??
+select array_remove_all(make_array([1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11,
12], [13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20, 21], [19, 20, 21], [28,
29, 30], [19, 20, 21], [22, 23, 24]), column2),
+ array_remove_all(column1, make_array(1, 2, 3)) from
fixed_size_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [13, 14, 15], [10, 11, 12], [10, 11, 12], [19, 20,
21], [19, 20, 21], [28, 29, 30], [19, 20, 21], [22, 23, 24]] [[4, 5, 6], [7, 8,
9], [4, 5, 6], [4, 5, 6], [7, 8, 9], [4, 5, 6], [7, 8, 9]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [13, 14, 15], [19, 20, 21], [19, 20, 21],
[28, 29, 30], [19, 20, 21], [22, 23, 24]] [[10, 11, 12], [10, 11, 12], [13, 14,
15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13, 14, 15], [13, 14, 15], [10,
11, 12], [10, 11, 12]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [28, 29, 30], [22, 23, 24]] [[19, 20, 21], [19, 20, 21], [19, 20,
21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19, 20, 21], [22, 23, 24], [19,
20, 21], [19, 20, 21]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [19, 20, 21], [19, 20, 21], [19, 20, 21], [22, 23, 24]] [[28, 29,
30], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28,
29, 30], [31, 32, 33], [34, 35, 36], [28, 29, 30]]
+
## trim_array (deprecated)
## array_length (aliases: `list_length`)
@@ -4009,6 +4378,21 @@ NULL 1 1
## array_has/array_has_all/array_has_any
+query BB
+select array_has([], null),
+ array_has([1, 2, 3], null);
+----
+false false
+
+#TODO: array_has_all and array_has_any cannot handle NULL
+#query BBBB
+#select array_has_any([], null),
+# array_has_any([1, 2, 3], null),
+# array_has_all([], null),
+# array_has_all([1, 2, 3], null);
+#----
+#false false false false
+
query BBBBBBBBBBBB
select array_has(make_array(1,2), 1),
array_has(make_array(1,2,NULL), 1),
@@ -4043,6 +4427,23 @@ select array_has(arrow_cast(make_array(1,2),
'LargeList(Int64)'), 1),
----
true true true true true false true false true false true false
+query BBBBBBBBBBBB
+select array_has(arrow_cast(make_array(1,2), 'FixedSizeList(2, Int64)'), 1),
+ array_has(arrow_cast(make_array(1,2,NULL), 'FixedSizeList(3, Int64)'),
1),
+ array_has(arrow_cast(make_array([2,3], [3,4]), 'FixedSizeList(2,
List(Int64))'), make_array(2,3)),
+ array_has(arrow_cast(make_array([[1], [2,3]], [[4,5], [6]]),
'FixedSizeList(2, List(List(Int64)))'), make_array([1], [2,3])),
+ array_has(arrow_cast(make_array([[1], [2,3]], [[4,5], [6]]),
'FixedSizeList(2, List(List(Int64)))'), make_array([4,5], [6])),
+ array_has(arrow_cast(make_array([[1], [2,3]], [[4,5], [6]]),
'FixedSizeList(2, List(List(Int64)))'), make_array([1])),
+ array_has(arrow_cast(make_array([[[1]]]), 'FixedSizeList(1,
List(List(List(Int64))))'), make_array([[1]])),
+ array_has(arrow_cast(make_array([[[1]]], [[[1], [2]]]),
'FixedSizeList(2, List(List(List(Int64))))'), make_array([[2]])),
+ array_has(arrow_cast(make_array([[[1]]], [[[1], [2]]]),
'FixedSizeList(2, List(List(List(Int64))))'), make_array([[1], [2]])),
+ list_has(arrow_cast(make_array(1,2,3), 'FixedSizeList(3, Int64)'), 4),
+ array_contains(arrow_cast(make_array(1,2,3), 'FixedSizeList(3,
Int64)'), 3),
+ list_contains(arrow_cast(make_array(1,2,3), 'FixedSizeList(3, Int64)'),
0)
+;
+----
+true true true true true false true false true false true false
+
query BBB
select array_has(column1, column2),
array_has_all(column3, column4),
@@ -4061,6 +4462,22 @@ from array_has_table_1D;
true true true
false false false
+query B
+select array_has(column1, column2)
+from fixed_size_array_has_table_1D;
+----
+true
+false
+
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query BB
+#select array_has_all(column3, column4),
+# array_has_any(column5, column6)
+#from fixed_size_array_has_table_1D;
+#----
+#true true
+#false false
+
query BBB
select array_has(column1, column2),
array_has_all(column3, column4),
@@ -4079,6 +4496,22 @@ from array_has_table_1D_Float;
true true false
false false true
+query B
+select array_has(column1, column2)
+from fixed_size_array_has_table_1D_Float;
+----
+true
+false
+
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query BB
+#select array_has_all(column3, column4),
+# array_has_any(column5, column6)
+#from fixed_size_array_has_table_1D_Float;
+#----
+#true true
+#false true
+
query BBB
select array_has(column1, column2),
array_has_all(column3, column4),
@@ -4097,6 +4530,22 @@ from array_has_table_1D_Boolean;
false true true
true true true
+query B
+select array_has(column1, column2)
+from fixed_size_array_has_table_1D_Boolean;
+----
+false
+true
+
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query BB
+#select array_has_all(column3, column4),
+# array_has_any(column5, column6)
+#from fixed_size_array_has_table_1D_Boolean;
+#----
+#true true
+#true true
+
query BBB
select array_has(column1, column2),
array_has_all(column3, column4),
@@ -4115,6 +4564,13 @@ from array_has_table_1D_UTF8;
true true false
false false true
+query B
+select array_has(column1, column2)
+from fixed_size_array_has_table_1D_UTF8;
+----
+true
+false
+
query BB
select array_has(column1, column2),
array_has_all(column3, column4)
@@ -4131,6 +4587,21 @@ from array_has_table_2D;
false true
true false
+query B
+select array_has(arrow_cast(column1, 'LargeList(List(Int64))'), column2)
+from fixed_size_array_has_table_2D;
+----
+false
+false
+
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query B
+#select array_has_all(arrow_cast(column3, 'LargeList(List(Int64))'),
arrow_cast(column4, 'LargeList(List(Int64))'))
+#from fixed_size_array_has_table_2D;
+#----
+#true
+#false
+
query B
select array_has_all(column1, column2)
from array_has_table_2D_float;
@@ -4145,6 +4616,14 @@ from array_has_table_2D_float;
true
false
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query B
+#select array_has_all(column1, column2)
+#from fixed_size_array_has_table_2D_float;
+#----
+#false
+#false
+
query B
select array_has(column1, column2) from array_has_table_3D;
----
@@ -4167,6 +4646,17 @@ true
false
true
+query B
+select array_has(column1, column2) from fixed_size_array_has_table_3D;
+----
+false
+false
+false
+false
+true
+true
+true
+
query BBBB
select array_has(column1, make_array(5, 6)),
array_has(column1, make_array(7, NULL)),
@@ -4195,6 +4685,21 @@ false true false false
false false false false
false false false false
+query BBBB
+select array_has(column1, make_array(5, 6)),
+ array_has(column1, make_array(7, NULL)),
+ array_has(column2, 5.5),
+ array_has(column3, 'o')
+from fixed_size_arrays;
+----
+false false false true
+true false true false
+true false false true
+false true false false
+false true false false
+false false false false
+false false false false
+
query BBBBBBBBBBBBB
select array_has_all(make_array(1,2,3), make_array(1,3)),
array_has_all(make_array(1,2,3), make_array(1,4)),
@@ -4231,23 +4736,24 @@ select array_has_all(arrow_cast(make_array(1,2,3),
'LargeList(Int64)'), arrow_ca
----
true false true false false false true true false false true false true
-query BBBBBBBBBBBBB
-select array_has_all(arrow_cast(make_array(1,2,3), 'LargeList(Int64)'),
arrow_cast(make_array(1,3), 'LargeList(Int64)')),
- array_has_all(arrow_cast(make_array(1,2,3),'LargeList(Int64)'),
arrow_cast(make_array(1,4), 'LargeList(Int64)')),
- array_has_all(arrow_cast(make_array([1,2], [3,4]),
'LargeList(List(Int64))'), arrow_cast(make_array([1,2]),
'LargeList(List(Int64))')),
- array_has_all(arrow_cast(make_array([1,2], [3,4]),
'LargeList(List(Int64))'), arrow_cast(make_array([1,3]),
'LargeList(List(Int64))')),
- array_has_all(arrow_cast(make_array([1,2], [3,4]),
'LargeList(List(Int64))'), arrow_cast(make_array([1,2], [3,4], [5,6]),
'LargeList(List(Int64))')),
- array_has_all(arrow_cast(make_array([[1,2,3]]),
'LargeList(List(List(Int64)))'), arrow_cast(make_array([[1]]),
'LargeList(List(List(Int64)))')),
- array_has_all(arrow_cast(make_array([[1,2,3]]),
'LargeList(List(List(Int64)))'), arrow_cast(make_array([[1,2,3]]),
'LargeList(List(List(Int64)))')),
- array_has_any(arrow_cast(make_array(1,2,3),'LargeList(Int64)'),
arrow_cast(make_array(1,10,100), 'LargeList(Int64)')),
- array_has_any(arrow_cast(make_array(1,2,3),'LargeList(Int64)'),
arrow_cast(make_array(10,100),'LargeList(Int64)')),
- array_has_any(arrow_cast(make_array([1,2], [3,4]),
'LargeList(List(Int64))'), arrow_cast(make_array([1,10], [10,4]),
'LargeList(List(Int64))')),
- array_has_any(arrow_cast(make_array([1,2], [3,4]),
'LargeList(List(Int64))'), arrow_cast(make_array([10,20], [3,4]),
'LargeList(List(Int64))')),
- array_has_any(arrow_cast(make_array([[1,2,3]]),
'LargeList(List(List(Int64)))'), arrow_cast(make_array([[1,2,3], [4,5,6]]),
'LargeList(List(List(Int64)))')),
- array_has_any(arrow_cast(make_array([[1,2,3]]),
'LargeList(List(List(Int64)))'), arrow_cast(make_array([[1,2,3]], [[4,5,6]]),
'LargeList(List(List(Int64)))'))
-;
-----
-true false true false false false true true false false true false true
+#TODO: array_has_all and array_has_any cannot handle FixedSizeList
+#query BBBBBBBBBBBBB
+#select array_has_all(arrow_cast(make_array(1,2,3), 'FixedSizeList(3,
Int64)'), arrow_cast(make_array(1, 3), 'FixedSizeList(2, Int64)')),
+# array_has_all(arrow_cast(make_array(1,2,3),'FixedSizeList(3, Int64)'),
arrow_cast(make_array(1, 4), 'FixedSizeList(2, Int64)')),
+# array_has_all(arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array([1,2]), 'FixedSizeList(1, List(Int64))')),
+# array_has_all(arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array([1,3]), 'FixedSizeList(1, List(Int64))')),
+# array_has_all(arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array([1,2], [3,4], [5,6]), 'FixedSizeList(3,
List(Int64))')),
+# array_has_all(arrow_cast(make_array([[1,2,3]]), 'FixedSizeList(1,
List(List(Int64)))'), arrow_cast(make_array([[1]]), 'FixedSizeList(1,
List(List(Int64)))')),
+# array_has_all(arrow_cast(make_array([[1,2,3]]), 'FixedSizeList(1,
List(List(Int64)))'), arrow_cast(make_array([[1,2,3]]), 'FixedSizeList(1,
List(List(Int64)))')),
+# array_has_any(arrow_cast(make_array(1,2,3),'FixedSizeList(3, Int64)'),
arrow_cast(make_array(1,10,100), 'FixedSizeList(3, Int64)')),
+# array_has_any(arrow_cast(make_array(1,2,3),'FixedSizeList(3, Int64)'),
arrow_cast(make_array(10, 100),'FixedSizeList(2, Int64)')),
+# array_has_any(arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array([1,10], [10,4]), 'FixedSizeList(2,
List(Int64))')),
+# array_has_any(arrow_cast(make_array([1,2], [3,4]), 'FixedSizeList(2,
List(Int64))'), arrow_cast(make_array([10,20], [3,4]), 'FixedSizeList(2,
List(Int64))')),
+# array_has_any(arrow_cast(make_array([[1,2,3]]), 'FixedSizeList(1,
List(List(Int64)))'), arrow_cast(make_array([[1,2,3], [4,5,6]]),
'FixedSizeList(1, List(List(Int64)))')),
+# array_has_any(arrow_cast(make_array([[1,2,3]]), 'FixedSizeList(1,
List(List(Int64)))'), arrow_cast(make_array([[1,2,3]], [[4,5,6]]),
'FixedSizeList(2, List(List(Int64)))'))
+#;
+#----
+#true false true false false false true true false false true false true
## array_distinct
@@ -5102,15 +5608,24 @@ drop table nested_arrays;
statement ok
drop table large_nested_arrays;
+statement ok
+drop table fixed_size_nested_arrays;
+
statement ok
drop table arrays;
statement ok
drop table large_arrays;
+statement ok
+drop table fixed_size_arrays;
+
statement ok
drop table slices;
+statement ok
+drop table fixed_slices;
+
statement ok
drop table arrayspop;
@@ -5187,7 +5702,25 @@ statement ok
drop table large_array_intersect_table_3D;
statement ok
-drop table arrays_values_without_nulls;
+drop table fixed_size_array_has_table_1D;
+
+statement ok
+drop table fixed_size_array_has_table_1D_Float;
+
+statement ok
+drop table fixed_size_array_has_table_1D_Boolean;
+
+statement ok
+drop table fixed_size_array_has_table_1D_UTF8;
+
+statement ok
+drop table fixed_size_array_has_table_2D;
+
+statement ok
+drop table fixed_size_array_has_table_2D_float;
+
+statement ok
+drop table fixed_size_array_has_table_3D;
statement ok
drop table arrays_range;
@@ -5198,11 +5731,26 @@ drop table arrays_with_repeating_elements;
statement ok
drop table large_arrays_with_repeating_elements;
+statement ok
+drop table fixed_arrays_with_repeating_elements;
+
statement ok
drop table nested_arrays_with_repeating_elements;
statement ok
drop table large_nested_arrays_with_repeating_elements;
+statement ok
+drop table fixed_size_nested_arrays_with_repeating_elements;
+
statement ok
drop table flatten_table;
+
+statement ok
+drop table arrays_values_without_nulls;
+
+statement ok
+drop table large_arrays_values_without_nulls;
+
+statement ok
+drop table fixed_size_arrays_values_without_nulls;
diff --git a/datafusion/sqllogictest/test_files/arrow_typeof.slt
b/datafusion/sqllogictest/test_files/arrow_typeof.slt
index 8b3bd7eac9..8e2a091423 100644
--- a/datafusion/sqllogictest/test_files/arrow_typeof.slt
+++ b/datafusion/sqllogictest/test_files/arrow_typeof.slt
@@ -421,4 +421,4 @@ FixedSizeList(Field { name: "item", data_type: Int64,
nullable: true, dict_id: 0
query ?
select arrow_cast([1, 2, 3], 'FixedSizeList(3, Int64)');
----
-[1, 2, 3]
+[1, 2, 3]
\ No newline at end of file
