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 bd1d82fe24 feat: support SQL array replacement and removement
functions (#7057)
bd1d82fe24 is described below
commit bd1d82fe24566ad98715a26c777eb225b187cefd
Author: Igor Izvekov <[email protected]>
AuthorDate: Thu Jul 27 16:07:49 2023 +0300
feat: support SQL array replacement and removement functions (#7057)
* feat: sqllogictests for array_remove, array_replace, array_removes and
array_replaces
* test: unit tests for array_replace, array_replaces, array_remove,
array_removes
* feat: array_replace, array_replaces, array_remove, array_removes
* docs: array_replace, array_replaces, array_remove, array_removes
* feat: proto
* fix: use prettier
* fix: cargo fmt
* feat: optional argument for array_replace and array_remove
* fix: proto
* fix: expr_fn tests
* feat: array_replace_n and array_remove_n
---
.../core/tests/sqllogictests/test_files/array.slt | 386 ++++++++-
datafusion/expr/src/built_in_function.rs | 73 +-
datafusion/expr/src/expr_fn.rs | 32 +-
datafusion/physical-expr/src/array_expressions.rs | 886 +++++++++++++++++----
datafusion/physical-expr/src/functions.rs | 20 +-
datafusion/proto/proto/datafusion.proto | 4 +
datafusion/proto/src/generated/pbjson.rs | 12 +
datafusion/proto/src/generated/prost.rs | 12 +
datafusion/proto/src/logical_plan/from_proto.rs | 29 +-
datafusion/proto/src/logical_plan/to_proto.rs | 4 +
docs/source/user-guide/expressions.md | 44 +-
docs/source/user-guide/sql/scalar_functions.md | 164 +++-
12 files changed, 1436 insertions(+), 230 deletions(-)
diff --git a/datafusion/core/tests/sqllogictests/test_files/array.slt
b/datafusion/core/tests/sqllogictests/test_files/array.slt
index 9e117b6d05..2a1add0b13 100644
--- a/datafusion/core/tests/sqllogictests/test_files/array.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/array.slt
@@ -58,8 +58,8 @@ AS VALUES
statement ok
CREATE TABLE nested_arrays
AS VALUES
- (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)), make_array(7,
8, 9), 2, make_array([[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]])),
- (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)),
make_array(10, 11, 12), 3, make_array([[11, 12, 13], [14, 15, 16]], [[17, 18,
19], [20, 21, 22]]))
+ (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)), make_array(7,
8, 9), 2, make_array([[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]),
make_array(11, 12, 13)),
+ (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)),
make_array(10, 11, 12), 3, make_array([[11, 12, 13], [14, 15, 16]], [[17, 18,
19], [20, 21, 22]]), make_array(121, 131, 141))
;
statement ok
@@ -149,6 +149,24 @@ AS VALUES
(make_array(31, 32, 33, 34, 35, 26, 37, 38, 39, 40), 34, 4, 'ok')
;
+statement ok
+CREATE TABLE arrays_with_repeating_elements
+AS VALUES
+ (make_array(1, 2, 1, 3, 2, 2, 1, 3, 2, 3), 2, 4, 3),
+ (make_array(4, 4, 5, 5, 6, 5, 5, 5, 4, 4), 4, 7, 2),
+ (make_array(7, 7, 7, 8, 7, 9, 7, 8, 7, 7), 7, 10, 5),
+ (make_array(10, 11, 12, 10, 11, 12, 10, 11, 12, 10), 10, 13, 10)
+;
+
+statement ok
+CREATE TABLE nested_arrays_with_repeating_elements
+AS VALUES
+ (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]), [4, 5, 6], [10, 11, 12], 3),
+ (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]),
[10, 11, 12], [19, 20, 21], 2),
+ (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]),
[19, 20, 21], [28, 29, 30], 5),
+ (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)
+;
+
# arrays table
query ???
select column1, column2, column3 from arrays;
@@ -162,11 +180,11 @@ NULL [13.3, 14.4, 15.5] [a, m, e, t]
[[15, 16], [, 18]] [16.6, 17.7, 18.8] NULL
# nested_arrays table
-query ??I?
-select column1, column2, column3, column4 from nested_arrays;
+query ??I??
+select column1, column2, column3, column4, column5 from nested_arrays;
----
-[[1, 2, 3], [2, 9, 1], [7, 8, 9], [1, 2, 3], [1, 7, 4], [4, 5, 6]] [7, 8, 9] 2
[[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]]
-[[4, 5, 6], [10, 11, 12], [4, 9, 8], [7, 8, 9], [10, 11, 12], [1, 8, 7]] [10,
11, 12] 3 [[[11, 12, 13], [14, 15, 16]], [[17, 18, 19], [20, 21, 22]]]
+[[1, 2, 3], [2, 9, 1], [7, 8, 9], [1, 2, 3], [1, 7, 4], [4, 5, 6]] [7, 8, 9] 2
[[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]] [11, 12, 13]
+[[4, 5, 6], [10, 11, 12], [4, 9, 8], [7, 8, 9], [10, 11, 12], [1, 8, 7]] [10,
11, 12] 3 [[[11, 12, 13], [14, 15, 16]], [[17, 18, 19], [20, 21, 22]]] [121,
131, 141]
# values table
query IIIRT
@@ -214,6 +232,24 @@ select column1, column2, column3 from
arrays_values_without_nulls;
[21, 22, 23, 24, 25, 26, 27, 28, 29, 30] 23 3
[31, 32, 33, 34, 35, 26, 37, 38, 39, 40] 34 4
+# arrays_with_repeating_elements table
+query ?III
+select column1, column2, column3, column4 from arrays_with_repeating_elements;
+----
+[1, 2, 1, 3, 2, 2, 1, 3, 2, 3] 2 4 3
+[4, 4, 5, 5, 6, 5, 5, 5, 4, 4] 4 7 2
+[7, 7, 7, 8, 7, 9, 7, 8, 7, 7] 7 10 5
+[10, 11, 12, 10, 11, 12, 10, 11, 12, 10] 10 13 10
+
+# nested_arrays_with_repeating_elements table
+query ???I
+select column1, column2, column3, column4 from
nested_arrays_with_repeating_elements;
+----
+[[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]] [4, 5, 6] [10, 11, 12] 3
+[[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]] [10, 11, 12]
[19, 20, 21] 2
+[[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]] [19, 20, 21]
[28, 29, 30] 5
+[[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
+
### Array function tests
@@ -907,14 +943,174 @@ select array_positions(column1, make_array(4, 5, 6)),
array_positions(make_array
[6] []
[1] []
-## array_replace
+## array_replace (aliases: `list_replace`)
-# array_replace scalar function
+# array_replace scalar function #1
query ???
select array_replace(make_array(1, 2, 3, 4), 2, 3),
array_replace(make_array(1, 4, 4, 5, 4, 6, 7), 4, 0),
array_replace(make_array(1, 2, 3), 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 array_replace(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6],
[7, 8, 9]), [4, 5, 6], [1, 1, 1]), array_replace(make_array([1, 3, 2], [2, 3,
4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), [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(make_array(1, 2, 3, 4), 2, 3), list_replace(make_array(1,
4, 4, 5, 4, 6, 7), 4, 0), list_replace(make_array(1, 2, 3), 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;
+----
+[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;
+----
+[[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 array_replace(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8), column2,
column3), array_replace(column1, 1, column3), array_replace(column1, column2,
4) from 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 array_replace(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, column3), array_replace(column1,
make_array(1, 2, 3), column3), array_replace(column1, column2, make_array(11,
12, 13)) from 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
+query ???
+select array_replace_n(make_array(1, 2, 3, 4), 2, 3, 2),
array_replace_n(make_array(1, 4, 4, 5, 4, 6, 7), 4, 0, 2),
array_replace_n(make_array(1, 2, 3), 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 array_replace_n(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6],
[7, 8, 9]), [4, 5, 6], [1, 1, 1], 2), array_replace_n(make_array([1, 3, 2], [2,
3, 4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), [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 list_replace_n(make_array(1, 2, 3, 4), 2, 3, 2),
list_replace_n(make_array(1, 4, 4, 5, 4, 6, 7), 4, 0, 2),
list_replace_n(make_array(1, 2, 3), 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 array_replace_n(column1, column2, column3, column4) from
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 array_replace_n(column1, column2, column3, column4) from
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 array_replace_n(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
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 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 array_replace_n(make_array([7, 8, 9], [2, 1, 3], [1, 5, 6], [10, 11,
12], [2, 1, 3], [7, 8, 9], [4, 5, 6]), 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
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
+query ???
+select array_replace_all(make_array(1, 2, 3, 4), 2, 3),
array_replace_all(make_array(1, 4, 4, 5, 4, 6, 7), 4, 0),
array_replace_all(make_array(1, 2, 3), 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 array_replace_all(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5,
6], [7, 8, 9]), [4, 5, 6], [1, 1, 1]), array_replace_all(make_array([1, 3, 2],
[2, 3, 4], [2, 3, 4], [5, 3, 1], [1, 3, 2]), [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 list_replace_all(make_array(1, 2, 3, 4), 2, 3),
list_replace_all(make_array(1, 4, 4, 5, 4, 6, 7), 4, 0),
list_replace_all(make_array(1, 2, 3), 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 array_replace_all(column1, column2, column3) from
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 array_replace_all(column1, column2, column3) from
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 array_replace_all(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
column2, column3), array_replace_all(column1, 1, column3),
array_replace_all(column1, column2, 4) from 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 array_replace_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, 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_to_string (aliases: `list_to_string`, `array_join`, `list_join`)
# array_to_string scalar function #1
@@ -1047,6 +1243,174 @@ NULL 3 4
4 NULL 1
4 3 NULL
+## array_remove (aliases: `list_remove`)
+
+# array_remove scalar function #1
+query ???
+select array_remove(make_array(1, 2, 2, 1, 1), 2),
array_remove(make_array(1.0, 2.0, 2.0, 1.0, 1.0), 1.0),
array_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]
+
+# array_remove scalar function #2 (element is list)
+query ??
+select array_remove(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6], [7,
8, 9]), [4, 5, 6]), array_remove(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], [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]
+
+# array_remove scalar function with columns #1
+query ?
+select array_remove(column1, column2) from 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;
+----
+[[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;
+----
+[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;
+----
+[[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]]
+
+## array_remove_n (aliases: `list_remove_n`)
+
+# array_remove_n scalar function #1
+query ???
+select array_remove_n(make_array(1, 2, 2, 1, 1), 2, 2),
array_remove_n(make_array(1.0, 2.0, 2.0, 1.0, 1.0), 1.0, 2),
array_remove_n(make_array('h', 'e', 'l', 'l', 'o'), 'l', 3);
+----
+[1, 1, 1] [2.0, 2.0, 1.0] [h, e, o]
+
+# array_remove_n scalar function #2 (element is list)
+query ??
+select array_remove_n(make_array([1, 2, 3], [4, 5, 6], [5, 5, 5], [4, 5, 6],
[7, 8, 9]), [4, 5, 6], 2), array_remove_n(make_array([1, 3, 2], [2, 3, 4], [2,
3, 4], [5, 3, 1], [1, 3, 2]), [2, 3, 4], 2);
+----
+[[1, 2, 3], [5, 5, 5], [7, 8, 9]] [[1, 3, 2], [5, 3, 1], [1, 3, 2]]
+
+# list_remove_n scalar function #3 (function alias `array_remove_n`)
+query ???
+select list_remove_n(make_array(1, 2, 2, 1, 1), 2, 2),
list_remove_n(make_array(1.0, 2.0, 2.0, 1.0, 1.0), 1.0, 2),
list_remove_n(make_array('h', 'e', 'l', 'l', 'o'), 'l', 3);
+----
+[1, 1, 1] [2.0, 2.0, 1.0] [h, e, o]
+
+# array_remove_n scalar function with columns #1
+query ?
+select array_remove_n(column1, column2, column4) from
arrays_with_repeating_elements;
+----
+[1, 1, 3, 1, 3, 2, 3]
+[5, 5, 6, 5, 5, 5, 4, 4]
+[8, 9, 8, 7, 7]
+[11, 12, 11, 12, 11, 12]
+
+# array_remove_n scalar function with columns #2 (element is list)
+query ?
+select array_remove_n(column1, column2, column4) from
nested_arrays_with_repeating_elements;
+----
+[[1, 2, 3], [1, 2, 3], [7, 8, 9], [1, 2, 3], [7, 8, 9], [4, 5, 6], [7, 8, 9]]
+[[13, 14, 15], [13, 14, 15], [16, 17, 18], [13, 14, 15], [13, 14, 15], [13,
14, 15], [10, 11, 12], [10, 11, 12]]
+[[22, 23, 24], [25, 26, 27], [22, 23, 24], [19, 20, 21], [19, 20, 21]]
+[[31, 32, 33], [34, 35, 36], [31, 32, 33], [34, 35, 36], [31, 32, 33], [34,
35, 36]]
+
+# array_remove_n scalar function with columns and scalars #1
+query ???
+select array_remove_n(make_array(1, 2, 2, 4, 5, 4, 4, 7, 7, 10, 7, 8),
column2, column4), array_remove_n(column1, 1, column4), array_remove_n(column1,
column2, 2) from arrays_with_repeating_elements;
+----
+[1, 4, 5, 4, 4, 7, 7, 10, 7, 8] [2, 3, 2, 2, 3, 2, 3] [1, 1, 3, 2, 1, 3, 2, 3]
+[1, 2, 2, 5, 4, 7, 7, 10, 7, 8] [4, 4, 5, 5, 6, 5, 5, 5, 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] [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]
[11, 12, 11, 12, 10, 11, 12, 10]
+
+# array_remove_n scalar function with columns and scalars #2 (element is list)
+query ???
+select array_remove_n(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, column4),
array_remove_n(column1, make_array(1, 2, 3), column4), array_remove_n(column1,
column2, 2) from 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], [1, 2,
3], [7, 8, 9], [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], [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]] [[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]] [[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]] [[31, 32, 33], [34, 35, 36],
[31, 32, 33], [34, 35, 36], [28, 29, 30], [31, 32, 33], [34, 35, 36], [28, 29,
30]]
+
+## array_remove_all (aliases: `list_removes`)
+
+# 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]
+
+# 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]]
+
+# 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]
+
+# array_remove_all scalar function with columns #1
+query ?
+select array_remove_all(column1, column2) from 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;
+----
+[[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;
+----
+[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;
+----
+[[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
# trim_array scalar function #1
@@ -1472,3 +1836,9 @@ drop table array_has_table_3D;
statement ok
drop table arrays_values_without_nulls;
+
+statement ok
+drop table arrays_with_repeating_elements;
+
+statement ok
+drop table nested_arrays_with_repeating_elements;
diff --git a/datafusion/expr/src/built_in_function.rs
b/datafusion/expr/src/built_in_function.rs
index 2cb1cf5441..33db0f9eb1 100644
--- a/datafusion/expr/src/built_in_function.rs
+++ b/datafusion/expr/src/built_in_function.rs
@@ -141,8 +141,16 @@ pub enum BuiltinScalarFunction {
ArrayPrepend,
/// array_remove
ArrayRemove,
+ /// array_remove_n
+ ArrayRemoveN,
+ /// array_remove_all
+ ArrayRemoveAll,
/// array_replace
ArrayReplace,
+ /// array_replace_n
+ ArrayReplaceN,
+ /// array_replace_all
+ ArrayReplaceAll,
/// array_to_string
ArrayToString,
/// cardinality
@@ -345,7 +353,11 @@ impl BuiltinScalarFunction {
BuiltinScalarFunction::ArrayPositions => Volatility::Immutable,
BuiltinScalarFunction::ArrayPrepend => Volatility::Immutable,
BuiltinScalarFunction::ArrayRemove => Volatility::Immutable,
+ BuiltinScalarFunction::ArrayRemoveN => Volatility::Immutable,
+ BuiltinScalarFunction::ArrayRemoveAll => Volatility::Immutable,
BuiltinScalarFunction::ArrayReplace => Volatility::Immutable,
+ BuiltinScalarFunction::ArrayReplaceN => Volatility::Immutable,
+ BuiltinScalarFunction::ArrayReplaceAll => Volatility::Immutable,
BuiltinScalarFunction::ArrayToString => Volatility::Immutable,
BuiltinScalarFunction::Cardinality => Volatility::Immutable,
BuiltinScalarFunction::MakeArray => Volatility::Immutable,
@@ -481,16 +493,7 @@ impl BuiltinScalarFunction {
// the return type of the built in function.
// Some built-in functions' return type depends on the incoming type.
match self {
- BuiltinScalarFunction::ArrayAppend => match &input_expr_types[0] {
- List(_) => Ok(List(Arc::new(Field::new(
- "item",
- input_expr_types[1].clone(),
- true,
- )))),
- _ => Err(DataFusionError::Internal(format!(
- "The {self} function can only accept list as the first
argument"
- ))),
- },
+ BuiltinScalarFunction::ArrayAppend =>
Ok(input_expr_types[0].clone()),
BuiltinScalarFunction::ArrayConcat => {
let mut expr_type = Null;
let mut max_dims = 0;
@@ -532,31 +535,13 @@ impl BuiltinScalarFunction {
BuiltinScalarFunction::ArrayPositions => {
Ok(List(Arc::new(Field::new("item", UInt64, true))))
}
- BuiltinScalarFunction::ArrayPrepend => Ok(List(Arc::new(Field::new(
- "item",
- input_expr_types[0].clone(),
- true,
- )))),
- BuiltinScalarFunction::ArrayRemove => match &input_expr_types[0] {
- List(field) => Ok(List(Arc::new(Field::new(
- "item",
- field.data_type().clone(),
- true,
- )))),
- _ => Err(DataFusionError::Internal(format!(
- "The {self} function can only accept list as the first
argument"
- ))),
- },
- BuiltinScalarFunction::ArrayReplace => match &input_expr_types[0] {
- List(field) => Ok(List(Arc::new(Field::new(
- "item",
- field.data_type().clone(),
- true,
- )))),
- _ => Err(DataFusionError::Internal(format!(
- "The {self} function can only accept list as the first
argument"
- ))),
- },
+ BuiltinScalarFunction::ArrayPrepend =>
Ok(input_expr_types[1].clone()),
+ BuiltinScalarFunction::ArrayRemove =>
Ok(input_expr_types[0].clone()),
+ BuiltinScalarFunction::ArrayRemoveN =>
Ok(input_expr_types[0].clone()),
+ BuiltinScalarFunction::ArrayRemoveAll =>
Ok(input_expr_types[0].clone()),
+ BuiltinScalarFunction::ArrayReplace =>
Ok(input_expr_types[0].clone()),
+ BuiltinScalarFunction::ArrayReplaceN =>
Ok(input_expr_types[0].clone()),
+ BuiltinScalarFunction::ArrayReplaceAll =>
Ok(input_expr_types[0].clone()),
BuiltinScalarFunction::ArrayToString => Ok(Utf8),
BuiltinScalarFunction::Cardinality => Ok(UInt64),
BuiltinScalarFunction::MakeArray => match input_expr_types.len() {
@@ -839,8 +824,12 @@ impl BuiltinScalarFunction {
BuiltinScalarFunction::ArrayPositions => Signature::any(2,
self.volatility()),
BuiltinScalarFunction::ArrayPrepend => Signature::any(2,
self.volatility()),
BuiltinScalarFunction::ArrayRemove => Signature::any(2,
self.volatility()),
- BuiltinScalarFunction::ArrayReplace => {
- Signature::variadic_any(self.volatility())
+ BuiltinScalarFunction::ArrayRemoveN => Signature::any(3,
self.volatility()),
+ BuiltinScalarFunction::ArrayRemoveAll => Signature::any(2,
self.volatility()),
+ BuiltinScalarFunction::ArrayReplace => Signature::any(3,
self.volatility()),
+ BuiltinScalarFunction::ArrayReplaceN => Signature::any(4,
self.volatility()),
+ BuiltinScalarFunction::ArrayReplaceAll => {
+ Signature::any(3, self.volatility())
}
BuiltinScalarFunction::ArrayToString => {
Signature::variadic_any(self.volatility())
@@ -1318,8 +1307,14 @@ fn aliases(func: &BuiltinScalarFunction) -> &'static
[&'static str] {
"array_push_front",
"list_push_front",
],
- BuiltinScalarFunction::ArrayRemove => &["array_remove"],
- BuiltinScalarFunction::ArrayReplace => &["array_replace"],
+ BuiltinScalarFunction::ArrayRemove => &["array_remove", "list_remove"],
+ BuiltinScalarFunction::ArrayRemoveN => &["array_remove_n",
"list_remove_n"],
+ BuiltinScalarFunction::ArrayRemoveAll => &["array_remove_all",
"list_remove_all"],
+ BuiltinScalarFunction::ArrayReplace => &["array_replace",
"list_replace"],
+ BuiltinScalarFunction::ArrayReplaceN => &["array_replace_n",
"list_replace_n"],
+ BuiltinScalarFunction::ArrayReplaceAll => {
+ &["array_replace_all", "list_replace_all"]
+ }
BuiltinScalarFunction::ArrayToString => &[
"array_to_string",
"list_to_string",
diff --git a/datafusion/expr/src/expr_fn.rs b/datafusion/expr/src/expr_fn.rs
index a48b5e0bee..cb5317da44 100644
--- a/datafusion/expr/src/expr_fn.rs
+++ b/datafusion/expr/src/expr_fn.rs
@@ -599,13 +599,37 @@ scalar_expr!(
ArrayRemove,
array_remove,
array element,
- "removes all elements equal to the given value from the array."
+ "removes the first element from the array equal to the given value."
+);
+scalar_expr!(
+ ArrayRemoveN,
+ array_remove_n,
+ array element max,
+ "removes the first `max` elements from the array equal to the given value."
+);
+scalar_expr!(
+ ArrayRemoveAll,
+ array_remove_all,
+ array element,
+ "removes all elements from the array equal to the given value."
);
scalar_expr!(
ArrayReplace,
array_replace,
array from to,
- "replaces a specified element with another specified element."
+ "replaces the first occurrence of the specified element with another
specified element."
+);
+scalar_expr!(
+ ArrayReplaceN,
+ array_replace_n,
+ array from to max,
+ "replaces the first `max` occurrences of the specified element with
another specified element."
+);
+scalar_expr!(
+ ArrayReplaceAll,
+ array_replace_all,
+ array from to,
+ "replaces all occurrences of the specified element with another specified
element."
);
scalar_expr!(
ArrayToString,
@@ -1026,7 +1050,11 @@ mod test {
test_scalar_expr!(ArrayPositions, array_positions, array, element);
test_scalar_expr!(ArrayPrepend, array_prepend, array, element);
test_scalar_expr!(ArrayRemove, array_remove, array, element);
+ test_scalar_expr!(ArrayRemoveN, array_remove_n, array, element, max);
+ test_scalar_expr!(ArrayRemoveAll, array_remove_all, array, element);
test_scalar_expr!(ArrayReplace, array_replace, array, from, to);
+ test_scalar_expr!(ArrayReplaceN, array_replace_n, array, from, to,
max);
+ test_scalar_expr!(ArrayReplaceAll, array_replace_all, array, from, to);
test_scalar_expr!(ArrayToString, array_to_string, array, delimiter);
test_unary_scalar_expr!(Cardinality, cardinality);
test_nary_scalar_expr!(MakeArray, array, input);
diff --git a/datafusion/physical-expr/src/array_expressions.rs
b/datafusion/physical-expr/src/array_expressions.rs
index 239b2a6a48..01b9ac95b4 100644
--- a/datafusion/physical-expr/src/array_expressions.rs
+++ b/datafusion/physical-expr/src/array_expressions.rs
@@ -361,7 +361,7 @@ macro_rules! append {
let element = downcast_arg!($ELEMENT, $ARRAY_TYPE);
for (arr, el) in $ARRAY.iter().zip(element.iter()) {
let last_offset: i32 = offsets.last().copied().ok_or_else(|| {
- DataFusionError::Internal(format!("offsets should not be
empty",))
+ DataFusionError::Internal(format!("offsets should not be
empty"))
})?;
match arr {
Some(arr) => {
@@ -451,7 +451,7 @@ macro_rules! prepend {
let element = downcast_arg!($ELEMENT, $ARRAY_TYPE);
for (arr, el) in $ARRAY.iter().zip(element.iter()) {
let last_offset: i32 = offsets.last().copied().ok_or_else(|| {
- DataFusionError::Internal(format!("offsets should not be
empty",))
+ DataFusionError::Internal(format!("offsets should not be
empty"))
})?;
match arr {
Some(arr) => {
@@ -902,178 +902,352 @@ pub fn array_positions(args: &[ArrayRef]) ->
Result<ArrayRef> {
Ok(res)
}
-macro_rules! remove {
- ($ARRAY:expr, $ELEMENT:expr, $ARRAY_TYPE:ident, $BUILDER_TYPE:ident) => {{
- let child_array =
- downcast_arg!(downcast_arg!($ARRAY, ListArray).values(),
$ARRAY_TYPE);
- let element = downcast_arg!($ELEMENT, $ARRAY_TYPE).value(0);
- let mut builder = new_builder!($BUILDER_TYPE, child_array.len());
+macro_rules! general_remove {
+ ($ARRAY:expr, $ELEMENT:expr, $MAX:expr, $ARRAY_TYPE:ident) => {{
+ let mut offsets: Vec<i32> = vec![0];
+ let mut values =
+ downcast_arg!(new_empty_array($ELEMENT.data_type()),
$ARRAY_TYPE).clone();
- for x in child_array {
- match x {
- Some(x) => {
- if x != element {
- builder.append_value(x);
- }
+ let element = downcast_arg!($ELEMENT, $ARRAY_TYPE);
+ for ((arr, el), max) in
$ARRAY.iter().zip(element.iter()).zip($MAX.iter()) {
+ let last_offset: i32 = offsets.last().copied().ok_or_else(|| {
+ DataFusionError::Internal(format!("offsets should not be
empty"))
+ })?;
+ match arr {
+ Some(arr) => {
+ let child_array = downcast_arg!(arr, $ARRAY_TYPE);
+ let mut counter = 0;
+ let max = if max < Some(1) { 1 } else { max.unwrap() };
+
+ let filter_array = child_array
+ .iter()
+ .map(|element| {
+ if counter != max && element == el {
+ counter += 1;
+ Some(false)
+ } else {
+ Some(true)
+ }
+ })
+ .collect::<BooleanArray>();
+
+ let filtered_array = compute::filter(&child_array,
&filter_array)?;
+ values = downcast_arg!(
+ compute::concat(&[&values, &filtered_array,])?.clone(),
+ $ARRAY_TYPE
+ )
+ .clone();
+ offsets.push(last_offset + filtered_array.len() as i32);
}
- None => builder.append_null(),
+ None => offsets.push(last_offset),
}
}
- let arr = builder.finish();
- let mut scalars = vec![];
- for i in 0..arr.len() {
-
scalars.push(ColumnarValue::Scalar(ScalarValue::try_from_array(&arr, i)?));
- }
- scalars
+ let field = Arc::new(Field::new("item", $ELEMENT.data_type().clone(),
true));
+
+ Arc::new(ListArray::try_new(
+ field,
+ OffsetBuffer::new(offsets.into()),
+ Arc::new(values),
+ None,
+ )?)
}};
}
-/// Array_remove SQL function
-pub fn array_remove(args: &[ColumnarValue]) -> Result<ColumnarValue> {
- let arr = match &args[0] {
- ColumnarValue::Scalar(scalar) => scalar.to_array().clone(),
- ColumnarValue::Array(arr) => arr.clone(),
- };
+macro_rules! array_removement_function {
+ ($FUNC:ident, $MAX_FUNC:expr, $DOC:expr) => {
+ #[doc = $DOC]
+ pub fn $FUNC(args: &[ArrayRef]) -> Result<ArrayRef> {
+ let arr = as_list_array(&args[0])?;
+ let element = &args[1];
+ let max = $MAX_FUNC(args)?;
+
+ let res = match (arr.value_type(), element.data_type()) {
+ (DataType::List(_), DataType::List(_)) =>
general_remove!(arr, element, max, ListArray),
+ (DataType::Utf8, DataType::Utf8) =>
general_remove!(arr, element, max, StringArray),
+ (DataType::LargeUtf8, DataType::LargeUtf8) =>
general_remove!(arr, element, max, LargeStringArray),
+ (DataType::Boolean, DataType::Boolean) =>
general_remove!(arr, element, max, BooleanArray),
+ (DataType::Float32, DataType::Float32) =>
general_remove!(arr, element, max, Float32Array),
+ (DataType::Float64, DataType::Float64) =>
general_remove!(arr, element, max, Float64Array),
+ (DataType::Int8, DataType::Int8) =>
general_remove!(arr, element, max, Int8Array),
+ (DataType::Int16, DataType::Int16) =>
general_remove!(arr, element, max, Int16Array),
+ (DataType::Int32, DataType::Int32) =>
general_remove!(arr, element, max, Int32Array),
+ (DataType::Int64, DataType::Int64) =>
general_remove!(arr, element, max, Int64Array),
+ (DataType::UInt8, DataType::UInt8) =>
general_remove!(arr, element, max, UInt8Array),
+ (DataType::UInt16, DataType::UInt16) =>
general_remove!(arr, element, max, UInt16Array),
+ (DataType::UInt32, DataType::UInt32) =>
general_remove!(arr, element, max, UInt32Array),
+ (DataType::UInt64, DataType::UInt64) =>
general_remove!(arr, element, max, UInt64Array),
+ (array_data_type, element_data_type) => {
+ return Err(DataFusionError::NotImplemented(format!(
+ "{} is not implemented for types
'{array_data_type:?}' and '{element_data_type:?}'.",
+ stringify!($FUNC),
+ )))
+ }
+ };
- let element = match &args[1] {
- ColumnarValue::Scalar(scalar) => scalar.to_array().clone(),
- _ => {
- return Err(DataFusionError::Internal(
- "Array_remove function requires scalar element".to_string(),
- ))
+ Ok(res)
}
- };
+ }
+}
- let data_type = arr.data_type();
- let res = match data_type {
- DataType::List(field) => {
- match (field.data_type(), element.data_type()) {
- (DataType::Utf8, DataType::Utf8) => remove!(arr, element,
StringArray, StringBuilder),
- (DataType::LargeUtf8, DataType::LargeUtf8) => remove!(arr,
element, LargeStringArray, LargeStringBuilder),
- (DataType::Boolean, DataType::Boolean) => remove!(arr,
element, BooleanArray, BooleanBuilder),
- (DataType::Float32, DataType::Float32) => remove!(arr,
element, Float32Array, Float32Builder),
- (DataType::Float64, DataType::Float64) => remove!(arr,
element, Float64Array, Float64Builder),
- (DataType::Int8, DataType::Int8) => remove!(arr, element,
Int8Array, Int8Builder),
- (DataType::Int16, DataType::Int16) => remove!(arr, element,
Int16Array, Int16Builder),
- (DataType::Int32, DataType::Int32) => remove!(arr, element,
Int32Array, Int32Builder),
- (DataType::Int64, DataType::Int64) => remove!(arr, element,
Int64Array, Int64Builder),
- (DataType::UInt8, DataType::UInt8) => remove!(arr, element,
UInt8Array, UInt8Builder),
- (DataType::UInt16, DataType::UInt16) => remove!(arr, element,
UInt16Array, UInt16Builder),
- (DataType::UInt32, DataType::UInt32) => remove!(arr, element,
UInt32Array, UInt32Builder),
- (DataType::UInt64, DataType::UInt64) => remove!(arr, element,
UInt64Array, UInt64Builder),
- (array_data_type, element_data_type) => {
- return Err(DataFusionError::NotImplemented(format!(
- "Array_remove is not implemented for types
'{array_data_type:?}' and '{element_data_type:?}'."
- )))
- }
- }
- }
- data_type => {
- return Err(DataFusionError::Internal(format!(
- "Array is not type '{data_type:?}'."
- )))
- }
- };
+fn remove_one(args: &[ArrayRef]) -> Result<Int64Array> {
+ Ok(Int64Array::from_value(1, args[0].len()))
+}
- array(res.as_slice())
+fn remove_n(args: &[ArrayRef]) -> Result<Int64Array> {
+ as_int64_array(&args[2]).cloned()
}
-macro_rules! replace {
- ($ARRAY:expr, $FROM:expr, $TO:expr, $ARRAY_TYPE:ident,
$BUILDER_TYPE:ident) => {{
- let child_array =
- downcast_arg!(downcast_arg!($ARRAY, ListArray).values(),
$ARRAY_TYPE);
- let from = downcast_arg!($FROM, $ARRAY_TYPE).value(0);
- let to = downcast_arg!($TO, $ARRAY_TYPE).value(0);
- let mut builder = new_builder!($BUILDER_TYPE, child_array.len());
+fn remove_all(args: &[ArrayRef]) -> Result<Int64Array> {
+ Ok(Int64Array::from_value(i64::MAX, args[0].len()))
+}
- for x in child_array {
- match x {
- Some(x) => {
- if x == from {
- builder.append_value(to);
- } else {
- builder.append_value(x);
- }
+// array removement functions
+array_removement_function!(array_remove, remove_one, "Array_remove SQL
function");
+array_removement_function!(array_remove_n, remove_n, "Array_remove_n SQL
function");
+array_removement_function!(
+ array_remove_all,
+ remove_all,
+ "Array_remove_all SQL function"
+);
+
+macro_rules! general_replace {
+ ($ARRAY:expr, $FROM:expr, $TO:expr, $MAX:expr, $ARRAY_TYPE:ident) => {{
+ let mut offsets: Vec<i32> = vec![0];
+ let mut values =
+ downcast_arg!(new_empty_array($FROM.data_type()),
$ARRAY_TYPE).clone();
+
+ let from_array = downcast_arg!($FROM, $ARRAY_TYPE);
+ let to_array = downcast_arg!($TO, $ARRAY_TYPE);
+ for (((arr, from), to), max) in $ARRAY
+ .iter()
+ .zip(from_array.iter())
+ .zip(to_array.iter())
+ .zip($MAX.iter())
+ {
+ let last_offset: i32 = offsets.last().copied().ok_or_else(|| {
+ DataFusionError::Internal(format!("offsets should not be
empty"))
+ })?;
+ match arr {
+ Some(arr) => {
+ let child_array = downcast_arg!(arr, $ARRAY_TYPE);
+ let mut counter = 0;
+ let max = if max < Some(1) { 1 } else { max.unwrap() };
+
+ let replaced_array = child_array
+ .iter()
+ .map(|el| {
+ if counter != max && el == from {
+ counter += 1;
+ to
+ } else {
+ el
+ }
+ })
+ .collect::<$ARRAY_TYPE>();
+
+ values = downcast_arg!(
+ compute::concat(&[&values, &replaced_array])?.clone(),
+ $ARRAY_TYPE
+ )
+ .clone();
+ offsets.push(last_offset + replaced_array.len() as i32);
+ }
+ None => {
+ offsets.push(last_offset);
}
- None => builder.append_null(),
}
}
- let arr = builder.finish();
- let mut scalars = vec![];
- for i in 0..arr.len() {
-
scalars.push(ColumnarValue::Scalar(ScalarValue::try_from_array(&arr, i)?));
- }
- scalars
+ let field = Arc::new(Field::new("item", $FROM.data_type().clone(),
true));
+
+ Arc::new(ListArray::try_new(
+ field,
+ OffsetBuffer::new(offsets.into()),
+ Arc::new(values),
+ None,
+ )?)
}};
}
-/// Array_replace SQL function
-pub fn array_replace(args: &[ColumnarValue]) -> Result<ColumnarValue> {
- let arr = match &args[0] {
- ColumnarValue::Scalar(scalar) => scalar.to_array().clone(),
- ColumnarValue::Array(arr) => arr.clone(),
- };
+macro_rules! general_replace_list {
+ ($ARRAY:expr, $FROM:expr, $TO:expr, $MAX:expr, $ARRAY_TYPE:ident) => {{
+ let mut offsets: Vec<i32> = vec![0];
+ let mut values =
+ downcast_arg!(new_empty_array($FROM.data_type()),
ListArray).clone();
- let from = match &args[1] {
- ColumnarValue::Scalar(scalar) => scalar.to_array().clone(),
- _ => {
- return Err(DataFusionError::Internal(
- "array_replace function requires scalar element".to_string(),
- ))
- }
- };
+ let from_array = downcast_arg!($FROM, ListArray);
+ let to_array = downcast_arg!($TO, ListArray);
+ for (((arr, from), to), max) in $ARRAY
+ .iter()
+ .zip(from_array.iter())
+ .zip(to_array.iter())
+ .zip($MAX.iter())
+ {
+ let last_offset: i32 = offsets.last().copied().ok_or_else(|| {
+ DataFusionError::Internal(format!("offsets should not be
empty"))
+ })?;
+ match arr {
+ Some(arr) => {
+ let child_array = downcast_arg!(arr, ListArray);
+ let mut counter = 0;
+ let max = if max < Some(1) { 1 } else { max.unwrap() };
- let to = match &args[2] {
- ColumnarValue::Scalar(scalar) => scalar.to_array().clone(),
- _ => {
- return Err(DataFusionError::Internal(
- "array_replace function requires scalar element".to_string(),
- ))
- }
- };
+ let replaced_vec = child_array
+ .iter()
+ .map(|el| {
+ if counter != max && el == from {
+ counter += 1;
+ to.clone().unwrap()
+ } else {
+ el.clone().unwrap()
+ }
+ })
+ .collect::<Vec<_>>();
+
+ let mut i: i32 = 0;
+ let mut replaced_offsets = vec![i];
+ replaced_offsets.extend(
+ replaced_vec
+ .clone()
+ .into_iter()
+ .map(|a| {
+ i += a.len() as i32;
+ i
+ })
+ .collect::<Vec<_>>(),
+ );
+
+ let mut replaced_values = downcast_arg!(
+ new_empty_array(&from_array.value_type()),
+ $ARRAY_TYPE
+ )
+ .clone();
+ for replaced_list in replaced_vec {
+ replaced_values = downcast_arg!(
+ compute::concat(&[&replaced_values,
&replaced_list])?,
+ $ARRAY_TYPE
+ )
+ .clone();
+ }
- if from.data_type() != to.data_type() {
- return Err(DataFusionError::Internal(
- "array_replace function requires scalar element".to_string(),
- ));
- }
+ let field = Arc::new(Field::new(
+ "item",
+ from_array.value_type().clone(),
+ true,
+ ));
+ let replaced_array = ListArray::try_new(
+ field,
+ OffsetBuffer::new(replaced_offsets.clone().into()),
+ Arc::new(replaced_values),
+ None,
+ )?;
- let data_type = arr.data_type();
- let res = match data_type {
- DataType::List(field) => {
- match (field.data_type(), from.data_type()) {
- (DataType::Utf8, DataType::Utf8) => replace!(arr, from, to,
StringArray, StringBuilder),
- (DataType::LargeUtf8, DataType::LargeUtf8) => replace!(arr,
from, to, LargeStringArray, LargeStringBuilder),
- (DataType::Boolean, DataType::Boolean) => replace!(arr, from,
to, BooleanArray, BooleanBuilder),
- (DataType::Float32, DataType::Float32) => replace!(arr, from,
to, Float32Array, Float32Builder),
- (DataType::Float64, DataType::Float64) => replace!(arr, from,
to, Float64Array, Float64Builder),
- (DataType::Int8, DataType::Int8) => replace!(arr, from, to,
Int8Array, Int8Builder),
- (DataType::Int16, DataType::Int16) => replace!(arr, from, to,
Int16Array, Int16Builder),
- (DataType::Int32, DataType::Int32) => replace!(arr, from, to,
Int32Array, Int32Builder),
- (DataType::Int64, DataType::Int64) => replace!(arr, from, to,
Int64Array, Int64Builder),
- (DataType::UInt8, DataType::UInt8) => replace!(arr, from, to,
UInt8Array, UInt8Builder),
- (DataType::UInt16, DataType::UInt16) => replace!(arr, from,
to, UInt16Array, UInt16Builder),
- (DataType::UInt32, DataType::UInt32) => replace!(arr, from,
to, UInt32Array, UInt32Builder),
- (DataType::UInt64, DataType::UInt64) => replace!(arr, from,
to, UInt64Array, UInt64Builder),
- (array_data_type, element_data_type) => {
- return Err(DataFusionError::NotImplemented(format!(
- "Array_replace is not implemented for types
'{array_data_type:?}' and '{element_data_type:?}'."
- )))
+ values = downcast_arg!(
+ compute::concat(&[&values, &replaced_array,])?.clone(),
+ ListArray
+ )
+ .clone();
+ offsets.push(last_offset + replaced_array.len() as i32);
+ }
+ None => {
+ offsets.push(last_offset);
}
}
}
- data_type => {
- return Err(DataFusionError::Internal(format!(
- "Array is not type '{data_type:?}'."
- )))
+
+ let field = Arc::new(Field::new("item", $FROM.data_type().clone(),
true));
+
+ Arc::new(ListArray::try_new(
+ field,
+ OffsetBuffer::new(offsets.into()),
+ Arc::new(values),
+ None,
+ )?)
+ }};
+}
+
+macro_rules! array_replacement_function {
+ ($FUNC:ident, $MAX_FUNC:expr, $DOC:expr) => {
+ #[doc = $DOC]
+ pub fn $FUNC(args: &[ArrayRef]) -> Result<ArrayRef> {
+ let arr = as_list_array(&args[0])?;
+ let from = &args[1];
+ let to = &args[2];
+ let max = $MAX_FUNC(args)?;
+
+ let res = match (arr.value_type(), from.data_type(),
to.data_type()) {
+ (DataType::List(afield), DataType::List(ffield),
DataType::List(tfield)) => {
+ match (afield.data_type(), ffield.data_type(),
tfield.data_type()) {
+ (DataType::List(_), DataType::List(_),
DataType::List(_)) => general_replace_list!(arr, from, to, max, ListArray),
+ (DataType::Utf8, DataType::Utf8,
DataType::Utf8) => general_replace_list!(arr, from, to, max, StringArray),
+ (DataType::LargeUtf8, DataType::LargeUtf8,
DataType::LargeUtf8) => general_replace_list!(arr, from, to, max,
LargeStringArray),
+ (DataType::Boolean, DataType::Boolean,
DataType::Boolean) => general_replace_list!(arr, from, to, max, BooleanArray),
+ (DataType::Float32, DataType::Float32,
DataType::Float32) => general_replace_list!(arr, from, to, max, Float32Array),
+ (DataType::Float64, DataType::Float64,
DataType::Float64) => general_replace_list!(arr, from, to, max, Float64Array),
+ (DataType::Int8, DataType::Int8,
DataType::Int8) => general_replace_list!(arr, from, to, max, Int8Array),
+ (DataType::Int16, DataType::Int16,
DataType::Int16) => general_replace_list!(arr, from, to, max, Int16Array),
+ (DataType::Int32, DataType::Int32,
DataType::Int32) => general_replace_list!(arr, from, to, max, Int32Array),
+ (DataType::Int64, DataType::Int64,
DataType::Int64) => general_replace_list!(arr, from, to, max, Int64Array),
+ (DataType::UInt8, DataType::UInt8,
DataType::UInt8) => general_replace_list!(arr, from, to, max, UInt8Array),
+ (DataType::UInt16, DataType::UInt16,
DataType::UInt16) => general_replace_list!(arr, from, to, max, UInt16Array),
+ (DataType::UInt32, DataType::UInt32,
DataType::UInt32) => general_replace_list!(arr, from, to, max, UInt32Array),
+ (DataType::UInt64, DataType::UInt64,
DataType::UInt64) => general_replace_list!(arr, from, to, max, UInt64Array),
+ (array_data_type, from_data_type,
to_data_type) => {
+ return
Err(DataFusionError::NotImplemented(format!(
+ "{} is not implemented for types
'List({array_data_type:?})', 'List({from_data_type:?})' and
'List({to_data_type:?})'.",
+ stringify!($FUNC),
+ )))
+ }
+ }
+ }
+ (DataType::Utf8, DataType::Utf8, DataType::Utf8) =>
general_replace!(arr, from, to, max, StringArray),
+ (DataType::LargeUtf8, DataType::LargeUtf8,
DataType::LargeUtf8) => general_replace!(arr, from, to, max, LargeStringArray),
+ (DataType::Boolean, DataType::Boolean,
DataType::Boolean) => general_replace!(arr, from, to, max, BooleanArray),
+ (DataType::Float32, DataType::Float32,
DataType::Float32) => general_replace!(arr, from, to, max, Float32Array),
+ (DataType::Float64, DataType::Float64,
DataType::Float64) => general_replace!(arr, from, to, max, Float64Array),
+ (DataType::Int8, DataType::Int8, DataType::Int8) =>
general_replace!(arr, from, to, max, Int8Array),
+ (DataType::Int16, DataType::Int16, DataType::Int16) =>
general_replace!(arr, from, to, max, Int16Array),
+ (DataType::Int32, DataType::Int32, DataType::Int32) =>
general_replace!(arr, from, to, max, Int32Array),
+ (DataType::Int64, DataType::Int64, DataType::Int64) =>
general_replace!(arr, from, to, max, Int64Array),
+ (DataType::UInt8, DataType::UInt8, DataType::UInt8) =>
general_replace!(arr, from, to, max, UInt8Array),
+ (DataType::UInt16, DataType::UInt16, DataType::UInt16)
=> general_replace!(arr, from, to, max, UInt16Array),
+ (DataType::UInt32, DataType::UInt32, DataType::UInt32)
=> general_replace!(arr, from, to, max, UInt32Array),
+ (DataType::UInt64, DataType::UInt64, DataType::UInt64)
=> general_replace!(arr, from, to, max, UInt64Array),
+ (array_data_type, from_data_type, to_data_type) => {
+ return Err(DataFusionError::NotImplemented(format!(
+ "{} is not implemented for types
'{array_data_type:?}', '{from_data_type:?}' and '{to_data_type:?}'.",
+ stringify!($FUNC),
+ )))
+ }
+ };
+
+ Ok(res)
}
};
+}
+
+fn replace_one(args: &[ArrayRef]) -> Result<Int64Array> {
+ Ok(Int64Array::from_value(1, args[0].len()))
+}
- array(res.as_slice())
+fn replace_n(args: &[ArrayRef]) -> Result<Int64Array> {
+ as_int64_array(&args[3]).cloned()
}
+fn replace_all(args: &[ArrayRef]) -> Result<Int64Array> {
+ Ok(Int64Array::from_value(i64::MAX, args[0].len()))
+}
+
+// array replacement functions
+array_replacement_function!(array_replace, replace_one, "Array_replace SQL
function");
+array_replacement_function!(array_replace_n, replace_n, "Array_replace_n SQL
function");
+array_replacement_function!(
+ array_replace_all,
+ replace_all,
+ "Array_replace_all SQL function"
+);
+
macro_rules! to_string {
($ARG:expr, $ARRAY:expr, $DELIMETER:expr, $NULL_STRING:expr,
$WITH_NULL_STRING:expr, $ARRAY_TYPE:ident) => {{
let arr = downcast_arg!($ARRAY, $ARRAY_TYPE);
@@ -1957,20 +2131,73 @@ mod tests {
#[test]
fn test_array_remove() {
- // array_remove([1, 2, 3, 4], 3) = [1, 2, 4]
- let list_array = return_array();
- let arr = array_remove(&[
+ // array_remove([3, 1, 2, 3, 2, 3], 3) = [1, 2, 3, 2, 3]
+ let list_array = return_array_with_repeating_elements().into_array(1);
+ let array = array_remove(&[list_array,
Arc::new(Int64Array::from_value(3, 1))])
+ .expect("failed to initialize function array_remove");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_remove");
+
+ assert_eq!(result.len(), 1);
+ assert_eq!(
+ &[1, 2, 3, 2, 3],
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<Int64Array>()
+ .unwrap()
+ .values()
+ );
+ }
+
+ #[test]
+ fn test_nested_array_remove() {
+ // array_remove(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // ) = [[5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let element_array = return_array().into_array(1);
+ let array = array_remove(&[list_array, element_array])
+ .expect("failed to initialize function array_remove");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_remove");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(1), Some(2), Some(3), Some(4)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
+ #[test]
+ fn test_array_remove_n() {
+ // array_remove_n([3, 1, 2, 3, 2, 3], 3, 2) = [1, 2, 2, 3]
+ let list_array = return_array_with_repeating_elements().into_array(1);
+ let array = array_remove_n(&[
list_array,
- ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ Arc::new(Int64Array::from_value(3, 1)),
+ Arc::new(Int64Array::from_value(2, 1)),
])
- .expect("failed to initialize function array_remove")
- .into_array(1);
+ .expect("failed to initialize function array_remove_n");
let result =
- as_list_array(&arr).expect("failed to initialize function
array_remove");
+ as_list_array(&array).expect("failed to initialize function
array_remove_n");
assert_eq!(result.len(), 1);
assert_eq!(
- &[1, 2, 4],
+ &[1, 2, 2, 3],
result
.value(0)
.as_any()
@@ -1980,23 +2207,238 @@ mod tests {
);
}
+ #[test]
+ fn test_nested_array_remove_n() {
+ // array_remove_n(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // 3,
+ // ) = [[5, 6, 7, 8], [9, 10, 11, 12], [5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let element_array = return_array().into_array(1);
+ let array = array_remove_n(&[
+ list_array,
+ element_array,
+ Arc::new(Int64Array::from_value(3, 1)),
+ ])
+ .expect("failed to initialize function array_remove_n");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_remove_n");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
+ #[test]
+ fn test_array_remove_all() {
+ // array_remove_all([3, 1, 2, 3, 2, 3], 3) = [1, 2, 2]
+ let list_array = return_array_with_repeating_elements().into_array(1);
+ let array =
+ array_remove_all(&[list_array, Arc::new(Int64Array::from_value(3,
1))])
+ .expect("failed to initialize function array_remove_all");
+ let result = as_list_array(&array)
+ .expect("failed to initialize function array_remove_all");
+
+ assert_eq!(result.len(), 1);
+ assert_eq!(
+ &[1, 2, 2],
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<Int64Array>()
+ .unwrap()
+ .values()
+ );
+ }
+
+ #[test]
+ fn test_nested_array_remove_all() {
+ // array_remove_all(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // ) = [[5, 6, 7, 8], [9, 10, 11, 12], [5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let element_array = return_array().into_array(1);
+ let array = array_remove_all(&[list_array, element_array])
+ .expect("failed to initialize function array_remove_all");
+ let result = as_list_array(&array)
+ .expect("failed to initialize function array_remove_all");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
#[test]
fn test_array_replace() {
- // array_replace([1, 2, 3, 4], 3, 4) = [1, 2, 4, 4]
- let list_array = return_array();
+ // array_replace([3, 1, 2, 3, 2, 3], 3, 4) = [4, 1, 2, 3, 2, 3]
+ let list_array = return_array_with_repeating_elements().into_array(1);
let array = array_replace(&[
list_array,
- ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
- ColumnarValue::Scalar(ScalarValue::Int64(Some(4))),
+ Arc::new(Int64Array::from_value(3, 1)),
+ Arc::new(Int64Array::from_value(4, 1)),
])
- .expect("failed to initialize function array_replace")
- .into_array(1);
+ .expect("failed to initialize function array_replace");
let result =
as_list_array(&array).expect("failed to initialize function
array_replace");
assert_eq!(result.len(), 1);
assert_eq!(
- &[1, 2, 4, 4],
+ &[4, 1, 2, 3, 2, 3],
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<Int64Array>()
+ .unwrap()
+ .values()
+ );
+ }
+
+ #[test]
+ fn test_nested_array_replace() {
+ // array_replace(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // [11, 12, 13, 14],
+ // ) = [[11, 12, 13, 14], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12],
[5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let from_array = return_array().into_array(1);
+ let to_array = return_extra_array().into_array(1);
+ let array = array_replace(&[list_array, from_array, to_array])
+ .expect("failed to initialize function array_replace");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_replace");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(11), Some(12), Some(13), Some(14)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(1), Some(2), Some(3), Some(4)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
+ #[test]
+ fn test_array_replace_n() {
+ // array_replace_n([3, 1, 2, 3, 2, 3], 3, 4, 2) = [4, 1, 2, 4, 2, 3]
+ let list_array = return_array_with_repeating_elements().into_array(1);
+ let array = array_replace_n(&[
+ list_array,
+ Arc::new(Int64Array::from_value(3, 1)),
+ Arc::new(Int64Array::from_value(4, 1)),
+ Arc::new(Int64Array::from_value(2, 1)),
+ ])
+ .expect("failed to initialize function array_replace_n");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_replace_n");
+
+ assert_eq!(result.len(), 1);
+ assert_eq!(
+ &[4, 1, 2, 4, 2, 3],
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<Int64Array>()
+ .unwrap()
+ .values()
+ );
+ }
+
+ #[test]
+ fn test_nested_array_replace_n() {
+ // array_replace_n(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // [11, 12, 13, 14],
+ // 2,
+ // ) = [[11, 12, 13, 14], [5, 6, 7, 8], [11, 12, 13, 14], [9, 10, 11,
12], [5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let from_array = return_array().into_array(1);
+ let to_array = return_extra_array().into_array(1);
+ let array = array_replace_n(&[
+ list_array,
+ from_array,
+ to_array,
+ Arc::new(Int64Array::from_value(2, 1)),
+ ])
+ .expect("failed to initialize function array_replace_n");
+ let result =
+ as_list_array(&array).expect("failed to initialize function
array_replace_n");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(11), Some(12), Some(13), Some(14)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(11), Some(12), Some(13), Some(14)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
+ #[test]
+ fn test_array_replace_all() {
+ // array_replace_all([3, 1, 2, 3, 2, 3], 3, 4) = [4, 1, 2, 4, 2, 4]
+ let list_array = return_array_with_repeating_elements().into_array(1);
+ let array = array_replace_all(&[
+ list_array,
+ Arc::new(Int64Array::from_value(3, 1)),
+ Arc::new(Int64Array::from_value(4, 1)),
+ ])
+ .expect("failed to initialize function array_replace_all");
+ let result = as_list_array(&array)
+ .expect("failed to initialize function array_replace_all");
+
+ assert_eq!(result.len(), 1);
+ assert_eq!(
+ &[4, 1, 2, 4, 2, 4],
result
.value(0)
.as_any()
@@ -2006,6 +2448,41 @@ mod tests {
);
}
+ #[test]
+ fn test_nested_array_replace_all() {
+ // array_replace_all(
+ // [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11, 12], [5,
6, 7, 8]],
+ // [1, 2, 3, 4],
+ // [11, 12, 13, 14],
+ // ) = [[11, 12, 13, 14], [5, 6, 7, 8], [11, 12, 13, 14], [9, 10, 11,
12], [5, 6, 7, 8]]
+ let list_array =
return_nested_array_with_repeating_elements().into_array(1);
+ let from_array = return_array().into_array(1);
+ let to_array = return_extra_array().into_array(1);
+ let array = array_replace_all(&[list_array, from_array, to_array])
+ .expect("failed to initialize function array_replace_all");
+ let result = as_list_array(&array)
+ .expect("failed to initialize function array_replace_all");
+
+ assert_eq!(result.len(), 1);
+ let data = vec![
+ Some(vec![Some(11), Some(12), Some(13), Some(14)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ Some(vec![Some(11), Some(12), Some(13), Some(14)]),
+ Some(vec![Some(9), Some(10), Some(11), Some(12)]),
+ Some(vec![Some(5), Some(6), Some(7), Some(8)]),
+ ];
+ let expected = ListArray::from_iter_primitive::<Int64Type, _, _>(data);
+ assert_eq!(
+ expected,
+ result
+ .value(0)
+ .as_any()
+ .downcast_ref::<ListArray>()
+ .unwrap()
+ .clone()
+ );
+ }
+
#[test]
fn test_array_to_string() {
// array_to_string([1, 2, 3, 4], ',') = 1,2,3,4
@@ -2277,6 +2754,7 @@ mod tests {
}
fn return_array() -> ColumnarValue {
+ // Returns: [1, 2, 3, 4]
let args = [
ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
@@ -2289,7 +2767,22 @@ mod tests {
ColumnarValue::Array(result.clone())
}
+ fn return_extra_array() -> ColumnarValue {
+ // Returns: [11, 12, 13, 14]
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(11))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(12))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(13))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(14))),
+ ];
+ let result = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+ ColumnarValue::Array(result.clone())
+ }
+
fn return_nested_array() -> ColumnarValue {
+ // Returns: [[1, 2, 3, 4], [5, 6, 7, 8]]
let args = [
ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
@@ -2318,6 +2811,7 @@ mod tests {
}
fn return_array_with_nulls() -> ColumnarValue {
+ // Returns: [1, NULL, 3, NULL]
let args = [
ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
ColumnarValue::Scalar(ScalarValue::Null),
@@ -2331,6 +2825,7 @@ mod tests {
}
fn return_nested_array_with_nulls() -> ColumnarValue {
+ // Returns: [[1, NULL, 3, NULL], [NULL, 6, 7, NULL]]
let args = [
ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
ColumnarValue::Scalar(ScalarValue::Null),
@@ -2357,4 +2852,85 @@ mod tests {
.into_array(1);
ColumnarValue::Array(result.clone())
}
+
+ fn return_array_with_repeating_elements() -> ColumnarValue {
+ // Returns: [3, 1, 2, 3, 2, 3]
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ ];
+ let result = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+ ColumnarValue::Array(result.clone())
+ }
+
+ fn return_nested_array_with_repeating_elements() -> ColumnarValue {
+ // Returns: [[1, 2, 3, 4], [5, 6, 7, 8], [1, 2, 3, 4], [9, 10, 11,
12], [5, 6, 7, 8]]
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(4))),
+ ];
+ let arr1 = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(5))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(6))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(7))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(8))),
+ ];
+ let arr2 = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(1))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(2))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(3))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(4))),
+ ];
+ let arr3 = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(9))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(10))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(11))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(12))),
+ ];
+ let arr4 = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+
+ let args = [
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(5))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(6))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(7))),
+ ColumnarValue::Scalar(ScalarValue::Int64(Some(8))),
+ ];
+ let arr5 = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+
+ let args = [
+ ColumnarValue::Array(arr1),
+ ColumnarValue::Array(arr2),
+ ColumnarValue::Array(arr3),
+ ColumnarValue::Array(arr4),
+ ColumnarValue::Array(arr5),
+ ];
+ let result = array(&args)
+ .expect("failed to initialize function array")
+ .into_array(1);
+ ColumnarValue::Array(result.clone())
+ }
}
diff --git a/datafusion/physical-expr/src/functions.rs
b/datafusion/physical-expr/src/functions.rs
index f48823bff5..948cb4ec47 100644
--- a/datafusion/physical-expr/src/functions.rs
+++ b/datafusion/physical-expr/src/functions.rs
@@ -444,8 +444,24 @@ pub fn create_physical_fun(
BuiltinScalarFunction::ArrayPrepend => {
Arc::new(|args|
make_scalar_function(array_expressions::array_prepend)(args))
}
- BuiltinScalarFunction::ArrayRemove =>
Arc::new(array_expressions::array_remove),
- BuiltinScalarFunction::ArrayReplace =>
Arc::new(array_expressions::array_replace),
+ BuiltinScalarFunction::ArrayRemove => {
+ Arc::new(|args|
make_scalar_function(array_expressions::array_remove)(args))
+ }
+ BuiltinScalarFunction::ArrayRemoveN => {
+ Arc::new(|args|
make_scalar_function(array_expressions::array_remove_n)(args))
+ }
+ BuiltinScalarFunction::ArrayRemoveAll => Arc::new(|args| {
+ make_scalar_function(array_expressions::array_remove_all)(args)
+ }),
+ BuiltinScalarFunction::ArrayReplace => {
+ Arc::new(|args|
make_scalar_function(array_expressions::array_replace)(args))
+ }
+ BuiltinScalarFunction::ArrayReplaceN => Arc::new(|args| {
+ make_scalar_function(array_expressions::array_replace_n)(args)
+ }),
+ BuiltinScalarFunction::ArrayReplaceAll => Arc::new(|args| {
+ make_scalar_function(array_expressions::array_replace_all)(args)
+ }),
BuiltinScalarFunction::ArrayToString => Arc::new(|args| {
make_scalar_function(array_expressions::array_to_string)(args)
}),
diff --git a/datafusion/proto/proto/datafusion.proto
b/datafusion/proto/proto/datafusion.proto
index f00c184056..e9ae76b25d 100644
--- a/datafusion/proto/proto/datafusion.proto
+++ b/datafusion/proto/proto/datafusion.proto
@@ -573,6 +573,10 @@ enum ScalarFunction {
ArrayHas = 104;
ArrayHasAny = 105;
ArrayHasAll = 106;
+ ArrayRemoveN = 107;
+ ArrayReplaceN = 108;
+ ArrayRemoveAll = 109;
+ ArrayReplaceAll = 110;
}
message ScalarFunctionNode {
diff --git a/datafusion/proto/src/generated/pbjson.rs
b/datafusion/proto/src/generated/pbjson.rs
index 2bac658f04..a5d85cc6cf 100644
--- a/datafusion/proto/src/generated/pbjson.rs
+++ b/datafusion/proto/src/generated/pbjson.rs
@@ -18277,6 +18277,10 @@ impl serde::Serialize for ScalarFunction {
Self::ArrayHas => "ArrayHas",
Self::ArrayHasAny => "ArrayHasAny",
Self::ArrayHasAll => "ArrayHasAll",
+ Self::ArrayRemoveN => "ArrayRemoveN",
+ Self::ArrayReplaceN => "ArrayReplaceN",
+ Self::ArrayRemoveAll => "ArrayRemoveAll",
+ Self::ArrayReplaceAll => "ArrayReplaceAll",
};
serializer.serialize_str(variant)
}
@@ -18394,6 +18398,10 @@ impl<'de> serde::Deserialize<'de> for ScalarFunction {
"ArrayHas",
"ArrayHasAny",
"ArrayHasAll",
+ "ArrayRemoveN",
+ "ArrayReplaceN",
+ "ArrayRemoveAll",
+ "ArrayReplaceAll",
];
struct GeneratedVisitor;
@@ -18542,6 +18550,10 @@ impl<'de> serde::Deserialize<'de> for ScalarFunction {
"ArrayHas" => Ok(ScalarFunction::ArrayHas),
"ArrayHasAny" => Ok(ScalarFunction::ArrayHasAny),
"ArrayHasAll" => Ok(ScalarFunction::ArrayHasAll),
+ "ArrayRemoveN" => Ok(ScalarFunction::ArrayRemoveN),
+ "ArrayReplaceN" => Ok(ScalarFunction::ArrayReplaceN),
+ "ArrayRemoveAll" => Ok(ScalarFunction::ArrayRemoveAll),
+ "ArrayReplaceAll" => Ok(ScalarFunction::ArrayReplaceAll),
_ => Err(serde::de::Error::unknown_variant(value, FIELDS)),
}
}
diff --git a/datafusion/proto/src/generated/prost.rs
b/datafusion/proto/src/generated/prost.rs
index 801162ab19..c6f3a23ed6 100644
--- a/datafusion/proto/src/generated/prost.rs
+++ b/datafusion/proto/src/generated/prost.rs
@@ -2306,6 +2306,10 @@ pub enum ScalarFunction {
ArrayHas = 104,
ArrayHasAny = 105,
ArrayHasAll = 106,
+ ArrayRemoveN = 107,
+ ArrayReplaceN = 108,
+ ArrayRemoveAll = 109,
+ ArrayReplaceAll = 110,
}
impl ScalarFunction {
/// String value of the enum field names used in the ProtoBuf definition.
@@ -2420,6 +2424,10 @@ impl ScalarFunction {
ScalarFunction::ArrayHas => "ArrayHas",
ScalarFunction::ArrayHasAny => "ArrayHasAny",
ScalarFunction::ArrayHasAll => "ArrayHasAll",
+ ScalarFunction::ArrayRemoveN => "ArrayRemoveN",
+ ScalarFunction::ArrayReplaceN => "ArrayReplaceN",
+ ScalarFunction::ArrayRemoveAll => "ArrayRemoveAll",
+ ScalarFunction::ArrayReplaceAll => "ArrayReplaceAll",
}
}
/// Creates an enum from field names used in the ProtoBuf definition.
@@ -2531,6 +2539,10 @@ impl ScalarFunction {
"ArrayHas" => Some(Self::ArrayHas),
"ArrayHasAny" => Some(Self::ArrayHasAny),
"ArrayHasAll" => Some(Self::ArrayHasAll),
+ "ArrayRemoveN" => Some(Self::ArrayRemoveN),
+ "ArrayReplaceN" => Some(Self::ArrayReplaceN),
+ "ArrayRemoveAll" => Some(Self::ArrayRemoveAll),
+ "ArrayReplaceAll" => Some(Self::ArrayReplaceAll),
_ => None,
}
}
diff --git a/datafusion/proto/src/logical_plan/from_proto.rs
b/datafusion/proto/src/logical_plan/from_proto.rs
index 71a1bf87db..2591f179b9 100644
--- a/datafusion/proto/src/logical_plan/from_proto.rs
+++ b/datafusion/proto/src/logical_plan/from_proto.rs
@@ -38,8 +38,9 @@ use datafusion_expr::expr::{Alias, Placeholder};
use datafusion_expr::{
abs, acos, acosh, array, array_append, array_concat, array_dims,
array_fill,
array_has, array_has_all, array_has_any, array_length, array_ndims,
array_position,
- array_positions, array_prepend, array_remove, array_replace,
array_to_string, ascii,
- asin, asinh, atan, atan2, atanh, bit_length, btrim, cardinality, cbrt,
ceil,
+ array_positions, array_prepend, array_remove, array_remove_all,
array_remove_n,
+ array_replace, array_replace_all, array_replace_n, array_to_string, ascii,
asin,
+ asinh, atan, atan2, atanh, bit_length, btrim, cardinality, cbrt, ceil,
character_length, chr, coalesce, concat_expr, concat_ws_expr, cos, cosh,
cot,
current_date, current_time, date_bin, date_part, date_trunc, degrees,
digest, exp,
expr::{self, InList, Sort, WindowFunction},
@@ -462,7 +463,11 @@ impl From<&protobuf::ScalarFunction> for
BuiltinScalarFunction {
ScalarFunction::ArrayPositions => Self::ArrayPositions,
ScalarFunction::ArrayPrepend => Self::ArrayPrepend,
ScalarFunction::ArrayRemove => Self::ArrayRemove,
+ ScalarFunction::ArrayRemoveN => Self::ArrayRemoveN,
+ ScalarFunction::ArrayRemoveAll => Self::ArrayRemoveAll,
ScalarFunction::ArrayReplace => Self::ArrayReplace,
+ ScalarFunction::ArrayReplaceN => Self::ArrayReplaceN,
+ ScalarFunction::ArrayReplaceAll => Self::ArrayReplaceAll,
ScalarFunction::ArrayToString => Self::ArrayToString,
ScalarFunction::Cardinality => Self::Cardinality,
ScalarFunction::Array => Self::MakeArray,
@@ -1258,11 +1263,31 @@ pub fn parse_expr(
parse_expr(&args[0], registry)?,
parse_expr(&args[1], registry)?,
)),
+ ScalarFunction::ArrayRemoveN => Ok(array_remove_n(
+ parse_expr(&args[0], registry)?,
+ parse_expr(&args[1], registry)?,
+ parse_expr(&args[2], registry)?,
+ )),
+ ScalarFunction::ArrayRemoveAll => Ok(array_remove_all(
+ parse_expr(&args[0], registry)?,
+ parse_expr(&args[1], registry)?,
+ )),
ScalarFunction::ArrayReplace => Ok(array_replace(
parse_expr(&args[0], registry)?,
parse_expr(&args[1], registry)?,
parse_expr(&args[2], registry)?,
)),
+ ScalarFunction::ArrayReplaceN => Ok(array_replace_n(
+ parse_expr(&args[0], registry)?,
+ parse_expr(&args[1], registry)?,
+ parse_expr(&args[2], registry)?,
+ parse_expr(&args[3], registry)?,
+ )),
+ ScalarFunction::ArrayReplaceAll => Ok(array_replace_all(
+ parse_expr(&args[0], registry)?,
+ parse_expr(&args[1], registry)?,
+ parse_expr(&args[2], registry)?,
+ )),
ScalarFunction::ArrayToString => Ok(array_to_string(
parse_expr(&args[0], registry)?,
parse_expr(&args[1], registry)?,
diff --git a/datafusion/proto/src/logical_plan/to_proto.rs
b/datafusion/proto/src/logical_plan/to_proto.rs
index f1a9615761..df5701a282 100644
--- a/datafusion/proto/src/logical_plan/to_proto.rs
+++ b/datafusion/proto/src/logical_plan/to_proto.rs
@@ -1411,7 +1411,11 @@ impl TryFrom<&BuiltinScalarFunction> for
protobuf::ScalarFunction {
BuiltinScalarFunction::ArrayPositions => Self::ArrayPositions,
BuiltinScalarFunction::ArrayPrepend => Self::ArrayPrepend,
BuiltinScalarFunction::ArrayRemove => Self::ArrayRemove,
+ BuiltinScalarFunction::ArrayRemoveN => Self::ArrayRemoveN,
+ BuiltinScalarFunction::ArrayRemoveAll => Self::ArrayRemoveAll,
BuiltinScalarFunction::ArrayReplace => Self::ArrayReplace,
+ BuiltinScalarFunction::ArrayReplaceN => Self::ArrayReplaceN,
+ BuiltinScalarFunction::ArrayReplaceAll => Self::ArrayReplaceAll,
BuiltinScalarFunction::ArrayToString => Self::ArrayToString,
BuiltinScalarFunction::Cardinality => Self::Cardinality,
BuiltinScalarFunction::MakeArray => Self::Array,
diff --git a/docs/source/user-guide/expressions.md
b/docs/source/user-guide/expressions.md
index 14cf8dc2ac..139e968ecc 100644
--- a/docs/source/user-guide/expressions.md
+++ b/docs/source/user-guide/expressions.md
@@ -179,26 +179,30 @@ Unlike to some databases the math functions in Datafusion
works the same way as
## Array Expressions
-| Function | Notes
|
-| ------------------------------------ |
--------------------------------------------------------------------------------------------------------------
|
-| array_append(array, element) | Appends an element to the end of an
array. `array_append([1, 2, 3], 4) -> [1, 2, 3, 4]` |
-| array_concat(array[, ..., array_n]) | Concatenates arrays.
`array_concat([1, 2, 3], [4, 5, 6]) -> [1, 2, 3, 4, 5, 6]`
|
-| array_has(array, element) | Returns true if the array contains
the element `array_has([1,2,3], 1) -> true` |
-| array_has_all(array, sub-array) | Returns true if all elements of
sub-array exist in array `array_has_all([1,2,3], [1,3]) -> true` |
-| array_has_any(array, sub-array) | Returns true if any elements exist in
both arrays `array_has_any([1,2,3], [1,4]) -> true` |
-| array_dims(array) | Returns an array of the array's
dimensions. `array_dims([[1, 2, 3], [4, 5, 6]]) -> [2, 3]` |
-| array_fill(element, array) | Returns an array filled with copies
of the given value. |
-| array_length(array, dimension) | Returns the length of the array
dimension. `array_length([1, 2, 3, 4, 5]) -> 5` |
-| array_ndims(array) | Returns the number of dimensions of
the array. `array_ndims([[1, 2, 3], [4, 5, 6]]) -> 2` |
-| array_position(array, element) | Searches for an element in the array,
returns first occurrence. `array_position([1, 2, 2, 3, 4], 2) -> 2` |
-| array_positions(array, element) | Searches for an element in the array,
returns all occurrences. `array_positions([1, 2, 2, 3, 4], 2) -> [2, 3]` |
-| array_prepend(array, element) | Prepends an element to the beginning
of an array. `array_prepend(1, [2, 3, 4]) -> [1, 2, 3, 4]` |
-| array_remove(array, element) | Removes all elements equal to the
given value from the array. |
-| array_replace(array, from, to) | Replaces a specified element with
another specified element. |
-| array_to_string(array, delimeter) | Converts each element to its text
representation. `array_to_string([1, 2, 3, 4], ',') -> 1,2,3,4` |
-| cardinality(array) | Returns the total number of elements
in the array. `cardinality([[1, 2, 3], [4, 5, 6]]) -> 6` |
-| make_array(value1, [value2 [, ...]]) | Returns an Arrow array using the
specified input expressions. `make_array(1, 2, 3) -> [1, 2, 3]` |
-| trim_array(array, n) | Removes the last n elements from the
array. |
+| Function | Notes
|
+| ------------------------------------- |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
+| array_append(array, element) | Appends an element to the end of an
array. `array_append([1, 2, 3], 4) -> [1, 2, 3, 4]`
|
+| array_concat(array[, ..., array_n]) | Concatenates arrays.
`array_concat([1, 2, 3], [4, 5, 6]) -> [1, 2, 3, 4, 5, 6]`
|
+| array_has(array, element) | Returns true if the array contains
the element `array_has([1,2,3], 1) -> true`
|
+| array_has_all(array, sub-array) | Returns true if all elements of
sub-array exist in array `array_has_all([1,2,3], [1,3]) -> true`
|
+| array_has_any(array, sub-array) | Returns true if any elements exist
in both arrays `array_has_any([1,2,3], [1,4]) -> true`
|
+| array_dims(array) | Returns an array of the array's
dimensions. `array_dims([[1, 2, 3], [4, 5, 6]]) -> [2, 3]`
|
+| array_fill(element, array) | Returns an array filled with copies
of the given value.
|
+| array_length(array, dimension) | Returns the length of the array
dimension. `array_length([1, 2, 3, 4, 5]) -> 5`
|
+| array_ndims(array) | Returns the number of dimensions of
the array. `array_ndims([[1, 2, 3], [4, 5, 6]]) -> 2`
|
+| array_position(array, element) | Searches for an element in the
array, returns first occurrence. `array_position([1, 2, 2, 3, 4], 2) -> 2`
|
+| array_positions(array, element) | Searches for an element in the
array, returns all occurrences. `array_positions([1, 2, 2, 3, 4], 2) -> [2, 3]`
|
+| array_prepend(array, element) | Prepends an element to the beginning
of an array. `array_prepend(1, [2, 3, 4]) -> [1, 2, 3, 4]`
|
+| array_remove(array, element) | Removes the first element from the
array equal to the given value. `array_remove([1, 2, 2, 3, 2, 1, 4], 2) -> [1,
2, 3, 2, 1, 4]` |
+| array_remove_n(array, element, max) | Removes the first `max` elements
from the array equal to the given value. `array_remove_n([1, 2, 2, 3, 2, 1, 4],
2, 2) -> [1, 3, 2, 1, 4]` |
+| array_remove_all(array, element) | Removes all elements from the array
equal to the given value. `array_remove_all([1, 2, 2, 3, 2, 1, 4], 2) -> [1, 3,
1, 4]` |
+| array_replace(array, from, to) | Replaces the first occurrence of the
specified element with another specified element. `array_replace([1, 2, 2, 3,
2, 1, 4], 2, 5) -> [1, 5, 2, 3, 2, 1, 4]` |
+| array_replace_n(array, from, to, max) | Replaces the first `max` occurrences
of the specified element with another specified element. `array_replace_n([1,
2, 2, 3, 2, 1, 4], 2, 5, 2) -> [1, 5, 5, 3, 2, 1, 4]` |
+| array_replace_all(array, from, to) | Replaces all occurrences of the
specified element with another specified element. `array_replace_all([1, 2, 2,
3, 2, 1, 4], 2, 5) -> [1, 5, 5, 3, 5, 1, 4]` |
+| array_to_string(array, delimeter) | Converts each element to its text
representation. `array_to_string([1, 2, 3, 4], ',') -> 1,2,3,4`
|
+| cardinality(array) | Returns the total number of elements
in the array. `cardinality([[1, 2, 3], [4, 5, 6]]) -> 6`
|
+| make_array(value1, [value2 [, ...]]) | Returns an Arrow array using the
specified input expressions. `make_array(1, 2, 3) -> [1, 2, 3]`
|
+| trim_array(array, n) | Removes the last n elements from the
array.
|
## Regular Expressions
diff --git a/docs/source/user-guide/sql/scalar_functions.md
b/docs/source/user-guide/sql/scalar_functions.md
index 301f57d031..1e90edc112 100644
--- a/docs/source/user-guide/sql/scalar_functions.md
+++ b/docs/source/user-guide/sql/scalar_functions.md
@@ -1441,7 +1441,11 @@ from_unixtime(expression)
- [array_push_back](#array_push_back)
- [array_push_front](#array_push_front)
- [array_remove](#array_remove)
+- [array_remove_n](#array_remove_n)
+- [array_remove_all](#array_remove_all)
- [array_replace](#array_replace)
+- [array_replace_n](#array_replace_n)
+- [array_replace_all](#array_replace_all)
- [array_to_string](#array_to_string)
- [cardinality](#cardinality)
- [list_append](#list_append)
@@ -1457,6 +1461,12 @@ from_unixtime(expression)
- [list_positions](#list_positions)
- [list_push_back](#list_push_back)
- [list_push_front](#list_push_front)
+- [list_remove](#list_remove)
+- [list_remove_n](#list_remove_n)
+- [list_remove_all](#list_remove_all)
+- [list_replace](#list_replace)
+- [list_replace_n](#list_replace_n)
+- [list_replace_all](#list_replace_all)
- [list_to_string](#list_to_string)
- [make_array](#make_array)
- [make_list](#make_list)
@@ -1783,7 +1793,7 @@ _Alias of [array_prepend](#array_prepend)._
### `array_remove`
-Removes all elements equal to the given value from the array.
+Removes the first element from the array equal to the given value.
```
array_remove(array, element)
@@ -1795,9 +1805,71 @@ array_remove(array, element)
Can be a constant, column, or function, and any combination of array
operators.
- **element**: Element to be removed from the array.
+#### Example
+
+```
+❯ select array_remove([1, 2, 2, 3, 2, 1, 4], 2);
++----------------------------------------------+
+| array_remove(List([1,2,2,3,2,1,4]),Int64(2)) |
++----------------------------------------------+
+| [1, 2, 3, 2, 1, 4] |
++----------------------------------------------+
+```
+
+### `array_remove_n`
+
+Removes the first `max` elements from the array equal to the given value.
+
+```
+array_remove_n(array, element, max)
+```
+
+#### Arguments
+
+- **array**: Array expression.
+ Can be a constant, column, or function, and any combination of array
operators.
+- **element**: Element to be removed from the array.
+- **max**: Number of first occurrences to remove.
+
+#### Example
+
+```
+❯ select array_remove_n([1, 2, 2, 3, 2, 1, 4], 2, 2);
++---------------------------------------------------------+
+| array_remove_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(2)) |
++---------------------------------------------------------+
+| [1, 3, 2, 1, 4] |
++---------------------------------------------------------+
+```
+
+### `array_remove_all`
+
+Removes all elements from the array equal to the given value.
+
+```
+array_remove_all(array, element)
+```
+
+#### Arguments
+
+- **array**: Array expression.
+ Can be a constant, column, or function, and any combination of array
operators.
+- **element**: Element to be removed from the array.
+
+#### Example
+
+```
+❯ select array_remove_all([1, 2, 2, 3, 2, 1, 4], 2);
++--------------------------------------------------+
+| array_remove_all(List([1,2,2,3,2,1,4]),Int64(2)) |
++--------------------------------------------------+
+| [1, 3, 1, 4] |
++--------------------------------------------------+
+```
+
### `array_replace`
-Replaces a specified element with another specified element.
+Replaces the first occurrence of the specified element with another specified
element.
```
array_replace(array, from, to)
@@ -1810,6 +1882,70 @@ array_replace(array, from, to)
- **from**: Initial element.
- **to**: Final element.
+#### Example
+
+```
+❯ select array_replace([1, 2, 2, 3, 2, 1, 4], 2, 5);
++--------------------------------------------------------+
+| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
++--------------------------------------------------------+
+| [1, 5, 2, 3, 2, 1, 4] |
++--------------------------------------------------------+
+```
+
+### `array_replace_n`
+
+Replaces the first `max` occurrences of the specified element with another
specified element.
+
+```
+array_replace_n(array, from, to, max)
+```
+
+#### Arguments
+
+- **array**: Array expression.
+ Can be a constant, column, or function, and any combination of array
operators.
+- **from**: Initial element.
+- **to**: Final element.
+- **max**: Number of first occurrences to replace.
+
+#### Example
+
+```
+❯ select array_replace_n([1, 2, 2, 3, 2, 1, 4], 2, 5, 2);
++-------------------------------------------------------------------+
+| array_replace_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(5),Int64(2)) |
++-------------------------------------------------------------------+
+| [1, 5, 5, 3, 2, 1, 4] |
++-------------------------------------------------------------------+
+```
+
+### `array_replace_all`
+
+Replaces all occurrences of the specified element with another specified
element.
+
+```
+array_replace_all(array, from, to)
+```
+
+#### Arguments
+
+- **array**: Array expression.
+ Can be a constant, column, or function, and any combination of array
operators.
+- **from**: Initial element.
+- **to**: Final element.
+
+#### Example
+
+```
+❯ select array_replace_all([1, 2, 2, 3, 2, 1, 4], 2, 5);
++------------------------------------------------------------+
+| array_replace_all(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
++------------------------------------------------------------+
+| [1, 5, 5, 3, 5, 1, 4] |
++------------------------------------------------------------+
+```
+
### `array_to_string`
Converts each element to its text representation.
@@ -1917,6 +2053,30 @@ _Alias of [array_append](#array_append)._
_Alias of [array_prepend](#array_prepend)._
+### `list_remove`
+
+_Alias of [array_remove](#array_remove)._
+
+### `list_remove_n`
+
+_Alias of [array_remove_n](#array_remove_n)._
+
+### `list_remove_all`
+
+_Alias of [array_remove_all](#array_remove_all)._
+
+### `list_replace`
+
+_Alias of [array_replace](#array_replace)._
+
+### `list_replace_n`
+
+_Alias of [array_replace_n](#array_replace_n)._
+
+### `list_replace_all`
+
+_Alias of [array_replace_all](#array_replace_all)._
+
### `list_to_string`
_Alias of [list_to_string](#list_to_string)._
