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 ef34af8877 support LargeList in array_remove (#8595)
ef34af8877 is described below
commit ef34af8877d25cd84006806b355127179e2d4c89
Author: Alex Huang <[email protected]>
AuthorDate: Fri Dec 22 13:36:01 2023 +0100
support LargeList in array_remove (#8595)
---
datafusion/physical-expr/src/array_expressions.rs | 114 +++++++--
datafusion/sqllogictest/test_files/array.slt | 269 ++++++++++++++++++++++
2 files changed, 365 insertions(+), 18 deletions(-)
diff --git a/datafusion/physical-expr/src/array_expressions.rs
b/datafusion/physical-expr/src/array_expressions.rs
index bdab65cab9..4dfc157e53 100644
--- a/datafusion/physical-expr/src/array_expressions.rs
+++ b/datafusion/physical-expr/src/array_expressions.rs
@@ -100,6 +100,14 @@ fn compare_element_to_list(
row_index: usize,
eq: bool,
) -> Result<BooleanArray> {
+ if list_array_row.data_type() != element_array.data_type() {
+ return exec_err!(
+ "compare_element_to_list received incompatible types: '{:?}' and
'{:?}'.",
+ list_array_row.data_type(),
+ element_array.data_type()
+ );
+ }
+
let indices = UInt32Array::from(vec![row_index as u32]);
let element_array_row = arrow::compute::take(element_array, &indices,
None)?;
@@ -126,6 +134,26 @@ fn compare_element_to_list(
})
.collect::<BooleanArray>()
}
+ DataType::LargeList(_) => {
+ // compare each element of the from array
+ let element_array_row_inner =
+ as_large_list_array(&element_array_row)?.value(0);
+ let list_array_row_inner = as_large_list_array(list_array_row)?;
+
+ list_array_row_inner
+ .iter()
+ // compare element by element the current row of list_array
+ .map(|row| {
+ row.map(|row| {
+ if eq {
+ row.eq(&element_array_row_inner)
+ } else {
+ row.ne(&element_array_row_inner)
+ }
+ })
+ })
+ .collect::<BooleanArray>()
+ }
_ => {
let element_arr = Scalar::new(element_array_row);
// use not_distinct so we can compare NULL
@@ -1511,14 +1539,14 @@ pub fn array_remove_n(args: &[ArrayRef]) ->
Result<ArrayRef> {
/// [4, 5, 6, 5], 5, 20, 2 ==> [4, 20, 6, 20] (both 5s are replaced)
/// )
/// ```
-fn general_replace(
- list_array: &ListArray,
+fn general_replace<O: OffsetSizeTrait>(
+ list_array: &GenericListArray<O>,
from_array: &ArrayRef,
to_array: &ArrayRef,
arr_n: Vec<i64>,
) -> Result<ArrayRef> {
// Build up the offsets for the final output array
- let mut offsets: Vec<i32> = vec![0];
+ let mut offsets: Vec<O> = vec![O::usize_as(0)];
let values = list_array.values();
let original_data = values.to_data();
let to_data = to_array.to_data();
@@ -1540,8 +1568,8 @@ fn general_replace(
continue;
}
- let start = offset_window[0] as usize;
- let end = offset_window[1] as usize;
+ let start = offset_window[0];
+ let end = offset_window[1];
let list_array_row = list_array.value(row_index);
@@ -1550,43 +1578,56 @@ fn general_replace(
let eq_array =
compare_element_to_list(&list_array_row, &from_array, row_index,
true)?;
- let original_idx = 0;
- let replace_idx = 1;
+ let original_idx = O::usize_as(0);
+ let replace_idx = O::usize_as(1);
let n = arr_n[row_index];
let mut counter = 0;
// All elements are false, no need to replace, just copy original data
if eq_array.false_count() == eq_array.len() {
- mutable.extend(original_idx, start, end);
- offsets.push(offsets[row_index] + (end - start) as i32);
+ mutable.extend(
+ original_idx.to_usize().unwrap(),
+ start.to_usize().unwrap(),
+ end.to_usize().unwrap(),
+ );
+ offsets.push(offsets[row_index] + (end - start));
valid.append(true);
continue;
}
for (i, to_replace) in eq_array.iter().enumerate() {
+ let i = O::usize_as(i);
if let Some(true) = to_replace {
- mutable.extend(replace_idx, row_index, row_index + 1);
+ mutable.extend(replace_idx.to_usize().unwrap(), row_index,
row_index + 1);
counter += 1;
if counter == n {
// copy original data for any matches past n
- mutable.extend(original_idx, start + i + 1, end);
+ mutable.extend(
+ original_idx.to_usize().unwrap(),
+ (start + i).to_usize().unwrap() + 1,
+ end.to_usize().unwrap(),
+ );
break;
}
} else {
// copy original data for false / null matches
- mutable.extend(original_idx, start + i, start + i + 1);
+ mutable.extend(
+ original_idx.to_usize().unwrap(),
+ (start + i).to_usize().unwrap(),
+ (start + i).to_usize().unwrap() + 1,
+ );
}
}
- offsets.push(offsets[row_index] + (end - start) as i32);
+ offsets.push(offsets[row_index] + (end - start));
valid.append(true);
}
let data = mutable.freeze();
- Ok(Arc::new(ListArray::try_new(
+ Ok(Arc::new(GenericListArray::<O>::try_new(
Arc::new(Field::new("item", list_array.value_type(), true)),
- OffsetBuffer::new(offsets.into()),
+ OffsetBuffer::<O>::new(offsets.into()),
arrow_array::make_array(data),
Some(NullBuffer::new(valid.finish())),
)?))
@@ -1595,19 +1636,56 @@ fn general_replace(
pub fn array_replace(args: &[ArrayRef]) -> Result<ArrayRef> {
// replace at most one occurence for each element
let arr_n = vec![1; args[0].len()];
- general_replace(as_list_array(&args[0])?, &args[1], &args[2], arr_n)
+ let array = &args[0];
+ match array.data_type() {
+ DataType::List(_) => {
+ let list_array = array.as_list::<i32>();
+ general_replace::<i32>(list_array, &args[1], &args[2], arr_n)
+ }
+ DataType::LargeList(_) => {
+ let list_array = array.as_list::<i64>();
+ general_replace::<i64>(list_array, &args[1], &args[2], arr_n)
+ }
+ array_type => exec_err!("array_replace does not support type
'{array_type:?}'."),
+ }
}
pub fn array_replace_n(args: &[ArrayRef]) -> Result<ArrayRef> {
// replace the specified number of occurences
let arr_n = as_int64_array(&args[3])?.values().to_vec();
- general_replace(as_list_array(&args[0])?, &args[1], &args[2], arr_n)
+ let array = &args[0];
+ match array.data_type() {
+ DataType::List(_) => {
+ let list_array = array.as_list::<i32>();
+ general_replace::<i32>(list_array, &args[1], &args[2], arr_n)
+ }
+ DataType::LargeList(_) => {
+ let list_array = array.as_list::<i64>();
+ general_replace::<i64>(list_array, &args[1], &args[2], arr_n)
+ }
+ array_type => {
+ exec_err!("array_replace_n does not support type
'{array_type:?}'.")
+ }
+ }
}
pub fn array_replace_all(args: &[ArrayRef]) -> Result<ArrayRef> {
// replace all occurrences (up to "i64::MAX")
let arr_n = vec![i64::MAX; args[0].len()];
- general_replace(as_list_array(&args[0])?, &args[1], &args[2], arr_n)
+ let array = &args[0];
+ match array.data_type() {
+ DataType::List(_) => {
+ let list_array = array.as_list::<i32>();
+ general_replace::<i32>(list_array, &args[1], &args[2], arr_n)
+ }
+ DataType::LargeList(_) => {
+ let list_array = array.as_list::<i64>();
+ general_replace::<i64>(list_array, &args[1], &args[2], arr_n)
+ }
+ array_type => {
+ exec_err!("array_replace_all does not support type
'{array_type:?}'.")
+ }
+ }
}
macro_rules! to_string {
diff --git a/datafusion/sqllogictest/test_files/array.slt
b/datafusion/sqllogictest/test_files/array.slt
index ca33f08de0..283f2d67b7 100644
--- a/datafusion/sqllogictest/test_files/array.slt
+++ b/datafusion/sqllogictest/test_files/array.slt
@@ -298,6 +298,17 @@ AS VALUES
(make_array(10, 11, 12, 10, 11, 12, 10, 11, 12, 10), 10, 13, 10)
;
+statement ok
+CREATE TABLE large_arrays_with_repeating_elements
+AS
+ SELECT
+ arrow_cast(column1, 'LargeList(Int64)') AS column1,
+ column2,
+ column3,
+ column4
+ FROM arrays_with_repeating_elements
+;
+
statement ok
CREATE TABLE nested_arrays_with_repeating_elements
AS VALUES
@@ -307,6 +318,17 @@ AS VALUES
(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]),
[28, 29, 30], [37, 38, 39], 10)
;
+statement ok
+CREATE TABLE large_nested_arrays_with_repeating_elements
+AS
+ SELECT
+ arrow_cast(column1, 'LargeList(List(Int64))') AS column1,
+ column2,
+ column3,
+ column4
+ FROM nested_arrays_with_repeating_elements
+;
+
query error
select [1, true, null]
@@ -2010,6 +2032,14 @@ select
----
[1, 3, 3, 4] [1, 0, 4, 5, 4, 6, 7] [1, 2, 3]
+query ???
+select
+ array_replace(arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2, 3),
+ array_replace(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0),
+ array_replace(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0);
+----
+[1, 3, 3, 4] [1, 0, 4, 5, 4, 6, 7] [1, 2, 3]
+
# array_replace scalar function #2 (element is list)
query ??
select
@@ -2026,6 +2056,21 @@ select
----
[[1, 2, 3], [1, 1, 1], [5, 5, 5], [4, 5, 6], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[2, 3, 4], [5, 3, 1], [1, 3, 2]]
+query ??
+select
+ array_replace(
+ 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],
+ [1, 1, 1]
+ ),
+ array_replace(
+ 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],
+ [3, 1, 4]
+ );
+----
+[[1, 2, 3], [1, 1, 1], [5, 5, 5], [4, 5, 6], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[2, 3, 4], [5, 3, 1], [1, 3, 2]]
+
# list_replace scalar function #3 (function alias `list_replace`)
query ???
select list_replace(
@@ -2035,6 +2080,14 @@ select list_replace(
----
[1, 3, 3, 4] [1, 0, 4, 5, 4, 6, 7] [1, 2, 3]
+query ???
+select list_replace(
+ arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2, 3),
+ list_replace(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0),
+ list_replace(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0);
+----
+[1, 3, 3, 4] [1, 0, 4, 5, 4, 6, 7] [1, 2, 3]
+
# array_replace scalar function with columns #1
query ?
select array_replace(column1, column2, column3) from
arrays_with_repeating_elements;
@@ -2044,6 +2097,14 @@ select array_replace(column1, column2, column3) from
arrays_with_repeating_eleme
[10, 7, 7, 8, 7, 9, 7, 8, 7, 7]
[13, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+query ?
+select array_replace(column1, column2, column3) from
large_arrays_with_repeating_elements;
+----
+[1, 4, 1, 3, 2, 2, 1, 3, 2, 3]
+[7, 4, 5, 5, 6, 5, 5, 5, 4, 4]
+[10, 7, 7, 8, 7, 9, 7, 8, 7, 7]
+[13, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+
# array_replace scalar function with columns #2 (element is list)
query ?
select array_replace(column1, column2, column3) from
nested_arrays_with_repeating_elements;
@@ -2053,6 +2114,14 @@ select array_replace(column1, column2, column3) from
nested_arrays_with_repeatin
[[28, 29, 30], [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]]
[[37, 38, 39], [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_replace(column1, column2, column3) from
large_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [1, 2, 3], [7, 8, 9], [4, 5, 6], [4, 5, 6], [1, 2,
3], [7, 8, 9], [4, 5, 6], [7, 8, 9]]
+[[19, 20, 21], [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]]
+[[28, 29, 30], [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]]
+[[37, 38, 39], [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_replace scalar function with columns and scalars #1
query ???
select
@@ -2066,6 +2135,18 @@ from arrays_with_repeating_elements;
[1, 2, 2, 4, 5, 4, 4, 10, 7, 10, 7, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 7,
7, 8, 7, 9, 7, 8, 7, 7]
[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+query ???
+select
+ array_replace(arrow_cast(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
'LargeList(Int64)'), column2, column3),
+ array_replace(column1, 1, column3),
+ array_replace(column1, column2, 4)
+from large_arrays_with_repeating_elements;
+----
+[1, 4, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8] [4, 2, 1, 3, 2, 2, 1, 3, 2, 3] [1, 4, 1,
3, 2, 2, 1, 3, 2, 3]
+[1, 2, 2, 7, 5, 4, 4, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 4, 4] [4, 4, 5,
5, 6, 5, 5, 5, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 10, 7, 10, 7, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 7,
7, 8, 7, 9, 7, 8, 7, 7]
+[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 10, 11, 12, 10, 11, 12, 10]
+
# array_replace scalar function with columns and scalars #2 (element is list)
query ???
select
@@ -2084,6 +2165,23 @@ from nested_arrays_with_repeating_elements;
[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [28, 29, 30], [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]] [[11, 12,
13], [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], [37, 38, 39], [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]] [[11, 12,
13], [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_replace(
+ arrow_cast(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]),'LargeList(List(Int64))'),
+ column2,
+ column3
+ ),
+ array_replace(column1, make_array(1, 2, 3), column3),
+ array_replace(column1, column2, make_array(11, 12, 13))
+from large_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [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]] [[10, 11, 12], [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], [11, 12, 13], [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], [19, 20, 21], [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]] [[11, 12,
13], [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], [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]] [[11, 12,
13], [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], [37, 38, 39], [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]] [[11, 12,
13], [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_replace_n (aliases: `list_replace_n`)
# array_replace_n scalar function #1
@@ -2095,6 +2193,14 @@ select
----
[1, 3, 3, 4] [1, 0, 0, 5, 4, 6, 7] [1, 2, 3]
+query ???
+select
+ array_replace_n(arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2,
3, 2),
+ array_replace_n(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0, 2),
+ array_replace_n(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0,
3);
+----
+[1, 3, 3, 4] [1, 0, 0, 5, 4, 6, 7] [1, 2, 3]
+
# array_replace_n scalar function #2 (element is list)
query ??
select
@@ -2113,6 +2219,23 @@ select
----
[[1, 2, 3], [1, 1, 1], [5, 5, 5], [1, 1, 1], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[3, 1, 4], [5, 3, 1], [1, 3, 2]]
+query ??
+select
+ array_replace_n(
+ 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],
+ [1, 1, 1],
+ 2
+ ),
+ array_replace_n(
+ 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],
+ [3, 1, 4],
+ 2
+ );
+----
+[[1, 2, 3], [1, 1, 1], [5, 5, 5], [1, 1, 1], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[3, 1, 4], [5, 3, 1], [1, 3, 2]]
+
# list_replace_n scalar function #3 (function alias `array_replace_n`)
query ???
select
@@ -2122,6 +2245,14 @@ select
----
[1, 3, 3, 4] [1, 0, 0, 5, 4, 6, 7] [1, 2, 3]
+query ???
+select
+ list_replace_n(arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2, 3,
2),
+ list_replace_n(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0, 2),
+ list_replace_n(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0, 3);
+----
+[1, 3, 3, 4] [1, 0, 0, 5, 4, 6, 7] [1, 2, 3]
+
# array_replace_n scalar function with columns #1
query ?
select
@@ -2133,6 +2264,16 @@ from arrays_with_repeating_elements;
[10, 10, 10, 8, 10, 9, 10, 8, 7, 7]
[13, 11, 12, 13, 11, 12, 13, 11, 12, 13]
+query ?
+select
+ array_replace_n(column1, column2, column3, column4)
+from large_arrays_with_repeating_elements;
+----
+[1, 4, 1, 3, 4, 4, 1, 3, 2, 3]
+[7, 7, 5, 5, 6, 5, 5, 5, 4, 4]
+[10, 10, 10, 8, 10, 9, 10, 8, 7, 7]
+[13, 11, 12, 13, 11, 12, 13, 11, 12, 13]
+
# array_replace_n scalar function with columns #2 (element is list)
query ?
select
@@ -2144,6 +2285,17 @@ from nested_arrays_with_repeating_elements;
[[28, 29, 30], [28, 29, 30], [28, 29, 30], [22, 23, 24], [28, 29, 30], [25,
26, 27], [28, 29, 30], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
[[37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39], [31, 32, 33], [34,
35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39]]
+query ?
+select
+ array_replace_n(column1, column2, column3, column4)
+from large_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [1, 2, 3], [7, 8, 9], [10, 11, 12], [10, 11, 12],
[1, 2, 3], [7, 8, 9], [4, 5, 6], [7, 8, 9]]
+[[19, 20, 21], [19, 20, 21], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13,
14, 15], [13, 14, 15], [13, 14, 15], [10, 11, 12], [10, 11, 12]]
+[[28, 29, 30], [28, 29, 30], [28, 29, 30], [22, 23, 24], [28, 29, 30], [25,
26, 27], [28, 29, 30], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
+[[37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39], [31, 32, 33], [34,
35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39]]
+
+
# array_replace_n scalar function with columns and scalars #1
query ????
select
@@ -2158,6 +2310,19 @@ from arrays_with_repeating_elements;
[1, 2, 2, 4, 5, 4, 4, 10, 10, 10, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 4,
4, 8, 4, 9, 4, 8, 7, 7] [10, 10, 7, 8, 7, 9, 7, 8, 7, 7]
[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 4, 11, 12, 4, 11, 12, 4] [13, 11, 12, 13, 11, 12, 10, 11, 12, 10]
+query ????
+select
+ array_replace_n(arrow_cast(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
'LargeList(Int64)'), column2, column3, column4),
+ array_replace_n(column1, 1, column3, column4),
+ array_replace_n(column1, column2, 4, column4),
+ array_replace_n(column1, column2, column3, 2)
+from large_arrays_with_repeating_elements;
+----
+[1, 4, 4, 4, 5, 4, 4, 7, 7, 10, 7, 8] [4, 2, 4, 3, 2, 2, 4, 3, 2, 3] [1, 4, 1,
3, 4, 4, 1, 3, 2, 3] [1, 4, 1, 3, 4, 2, 1, 3, 2, 3]
+[1, 2, 2, 7, 5, 7, 4, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 4, 4] [4, 4, 5,
5, 6, 5, 5, 5, 4, 4] [7, 7, 5, 5, 6, 5, 5, 5, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 10, 10, 10, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 4,
4, 8, 4, 9, 4, 8, 7, 7] [10, 10, 7, 8, 7, 9, 7, 8, 7, 7]
+[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 4, 11, 12, 4, 11, 12, 4] [13, 11, 12, 13, 11, 12, 10, 11, 12, 10]
+
# array_replace_n scalar function with columns and scalars #2 (element is list)
query ????
select
@@ -2178,6 +2343,25 @@ from nested_arrays_with_repeating_elements;
[[7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [4, 5,
6]] [[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]] [[11, 12, 13],
[11, 12, 13], [11, 12, 13], [22, 23, 24], [11, 12, 13], [25, 26, 27], [11, 12,
13], [22, 23, 24], [19, 20, 21], [19, 20, 21]] [[28, 29, 30], [28, 29, 30],
[19, 20, 21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19, 20, 21], [22, 23,
24], [19, 20, 21], [1 [...]
[[7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [4, 5,
6]] [[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]] [[11, 12, 13],
[31, 32, 33], [34, 35, 36], [11, 12, 13], [31, 32, 33], [34, 35, 36], [11, 12,
13], [31, 32, 33], [34, 35, 36], [11, 12, 13]] [[37, 38, 39], [31, 32, 33],
[34, 35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32,
33], [34, 35, 36], [2 [...]
+query ????
+select
+ array_replace_n(
+ arrow_cast(make_array(
+ [7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [4,
5, 6]), 'LargeList(List(Int64))'),
+ column2,
+ column3,
+ column4
+ ),
+ array_replace_n(column1, make_array(1, 2, 3), column3, column4),
+ array_replace_n(column1, column2, make_array(11, 12, 13), column4),
+ array_replace_n(column1, column2, column3, 2)
+from large_nested_arrays_with_repeating_elements;
+----
+[[7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [10, 11,
12]] [[10, 11, 12], [4, 5, 6], [10, 11, 12], [7, 8, 9], [4, 5, 6], [4, 5, 6],
[10, 11, 12], [7, 8, 9], [4, 5, 6], [7, 8, 9]] [[1, 2, 3], [11, 12, 13], [1, 2,
3], [7, 8, 9], [11, 12, 13], [11, 12, 13], [1, 2, 3], [7, 8, 9], [4, 5, 6], [7,
8, 9]] [[1, 2, 3], [10, 11, 12], [1, 2, 3], [7, 8, 9], [10, 11, 12], [4, 5, 6],
[1, 2, 3], [7, 8, 9], [4, 5, 6], [7, 8, 9]]
+[[7, 8, 9], [2, 1, 3], [1, 5, 6], [19, 20, 21], [2, 1, 3], [7, 8, 9], [4, 5,
6]] [[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]] [[11, 12, 13],
[11, 12, 13], [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],
[13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13, 14, 15], [13, 14,
15], [10, 11, 12], [1 [...]
+[[7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [4, 5,
6]] [[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]] [[11, 12, 13],
[11, 12, 13], [11, 12, 13], [22, 23, 24], [11, 12, 13], [25, 26, 27], [11, 12,
13], [22, 23, 24], [19, 20, 21], [19, 20, 21]] [[28, 29, 30], [28, 29, 30],
[19, 20, 21], [22, 23, 24], [19, 20, 21], [25, 26, 27], [19, 20, 21], [22, 23,
24], [19, 20, 21], [1 [...]
+[[7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11, 12], [2, 1, 3], [7, 8, 9], [4, 5,
6]] [[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]] [[11, 12, 13],
[31, 32, 33], [34, 35, 36], [11, 12, 13], [31, 32, 33], [34, 35, 36], [11, 12,
13], [31, 32, 33], [34, 35, 36], [11, 12, 13]] [[37, 38, 39], [31, 32, 33],
[34, 35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32,
33], [34, 35, 36], [2 [...]
+
## array_replace_all (aliases: `list_replace_all`)
# array_replace_all scalar function #1
@@ -2189,6 +2373,14 @@ select
----
[1, 3, 3, 4] [1, 0, 0, 5, 0, 6, 7] [1, 2, 3]
+query ???
+select
+ array_replace_all(arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2,
3),
+ array_replace_all(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0),
+ array_replace_all(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0);
+----
+[1, 3, 3, 4] [1, 0, 0, 5, 0, 6, 7] [1, 2, 3]
+
# array_replace_all scalar function #2 (element is list)
query ??
select
@@ -2205,6 +2397,21 @@ select
----
[[1, 2, 3], [1, 1, 1], [5, 5, 5], [1, 1, 1], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[3, 1, 4], [5, 3, 1], [1, 3, 2]]
+query ??
+select
+ array_replace_all(
+ 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],
+ [1, 1, 1]
+ ),
+ array_replace_all(
+ 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],
+ [3, 1, 4]
+ );
+----
+[[1, 2, 3], [1, 1, 1], [5, 5, 5], [1, 1, 1], [7, 8, 9]] [[1, 3, 2], [3, 1, 4],
[3, 1, 4], [5, 3, 1], [1, 3, 2]]
+
# list_replace_all scalar function #3 (function alias `array_replace_all`)
query ???
select
@@ -2214,6 +2421,14 @@ select
----
[1, 3, 3, 4] [1, 0, 0, 5, 0, 6, 7] [1, 2, 3]
+query ???
+select
+ list_replace_all(arrow_cast(make_array(1, 2, 3, 4), 'LargeList(Int64)'), 2,
3),
+ list_replace_all(arrow_cast(make_array(1, 4, 4, 5, 4, 6, 7),
'LargeList(Int64)'), 4, 0),
+ list_replace_all(arrow_cast(make_array(1, 2, 3), 'LargeList(Int64)'), 4, 0);
+----
+[1, 3, 3, 4] [1, 0, 0, 5, 0, 6, 7] [1, 2, 3]
+
# array_replace_all scalar function with columns #1
query ?
select
@@ -2225,6 +2440,16 @@ from arrays_with_repeating_elements;
[10, 10, 10, 8, 10, 9, 10, 8, 10, 10]
[13, 11, 12, 13, 11, 12, 13, 11, 12, 13]
+query ?
+select
+ array_replace_all(column1, column2, column3)
+from large_arrays_with_repeating_elements;
+----
+[1, 4, 1, 3, 4, 4, 1, 3, 4, 3]
+[7, 7, 5, 5, 6, 5, 5, 5, 7, 7]
+[10, 10, 10, 8, 10, 9, 10, 8, 10, 10]
+[13, 11, 12, 13, 11, 12, 13, 11, 12, 13]
+
# array_replace_all scalar function with columns #2 (element is list)
query ?
select
@@ -2236,6 +2461,16 @@ from nested_arrays_with_repeating_elements;
[[28, 29, 30], [28, 29, 30], [28, 29, 30], [22, 23, 24], [28, 29, 30], [25,
26, 27], [28, 29, 30], [22, 23, 24], [28, 29, 30], [28, 29, 30]]
[[37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39], [31, 32, 33], [34,
35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39]]
+query ?
+select
+ array_replace_all(column1, column2, column3)
+from large_nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [1, 2, 3], [7, 8, 9], [10, 11, 12], [10, 11, 12],
[1, 2, 3], [7, 8, 9], [10, 11, 12], [7, 8, 9]]
+[[19, 20, 21], [19, 20, 21], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13,
14, 15], [13, 14, 15], [13, 14, 15], [19, 20, 21], [19, 20, 21]]
+[[28, 29, 30], [28, 29, 30], [28, 29, 30], [22, 23, 24], [28, 29, 30], [25,
26, 27], [28, 29, 30], [22, 23, 24], [28, 29, 30], [28, 29, 30]]
+[[37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39], [31, 32, 33], [34,
35, 36], [37, 38, 39], [31, 32, 33], [34, 35, 36], [37, 38, 39]]
+
# array_replace_all scalar function with columns and scalars #1
query ???
select
@@ -2249,6 +2484,18 @@ from arrays_with_repeating_elements;
[1, 2, 2, 4, 5, 4, 4, 10, 10, 10, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 4,
4, 8, 4, 9, 4, 8, 4, 4]
[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 4, 11, 12, 4, 11, 12, 4]
+query ???
+select
+ array_replace_all(arrow_cast(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7,
8), 'LargeList(Int64)'), column2, column3),
+ array_replace_all(column1, 1, column3),
+ array_replace_all(column1, column2, 4)
+from large_arrays_with_repeating_elements;
+----
+[1, 4, 4, 4, 5, 4, 4, 7, 7, 10, 7, 8] [4, 2, 4, 3, 2, 2, 4, 3, 2, 3] [1, 4, 1,
3, 4, 4, 1, 3, 4, 3]
+[1, 2, 2, 7, 5, 7, 7, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 4, 4] [4, 4, 5,
5, 6, 5, 5, 5, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 10, 10, 10, 10, 8] [7, 7, 7, 8, 7, 9, 7, 8, 7, 7] [4, 4,
4, 8, 4, 9, 4, 8, 4, 4]
+[1, 2, 2, 4, 5, 4, 4, 7, 7, 13, 7, 8] [10, 11, 12, 10, 11, 12, 10, 11, 12, 10]
[4, 11, 12, 4, 11, 12, 4, 11, 12, 4]
+
# array_replace_all scalar function with columns and scalars #2 (element is
list)
query ???
select
@@ -2266,6 +2513,22 @@ from nested_arrays_with_repeating_elements;
[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [28, 29, 30], [28, 29, 30], [28, 29, 30], [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]] [[11, 12,
13], [11, 12, 13], [11, 12, 13], [22, 23, 24], [11, 12, 13], [25, 26, 27], [11,
12, 13], [22, 23, 24], [11, 12, 13], [11, 12, 13]]
[[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], [37, 38, 39], [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]] [[11, 12,
13], [31, 32, 33], [34, 35, 36], [11, 12, 13], [31, 32, 33], [34, 35, 36], [11,
12, 13], [31, 32, 33], [34, 35, 36], [11, 12, 13]]
+query ???
+select
+ array_replace_all(
+ arrow_cast(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]), 'LargeList(List(Int64))'),
+ column2,
+ column3
+ ),
+ array_replace_all(column1, make_array(1, 2, 3), column3),
+ array_replace_all(column1, column2, make_array(11, 12, 13))
+from nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [10, 11, 12], [10, 11, 12], [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]] [[10, 11, 12], [4, 5, 6], [10, 11, 12], [7, 8, 9], [4, 5, 6], [4, 5,
6], [10, 11, 12], [7, 8, 9], [4, 5, 6], [7, 8, 9]] [[1, 2, 3], [11, 12, 13],
[1, 2, 3], [7, 8, 9], [11, 12, 13], [11, 12, 13], [1, 2, 3], [7, 8, 9], [11,
12, 13], [7, 8, 9]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [19, 20, 21], [13, 14, 15], [19, 20, 21],
[19, 20, 21], [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]] [[11, 12,
13], [11, 12, 13], [13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13,
14, 15], [13, 14, 15], [11, 12, 13], [11, 12, 13]]
+[[1, 2, 3], [4, 5, 6], [4, 5, 6], [10, 11, 12], [13, 14, 15], [10, 11, 12],
[10, 11, 12], [28, 29, 30], [28, 29, 30], [28, 29, 30], [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]] [[11, 12,
13], [11, 12, 13], [11, 12, 13], [22, 23, 24], [11, 12, 13], [25, 26, 27], [11,
12, 13], [22, 23, 24], [11, 12, 13], [11, 12, 13]]
+[[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], [37, 38, 39], [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]] [[11, 12,
13], [31, 32, 33], [34, 35, 36], [11, 12, 13], [31, 32, 33], [34, 35, 36], [11,
12, 13], [31, 32, 33], [34, 35, 36], [11, 12, 13]]
+
# array_replace with null handling
statement ok
@@ -3870,8 +4133,14 @@ drop table arrays_range;
statement ok
drop table arrays_with_repeating_elements;
+statement ok
+drop table large_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 flatten_table;
