This is an automated email from the ASF dual-hosted git repository.
Mryange pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 4ab2cd71095 [feature](function) Add array_except_all scalar function
(#67132)
4ab2cd71095 is described below
commit 4ab2cd710954a279c931581a294113736a6eda23
Author: Mryange <[email protected]>
AuthorDate: Fri Sep 4 14:42:20 2026 +0800
[feature](function) Add array_except_all scalar function (#67132)
Problem Summary: Add array_except_all for ARRAY<scalar> arguments. The
function applies multiset difference semantics, preserves unmatched
left-side duplicates and order, handles nullable elements and constant
columns through ColumnArrayView, and rejects complex element types. Add
BE unit coverage and regression coverage for scalar types, nulls,
constants, duplicates, and unsupported nested types.
doc https://github.com/apache/doris-website/pull/4089
---
.../function/array/function_array_except_all.cpp | 155 +++++++++++++++++++++
.../function/array/function_array_register.cpp | 2 +
be/src/exprs/function/function_format.cpp | 1 +
.../function/function_array_except_all_test.cpp | 102 ++++++++++++++
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../functions/scalar/ArrayExceptAll.java | 83 +++++++++++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 +
.../array_functions/test_array_except_all.out | 72 ++++++++++
.../array_functions/test_array_except_all.groovy | 139 ++++++++++++++++++
9 files changed, 561 insertions(+)
diff --git a/be/src/exprs/function/array/function_array_except_all.cpp
b/be/src/exprs/function/array/function_array_except_all.cpp
new file mode 100644
index 00000000000..cc6823a0062
--- /dev/null
+++ b/be/src/exprs/function/array/function_array_except_all.cpp
@@ -0,0 +1,155 @@
+// 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 <type_traits>
+
+#include "core/assert_cast.h"
+#include "core/call_on_type_index.h"
+#include "core/column/column_array.h"
+#include "core/column/column_array_view.h"
+#include "core/column/column_decimal.h"
+#include "core/column/column_nullable.h"
+#include "core/column/column_string.h"
+#include "core/column/column_vector.h"
+#include "core/data_type/data_type_array.h"
+#include "core/data_type/primitive_type.h"
+#include "core/string_ref.h"
+#include "exec/common/hash_table/phmap_fwd_decl.h"
+#include "exprs/function/function.h"
+#include "exprs/function/simple_function_factory.h"
+
+namespace doris {
+
+template <PrimitiveType PType>
+struct ArrayExceptAllCountMap {
+ using ElementType = typename ColumnElementView<PType>::ElementType;
+ using KeyType = typename NativeType<ElementType>::Type;
+ using Type = doris::flat_hash_map<KeyType, size_t>;
+};
+
+template <>
+struct ArrayExceptAllCountMap<TYPE_STRING> {
+ using Type = doris::flat_hash_map<StringRef, size_t, StringRefHash>;
+};
+
+class FunctionArrayExceptAll : public IFunction {
+public:
+ static constexpr auto name = "array_except_all";
+ static FunctionPtr create() { return
std::make_shared<FunctionArrayExceptAll>(); }
+
+ String get_name() const override { return name; }
+ bool is_variadic() const override { return false; }
+ size_t get_number_of_arguments() const override { return 2; }
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ return arguments[0];
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ uint32_t result, size_t input_rows_count) const
override {
+ const auto& left_column = block.get_by_position(arguments[0]).column;
+ const auto& right_column = block.get_by_position(arguments[1]).column;
+
+ const auto& array_type =
+ assert_cast<const
DataTypeArray&>(*block.get_by_position(arguments[0]).type);
+
+ ColumnPtr result_column;
+ auto execute = [&](const auto& type) -> bool {
+ using DispatchType = std::decay_t<decltype(type)>;
+ constexpr PrimitiveType PType = DispatchType::PType;
+ result_column =
execute_internal(ColumnArrayView<PType>::create(left_column),
+
ColumnArrayView<PType>::create(right_column),
+
array_type.get_nested_type()->create_column());
+ return true;
+ };
+ if
(!dispatch_switch_all(array_type.get_nested_type()->get_primitive_type(),
execute)) {
+ return Status::InvalidArgument("function {} does not support
element type {}",
+ get_name(),
array_type.get_nested_type()->get_name());
+ }
+
+ DCHECK_EQ(result_column->size(), input_rows_count);
+ block.replace_by_position(result, std::move(result_column));
+ return Status::OK();
+ }
+
+private:
+ template <PrimitiveType PType>
+ static ColumnPtr execute_internal(const ColumnArrayView<PType>& left_view,
+ const ColumnArrayView<PType>& right_view,
+ MutableColumnPtr result_data) {
+ using CountMap = typename ArrayExceptAllCountMap<PType>::Type;
+ using ResultColumn = typename PrimitiveTypeTraits<PType>::ColumnType;
+
+ auto& result_nullable = assert_cast<ColumnNullable&>(*result_data);
+ auto& result_values =
assert_cast<ResultColumn&>(result_nullable.get_nested_column());
+ auto& result_null_map = result_nullable.get_null_map_data();
+ auto result_offsets_column = ColumnArray::ColumnOffsets::create();
+ auto& result_offsets = result_offsets_column->get_data();
+ result_offsets.reserve(left_view.size());
+
+ CountMap counts;
+ size_t null_count = 0;
+ size_t result_offset = 0;
+ for (size_t row = 0; row < left_view.size(); ++row) {
+ const auto right_array = right_view[row];
+ for (size_t pos = 0; pos < right_array.size(); ++pos) {
+ if (right_array.is_null_at(pos)) {
+ ++null_count;
+ } else {
+ ++counts[right_array.value_at(pos)];
+ }
+ }
+
+ const auto left_array = left_view[row];
+ for (size_t pos = 0; pos < left_array.size(); ++pos) {
+ if (left_array.is_null_at(pos)) {
+ if (null_count > 0) {
+ --null_count;
+ } else {
+ result_values.insert_default();
+ result_null_map.push_back(1);
+ ++result_offset;
+ }
+ } else {
+ const auto value = left_array.value_at(pos);
+ auto count = counts.find(value);
+ if (count != counts.end() && count->second > 0) {
+ --count->second;
+ } else {
+ if constexpr (is_string_type(PType)) {
+ result_values.insert_data(value.data, value.size);
+ } else {
+ result_values.get_data().push_back(value);
+ }
+ result_null_map.push_back(0);
+ ++result_offset;
+ }
+ }
+ }
+ result_offsets.push_back(result_offset);
+ counts.clear();
+ null_count = 0;
+ }
+
+ return ColumnArray::create(std::move(result_data),
std::move(result_offsets_column));
+ }
+};
+
+void register_function_array_except_all(SimpleFunctionFactory& factory) {
+ factory.register_function<FunctionArrayExceptAll>();
+}
+
+} // namespace doris
diff --git a/be/src/exprs/function/array/function_array_register.cpp
b/be/src/exprs/function/array/function_array_register.cpp
index 75c1401dd95..08e4f7ac136 100644
--- a/be/src/exprs/function/array/function_array_register.cpp
+++ b/be/src/exprs/function/array/function_array_register.cpp
@@ -35,6 +35,7 @@ void register_function_array_sortby(SimpleFunctionFactory&);
void register_function_arrays_overlap(SimpleFunctionFactory&);
void register_function_array_union(SimpleFunctionFactory&);
void register_function_array_except(SimpleFunctionFactory&);
+void register_function_array_except_all(SimpleFunctionFactory&);
void register_function_array_intersect(SimpleFunctionFactory&);
void register_function_array_slice(SimpleFunctionFactory&);
void register_function_array_difference(SimpleFunctionFactory&);
@@ -74,6 +75,7 @@ void register_function_array(SimpleFunctionFactory& factory) {
register_function_arrays_overlap(factory);
register_function_array_union(factory);
register_function_array_except(factory);
+ register_function_array_except_all(factory);
register_function_array_intersect(factory);
register_function_array_slice(factory);
register_function_array_difference(factory);
diff --git a/be/src/exprs/function/function_format.cpp
b/be/src/exprs/function/function_format.cpp
index cc88de71c7d..31a45229357 100644
--- a/be/src/exprs/function/function_format.cpp
+++ b/be/src/exprs/function/function_format.cpp
@@ -26,6 +26,7 @@
#include "core/column/column.h"
#include "core/column/column_vector.h"
#include "core/data_type/data_type_number.h"
+#include "core/data_type/data_type_string.h"
#include "core/data_type/define_primitive_type.h"
#include "core/types.h"
#include "exprs/function/cast_type_to_either.h"
diff --git a/be/test/exprs/function/function_array_except_all_test.cpp
b/be/test/exprs/function/function_array_except_all_test.cpp
new file mode 100644
index 00000000000..abdc7576a59
--- /dev/null
+++ b/be/test/exprs/function/function_array_except_all_test.cpp
@@ -0,0 +1,102 @@
+// 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 <memory>
+#include <string>
+#include <vector>
+
+#include "core/block/block.h"
+#include "core/column/column_const.h"
+#include "core/data_type/data_type_array.h"
+#include "core/data_type/data_type_nullable.h"
+#include "core/data_type/data_type_number.h"
+#include "core/data_type/data_type_string.h"
+#include "exprs/function/function_test_util.h"
+#include "exprs/function/simple_function_factory.h"
+
+namespace doris {
+
+namespace {
+
+void check_array_except_all(const DataTypePtr& array_type, const TestArray&
left,
+ const TestArray& right, const TestArray& expected,
bool left_const,
+ bool right_const) {
+ MutableColumnPtr left_column = array_type->create_column();
+ ASSERT_TRUE(insert_cell(left_column, array_type, left));
+ MutableColumnPtr right_column = array_type->create_column();
+ ASSERT_TRUE(insert_cell(right_column, array_type, right));
+
+ constexpr size_t row_count = 1;
+ if (left_const) {
+ left_column = ColumnConst::create(std::move(left_column), row_count);
+ }
+ if (right_const) {
+ right_column = ColumnConst::create(std::move(right_column), row_count);
+ }
+
+ Block block;
+ block.insert({std::move(left_column), array_type, "left"});
+ block.insert({std::move(right_column), array_type, "right"});
+ auto function = SimpleFunctionFactory::instance().get_function(
+ "array_except_all", block.get_columns_with_type_and_name(),
array_type);
+ ASSERT_NE(function, nullptr);
+
+ FunctionUtils function_utils(array_type, {array_type, array_type}, false);
+ auto* context = function_utils.get_fn_ctx();
+ ASSERT_TRUE(function->open(context, FunctionContext::FRAGMENT_LOCAL).ok());
+ ASSERT_TRUE(function->open(context, FunctionContext::THREAD_LOCAL).ok());
+ block.insert({nullptr, array_type, "result"});
+ ASSERT_TRUE(function->execute(context, block, {0, 1}, 2, row_count).ok());
+ ASSERT_TRUE(function->close(context, FunctionContext::THREAD_LOCAL).ok());
+ ASSERT_TRUE(function->close(context,
FunctionContext::FRAGMENT_LOCAL).ok());
+
+ MutableColumnPtr expected_column = array_type->create_column();
+ ASSERT_TRUE(insert_cell(expected_column, array_type, expected));
+ Field actual_value;
+ block.get_by_position(2).column->get(0, actual_value);
+ Field expected_value;
+ expected_column->get(0, expected_value);
+ EXPECT_EQ(actual_value, expected_value);
+}
+
+} // namespace
+
+TEST(function_array_except_all_test, integer_multiset_semantics) {
+ auto array_type =
+
std::make_shared<DataTypeArray>(make_nullable(std::make_shared<DataTypeInt32>()));
+ check_array_except_all(array_type, {Int32(1), Int32(1), Int32(2)},
{Int32(1)},
+ {Int32(1), Int32(2)}, false, false);
+ check_array_except_all(array_type, {Int32(1), Int32(1)}, {Int32(1),
Int32(1), Int32(1)}, {},
+ true, false);
+ check_array_except_all(array_type, {Int32(1), Int32(2)}, {}, {Int32(1),
Int32(2)}, false, true);
+}
+
+TEST(function_array_except_all_test, null_and_string_counts) {
+ auto integer_array_type =
+
std::make_shared<DataTypeArray>(make_nullable(std::make_shared<DataTypeInt32>()));
+ check_array_except_all(integer_array_type, {Null(), Null(), Int32(1)},
{Null()},
+ {Null(), Int32(1)}, false, false);
+ check_array_except_all(integer_array_type, {Null()}, {Null(), Null()}, {},
false, false);
+
+ auto string_array_type =
+
std::make_shared<DataTypeArray>(make_nullable(std::make_shared<DataTypeString>()));
+ check_array_except_all(string_array_type,
+ {std::string("a"), std::string("a"),
std::string("b")},
+ {std::string("a")}, {std::string("a"),
std::string("b")}, false, false);
+}
+
+} // namespace doris
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 e55e1008762..f7a11154cd0 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
@@ -53,6 +53,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayDistinct
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayEnumerate;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayEnumerateUniq;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExcept;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExceptAll;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExists;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayFilter;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayFirst;
@@ -657,6 +658,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(ArrayEnumerate.class, "array_enumerate"),
scalar(ArrayEnumerateUniq.class, "array_enumerate_uniq"),
scalar(ArrayExcept.class, "array_except"),
+ scalar(ArrayExceptAll.class, "array_except_all"),
scalar(ArrayFlatten.class, "array_flatten"),
scalar(ArrayIntersect.class, "array_intersect"),
scalar(ArrayJoin.class, "array_join"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayExceptAll.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayExceptAll.java
new file mode 100644
index 00000000000..8624672a76f
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArrayExceptAll.java
@@ -0,0 +1,83 @@
+// 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.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.ArrayType;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.coercion.AnyDataType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/** Scalar function array_except_all. */
+public class ArrayExceptAll extends ScalarFunction implements
ExplicitlyCastableSignature,
+ BinaryExpression, PropagateNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.retArgType(0)
+ .args(ArrayType.of(new AnyDataType(0)), ArrayType.of(new
AnyDataType(0)))
+ );
+
+ public ArrayExceptAll(Expression arg0, Expression arg1) {
+ super("array_except_all", arg0, arg1);
+ }
+
+ private ArrayExceptAll(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public ArrayExceptAll withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ return new ArrayExceptAll(getFunctionParams(children));
+ }
+
+ @Override
+ public void checkLegalityBeforeTypeCoercion() {
+ for (Expression argument : getArguments()) {
+ DataType argumentType = argument.getDataType();
+ if (!argumentType.isArrayType()) {
+ continue;
+ }
+ DataType itemType = ((ArrayType) argumentType).getItemType();
+ if (itemType.isComplexType() || itemType.isVariantType() ||
itemType.isJsonType()) {
+ throw new AnalysisException("array_except_all does not support
types: "
+ + argumentType.toSql());
+ }
+ }
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitArrayExceptAll(this, context);
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ 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 ec86766df02..afc313b7ee9 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
@@ -54,6 +54,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayDistinct
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayEnumerate;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayEnumerateUniq;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExcept;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExceptAll;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayExists;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayFilter;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayFirst;
@@ -710,6 +711,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(arrayExcept, context);
}
+ default R visitArrayExceptAll(ArrayExceptAll arrayExceptAll, C context) {
+ return visitScalarFunction(arrayExceptAll, context);
+ }
+
default R visitArrayExists(ArrayExists arrayExists, C context) {
return visitScalarFunction(arrayExists, context);
}
diff --git
a/regression-test/data/query_p0/sql_functions/array_functions/test_array_except_all.out
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_except_all.out
new file mode 100644
index 00000000000..aaf64fdba1f
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_except_all.out
@@ -0,0 +1,72 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !partial_cancel --
+["a", "b"]
+
+-- !preserve_duplicates --
+["a", "a", "b"]
+
+-- !saturating_cancel --
+[]
+
+-- !null_cancel --
+[null, "a", "a"]
+
+-- !null_saturating_cancel --
+[]
+
+-- !empty_left --
+[]
+
+-- !empty_right --
+[1, 1, 2]
+
+-- !null_left --
+\N
+
+-- !null_right --
+\N
+
+-- !implicit_type_coercion --
+[1, 2]
+
+-- !decimal --
+[1.25, 2.50]
+
+-- !date --
+["2026-08-25", "2026-08-26"]
+
+-- !datetime --
+["2026-08-25 10:00:00"]
+
+-- !ipv4 --
+["192.168.0.1", "192.168.0.2"]
+
+-- !ipv6 --
+["2001:db8::1", "2001:db8::2"]
+
+-- !composed --
+2
+
+-- !compare_set_semantics --
+["b"] ["a", "b"]
+
+-- !column_arguments --
+1 [1, 2] ["a", "b"]
+2 [1, 1] ["a", "a"]
+3 [] []
+4 [1, 2] ["a", "b"]
+5 \N \N
+
+-- !left_constant --
+1 [1, 2]
+2 [1, 1, 2]
+3 [1, 2]
+4 [1, 1, 2]
+5 [1, 2]
+
+-- !right_constant --
+1 [1, 2]
+2 [null, 1]
+3 []
+4 [2]
+5 \N
diff --git
a/regression-test/suites/query_p0/sql_functions/array_functions/test_array_except_all.groovy
b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_except_all.groovy
new file mode 100644
index 00000000000..b12d7457b38
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_except_all.groovy
@@ -0,0 +1,139 @@
+// 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.
+
+suite("test_array_except_all") {
+ order_qt_partial_cancel """
+ select array_sort(array_except_all(['a', 'a', 'b'], ['a']))
+ """
+ order_qt_preserve_duplicates """
+ select array_sort(array_except_all(['a', 'a', 'b'], ['c']))
+ """
+ order_qt_saturating_cancel """
+ select array_sort(array_except_all(['a', 'a'], ['a', 'a', 'a']))
+ """
+ order_qt_null_cancel """
+ select array_sort(array_except_all(['a', null, 'a', null], [null]))
+ """
+ order_qt_null_saturating_cancel """
+ select array_sort(array_except_all([null], [null, null]))
+ """
+ order_qt_empty_left """
+ select array_except_all(cast([] as array<int>), [1])
+ """
+ order_qt_empty_right """
+ select array_except_all([1, 1, 2], cast([] as array<int>))
+ """
+ order_qt_null_left """
+ select array_except_all(cast(null as array<int>), [1])
+ """
+ order_qt_null_right """
+ select array_except_all([1], cast(null as array<int>))
+ """
+ order_qt_implicit_type_coercion """
+ select array_sort(array_except_all([1, 1, 2], array(cast(1 as
bigint))))
+ """
+ order_qt_decimal """
+ select array_sort(array_except_all(
+ array(cast(1.25 as decimal(9, 2)), cast(1.25 as decimal(9, 2)),
+ cast(2.50 as decimal(9, 2))),
+ array(cast(1.25 as decimal(9, 2)))))
+ """
+ order_qt_date """
+ select array_sort(array_except_all(
+ array(cast('2026-08-25' as date), cast('2026-08-25' as date),
+ cast('2026-08-26' as date)),
+ array(cast('2026-08-25' as date))))
+ """
+ order_qt_datetime """
+ select array_sort(array_except_all(
+ array(cast('2026-08-25 10:00:00' as datetime),
+ cast('2026-08-25 10:00:00' as datetime)),
+ array(cast('2026-08-25 10:00:00' as datetime))))
+ """
+ order_qt_ipv4 """
+ select array_sort(array_except_all(
+ array(cast('192.168.0.1' as ipv4), cast('192.168.0.1' as ipv4),
+ cast('192.168.0.2' as ipv4)),
+ array(cast('192.168.0.1' as ipv4))))
+ """
+ order_qt_ipv6 """
+ select array_sort(array_except_all(
+ array(cast('2001:db8::1' as ipv6), cast('2001:db8::1' as ipv6),
+ cast('2001:db8::2' as ipv6)),
+ array(cast('2001:db8::1' as ipv6))))
+ """
+ order_qt_composed """
+ select array_size(array_except_all([1, 1, 2, 3], [1, 3]))
+ """
+ order_qt_compare_set_semantics """
+ select array_sort(array_except(['a', 'a', 'b'], ['a'])),
+ array_sort(array_except_all(['a', 'a', 'b'], ['a']))
+ """
+
+ sql "drop table if exists test_array_except_all_table"
+ sql """
+ create table test_array_except_all_table (
+ id int,
+ left_int array<int>,
+ right_int array<int>,
+ left_string array<string>,
+ right_string array<string>
+ ) distributed by hash(id) buckets 1
+ properties("replication_num" = "1")
+ """
+ sql """
+ insert into test_array_except_all_table values
+ (1, [1, 1, 2], [1], ['a', 'a', 'b'], ['a']),
+ (2, [1, null, 1], [null], ['a', null, 'a'], [null]),
+ (3, [], [1], [], ['a']),
+ (4, [1, 2], [], ['a', 'b'], []),
+ (5, null, [1], null, ['a'])
+ """
+ order_qt_column_arguments """
+ select id,
+ array_sort(array_except_all(left_int, right_int)),
+ array_sort(array_except_all(left_string, right_string))
+ from test_array_except_all_table
+ order by id
+ """
+ order_qt_left_constant """
+ select id, array_sort(array_except_all([1, 1, 2], right_int))
+ from test_array_except_all_table
+ order by id
+ """
+ order_qt_right_constant """
+ select id, array_sort(array_except_all(left_int, [1]))
+ from test_array_except_all_table
+ order by id
+ """
+
+ test {
+ sql "select array_except_all([[1], [1], [2]], [[1]])"
+ exception "array_except_all does not support types"
+ }
+ test {
+ sql "select array_except_all(array(map(1, 'a')), array(map(1, 'a')))"
+ exception "array_except_all does not support types"
+ }
+ test {
+ sql """
+ select array_except_all(
+ array(named_struct('a', 1)), array(named_struct('a', 1)))
+ """
+ exception "array_except_all does not support types"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
