You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/11/25 06:19:55 UTC
[doris] branch master updated: [enhancement](array-function) `array_position`,`array_contains`,`countequal` which in `FunctionArrayIndex` handle target NULL (#14564)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7ba4cd764a [enhancement](array-function) `array_position`,`array_contains`,`countequal` which in `FunctionArrayIndex` handle target NULL (#14564)
7ba4cd764a is described below
commit 7ba4cd764acac3a03a426fccae2e27d3fa226ccb
Author: lihangyu <15...@163.com>
AuthorDate: Fri Nov 25 14:19:50 2022 +0800
[enhancement](array-function) `array_position`,`array_contains`,`countequal` which in `FunctionArrayIndex` handle target NULL (#14564)
in the previous, the result is:
```
mysql> select array_position([1, null], null);
+--------------------------------------+
| array_position(ARRAY(1, NULL), NULL) |
+--------------------------------------+
| NULL |
+--------------------------------------+
1 row in set (0.02 sec)
```
but after this commit, the result become:
```
mysql> select array_position([1, null], null);
+--------------------------------------+
| array_position(ARRAY(1, NULL), NULL) |
+--------------------------------------+
| 2 |
+--------------------------------------+
1 row in set (0.02 sec)
```
---
be/src/vec/functions/array/function_array_index.h | 189 +++++++++++++++------
.../array-functions/array_contains.md | 8 +
.../array-functions/array_position.md | 10 +-
.../array-functions/array_contains.md | 8 +
.../array-functions/array_position.md | 10 +-
gensrc/script/doris_builtins_functions.py | 92 +++++-----
.../array_functions/test_array_functions.out | 127 +++++++++++++-
.../test_array_functions_by_literal.out | 20 ++-
.../array_functions/test_array_functions.groovy | 11 ++
.../test_array_functions_by_literal.groovy | 4 +
10 files changed, 368 insertions(+), 111 deletions(-)
diff --git a/be/src/vec/functions/array/function_array_index.h b/be/src/vec/functions/array/function_array_index.h
index be39c4dcb5..bce8eb2caf 100644
--- a/be/src/vec/functions/array/function_array_index.h
+++ b/be/src/vec/functions/array/function_array_index.h
@@ -62,9 +62,11 @@ public:
size_t get_number_of_arguments() const override { return 2; }
+ bool use_default_implementation_for_nulls() const override { return false; }
+
DataTypePtr get_return_type_impl(const DataTypes& arguments) const override {
- DCHECK(is_array(arguments[0]));
- return std::make_shared<DataTypeNumber<ResultType>>();
+ // DCHECK(is_array(arguments[0]));
+ return make_nullable(std::make_shared<DataTypeNumber<ResultType>>());
}
Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments,
@@ -74,7 +76,8 @@ public:
private:
ColumnPtr _execute_string(const ColumnArray::Offsets64& offsets, const UInt8* nested_null_map,
- const IColumn& nested_column, const IColumn& right_column) {
+ const IColumn& nested_column, const IColumn& right_column,
+ const UInt8* right_nested_null_map, const UInt8* outer_null_map) {
// check array nested column type and get data
const auto& str_offs = reinterpret_cast<const ColumnString&>(nested_column).get_offsets();
const auto& str_chars = reinterpret_cast<const ColumnString&>(nested_column).get_chars();
@@ -86,9 +89,16 @@ private:
// prepare return data
auto dst = ColumnVector<ResultType>::create(offsets.size());
auto& dst_data = dst->get_data();
+ auto dst_null_column = ColumnUInt8::create(offsets.size());
+ auto& dst_null_data = dst_null_column->get_data();
// process
for (size_t row = 0; row < offsets.size(); ++row) {
+ if (outer_null_map && outer_null_map[row]) {
+ dst_null_data[row] = true;
+ continue;
+ }
+ dst_null_data[row] = false;
ResultType res = 0;
size_t off = offsets[row - 1];
size_t len = offsets[row] - off;
@@ -96,10 +106,20 @@ private:
size_t right_off = right_offs[row - 1];
size_t right_len = right_offs[row] - right_off;
for (size_t pos = 0; pos < len; ++pos) {
+ // match null value
+ if (right_nested_null_map && right_nested_null_map[row] && nested_null_map &&
+ nested_null_map[pos + off]) {
+ ConcreteAction::apply(res, pos);
+ break;
+ }
+ // some is null while another is not
+ if (right_nested_null_map && nested_null_map &&
+ right_nested_null_map[row] != nested_null_map[pos + off]) {
+ continue;
+ }
if (nested_null_map && nested_null_map[pos + off]) {
continue;
}
-
size_t str_pos = str_offs[pos + off - 1];
size_t str_len = str_offs[pos + off] - str_pos;
const char* left_raw_v = reinterpret_cast<const char*>(&str_chars[str_pos]);
@@ -112,12 +132,13 @@ private:
}
dst_data[row] = res;
}
- return dst;
+ return ColumnNullable::create(std::move(dst), std::move(dst_null_column));
}
template <typename NestedColumnType, typename RightColumnType>
ColumnPtr _execute_number(const ColumnArray::Offsets64& offsets, const UInt8* nested_null_map,
- const IColumn& nested_column, const IColumn& right_column) {
+ const IColumn& nested_column, const IColumn& right_column,
+ const UInt8* right_nested_null_map, const UInt8* outer_null_map) {
// check array nested column type and get data
const auto& nested_data =
reinterpret_cast<const NestedColumnType&>(nested_column).get_data();
@@ -128,17 +149,34 @@ private:
// prepare return data
auto dst = ColumnVector<ResultType>::create(offsets.size());
auto& dst_data = dst->get_data();
+ auto dst_null_column = ColumnUInt8::create(offsets.size());
+ auto& dst_null_data = dst_null_column->get_data();
// process
for (size_t row = 0; row < offsets.size(); ++row) {
+ if (outer_null_map && outer_null_map[row]) {
+ dst_null_data[row] = true;
+ continue;
+ }
+ dst_null_data[row] = false;
ResultType res = 0;
size_t off = offsets[row - 1];
size_t len = offsets[row] - off;
for (size_t pos = 0; pos < len; ++pos) {
+ // match null value
+ if (right_nested_null_map && right_nested_null_map[row] && nested_null_map &&
+ nested_null_map[pos + off]) {
+ ConcreteAction::apply(res, pos);
+ break;
+ }
+ // some is null while another is not
+ if (right_nested_null_map && nested_null_map &&
+ right_nested_null_map[row] != nested_null_map[pos + off]) {
+ continue;
+ }
if (nested_null_map && nested_null_map[pos + off]) {
continue;
}
-
if (nested_data[pos + off] == right_data[row]) {
ConcreteAction::apply(res, pos);
break;
@@ -146,46 +184,59 @@ private:
}
dst_data[row] = res;
}
- return dst;
+ return ColumnNullable::create(std::move(dst), std::move(dst_null_column));
}
template <typename NestedColumnType>
ColumnPtr _execute_number_expanded(const ColumnArray::Offsets64& offsets,
const UInt8* nested_null_map, const IColumn& nested_column,
- const IColumn& right_column) {
+ const IColumn& right_column,
+ const UInt8* right_nested_null_map,
+ const UInt8* outer_null_map) {
if (check_column<ColumnUInt8>(right_column)) {
- return _execute_number<NestedColumnType, ColumnUInt8>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnUInt8>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnInt8>(right_column)) {
- return _execute_number<NestedColumnType, ColumnInt8>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnInt8>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnInt16>(right_column)) {
- return _execute_number<NestedColumnType, ColumnInt16>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnInt16>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnInt32>(right_column)) {
- return _execute_number<NestedColumnType, ColumnInt32>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnInt32>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnInt64>(right_column)) {
- return _execute_number<NestedColumnType, ColumnInt64>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnInt64>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnInt128>(right_column)) {
- return _execute_number<NestedColumnType, ColumnInt128>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnInt128>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnFloat32>(right_column)) {
- return _execute_number<NestedColumnType, ColumnFloat32>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnFloat32>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnFloat64>(right_column)) {
- return _execute_number<NestedColumnType, ColumnFloat64>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnFloat64>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (right_column.is_date_type()) {
- return _execute_number<NestedColumnType, ColumnDate>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnDate>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (right_column.is_datetime_type()) {
- return _execute_number<NestedColumnType, ColumnDateTime>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnDateTime>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
} else if (check_column<ColumnDecimal128>(right_column)) {
- return _execute_number<NestedColumnType, ColumnDecimal128>(offsets, nested_null_map,
- nested_column, right_column);
+ return _execute_number<NestedColumnType, ColumnDecimal128>(
+ offsets, nested_null_map, nested_column, right_column, right_nested_null_map,
+ outer_null_map);
}
return nullptr;
}
@@ -195,71 +246,97 @@ private:
// extract array offsets and nested data
auto left_column =
block.get_by_position(arguments[0]).column->convert_to_full_column_if_const();
- const auto& array_column = reinterpret_cast<const ColumnArray&>(*left_column);
- const auto& offsets = array_column.get_offsets();
+ const ColumnArray* array_column = nullptr;
+ const UInt8* array_null_map = nullptr;
+ if (left_column->is_nullable()) {
+ auto nullable_array = reinterpret_cast<const ColumnNullable*>(left_column.get());
+ array_column =
+ reinterpret_cast<const ColumnArray*>(&nullable_array->get_nested_column());
+ array_null_map = nullable_array->get_null_map_column().get_data().data();
+ } else {
+ array_column = reinterpret_cast<const ColumnArray*>(left_column.get());
+ }
+ const auto& offsets = array_column->get_offsets();
const UInt8* nested_null_map = nullptr;
ColumnPtr nested_column = nullptr;
- if (array_column.get_data().is_nullable()) {
+ if (array_column->get_data().is_nullable()) {
const auto& nested_null_column =
- reinterpret_cast<const ColumnNullable&>(array_column.get_data());
+ reinterpret_cast<const ColumnNullable&>(array_column->get_data());
nested_null_map = nested_null_column.get_null_map_column().get_data().data();
nested_column = nested_null_column.get_nested_column_ptr();
} else {
- nested_column = array_column.get_data_ptr();
+ nested_column = array_column->get_data_ptr();
}
// get right column
- auto right_column =
+ ColumnPtr right_full_column =
block.get_by_position(arguments[1]).column->convert_to_full_column_if_const();
-
+ ColumnPtr right_column = right_full_column;
+ const UInt8* right_nested_null_map = nullptr;
+ if (right_column->is_nullable()) {
+ const auto& nested_null_column = assert_cast<const ColumnNullable&>(*right_full_column);
+ right_column = nested_null_column.get_nested_column_ptr();
+ right_nested_null_map = nested_null_column.get_null_map_column().get_data().data();
+ }
// execute
- auto left_element_type = remove_nullable(
- assert_cast<const DataTypeArray&>(*block.get_by_position(arguments[0]).type)
- .get_nested_type());
+ auto array_type = remove_nullable(block.get_by_position(arguments[0]).type);
+ auto left_element_type =
+ remove_nullable(assert_cast<const DataTypeArray&>(*array_type).get_nested_type());
auto right_type = remove_nullable(block.get_by_position(arguments[1]).type);
ColumnPtr return_column = nullptr;
if (is_string(right_type) && is_string(left_element_type)) {
- return_column =
- _execute_string(offsets, nested_null_map, *nested_column, *right_column);
+ return_column = _execute_string(offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (is_number(right_type) && is_number(left_element_type)) {
if (check_column<ColumnUInt8>(*nested_column)) {
return_column = _execute_number_expanded<ColumnUInt8>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnInt8>(*nested_column)) {
- return_column = _execute_number_expanded<ColumnInt8>(offsets, nested_null_map,
- *nested_column, *right_column);
+ return_column = _execute_number_expanded<ColumnInt8>(
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnInt16>(*nested_column)) {
return_column = _execute_number_expanded<ColumnInt16>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnInt32>(*nested_column)) {
return_column = _execute_number_expanded<ColumnInt32>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnInt64>(*nested_column)) {
return_column = _execute_number_expanded<ColumnInt64>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnInt128>(*nested_column)) {
return_column = _execute_number_expanded<ColumnInt128>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnFloat32>(*nested_column)) {
return_column = _execute_number_expanded<ColumnFloat32>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnFloat64>(*nested_column)) {
return_column = _execute_number_expanded<ColumnFloat64>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (check_column<ColumnDecimal128>(*nested_column)) {
return_column = _execute_number_expanded<ColumnDecimal128>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
}
} else if ((is_date_or_datetime(right_type) || is_date_v2_or_datetime_v2(right_type)) &&
(is_date_or_datetime(left_element_type) ||
is_date_v2_or_datetime_v2(left_element_type))) {
if (nested_column->is_date_type()) {
- return_column = _execute_number_expanded<ColumnDate>(offsets, nested_null_map,
- *nested_column, *right_column);
+ return_column = _execute_number_expanded<ColumnDate>(
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
} else if (nested_column->is_datetime_type()) {
return_column = _execute_number_expanded<ColumnDateTime>(
- offsets, nested_null_map, *nested_column, *right_column);
+ offsets, nested_null_map, *nested_column, *right_column,
+ right_nested_null_map, array_null_map);
}
}
diff --git a/docs/en/docs/sql-manual/sql-functions/array-functions/array_contains.md b/docs/en/docs/sql-manual/sql-functions/array-functions/array_contains.md
index 501bbbb26f..1a17c560c9 100644
--- a/docs/en/docs/sql-manual/sql-functions/array-functions/array_contains.md
+++ b/docs/en/docs/sql-manual/sql-functions/array-functions/array_contains.md
@@ -58,6 +58,14 @@ mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
| 3 | [] | 0 |
| 4 | NULL | NULL |
+------+-----------------+------------------------------+
+
+mysql> select array_contains([null, 1], null);
++--------------------------------------+
+| array_contains(ARRAY(NULL, 1), NULL) |
++--------------------------------------+
+| 1 |
++--------------------------------------+
+1 row in set (0.00 sec)
```
### keywords
diff --git a/docs/en/docs/sql-manual/sql-functions/array-functions/array_position.md b/docs/en/docs/sql-manual/sql-functions/array-functions/array_position.md
index c7337032fc..8784ce2d2e 100644
--- a/docs/en/docs/sql-manual/sql-functions/array-functions/array_position.md
+++ b/docs/en/docs/sql-manual/sql-functions/array-functions/array_position.md
@@ -37,7 +37,7 @@ Returns a position/index of first occurrence of the `value` in the given array.
```
position - value position in array (starts with 1);
0 - if value does not present in the array;
-NULL - when array is NULL or value is NULL.
+NULL - when array is NULL.
```
### notice
@@ -58,6 +58,14 @@ mysql> SELECT id,c_array,array_position(c_array, 5) FROM `array_test`;
| 3 | [] | 0 |
| 4 | NULL | NULL |
+------+-----------------+------------------------------+
+
+mysql> select array_position([1, null], null);
++--------------------------------------+
+| array_position(ARRAY(1, NULL), NULL) |
++--------------------------------------+
+| 2 |
++--------------------------------------+
+1 row in set (0.01 sec)
```
### keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_contains.md b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_contains.md
index c5e82714af..4f5ac0ef0e 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_contains.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_contains.md
@@ -58,6 +58,14 @@ mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
| 3 | [] | 0 |
| 4 | NULL | NULL |
+------+-----------------+------------------------------+
+
+mysql> select array_contains([null, 1], null);
++--------------------------------------+
+| array_contains(ARRAY(NULL, 1), NULL) |
++--------------------------------------+
+| 1 |
++--------------------------------------+
+1 row in set (0.00 sec)
```
### keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_position.md b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_position.md
index 037b0b7d51..d7536a55be 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_position.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/array_position.md
@@ -37,7 +37,7 @@ under the License.
```
position - value在array中的位置(从1开始计算);
0 - 如果value在array中不存在;
-NULL - 如果数组为NULL,或者value为NULL。
+NULL - 如果数组为NULL。
```
### notice
@@ -58,6 +58,14 @@ mysql> SELECT id,c_array,array_position(c_array, 5) FROM `array_test`;
| 3 | [] | 0 |
| 4 | NULL | NULL |
+------+-----------------+------------------------------+
+
+mysql> select array_position([1, null], null);
++--------------------------------------+
+| array_position(ARRAY(1, NULL), NULL) |
++--------------------------------------+
+| 2 |
++--------------------------------------+
+1 row in set (0.01 sec)
```
### keywords
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index 4109125494..25be32c7ee 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -158,21 +158,21 @@ visible_functions = [
[['arrays_overlap'], 'BOOLEAN', ['ARRAY_VARCHAR', 'ARRAY_VARCHAR'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
[['arrays_overlap'], 'BOOLEAN', ['ARRAY_STRING', 'ARRAY_STRING'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
- [['array_contains'], 'BOOLEAN', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_INT', 'INT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', ''],
- [['array_contains'], 'BOOLEAN', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', ''],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_INT', 'INT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_contains'], 'BOOLEAN', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
[['array_enumerate'], 'ARRAY_BIGINT', ['ARRAY_BOOLEAN'], '', '', '', 'vec', ''],
@@ -191,37 +191,37 @@ visible_functions = [
[['array_enumerate'], 'ARRAY_BIGINT', ['ARRAY_VARCHAR'], '', '', '', 'vec', ''],
[['array_enumerate'], 'ARRAY_BIGINT', ['ARRAY_STRING'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_INT', 'INT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', ''],
- [['countequal'], 'BIGINT', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', ''],
-
- [['array_position'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_INT', 'INT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', ''],
- [['array_position'], 'BIGINT', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_INT', 'INT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['countequal'], 'BIGINT', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+
+ [['array_position'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_INT', 'INT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DATETIME', 'DATETIME'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['array_position'], 'BIGINT', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec', 'ALWAYS_NULLABLE'],
[['cardinality', 'size'], 'BIGINT', ['ARRAY'], '', '', '', 'vec', ''],
[['array_distinct'], 'ARRAY_BOOLEAN', ['ARRAY_BOOLEAN'], '', '', '', 'vec', ''],
diff --git a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
index 6a7dd18f8d..e0bb00b0e0 100644
--- a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
+++ b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
@@ -142,6 +142,50 @@
8 1_2_3_3_4_4_null a-b-b-b
9 1_2_3 a-b-
+-- !select --
+1 true
+2 false
+3 false
+4 true
+5 false
+6 true
+7 false
+8 true
+9 true
+
+-- !select --
+1 true
+2 \N
+3 false
+4 false
+5 true
+6 true
+7 false
+8 true
+9 true
+
+-- !select --
+1 false
+2 \N
+3 false
+4 false
+5 false
+6 false
+7 true
+8 false
+9 false
+
+-- !select --
+1 false
+2 false
+3 \N
+4 false
+5 \N
+6 \N
+7 \N
+8 false
+9 false
+
-- !select --
1 true
2 false
@@ -165,15 +209,15 @@
9 false
-- !select --
-1 \N
+1 false
2 \N
3 \N
4 \N
5 \N
6 \N
7 \N
-8 \N
-9 \N
+8 false
+9 false
-- !select --
1 [1, 2, 3]
@@ -340,6 +384,83 @@
8 [NULL, 8]
9 [NULL, 9]
+-- !select --
+1 0
+2 0
+3 0
+4 5
+5 0
+6 5
+7 0
+8 0
+9 0
+
+-- !select --
+1 1
+2 \N
+3 0
+4 0
+5 1
+6 1
+7 0
+8 1
+9 1
+
+-- !select --
+1 0
+2 \N
+3 0
+4 0
+5 0
+6 0
+7 2
+8 0
+9 0
+
+-- !select --
+1 0
+2 0
+3 \N
+4 0
+5 \N
+6 \N
+7 \N
+8 0
+9 0
+
+-- !select --
+1 1
+2 0
+3 0
+4 \N
+5 \N
+6 \N
+7 \N
+8 1
+9 1
+
+-- !select --
+1 0
+2 0
+3 0
+4 \N
+5 \N
+6 \N
+7 \N
+8 0
+9 0
+
+-- !select --
+1 0
+2 \N
+3 \N
+4 \N
+5 \N
+6 \N
+7 \N
+8 0
+9 0
+
-- !select --
\N \N
-1 \N
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 672e4cf2d2..01ec14bfdf 100644
--- 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
@@ -9,13 +9,13 @@ false
true
-- !sql --
-\N
+true
-- !sql --
false
-- !sql --
-\N
+false
-- !sql --
\N
@@ -39,13 +39,13 @@ false
2
-- !sql --
-\N
+1
-- !sql --
0
-- !sql --
-\N
+0
-- !sql --
\N
@@ -53,6 +53,18 @@ false
-- !sql --
\N
+-- !sql --
+0
+
+-- !sql --
+0
+
+-- !sql --
+0
+
+-- !sql --
+0
+
-- !sql --
3
diff --git a/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions.groovy b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions.groovy
index 07ea61f759..1514692651 100644
--- a/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions.groovy
+++ b/regression-test/suites/query_p0/sql_functions/array_functions/test_array_functions.groovy
@@ -61,6 +61,10 @@ suite("test_array_functions") {
qt_select "SELECT k1, array_slice(k2, 1, 2) FROM ${tableName} ORDER BY k1"
qt_select "SELECT k1, reverse(k2), reverse(k3), reverse(k4) FROM ${tableName} ORDER BY k1"
qt_select "SELECT k1, array_join(k2, '_', 'null'), array_join(k3, '-', 'null') FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_contains(k2, 1) FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_contains(k3, 'a') FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_contains(k3, null) FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_contains(k4, null) FROM ${tableName} ORDER BY k1"
qt_select "SELECT k1, array_contains(k5, 'hi') FROM ${tableName} ORDER BY k1"
qt_select "SELECT k1, array_contains(k5, 'hi222') FROM ${tableName} ORDER BY k1"
qt_select "SELECT k1, array_contains(k6, null) from ${tableName} ORDER BY k1"
@@ -79,6 +83,13 @@ suite("test_array_functions") {
qt_select "SELECT k1, array(2, k1) from ${tableName} ORDER BY k1"
qt_select "SELECT k1, array(k1, null, '2020-01-01') from ${tableName} ORDER BY k1"
qt_select "SELECT k1, array(null, k1) from ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k2, 5) FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k3, 'a') FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k3, null) FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k4, null) FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k5, 'hi') FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k5, 'hi222') FROM ${tableName} ORDER BY k1"
+ qt_select "SELECT k1, array_position(k6, null) from ${tableName} ORDER BY k1"
def tableName2 = "tbl_test_array_range"
sql """DROP TABLE IF EXISTS ${tableName2}"""
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 bcf0edbeb1..29b4d3690c 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,6 +39,10 @@ suite("test_array_functions_by_literal") {
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], 0)"
+ 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)"
// element_at function
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org