This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 123000ed9d4 [fix](array_avg) fix core for array_avg (#46927) (#48631)
123000ed9d4 is described below
commit 123000ed9d4cff442c7a1346e0ff0fe3bcf7a11b
Author: amory <[email protected]>
AuthorDate: Tue Mar 4 21:46:26 2025 +0800
[fix](array_avg) fix core for array_avg (#46927) (#48631)
if we set session variable for fold_constant_for_be = 1 and
enable_decimal256 = true
here will meet error in sql
```
SELECT ARRAY_AVG(CAST([] AS ARRAY < DECIMALV3(1,0) > ));
```
with core
```
SUMMARY: UndefinedBehaviorSanitizer: undefined-behavior
/mnt/disk1/wangqiannan/amory/doris/be/src/vec/columns/column_decimal.h:200:15 in
0# doris::signal::(anonymous namespace)::FailureSignalHandler(int,
siginfo_t*, void*) at
/mnt/disk1/wangqiannan/amory/doris/be/src/common/signal_handler.h:421
1# PosixSignals::chained_handler(int, siginfo*, void*) [clone .part.0] in
/mnt/disk1/wangqiannan/tool/jdk-17.0.10/lib/server/libjvm.so
2# JVM_handle_linux_signal in
/mnt/disk1/wangqiannan/tool/jdk-17.0.10/lib/server/libjvm.so
3# 0x00007FD98C1A2B50 in /lib64/libc.so.6
4#
doris::vectorized::ColumnDecimal<doris::vectorized::Decimal<wide::integer<256ul,
int> > >::get_data_at(unsigned long) const at
/mnt/disk1/wangqiannan/amory/doris/be/src/vec/columns/column_decimal.h:201
5#
doris::vectorized::DataTypeDecimalSerDe<doris::vectorized::Decimal<wide::integer<256ul,
int> > >::write_column_to_pb(doris::vectorized::IColumn const&,
doris::PValues&, long, long) const at
/mnt/disk1/wangqiannan/amory/doris/be/src/vec/data_types/serde/data_type_decimal_serde.h:158
6#
doris::vectorized::DataTypeNullableSerDe::write_column_to_pb(doris::vectorized::IColumn
const&, doris::PValues&, long, long) const at
/mnt/disk1/wangqiannan/amory/doris/be/src/vec/data_types/serde/data_type_nullable_serde.cpp:237
7#
doris::FoldConstantExecutor::fold_constant_vexpr(doris::TFoldConstantParams
const&, doris::PConstantExprResult*) at
/mnt/disk1/wangqiannan/amory/doris/be/src/runtime/fold_constant_executor.cpp:118
8#
doris::PInternalService::_fold_constant_expr(std::__cxx11::basic_string<char,
std::char_traits<char>, std::allocator<char> > const&,
doris::PConstantExprResult*) at
/mnt/disk1/wangqiannan/amory/doris/be/src/service/internal_service.cpp:1537
9#
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0::operator()() const at
/mnt/disk1/wangqiannan/amory/doris/be/src/service/internal_service.cpp:1515
10# void std::__invoke_impl<void,
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0&>(std::__invoke_other,
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0&) at
/mnt/disk1/wangqiannan/tool/ldb_toolchain_16/bin/../lib/gcc/x86_64-linux-gn
[...]
11# std::enable_if<is_invocable_r_v<void,
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0&>, void>::type std::__invoke_r<void,
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0&>(doris::PInternalService::fold_constant_expr(google::prot
[...]
12# std::_Function_handler<void (),
doris::PInternalService::fold_constant_expr(google::protobuf::RpcController*,
doris::PConstantExprRequest const*, doris::PConstantExprResult*,
google::protobuf::Closure*)::$_0>::_M_invoke(std::_Any_data const&) at
/mnt/disk1/wangqiannan/tool/ldb_toolchain_16/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/std_function.h:291
13# std::function<void ()>::operator()() const at
/mnt/disk1/wangqiannan/tool/ldb_toolchain_16/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/std_function.h:560
14# doris::WorkThreadPool<false>::work_thread(int) at
/mnt/disk1/wangqiannan/amory/doris/be/src/util/work_thread_pool.hpp:158
15# void std::__invoke_impl<void, void (doris::WorkThreadPool<false>::*
const&)(int), doris::WorkThreadPool<false>*&, int&>(std::__invoke_memfun_deref,
void (doris::WorkThreadPool<false>::* const&)(int),
doris::WorkThreadPool<false>*&, int&) at
/mnt/disk1/wangqiannan/tool/ldb_toolchain_16/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/invoke.h:74
16# std::__invoke_result<void (doris::WorkThreadPool<false>::*
const&)(int), doris::WorkThreadPool<false>*&, int&>::type std::__invoke<void
(doris::WorkThreadPool<false>::* const&)(int), doris::WorkThreadPool<false>*&,
int&>(void (doris::WorkThreadPool<false>::* const&)(int),
doris::WorkThreadPool<false>*&, int&) at
/mnt/disk1/wangqiannan/tool/ldb_toolchain_16/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/invoke.h:96
```
### 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. -->
- [ ] 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:
- [ ] 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)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../expression/rules/FoldConstantRuleOnBE.java | 1 +
.../functions/ComputePrecisionForArrayItemAgg.java | 13 +
.../test_array_functions_by_literal.out | Bin 10064 -> 30047 bytes
.../test_array_functions_by_literal.groovy | 1633 +++++++++++++++-----
4 files changed, 1274 insertions(+), 373 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnBE.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnBE.java
index abef5a13f9d..b76c71a9fa4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnBE.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnBE.java
@@ -290,6 +290,7 @@ public class FoldConstantRuleOnBE implements
ExpressionPatternRuleFactory {
TQueryOptions tQueryOptions = new TQueryOptions();
tQueryOptions.setRepeatMaxNum(context.getSessionVariable().repeatMaxNum);
tQueryOptions.setBeExecVersion(Config.be_exec_version);
+
tQueryOptions.setEnableDecimal256(context.getSessionVariable().isEnableDecimal256());
TFoldConstantParams tParams = new TFoldConstantParams(paramMap,
queryGlobals);
tParams.setVecExec(true);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputePrecisionForArrayItemAgg.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputePrecisionForArrayItemAgg.java
index 05efb92222b..de1f01d5e10 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputePrecisionForArrayItemAgg.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputePrecisionForArrayItemAgg.java
@@ -23,6 +23,9 @@ import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.DecimalV3Type;
import org.apache.doris.qe.ConnectContext;
+import java.util.ArrayList;
+import java.util.List;
+
/** ComputePrecisionForSum */
public interface ComputePrecisionForArrayItemAgg extends ComputePrecision {
@Override
@@ -39,6 +42,16 @@ public interface ComputePrecisionForArrayItemAgg extends
ComputePrecision {
enableDecimal256 ?
DecimalV3Type.MAX_DECIMAL256_PRECISION
: DecimalV3Type.MAX_DECIMAL128_PRECISION,
((DecimalV3Type) itemType).getScale());
+ List<DataType> newArgumentsTypes = new
ArrayList<>(signature.argumentsTypes.size());
+ for (int i = 0; i < signature.argumentsTypes.size(); i++) {
+ DataType argType = signature.argumentsTypes.get(i);
+ if (argType instanceof ArrayType && i == 0) {
+ newArgumentsTypes.add(ArrayType.of(returnType));
+ } else {
+ newArgumentsTypes.add(argType);
+ }
+ }
+ signature = signature.withArgumentTypes(signature.hasVarArgs,
newArgumentsTypes);
if (signature.returnType instanceof ArrayType) {
signature =
signature.withReturnType(ArrayType.of(returnType));
} else {
diff --git
a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions_by_literal.out
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions_by_literal.out
index bddcebea700..f8b42d49332 100644
Binary files
a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions_by_literal.out
and
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions_by_literal.out
differ
diff --git
a/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions_by_literal.groovy
b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions_by_literal.groovy
index 14c6ed5b925..b20b2c874f5 100644
---
a/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions_by_literal.groovy
+++
b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions_by_literal.groovy
@@ -39,380 +39,1267 @@ suite("test_array_functions_by_literal") {
qt_sql_16 """select array_concat(array(1.0,2.0,null, null,2.0),
array(1.0,2.0,null, null,2.0));"""
- // array_contains function
- qt_sql "select array_contains([1.111, 2, 3.333], 2);"
- qt_sql "select array_contains([1,2,3], 1)"
- qt_sql "select array_contains([1,2,3], 4)"
- qt_sql "select array_contains([1,2,3,NULL], 1)"
- qt_sql "select array_contains([1,2,3,NULL], NULL)"
- qt_sql "select array_contains([], true)"
- qt_sql "select array_contains([], NULL)"
- qt_sql "select array_contains(NULL, 1)"
- qt_sql "select array_contains(NULL, NULL)"
- qt_sql "select array_contains([true], false)"
- qt_sql "select array_contains(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
- qt_sql "select array_contains(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
- qt_sql "select array_contains(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), cast (111.111 as decimalv3(6,3)))"
-
- // array_position function
- qt_sql "select array_position([1.111, 2, 3.333], 2);"
- qt_sql "select array_position([1,2,3], 1)"
- qt_sql "select array_position([1,2,3], 3)"
- qt_sql "select array_position([1,2,3], 4)"
- qt_sql "select array_position([NULL,2,3], 2)"
- qt_sql "select array_position([NULL,2,3], NULL)"
- qt_sql "select array_position([], true)"
- qt_sql "select array_position([], NULL)"
- qt_sql "select array_position(NULL, 1)"
- qt_sql "select array_position(NULL, NULL)"
- qt_sql "select array_position([null], true)"
- qt_sql "select array_position([0], null)"
- qt_sql "select array_position([null, '1'], '')"
- qt_sql "select array_position([''], null)"
- qt_sql "select array_position([false, NULL, true], true)"
- qt_sql "select array_position(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
- qt_sql "select array_position(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
- qt_sql "select array_position(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), cast (111.111 as decimalv3(6,3)))"
-
- // element_at function
- qt_sql "select element_at([1,2,3], 1)"
- qt_sql "select element_at([1,2,3], 3)"
- qt_sql "select element_at([1,2,3], 4)"
- qt_sql "select element_at([1,2,3], -1)"
- qt_sql "select element_at([1,2,3], NULL)"
- qt_sql "select element_at([1,2,NULL], 3)"
- qt_sql "select element_at([1,2,NULL], 2)"
- qt_sql "select element_at([], -1)"
- qt_sql "select element_at([true, NULL, false], 2)"
- qt_sql "select element_at(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), 1)"
- qt_sql "select element_at(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), 2)"
- qt_sql "select element_at(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), 1)"
-
- // array subscript function
- qt_sql "select [1,2,3][1]"
- qt_sql "select [1,2,3][3]"
- qt_sql "select [1,2,3][4]"
- qt_sql "select [1,2,3][-1]"
- qt_sql "select [1,2,3][NULL]"
- qt_sql "select [1,2,NULL][3]"
- qt_sql "select [1,2,NULL][2]"
- qt_sql "select [][-1]"
- qt_sql "select [true, false]"
- qt_sql "select (array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[2]"
- qt_sql "select (array(cast ('2023-02-04' as datev2),cast ('2023-02-05' as
datev2)))[2]"
- qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222 as
decimalv3(6,3))))[2]"
-
- // array_aggregation function
- qt_sql "select array_avg([1,2,3])"
- qt_sql "select array_sum([1,2,3])"
- qt_sql "select array_min([1,2,3])"
- qt_sql "select array_max([1,2,3])"
- qt_sql "select array_product([1,2,3])"
- qt_sql "select array_avg([1,2,3,null])"
- qt_sql "select array_sum([1,2,3,null])"
- qt_sql "select array_min([1,2,3,null])"
- qt_sql "select array_max([1,2,3,null])"
- qt_sql "select array_product([1,2,3,null])"
- qt_sql "select array_avg([])"
- qt_sql "select array_sum([])"
- qt_sql "select array_min([])"
- qt_sql "select array_max([])"
- qt_sql "select array_product([])"
- qt_sql "select array_avg([null])"
- qt_sql "select array_sum([null])"
- qt_sql "select array_min([null])"
- qt_sql "select array_max([null])"
- qt_sql "select array_product([null])"
- qt_sql "select array_product([1.12, 3.45, 4.23])"
- qt_sql "select array_product([1.12, 3.45, -4.23])"
- qt_sql "select array_min(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
- qt_sql "select array_max(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
- qt_sql "select array_min(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
- qt_sql "select array_max(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
- qt_sql "select array_avg(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
- qt_sql "select array_sum(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
- qt_sql "select array_min(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
- qt_sql "select array_max(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
- qt_sql "select array_product(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
-
- // array_distinct function
- qt_sql "select array_distinct([1,1,2,2,3,3])"
- qt_sql "select array_distinct([1,1,2,2,3,3,null])"
- qt_sql "select array_distinct([1,1,3,3,null, null, null])"
- qt_sql "select array_distinct(['a','a','a'])"
- qt_sql "select array_distinct([null, 'a','a','a', null])"
- qt_sql "select array_distinct([true, false, false, null])"
- qt_sql "select array_distinct([])"
- qt_sql "select array_distinct([null,null])"
- qt_sql "select array_distinct([1, 0, 0, null])"
- qt_sql "select array_distinct(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)),cast
('2023-02-04 23:07:34.999' as datetimev2(3))))"
- qt_sql "select array_distinct(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2),cast ('2023-02-05' as datev2)))"
- qt_sql "select array_distinct(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
-
-
- // array_remove function
- qt_sql "select array_remove([1,2,3], 1)"
- qt_sql "select array_remove([1,2,3,null], 1)"
- qt_sql "select array_remove(['a','b','c'], 'a')"
- qt_sql "select array_remove(['a','b','c',null], 'a')"
- qt_sql "select array_remove([true, false, false], false)"
- qt_sql "select array_remove(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
- qt_sql "select array_remove(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
- qt_sql "select array_remove(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), cast (111.111 as decimalv3(6,3)))"
-
- // array_sort function
- qt_sql_array_sort1 "select array_sort([1,2,3])"
- qt_sql_array_sort2 "select array_sort([3,2,1])"
- qt_sql_array_sort3 "select array_sort([1,2,3,null])"
- qt_sql_array_sort4 "select array_sort([null,1,2,3])"
- qt_sql_array_sort5 "select array_sort(['a','b','c'])"
- qt_sql_array_sort6 "select array_sort(['c','b','a'])"
- qt_sql_array_sort7 "select array_sort([true, false, true])"
- qt_sql_array_sort8 "select array_sort([])"
- qt_sql_array_sort9 "select array_sort(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql_array_sort10 "select array_sort(array(cast ('2023-02-06' as
datev2),cast ('2023-02-05' as datev2)))"
- qt_sql_array_sort11 "select array_sort(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
-
- // array_reverse_sort function
- qt_sql_array_reverse_sort1 "select array_reverse_sort([1,2,3])"
- qt_sql_array_reverse_sort2 "select array_reverse_sort([3,2,1])"
- qt_sql_array_reverse_sort3 "select array_reverse_sort([1,2,3,null])"
- qt_sql_array_reverse_sort4 "select array_reverse_sort([null,1,2,3])"
- qt_sql_array_reverse_sort5 "select array_reverse_sort(['a','b','c'])"
- qt_sql_array_reverse_sort6 "select array_reverse_sort(['c','b','a'])"
- qt_sql_array_reverse_sort7 "select array_reverse_sort([true, false, true])"
- qt_sql_array_reverse_sort8 "select array_reverse_sort([])"
- qt_sql_array_reverse_sort9 "select array_reverse_sort(array(cast
('2023-02-06 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql_array_reverse_sort10 "select array_reverse_sort(array(cast
('2023-02-06' as datev2),cast ('2023-02-05' as datev2)))"
- qt_sql_array_reverse_sort11 "select array_reverse_sort(array(cast (111.111
as decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
-
- // array_overlap function
- qt_sql "select arrays_overlap([1,2,3], [4,5,6])"
- qt_sql "select arrays_overlap([1,2,3], [3,4,5])"
- qt_sql "select arrays_overlap([1,2,3,null], [3,4,5])"
- qt_sql "select arrays_overlap([true], [false])"
- qt_sql "select arrays_overlap([], [])"
- qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-08 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-08' as datev2)))"
- qt_sql "select arrays_overlap(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
-
- // array_binary function
- qt_sql "select array_union([1,2,3], [2,3,4])"
- qt_sql "select array_except([1,2,3], [2,3,4])"
- qt_sql "select array_intersect([1,2,3], [2,3,4])"
- qt_sql "select array_union([1,2,3], [2,3,4,null])"
- qt_sql "select array_except([1,2,3], [2,3,4,null])"
- qt_sql "select array_intersect([1,2,3], [2,3,4,null])"
- qt_sql "select array_union([true], [false])"
- qt_sql "select array_except([true, false], [true])"
- qt_sql "select array_intersect([false, true], [false])"
- qt_sql "select array_union([], [])"
- qt_sql "select array_except([], [])"
- qt_sql "select array_intersect([], [])"
- qt_sql "select array_union([], [1,2,3])"
- qt_sql "select array_except([], [1,2,3])"
- qt_sql "select array_intersect([], [1,2,3])"
- qt_sql "select array_union([null], [1,2,3])"
- qt_sql "select array_except([null], [1,2,3])"
- qt_sql "select array_intersect([null], [1,2,3])"
- qt_sql "select array_union([1], [100000000])"
- qt_sql "select array_except([1], [100000000])"
- qt_sql "select array_intersect([1], [100000000])"
- qt_sql "select array_union(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select array_except(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select array_intersect(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select array_union(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select array_except(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select array_intersect(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select array_union(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
- qt_sql "select array_except(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
- qt_sql "select array_intersect(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
-
- // array_slice function
- qt_sql "select [1,2,3][1:1]"
- qt_sql "select [1,2,3][1:3]"
- qt_sql "select [1,2,3][1:5]"
- qt_sql "select [1,2,3][2:]"
- qt_sql "select [1,2,3][-2:]"
- qt_sql "select [1,2,3][2:-1]"
- qt_sql "select [1,2,3][0:]"
- qt_sql "select [1,2,3][-5:]"
- qt_sql "select [true, false, false][2:]"
- qt_sql "select (array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[1:2]"
- qt_sql "select (array(cast ('2023-02-06' as datev2), cast ('2023-02-05' as
datev2)))[1:2]"
- qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222 as
decimalv3(6,3))))[1:2]"
-
- // array_intersect
- qt_sql_intersect_1 "select array_intersect([1,2,3], [1,2,3], [null])"
- qt_sql_intersect_2 "select array_intersect([1, 2, null], [1, 3, null],
[1,2,3,null])"
- qt_sql_intersect_3 "select array_intersect([1,2,3, null], [1,2,3,null],
[1,2,null], [1, null])"
- qt_sql_intersect_4 "select array_intersect([1,2,3], [1,2,3], [null], [])"
-
- // array_union-with-multiple-arguments
- qt_sql_union_1 "select array_union([1,2,3], [1,2,3], [null])"
- qt_sql_union_2 "select array_union([1, 2, null], [1, 3, null],
[1,2,3,null])"
- qt_sql_union_3 "select array_union([1,2,3, null], [1,2,3,null],
[1,2,null], [1, null])"
- qt_sql_union_4 "select array_union([1,2,3], [1,2,3], [null], [])"
-
- // array_popfront function
- qt_sql "select array_popfront([1,2,3,4,5,6])"
- qt_sql "select array_popfront([])"
- qt_sql "select array_popfront(null)"
- qt_sql "select array_popfront([null,2,3,4,5])"
- qt_sql "select array_popfront([1,2,3,4,null])"
- qt_sql "select array_popfront(['1','2','3','4','5','6'])"
- qt_sql "select array_popfront([null,'2','3','4','5','6'])"
- qt_sql "select array_popfront(['1','2','3','4','5',null])"
- qt_sql "select array_popfront(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast ('2023-02-05' as
datev2)))"
- qt_sql "select array_popfront(array(null, cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select array_popfront(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select array_popfront(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
-
- // array_join function
- qt_sql "select array_join([1, 2, 3], '_')"
- qt_sql "select array_join(['1', '2', '3', null], '_')"
- qt_sql "select array_join([null, '1', '2', '3', null], '_')"
- qt_sql "select array_join(['', '2', '3'], '_')"
- qt_sql "select array_join(['1', '2', ''], '_')"
- qt_sql "select array_join(['1', '2', '', null], '_')"
- qt_sql "select array_join(['', '', '3'], '_')"
- qt_sql "select array_join(['1', '2', '', ''], '_')"
- qt_sql "select array_join([null, null, '1', '2', '', '', null], '_')"
- qt_sql "select array_join([null, null, 1, 2, '', '', null], '_', 'any')"
- qt_sql "select array_join([''], '_')"
- qt_sql "select array_join(['', ''], '_')"
- qt_sql_array_with_constant1 "select array_with_constant(3, '_'),
array_repeat('_', 3)"
- qt_sql_array_with_constant2 "select array_with_constant(2, '1'),
array_repeat('1', 2)"
- qt_sql_array_with_constant3 "select array_with_constant(4, 1223),
array_repeat(1223, 4)"
- qt_sql_array_with_constant4 "select array_with_constant(8, null),
array_repeat(null, 8)"
- qt_sql_array_with_constant5 "select array_with_constant(null, 'abc'),
array_repeat('abc', null)"
- qt_sql_array_with_constant6 "select array_with_constant(null, null),
array_repeat(null, null)"
- // array_compact function
- qt_sql "select array_compact([1, 2, 3, 3, null, null, 4, 4])"
- qt_sql "select array_compact([null, null, null])"
- qt_sql "select array_compact([1.2, 1.2, 3.4, 3.3, 2.1])"
- qt_sql "select array_compact(['a','b','c','c','d'])"
- qt_sql "select array_compact(['aaa','aaa','bbb','ccc','ccccc',null,
null,'dddd'])"
- qt_sql "select array_compact(['2015-03-13','2015-03-13'])"
- qt_sql "select array_compact(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
- qt_sql "select array_compact(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast ('2023-02-05' as
datev2)))"
- qt_sql "select array_compact(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3)),cast (222.222 as decimalv3(6,3)),cast (333.333 as
decimalv3(6,3))))"
- qt_sql "select array_compact(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
-
- // array_apply
- qt_sql """select array_apply([1000000, 1000001, 1000002], '=', 1000002)"""
- qt_sql """select array_apply([1.111, 2.222, 3.333], '>=', 2)"""
- qt_sql """select array_apply(cast(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17") as array<datetimev2>), ">", '2020-01-02')"""
- qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), ">", '25')"""
- qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), "!=", '25')"""
- // qt_sql """select array_apply(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), ">", '111.111')"""
-
- qt_sql "select array_concat([1, 2, 3], [2, 3, 4], [8, 1, 2], [9])"
- qt_sql "select array_concat([12, 23], [25, null], [null], [66])"
- qt_sql "select array_concat([1.2, 1.8], [9.0, 2.2], [2.8])"
- qt_sql "select array_concat(['aaa', null], ['bbb', 'fff'], [null, 'ccc'])"
- qt_sql "select array_concat(null, [1, 2, 3], null)"
- qt_sql "select array_concat(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), array(cast (999.28 as decimal(10,3)), cast (123.99
as decimal(10,3))))"
- qt_sql "select array_concat(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), array(cast ('2023-02-01' as datev2), cast
('2023-02-05' as datev2)))"
- qt_sql "select array_concat(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
-
- // array_shuffle
- // do not check result, since shuffle result is random
- sql "SELECT array_sum(array_shuffle([1, 2, 3, 3, null, null, 4, 4])),
array_shuffle([1, 2, 3, 3, null, null, 4, 4], 0), shuffle([1, 2, 3, 3, null,
null, 4, 4], 0)"
- sql "SELECT array_sum(array_shuffle([1.111, 2.222, 3.333])),
array_shuffle([1.111, 2.222, 3.333], 0), shuffle([1.111, 2.222, 3.333], 0)"
- sql "SELECT array_size(array_shuffle(['aaa', null, 'bbb', 'fff'])),
array_shuffle(['aaa', null, 'bbb', 'fff'], 0), shuffle(['aaa', null, 'bbb',
'fff'], 0)"
- sql """select array_size(array("2020-01-02", "2022-01-03", "2021-01-01",
"1996-04-17")), array_shuffle(array("2020-01-02", "2022-01-03", "2021-01-01",
"1996-04-17"), 0), shuffle(array("2020-01-02", "2022-01-03", "2021-01-01",
"1996-04-17"), 0)"""
-
- // array_zip
- qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'])"
- qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h',
'i'])"
- qt_sql "select array_zip([1, 2, 3, 4, 5], ['d', 'o', 'r', 'i', 's'])"
- qt_sql "select array_zip([1.1, 2.2, 3.3], [1, 2, 3])"
- qt_sql "select array_zip([1, null, 3], [null, 'b', null])"
- qt_sql "select array_zip(array(cast (3.05 as decimal(10,3)), cast (2.22 as
decimal(10,3))), array(cast (3.14 as decimal(10,3)), cast (6.66 as
decimal(10,3))))"
- qt_sql "select array_zip(array(cast ('2000-03-05' as datev2), cast
('2023-03-10' as datev2)), array(cast ('2000-02-02' as datev2), cast
('2023-03-10' as datev2)))"
- qt_sql "select array_zip(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
- qt_sql "select array_zip([1, 2, 3], null, ['foo', 'bar', 'test'])"
-
- qt_sql "select array(8, null)"
- qt_sql "select array('a', 1, 2)"
- qt_sql "select array(null, null, null)"
-
- // array_enumerate_uniq
- qt_sql "select array_enumerate_uniq([])"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5])"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2,
3, 4, 5])"
- qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 3, 3, 4, 4])"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 1, 3, 4, 2, 5, 4, 5])"
- qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 1,
1, 1, 1, 1])"
- qt_sql "select array_enumerate_uniq([null])"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, null, null])"
- qt_sql "select array_enumerate_uniq([1, null, 2, null, 3, null, 4, 1,
null, 2, null, 3, null, 4])"
- qt_sql "select array_enumerate_uniq(['11', '22', '33', '11', '33', '22'])"
- qt_sql "select array_enumerate_uniq(array(cast (24.99 as decimal(10,3)),
cast (25.99 as decimal(10,3)), cast (24.99 as decimal(10,3))))"
- qt_sql "select array_enumerate_uniq(array(cast ('2023-02-06 22:07:34.999'
as datetimev2(3)), cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-06 22:07:34.999' as datetimev2(3))))"
- qt_sql "select array_enumerate_uniq(array(cast (384.2933 as decimalv3(7,
4)), cast (984.1913 as decimalv3(7, 4)), cast (384.2933 as decimalv3(7, 4)),
cast (722.9333 as decimalv3(7, 4)), cast (384.2933 as decimalv3(7, 4))))"
- qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5], [1, 2, 3, 4, 5])"
- qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
- qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
- qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 1, 2], [1, 2, 1, 2, 2,
1])"
- qt_sql "select array_enumerate_uniq([1, null, 1, null], [null, 1, null,
1])"
- qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 1], [2, 1, 2, 1, 2,
1], [3, 1, 3, 1, 3, 1])"
- qt_sql "select array_enumerate_uniq([1, 3, 1], [2.0, 5.0, 2.0], ['3', '8',
'3'], array(cast (34.9876 as decimalv3(6, 4)), cast (89.9865 as decimalv3(6,
4)), cast (34.9876 as decimalv3(6, 4))))"
-
- // array_pushfront
- qt_sql "select array_pushfront([1, 2, 3], 6)"
- qt_sql "select array_pushfront([1, 2, 3], null)"
- qt_sql "select array_pushfront(null, 6)"
- qt_sql "select array_pushfront([1.111, 2.222, 3.333], 9.999)"
- qt_sql "select array_pushfront(['aaa', 'bbb', 'ccc'], 'dddd')"
- qt_sql "select array_pushfront(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
- qt_sql "select array_pushfront(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
- qt_sql "select array_pushfront(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
- qt_sql "select array_pushfront(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), cast (333.333 as decimalv3(6,3)))"
-
- // array_pushback
- qt_sql "select array_pushback([1, 2, 3], 6)"
- qt_sql "select array_pushback([1, 2, 3], null)"
- qt_sql "select array_pushback(null, 6)"
- qt_sql "select array_pushback([1.111, 2.222, 3.333], 9.999)"
- qt_sql "select array_pushback(['aaa', 'bbb', 'ccc'], 'dddd')"
- qt_sql "select array_pushback(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
- qt_sql "select array_pushback(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
- qt_sql "select array_pushback(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
- qt_sql "select array_pushback(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), cast (333.333 as decimalv3(6,3)))"
- qt_sql "select array_pushback([null,null], null)"
- qt_sql "select array_pushback([null,null,null,null], 80)"
-
- // array_cum_sum
- qt_sql "select array_cum_sum([0, 2, 127])"
- qt_sql "select array_cum_sum([254, 4, 0])"
- qt_sql "select array_cum_sum([1.0, 2.1 ,3.2, 4.3, 5.4])"
- qt_sql "select array_cum_sum([-1, 2 ,-3, 4, -5])"
- qt_sql "select array_cum_sum([-5.23, 4.12, -3.02, 2.00 ,1.01])"
- qt_sql "select array_cum_sum([1, 2, 3, null])"
- qt_sql "select array_cum_sum([null, 1, null, 3, 8, null])"
- qt_sql "select array_cum_sum([null, null])"
- qt_sql "select array_cum_sum([8])"
- qt_sql "select array_cum_sum([1.1])"
- qt_sql "select array_cum_sum([null])"
- qt_sql "select array_cum_sum([])"
- qt_sql "select array_cum_sum(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3)), cast (999.28 as decimal(10,3))))"
- qt_sql "select array_cum_sum(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
- qt_sql "select array_cum_sum(array(cast (11.9999 as decimalv3(6,4)),cast
(22.0001 as decimalv3(6,4))))"
-
- // abnormal test
- try {
- sql "select array_intersect([1, 2, 3, 1, 2, 3], '1[3, 2, 5]')"
- } catch (Exception ex) {
- assert("${ex}".contains("errCode = 2, detailMessage = No matching
function with signature: array_intersect"))
- }
+ // array_contains function
+ qt_sql "select array_contains([1.111, 2, 3.333], 2);"
+ qt_sql "select array_contains([1,2,3], 1)"
+ qt_sql "select array_contains([1,2,3], 4)"
+ qt_sql "select array_contains([1,2,3,NULL], 1)"
+ qt_sql "select array_contains([1,2,3,NULL], NULL)"
+ qt_sql "select array_contains([], true)"
+ qt_sql "select array_contains([], NULL)"
+ qt_sql "select array_contains(NULL, 1)"
+ qt_sql "select array_contains(NULL, NULL)"
+ qt_sql "select array_contains([true], false)"
+ qt_sql "select array_contains(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_contains(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_contains(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_position function
+ qt_sql "select array_position([1.111, 2, 3.333], 2);"
+ qt_sql "select array_position([1,2,3], 1)"
+ qt_sql "select array_position([1,2,3], 3)"
+ qt_sql "select array_position([1,2,3], 4)"
+ qt_sql "select array_position([NULL,2,3], 2)"
+ qt_sql "select array_position([NULL,2,3], NULL)"
+ qt_sql "select array_position([], true)"
+ qt_sql "select array_position([], NULL)"
+ qt_sql "select array_position(NULL, 1)"
+ qt_sql "select array_position(NULL, NULL)"
+ qt_sql "select array_position([null], true)"
+ qt_sql "select array_position([0], null)"
+ qt_sql "select array_position([null, '1'], '')"
+ qt_sql "select array_position([''], null)"
+ qt_sql "select array_position([false, NULL, true], true)"
+ qt_sql "select array_position(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_position(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_position(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // element_at function
+ qt_sql "select element_at([1,2,3], 1)"
+ qt_sql "select element_at([1,2,3], 3)"
+ qt_sql "select element_at([1,2,3], 4)"
+ qt_sql "select element_at([1,2,3], -1)"
+ qt_sql "select element_at([1,2,3], NULL)"
+ qt_sql "select element_at([1,2,NULL], 3)"
+ qt_sql "select element_at([1,2,NULL], 2)"
+ qt_sql "select element_at([], -1)"
+ qt_sql "select element_at([true, NULL, false], 2)"
+ qt_sql "select element_at(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), 1)"
+ qt_sql "select element_at(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), 2)"
+ qt_sql "select element_at(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), 1)"
+
+ // array subscript function
+ qt_sql "select [1,2,3][1]"
+ qt_sql "select [1,2,3][3]"
+ qt_sql "select [1,2,3][4]"
+ qt_sql "select [1,2,3][-1]"
+ qt_sql "select [1,2,3][NULL]"
+ qt_sql "select [1,2,NULL][3]"
+ qt_sql "select [1,2,NULL][2]"
+ qt_sql "select [][-1]"
+ qt_sql "select [true, false]"
+ qt_sql "select (array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[2]"
+ qt_sql "select (array(cast ('2023-02-04' as datev2),cast ('2023-02-05'
as datev2)))[2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[2]"
+
+ // array_aggregation function
+ qt_sql "select array_avg([1,2,3])"
+ qt_sql "select array_sum([1,2,3])"
+ qt_sql "select array_min([1,2,3])"
+ qt_sql "select array_max([1,2,3])"
+ qt_sql "select array_product([1,2,3])"
+ qt_sql "select array_avg([1,2,3,null])"
+ qt_sql "select array_sum([1,2,3,null])"
+ qt_sql "select array_min([1,2,3,null])"
+ qt_sql "select array_max([1,2,3,null])"
+ qt_sql "select array_product([1,2,3,null])"
+ qt_sql "select array_avg([])"
+ qt_sql "select array_sum([])"
+ qt_sql "select array_min([])"
+ qt_sql "select array_max([])"
+ qt_sql "select array_product([])"
+ qt_sql "select array_avg([null])"
+ qt_sql "select array_sum([null])"
+ qt_sql "select array_min([null])"
+ qt_sql "select array_max([null])"
+ qt_sql "select array_product([null])"
+ qt_sql "select array_product([1.12, 3.45, 4.23])"
+ qt_sql "select array_product([1.12, 3.45, -4.23])"
+ qt_sql "select array_min(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_max(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_min(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_max(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_avg(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_sum(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_min(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_max(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_product(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_distinct function
+ qt_sql "select array_distinct([1,1,2,2,3,3])"
+ qt_sql "select array_distinct([1,1,2,2,3,3,null])"
+ qt_sql "select array_distinct([1,1,3,3,null, null, null])"
+ qt_sql "select array_distinct(['a','a','a'])"
+ qt_sql "select array_distinct([null, 'a','a','a', null])"
+ qt_sql "select array_distinct([true, false, false, null])"
+ qt_sql "select array_distinct([])"
+ qt_sql "select array_distinct([null,null])"
+ qt_sql "select array_distinct([1, 0, 0, null])"
+ qt_sql "select array_distinct(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)),cast
('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_distinct(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql "select array_distinct(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+
+ // array_remove function
+ qt_sql "select array_remove([1,2,3], 1)"
+ qt_sql "select array_remove([1,2,3,null], 1)"
+ qt_sql "select array_remove(['a','b','c'], 'a')"
+ qt_sql "select array_remove(['a','b','c',null], 'a')"
+ qt_sql "select array_remove([true, false, false], false)"
+ qt_sql "select array_remove(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_remove(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_remove(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_sort function
+ qt_sql_array_sort1 "select array_sort([1,2,3])"
+ qt_sql_array_sort2 "select array_sort([3,2,1])"
+ qt_sql_array_sort3 "select array_sort([1,2,3,null])"
+ qt_sql_array_sort4 "select array_sort([null,1,2,3])"
+ qt_sql_array_sort5 "select array_sort(['a','b','c'])"
+ qt_sql_array_sort6 "select array_sort(['c','b','a'])"
+ qt_sql_array_sort7 "select array_sort([true, false, true])"
+ qt_sql_array_sort8 "select array_sort([])"
+ qt_sql_array_sort9 "select array_sort(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_sort10 "select array_sort(array(cast ('2023-02-06' as
datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_sort11 "select array_sort(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_reverse_sort function
+ qt_sql_array_reverse_sort1 "select array_reverse_sort([1,2,3])"
+ qt_sql_array_reverse_sort2 "select array_reverse_sort([3,2,1])"
+ qt_sql_array_reverse_sort3 "select array_reverse_sort([1,2,3,null])"
+ qt_sql_array_reverse_sort4 "select array_reverse_sort([null,1,2,3])"
+ qt_sql_array_reverse_sort5 "select array_reverse_sort(['a','b','c'])"
+ qt_sql_array_reverse_sort6 "select array_reverse_sort(['c','b','a'])"
+ qt_sql_array_reverse_sort7 "select array_reverse_sort([true, false,
true])"
+ qt_sql_array_reverse_sort8 "select array_reverse_sort([])"
+ qt_sql_array_reverse_sort9 "select array_reverse_sort(array(cast
('2023-02-06 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_reverse_sort10 "select array_reverse_sort(array(cast
('2023-02-06' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_reverse_sort11 "select array_reverse_sort(array(cast
(111.111 as decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_overlap function
+ qt_sql "select arrays_overlap([1,2,3], [4,5,6])"
+ qt_sql "select arrays_overlap([1,2,3], [3,4,5])"
+ qt_sql "select arrays_overlap([1,2,3,null], [3,4,5])"
+ qt_sql "select arrays_overlap([true], [false])"
+ qt_sql "select arrays_overlap([], [])"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-08 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-08' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_binary function
+ qt_sql "select array_union([1,2,3], [2,3,4])"
+ qt_sql "select array_except([1,2,3], [2,3,4])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4])"
+ qt_sql "select array_union([1,2,3], [2,3,4,null])"
+ qt_sql "select array_except([1,2,3], [2,3,4,null])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4,null])"
+ qt_sql "select array_union([true], [false])"
+ qt_sql "select array_except([true, false], [true])"
+ qt_sql "select array_intersect([false, true], [false])"
+ qt_sql "select array_union([], [])"
+ qt_sql "select array_except([], [])"
+ qt_sql "select array_intersect([], [])"
+ qt_sql "select array_union([], [1,2,3])"
+ qt_sql "select array_except([], [1,2,3])"
+ qt_sql "select array_intersect([], [1,2,3])"
+ qt_sql "select array_union([null], [1,2,3])"
+ qt_sql "select array_except([null], [1,2,3])"
+ qt_sql "select array_intersect([null], [1,2,3])"
+ qt_sql "select array_union([1], [100000000])"
+ qt_sql "select array_except([1], [100000000])"
+ qt_sql "select array_intersect([1], [100000000])"
+ qt_sql "select array_union(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_except(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06 22:07:34.999'
as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_union(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_except(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_union(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
+ qt_sql "select array_except(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_intersect(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_slice function
+ qt_sql "select [1,2,3][1:1]"
+ qt_sql "select [1,2,3][1:3]"
+ qt_sql "select [1,2,3][1:5]"
+ qt_sql "select [1,2,3][2:]"
+ qt_sql "select [1,2,3][-2:]"
+ qt_sql "select [1,2,3][2:-1]"
+ qt_sql "select [1,2,3][0:]"
+ qt_sql "select [1,2,3][-5:]"
+ qt_sql "select [true, false, false][2:]"
+ qt_sql "select (array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[1:2]"
+ qt_sql "select (array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)))[1:2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[1:2]"
+
+ // array_intersect
+ qt_sql_intersect_1 "select array_intersect([1,2,3], [1,2,3], [null])"
+ qt_sql_intersect_2 "select array_intersect([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_intersect_3 "select array_intersect([1,2,3, null],
[1,2,3,null], [1,2,null], [1, null])"
+ qt_sql_intersect_4 "select array_intersect([1,2,3], [1,2,3], [null],
[])"
+
+ // array_union-with-multiple-arguments
+ qt_sql_union_1 "select array_union([1,2,3], [1,2,3], [null])"
+ qt_sql_union_2 "select array_union([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_union_3 "select array_union([1,2,3, null], [1,2,3,null],
[1,2,null], [1, null])"
+ qt_sql_union_4 "select array_union([1,2,3], [1,2,3], [null], [])"
+
+ // array_popfront function
+ qt_sql "select array_popfront([1,2,3,4,5,6])"
+ qt_sql "select array_popfront([])"
+ qt_sql "select array_popfront(null)"
+ qt_sql "select array_popfront([null,2,3,4,5])"
+ qt_sql "select array_popfront([1,2,3,4,null])"
+ qt_sql "select array_popfront(['1','2','3','4','5','6'])"
+ qt_sql "select array_popfront([null,'2','3','4','5','6'])"
+ qt_sql "select array_popfront(['1','2','3','4','5',null])"
+ qt_sql "select array_popfront(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(null, cast ('2023-02-06' as
datev2), cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_popfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_join function
+ qt_sql "select array_join([1, 2, 3], '_')"
+ qt_sql "select array_join(['1', '2', '3', null], '_')"
+ qt_sql "select array_join([null, '1', '2', '3', null], '_')"
+ qt_sql "select array_join(['', '2', '3'], '_')"
+ qt_sql "select array_join(['1', '2', ''], '_')"
+ qt_sql "select array_join(['1', '2', '', null], '_')"
+ qt_sql "select array_join(['', '', '3'], '_')"
+ qt_sql "select array_join(['1', '2', '', ''], '_')"
+ qt_sql "select array_join([null, null, '1', '2', '', '', null], '_')"
+ qt_sql "select array_join([null, null, 1, 2, '', '', null], '_',
'any')"
+ qt_sql "select array_join([''], '_')"
+ qt_sql "select array_join(['', ''], '_')"
+ qt_sql_array_with_constant1 "select array_with_constant(3, '_'),
array_repeat('_', 3)"
+ qt_sql_array_with_constant2 "select array_with_constant(2, '1'),
array_repeat('1', 2)"
+ qt_sql_array_with_constant3 "select array_with_constant(4, 1223),
array_repeat(1223, 4)"
+ qt_sql_array_with_constant4 "select array_with_constant(8, null),
array_repeat(null, 8)"
+ qt_sql_array_with_constant5 "select array_with_constant(null, 'abc'),
array_repeat('abc', null)"
+ qt_sql_array_with_constant6 "select array_with_constant(null, null),
array_repeat(null, null)"
+ // array_compact function
+ qt_sql "select array_compact([1, 2, 3, 3, null, null, 4, 4])"
+ qt_sql "select array_compact([null, null, null])"
+ qt_sql "select array_compact([1.2, 1.2, 3.4, 3.3, 2.1])"
+ qt_sql "select array_compact(['a','b','c','c','d'])"
+ qt_sql "select array_compact(['aaa','aaa','bbb','ccc','ccccc',null,
null,'dddd'])"
+ qt_sql "select array_compact(['2015-03-13','2015-03-13'])"
+ qt_sql "select array_compact(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_compact(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast ('2023-02-05' as
datev2)))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3)),cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_apply
+ qt_sql """select array_apply([1000000, 1000001, 1000002], '=',
1000002)"""
+ qt_sql """select array_apply([1.111, 2.222, 3.333], '>=', 2)"""
+ qt_sql """select array_apply(cast(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17") as array<datetimev2>), ">", '2020-01-02')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), ">", '25')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), "!=", '25')"""
+ // qt_sql """select array_apply(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), ">", '111.111')"""
+
+ qt_sql "select array_concat([1, 2, 3], [2, 3, 4], [8, 1, 2], [9])"
+ qt_sql "select array_concat([12, 23], [25, null], [null], [66])"
+ qt_sql "select array_concat([1.2, 1.8], [9.0, 2.2], [2.8])"
+ qt_sql "select array_concat(['aaa', null], ['bbb', 'fff'], [null,
'ccc'])"
+ qt_sql "select array_concat(null, [1, 2, 3], null)"
+ qt_sql "select array_concat(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), array(cast (999.28 as decimal(10,3)), cast (123.99
as decimal(10,3))))"
+ qt_sql "select array_concat(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), array(cast ('2023-02-01' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_concat(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+
+ // array_shuffle
+ // do not check result, since shuffle result is random
+ sql "SELECT array_sum(array_shuffle([1, 2, 3, 3, null, null, 4, 4])),
array_shuffle([1, 2, 3, 3, null, null, 4, 4], 0), shuffle([1, 2, 3, 3, null,
null, 4, 4], 0)"
+ sql "SELECT array_sum(array_shuffle([1.111, 2.222, 3.333])),
array_shuffle([1.111, 2.222, 3.333], 0), shuffle([1.111, 2.222, 3.333], 0)"
+ sql "SELECT array_size(array_shuffle(['aaa', null, 'bbb', 'fff'])),
array_shuffle(['aaa', null, 'bbb', 'fff'], 0), shuffle(['aaa', null, 'bbb',
'fff'], 0)"
+ sql """select array_size(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17")), array_shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0), shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0)"""
+
+ // array_zip
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'])"
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h',
'i'])"
+ qt_sql "select array_zip([1, 2, 3, 4, 5], ['d', 'o', 'r', 'i', 's'])"
+ qt_sql "select array_zip([1.1, 2.2, 3.3], [1, 2, 3])"
+ qt_sql "select array_zip([1, null, 3], [null, 'b', null])"
+ qt_sql "select array_zip(array(cast (3.05 as decimal(10,3)), cast
(2.22 as decimal(10,3))), array(cast (3.14 as decimal(10,3)), cast (6.66 as
decimal(10,3))))"
+ qt_sql "select array_zip(array(cast ('2000-03-05' as datev2), cast
('2023-03-10' as datev2)), array(cast ('2000-02-02' as datev2), cast
('2023-03-10' as datev2)))"
+ qt_sql "select array_zip(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+ qt_sql "select array_zip([1, 2, 3], null, ['foo', 'bar', 'test'])"
+
+ qt_sql "select array(8, null)"
+ qt_sql "select array('a', 1, 2)"
+ qt_sql "select array(null, null, null)"
+
+ // array_enumerate_uniq
+ qt_sql "select array_enumerate_uniq([])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1,
2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 3, 3, 4, 4])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 1, 3, 4, 2, 5, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2,
1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([null])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, null, null])"
+ qt_sql "select array_enumerate_uniq([1, null, 2, null, 3, null, 4, 1,
null, 2, null, 3, null, 4])"
+ qt_sql "select array_enumerate_uniq(['11', '22', '33', '11', '33',
'22'])"
+ qt_sql "select array_enumerate_uniq(array(cast (24.99 as
decimal(10,3)), cast (25.99 as decimal(10,3)), cast (24.99 as decimal(10,3))))"
+ qt_sql "select array_enumerate_uniq(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)), cast ('2023-02-04 23:07:34.999' as
datetimev2(3)), cast ('2023-02-06 22:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_enumerate_uniq(array(cast (384.2933 as
decimalv3(7, 4)), cast (984.1913 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4)), cast (722.9333 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4))))"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5], [1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 1, 2], [1, 2, 1, 2,
2, 1])"
+ qt_sql "select array_enumerate_uniq([1, null, 1, null], [null, 1,
null, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 1], [2, 1, 2, 1,
2, 1], [3, 1, 3, 1, 3, 1])"
+ qt_sql "select array_enumerate_uniq([1, 3, 1], [2.0, 5.0, 2.0], ['3',
'8', '3'], array(cast (34.9876 as decimalv3(6, 4)), cast (89.9865 as
decimalv3(6, 4)), cast (34.9876 as decimalv3(6, 4))))"
+
+ // array_pushfront
+ qt_sql "select array_pushfront([1, 2, 3], 6)"
+ qt_sql "select array_pushfront([1, 2, 3], null)"
+ qt_sql "select array_pushfront(null, 6)"
+ qt_sql "select array_pushfront([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushfront(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushfront(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05 12:23:24.999'
as datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+
+ // abnormal test
+ test {
+ sql "select array_intersect([1, 2, 3, 1, 2, 3], '1[3, 2, 5]')"
+ exception "No matching function with signature"
+ }
+ // array_pushback
+ qt_sql "select array_pushback([1, 2, 3], 6)"
+ qt_sql "select array_pushback([1, 2, 3], null)"
+ qt_sql "select array_pushback(null, 6)"
+ qt_sql "select array_pushback([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushback(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushback(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushback(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+ qt_sql "select array_pushback([null,null], null)"
+ qt_sql "select array_pushback([null,null,null,null], 80)"
+
+ // array_cum_sum
+ qt_sql "select array_cum_sum([0, 2, 127])"
+ qt_sql "select array_cum_sum([254, 4, 0])"
+ qt_sql "select array_cum_sum([1.0, 2.1 ,3.2, 4.3, 5.4])"
+ qt_sql "select array_cum_sum([-1, 2 ,-3, 4, -5])"
+ qt_sql "select array_cum_sum([-5.23, 4.12, -3.02, 2.00 ,1.01])"
+ qt_sql "select array_cum_sum([1, 2, 3, null])"
+ qt_sql "select array_cum_sum([null, 1, null, 3, 8, null])"
+ qt_sql "select array_cum_sum([null, null])"
+ qt_sql "select array_cum_sum([8])"
+ qt_sql "select array_cum_sum([1.1])"
+ qt_sql "select array_cum_sum([null])"
+ qt_sql "select array_cum_sum([])"
+ qt_sql "select array_cum_sum(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3)), cast (999.28 as decimal(10,3))))"
+ qt_sql "select array_cum_sum(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+ qt_sql "select array_cum_sum(array(cast (11.9999 as
decimalv3(6,4)),cast (22.0001 as decimalv3(6,4))))"
+
+ // abnormal test
+ test {
+ sql "select array_intersect([1, 2, 3, 1, 2, 3], '1[3, 2, 5]')"
+ exception "No matching function with signature"
+ }
+
+ // array_min/max with nested array for args
+ test {
+ sql "select array_min(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+
+ test {
+ sql "select array_min(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+
+ // array_map with string is can be succeed
+ qt_sql_array_map """ select array_map(x->x!='',
split_by_string('amory,is,better,committing', ',')) """
+
+ // array_apply with string should be failed
+ test {
+ sql """select
array_apply(split_by_string("amory,is,better,committing", ","), '!=', '');"""
+ exception("No matching function with signature")
+ }
+
+ qt_sql """ SELECT ARRAY_AVG(CAST([] AS ARRAY < DECIMALV3(1,0) > )); """
+
+ // set session variable for enale_fold_constant_by_be and
enable_decimal256
+ sql """ set enable_fold_constant_by_be = 1 """
+ sql """ set enable_decimal256 = true """
+
+
+ // array function
+ // array_nested function
+ qt_sql "select a from (select array(1, 1, 2, 2, 2, 2) as a) t"
+
+ // array with decimal and other types
+ qt_sql_1 """select array(1.0,2.0,null, null,2.0);"""
+ qt_sql_1 """select array('a',2.0,null, null,2.0);"""
+ qt_sql_1 """select array(1,2.0,null, null,2.0);"""
+ qt_sql_2 """select array_sort(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_3 """select array_min(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_4 """select array_max(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_5 """select array_avg(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_6 """select array_sum(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_7 """select array_product(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_8 """select array_distinct(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_9 """select array_sort(array_intersect(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_10 """select array_sort(array_except(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_11 """select array_sort(array_union(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_14 """select array_popfront(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_15 """select array_popback(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_16 """select array_concat(array(1.0,2.0,null, null,2.0),
array(1.0,2.0,null, null,2.0));"""
+
+
+ // array_contains function
+ qt_sql "select array_contains([1.111, 2, 3.333], 2);"
+ qt_sql "select array_contains([1,2,3], 1)"
+ qt_sql "select array_contains([1,2,3], 4)"
+ qt_sql "select array_contains([1,2,3,NULL], 1)"
+ qt_sql "select array_contains([1,2,3,NULL], NULL)"
+ qt_sql "select array_contains([], true)"
+ qt_sql "select array_contains([], NULL)"
+ qt_sql "select array_contains(NULL, 1)"
+ qt_sql "select array_contains(NULL, NULL)"
+ qt_sql "select array_contains([true], false)"
+ qt_sql "select array_contains(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_contains(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_contains(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_position function
+ qt_sql "select array_position([1.111, 2, 3.333], 2);"
+ qt_sql "select array_position([1,2,3], 1)"
+ qt_sql "select array_position([1,2,3], 3)"
+ qt_sql "select array_position([1,2,3], 4)"
+ qt_sql "select array_position([NULL,2,3], 2)"
+ qt_sql "select array_position([NULL,2,3], NULL)"
+ qt_sql "select array_position([], true)"
+ qt_sql "select array_position([], NULL)"
+ qt_sql "select array_position(NULL, 1)"
+ qt_sql "select array_position(NULL, NULL)"
+ qt_sql "select array_position([null], true)"
+ qt_sql "select array_position([0], null)"
+ qt_sql "select array_position([null, '1'], '')"
+ qt_sql "select array_position([''], null)"
+ qt_sql "select array_position([false, NULL, true], true)"
+ qt_sql "select array_position(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_position(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_position(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // element_at function
+ qt_sql "select element_at([1,2,3], 1)"
+ qt_sql "select element_at([1,2,3], 3)"
+ qt_sql "select element_at([1,2,3], 4)"
+ qt_sql "select element_at([1,2,3], -1)"
+ qt_sql "select element_at([1,2,3], NULL)"
+ qt_sql "select element_at([1,2,NULL], 3)"
+ qt_sql "select element_at([1,2,NULL], 2)"
+ qt_sql "select element_at([], -1)"
+ qt_sql "select element_at([true, NULL, false], 2)"
+ qt_sql "select element_at(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), 1)"
+ qt_sql "select element_at(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), 2)"
+ qt_sql "select element_at(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), 1)"
+
+ // array subscript function
+ qt_sql "select [1,2,3][1]"
+ qt_sql "select [1,2,3][3]"
+ qt_sql "select [1,2,3][4]"
+ qt_sql "select [1,2,3][-1]"
+ qt_sql "select [1,2,3][NULL]"
+ qt_sql "select [1,2,NULL][3]"
+ qt_sql "select [1,2,NULL][2]"
+ qt_sql "select [][-1]"
+ qt_sql "select [true, false]"
+ qt_sql "select (array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[2]"
+ qt_sql "select (array(cast ('2023-02-04' as datev2),cast ('2023-02-05'
as datev2)))[2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[2]"
+
+ // array_aggregation function
+ qt_sql "select array_avg([1,2,3])"
+ qt_sql "select array_sum([1,2,3])"
+ qt_sql "select array_min([1,2,3])"
+ qt_sql "select array_max([1,2,3])"
+ qt_sql "select array_product([1,2,3])"
+ qt_sql "select array_avg([1,2,3,null])"
+ qt_sql "select array_sum([1,2,3,null])"
+ qt_sql "select array_min([1,2,3,null])"
+ qt_sql "select array_max([1,2,3,null])"
+ qt_sql "select array_product([1,2,3,null])"
+ qt_sql "select array_avg([])"
+ qt_sql "select array_sum([])"
+ qt_sql "select array_min([])"
+ qt_sql "select array_max([])"
+ qt_sql "select array_product([])"
+ qt_sql "select array_avg([null])"
+ qt_sql "select array_sum([null])"
+ qt_sql "select array_min([null])"
+ qt_sql "select array_max([null])"
+ qt_sql "select array_product([null])"
+ qt_sql "select array_product([1.12, 3.45, 4.23])"
+ qt_sql "select array_product([1.12, 3.45, -4.23])"
+ qt_sql "select array_min(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_max(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_min(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_max(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_avg(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_sum(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_min(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_max(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_product(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_distinct function
+ qt_sql "select array_distinct([1,1,2,2,3,3])"
+ qt_sql "select array_distinct([1,1,2,2,3,3,null])"
+ qt_sql "select array_distinct([1,1,3,3,null, null, null])"
+ qt_sql "select array_distinct(['a','a','a'])"
+ qt_sql "select array_distinct([null, 'a','a','a', null])"
+ qt_sql "select array_distinct([true, false, false, null])"
+ qt_sql "select array_distinct([])"
+ qt_sql "select array_distinct([null,null])"
+ qt_sql "select array_distinct([1, 0, 0, null])"
+ qt_sql "select array_distinct(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)),cast
('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_distinct(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql "select array_distinct(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+
+ // array_remove function
+ qt_sql "select array_remove([1,2,3], 1)"
+ qt_sql "select array_remove([1,2,3,null], 1)"
+ qt_sql "select array_remove(['a','b','c'], 'a')"
+ qt_sql "select array_remove(['a','b','c',null], 'a')"
+ qt_sql "select array_remove([true, false, false], false)"
+ qt_sql "select array_remove(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_remove(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_remove(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_sort function
+ qt_sql_array_sort1 "select array_sort([1,2,3])"
+ qt_sql_array_sort2 "select array_sort([3,2,1])"
+ qt_sql_array_sort3 "select array_sort([1,2,3,null])"
+ qt_sql_array_sort4 "select array_sort([null,1,2,3])"
+ qt_sql_array_sort5 "select array_sort(['a','b','c'])"
+ qt_sql_array_sort6 "select array_sort(['c','b','a'])"
+ qt_sql_array_sort7 "select array_sort([true, false, true])"
+ qt_sql_array_sort8 "select array_sort([])"
+ qt_sql_array_sort9 "select array_sort(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_sort10 "select array_sort(array(cast ('2023-02-06' as
datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_sort11 "select array_sort(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_reverse_sort function
+ qt_sql_array_reverse_sort1 "select array_reverse_sort([1,2,3])"
+ qt_sql_array_reverse_sort2 "select array_reverse_sort([3,2,1])"
+ qt_sql_array_reverse_sort3 "select array_reverse_sort([1,2,3,null])"
+ qt_sql_array_reverse_sort4 "select array_reverse_sort([null,1,2,3])"
+ qt_sql_array_reverse_sort5 "select array_reverse_sort(['a','b','c'])"
+ qt_sql_array_reverse_sort6 "select array_reverse_sort(['c','b','a'])"
+ qt_sql_array_reverse_sort7 "select array_reverse_sort([true, false,
true])"
+ qt_sql_array_reverse_sort8 "select array_reverse_sort([])"
+ qt_sql_array_reverse_sort9 "select array_reverse_sort(array(cast
('2023-02-06 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_reverse_sort10 "select array_reverse_sort(array(cast
('2023-02-06' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_reverse_sort11 "select array_reverse_sort(array(cast
(111.111 as decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_overlap function
+ qt_sql "select arrays_overlap([1,2,3], [4,5,6])"
+ qt_sql "select arrays_overlap([1,2,3], [3,4,5])"
+ qt_sql "select arrays_overlap([1,2,3,null], [3,4,5])"
+ qt_sql "select arrays_overlap([true], [false])"
+ qt_sql "select arrays_overlap([], [])"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-08 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-08' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_binary function
+ qt_sql "select array_union([1,2,3], [2,3,4])"
+ qt_sql "select array_except([1,2,3], [2,3,4])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4])"
+ qt_sql "select array_union([1,2,3], [2,3,4,null])"
+ qt_sql "select array_except([1,2,3], [2,3,4,null])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4,null])"
+ qt_sql "select array_union([true], [false])"
+ qt_sql "select array_except([true, false], [true])"
+ qt_sql "select array_intersect([false, true], [false])"
+ qt_sql "select array_union([], [])"
+ qt_sql "select array_except([], [])"
+ qt_sql "select array_intersect([], [])"
+ qt_sql "select array_union([], [1,2,3])"
+ qt_sql "select array_except([], [1,2,3])"
+ qt_sql "select array_intersect([], [1,2,3])"
+ qt_sql "select array_union([null], [1,2,3])"
+ qt_sql "select array_except([null], [1,2,3])"
+ qt_sql "select array_intersect([null], [1,2,3])"
+ qt_sql "select array_union([1], [100000000])"
+ qt_sql "select array_except([1], [100000000])"
+ qt_sql "select array_intersect([1], [100000000])"
+ qt_sql "select array_union(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_except(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06 22:07:34.999'
as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_union(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_except(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_union(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
+ qt_sql "select array_except(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_intersect(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_slice function
+ qt_sql "select [1,2,3][1:1]"
+ qt_sql "select [1,2,3][1:3]"
+ qt_sql "select [1,2,3][1:5]"
+ qt_sql "select [1,2,3][2:]"
+ qt_sql "select [1,2,3][-2:]"
+ qt_sql "select [1,2,3][2:-1]"
+ qt_sql "select [1,2,3][0:]"
+ qt_sql "select [1,2,3][-5:]"
+ qt_sql "select [true, false, false][2:]"
+ qt_sql "select (array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[1:2]"
+ qt_sql "select (array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)))[1:2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[1:2]"
+
+ // array_intersect
+ qt_sql_intersect_1 "select array_intersect([1,2,3], [1,2,3], [null])"
+ qt_sql_intersect_2 "select array_intersect([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_intersect_3 "select array_intersect([1,2,3, null],
[1,2,3,null], [1,2,null], [1, null])"
+ qt_sql_intersect_4 "select array_intersect([1,2,3], [1,2,3], [null],
[])"
+
+ // array_union-with-multiple-arguments
+ qt_sql_union_1 "select array_union([1,2,3], [1,2,3], [null])"
+ qt_sql_union_2 "select array_union([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_union_3 "select array_union([1,2,3, null], [1,2,3,null],
[1,2,null], [1, null])"
+ qt_sql_union_4 "select array_union([1,2,3], [1,2,3], [null], [])"
+
+ // array_popfront function
+ qt_sql "select array_popfront([1,2,3,4,5,6])"
+ qt_sql "select array_popfront([])"
+ qt_sql "select array_popfront(null)"
+ qt_sql "select array_popfront([null,2,3,4,5])"
+ qt_sql "select array_popfront([1,2,3,4,null])"
+ qt_sql "select array_popfront(['1','2','3','4','5','6'])"
+ qt_sql "select array_popfront([null,'2','3','4','5','6'])"
+ qt_sql "select array_popfront(['1','2','3','4','5',null])"
+ qt_sql "select array_popfront(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(null, cast ('2023-02-06' as
datev2), cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_popfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_join function
+ qt_sql "select array_join([1, 2, 3], '_')"
+ qt_sql "select array_join(['1', '2', '3', null], '_')"
+ qt_sql "select array_join([null, '1', '2', '3', null], '_')"
+ qt_sql "select array_join(['', '2', '3'], '_')"
+ qt_sql "select array_join(['1', '2', ''], '_')"
+ qt_sql "select array_join(['1', '2', '', null], '_')"
+ qt_sql "select array_join(['', '', '3'], '_')"
+ qt_sql "select array_join(['1', '2', '', ''], '_')"
+ qt_sql "select array_join([null, null, '1', '2', '', '', null], '_')"
+ qt_sql "select array_join([null, null, 1, 2, '', '', null], '_',
'any')"
+ qt_sql "select array_join([''], '_')"
+ qt_sql "select array_join(['', ''], '_')"
+ qt_sql_array_with_constant1 "select array_with_constant(3, '_'),
array_repeat('_', 3)"
+ qt_sql_array_with_constant2 "select array_with_constant(2, '1'),
array_repeat('1', 2)"
+ qt_sql_array_with_constant3 "select array_with_constant(4, 1223),
array_repeat(1223, 4)"
+ qt_sql_array_with_constant4 "select array_with_constant(8, null),
array_repeat(null, 8)"
+ qt_sql_array_with_constant5 "select array_with_constant(null, 'abc'),
array_repeat('abc', null)"
+ qt_sql_array_with_constant6 "select array_with_constant(null, null),
array_repeat(null, null)"
+ // array_compact function
+ qt_sql "select array_compact([1, 2, 3, 3, null, null, 4, 4])"
+ qt_sql "select array_compact([null, null, null])"
+ qt_sql "select array_compact([1.2, 1.2, 3.4, 3.3, 2.1])"
+ qt_sql "select array_compact(['a','b','c','c','d'])"
+ qt_sql "select array_compact(['aaa','aaa','bbb','ccc','ccccc',null,
null,'dddd'])"
+ qt_sql "select array_compact(['2015-03-13','2015-03-13'])"
+ qt_sql "select array_compact(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_compact(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast ('2023-02-05' as
datev2)))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3)),cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_apply
+ qt_sql """select array_apply([1000000, 1000001, 1000002], '=',
1000002)"""
+ qt_sql """select array_apply([1.111, 2.222, 3.333], '>=', 2)"""
+ qt_sql """select array_apply(cast(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17") as array<datetimev2>), ">", '2020-01-02')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), ">", '25')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), "!=", '25')"""
+ // qt_sql """select array_apply(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), ">", '111.111')"""
+
+ qt_sql "select array_concat([1, 2, 3], [2, 3, 4], [8, 1, 2], [9])"
+ qt_sql "select array_concat([12, 23], [25, null], [null], [66])"
+ qt_sql "select array_concat([1.2, 1.8], [9.0, 2.2], [2.8])"
+ qt_sql "select array_concat(['aaa', null], ['bbb', 'fff'], [null,
'ccc'])"
+ qt_sql "select array_concat(null, [1, 2, 3], null)"
+ qt_sql "select array_concat(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), array(cast (999.28 as decimal(10,3)), cast (123.99
as decimal(10,3))))"
+ qt_sql "select array_concat(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), array(cast ('2023-02-01' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_concat(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+
+ // array_shuffle
+ // do not check result, since shuffle result is random
+ sql "SELECT array_sum(array_shuffle([1, 2, 3, 3, null, null, 4, 4])),
array_shuffle([1, 2, 3, 3, null, null, 4, 4], 0), shuffle([1, 2, 3, 3, null,
null, 4, 4], 0)"
+ sql "SELECT array_sum(array_shuffle([1.111, 2.222, 3.333])),
array_shuffle([1.111, 2.222, 3.333], 0), shuffle([1.111, 2.222, 3.333], 0)"
+ sql "SELECT array_size(array_shuffle(['aaa', null, 'bbb', 'fff'])),
array_shuffle(['aaa', null, 'bbb', 'fff'], 0), shuffle(['aaa', null, 'bbb',
'fff'], 0)"
+ sql """select array_size(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17")), array_shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0), shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0)"""
+
+ // array_zip
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'])"
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h',
'i'])"
+ qt_sql "select array_zip([1, 2, 3, 4, 5], ['d', 'o', 'r', 'i', 's'])"
+ qt_sql "select array_zip([1.1, 2.2, 3.3], [1, 2, 3])"
+ qt_sql "select array_zip([1, null, 3], [null, 'b', null])"
+ qt_sql "select array_zip(array(cast (3.05 as decimal(10,3)), cast
(2.22 as decimal(10,3))), array(cast (3.14 as decimal(10,3)), cast (6.66 as
decimal(10,3))))"
+ qt_sql "select array_zip(array(cast ('2000-03-05' as datev2), cast
('2023-03-10' as datev2)), array(cast ('2000-02-02' as datev2), cast
('2023-03-10' as datev2)))"
+ qt_sql "select array_zip(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+ qt_sql "select array_zip([1, 2, 3], null, ['foo', 'bar', 'test'])"
+
+ qt_sql "select array(8, null)"
+ qt_sql "select array('a', 1, 2)"
+ qt_sql "select array(null, null, null)"
+
+ // array_enumerate_uniq
+ qt_sql "select array_enumerate_uniq([])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1,
2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 3, 3, 4, 4])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 1, 3, 4, 2, 5, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2,
1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([null])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, null, null])"
+ qt_sql "select array_enumerate_uniq([1, null, 2, null, 3, null, 4, 1,
null, 2, null, 3, null, 4])"
+ qt_sql "select array_enumerate_uniq(['11', '22', '33', '11', '33',
'22'])"
+ qt_sql "select array_enumerate_uniq(array(cast (24.99 as
decimal(10,3)), cast (25.99 as decimal(10,3)), cast (24.99 as decimal(10,3))))"
+ qt_sql "select array_enumerate_uniq(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)), cast ('2023-02-04 23:07:34.999' as
datetimev2(3)), cast ('2023-02-06 22:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_enumerate_uniq(array(cast (384.2933 as
decimalv3(7, 4)), cast (984.1913 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4)), cast (722.9333 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4))))"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5], [1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 1, 2], [1, 2, 1, 2,
2, 1])"
+ qt_sql "select array_enumerate_uniq([1, null, 1, null], [null, 1,
null, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 1], [2, 1, 2, 1,
2, 1], [3, 1, 3, 1, 3, 1])"
+ qt_sql "select array_enumerate_uniq([1, 3, 1], [2.0, 5.0, 2.0], ['3',
'8', '3'], array(cast (34.9876 as decimalv3(6, 4)), cast (89.9865 as
decimalv3(6, 4)), cast (34.9876 as decimalv3(6, 4))))"
+
+ // array_pushfront
+ qt_sql "select array_pushfront([1, 2, 3], 6)"
+ qt_sql "select array_pushfront([1, 2, 3], null)"
+ qt_sql "select array_pushfront(null, 6)"
+ qt_sql "select array_pushfront([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushfront(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushfront(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05 12:23:24.999'
as datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+
+ // array_pushback
+ qt_sql "select array_pushback([1, 2, 3], 6)"
+ qt_sql "select array_pushback([1, 2, 3], null)"
+ qt_sql "select array_pushback(null, 6)"
+ qt_sql "select array_pushback([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushback(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushback(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushback(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+ qt_sql "select array_pushback([null,null], null)"
+ qt_sql "select array_pushback([null,null,null,null], 80)"
+
+ // array_cum_sum
+ qt_sql "select array_cum_sum([0, 2, 127])"
+ qt_sql "select array_cum_sum([254, 4, 0])"
+ qt_sql "select array_cum_sum([1.0, 2.1 ,3.2, 4.3, 5.4])"
+ qt_sql "select array_cum_sum([-1, 2 ,-3, 4, -5])"
+ qt_sql "select array_cum_sum([-5.23, 4.12, -3.02, 2.00 ,1.01])"
+ qt_sql "select array_cum_sum([1, 2, 3, null])"
+ qt_sql "select array_cum_sum([null, 1, null, 3, 8, null])"
+ qt_sql "select array_cum_sum([null, null])"
+ qt_sql "select array_cum_sum([8])"
+ qt_sql "select array_cum_sum([1.1])"
+ qt_sql "select array_cum_sum([null])"
+ qt_sql "select array_cum_sum([])"
+ qt_sql "select array_cum_sum(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3)), cast (999.28 as decimal(10,3))))"
+ qt_sql "select array_cum_sum(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+ qt_sql "select array_cum_sum(array(cast (11.9999 as
decimalv3(6,4)),cast (22.0001 as decimalv3(6,4))))"
+
+ // abnormal test
+ test {
+ sql "select array_intersect([1, 2, 3, 1, 2, 3], '1[3, 2, 5]')"
+ exception "No matching function with signature"
+ }
+
+ // array_min/max with nested array for args
+ test {
+ sql "select array_min(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+
+ test {
+ sql "select array_min(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+
+ // array_map with string is can be succeed
+ qt_sql_array_map """ select array_map(x->x!='',
split_by_string('amory,is,better,committing', ',')) """
+
+ // array_apply with string should be failed
+ test {
+ sql """select
array_apply(split_by_string("amory,is,better,committing", ","), '!=', '');"""
+ exception("No matching function with")
+ }
+
+ qt_sql """ SELECT ARRAY_AVG(CAST([] AS ARRAY < DECIMALV3(1,0) > )); """
+
+ // set session variable for enale_fold_constant_by_be and
enable_decimal256
+ sql """ set enable_fold_constant_by_be = 0 """
+
+ // array function
+ // array_nested function
+ qt_sql "select a from (select array(1, 1, 2, 2, 2, 2) as a) t"
+
+ // array with decimal and other types
+ qt_sql_1 """select array(1.0,2.0,null, null,2.0);"""
+ qt_sql_1 """select array('a',2.0,null, null,2.0);"""
+ qt_sql_1 """select array(1,2.0,null, null,2.0);"""
+ qt_sql_2 """select array_sort(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_3 """select array_min(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_4 """select array_max(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_5 """select array_avg(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_6 """select array_sum(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_7 """select array_product(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_8 """select array_distinct(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_9 """select array_sort(array_intersect(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_10 """select array_sort(array_except(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_11 """select array_sort(array_union(array(1.0,2.0,null,
null,2.0), array(1.0,2.0,null, null,2.0)));"""
+ qt_sql_14 """select array_popfront(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_15 """select array_popback(array(1.0,2.0,null, null,2.0));"""
+ qt_sql_16 """select array_concat(array(1.0,2.0,null, null,2.0),
array(1.0,2.0,null, null,2.0));"""
+
+
+ // array_contains function
+ qt_sql "select array_contains([1.111, 2, 3.333], 2);"
+ qt_sql "select array_contains([1,2,3], 1)"
+ qt_sql "select array_contains([1,2,3], 4)"
+ qt_sql "select array_contains([1,2,3,NULL], 1)"
+ qt_sql "select array_contains([1,2,3,NULL], NULL)"
+ qt_sql "select array_contains([], true)"
+ qt_sql "select array_contains([], NULL)"
+ qt_sql "select array_contains(NULL, 1)"
+ qt_sql "select array_contains(NULL, NULL)"
+ qt_sql "select array_contains([true], false)"
+ qt_sql "select array_contains(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_contains(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_contains(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_position function
+ qt_sql "select array_position([1.111, 2, 3.333], 2);"
+ qt_sql "select array_position([1,2,3], 1)"
+ qt_sql "select array_position([1,2,3], 3)"
+ qt_sql "select array_position([1,2,3], 4)"
+ qt_sql "select array_position([NULL,2,3], 2)"
+ qt_sql "select array_position([NULL,2,3], NULL)"
+ qt_sql "select array_position([], true)"
+ qt_sql "select array_position([], NULL)"
+ qt_sql "select array_position(NULL, 1)"
+ qt_sql "select array_position(NULL, NULL)"
+ qt_sql "select array_position([null], true)"
+ qt_sql "select array_position([0], null)"
+ qt_sql "select array_position([null, '1'], '')"
+ qt_sql "select array_position([''], null)"
+ qt_sql "select array_position([false, NULL, true], true)"
+ qt_sql "select array_position(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_position(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_position(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // element_at function
+ qt_sql "select element_at([1,2,3], 1)"
+ qt_sql "select element_at([1,2,3], 3)"
+ qt_sql "select element_at([1,2,3], 4)"
+ qt_sql "select element_at([1,2,3], -1)"
+ qt_sql "select element_at([1,2,3], NULL)"
+ qt_sql "select element_at([1,2,NULL], 3)"
+ qt_sql "select element_at([1,2,NULL], 2)"
+ qt_sql "select element_at([], -1)"
+ qt_sql "select element_at([true, NULL, false], 2)"
+ qt_sql "select element_at(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), 1)"
+ qt_sql "select element_at(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), 2)"
+ qt_sql "select element_at(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), 1)"
+
+ // array subscript function
+ qt_sql "select [1,2,3][1]"
+ qt_sql "select [1,2,3][3]"
+ qt_sql "select [1,2,3][4]"
+ qt_sql "select [1,2,3][-1]"
+ qt_sql "select [1,2,3][NULL]"
+ qt_sql "select [1,2,NULL][3]"
+ qt_sql "select [1,2,NULL][2]"
+ qt_sql "select [][-1]"
+ qt_sql "select [true, false]"
+ qt_sql "select (array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[2]"
+ qt_sql "select (array(cast ('2023-02-04' as datev2),cast ('2023-02-05'
as datev2)))[2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[2]"
+
+ // array_aggregation function
+ qt_sql "select array_avg([1,2,3])"
+ qt_sql "select array_sum([1,2,3])"
+ qt_sql "select array_min([1,2,3])"
+ qt_sql "select array_max([1,2,3])"
+ qt_sql "select array_product([1,2,3])"
+ qt_sql "select array_avg([1,2,3,null])"
+ qt_sql "select array_sum([1,2,3,null])"
+ qt_sql "select array_min([1,2,3,null])"
+ qt_sql "select array_max([1,2,3,null])"
+ qt_sql "select array_product([1,2,3,null])"
+ qt_sql "select array_avg([])"
+ qt_sql "select array_sum([])"
+ qt_sql "select array_min([])"
+ qt_sql "select array_max([])"
+ qt_sql "select array_product([])"
+ qt_sql "select array_avg([null])"
+ qt_sql "select array_sum([null])"
+ qt_sql "select array_min([null])"
+ qt_sql "select array_max([null])"
+ qt_sql "select array_product([null])"
+ qt_sql "select array_product([1.12, 3.45, 4.23])"
+ qt_sql "select array_product([1.12, 3.45, -4.23])"
+ qt_sql "select array_min(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_max(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_min(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_max(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_avg(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_sum(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_min(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_max(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))))"
+ qt_sql "select array_product(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_distinct function
+ qt_sql "select array_distinct([1,1,2,2,3,3])"
+ qt_sql "select array_distinct([1,1,2,2,3,3,null])"
+ qt_sql "select array_distinct([1,1,3,3,null, null, null])"
+ qt_sql "select array_distinct(['a','a','a'])"
+ qt_sql "select array_distinct([null, 'a','a','a', null])"
+ qt_sql "select array_distinct([true, false, false, null])"
+ qt_sql "select array_distinct([])"
+ qt_sql "select array_distinct([null,null])"
+ qt_sql "select array_distinct([1, 0, 0, null])"
+ qt_sql "select array_distinct(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)),cast
('2023-02-04 23:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_distinct(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql "select array_distinct(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+
+ // array_remove function
+ qt_sql "select array_remove([1,2,3], 1)"
+ qt_sql "select array_remove([1,2,3,null], 1)"
+ qt_sql "select array_remove(['a','b','c'], 'a')"
+ qt_sql "select array_remove(['a','b','c',null], 'a')"
+ qt_sql "select array_remove([true, false, false], false)"
+ qt_sql "select array_remove(array(cast ('2023-02-04 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))), cast
('2023-02-04 22:07:34.999' as datetimev2(3)))"
+ qt_sql "select array_remove(array(cast ('2023-02-04' as datev2),cast
('2023-02-05' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_remove(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (111.111 as
decimalv3(6,3)))"
+
+ // array_sort function
+ qt_sql_array_sort1 "select array_sort([1,2,3])"
+ qt_sql_array_sort2 "select array_sort([3,2,1])"
+ qt_sql_array_sort3 "select array_sort([1,2,3,null])"
+ qt_sql_array_sort4 "select array_sort([null,1,2,3])"
+ qt_sql_array_sort5 "select array_sort(['a','b','c'])"
+ qt_sql_array_sort6 "select array_sort(['c','b','a'])"
+ qt_sql_array_sort7 "select array_sort([true, false, true])"
+ qt_sql_array_sort8 "select array_sort([])"
+ qt_sql_array_sort9 "select array_sort(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_sort10 "select array_sort(array(cast ('2023-02-06' as
datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_sort11 "select array_sort(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_reverse_sort function
+ qt_sql_array_reverse_sort1 "select array_reverse_sort([1,2,3])"
+ qt_sql_array_reverse_sort2 "select array_reverse_sort([3,2,1])"
+ qt_sql_array_reverse_sort3 "select array_reverse_sort([1,2,3,null])"
+ qt_sql_array_reverse_sort4 "select array_reverse_sort([null,1,2,3])"
+ qt_sql_array_reverse_sort5 "select array_reverse_sort(['a','b','c'])"
+ qt_sql_array_reverse_sort6 "select array_reverse_sort(['c','b','a'])"
+ qt_sql_array_reverse_sort7 "select array_reverse_sort([true, false,
true])"
+ qt_sql_array_reverse_sort8 "select array_reverse_sort([])"
+ qt_sql_array_reverse_sort9 "select array_reverse_sort(array(cast
('2023-02-06 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql_array_reverse_sort10 "select array_reverse_sort(array(cast
('2023-02-06' as datev2),cast ('2023-02-05' as datev2)))"
+ qt_sql_array_reverse_sort11 "select array_reverse_sort(array(cast
(111.111 as decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_overlap function
+ qt_sql "select arrays_overlap([1,2,3], [4,5,6])"
+ qt_sql "select arrays_overlap([1,2,3], [3,4,5])"
+ qt_sql "select arrays_overlap([1,2,3,null], [3,4,5])"
+ qt_sql "select arrays_overlap([true], [false])"
+ qt_sql "select arrays_overlap([], [])"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-08 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-08' as datev2)))"
+ qt_sql "select arrays_overlap(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_binary function
+ qt_sql "select array_union([1,2,3], [2,3,4])"
+ qt_sql "select array_except([1,2,3], [2,3,4])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4])"
+ qt_sql "select array_union([1,2,3], [2,3,4,null])"
+ qt_sql "select array_except([1,2,3], [2,3,4,null])"
+ qt_sql "select array_intersect([1,2,3], [2,3,4,null])"
+ qt_sql "select array_union([true], [false])"
+ qt_sql "select array_except([true, false], [true])"
+ qt_sql "select array_intersect([false, true], [false])"
+ qt_sql "select array_union([], [])"
+ qt_sql "select array_except([], [])"
+ qt_sql "select array_intersect([], [])"
+ qt_sql "select array_union([], [1,2,3])"
+ qt_sql "select array_except([], [1,2,3])"
+ qt_sql "select array_intersect([], [1,2,3])"
+ qt_sql "select array_union([null], [1,2,3])"
+ qt_sql "select array_except([null], [1,2,3])"
+ qt_sql "select array_intersect([null], [1,2,3])"
+ qt_sql "select array_union([1], [100000000])"
+ qt_sql "select array_except([1], [100000000])"
+ qt_sql "select array_intersect([1], [100000000])"
+ qt_sql "select array_union(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_except(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06 22:07:34.999'
as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))),array(cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_union(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_except(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_intersect(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2)), array(cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_union(array(cast (111.111 as decimalv3(6,3)),cast
(222.222 as decimalv3(6,3))), array(cast (222.222 as decimalv3(6,3)),cast
(333.333 as decimalv3(6,3))))"
+ qt_sql "select array_except(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_intersect(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), array(cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+
+ // array_slice function
+ qt_sql "select [1,2,3][1:1]"
+ qt_sql "select [1,2,3][1:3]"
+ qt_sql "select [1,2,3][1:5]"
+ qt_sql "select [1,2,3][2:]"
+ qt_sql "select [1,2,3][-2:]"
+ qt_sql "select [1,2,3][2:-1]"
+ qt_sql "select [1,2,3][0:]"
+ qt_sql "select [1,2,3][-5:]"
+ qt_sql "select [true, false, false][2:]"
+ qt_sql "select (array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3))))[1:2]"
+ qt_sql "select (array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2)))[1:2]"
+ qt_sql "select (array(cast (111.111 as decimalv3(6,3)),cast (222.222
as decimalv3(6,3))))[1:2]"
+
+ // array_intersect
+ qt_sql_intersect_1 "select array_intersect([1,2,3], [1,2,3], [null])"
+ qt_sql_intersect_2 "select array_intersect([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_intersect_3 "select array_intersect([1,2,3, null],
[1,2,3,null], [1,2,null], [1, null])"
+ qt_sql_intersect_4 "select array_intersect([1,2,3], [1,2,3], [null],
[])"
+
+ // array_union-with-multiple-arguments
+ qt_sql_union_1 "select array_union([1,2,3], [1,2,3], [null])"
+ qt_sql_union_2 "select array_union([1, 2, null], [1, 3, null],
[1,2,3,null])"
+ qt_sql_union_3 "select array_union([1,2,3, null], [1,2,3,null],
[1,2,null], [1, null])"
+ qt_sql_union_4 "select array_union([1,2,3], [1,2,3], [null], [])"
+
+ // array_popfront function
+ qt_sql "select array_popfront([1,2,3,4,5,6])"
+ qt_sql "select array_popfront([])"
+ qt_sql "select array_popfront(null)"
+ qt_sql "select array_popfront([null,2,3,4,5])"
+ qt_sql "select array_popfront([1,2,3,4,null])"
+ qt_sql "select array_popfront(['1','2','3','4','5','6'])"
+ qt_sql "select array_popfront([null,'2','3','4','5','6'])"
+ qt_sql "select array_popfront(['1','2','3','4','5',null])"
+ qt_sql "select array_popfront(array(cast ('2023-02-06' as datev2),
cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(null, cast ('2023-02-06' as
datev2), cast ('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_popfront(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_popfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_join function
+ qt_sql "select array_join([1, 2, 3], '_')"
+ qt_sql "select array_join(['1', '2', '3', null], '_')"
+ qt_sql "select array_join([null, '1', '2', '3', null], '_')"
+ qt_sql "select array_join(['', '2', '3'], '_')"
+ qt_sql "select array_join(['1', '2', ''], '_')"
+ qt_sql "select array_join(['1', '2', '', null], '_')"
+ qt_sql "select array_join(['', '', '3'], '_')"
+ qt_sql "select array_join(['1', '2', '', ''], '_')"
+ qt_sql "select array_join([null, null, '1', '2', '', '', null], '_')"
+ qt_sql "select array_join([null, null, 1, 2, '', '', null], '_',
'any')"
+ qt_sql "select array_join([''], '_')"
+ qt_sql "select array_join(['', ''], '_')"
+ qt_sql_array_with_constant1 "select array_with_constant(3, '_'),
array_repeat('_', 3)"
+ qt_sql_array_with_constant2 "select array_with_constant(2, '1'),
array_repeat('1', 2)"
+ qt_sql_array_with_constant3 "select array_with_constant(4, 1223),
array_repeat(1223, 4)"
+ qt_sql_array_with_constant4 "select array_with_constant(8, null),
array_repeat(null, 8)"
+ qt_sql_array_with_constant5 "select array_with_constant(null, 'abc'),
array_repeat('abc', null)"
+ qt_sql_array_with_constant6 "select array_with_constant(null, null),
array_repeat(null, null)"
+ // array_compact function
+ qt_sql "select array_compact([1, 2, 3, 3, null, null, 4, 4])"
+ qt_sql "select array_compact([null, null, null])"
+ qt_sql "select array_compact([1.2, 1.2, 3.4, 3.3, 2.1])"
+ qt_sql "select array_compact(['a','b','c','c','d'])"
+ qt_sql "select array_compact(['aaa','aaa','bbb','ccc','ccccc',null,
null,'dddd'])"
+ qt_sql "select array_compact(['2015-03-13','2015-03-13'])"
+ qt_sql "select array_compact(array(cast ('2023-02-06 22:07:34.999' as
datetimev2(3)),cast ('2023-02-04 23:07:34.999' as datetimev2(3)), cast
('2023-02-07 22:07:34.999' as datetimev2(3)),cast ('2023-02-04 23:07:34.999' as
datetimev2(3))))"
+ qt_sql "select array_compact(array(cast ('2023-02-06' as datev2), cast
('2023-02-05' as datev2), cast ('2023-02-07' as datev2), cast ('2023-02-05' as
datev2)))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3)),cast (222.222 as
decimalv3(6,3)),cast (333.333 as decimalv3(6,3))))"
+ qt_sql "select array_compact(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+
+ // array_apply
+ qt_sql """select array_apply([1000000, 1000001, 1000002], '=',
1000002)"""
+ qt_sql """select array_apply([1.111, 2.222, 3.333], '>=', 2)"""
+ qt_sql """select array_apply(cast(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17") as array<datetimev2>), ">", '2020-01-02')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), ">", '25')"""
+ qt_sql """select array_apply(array(cast (24.99 as decimal(10,3)),cast
(25.99 as decimal(10,3))), "!=", '25')"""
+ // qt_sql """select array_apply(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), ">", '111.111')"""
+
+ qt_sql "select array_concat([1, 2, 3], [2, 3, 4], [8, 1, 2], [9])"
+ qt_sql "select array_concat([12, 23], [25, null], [null], [66])"
+ qt_sql "select array_concat([1.2, 1.8], [9.0, 2.2], [2.8])"
+ qt_sql "select array_concat(['aaa', null], ['bbb', 'fff'], [null,
'ccc'])"
+ qt_sql "select array_concat(null, [1, 2, 3], null)"
+ qt_sql "select array_concat(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3))), array(cast (999.28 as decimal(10,3)), cast (123.99
as decimal(10,3))))"
+ qt_sql "select array_concat(array(cast ('2023-03-05' as datev2), cast
('2023-03-04' as datev2)), array(cast ('2023-02-01' as datev2), cast
('2023-02-05' as datev2)))"
+ qt_sql "select array_concat(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+
+ // array_shuffle
+ // do not check result, since shuffle result is random
+ sql "SELECT array_sum(array_shuffle([1, 2, 3, 3, null, null, 4, 4])),
array_shuffle([1, 2, 3, 3, null, null, 4, 4], 0), shuffle([1, 2, 3, 3, null,
null, 4, 4], 0)"
+ sql "SELECT array_sum(array_shuffle([1.111, 2.222, 3.333])),
array_shuffle([1.111, 2.222, 3.333], 0), shuffle([1.111, 2.222, 3.333], 0)"
+ sql "SELECT array_size(array_shuffle(['aaa', null, 'bbb', 'fff'])),
array_shuffle(['aaa', null, 'bbb', 'fff'], 0), shuffle(['aaa', null, 'bbb',
'fff'], 0)"
+ sql """select array_size(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17")), array_shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0), shuffle(array("2020-01-02", "2022-01-03",
"2021-01-01", "1996-04-17"), 0)"""
+
+ // array_zip
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'])"
+ qt_sql "select array_zip(['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h',
'i'])"
+ qt_sql "select array_zip([1, 2, 3, 4, 5], ['d', 'o', 'r', 'i', 's'])"
+ qt_sql "select array_zip([1.1, 2.2, 3.3], [1, 2, 3])"
+ qt_sql "select array_zip([1, null, 3], [null, 'b', null])"
+ qt_sql "select array_zip(array(cast (3.05 as decimal(10,3)), cast
(2.22 as decimal(10,3))), array(cast (3.14 as decimal(10,3)), cast (6.66 as
decimal(10,3))))"
+ qt_sql "select array_zip(array(cast ('2000-03-05' as datev2), cast
('2023-03-10' as datev2)), array(cast ('2000-02-02' as datev2), cast
('2023-03-10' as datev2)))"
+ qt_sql "select array_zip(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))))"
+ qt_sql "select array_zip([1, 2, 3], null, ['foo', 'bar', 'test'])"
+
+ qt_sql "select array(8, null)"
+ qt_sql "select array('a', 1, 2)"
+ qt_sql "select array(null, null, null)"
+
+ // array_enumerate_uniq
+ qt_sql "select array_enumerate_uniq([])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1,
2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 3, 3, 4, 4])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 1, 3, 4, 2, 5, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2,
1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([null])"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5, null, null])"
+ qt_sql "select array_enumerate_uniq([1, null, 2, null, 3, null, 4, 1,
null, 2, null, 3, null, 4])"
+ qt_sql "select array_enumerate_uniq(['11', '22', '33', '11', '33',
'22'])"
+ qt_sql "select array_enumerate_uniq(array(cast (24.99 as
decimal(10,3)), cast (25.99 as decimal(10,3)), cast (24.99 as decimal(10,3))))"
+ qt_sql "select array_enumerate_uniq(array(cast ('2023-02-06
22:07:34.999' as datetimev2(3)), cast ('2023-02-04 23:07:34.999' as
datetimev2(3)), cast ('2023-02-06 22:07:34.999' as datetimev2(3))))"
+ qt_sql "select array_enumerate_uniq(array(cast (384.2933 as
decimalv3(7, 4)), cast (984.1913 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4)), cast (722.9333 as decimalv3(7, 4)), cast (384.2933 as
decimalv3(7, 4))))"
+ qt_sql "select array_enumerate_uniq([1, 2, 3, 4, 5], [1, 2, 3, 4, 5])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1], [1, 1, 1, 1, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 2, 2, 1, 2], [1, 2, 1, 2,
2, 1])"
+ qt_sql "select array_enumerate_uniq([1, null, 1, null], [null, 1,
null, 1])"
+ qt_sql "select array_enumerate_uniq([1, 1, 1, 1, 1, 1], [2, 1, 2, 1,
2, 1], [3, 1, 3, 1, 3, 1])"
+ qt_sql "select array_enumerate_uniq([1, 3, 1], [2.0, 5.0, 2.0], ['3',
'8', '3'], array(cast (34.9876 as decimalv3(6, 4)), cast (89.9865 as
decimalv3(6, 4)), cast (34.9876 as decimalv3(6, 4))))"
+
+ // array_pushfront
+ qt_sql "select array_pushfront([1, 2, 3], 6)"
+ qt_sql "select array_pushfront([1, 2, 3], null)"
+ qt_sql "select array_pushfront(null, 6)"
+ qt_sql "select array_pushfront([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushfront(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushfront(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushfront(array(cast ('2023-03-05 12:23:24.999'
as datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushfront(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+
+ // array_pushback
+ qt_sql "select array_pushback([1, 2, 3], 6)"
+ qt_sql "select array_pushback([1, 2, 3], null)"
+ qt_sql "select array_pushback(null, 6)"
+ qt_sql "select array_pushback([1.111, 2.222, 3.333], 9.999)"
+ qt_sql "select array_pushback(['aaa', 'bbb', 'ccc'], 'dddd')"
+ qt_sql "select array_pushback(array(cast (12.99 as decimal(10,3)),
cast (34.99 as decimal(10,3))), cast (999.28 as decimal(10,3)))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05' as datev2),
cast ('2023-03-04' as datev2)), cast ('2023-02-05' as datev2))"
+ qt_sql "select array_pushback(array(cast ('2023-03-05 12:23:24.999' as
datetimev2(3)),cast ('2023-03-05 15:23:23.997' as datetimev2(3))), cast
('2023-03-08 16:23:54.999' as datetimev2(3)))"
+ qt_sql "select array_pushback(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))), cast (333.333 as
decimalv3(6,3)))"
+ qt_sql "select array_pushback([null,null], null)"
+ qt_sql "select array_pushback([null,null,null,null], 80)"
+
+ // array_cum_sum
+ qt_sql "select array_cum_sum([0, 2, 127])"
+ qt_sql "select array_cum_sum([254, 4, 0])"
+ qt_sql "select array_cum_sum([1.0, 2.1 ,3.2, 4.3, 5.4])"
+ qt_sql "select array_cum_sum([-1, 2 ,-3, 4, -5])"
+ qt_sql "select array_cum_sum([-5.23, 4.12, -3.02, 2.00 ,1.01])"
+ qt_sql "select array_cum_sum([1, 2, 3, null])"
+ qt_sql "select array_cum_sum([null, 1, null, 3, 8, null])"
+ qt_sql "select array_cum_sum([null, null])"
+ qt_sql "select array_cum_sum([8])"
+ qt_sql "select array_cum_sum([1.1])"
+ qt_sql "select array_cum_sum([null])"
+ qt_sql "select array_cum_sum([])"
+ qt_sql "select array_cum_sum(array(cast (12.99 as decimal(10,3)), cast
(34.99 as decimal(10,3)), cast (999.28 as decimal(10,3))))"
+ qt_sql "select array_cum_sum(array(cast (111.111 as
decimalv3(6,3)),cast (222.222 as decimalv3(6,3))))"
+ qt_sql "select array_cum_sum(array(cast (11.9999 as
decimalv3(6,4)),cast (22.0001 as decimalv3(6,4))))"
+
+ // abnormal test
+ test {
+ sql "select array_intersect([1, 2, 3, 1, 2, 3], '1[3, 2, 5]')"
+ exception "No matching function with signature"
+ }
+
+ // array_min/max with nested array for args
+ test {
+ sql "select array_min(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(1,2,3),array(4,5,6));"
+ exception ""
+ }
+
+ test {
+ sql "select array_min(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+ test {
+ sql "select array_max(array(split_by_string('a,b,c',',')));"
+ exception ""
+ }
+
+ // array_map with string is can be succeed
+ qt_sql_array_map """ select array_map(x->x!='',
split_by_string('amory,is,better,committing', ',')) """
+
+ // array_apply with string should be failed
+ test {
+ sql """select
array_apply(split_by_string("amory,is,better,committing", ","), '!=', '');"""
+ exception("No matching function with")
+ }
- // array_map with string is can be succeed
- qt_sql_array_map """ select array_map(x->x!='',
split_by_string('amory,is,better,committing', ',')) """
+ qt_sql """ SELECT ARRAY_AVG(CAST([] AS ARRAY < DECIMALV3(1,0) > )); """
// array_apply with string should be failed
test {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
