This is an automated email from the ASF dual-hosted git repository.
eldenmoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new ce227e39465 [fix](array function) fix some array functions (#2685)
ce227e39465 is described below
commit ce227e39465e960d92e771b36af81bc19248f049
Author: Sun Chenyang <[email protected]>
AuthorDate: Mon Aug 25 09:59:44 2025 +0800
[fix](array function) fix some array functions (#2685)
https://github.com/apache/doris/pull/54439
---
.../scalar-functions/array-functions/array-sum.md | 70 ++++++---
.../array-functions/array-union.md | 94 +++++++++---
.../array-functions/array-with-constant.md | 81 +++++++---
.../scalar-functions/array-functions/array-zip.md | 78 +++++++---
.../array-functions/arrays-overlap.md | 168 +++++++++++++++-----
.../scalar-functions/array-functions/array-sum.md | 70 ++++++---
.../array-functions/array-union.md | 94 +++++++++---
.../array-functions/array-with-constant.md | 81 +++++++---
.../scalar-functions/array-functions/array-zip.md | 79 +++++++---
.../array-functions/arrays-overlap.md | 169 ++++++++++++++++-----
10 files changed, 758 insertions(+), 226 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
index 2041391c89a..ba2815dbc71 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
@@ -5,36 +5,68 @@
}
---
-## Description
+### Function
-Calculates the sum of all elements in an array
+The `ARRAY_SUM` function calculates the sum of all numeric elements in an
array.
## Syntax
-```sql
-ARRAY_SUM(<src>)
+```SQL
+ARRAY_SUM(ARRAY<T>)
```
## Parameters
-| Parameter | Description |
-|--|--|
-| `<src>` | Corresponding array |
+`ARRAY<T>`: An array containing **numeric type** elements.
## Return Value
-Returns the sum of all elements in the array. NULL values in the array will be
skipped. For an empty array or an array with all NULL values, the result
returns a NULL value.
+- Returns the sum of all non-`NULL` elements in the array.
-## Example
+ - If all elements are `NULL`, returns `NULL`.
-```sql
-SELECT ARRAY_SUM([1, 2, 3, 6]),ARRAY_SUM([1, 4, 3, 5, NULL]),ARRAY_SUM([NULL]);
-```
+## Usage Notes
-```text
-+-------------------------+-------------------------------+-------------------------------------------+
-| array_sum([1, 2, 3, 6]) | array_sum([1, 4, 3, 5, NULL]) |
array_sum(cast([NULL] as ARRAY<BOOLEAN>)) |
-+-------------------------+-------------------------------+-------------------------------------------+
-| 12 | 13 |
NULL |
-+-------------------------+-------------------------------+-------------------------------------------+
-```
+1. Summation of elements uses the `+` operator.
+
+2. Elements that are `NULL` are automatically ignored.
+
+3. If the array contains non-numeric type elements (such as strings), it will
result in a runtime error.
+
+## Examples
+
+1. Simple example
+
+ ```SQL
+ SELECT ARRAY_SUM([1, 2, 3, 4]);
+ +-------------------------+
+ | ARRAY_SUM([1, 2, 3, 4]) |
+ +-------------------------+
+ | 10 |
+ +-------------------------+
+ ```
+
+2. Handling `NULL` values in arrays
+
+ ```SQL
+ SELECT ARRAY_SUM([1, NULL, 3]);
+ +-------------------------+
+ | ARRAY_SUM([1, NULL, 3]) |
+ +-------------------------+
+ | 4 |
+ +-------------------------+
+
+ SELECT ARRAY_SUM(NULL);
+ +-----------------+
+ | ARRAY_SUM(NULL) |
+ +-----------------+
+ | NULL |
+ +-----------------+
+
+ SELECT ARRAY_SUM([NULL, NULL]);
+ +-------------------------+
+ | ARRAY_SUM([NULL, NULL]) |
+ +-------------------------+
+ | NULL |
+ +-------------------------+
+ ```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
index 5f6604682ad..6d37a54f749 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
@@ -5,36 +5,90 @@
}
---
-## Description
+## Function
-Merge multiple arrays without duplicate elements to generate a new array
+`ARRAY_UNION` returns the union of multiple arrays, i.e., merges all elements
from the arrays, removes duplicates, and returns a new array.
## Syntax
-```sql
-ARRAY_UNION(<array>, <array> [, ... ])
+```SQL
+ARRAY_UNION(arr1, arr2, ..., arrN)
```
## Parameters
-| Parameter | Description |
-|--|--|
-| `<array>` | The array to be merged |
-
+- `arr1, arr2, ..., arrN`: Any number of array inputs, all of type `ARRAY<T>`.
+ - The element type `T` of all arrays must be the same, or implicitly
convertible to a unified type.
+ - The element type `T` can be numeric, string, date/time, or IP type.
+
## Return Value
-Returns an array containing all elements in the union of all arrays, excluding
duplicates. If the input parameter is NULL, it returns NULL.
+- Returns a new array of type `ARRAY<T>` containing all unique elements from
the input arrays (duplicates removed).
+ - If any input parameter is `NULL`, returns `NULL` (see example).
-## Example
+## Usage Notes
-```sql
-SELECT ARRAY_UNION([1, 2, 3, 6],[1, 2, 5]),ARRAY_UNION([1, 4, 3, 5,
NULL],[1,6,10]);
-```
+1. Duplicate removal is based on equality comparison (`=` operator).
+2. Only one `NULL` will be kept in the result array (see example).
+3. If the input array itself contains multiple identical elements, only one
will be kept (see example).
+4. The order of elements in the result array is not guaranteed.
-```text
-+--------------------------------------+---------------------------------------------+
-| array_union([1, 2, 3, 6], [1, 2, 5]) | array_union([1, 4, 3, 5, NULL], [1,
6, 10]) |
-+--------------------------------------+---------------------------------------------+
-| [3, 2, 1, 6, 5] | [null, 10, 3, 1, 6, 4, 5]
|
-+--------------------------------------+---------------------------------------------+
-```
+## Examples
+
+1. Simple example
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'));
+ +---------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world')) |
+ +---------------------------------------------------------------+
+ | ["world", "hello"] |
+ +---------------------------------------------------------------+
+
+ SELECT ARRAY_UNION(ARRAY(1, 2, 3), ARRAY(3, 5, 6));
+ +---------------------------------------------+
+ | ARRAY_UNION(ARRAY(1, 2, 3), ARRAY(3, 5, 6)) |
+ +---------------------------------------------+
+ | [1, 5, 2, 6, 3] |
+ +---------------------------------------------+
+ ```
+
+2. If any input array is `NULL`, returns `NULL`
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'),
NULL);
+ +---------------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'), NULL) |
+ +---------------------------------------------------------------------+
+ | NULL |
+ +---------------------------------------------------------------------+
+ ```
+
+3. If input arrays contain `NULL`, the output array will contain only one
`NULL`
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL));
+ +------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL)) |
+ +------------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +------------------------------------------------------------+
+
+ SELECT ARRAY_UNION(ARRAY(NULL, 'world'), ARRAY('hello', NULL));
+ +---------------------------------------------------------+
+ | ARRAY_UNION(ARRAY(NULL, 'world'), ARRAY('hello', NULL)) |
+ +---------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +---------------------------------------------------------+
+ ```
+
+4. If an array contains duplicate elements, only one will be returned
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world', 'hello'), ARRAY('hello',
NULL));
+ +------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL)) |
+ +------------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +------------------------------------------------------------+
+ ```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
index 2e0b8ff9414..176a282438f 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
@@ -5,37 +5,78 @@
}
---
-## Description
+## Function
-Generates an array containing n repeated elements
+`ARRAY_WITH_CONSTANT` is used to generate an array of a specified length,
where all elements have the given value.
## Syntax
-```sql
-ARRAY_WITH_CONSTANT(<n>, <element>)
+```SQL
+ARRAY_WITH_CONSTANT(count, element)
```
## Parameters
-| Parameter | Description |
-|--|--|
-| `<n>` | Number of digits |
-| `<element>` | Specifying Elements |
+- `count`: Integer type, specifies the length of the returned array.
+
+- `element`: Any storage type supported in an `ARRAY`.
## Return Value
-Returns an array containing n repeated elements. array_repeat has the same
function as array_with_constant and is used to be compatible with the hive
syntax format.
+- Returns an array of type `ARRAY<T>`, where `T` is the type of `element`.
+ - The array contains `count` copies of the same `element`.
-## Example
+## Usage Notes
-```sql
-SELECT ARRAY_WITH_CONSTANT(2, "hello"),ARRAY_WITH_CONSTANT(3, 12345);
-```
+- If `count = 0` or `NULL`, returns an empty array.
+- If `element` is `NULL`, all elements in the array are `NULL`.
+- This function has the same functionality as `ARRAY_REPEAT`, but the
parameter order is reversed.
+- Can be combined with other array functions to construct more complex data.
-```text
-+---------------------------------+-------------------------------+
-| array_with_constant(2, 'hello') | array_with_constant(3, 12345) |
-+---------------------------------+-------------------------------+
-| ["hello", "hello"] | [12345, 12345, 12345] |
-+---------------------------------+-------------------------------+
-```
+## Examples
+
+1. Simple example
+
+ ```SQL
+ SELECT ARRAY_WITH_CONSTANT(3, 'hello');
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(3, 'hello') |
+ +---------------------------------+
+ | ["hello", "hello", "hello"] |
+ +---------------------------------+
+ ```
+
+2. Special cases
+
+ ```SQL
+ SELECT ARRAY_WITH_CONSTANT(0, 'hello');
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(0, 'hello') |
+ +---------------------------------+
+ | [] |
+ +---------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(NULL, 'hello');
+ +------------------------------------+
+ | ARRAY_WITH_CONSTANT(NULL, 'hello') |
+ +------------------------------------+
+ | [] |
+ +------------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(2, NULL);
+ +------------------------------+
+ | ARRAY_WITH_CONSTANT(2, NULL) |
+ +------------------------------+
+ | [null, null] |
+ +------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(NULL, NULL);
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(NULL, NULL) |
+ +---------------------------------+
+ | [] |
+ +---------------------------------+
+
+ -- Returns error: INVALID_ARGUMENT
+ SELECT ARRAY_WITH_CONSTANT(-1, 'hello');
+ ```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
index e46ac1d387c..55879662b88 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
@@ -5,36 +5,76 @@
}
---
-## Description
+## Function
-Merges all arrays into a single array. The resulting array contains
corresponding elements from the source arrays, grouped in the order of the
argument list.
+The `ARRAY_ZIP` function combines multiple `ARRAY`s (e.g., `arr1, arr2, ... ,
arrN`) element-wise into a single `ARRAY<STRUCT>`, where each `STRUCT` contains
the corresponding elements from each input array.
## Syntax
-```sql
-ARRAY_ZIP(<array>[, <array> ])
+```SQL
+ARRAY_ZIP(arr1, arr2, ... , arrN)
```
## Parameters
-| Parameter | Description |
-|--|--|
-| `<array>` | Input array |
+- `arr1, arr2, ..., arrN`: The N input arrays, with types `ARRAY<T1>,
ARRAY<T2>, ..., ARRAY<Tn>`.
## Return Value
-Returns an array with the elements from the source array grouped into a
structure. The data types in the structure are the same as the input array and
are in the order in which the array was passed.
+- The return type is `ARRAY<STRUCT<col1 T1, col2 T2, ..., colN Tn>>`, where
each `STRUCT` represents the combination of elements at the same index from the
input arrays.
-## Example
+## Usage Notes
-```sql
-SELECT ARRAY_ZIP(['a', 'b', 'c'], [1, 2, 3]);
-```
+1. **If the arrays have different lengths, the function fails with
`RUNTIME_ERROR`**.
+2. Supports input arrays of different types; the resulting struct fields
correspond one-to-one with the input array types.
+3. Useful for combining multiple parallel arrays into a structured format for
easier processing or analysis.
-```text
-+--------------------------------------------------------+
-| array_zip(['a', 'b', 'c'], [1, 2, 3]) |
-+--------------------------------------------------------+
-| [{"1":"a", "2":1}, {"1":"b", "2":2}, {"1":"c", "2":3}] |
-+--------------------------------------------------------+
-```
+## Examples
+
+1. Combine multiple arrays
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"),
ARRAY(true, false, true));
+
+-------------------------------------------------------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), ARRAY(true,
false, true)) |
+
+-------------------------------------------------------------------------------------------------------------------+
+ | [{"col1":23, "col2":"John", "col3":1}, {"col1":24, "col2":"Jane",
"col3":0}, {"col1":25, "col2":"Jim", "col3":1}] |
+
+-------------------------------------------------------------------------------------------------------------------+
+ ```
+ - The first `STRUCT` in the return value contains the first element from
each input `ARRAY`.
+ - The second `STRUCT` contains the second element from each input `ARRAY`.
+ - The third `STRUCT` contains the third element from each input `ARRAY`.
+
+2. Access the return value
+
+ ```SQL
+ -- Access the returned ARRAY
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"))[1];
+ +---------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"))[1] |
+ +---------------------------------------------------------------+
+ | {"col1":23, "col2":"John"} |
+ +---------------------------------------------------------------+
+ ```
+
+3. If one of the arrays is `NULL`, returns `NULL`
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), NULL) ;
+ +------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), NULL) |
+ +------------------------------------------------------------------+
+ | NULL |
+ +------------------------------------------------------------------+
+ ```
+
+4. If an element in an `ARRAY` is `NULL`, the corresponding field in the
`STRUCT` is `NULL`
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, NULL, 25), ARRAY("John", "Jane", NULL),
ARRAY(NULL, false, true));
+
+-----------------------------------------------------------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, NULL, 25), ARRAY("John", "Jane", NULL), ARRAY(NULL,
false, true)) |
+
+-----------------------------------------------------------------------------------------------------------------------+
+ | [{"col1":23, "col2":"John", "col3":null}, {"col1":null, "col2":"Jane",
"col3":0}, {"col1":25, "col2":null, "col3":1}] |
+
+-----------------------------------------------------------------------------------------------------------------------+
+ ```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
index 2cff2270f10..d2eb1189d3b 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
@@ -5,50 +5,150 @@
}
---
-## Description
+## Function
-Determine whether the left and right arrays contain common elements
+`ARRAYS_OVERLAP` is used to ◊ whether two arrays have at least one common
element. Returns `true` if they do, otherwise returns `false`.
## Syntax
-```sql
-ARRAYS_OVERLAP(<left>, <right>)
+```SQL
+ARRAYS_OVERLAP(arr1, arr2)
```
## Parameters
-| Parameter | Description |
-|--|--|
-| `<left>` | The array to be judged |
-| `<right>` | The array to be judged |
+- `arr1`: The first array, type `ARRAY<T>`.
+
+- `arr2`: The second array, type `ARRAY<T>`.
+
+ - The element type `T` of both arrays must be the same or implicitly
convertible to each other.
+ - The element type `T` can be numeric, string, date/time, or IP type.
## Return Value
-Returns if left and right have any non-null elements in common. Returns null
if there are no non-null elements in common but either array contains null.
+- Returns `BOOLEAN` type:
+
+ - If the two arrays have an intersection, returns `true`;
+ - If they have no intersection, returns `false`.
+
+## Usage Notes
+
+1. **Comparison is done using element equality (`=` operator)**.
+2. **`NULL` and `NULL` are considered equal in this function** (see example).
+3. You can specify **an inverted index in the table creation statement to
accelerate execution** (see example).
+ - When the function is used as a predicate condition, the inverted index
will speed up execution.
+ - When the function is used in the query result, the inverted index will
not speed up execution.
+4. Commonly used in data cleaning, tag matching, and user behavior
intersection scenarios.
+
+## Examples
+
+1. Simple Example
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi',
'hey'));
+ +----------------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey')) |
+ +----------------------------------------------------------------------+
+ | 1 |
+ +----------------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey'));
+ +----------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey')) |
+ +----------------------------------------------------------------+
+ | 0 |
+ +----------------------------------------------------------------+
+ ```
+
+2. Invalid parameter type: when unsupported types are passed in, returns
`INVALID_ARGUMENT`
+
+ ```SQL
+ -- [INVALID_ARGUMENT] execute failed, unsupported types for function
arrays_overlap
+ SELECT ARRAYS_OVERLAP(ARRAY(ARRAY('hello', 'aloha'), ARRAY('hi', 'hey')),
ARRAY(ARRAY('hello', 'hi', 'hey'), ARRAY('aloha', 'hi')));
+ ```
+
+3. If the input `ARRAY` is `NULL`, the return value is `NULL`
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL);
+ +-----------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL) |
+ +-----------------------------------------------+
+ | NULL |
+ +-----------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(NULL, NULL);
+ +----------------------------+
+ | ARRAYS_OVERLAP(NULL, NULL) |
+ +----------------------------+
+ | NULL |
+ +----------------------------+
+ ```
+
+4. When the input `ARRAY` contains `NULL`, `NULL` and `NULL` are considered
equal
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL));
+ +---------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL)) |
+ +---------------------------------------------------------------+
+ | 1 |
+ +---------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL));
+ +----------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL)) |
+ +----------------------------------------------------------------+
+ | 0 |
+ +----------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL));
+ +---------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL)) |
+ +---------------------------------------------------+
+ | 1 |
+ +---------------------------------------------------+
+ ```
+
+5. Using inverted index to accelerate query
+
+ ```SQL
+ -- Create table with inverted index
+ CREATE TABLE IF NOT EXISTS arrays_overlap_table (
+ id INT,
+ array_column ARRAY<STRING>,
+ INDEX idx_array_column (array_column) USING INVERTED -- only
non-tokenized inverted indexes are allowed
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ -- Insert two rows
+ INSERT INTO arrays_overlap_table (id, array_column) VALUES (1,
ARRAY('HELLO', 'ALOHA')), (2, ARRAY('NO', 'WORLD'));
+ ```
+
+ - When the function is used as a predicate condition, the inverted index will
accelerate execution
+
+ ```SQL
+ SELECT * from arrays_overlap_table WHERE ARRAYS_OVERLAP(array_column,
ARRAY('HELLO', 'PICKLE'));
+ +------+--------------------+
+ | id | array_column |
+ +------+--------------------+
+ | 1 | ["HELLO", "ALOHA"] |
+ +------+--------------------+
+
+- When the function is used in the query result, the inverted index will not
accelerate execution
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) FROM
arrays_overlap_table;
+ +--------------------------------------------------------+
+ | ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) |
+ +--------------------------------------------------------+
+ | 1 |
+ | 0 |
+ +--------------------------------------------------------+
+ ```
-## Example
-```
-select arrays_overlap([1, 2, 3], [1, null]);
-+--------------------------------------+
-| arrays_overlap([1, 2, 3], [1, null]) |
-+--------------------------------------+
-| 1 |
-+--------------------------------------+
-
-
-select arrays_overlap([2, 3], [1, null]);
-+-----------------------------------+
-| arrays_overlap([2, 3], [1, null]) |
-+-----------------------------------+
-| NULL |
-+-----------------------------------+
-
-select arrays_overlap([2, 3], [1]);
-+-----------------------------+
-+-----------------------------+
-| arrays_overlap([2, 3], [1]) |
-+-----------------------------+
-| 0 |
-+-----------------------------+
-```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
index 7b0ac9b3bc6..9e7da3ef74a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-sum.md
@@ -5,36 +5,68 @@
}
---
-## 描述
+### 功能
-计算数组中所有元素之和
+`ARRAY_SUM` 函数用于对数组中的所有数值元素求和。
## 语法
-```sql
-ARRAY_SUM(<src>)
+```SQL
+ARRAY_SUM(ARRAY<T>)
```
## 参数
-| 参数 | 说明 |
-|--|--|
-| `<src>` | 对应数组 |
+`ARRAY<T>`：一个包含**数值类型**元素的数组。
## 返回值
-返回数组中所有元素之和，数组中的 NULL 值会被跳过。空数组以及元素全为 NULL 值的数组，结果返回 NULL 值。
+- 返回数组中所有非 `NULL` 元素的总和。
-## 举例
+ - 如果全是 `NULL`，返回 `NULL`。
-```sql
-SELECT ARRAY_SUM([1, 2, 3, 6]),ARRAY_SUM([1, 4, 3, 5, NULL]),ARRAY_SUM([NULL]);
-```
+## 使用说明
-```text
-+-------------------------+-------------------------------+-------------------------------------------+
-| array_sum([1, 2, 3, 6]) | array_sum([1, 4, 3, 5, NULL]) |
array_sum(cast([NULL] as ARRAY<BOOLEAN>)) |
-+-------------------------+-------------------------------+-------------------------------------------+
-| 12 | 13 |
NULL |
-+-------------------------+-------------------------------+-------------------------------------------+
-```
+1. 元素的求和使用 + 运算符。
+
+2. 对于包含 `NULL` 的元素，会自动忽略这些元素。
+
+3. 如果数组包含非数值类型元素（如字符串），将导致运行错误。
+
+## 示例
+
+1. 简单示例
+
+ ```SQL
+ SELECT ARRAY_SUM([1, 2, 3, 4]);
+ +-------------------------+
+ | ARRAY_SUM([1, 2, 3, 4]) |
+ +-------------------------+
+ | 10 |
+ +-------------------------+
+ ```
+
+2. 数组中的 `NULL` 处理
+
+ ```SQL
+ SELECT ARRAY_SUM([1, NULL, 3]);
+ +-------------------------+
+ | ARRAY_SUM([1, NULL, 3]) |
+ +-------------------------+
+ | 4 |
+ +-------------------------+
+
+ SELECT ARRAY_SUM(NULL);
+ +-----------------+
+ | ARRAY_SUM(NULL) |
+ +-----------------+
+ | NULL |
+ +-----------------+
+
+ SELECT ARRAY_SUM([NULL, NULL]);
+ +-------------------------+
+ | ARRAY_SUM([NULL, NULL]) |
+ +-------------------------+
+ | NULL |
+ +-------------------------+
+ ```
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
index bb2eccd650c..c1966b5932a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-union.md
@@ -5,36 +5,90 @@
}
---
-## 描述
+## 功能
-将多个数组进行合并，不包含重复元素，生成一个新数组
+`ARRAY_UNION` 用于返回多个数组的并集，即合并所有数组中出现的元素，去重后组成一个新的数组。
## 语法
-```sql
-ARRAY_UNION(<array>, <array> [, ... ])
+```SQL
+ARRAY_UNION(arr1, arr2， ..., arrN)
```
## 参数
-| 参数 | 说明 |
-|--|--|
-| `<array>` | 待合并的数组 |
-
+- `arr1, arr2, ..., arrN`：任意数量的数组输入，类型均为 `ARRAY<T>`。
+ - 所有数组的元素类型 `T` 必须一致，或可隐式转换为统一类型。
+ - 两个数组的元素类型 `T` 可以是数值类型、字符串类型、时间类型、IP类型。
+
## 返回值
-返回一个数组，包含 array1 和 array2 的并集中的所有元素，不包含重复项，如果输入参数为 NULL，则返回 NULL
+- 返回一个 ARRAY<T> 类型的新数组, 包含所有输入数组中的唯一元素，即去重后的并集。
+ - 如果某一个参数是 `NULL`，返回 `NULL` （见示例）。
-## 举例
+## 使用说明
-```sql
-SELECT ARRAY_UNION([1, 2, 3, 6],[1, 2, 5]),ARRAY_UNION([1, 4, 3, 5,
NULL],[1,6,10]);
-```
+1. 元素的去重依赖等值比较（= 运算符）。
+2. 数组结果中的 `NULL` 只会保留一个（见示例）。
+3. 输入的数组本身包含多个相同元素，结果中只保留一个（见示例）。
+4. 数组结果的顺序是不确定的。
-```text
-+--------------------------------------+---------------------------------------------+
-| array_union([1, 2, 3, 6], [1, 2, 5]) | array_union([1, 4, 3, 5, NULL], [1,
6, 10]) |
-+--------------------------------------+---------------------------------------------+
-| [3, 2, 1, 6, 5] | [null, 10, 3, 1, 6, 4, 5]
|
-+--------------------------------------+---------------------------------------------+
-```
+## 示例
+
+1. 简单实例
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'));
+ +---------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world')) |
+ +---------------------------------------------------------------+
+ | ["world", "hello"] |
+ +---------------------------------------------------------------+
+
+ SELECT ARRAY_UNION(ARRAY(1, 2, 3), ARRAY(3, 5, 6));
+ +---------------------------------------------+
+ | ARRAY_UNION(ARRAY(1, 2, 3), ARRAY(3, 5, 6)) |
+ +---------------------------------------------+
+ | [1, 5, 2, 6, 3] |
+ +---------------------------------------------+
+ ```
+
+2. 输入的数组是 `NULL`, 返回`NULL`
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'),
NULL);
+ +---------------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', 'world'), NULL) |
+ +---------------------------------------------------------------------+
+ | NULL |
+ +---------------------------------------------------------------------+
+ ```
+
+3. 输入的数组里面包含 `NULL`, 输出的数组里面仅包含一个 `NULL`
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL));
+ +------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL)) |
+ +------------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +------------------------------------------------------------+
+
+ SELECT ARRAY_UNION(ARRAY(NULL, 'world'), ARRAY('hello', NULL));
+ +---------------------------------------------------------+
+ | ARRAY_UNION(ARRAY(NULL, 'world'), ARRAY('hello', NULL)) |
+ +---------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +---------------------------------------------------------+
+ ```
+
+4. 数组本身包含重复元素, 只会返回一个
+
+ ```SQL
+ SELECT ARRAY_UNION(ARRAY('hello', 'world', 'hello'), ARRAY('hello',
NULL));
+ +------------------------------------------------------------+
+ | ARRAY_UNION(ARRAY('hello', 'world'), ARRAY('hello', NULL)) |
+ +------------------------------------------------------------+
+ | [null, "world", "hello"] |
+ +------------------------------------------------------------+
+ ```
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
index 36f337c4c04..97378011ae2 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-with-constant.md
@@ -5,37 +5,78 @@
}
---
-## 描述
+## 功能
-生成一个包含 n 个重复元素 element 的数组
+`ARRAY_WITH_CONSTANT` 用于生成一个指定长度的数组，其中所有元素都为给定的值。
## 语法
-```sql
-ARRAY_WITH_CONSTANT(<n>, <element>)
+```SQL
+ARRAY_WITH_CONSTANT(count, element)
```
## 参数
-| 参数 | 说明 |
-|--|------|
-| `<n>` | 元数个数 |
-| `<element>` | 指定元素 |
+- `count`：整数类型，指定返回数组的长度。
+
+- `element`： `ARRAY` 类型中支持的所有存储类型。
## 返回值
-返回一个数组，包含 n 个重复的 element 元素。array_repeat 与 array_with_constant 功能相同，用来兼容 hive
语法格式。
+- 返回一个 `ARRAY<T>` 类型的数组，其中 `T` 是 `element` 的类型。
+ - 数组中包含 `count` 个相同的 `element`。
-## 举例
+## 使用说明
-```sql
-SELECT ARRAY_WITH_CONSTANT(2, "hello"),ARRAY_WITH_CONSTANT(3, 12345);
-```
+- 如果 `count = 0` 或者 `NULL`，返回空数组。
+- 如果 element 为 NULL，数组中所有元素均为 NULL。
+- 函数功能和 `ARRAY_REPEAT` 函数相同，参数位置相反。
+- 可以与其他数组函数组合使用，实现更复杂的数据构造逻辑。
-```text
-+---------------------------------+-------------------------------+
-| array_with_constant(2, 'hello') | array_with_constant(3, 12345) |
-+---------------------------------+-------------------------------+
-| ["hello", "hello"] | [12345, 12345, 12345] |
-+---------------------------------+-------------------------------+
-```
+## 示例
+
+1. 简单实例
+
+ ```SQL
+ SELECT ARRAY_WITH_CONSTANT(3, 'hello');
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(3, 'hello') |
+ +---------------------------------+
+ | ["hello", "hello", "hello"] |
+ +---------------------------------+
+ ```
+
+2. 异常参数
+
+ ```SQL
+ SELECT ARRAY_WITH_CONSTANT(0, 'hello');
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(0, 'hello') |
+ +---------------------------------+
+ | [] |
+ +---------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(NULL, 'hello');
+ +------------------------------------+
+ | ARRAY_WITH_CONSTANT(NULL, 'hello') |
+ +------------------------------------+
+ | [] |
+ +------------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(2, NULL);
+ +------------------------------+
+ | ARRAY_WITH_CONSTANT(2, NULL) |
+ +------------------------------+
+ | [null, null] |
+ +------------------------------+
+
+ SELECT ARRAY_WITH_CONSTANT(NULL, NULL);
+ +---------------------------------+
+ | ARRAY_WITH_CONSTANT(NULL, NULL) |
+ +---------------------------------+
+ | [] |
+ +---------------------------------+
+
+ -- 返回错误：INVALID_ARGUMENT
+ SELECT ARRAY_WITH_CONSTANT(-1, 'hello');
+ ```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
index 749a866db85..7a240878e3e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/array-zip.md
@@ -5,36 +5,77 @@
}
---
-## 描述
+## 功能
-将所有数组合并成一个单一的数组。结果数组包含源数组中按参数列表顺序分组的相应元素。
+`ARRAY_ZIP` 函数用于将多个 `ARRAY` (如 `arr1, arr2, ... , arrN`)
按元素位置组合成一个`ARRAY<STRUCT>`，其中每个 `STRUCT` 包含来自各个输入数组对应位置的元素。
## 语法
-```sql
-ARRAY_ZIP(<array>[, <array> ])
+```SQL
+ARRAY_ZIP(arr1, arr2, ... , arrN)
```
## 参数
-| 参数 | 说明 |
-|--|------|
-| `<array>` | 输入数组 |
+- `arr1, arr2, ..., arrN`：输入的 N 个数组，类型为 `ARRAY<T1>, ARRAY<T2>, ..., ARRAY<Tn>`。
## 返回值
-返回来自源数组的元素分组成结构体的数组。结构体中的数据类型与输入数组的类型相同，并按照传递数组的顺序排列。
+- 返回值类型是 `ARRAY<STRUCT<col1 T1, col2 T2, ..., colN Tn>>`，其中每个 `STRUCT`
代表输入数组在同一索引位置上的组合。
-## 举例
-```sql
-SELECT ARRAY_ZIP(['a', 'b', 'c'], [1, 2, 3]);
-```
+## 使用说明
-```text
-+--------------------------------------------------------+
-| array_zip(['a', 'b', 'c'], [1, 2, 3]) |
-+--------------------------------------------------------+
-| [{"1":"a", "2":1}, {"1":"b", "2":2}, {"1":"c", "2":3}] |
-+--------------------------------------------------------+
-```
+1. **如果多个数组长度不一致，函数执行失败，返回 `RUNTIME_ERROR`**。
+2. 支持不同类型的数组输入，返回的结构体字段类型与输入数组一一对应。
+3. 可用于将多个并行数组组合成结构化格式，方便进一步的处理或分析。
+
+## 示例
+
+1. 多个数组组合
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"),
ARRAY(true, false, true));
+
+-------------------------------------------------------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), ARRAY(true,
false, true)) |
+
+-------------------------------------------------------------------------------------------------------------------+
+ | [{"col1":23, "col2":"John", "col3":1}, {"col1":24, "col2":"Jane",
"col3":0}, {"col1":25, "col2":"Jim", "col3":1}] |
+
+-------------------------------------------------------------------------------------------------------------------+
+ ```
+ - 返回值的第一个 `STRUCT` 包含了每个参数 `ARRAY` 的第一个元素。
+ - 返回值的第二个 `STRUCT` 包含了每个参数 `ARRAY` 的第二个元素。
+ - 返回值的第三个 `STRUCT` 包含了每个参数 `ARRAY` 的第三个元素。
+
+2. 访问返回值
+
+ ```SQL
+ -- 访问返回的ARRAY
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"))[1];
+ +---------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"))[1] |
+ +---------------------------------------------------------------+
+ | {"col1":23, "col2":"John"} |
+ +---------------------------------------------------------------+
+ ```
+
+3. 某一个数组为`NULL`，返回 `NULL`
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), NULL) ;
+ +------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, 24, 25), ARRAY("John", "Jane", "Jim"), NULL) |
+ +------------------------------------------------------------------+
+ | NULL |
+ +------------------------------------------------------------------+
+ ```
+
+4. `ARRAY` 中的元素含有 `NULL`, 对应的 `STRUCT` 的那一列是 `NULL`
+
+ ```SQL
+ SELECT ARRAY_ZIP(ARRAY(23, NULL, 25), ARRAY("John", "Jane", NULL),
ARRAY(NULL, false, true));
+
+-----------------------------------------------------------------------------------------------------------------------+
+ | ARRAY_ZIP(ARRAY(23, NULL, 25), ARRAY("John", "Jane", NULL), ARRAY(NULL,
false, true)) |
+
+-----------------------------------------------------------------------------------------------------------------------+
+ | [{"col1":23, "col2":"John", "col3":null}, {"col1":null, "col2":"Jane",
"col3":0}, {"col1":25, "col2":null, "col3":1}] |
+
+-----------------------------------------------------------------------------------------------------------------------+
+ ```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
index 4e6854b8ce1..fd8d6597b0a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/array-functions/arrays-overlap.md
@@ -5,52 +5,149 @@
}
---
-## 描述
+## 功能
-判断 left 和 right 数组中是否包含公共元素
+`ARRAYS_OVERLAP` 用于判断两个数组是否存在至少一个相同的元素，如果存在返回 `true`，否则返回 `false`。
## 语法
-```sql
-ARRAYS_OVERLAP(<left>, <right>)
+```SQL
+ARRAYS_OVERLAP(arr1, arr2)
```
## 参数
-| 参数 | 说明 |
-|--|--|
-| `<left>` | 待判断的数组 |
-| `<right>` | 待判断的数组 |
+- `arr1`：第一个数组，类型为 `ARRAY<T>`。
+
+- `arr2`：第二个数组，类型为 `ARRAY<T>`。
+
+ - 两个数组的元素类型 `T` 必须一致或可以相互隐式转换。
+ - 两个数组的元素类型 `T` 可以是数值类型、字符串类型、时间类型、IP类型。
## 返回值
-如果 left 和 right 具有任何非 null 的共同元素，则返回 true。
-如果没有非 null 的共同元素且任一数组包含 null，则返回 null。
-如果没有非 null 的共同元素，且 left 和 right 都不包含 null，则返回 false。
+- 返回 `BOOLEAN` 类型：
-## 举例
+ - 如果两个数组有交集，返回 `true`；
-```
-select arrays_overlap([1, 2, 3], [1, null]);
-+--------------------------------------+
-| arrays_overlap([1, 2, 3], [1, null]) |
-+--------------------------------------+
-| 1 |
-+--------------------------------------+
-
-
-select arrays_overlap([2, 3], [1, null]);
-+-----------------------------------+
-| arrays_overlap([2, 3], [1, null]) |
-+-----------------------------------+
-| NULL |
-+-----------------------------------+
-
-select arrays_overlap([2, 3], [1]);
-+-----------------------------+
-+-----------------------------+
-| arrays_overlap([2, 3], [1]) |
-+-----------------------------+
-| 0 |
-+-----------------------------+
-```
+ - 如果没有交集，返回 `false`。
+
+## 使用说明
+
+1. **比较方式使用元素的等值判断（= 运算符）**。
+2. **`NULL`和`NULL`在这个函数中被认为是相等的**（具体见示例）。
+3. 可以在建表语句中指定**倒排索引来加速函数的执行**（具体见示例）。
+ - 函数作谓词判断条件使用时，倒排索引会加速函数的执行。
+ - 当函数作查询结果使用时，倒排索引不会加速函数的执行。
+4. 常用于数据清洗、标签匹配、用户行为交集判断等场景。
+
+## 示例
+
+1. 简单示例
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi',
'hey'));
+ +----------------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey')) |
+ +----------------------------------------------------------------------+
+ | 1 |
+ +----------------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey'));
+ +----------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey')) |
+ +----------------------------------------------------------------+
+ | 0 |
+ +----------------------------------------------------------------+
+ ```
+
+2. 错误参数，当输入的参数是不支持的类型时，返回 `INVALID_ARGUMENT`
+
+ ```SQL
+ -- [INVALID_ARGUMENT]execute failed, unsupported types for function
arrays_overlap
+ SELECT ARRAYS_OVERLAP(ARRAY(ARRAY('hello', 'aloha'), ARRAY('hi', 'hey')),
ARRAY(ARRAY('hello', 'hi', 'hey'), ARRAY('aloha', 'hi')));
+ ```
+
+3. 输入的`ARRAY` 是 `NULL`，返回值是 `NULL`
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL);
+ +-----------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL) |
+ +-----------------------------------------------+
+ | NULL |
+ +-----------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(NULL, NULL);
+ +----------------------------+
+ | ARRAYS_OVERLAP(NULL, NULL) |
+ +----------------------------+
+ | NULL |
+ +----------------------------+
+ ```'
+
+4. 输入的`ARRAY` 包含 `NULL` 时，`NULL` 和 `NULL`被视作相等
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL));
+ +---------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL)) |
+ +---------------------------------------------------------------+
+ | 1 |
+ +---------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL));
+ +----------------------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL)) |
+ +----------------------------------------------------------------+
+ | 0 |
+ +----------------------------------------------------------------+
+
+ SELECT ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL));
+ +---------------------------------------------------+
+ | ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL)) |
+ +---------------------------------------------------+
+ | 1 |
+ +---------------------------------------------------+
+ ```'
+
+5. 使用倒排索引加速查询
+
+ ```SQL
+ -- 建表包含倒排索引
+ CREATE TABLE IF NOT EXISTS arrays_overlap_table (
+ id INT,
+ array_column ARRAY<STRING>,
+ INDEX idx_array_column (array_column) USING INVERTED --只允许不分词倒排索引
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+
+ -- 插入两行
+ INSERT INTO arrays_overlap_table (id, array_column) VALUES (1,
ARRAY('HELLO', 'ALOHA')), (2, ARRAY('NO', 'WORLD'));
+ ```
+
+ - 当函数作谓词判断条件使用时，倒排索引会加速函数的执行
+
+ ```SQL
+ SELECT * from arrays_overlap_table WHERE ARRAYS_OVERLAP(array_column,
ARRAY('HELLO', 'PICKLE'));
+ +------+--------------------+
+ | id | array_column |
+ +------+--------------------+
+ | 1 | ["HELLO", "ALOHA"] |
+ +------+--------------------+
+
+- 当函数作查询结果使用时，倒排索引不会加速函数的执行
+
+ ```SQL
+ SELECT ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) FROM
arrays_overlap_table;
+ +--------------------------------------------------------+
+ | ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) |
+ +--------------------------------------------------------+
+ | 1 |
+ | 0 |
+ +--------------------------------------------------------+
+ ```
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
