This is an automated email from the ASF dual-hosted git repository.
airborne pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new c8b2a2ecf3f [feature](array-func)support array_match_all/any (#40605)
(#43514)
c8b2a2ecf3f is described below
commit c8b2a2ecf3f61b3ccccf4b9cdf60943b29ba96f9
Author: amory <wangqian...@selectdb.com>
AuthorDate: Sat Nov 9 20:20:33 2024 +0800
[feature](array-func)support array_match_all/any (#40605) (#43514)
array_match_all
means that every element in array column should all match filter
according to lambda expr
array_match_any
means that any element in array column match filter according to lambda
expr
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [x] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [x] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../functions/array/function_array_register.cpp | 2 +
.../vec/functions/array/varray_match_function.cpp | 145 +++
.../doris/analysis/LambdaFunctionCallExpr.java | 4 +-
.../doris/catalog/BuiltinScalarFunctions.java | 4 +
.../glue/translator/ExpressionTranslator.java | 7 +-
.../functions/scalar/ArrayMatchAll.java | 68 +
.../functions/scalar/ArrayMatchAny.java | 68 +
.../expressions/visitor/ScalarFunctionVisitor.java | 10 +
.../data/nereids_function_p0/fn_test_am.csv | 102 ++
.../nereids_function_p0/scalar_function/Array.out | 1320 ++++++++++++++++++++
.../suites/nereids_function_p0/load.groovy | 25 +
.../scalar_function/Array.groovy | 42 +
12 files changed, 1789 insertions(+), 8 deletions(-)
diff --git a/be/src/vec/functions/array/function_array_register.cpp
b/be/src/vec/functions/array/function_array_register.cpp
index 6ddf6b6a5e2..071c3198189 100644
--- a/be/src/vec/functions/array/function_array_register.cpp
+++ b/be/src/vec/functions/array/function_array_register.cpp
@@ -56,6 +56,7 @@ void register_function_array_cum_sum(SimpleFunctionFactory&
factory);
void register_function_array_count(SimpleFunctionFactory&);
void register_function_array_filter_function(SimpleFunctionFactory&);
void register_function_array_splits(SimpleFunctionFactory&);
+void register_function_array_match(SimpleFunctionFactory&);
void register_function_array(SimpleFunctionFactory& factory) {
register_function_array_shuffle(factory);
@@ -92,6 +93,7 @@ void register_function_array(SimpleFunctionFactory& factory) {
register_function_array_count(factory);
register_function_array_filter_function(factory);
register_function_array_splits(factory);
+ register_function_array_match(factory);
}
} // namespace doris::vectorized
diff --git a/be/src/vec/functions/array/varray_match_function.cpp
b/be/src/vec/functions/array/varray_match_function.cpp
new file mode 100644
index 00000000000..c322fea7566
--- /dev/null
+++ b/be/src/vec/functions/array/varray_match_function.cpp
@@ -0,0 +1,145 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+#include <vec/functions/simple_function_factory.h>
+
+#include <memory>
+#include <string>
+#include <utility>
+#include <vector>
+
+#include "common/status.h"
+#include "vec/aggregate_functions/aggregate_function.h"
+#include "vec/columns/column.h"
+#include "vec/columns/column_array.h"
+#include "vec/columns/column_nullable.h"
+#include "vec/columns/column_vector.h"
+#include "vec/columns/columns_number.h"
+#include "vec/common/assert_cast.h"
+#include "vec/core/block.h"
+#include "vec/core/column_numbers.h"
+#include "vec/core/column_with_type_and_name.h"
+#include "vec/utils/util.hpp"
+
+namespace doris::vectorized {
+
+///* bool array_match_all/any(array<boolean>) *///
+template <bool MATCH_ALL>
+class ArrayMatchFunction : public IFunction {
+public:
+ static constexpr auto name = MATCH_ALL ? "array_match_all" :
"array_match_any";
+ static FunctionPtr create() { return
std::make_shared<ArrayMatchFunction>(); }
+
+ std::string get_name() const override { return name; }
+
+ bool is_variadic() const override { return false; }
+
+ size_t get_number_of_arguments() const override { return 1; }
+
+ bool is_use_default_implementation_for_constants() const override { return
false; }
+
+ bool use_default_implementation_for_nulls() const override { return false;
}
+
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ return make_nullable(std::make_shared<DataTypeUInt8>());
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) const override
{
+ // here is executed by array_map filtered and arg[0] is bool result
column
+ const auto& [src_column, src_const] =
+ unpack_if_const(block.get_by_position(arguments[0]).column);
+ const ColumnArray* array_column = nullptr;
+ const UInt8* array_null_map = nullptr;
+ if (src_column->is_nullable()) {
+ auto nullable_array = assert_cast<const
ColumnNullable*>(src_column.get());
+ array_column = assert_cast<const
ColumnArray*>(&nullable_array->get_nested_column());
+ array_null_map =
nullable_array->get_null_map_column().get_data().data();
+ } else {
+ array_column = assert_cast<const ColumnArray*>(src_column.get());
+ }
+
+ if (!array_column) {
+ return Status::RuntimeError("unsupported types for function
{}({})", get_name(),
+
block.get_by_position(arguments[0]).type->get_name());
+ }
+
+ const auto& offsets = array_column->get_offsets();
+ ColumnPtr nested_column = nullptr;
+ const UInt8* nested_null_map = nullptr;
+ if (array_column->get_data().is_nullable()) {
+ const auto& nested_null_column =
+ assert_cast<const
ColumnNullable&>(array_column->get_data());
+ nested_null_map =
nested_null_column.get_null_map_column().get_data().data();
+ nested_column = nested_null_column.get_nested_column_ptr();
+ } else {
+ nested_column = array_column->get_data_ptr();
+ }
+
+ if (!nested_column) {
+ return Status::RuntimeError("unsupported types for function
{}({})", get_name(),
+
block.get_by_position(arguments[0]).type->get_name());
+ }
+
+ const auto& nested_data = assert_cast<const
ColumnUInt8&>(*nested_column).get_data();
+
+ // result is nullable bool column for every array column
+ auto result_data_column = ColumnUInt8::create(input_rows_count, 1);
+ auto result_null_column = ColumnUInt8::create(input_rows_count, 0);
+
+ // iterate over all arrays with bool elements
+ for (int row = 0; row < input_rows_count; ++row) {
+ if (array_null_map && array_null_map[row]) {
+ // current array is null, this is always null
+ result_null_column->get_data()[row] = 1;
+ result_data_column->get_data()[row] = 0;
+ } else {
+ // we should calculate the bool result for current array
+ // has_null in current array
+ bool has_null_elem = false;
+ // res for current array
+ bool res_for_array = MATCH_ALL;
+ for (auto off = offsets[row - 1]; off < offsets[row]; ++off) {
+ if (nested_null_map && nested_null_map[off]) {
+ has_null_elem = true;
+ } else {
+ if (nested_data[off] != MATCH_ALL) { // not match
+ res_for_array = !MATCH_ALL;
+ break;
+ } // default is MATCH_ALL
+ }
+ }
+ result_null_column->get_data()[row] = has_null_elem &&
res_for_array == MATCH_ALL;
+ result_data_column->get_data()[row] = res_for_array;
+ }
+ }
+
+ // insert the result column to block
+ DCHECK(block.get_by_position(result).type->is_nullable());
+ ColumnPtr dst_column =
ColumnNullable::create(std::move(result_data_column),
+
std::move(result_null_column));
+ block.replace_by_position(result, std::move(dst_column));
+ return Status::OK();
+ }
+};
+
+void register_function_array_match(SimpleFunctionFactory& factory) {
+ factory.register_function<ArrayMatchFunction<true>>(); // MATCH_ALL = true
means array_match_all
+ factory.register_function<
+ ArrayMatchFunction<false>>(); // MATCH_ALL = false means
array_match_any
+}
+} // namespace doris::vectorized
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/LambdaFunctionCallExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/LambdaFunctionCallExpr.java
index 5fcb62f76ee..707f76f9672 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/analysis/LambdaFunctionCallExpr.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/analysis/LambdaFunctionCallExpr.java
@@ -46,8 +46,8 @@ public class LambdaFunctionCallExpr extends FunctionCallExpr {
public static final ImmutableSet<String> LAMBDA_MAPPED_FUNCTION_SET = new
ImmutableSortedSet.Builder(
String.CASE_INSENSITIVE_ORDER).add("array_exists").add("array_sortby")
.add("array_first_index").add("array_last_index").add("array_first").add("array_last").add("array_count")
- .add("element_at").add("array_split").add("array_reverse_split")
- .build();
+
.add("element_at").add("array_split").add("array_reverse_split").add("array_match_any")
+ .add("array_match_all").build();
private static final Logger LOG =
LogManager.getLogger(LambdaFunctionCallExpr.class);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index 587805ba3a7..b0eaa75e3ca 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -46,6 +46,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayJoin;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayLast;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayLastIndex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMap;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAll;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAny;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMax;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMin;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayPopBack;
@@ -504,6 +506,8 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(ArrayLast.class, "array_last"),
scalar(ArrayLastIndex.class, "array_last_index"),
scalar(ArrayMap.class, "array_map"),
+ scalar(ArrayMatchAll.class, "array_match_all"),
+ scalar(ArrayMatchAny.class, "array_match_any"),
scalar(ArrayMax.class, "array_max"),
scalar(ArrayMin.class, "array_min"),
scalar(ArrayPopBack.class, "array_popback"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
index e876b30280b..c9e49b7535b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
@@ -88,7 +88,6 @@ import
org.apache.doris.nereids.trees.expressions.functions.combinator.UnionComb
import
org.apache.doris.nereids.trees.expressions.functions.generator.TableGeneratingFunction;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ElementAt;
-import
org.apache.doris.nereids.trees.expressions.functions.scalar.HighOrderFunction;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Lambda;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ScalarFunction;
import org.apache.doris.nereids.trees.expressions.functions.udf.JavaUdaf;
@@ -507,11 +506,7 @@ public class ExpressionTranslator extends
DefaultExpressionVisitor<Expr, PlanTra
FunctionCallExpr functionCallExpr;
// create catalog FunctionCallExpr without analyze again
- if (function instanceof HighOrderFunction) {
- functionCallExpr = new LambdaFunctionCallExpr(catalogFunction, new
FunctionParams(false, arguments));
- } else {
- functionCallExpr = new FunctionCallExpr(catalogFunction, new
FunctionParams(false, arguments));
- }
+ functionCallExpr = new FunctionCallExpr(catalogFunction, new
FunctionParams(false, arguments));
functionCallExpr.setNullableFromNereids(function.nullable());
return functionCallExpr;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAll.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAll.java
new file mode 100644
index 00000000000..adc0452b726
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAll.java
@@ -0,0 +1,68 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.expressions.functions.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.ArrayType;
+import org.apache.doris.nereids.types.BooleanType;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'array_match_all'.
+ */
+public class ArrayMatchAll extends ScalarFunction
+ implements HighOrderFunction, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+
FunctionSignature.ret(BooleanType.INSTANCE).args(ArrayType.of(BooleanType.INSTANCE))
+ );
+
+ /**
+ * constructor with arguments.
+ * array_match_all(lambda, a1, ...) = array_match(a1, array_map(lambda,
a1, ...))
+ */
+ public ArrayMatchAll(Expression arg) {
+ super("array_match_all", arg instanceof Lambda ? new ArrayMap(arg) :
arg);
+ }
+
+ @Override
+ public ArrayMatchAll withChildren(List<Expression> children) {
+ if (children.size() != 1) {
+ throw new AnalysisException(
+ String.format("The number of args of %s must be 1 but is
%d", getName(), children.size()));
+ }
+ return new ArrayMatchAll(children.get(0));
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitArrayMatchAll(this, context);
+ }
+
+ @Override
+ public List<FunctionSignature> getImplSignature() {
+ return SIGNATURES;
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAny.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAny.java
new file mode 100644
index 00000000000..bd42bbb3f8c
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayMatchAny.java
@@ -0,0 +1,68 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.expressions.functions.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.ArrayType;
+import org.apache.doris.nereids.types.BooleanType;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'array_match_any'.
+ */
+public class ArrayMatchAny extends ScalarFunction
+ implements HighOrderFunction, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+
FunctionSignature.ret(BooleanType.INSTANCE).args(ArrayType.of(BooleanType.INSTANCE))
+ );
+
+ /**
+ * constructor with arguments.
+ * array_match_any(lambda, a1, ...) = array_match_any(a1,
array_map(lambda, a1, ...))
+ */
+ public ArrayMatchAny(Expression arg) {
+ super("array_match_any", arg instanceof Lambda ? new ArrayMap(arg) :
arg);
+ }
+
+ @Override
+ public ArrayMatchAny withChildren(List<Expression> children) {
+ if (children.size() != 1) {
+ throw new AnalysisException(
+ String.format("The number of args of %s must be 1 but is
%d", getName(), children.size()));
+ }
+ return new ArrayMatchAny(children.get(0));
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitArrayMatchAny(this, context);
+ }
+
+ @Override
+ public List<FunctionSignature> getImplSignature() {
+ return SIGNATURES;
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index 56f1e724650..367e91b0abc 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -46,6 +46,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayIntersec
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayJoin;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayLastIndex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMap;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAll;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAny;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMax;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMin;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayPopBack;
@@ -661,6 +663,14 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(arraySort, context);
}
+ default R visitArrayMatchAll(ArrayMatchAll arrayMatchAll, C context) {
+ return visitScalarFunction(arrayMatchAll, context);
+ }
+
+ default R visitArrayMatchAny(ArrayMatchAny arrayMatchAny, C context) {
+ return visitScalarFunction(arrayMatchAny, context);
+ }
+
default R visitArrayRepeat(ArrayRepeat arrayRepeat, C context) {
return visitScalarFunction(arrayRepeat, context);
}
diff --git a/regression-test/data/nereids_function_p0/fn_test_am.csv
b/regression-test/data/nereids_function_p0/fn_test_am.csv
new file mode 100644
index 00000000000..de0524a3ef6
--- /dev/null
+++ b/regression-test/data/nereids_function_p0/fn_test_am.csv
@@ -0,0 +1,102 @@
+1 ["a", "b", "c"] []
+2 ["a", null, "c"] [1, 2, 3]
+3 [] []
+4 \N [1, null, 3]
+5 [null, null] \N
+6 \N \N
+7 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[1]
+8 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[7]
+9 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[11]
+10 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+11 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
+12 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+13 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[3]
+14 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+15 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+16 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[4]
+17 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[6]
+18 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+19 \N \N
+20 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+21 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+22 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+23 ["a", "b", "c"] []
+24 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[1]
+25 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
+26 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[3]
+27 [] []
+28 [null, null] \N
+29 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[11]
+30 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+31 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[6]
+32 ["a", null, "c"] [1, 2, 3]
+33 \N [1, null, 3]
+34 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[7]
+35 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+36 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[4]
+37 \N \N
+38 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+39 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+40 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+41 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+42 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[1]
+43 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+44 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+45 ["a", "b", "c"] []
+46 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[1]
+47 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
+48 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[3]
+49 \N \N
+50 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+51 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
+52 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[6]
+53 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[7]
+54 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[4]
+55 [] []
+56 [null, null] \N
+57 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[11]
+58 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+59 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[6]
+60 ["a", "b", "c"] []
+61 [] []
+62 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[11]
+63 ["a", null, "c"] [1, 2, 3]
+64 ["a", null, "c"] [1, 2, 3]
+65 \N [1, null, 3]
+66 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[7]
+67 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+68 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[4]
+69 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+70 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[3]
+71 [null, null] \N
+72 \N [1, null, 3]
+73 \N \N
+74 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+75 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+76 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+77 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+78 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[1]
+79 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+80 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+81 \N \N
+82 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[2]
+83 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
+84 \N \N
+85 [] []
+86 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[6]
+87 [] []
+88 \N [1, null, 3]
+89 [null, null] \N
+90 ["a", null, "c"] [1, 2, 3]
+91 \N [1, null, 3]
+92 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[7]
+93 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[5]
+94 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[4]
+95 ["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"]
[10]
+96 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[3]
+97 [null, null] \N
+98 \N [1, null, 3]
+99 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[12]
+100 ["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"]
[9]
+101 ["a", "b", "c"] []
+102 ["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"]
[8]
diff --git a/regression-test/data/nereids_function_p0/scalar_function/Array.out
b/regression-test/data/nereids_function_p0/scalar_function/Array.out
index 1b926318ec7..9570ac178d7 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/Array.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/Array.out
@@ -15576,3 +15576,1323 @@ false
\N
\N
+-- !sql_array_match_any_1 --
+["a", "b", "c"] true
+["a", null, "c"] true
+[] false
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", "b", "c"] true
+[] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["a", null, "c"] true
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+\N \N
+[] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[] false
+\N \N
+[null, null] \N
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", "b", "c"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+
+-- !sql_array_match_all_1 --
+["a", "b", "c"] true
+["a", null, "c"] \N
+[] true
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", "b", "c"] true
+[] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["a", null, "c"] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+\N \N
+[] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[] true
+\N \N
+[null, null] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] true
+["a", "b", "c"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] true
+
+-- !sql_array_match_any_2 --
+["a", "b", "c"] \N
+["a", null, "c"] \N
+[] false
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["a", "b", "c"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["a", "b", "c"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", "b", "c"] \N
+[] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["a", null, "c"] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+\N \N
+[] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[] false
+\N \N
+[null, null] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", "b", "c"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+
+-- !sql_array_match_all_2 --
+["a", "b", "c"] \N
+["a", null, "c"] \N
+[] true
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["a", "b", "c"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["a", "b", "c"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", "b", "c"] \N
+[] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["a", null, "c"] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+\N \N
+[] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[] true
+\N \N
+[null, null] \N
+["a", null, "c"] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] \N
+["a", "b", "c"] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] \N
+
+-- !sql_array_match_any_3 --
+[] false
+[1, 2, 3] \N
+[] false
+[1, null, 3] \N
+\N \N
+\N \N
+[1] \N
+[7] \N
+[11] \N
+[2] \N
+[8] \N
+[12] \N
+[3] \N
+[5] \N
+[9] \N
+[4] \N
+[6] \N
+[10] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[] false
+[1] \N
+[8] \N
+[3] \N
+[] false
+\N \N
+[11] \N
+[9] \N
+[6] \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[2] \N
+[1] \N
+[9] \N
+[5] \N
+[] false
+[1] \N
+[8] \N
+[3] \N
+\N \N
+[12] \N
+[8] \N
+[6] \N
+[7] \N
+[4] \N
+[] false
+\N \N
+[11] \N
+[9] \N
+[6] \N
+[] false
+[] false
+[11] \N
+[1, 2, 3] \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+[10] \N
+[3] \N
+\N \N
+[1, null, 3] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[2] \N
+[1] \N
+[9] \N
+[5] \N
+\N \N
+[2] \N
+[8] \N
+\N \N
+[] false
+[6] \N
+[] false
+[1, null, 3] \N
+\N \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+[10] \N
+[3] \N
+\N \N
+[1, null, 3] \N
+[12] \N
+[9] \N
+[] false
+[8] \N
+
+-- !sql_array_match_all_3 --
+[] true
+[1, 2, 3] \N
+[] true
+[1, null, 3] \N
+\N \N
+\N \N
+[1] \N
+[7] \N
+[11] \N
+[2] \N
+[8] \N
+[12] \N
+[3] \N
+[5] \N
+[9] \N
+[4] \N
+[6] \N
+[10] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[] true
+[1] \N
+[8] \N
+[3] \N
+[] true
+\N \N
+[11] \N
+[9] \N
+[6] \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[2] \N
+[1] \N
+[9] \N
+[5] \N
+[] true
+[1] \N
+[8] \N
+[3] \N
+\N \N
+[12] \N
+[8] \N
+[6] \N
+[7] \N
+[4] \N
+[] true
+\N \N
+[11] \N
+[9] \N
+[6] \N
+[] true
+[] true
+[11] \N
+[1, 2, 3] \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+[10] \N
+[3] \N
+\N \N
+[1, null, 3] \N
+\N \N
+[2] \N
+[12] \N
+[10] \N
+[2] \N
+[1] \N
+[9] \N
+[5] \N
+\N \N
+[2] \N
+[8] \N
+\N \N
+[] true
+[6] \N
+[] true
+[1, null, 3] \N
+\N \N
+[1, 2, 3] \N
+[1, null, 3] \N
+[7] \N
+[5] \N
+[4] \N
+[10] \N
+[3] \N
+\N \N
+[1, null, 3] \N
+[12] \N
+[9] \N
+[] true
+[8] \N
+
+-- !sql_array_match_any_4 --
+["a", "b", "c"] true
+["a", null, "c"] true
+[] false
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["a", "b", "c"] true
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+[] false
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", "b", "c"] true
+[] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["a", null, "c"] true
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+\N \N
+[] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[] false
+\N \N
+[null, null] \N
+["a", null, "c"] true
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", "b", "c"] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+
+-- !sql_array_match_all_4 --
+["a", "b", "c"] false
+["a", null, "c"] false
+[] true
+\N \N
+[null, null] \N
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["a", "b", "c"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", null, "c"] false
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["a", "b", "c"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+[] true
+[null, null] \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", "b", "c"] false
+[] true
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["a", null, "c"] false
+["a", null, "c"] false
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[null, null] \N
+\N \N
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+\N \N
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+\N \N
+[] true
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[] true
+\N \N
+[null, null] \N
+["a", null, "c"] false
+\N \N
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char21", "char31", "varchar11", "varchar21", "varchar31", "string1"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+[null, null] \N
+\N \N
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["char23", "char33", "varchar13", "varchar23", "varchar33", "string3"] false
+["a", "b", "c"] false
+["char22", "char32", "varchar12", "varchar22", "varchar32", "string2"] false
+
+-- !sql_array_match_any_5 --
+[] false
+[1, 2, 3] true
+[] false
+[1, null, 3] \N
+\N \N
+\N \N
+[1] false
+[7] false
+[11] false
+[2] true
+[8] false
+[12] false
+[3] false
+[5] false
+[9] false
+[4] false
+[6] false
+[10] false
+\N \N
+[2] true
+[12] false
+[10] false
+[] false
+[1] false
+[8] false
+[3] false
+[] false
+\N \N
+[11] false
+[9] false
+[6] false
+[1, 2, 3] true
+[1, null, 3] \N
+[7] false
+[5] false
+[4] false
+\N \N
+[2] true
+[12] false
+[10] false
+[2] true
+[1] false
+[9] false
+[5] false
+[] false
+[1] false
+[8] false
+[3] false
+\N \N
+[12] false
+[8] false
+[6] false
+[7] false
+[4] false
+[] false
+\N \N
+[11] false
+[9] false
+[6] false
+[] false
+[] false
+[11] false
+[1, 2, 3] true
+[1, 2, 3] true
+[1, null, 3] \N
+[7] false
+[5] false
+[4] false
+[10] false
+[3] false
+\N \N
+[1, null, 3] \N
+\N \N
+[2] true
+[12] false
+[10] false
+[2] true
+[1] false
+[9] false
+[5] false
+\N \N
+[2] true
+[8] false
+\N \N
+[] false
+[6] false
+[] false
+[1, null, 3] \N
+\N \N
+[1, 2, 3] true
+[1, null, 3] \N
+[7] false
+[5] false
+[4] false
+[10] false
+[3] false
+\N \N
+[1, null, 3] \N
+[12] false
+[9] false
+[] false
+[8] false
+
+-- !sql_array_match_all_5 --
+[] true
+[1, 2, 3] false
+[] true
+[1, null, 3] false
+\N \N
+\N \N
+[1] false
+[7] false
+[11] false
+[2] true
+[8] false
+[12] false
+[3] false
+[5] false
+[9] false
+[4] false
+[6] false
+[10] false
+\N \N
+[2] true
+[12] false
+[10] false
+[] true
+[1] false
+[8] false
+[3] false
+[] true
+\N \N
+[11] false
+[9] false
+[6] false
+[1, 2, 3] false
+[1, null, 3] false
+[7] false
+[5] false
+[4] false
+\N \N
+[2] true
+[12] false
+[10] false
+[2] true
+[1] false
+[9] false
+[5] false
+[] true
+[1] false
+[8] false
+[3] false
+\N \N
+[12] false
+[8] false
+[6] false
+[7] false
+[4] false
+[] true
+\N \N
+[11] false
+[9] false
+[6] false
+[] true
+[] true
+[11] false
+[1, 2, 3] false
+[1, 2, 3] false
+[1, null, 3] false
+[7] false
+[5] false
+[4] false
+[10] false
+[3] false
+\N \N
+[1, null, 3] false
+\N \N
+[2] true
+[12] false
+[10] false
+[2] true
+[1] false
+[9] false
+[5] false
+\N \N
+[2] true
+[8] false
+\N \N
+[] true
+[6] false
+[] true
+[1, null, 3] false
+\N \N
+[1, 2, 3] false
+[1, null, 3] false
+[7] false
+[5] false
+[4] false
+[10] false
+[3] false
+\N \N
+[1, null, 3] false
+[12] false
+[9] false
+[] true
+[8] false
+
+-- !sql_array_match_any_6 --
+[] false
+[1, 2, 3] true
+[] false
+[1, null, 3] true
+\N \N
+\N \N
+[1] true
+[7] true
+[11] true
+[2] false
+[8] true
+[12] true
+[3] true
+[5] true
+[9] true
+[4] true
+[6] true
+[10] true
+\N \N
+[2] false
+[12] true
+[10] true
+[] false
+[1] true
+[8] true
+[3] true
+[] false
+\N \N
+[11] true
+[9] true
+[6] true
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+\N \N
+[2] false
+[12] true
+[10] true
+[2] false
+[1] true
+[9] true
+[5] true
+[] false
+[1] true
+[8] true
+[3] true
+\N \N
+[12] true
+[8] true
+[6] true
+[7] true
+[4] true
+[] false
+\N \N
+[11] true
+[9] true
+[6] true
+[] false
+[] false
+[11] true
+[1, 2, 3] true
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+[10] true
+[3] true
+\N \N
+[1, null, 3] true
+\N \N
+[2] false
+[12] true
+[10] true
+[2] false
+[1] true
+[9] true
+[5] true
+\N \N
+[2] false
+[8] true
+\N \N
+[] false
+[6] true
+[] false
+[1, null, 3] true
+\N \N
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+[10] true
+[3] true
+\N \N
+[1, null, 3] true
+[12] true
+[9] true
+[] false
+[8] true
+
+-- !sql_array_match_all_6 --
+[] false
+[1, 2, 3] true
+[] false
+[1, null, 3] true
+\N \N
+\N \N
+[1] true
+[7] true
+[11] true
+[2] false
+[8] true
+[12] true
+[3] true
+[5] true
+[9] true
+[4] true
+[6] true
+[10] true
+\N \N
+[2] false
+[12] true
+[10] true
+[] false
+[1] true
+[8] true
+[3] true
+[] false
+\N \N
+[11] true
+[9] true
+[6] true
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+\N \N
+[2] false
+[12] true
+[10] true
+[2] false
+[1] true
+[9] true
+[5] true
+[] false
+[1] true
+[8] true
+[3] true
+\N \N
+[12] true
+[8] true
+[6] true
+[7] true
+[4] true
+[] false
+\N \N
+[11] true
+[9] true
+[6] true
+[] false
+[] false
+[11] true
+[1, 2, 3] true
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+[10] true
+[3] true
+\N \N
+[1, null, 3] true
+\N \N
+[2] false
+[12] true
+[10] true
+[2] false
+[1] true
+[9] true
+[5] true
+\N \N
+[2] false
+[8] true
+\N \N
+[] false
+[6] true
+[] false
+[1, null, 3] true
+\N \N
+[1, 2, 3] true
+[1, null, 3] true
+[7] true
+[5] true
+[4] true
+[10] true
+[3] true
+\N \N
+[1, null, 3] true
+[12] true
+[9] true
+[] false
+[8] true
+
+-- !sql_array_match_any_7 --
+\N
+
+-- !sql_array_match_all_7 --
+\N
+
+-- !sql_array_match_any_8 --
+\N
+
+-- !sql_array_match_all_8 --
+\N
+
+-- !sql_array_match_any_9 --
+false
+
+-- !sql_array_match_all_9 --
+true
+
+-- !sql_array_match_any_10 --
+\N
+
+-- !sql_array_match_all_10 --
+\N
+
+-- !sql_array_match_any_11 --
+true
+
+-- !sql_array_match_all_11 --
+false
+
+-- !sql_array_match_any_12 --
+true
+
+-- !sql_array_match_all_12 --
+false
+
+-- !sql_array_match_any_13 --
+\N
+
+-- !sql_array_match_all_13 --
+false
+
+-- !sql_array_match_any_14 --
+false
+
+-- !sql_array_match_all_14 --
+true
+
+-- !sql_array_match_any_15 --
+\N
+
+-- !sql_array_match_all_15 --
+\N
+
+-- !sql_array_match_any_16 --
+true
+
+-- !sql_array_match_all_16 --
+false
+
+-- !sql_array_match_any_17 --
+\N
+
+-- !sql_array_match_all_17 --
+false
+
+-- !sql_array_match_any_18 --
+false
+
+-- !sql_array_match_all_18 --
+true
+
diff --git a/regression-test/suites/nereids_function_p0/load.groovy
b/regression-test/suites/nereids_function_p0/load.groovy
index 2e0fc1e953e..47a5da12d0f 100644
--- a/regression-test/suites/nereids_function_p0/load.groovy
+++ b/regression-test/suites/nereids_function_p0/load.groovy
@@ -275,6 +275,7 @@ suite("load") {
db "regression_test_nereids_function_p0"
set 'column_separator', ';'
file "test_array_large_decimal.csv"
+
time 60000
check { result, exception, startTime, endTime ->
@@ -287,4 +288,28 @@ suite("load") {
assertEquals(100, json.NumberLoadedRows)
}
}
+
+
+ // array_match_any && array_match_all
+ sql """ drop table if exists fn_test_am """
+ sql """ CREATE TABLE IF NOT EXISTS fn_test_am (id int, kastr
array<string>, kaint array<int>) engine=olap
+
DISTRIBUTED BY HASH(`id`) BUCKETS 4
+
properties("replication_num" = "1") """
+ streamLoad {
+ table "fn_test_am"
+ db "regression_test_nereids_function_p0"
+ file "fn_test_am.csv"
+ time 60000
+
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals(102, json.NumberTotalRows)
+ assertEquals(102, json.NumberLoadedRows)
+ }
+ }
+
}
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/Array.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/Array.groovy
index 652cb4f5c9b..3da1225ca81 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/Array.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/Array.groovy
@@ -1364,4 +1364,46 @@ suite("nereids_scalar_fn_Array") {
order_qt_sql_array_overlaps_4 """select arrays_overlap(c, a) from
fn_test_array_with_large_decimal order by id"""
order_qt_sql_array_overlaps_5 """select arrays_overlap(b, c) from
fn_test_array_with_large_decimal order by id"""
order_qt_sql_array_overlaps_6 """select arrays_overlap(c, b) from
fn_test_array_with_large_decimal order by id"""
+
+ // array_match_any && array_match_all
+ // for table
+ qt_sql_array_match_any_1 "select kastr, array_match_any(x->x!='',
kastr) from fn_test_am order by id"
+ qt_sql_array_match_all_1 "select kastr, array_match_all(x->x!='',
kastr) from fn_test_am order by id"
+ qt_sql_array_match_any_2 "select kastr, array_match_any(x->x!=null,
kastr) from fn_test_am order by id"
+ qt_sql_array_match_all_2 "select kastr, array_match_all(x->x!=null,
kastr) from fn_test_am order by id"
+ qt_sql_array_match_any_3 "select kaint, array_match_any(x->x!=null,
kaint) from fn_test_am order by id"
+ qt_sql_array_match_all_3 "select kaint, array_match_all(x->x!=null,
kaint) from fn_test_am order by id"
+ qt_sql_array_match_any_4 "select kastr, array_match_any(x->x='a',
kastr) from fn_test_am order by id"
+ qt_sql_array_match_all_4 "select kastr, array_match_all(x->x='a',
kastr) from fn_test_am order by id"
+ qt_sql_array_match_any_5 "select kaint, array_match_any(x->x=2, kaint)
from fn_test_am order by id"
+ qt_sql_array_match_all_5 "select kaint, array_match_all(x->x=2, kaint)
from fn_test_am order by id"
+ qt_sql_array_match_any_6 "select kaint, array_match_any(x->x!=2, kaint)
from fn_test_am order by id"
+ qt_sql_array_match_all_6 "select kaint, array_match_any(x->x!=2, kaint)
from fn_test_am order by id"
+
+ // for literal
+ qt_sql_array_match_any_7 "select array_match_any(x->x!=null, array('a',
'b', 'c'))"
+ qt_sql_array_match_all_7 "select array_match_all(x->x!=null, array('a',
'b', 'c'))"
+ qt_sql_array_match_any_8 "select array_match_any(x->x!=null, array('a',
null, 'c'))"
+ qt_sql_array_match_all_8 "select array_match_all(x->x!=null, array('a',
null, 'c'))"
+ qt_sql_array_match_any_9 "select array_match_any(x->x!=null, array())"
+ qt_sql_array_match_all_9 "select array_match_all(x->x!=null, array())"
+ qt_sql_array_match_any_10 "select array_match_any(x->x!=null,
array(null, null))"
+ qt_sql_array_match_all_10 "select array_match_all(x->x!=null,
array(null, null))"
+ qt_sql_array_match_any_11 "select array_match_any(x->x='a', array('a',
'b', 'c'))"
+ qt_sql_array_match_all_11 "select array_match_all(x->x='a', array('a',
'b', 'c'))"
+ qt_sql_array_match_any_12 "select array_match_any(x->x=2, array(1, 2,
3))"
+ qt_sql_array_match_all_12 "select array_match_all(x->x=2, array(1, 2,
3))"
+ qt_sql_array_match_any_13 "select array_match_any(x->x=2, array(1,
null, 3))"
+ qt_sql_array_match_all_13 "select array_match_all(x->x=2, array(1,
null, 3))"
+ qt_sql_array_match_any_14 "select array_match_any(x->x=2, array())"
+ qt_sql_array_match_all_14 "select array_match_all(x->x=2, array())"
+ qt_sql_array_match_any_15 "select array_match_any(x->x=2, array(null,
null))"
+ qt_sql_array_match_all_15 "select array_match_all(x->x=2, array(null,
null))"
+ qt_sql_array_match_any_16 "select array_match_any(x->x=2, array(1, 2,
3))"
+ qt_sql_array_match_all_16 "select array_match_all(x->x=2, array(1, 2,
3))"
+ qt_sql_array_match_any_17 "select array_match_any(x->x=2, array(1,
null, 3))"
+ qt_sql_array_match_all_17 "select array_match_all(x->x=2, array(1,
null, 3))"
+ qt_sql_array_match_any_18 "select array_match_any(x->x=2, array())"
+ qt_sql_array_match_all_18 "select array_match_all(x->x=2, array())"
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
