This is an automated email from the ASF dual-hosted git repository.
qiaojialin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new d976dcf530 [IOTDB-3434] Implement the Built-in Functions of String
(#6250)
d976dcf530 is described below
commit d976dcf5301990fe935f12dc44f8a49ed027b151
Author: Weihao Li <[email protected]>
AuthorDate: Tue Jun 14 19:46:38 2022 +0800
[IOTDB-3434] Implement the Built-in Functions of String (#6250)
---
docs/UserGuide/UDF-Library/String-Processing.md | 656 +++++++++--
docs/zh/UserGuide/UDF-Library/String-Processing.md | 652 +++++++++--
.../iotdb/db/it/IoTDBUDTFBuiltinFunctionIT.java | 1230 ++++++++++++++++++++
.../BuiltinTimeSeriesGeneratingFunction.java | 21 +
.../commons/udf/builtin/String/UDTFConcat.java | 76 ++
.../commons/udf/builtin/String/UDTFEndsWith.java | 53 +
.../commons/udf/builtin/String/UDTFLower.java | 50 +
.../commons/udf/builtin/String/UDTFStartsWith.java | 52 +
.../commons/udf/builtin/String/UDTFStrCompare.java | 57 +
.../commons/udf/builtin/String/UDTFStrLength.java | 50 +
.../commons/udf/builtin/String/UDTFStrLocate.java | 62 +
.../commons/udf/builtin/String/UDTFSubstr.java | 72 ++
.../iotdb/commons/udf/builtin/String/UDTFTrim.java | 50 +
.../commons/udf/builtin/String/UDTFUpper.java | 50 +
14 files changed, 2974 insertions(+), 157 deletions(-)
diff --git a/docs/UserGuide/UDF-Library/String-Processing.md
b/docs/UserGuide/UDF-Library/String-Processing.md
index f339369692..5d3e4e9cf3 100644
--- a/docs/UserGuide/UDF-Library/String-Processing.md
+++ b/docs/UserGuide/UDF-Library/String-Processing.md
@@ -21,6 +21,583 @@
# String Processing
+## Length
+
+### Usage
+
+The function is used to get the length of input series.
+
+**Name:** LENGTH
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Output Series:** Output a single series. The type is INT32.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, length(s1) from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+----------------------+
+| Time|root.sg1.d1.s1|length(root.sg1.d1.s1)|
++-----------------------------+--------------+----------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| 6|
+|1970-01-01T08:00:00.002+08:00| 22test22| 8|
++-----------------------------+--------------+----------------------+
+```
+
+## StrLocate
+
+### Usage
+
+The function is used to get the position of the first occurrence of substring
`target` in input series. Returns -1 if there are no `target` in input.
+
+**Name:** LENGTH
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Parameter:**
+
++ `target`: The substring to be located.
++ `reverse`: Indicates whether reverse locate is required. The default value
is `false`, means left-to-right locate.
+
+**Output Series:** Output a single series. The type is INT32.
+
+**Note:** The index begins from 0.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, locate(s1, "target"="1") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+------------------------------------+
+| Time|root.sg1.d1.s1|locate(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
0|
+|1970-01-01T08:00:00.002+08:00| 22test22|
-1|
++-----------------------------+--------------+------------------------------------+
+```
+
+Another SQL for query:
+
+```sql
+select s1, locate(s1, "target"="1", "reverse"="true") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+------------------------------------------------------+
+| Time|root.sg1.d1.s1|locate(root.sg1.d1.s1,
"target"="1", "reverse"="true")|
++-----------------------------+--------------+------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
5|
+|1970-01-01T08:00:00.002+08:00| 22test22|
-1|
++-----------------------------+--------------+------------------------------------------------------+
+```
+
+## StartsWith
+
+### Usage
+
+The function is used to check whether input series starts with the specified
prefix.
+
+**Name:** STARTSWITH
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Parameter:**
++ `target`: The prefix to be checked.
+
+**Output Series:** Output a single series. The type is BOOLEAN.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, startswith(s1, "target"="1") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+----------------------------------------+
+| Time|root.sg1.d1.s1|startswith(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+----------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
true|
+|1970-01-01T08:00:00.002+08:00| 22test22|
false|
++-----------------------------+--------------+----------------------------------------+
+```
+
+## EndsWith
+
+### Usage
+
+The function is used to check whether input series ends with the specified
suffix.
+
+**Name:** ENDSWITH
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Parameter:**
++ `target`: The suffix to be checked.
+
+**Output Series:** Output a single series. The type is BOOLEAN.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, endswith(s1, "target"="1") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+--------------------------------------+
+| Time|root.sg1.d1.s1|endswith(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
true|
+|1970-01-01T08:00:00.002+08:00| 22test22|
false|
++-----------------------------+--------------+--------------------------------------+
+```
+
+## Concat
+
+### Usage
+
+The function is used to concat input series and target strings.
+
+**Name:** CONCAT
+
+**Input Series:** At least one input series. The data type is TEXT.
+
+**Parameter:**
++ `targets`: A series of K-V, key needs to start with `target` and be not
duplicated, value is the string you want to concat.
++ `series_behind`: Indicates whether series behind targets. The default value
is `false`.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+**Note:**
++ If value of input series is NULL, it will be skipped.
++ We can only concat input series and `targets` separately. `concat(s1,
"target1"="IoT", s2, "target2"="DB")` and
+ `concat(s1, s2, "target1"="IoT", "target2"="DB")` gives the same result.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+--------------+
+| Time|root.sg1.d1.s1|root.sg1.d1.s2|
++-----------------------------+--------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
++-----------------------------+--------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|concat(root.sg1.d1.s1, root.sg1.d1.s2,
"target1"="IoT", "target2"="DB")|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
1test1IoTDB|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
22test222222testIoTDB|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+```
+
+Another SQL for query:
+
+```sql
+select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB",
"series_behind"="true") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|concat(root.sg1.d1.s1, root.sg1.d1.s2,
"target1"="IoT", "target2"="DB", "series_behind"="true")|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
IoTDB1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
IoTDB22test222222test|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+```
+
+## Substr
+
+### Usage
+
+The function is used to get the substring `start` to `end - 1`.
+
+**Name:** SUBSTR
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Parameter:**
++ `start`: Indicates the start position of substring.
++ `end`: Indicates the end position of substring.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, substr(s1, "start"="0", "end"="2") from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+----------------------------------------------+
+| Time|root.sg1.d1.s1|substr(root.sg1.d1.s1,
"start"="0", "end"="2")|
++-----------------------------+--------------+----------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
1t|
+|1970-01-01T08:00:00.002+08:00| 22test22|
22|
++-----------------------------+--------------+----------------------------------------------+
+```
+
+## Upper
+
+### Usage
+
+The function is used to get the string of input series with all characters
changed to uppercase.
+
+**Name:** UPPER
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, upper(s1) from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+---------------------+
+| Time|root.sg1.d1.s1|upper(root.sg1.d1.s1)|
++-----------------------------+--------------+---------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| 1TEST1|
+|1970-01-01T08:00:00.002+08:00| 22test22| 22TEST22|
++-----------------------------+--------------+---------------------+
+```
+
+## Lower
+
+### Usage
+
+The function is used to get the string of input series with all characters
changed to lowercase.
+
+**Name:** LOWER
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1TEST1|
+|1970-01-01T08:00:00.002+08:00| 22TEST22|
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, lower(s1) from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+---------------------+
+| Time|root.sg1.d1.s1|lower(root.sg1.d1.s1)|
++-----------------------------+--------------+---------------------+
+|1970-01-01T08:00:00.001+08:00| 1TEST1| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22TEST22| 22test22|
++-----------------------------+--------------+---------------------+
+```
+
+## Trim
+
+### Usage
+
+The function is used to get the string whose value is same to input series,
with all leading and trailing space removed.
+
+**Name:** TRIM
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+**Note:** Returns NULL if input is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s3|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.002+08:00| 3querytest3|
+|1970-01-01T08:00:00.003+08:00| 3querytest3 |
++-----------------------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s3, trim(s3) from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+--------------------+
+| Time|root.sg1.d1.s3|trim(root.sg1.d1.s3)|
++-----------------------------+--------------+--------------------+
+|1970-01-01T08:00:00.002+08:00| 3querytest3| 3querytest3|
+|1970-01-01T08:00:00.003+08:00| 3querytest3 | 3querytest3|
++-----------------------------+--------------+--------------------+
+```
+
+## StrCmp
+
+### Usage
+
+The function is used to get the compare result of two input series. Returns
`0` if series value are the same, a `negative integer` if value of series1 is
smaller than series2,
+a `positive integer` if value of series1 is more than series2.
+
+**Name:** StrCmp
+
+**Input Series:** Support two input series. Data types are all the TEXT.
+
+**Output Series:** Output a single series. The type is INT32.
+
+**Note:** Returns NULL either series value is NULL.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+--------------+--------------+
+| Time|root.sg1.d1.s1|root.sg1.d1.s2|
++-----------------------------+--------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
++-----------------------------+--------------+--------------+
+```
+
+SQL for query:
+
+```sql
+select s1, s2, strcmp(s1, s2) from root.sg1.d1
+```
+
+Output series:
+
+```
++-----------------------------+--------------+--------------+--------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|strcmp(root.sg1.d1.s1, root.sg1.d1.s2)|
++-----------------------------+--------------+--------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
66|
++-----------------------------+--------------+--------------+--------------------------------------+
+```
+
+
+## StrReplace
+
+### Usage
+
+The function is used to replace the specific substring with given string.
+
+**Name:** STRREPLACE
+
+**Input Series:** Only support a single input series. The data type is TEXT.
+
+**Parameter:**
+
++ `target`: The target substring to be replaced.
++ `replace`: The string to be put on.
++ `limit`: The number of matches to be replaced which should be an integer no
less than -1,
+ default to -1 which means all matches will be replaced.
++ `offset`: The number of matches to be skipped, which means the first
`offset` matches will not be replaced, default to 0.
++ `reverse`: Whether to count all the matches reversely, default to 'false'.
+
+**Output Series:** Output a single series. The type is TEXT.
+
+### Examples
+
+Input series:
+
+```
++-----------------------------+---------------+
+| Time|root.test.d1.s1|
++-----------------------------+---------------+
+|2021-01-01T00:00:01.000+08:00| A,B,A+,B-|
+|2021-01-01T00:00:02.000+08:00| A,A+,A,B+|
+|2021-01-01T00:00:03.000+08:00| B+,B,B|
+|2021-01-01T00:00:04.000+08:00| A+,A,A+,A|
+|2021-01-01T00:00:05.000+08:00| A,B-,B,B|
++-----------------------------+---------------+
+```
+
+SQL for query:
+
+```sql
+select strreplace(s1, "target"=",", "replace"="/", "limit"="2") from
root.test.d1
+```
+
+Output series:
+
+```
++-----------------------------+-----------------------------------------+
+| Time|strreplace(root.test.d1.s1, "target"=",",|
+| | "replace"="/", "limit"="2")|
++-----------------------------+-----------------------------------------+
+|2021-01-01T00:00:01.000+08:00| A/B/A+,B-|
+|2021-01-01T00:00:02.000+08:00| A/A+/A,B+|
+|2021-01-01T00:00:03.000+08:00| B+/B/B|
+|2021-01-01T00:00:04.000+08:00| A+/A/A+,A|
+|2021-01-01T00:00:05.000+08:00| A/B-/B,B|
++-----------------------------+-----------------------------------------+
+```
+
+Another SQL for query:
+
+```sql
+select strreplace(s1, "target"=",", "replace"="/", "limit"="1", "offset"="1",
"reverse"="true") from root.test.d1
+```
+
+Output series:
+
+```
++-----------------------------+-----------------------------------------------------+
+| Time|strreplace(root.test.d1.s1, "target"=",",
"replace"= |
+| | "|", "limit"="1", "offset"="1",
"reverse"="true")|
++-----------------------------+-----------------------------------------------------+
+|2021-01-01T00:00:01.000+08:00|
A,B/A+,B-|
+|2021-01-01T00:00:02.000+08:00|
A,A+/A,B+|
+|2021-01-01T00:00:03.000+08:00|
B+/B,B|
+|2021-01-01T00:00:04.000+08:00|
A+,A/A+,A|
+|2021-01-01T00:00:05.000+08:00|
A,B-/B,B|
++-----------------------------+-----------------------------------------------------+
+```
+
## RegexMatch
### Usage
@@ -217,83 +794,4 @@ Output series:
|2021-01-01T00:00:04.000+08:00|
A|
|2021-01-01T00:00:05.000+08:00|
B|
+-----------------------------+-----------------------------------------------------+
-```
-
-## StrReplace
-
-### Usage
-
-The function is used to replace the specific substring with given string.
-
-**Name:** STRREPLACE
-
-**Input Series:** Only support a single input series. The data type is TEXT.
-
-**Parameter:**
-
-+ `target`: The target substring to be replaced.
-+ `replace`: The string to be put on.
-+ `limit`: The number of matches to be replaced which should be an integer no
less than -1,
- default to -1 which means all matches will be replaced.
-+ `offset`: The number of matches to be skipped, which means the first
`offset` matches will not be replaced, default to 0.
-+ `reverse`: Whether to count all the matches reversely, default to 'false'.
-
-**Output Series:** Output a single series. The type is TEXT.
-
-### Examples
-
-Input series:
-
-```
-+-----------------------------+---------------+
-| Time|root.test.d1.s1|
-+-----------------------------+---------------+
-|2021-01-01T00:00:01.000+08:00| A,B,A+,B-|
-|2021-01-01T00:00:02.000+08:00| A,A+,A,B+|
-|2021-01-01T00:00:03.000+08:00| B+,B,B|
-|2021-01-01T00:00:04.000+08:00| A+,A,A+,A|
-|2021-01-01T00:00:05.000+08:00| A,B-,B,B|
-+-----------------------------+---------------+
-```
-
-SQL for query:
-
-```sql
-select strreplace(s1, "target"=",", "replace"="/", "limit"="2") from
root.test.d1
-```
-
-Output series:
-
-```
-+-----------------------------+-----------------------------------------+
-| Time|strreplace(root.test.d1.s1, "target"=",",|
-| | "replace"="/", "limit"="2")|
-+-----------------------------+-----------------------------------------+
-|2021-01-01T00:00:01.000+08:00| A/B/A+,B-|
-|2021-01-01T00:00:02.000+08:00| A/A+/A,B+|
-|2021-01-01T00:00:03.000+08:00| B+/B/B|
-|2021-01-01T00:00:04.000+08:00| A+/A/A+,A|
-|2021-01-01T00:00:05.000+08:00| A/B-/B,B|
-+-----------------------------+-----------------------------------------+
-```
-
-Another SQL for query:
-
-```sql
-select strreplace(s1, "target"=",", "replace"="/", "limit"="1", "offset"="1",
"reverse"="true") from root.test.d1
-```
-
-Output series:
-
-```
-+-----------------------------+-----------------------------------------------------+
-| Time|strreplace(root.test.d1.s1, "target"=",",
"replace"= |
-| | "|", "limit"="1", "offset"="1",
"reverse"="true")|
-+-----------------------------+-----------------------------------------------------+
-|2021-01-01T00:00:01.000+08:00|
A,B/A+,B-|
-|2021-01-01T00:00:02.000+08:00|
A,A+/A,B+|
-|2021-01-01T00:00:03.000+08:00|
B+/B,B|
-|2021-01-01T00:00:04.000+08:00|
A+,A/A+,A|
-|2021-01-01T00:00:05.000+08:00|
A,B-/B,B|
-+-----------------------------+-----------------------------------------------------+
```
\ No newline at end of file
diff --git a/docs/zh/UserGuide/UDF-Library/String-Processing.md
b/docs/zh/UserGuide/UDF-Library/String-Processing.md
index 2b5fec4e83..4ff0d7c2d6 100644
--- a/docs/zh/UserGuide/UDF-Library/String-Processing.md
+++ b/docs/zh/UserGuide/UDF-Library/String-Processing.md
@@ -21,6 +21,580 @@
# 字符串处理
+## Length
+
+### 函数简介
+
+本函数用于获取输入序列的长度。
+
+**函数名:** LENGTH
+
+**输入序列:** 仅支持单个输入序列，类型为 TEXT。
+
+**输出序列:** 输出单个序列，类型为 INT32。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, length(s1) from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+----------------------+
+| Time|root.sg1.d1.s1|length(root.sg1.d1.s1)|
++-----------------------------+--------------+----------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| 6|
+|1970-01-01T08:00:00.002+08:00| 22test22| 8|
++-----------------------------+--------------+----------------------+
+```
+
+## StrLocate
+
+### 函数简介
+
+本函数用于获取`target`子串第一次出现在输入序列的位置，如果输入序列中不包含`target`则返回 -1 。
+
+**函数名:** LENGTH
+
+**输入序列:** 仅支持单个输入序列，类型为 TEXT。
+
+**参数:**
+
++ `target`: 需要被定位的子串。
++ `reverse`: 指定是否需要倒序定位，默认值为`false`, 即从左至右定位。
+
+**输出序列:** 输出单个序列，类型为INT32。
+
+**提示:** 下标从 0 开始。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, locate(s1, "target"="1") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+------------------------------------+
+| Time|root.sg1.d1.s1|locate(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
0|
+|1970-01-01T08:00:00.002+08:00| 22test22|
-1|
++-----------------------------+--------------+------------------------------------+
+```
+
+另一个用于查询的 SQL 语句:
+
+```sql
+select s1, locate(s1, "target"="1", "reverse"="true") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+------------------------------------------------------+
+| Time|root.sg1.d1.s1|locate(root.sg1.d1.s1,
"target"="1", "reverse"="true")|
++-----------------------------+--------------+------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
5|
+|1970-01-01T08:00:00.002+08:00| 22test22|
-1|
++-----------------------------+--------------+------------------------------------------------------+
+```
+
+## StartsWith
+
+### 函数简介
+
+本函数用于判断输入序列是否有指定前缀。
+
+**函数名:** STARTSWITH
+
+**输入序列:** 仅支持单个输入序列，类型为 TEXT。
+
+**参数:**
++ `target`: 需要匹配的前缀。
+
+**输出序列:** 输出单个序列，类型为 BOOLEAN。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, startswith(s1, "target"="1") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+----------------------------------------+
+| Time|root.sg1.d1.s1|startswith(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+----------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
true|
+|1970-01-01T08:00:00.002+08:00| 22test22|
false|
++-----------------------------+--------------+----------------------------------------+
+```
+
+## EndsWith
+
+### 函数简介
+
+本函数用于判断输入序列是否有指定后缀。
+
+**函数名:** ENDSWITH
+
+**输入序列:** 仅支持单个输入序列，类型为 TEXT。
+
+**参数:**
++ `target`: 需要匹配的后缀。
+
+**输出序列:** 输出单个序列，类型为 BOOLEAN。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, endswith(s1, "target"="1") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+--------------------------------------+
+| Time|root.sg1.d1.s1|endswith(root.sg1.d1.s1,
"target"="1")|
++-----------------------------+--------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
true|
+|1970-01-01T08:00:00.002+08:00| 22test22|
false|
++-----------------------------+--------------+--------------------------------------+
+```
+
+## Concat
+
+### 函数简介
+
+本函数用于拼接输入序列和`target`字串。
+
+**函数名:** CONCAT
+
+**输入序列:** 至少一个输入序列，类型为 TEXT。
+
+**参数:**
++ `targets`: 一系列 K-V, key需要以`target`为前缀且不重复, value是待拼接的字符串。
++ `series_behind`: 指定拼接时时间序列是否在后面，默认为`false`。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:**
++ 如果输入序列是NULL, 跳过该序列的拼接。
++ 函数只能将输入序列和`targets`区分开各自拼接。`concat(s1, "target1"="IoT", s2, "target2"="DB")`和
+ `concat(s1, s2, "target1"="IoT", "target2"="DB")`得到的结果是一样的。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+--------------+
+| Time|root.sg1.d1.s1|root.sg1.d1.s2|
++-----------------------------+--------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
++-----------------------------+--------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|concat(root.sg1.d1.s1, root.sg1.d1.s2,
"target1"="IoT", "target2"="DB")|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
1test1IoTDB|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
22test222222testIoTDB|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------+
+```
+
+另一个用于查询的 SQL 语句:
+
+```sql
+select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB",
"series_behind"="true") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|concat(root.sg1.d1.s1, root.sg1.d1.s2,
"target1"="IoT", "target2"="DB", "series_behind"="true")|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
IoTDB1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
IoTDB22test222222test|
++-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
+```
+
+## Substr
+
+### 函数简介
+
+本函数用于获取下标从`start`到`end - 1`的子串
+
+**函数名:** SUBSTR
+
+**输入序列:** 仅支持单个输入序列，类型为TEXT。
+
+**参数:**
++ `start`: 指定子串开始下标。
++ `end`: 指定子串结束下标。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, substr(s1, "start"="0", "end"="2") from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+----------------------------------------------+
+| Time|root.sg1.d1.s1|substr(root.sg1.d1.s1,
"start"="0", "end"="2")|
++-----------------------------+--------------+----------------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
1t|
+|1970-01-01T08:00:00.002+08:00| 22test22|
22|
++-----------------------------+--------------+----------------------------------------------+
+```
+
+## Upper
+
+### 函数简介
+
+本函数用于将输入序列转化为大写。
+
+**函数名:** UPPER
+
+**输入序列:** 仅支持单个输入序列，类型为TEXT。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22test22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, upper(s1) from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+---------------------+
+| Time|root.sg1.d1.s1|upper(root.sg1.d1.s1)|
++-----------------------------+--------------+---------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| 1TEST1|
+|1970-01-01T08:00:00.002+08:00| 22test22| 22TEST22|
++-----------------------------+--------------+---------------------+
+```
+
+## Lower
+
+### 函数简介
+
+本函数用于将输入序列转换为小写。
+
+**函数名:** LOWER
+
+**输入序列:** 仅支持单个输入序列，类型为TEXT。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s1|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1TEST1|
+|1970-01-01T08:00:00.002+08:00| 22TEST22|
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, lower(s1) from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+---------------------+
+| Time|root.sg1.d1.s1|lower(root.sg1.d1.s1)|
++-----------------------------+--------------+---------------------+
+|1970-01-01T08:00:00.001+08:00| 1TEST1| 1test1|
+|1970-01-01T08:00:00.002+08:00| 22TEST22| 22test22|
++-----------------------------+--------------+---------------------+
+```
+
+## Trim
+
+### 函数简介
+
+本函数用于移除输入序列前后的空格。
+
+**函数名:** TRIM
+
+**输入序列:** 仅支持单个输入序列，类型为TEXT。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:** 如果输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+
+| Time|root.sg1.d1.s3|
++-----------------------------+--------------+
+|1970-01-01T08:00:00.002+08:00| 3querytest3|
+|1970-01-01T08:00:00.003+08:00| 3querytest3 |
++-----------------------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s3, trim(s3) from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+--------------------+
+| Time|root.sg1.d1.s3|trim(root.sg1.d1.s3)|
++-----------------------------+--------------+--------------------+
+|1970-01-01T08:00:00.002+08:00| 3querytest3| 3querytest3|
+|1970-01-01T08:00:00.003+08:00| 3querytest3 | 3querytest3|
++-----------------------------+--------------+--------------------+
+```
+
+## StrCmp
+
+### 函数简介
+
+本函数用于比较两个输入序列。 如果值相同返回 `0` , 序列1的值小于序列2的值返回一个`负数`，序列1的值大于序列2的值返回一个`正数`。
+
+**函数名:** StrCmp
+
+**输入序列:** 输入两个序列，类型均为 TEXT。
+
+**输出序列:** 输出单个序列，类型为 TEXT。
+
+**提示:** 如果任何一个输入是NULL，返回NULL。
+
+### 使用示例
+
+输入序列:
+
+```
++-----------------------------+--------------+--------------+
+| Time|root.sg1.d1.s1|root.sg1.d1.s2|
++-----------------------------+--------------+--------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
++-----------------------------+--------------+--------------+
+```
+
+用于查询的 SQL 语句:
+
+```sql
+select s1, s2, strcmp(s1, s2) from root.sg1.d1
+```
+
+输出序列:
+
+```
++-----------------------------+--------------+--------------+--------------------------------------+
+|
Time|root.sg1.d1.s1|root.sg1.d1.s2|strcmp(root.sg1.d1.s1, root.sg1.d1.s2)|
++-----------------------------+--------------+--------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 1test1| null|
null|
+|1970-01-01T08:00:00.002+08:00| 22test22| 2222test|
66|
++-----------------------------+--------------+--------------+--------------------------------------+
+```
+
+## StrReplace
+
+### 函数简介
+
+本函数用于将文本中的子串替换为指定的字符串。
+
+**函数名：** STRREPLACE
+
+**输入序列：** 仅支持单个输入序列，类型为 TEXT。
+
+**参数：**
+
++ `target`: 需要替换的字符子串
++ `replace`: 替换后的字符串。
++ `limit`: 替换次数，大于等于 -1 的整数，默认为 -1 表示所有匹配的子串都会被替换。
++ `offset`: 需要跳过的匹配次数，即前`offset`次匹配到的字符子串并不会被替换，默认为 0。
++ `reverse`: 是否需要反向计数，默认为 false 即按照从左向右的次序。
+
+**输出序列：** 输出单个序列，类型为 TEXT。
+
+### 使用示例
+
+输入序列：
+
+```
++-----------------------------+---------------+
+| Time|root.test.d1.s1|
++-----------------------------+---------------+
+|2021-01-01T00:00:01.000+08:00| A,B,A+,B-|
+|2021-01-01T00:00:02.000+08:00| A,A+,A,B+|
+|2021-01-01T00:00:03.000+08:00| B+,B,B|
+|2021-01-01T00:00:04.000+08:00| A+,A,A+,A|
+|2021-01-01T00:00:05.000+08:00| A,B-,B,B|
++-----------------------------+---------------+
+```
+
+用于查询的 SQL 语句：
+
+```sql
+select strreplace(s1, "target"=",", "replace"="/", "limit"="2") from
root.test.d1
+```
+
+输出序列：
+
+```
++-----------------------------+-----------------------------------------+
+| Time|strreplace(root.test.d1.s1, "target"=",",|
+| | "replace"="/", "limit"="2")|
++-----------------------------+-----------------------------------------+
+|2021-01-01T00:00:01.000+08:00| A/B/A+,B-|
+|2021-01-01T00:00:02.000+08:00| A/A+/A,B+|
+|2021-01-01T00:00:03.000+08:00| B+/B/B|
+|2021-01-01T00:00:04.000+08:00| A+/A/A+,A|
+|2021-01-01T00:00:05.000+08:00| A/B-/B,B|
++-----------------------------+-----------------------------------------+
+```
+
+另一个用于查询的 SQL 语句：
+
+```sql
+select strreplace(s1, "target"=",", "replace"="/", "limit"="1", "offset"="1",
"reverse"="true") from root.test.d1
+```
+
+输出序列：
+
+```
++-----------------------------+-----------------------------------------------------+
+| Time|strreplace(root.test.d1.s1, "target"=",",
"replace"= |
+| | "|", "limit"="1", "offset"="1",
"reverse"="true")|
++-----------------------------+-----------------------------------------------------+
+|2021-01-01T00:00:01.000+08:00|
A,B/A+,B-|
+|2021-01-01T00:00:02.000+08:00|
A,A+/A,B+|
+|2021-01-01T00:00:03.000+08:00|
B+/B,B|
+|2021-01-01T00:00:04.000+08:00|
A+,A/A+,A|
+|2021-01-01T00:00:05.000+08:00|
A,B-/B,B|
++-----------------------------+-----------------------------------------------------+
+```
+
## RegexMatch
### 函数简介
@@ -209,82 +783,4 @@ select regexsplit(s1, "regex"=",", "index"="3") from
root.test.d1
|2021-01-01T00:00:04.000+08:00|
A|
|2021-01-01T00:00:05.000+08:00|
B|
+-----------------------------+-----------------------------------------------------+
-```
-
-## StrReplace
-
-### 函数简介
-
-本函数用于将文本中的子串替换为指定的字符串。
-
-**函数名：** STRREPLACE
-
-**输入序列：** 仅支持单个输入序列，类型为 TEXT。
-
-**参数：**
-
-+ `target`: 需要替换的字符子串
-+ `replace`: 替换后的字符串。
-+ `limit`: 替换次数，大于等于 -1 的整数，默认为 -1 表示所有匹配的子串都会被替换。
-+ `offset`: 需要跳过的匹配次数，即前`offset`次匹配到的字符子串并不会被替换，默认为 0。
-+ `reverse`: 是否需要反向计数，默认为 false 即按照从左向右的次序。
-
-**输出序列：** 输出单个序列，类型为 TEXT。
-
-### 使用示例
-
-输入序列：
-
-```
-+-----------------------------+---------------+
-| Time|root.test.d1.s1|
-+-----------------------------+---------------+
-|2021-01-01T00:00:01.000+08:00| A,B,A+,B-|
-|2021-01-01T00:00:02.000+08:00| A,A+,A,B+|
-|2021-01-01T00:00:03.000+08:00| B+,B,B|
-|2021-01-01T00:00:04.000+08:00| A+,A,A+,A|
-|2021-01-01T00:00:05.000+08:00| A,B-,B,B|
-+-----------------------------+---------------+
-```
-
-用于查询的 SQL 语句：
-
-```sql
-select strreplace(s1, "target"=",", "replace"="/", "limit"="2") from
root.test.d1
-```
-
-输出序列：
-
-```
-+-----------------------------+-----------------------------------------+
-| Time|strreplace(root.test.d1.s1, "target"=",",|
-| | "replace"="/", "limit"="2")|
-+-----------------------------+-----------------------------------------+
-|2021-01-01T00:00:01.000+08:00| A/B/A+,B-|
-|2021-01-01T00:00:02.000+08:00| A/A+/A,B+|
-|2021-01-01T00:00:03.000+08:00| B+/B/B|
-|2021-01-01T00:00:04.000+08:00| A+/A/A+,A|
-|2021-01-01T00:00:05.000+08:00| A/B-/B,B|
-+-----------------------------+-----------------------------------------+
-```
-
-另一个用于查询的 SQL 语句：
-
-```sql
-select strreplace(s1, "target"=",", "replace"="/", "limit"="1", "offset"="1",
"reverse"="true") from root.test.d1
-```
-
-输出序列：
-
-```
-+-----------------------------+-----------------------------------------------------+
-| Time|strreplace(root.test.d1.s1, "target"=",",
"replace"= |
-| | "|", "limit"="1", "offset"="1",
"reverse"="true")|
-+-----------------------------+-----------------------------------------------------+
-|2021-01-01T00:00:01.000+08:00|
A,B/A+,B-|
-|2021-01-01T00:00:02.000+08:00|
A,A+/A,B+|
-|2021-01-01T00:00:03.000+08:00|
B+/B,B|
-|2021-01-01T00:00:04.000+08:00|
A+,A/A+,A|
-|2021-01-01T00:00:05.000+08:00|
A,B-/B,B|
-+-----------------------------+-----------------------------------------------------+
```
\ No newline at end of file
diff --git
a/integration-test/src/test/java/org/apache/iotdb/db/it/IoTDBUDTFBuiltinFunctionIT.java
b/integration-test/src/test/java/org/apache/iotdb/db/it/IoTDBUDTFBuiltinFunctionIT.java
new file mode 100644
index 0000000000..278353c122
--- /dev/null
+++
b/integration-test/src/test/java/org/apache/iotdb/db/it/IoTDBUDTFBuiltinFunctionIT.java
@@ -0,0 +1,1230 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.it;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+// TODO @Category({LocalStandaloneIT.class, ClusterIT.class})
+// After bug of init MultiInputColumnIntermediateLayer fixed
+@Category({LocalStandaloneIT.class})
+public class IoTDBUDTFBuiltinFunctionIT {
+
+ private static final double E = 0.0001;
+
+ private static final String[] INSERTION_SQLS = {
+ "insert into root.sg.d1(time, s1, s2, s3, s4, s5, s6, s7, s8) values (0,
0, 0, 0, 0, true, '0', 0, 0)",
+ "insert into root.sg.d1(time, s1, s2, s3, s4, s5, s6, s7) values (2, 1, 1,
1, 1, false, '1', 1)",
+ "insert into root.sg.d1(time, s1, s2, s3, s4, s5, s6, s7) values (4, 2, 2,
2, 2, false, '2', 2)",
+ "insert into root.sg.d1(time, s1, s2, s3, s4, s5, s6, s8) values (6, 3, 3,
3, 3, true, '3', 3)",
+ "insert into root.sg.d1(time, s1, s2, s3, s4, s5, s6, s8) values (8, 4, 4,
4, 4, true, '4', 4)",
+ };
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().initBeforeClass();
+ createTimeSeries();
+ generateData();
+ }
+
+ private static void createTimeSeries() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("SET STORAGE GROUP TO root.sg");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s1 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s2 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s3 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s4 with
datatype=DOUBLE,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s5 with
datatype=BOOLEAN,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d1.s6 with
datatype=TEXT,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ private static void generateData() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (String dataGenerationSql : INSERTION_SQLS) {
+ statement.execute(dataGenerationSql);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanAfterClass();
+ }
+
+ @Test
+ public void testMathFunctions() {
+ testMathFunction("sin", Math::sin);
+ testMathFunction("cos", Math::cos);
+ testMathFunction("tan", Math::tan);
+ testMathFunction("asin", Math::asin);
+ testMathFunction("acos", Math::acos);
+ testMathFunction("atan", Math::atan);
+ testMathFunction("sinh", Math::sinh);
+ testMathFunction("cosh", Math::cosh);
+ testMathFunction("tanh", Math::tanh);
+ testMathFunction("degrees", Math::toDegrees);
+ testMathFunction("radians", Math::toRadians);
+ testMathFunction("abs", Math::abs);
+ testMathFunction("sign", Math::signum);
+ testMathFunction("ceil", Math::ceil);
+ testMathFunction("floor", Math::floor);
+ testMathFunction("round", Math::rint);
+ testMathFunction("exp", Math::exp);
+ testMathFunction("ln", Math::log);
+ testMathFunction("log10", Math::log10);
+ testMathFunction("sqrt", Math::sqrt);
+ }
+
+ private interface MathFunctionProxy {
+
+ double invoke(double x);
+ }
+
+ private void testMathFunction(String functionName, MathFunctionProxy
functionProxy) {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4) from root.sg.d1",
+ functionName, functionName, functionName, functionName));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 4, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 4; ++j) {
+ double expected = functionProxy.invoke(i);
+ double actual = Double.parseDouble(resultSet.getString(2 + j));
+ assertEquals(expected, actual, E);
+ }
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testSelectorFunctions() {
+ final String TOP_K = "TOP_K";
+ final String BOTTOM_K = "BOTTOM_K";
+ final String K = "'k'='2'";
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1, %s), %s(s2, %s), %s(s3, %s), %s(s4, %s),
%s(s6, %s) from root.sg.d1",
+ TOP_K, K, TOP_K, K, TOP_K, K, TOP_K, K, TOP_K, K));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = INSERTION_SQLS.length - 2; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 5; ++j) {
+ assertEquals(i, Double.parseDouble(resultSet.getString(2 + j)), E);
+ }
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1, %s), %s(s2, %s), %s(s3, %s), %s(s4, %s),
%s(s6, %s) from root.sg.d1",
+ BOTTOM_K, K, BOTTOM_K, K, BOTTOM_K, K, BOTTOM_K, K,
BOTTOM_K, K));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = 0; i < 2; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 5; ++j) {
+ assertEquals(i, Double.parseDouble(resultSet.getString(2 + j)), E);
+ }
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testStringProcessingFunctions() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ "select STRING_CONTAINS(s6, 's'='0'), STRING_MATCHES(s6,
'regex'='\\d') from root.sg.d1");
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 2, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ if (i == 0) {
+ assertTrue(Boolean.parseBoolean(resultSet.getString(2)));
+ } else {
+ assertFalse(Boolean.parseBoolean(resultSet.getString(2)));
+ }
+ assertTrue(Boolean.parseBoolean(resultSet.getString(2 + 1)));
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testVariationTrendCalculationFunctions() {
+ testVariationTrendCalculationFunction("TIME_DIFFERENCE", 2);
+ testVariationTrendCalculationFunction("DIFFERENCE", 1);
+ testVariationTrendCalculationFunction("NON_NEGATIVE_DIFFERENCE", 1);
+ testVariationTrendCalculationFunction("DERIVATIVE", 0.5);
+ testVariationTrendCalculationFunction("NON_NEGATIVE_DERIVATIVE", 0.5);
+ }
+
+ public void testVariationTrendCalculationFunction(String functionName,
double expected) {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4) from root.sg.d1",
+ functionName, functionName, functionName, functionName));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 4, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length - 1; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 4; ++j) {
+ assertEquals(expected, Double.parseDouble(resultSet.getString(2 +
j)), E);
+ }
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testConstantTimeSeriesGeneratingFunctions() {
+ String[] expected = {
+ "0, 0.0, 0.0, 1024, 3.141592653589793, 2.718281828459045, ",
+ "2, 1.0, null, 1024, 3.141592653589793, 2.718281828459045, ",
+ "4, 2.0, null, 1024, 3.141592653589793, 2.718281828459045, ",
+ "6, null, 3.0, null, null, 2.718281828459045, ",
+ "8, null, 4.0, null, null, 2.718281828459045, ",
+ };
+
+ try (Connection connection = EnvFactory.getEnv().getConnection()) {
+
+ try (Statement statement = connection.createStatement();
+ ResultSet resultSet =
+ statement.executeQuery(
+ "select s7, s8, const(s7, 'value'='1024', 'type'='INT64'),
pi(s7, s7), e(s7, s8, s7, s8) from root.sg.d1")) {
+ assertEquals(1 + 5, resultSet.getMetaData().getColumnCount());
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ StringBuilder actual = new StringBuilder();
+ for (int j = 0; j < 1 + 5; ++j) {
+ actual.append(resultSet.getString(1 + j)).append(", ");
+ }
+ assertEquals(expected[i], actual.toString());
+ }
+
+ assertFalse(resultSet.next());
+ }
+
+ try (Statement statement = connection.createStatement();
+ ResultSet ignored =
+ statement.executeQuery("select const(s7, 'value'='1024') from
root.sg.d1")) {
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("attribute \"type\" is required but
was not provided"));
+ }
+
+ try (Statement statement = connection.createStatement();
+ ResultSet ignored =
+ statement.executeQuery("select const(s8, 'type'='INT64') from
root.sg.d1")) {
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("attribute \"value\" is required
but was not provided"));
+ }
+
+ try (Statement statement = connection.createStatement();
+ ResultSet ignored =
+ statement.executeQuery(
+ "select const(s8, 'value'='1024', 'type'='long') from
root.sg.d1")) {
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("the given value type is not
supported"));
+ }
+
+ try (Statement statement = connection.createStatement();
+ ResultSet ignored =
+ statement.executeQuery(
+ "select const(s8, 'value'='1024e', 'type'='INT64') from
root.sg.d1")) {
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("java.lang.NumberFormatException"));
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testConversionFunction() {
+ String[] expected = {
+ "0, 0, 0.0, 1, 0.0, ",
+ "2, 1, 1.0, 0, 1.0, ",
+ "4, 2, 2.0, 0, 2.0, ",
+ "6, 3, 3.0, 1, null, ",
+ "8, 4, 4.0, 1, null, ",
+ };
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ "select cast(s1, 'type'='TEXT'), cast(s3, 'type'='FLOAT'),
cast(s5, 'type'='INT32'), cast(s7, 'type'='DOUBLE') from root.sg.d1");
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(5, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ StringBuilder actual = new StringBuilder();
+ for (int j = 0; j < 1 + 4; ++j) {
+ actual.append(resultSet.getString(1 + j)).append(", ");
+ }
+ assertEquals(expected[i], actual.toString());
+ }
+ resultSet.close();
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testContinuouslySatisfies() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d2.s1 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d2.s2 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d2.s3 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d2.s4 with
datatype=DOUBLE,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d2.s5 with
datatype=BOOLEAN,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ // create timeseries with only 0,1 values
+ String[] ZERO_ONE_SQL = {
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (0, 0, 0, 0, 0,
false)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (1, 1, 1, 1, 1,
true)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (2, 1, 1, 1, 1,
true)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (3, 0, 0, 0, 0,
false)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (4, 1, 1, 1, 1,
true)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (5, 0, 0, 0, 0,
false)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (6, 0, 0, 0, 0,
false)",
+ "insert into root.sg.d2(time, s1, s2, s3, s4, s5) values (7, 1, 1, 1, 1,
true)",
+ };
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (String dataGenerationSql : ZERO_ONE_SQL) {
+ statement.execute(dataGenerationSql);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ // test ZERO_DURATION
+ // result should be (0,0),(3,0),(5,1)
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ int[] timestamps = {0, 3, 5};
+ int[] durations = {0, 0, 1};
+ String functionName = "zero_duration";
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4), %s(s5) from
root.sg.d2",
+ functionName, functionName, functionName, functionName,
functionName));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = 0; i < timestamps.length; ++i) {
+ resultSet.next();
+ long expectedTimestamp = timestamps[i];
+ long actualTimestamp = Long.parseLong(resultSet.getString(1));
+ assertEquals(expectedTimestamp, actualTimestamp);
+
+ long expectedDuration = durations[i];
+ for (int j = 0; j < 5; ++j) {
+ long actualDuration = Long.parseLong(resultSet.getString(2 + j));
+ assertEquals(expectedDuration, actualDuration);
+ }
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ // test NON_ZERO_DURATION
+ // result should be (1,1),(4,0),(7,0)
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "non_zero_duration";
+ int[] timestamps = {1, 4, 7};
+ int[] durations = {1, 0, 0};
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4), %s(s5) from
root.sg.d2",
+ functionName, functionName, functionName, functionName,
functionName));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = 0; i < timestamps.length; ++i) {
+ resultSet.next();
+ long expectedTimestamp = timestamps[i];
+ long actualTimestamp = Long.parseLong(resultSet.getString(1));
+ assertEquals(expectedTimestamp, actualTimestamp);
+
+ long expectedDuration = durations[i];
+ for (int j = 0; j < 5; ++j) {
+ long actualDuration = Long.parseLong(resultSet.getString(2 + j));
+ assertEquals(expectedDuration, actualDuration);
+ }
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ // test ZERO_COUNT
+ // result should be (0,1),(3,1),(5,2)
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "zero_count";
+ int[] timestamps = {0, 3, 5};
+ int[] durations = {1, 1, 2};
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4), %s(s5) from
root.sg.d2",
+ functionName, functionName, functionName, functionName,
functionName));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = 0; i < timestamps.length; ++i) {
+ resultSet.next();
+ long expectedTimestamp = timestamps[i];
+ long actualTimestamp = Long.parseLong(resultSet.getString(1));
+ assertEquals(expectedTimestamp, actualTimestamp);
+
+ long expectedDuration = durations[i];
+ for (int j = 0; j < 5; ++j) {
+ long actualDuration = Long.parseLong(resultSet.getString(2 + j));
+ assertEquals(expectedDuration, actualDuration);
+ }
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ // test NON_ZERO_COUNT
+ // result should be (1,2),(4,1),(7,1)
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "non_zero_count";
+ int[] timestamps = {1, 4, 7};
+ int[] durations = {2, 1, 1};
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1), %s(s2), %s(s3), %s(s4), %s(s5) from
root.sg.d2",
+ functionName, functionName, functionName, functionName,
functionName));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 5, columnCount);
+
+ for (int i = 0; i < timestamps.length; ++i) {
+ resultSet.next();
+ long expectedTimestamp = timestamps[i];
+ long actualTimestamp = Long.parseLong(resultSet.getString(1));
+ assertEquals(expectedTimestamp, actualTimestamp);
+
+ long expectedDuration = durations[i];
+ for (int j = 0; j < 5; ++j) {
+ long actualDuration = Long.parseLong(resultSet.getString(2 + j));
+ assertEquals(expectedDuration, actualDuration);
+ }
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testOnOffFunction() {
+ Double[] thresholds = {Double.MAX_VALUE, -1.0, 0.0, 1.0, Double.MAX_VALUE};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (Double threshold : thresholds) {
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select on_off(s1,'threshold'='%f'),
on_off(s2,'threshold'='%f'), on_off(s3,'threshold'='%f'),
on_off(s4,'threshold'='%f') from root.sg.d1",
+ threshold, threshold, threshold, threshold));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 4, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 4; ++j) {
+ Boolean expected = i >= threshold;
+ Boolean actual = Boolean.parseBoolean(resultSet.getString(2 + j));
+ assertEquals(expected, actual);
+ }
+ }
+ resultSet.close();
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testInRange() {
+ Double[] lowers = {-1.0, 0.0, 1.5, 2.0, 4.0};
+ Double[] uppers = {0.0, 2.0, 4.5, 2.0, 1.0};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (int k = 0; k < lowers.length; ++k) {
+ Double lower = lowers[k];
+ Double upper = uppers[k];
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select in_range(s1,'upper'='%f','lower'='%f'),
in_range(s2,'upper'='%f','lower'='%f'), "
+ + "in_range(s3,'upper'='%f','lower'='%f'),
in_range(s4,'upper'='%f','lower'='%f') from root.sg.d1",
+ upper, lower, upper, lower, upper, lower, upper, lower));
+
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 4, columnCount);
+
+ for (int i = 0; i < INSERTION_SQLS.length; ++i) {
+ resultSet.next();
+ for (int j = 0; j < 4; ++j) {
+ Boolean expected = (i >= lower && i <= upper);
+ Boolean actual = Boolean.parseBoolean(resultSet.getString(2 + j));
+ assertEquals(expected, actual);
+ }
+ }
+ resultSet.close();
+ }
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("Upper can not be smaller than
lower."));
+ }
+ }
+
+ @Test
+ public void testEqualBucketSampleForRandom() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d5.s1 with
datatype=INT32,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ String[] SQL_FOR_SAMPLE_S1 = new String[100];
+ for (int i = 0; i < 100; i++) {
+ SQL_FOR_SAMPLE_S1[i] =
+ String.format("insert into root.sg.d5(time, s1) values (%d, %d)", i,
i);
+ }
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (int i = 0; i < 100; i++) {
+ statement.execute(SQL_FOR_SAMPLE_S1[i]);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "EQUAL_SIZE_BUCKET_RANDOM_SAMPLE";
+ double proportionValue = 0.1;
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select " + "%s(s1, 'proportion'='%f') from root.sg.d5",
+ functionName, proportionValue));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount);
+ int count = 0;
+ while (resultSet.next()) {
+ count++;
+ }
+ assertEquals(10, count);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void testEqualBucketSampleForAgg() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d4.s1 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d4.s2 with
datatype=DOUBLE,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d4.s3 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d4.s4 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d4.s5 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d4.s6 with
datatype=DOUBLE,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ String[] SQL_FOR_SAMPLE_S1 = new String[100];
+ String[] SQL_FOR_SAMPLE_S2 = new String[100];
+ String[] SQL_FOR_SAMPLE_S3 = new String[100];
+ String[] SQL_FOR_SAMPLE_S4 = new String[100];
+ String[] SQL_FOR_SAMPLE_S5 = new String[100];
+ String[] SQL_FOR_SAMPLE_S6 = new String[100];
+
+ for (int i = 0; i < 100; i++) {
+ SQL_FOR_SAMPLE_S1[i] =
+ String.format("insert into root.sg.d4(time, s1) values (%d, %f)", i,
i * 1.0);
+ SQL_FOR_SAMPLE_S2[i] =
+ String.format("insert into root.sg.d4(time, s2) values (%d, %f)", i,
i * 1.0);
+ SQL_FOR_SAMPLE_S3[i] =
+ String.format("insert into root.sg.d4(time, s3) values (%d, %d)", i,
i);
+ SQL_FOR_SAMPLE_S4[i] =
+ String.format("insert into root.sg.d4(time, s4) values (%d, %d)", i,
i);
+ SQL_FOR_SAMPLE_S5[i] =
+ String.format("insert into root.sg.d4(time, s5) values (%d, %d)", i,
-i);
+ SQL_FOR_SAMPLE_S6[i] =
+ String.format("insert into root.sg.d4(time, s6) values (%d, %f)", i,
i * 1.0);
+ }
+ float[] ANSWER1 =
+ new float[] {4.5F, 14.5F, 24.5F, 34.5F, 44.5F, 54.5F, 64.5F, 74.5F,
84.5F, 94.5F};
+ double[] ANSWER2 = new double[] {0, 10, 20, 30, 40, 50, 60, 70, 80, 90};
+ long[] ANSWER3 = new long[] {9, 19, 29, 39, 49, 59, 69, 79, 89, 99};
+ long[] ANSWER4 = new long[] {45, 145, 245, 345, 445, 545, 645, 745, 845,
945};
+ int[] ANSWER5 = new int[] {-9, -19, -29, -39, -49, -59, -69, -79, -89,
-99};
+ double[] ANSWER6 = new double[] {8.25, 8.25, 8.25, 8.25, 8.25, 8.25, 8.25,
8.25, 8.25, 8.25};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (int i = 0; i < 100; i++) {
+ statement.execute(SQL_FOR_SAMPLE_S1[i]);
+ statement.execute(SQL_FOR_SAMPLE_S2[i]);
+ statement.execute(SQL_FOR_SAMPLE_S3[i]);
+ statement.execute(SQL_FOR_SAMPLE_S4[i]);
+ statement.execute(SQL_FOR_SAMPLE_S5[i]);
+ statement.execute(SQL_FOR_SAMPLE_S6[i]);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "EQUAL_SIZE_BUCKET_AGG_SAMPLE";
+ double proportionValue = 0.1;
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s1, 'proportion'='%f'), "
+ + "%s(s2, 'type'='%s', 'proportion'='%f'), "
+ + "%s(s3, 'type'='%s', 'proportion'='%f'), "
+ + "%s(s4, 'type'='%s', 'proportion'='%f'), "
+ + "%s(s5, 'type'='%s', 'proportion'='%f'), "
+ + "%s(s6, 'type'='%s', 'proportion'='%f')"
+ + "from root.sg.d4",
+ functionName,
+ proportionValue,
+ functionName,
+ "min",
+ proportionValue,
+ functionName,
+ "max",
+ proportionValue,
+ functionName,
+ "sum",
+ proportionValue,
+ functionName,
+ "extreme",
+ proportionValue,
+ functionName,
+ "variance",
+ proportionValue));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 6, columnCount);
+ for (int i = 0; i < 10; i++) {
+ resultSet.next();
+ assertEquals(ANSWER1[i], resultSet.getDouble(2), 0.01);
+ assertEquals(ANSWER2[i], resultSet.getDouble(3), 0.01);
+ assertEquals(ANSWER3[i], resultSet.getLong(4));
+ assertEquals(ANSWER4[i], resultSet.getLong(5));
+ assertEquals(ANSWER5[i], resultSet.getInt(6));
+ assertEquals(ANSWER6[i], resultSet.getDouble(7), 0.01);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void testEqualBucketSampleForM4() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d3.s1 with
datatype=INT32,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ String[] SQL_FOR_SAMPLE = new String[100];
+ for (int i = 0; i < 100; i++) {
+ SQL_FOR_SAMPLE[i] =
+ String.format("insert into root.sg.d3(time, s1) values (%d, %d)", i,
i + 1);
+ }
+ int[] ANSWER1 = new int[] {1, 2, 39, 40, 41, 42, 79, 80, 81, 82, 99, 100};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (String dataGenerationSql : SQL_FOR_SAMPLE) {
+ statement.execute(dataGenerationSql);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "EQUAL_SIZE_BUCKET_M4_SAMPLE";
+ String methodName = "m4";
+ double proportionValue = 0.1;
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1, 'method'='%s', 'proportion'='%f') from
root.sg.d3",
+ functionName, methodName, proportionValue));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount);
+ for (int j : ANSWER1) {
+ resultSet.next();
+ assertEquals(j, resultSet.getInt(2));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void testEqualBucketSampleForOutlier() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d6.s1 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d6.s2 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d6.s3 with
datatype=DOUBLE,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d6.s4 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d6.s5 with
datatype=FLOAT,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ String[] SQL_FOR_SAMPLE_S1 = new String[100];
+ String[] SQL_FOR_SAMPLE_S2 = new String[100];
+ String[] SQL_FOR_SAMPLE_S3 = new String[100];
+ String[] SQL_FOR_SAMPLE_S4 = new String[100];
+ String[] SQL_FOR_SAMPLE_S5 = new String[20];
+ for (int i = 0; i < 100; i++) {
+ SQL_FOR_SAMPLE_S1[i] =
+ String.format(
+ "insert into root.sg.d6(time, s1) values (%d, %d)",
+ i, i % 5 == 0 && i % 10 != 0 ? i + 100 : i);
+ SQL_FOR_SAMPLE_S2[i] =
+ String.format(
+ "insert into root.sg.d6(time, s2) values (%d, %d)", i, i % 10 ==
6 ? i + 100 : i);
+ if (i % 10 == 9 || i % 20 == 0) {
+ SQL_FOR_SAMPLE_S2[i] = String.format("insert into root.sg.d6(time, s2)
values (%d, 0)", i);
+ }
+ SQL_FOR_SAMPLE_S3[i] =
+ String.format(
+ "insert into root.sg.d6(time, s3) values (%d, %d)", i, i % 10 ==
7 ? i + 100 : i);
+ SQL_FOR_SAMPLE_S4[i] =
+ String.format(
+ "insert into root.sg.d6(time, s4) values (%d, %d)", i, i % 10 ==
8 ? i + 100 : i);
+ }
+ for (int i = 0; i < 20; i++) {
+ SQL_FOR_SAMPLE_S5[i] =
+ String.format("insert into root.sg.d6(time, s5) values (%d, %d)", i,
i);
+ }
+ int[] ANSWER1 = new int[] {105, 115, 125, 135, 145, 155, 165, 175, 185,
195};
+ long[] ANSWER2 = new long[] {106, 116, 126, 136, 146, 156, 166, 176, 186,
196};
+ double[] ANSWER3 = new double[] {107, 117, 127, 137, 147, 157, 167, 177,
187, 197};
+ float[] ANSWER4 = new float[] {108, 118, 128, 138, 148, 158, 168, 178,
188, 198};
+ float[] ANSWER5 = new float[] {0, 2, 4, 6, 8, 10, 12, 14, 16, 18};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (int i = 0; i < 100; i++) {
+ statement.execute(SQL_FOR_SAMPLE_S1[i]);
+ statement.execute(SQL_FOR_SAMPLE_S2[i]);
+ statement.execute(SQL_FOR_SAMPLE_S3[i]);
+ statement.execute(SQL_FOR_SAMPLE_S4[i]);
+ }
+ for (int i = 0; i < 20; i++) {
+ statement.execute(SQL_FOR_SAMPLE_S5[i]);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "EQUAL_SIZE_BUCKET_OUTLIER_SAMPLE";
+ double proportionValue = 0.1;
+
+ ResultSet resultSet0 =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s1, 'proportion'='%f', 'type'='%s', 'number'='%d')
"
+ + "from root.sg.d6",
+ functionName, proportionValue, "avg", 2));
+ int columnCount0 = resultSet0.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount0);
+ for (int i = 0; i < 10; i++) {
+ resultSet0.next();
+ assertEquals(ANSWER1[i], resultSet0.getInt(2));
+ }
+
+ ResultSet resultSet1 =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s2, 'proportion'='%f', 'type'='%s', 'number'='%d')
"
+ + "from root.sg.d6",
+ functionName, proportionValue, "stendis", 2));
+ int columnCount1 = resultSet1.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount1);
+ for (int i = 0; i < 10; i++) {
+ resultSet1.next();
+ assertEquals(ANSWER2[i], resultSet1.getLong(2));
+ }
+
+ ResultSet resultSet2 =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s3, 'proportion'='%f', 'type'='%s', 'number'='%d')
"
+ + "from root.sg.d6",
+ functionName, proportionValue, "cos", 2));
+ int columnCount2 = resultSet2.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount2);
+ for (int i = 0; i < 10; i++) {
+ resultSet2.next();
+ assertEquals(ANSWER3[i], resultSet2.getDouble(2), 0.01);
+ }
+
+ ResultSet resultSet3 =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s4, 'proportion'='%f', 'type'='%s', 'number'='%d')
"
+ + "from root.sg.d6",
+ functionName, proportionValue, "prenextdis", 2));
+ int columnCount3 = resultSet3.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount3);
+ for (int i = 0; i < 10; i++) {
+ resultSet3.next();
+ assertEquals(ANSWER4[i], resultSet3.getFloat(2), 0.01);
+ }
+
+ ResultSet resultSet4 =
+ statement.executeQuery(
+ String.format(
+ "select "
+ + "%s(s5, 'proportion'='%f', 'type'='%s', 'number'='%d')
"
+ + "from root.sg.d6",
+ functionName, 0.5, "cos", 1));
+ int columnCount4 = resultSet4.getMetaData().getColumnCount();
+ assertEquals(1 + 1, columnCount4);
+ for (int i = 0; i < 10; i++) {
+ resultSet4.next();
+ assertEquals(ANSWER5[i], resultSet4.getFloat(2), 0.01);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void testUDTFJexl() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TIMESERIES root.sg.d7.s1 with
datatype=INT32,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s2 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s3 with
datatype=DOUBLE,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s4 with
datatype=TEXT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s5 with
datatype=BOOLEAN,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s6 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s7 with
datatype=INT64,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s8 with
datatype=FLOAT,encoding=PLAIN");
+ statement.execute("CREATE TIMESERIES root.sg.d7.s9 with
datatype=TEXT,encoding=PLAIN");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ String[] SQL_FOR_SAMPLE_1 = new String[5];
+ String[] SQL_FOR_SAMPLE_2 = new String[5];
+ String[] SQL_FOR_SAMPLE_3 = new String[5];
+ String[] SQL_FOR_SAMPLE_4 = new String[5];
+ String[] SQL_FOR_SAMPLE_5 = new String[5];
+ String[] SQL_FOR_SAMPLE_6 = new String[5];
+ String[] SQL_FOR_SAMPLE_7 = new String[5];
+ String[] SQL_FOR_SAMPLE_8 = new String[5];
+ String[] SQL_FOR_SAMPLE_9 = new String[5];
+ for (int i = 0; i < 5; i++) {
+ SQL_FOR_SAMPLE_1[i] =
+ String.format("insert into root.sg.d7(time, s1) values (%d, %d)", i,
i + 1);
+ SQL_FOR_SAMPLE_2[i] =
+ String.format("insert into root.sg.d7(time, s2) values (%d, %f)", i,
i + 1.0);
+ SQL_FOR_SAMPLE_3[i] =
+ String.format("insert into root.sg.d7(time, s3) values (%d, %f)", i,
i + 1.0);
+ SQL_FOR_SAMPLE_4[i] =
+ String.format("insert into root.sg.d7(time, s4) values (%d, '%s')",
i, "string");
+ SQL_FOR_SAMPLE_5[i] = String.format("insert into root.sg.d7(time, s5)
values (%d, true)", i);
+ SQL_FOR_SAMPLE_6[i] =
+ String.format("insert into root.sg.d7(time, s6) values (%d, %d)", i,
i + 8);
+ SQL_FOR_SAMPLE_7[i] =
+ String.format("insert into root.sg.d7(time, s7) values (%d, %d)", i,
i + 1);
+ SQL_FOR_SAMPLE_8[i] =
+ String.format("insert into root.sg.d7(time, s8) values (%d, %f)", i,
i + 1.0);
+ SQL_FOR_SAMPLE_9[i] =
+ String.format("insert into root.sg.d7(time, s9) values (%d, '%s')",
i, "string");
+ }
+ double[] ANSWER1 = new double[] {2, 4, 6, 8, 10};
+ double[] ANSWER2 = new double[] {2, 4, 6, 8, 10};
+ double[] ANSWER3 = new double[] {4, 7, 10, 13, 16};
+ String[] ANSWER4 = new String[] {"string2", "string2", "string2",
"string2", "string2"};
+ double[] ANSWER7 = new double[] {1, 4, 9, 16, 25};
+ String[] ANSWER8 = new String[] {"string1", "string4", "string9",
"string16", "string25"};
+ double[] ANSWER9 = new double[] {2, 9, 28, 65, 126};
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ for (int i = 0; i < 5; i++) {
+ statement.execute(SQL_FOR_SAMPLE_1[i]);
+ statement.execute(SQL_FOR_SAMPLE_2[i]);
+ statement.execute(SQL_FOR_SAMPLE_3[i]);
+ statement.execute(SQL_FOR_SAMPLE_4[i]);
+ statement.execute(SQL_FOR_SAMPLE_5[i]);
+ statement.execute(SQL_FOR_SAMPLE_6[i]);
+ statement.execute(SQL_FOR_SAMPLE_7[i]);
+ statement.execute(SQL_FOR_SAMPLE_8[i]);
+ statement.execute(SQL_FOR_SAMPLE_9[i]);
+ }
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ String functionName = "JEXL";
+ String expr1 = "x -> {2 * x}";
+ String expr2 = "x -> {x + x}";
+ String expr3 = "x -> {x * 3 + 1}";
+ String expr4 = "x -> {x + 2}";
+ String expr5 = "x -> {x == true}";
+ String expr6 = "x -> {x == x}";
+ String expr7 = "(x, y) -> {x * y}";
+ String expr8 = "(x, y, z) -> {x + y * z}";
+ String expr9 = "(x, y, z, a) -> {x * y * z + (a ? 1 : -1)}";
+ ResultSet resultSet =
+ statement.executeQuery(
+ String.format(
+ "select %s(s1, 'expr'='%s'), "
+ + "%s(s2, 'expr'='%s'), "
+ + "%s(s3, 'expr'='%s'), "
+ + "%s(s4, 'expr'='%s'), "
+ + "%s(s5, 'expr'='%s'), "
+ + "%s(s6, 'expr'='%s'), "
+ + "%s(s7, s8, 'expr'='%s'), "
+ + "%s(s4, s7, s1, 'expr'='%s'), "
+ + "%s(s1, s7, s8, s5, 'expr'='%s') "
+ + "from root.sg.d7",
+ functionName,
+ expr1,
+ functionName,
+ expr2,
+ functionName,
+ expr3,
+ functionName,
+ expr4,
+ functionName,
+ expr5,
+ functionName,
+ expr6,
+ functionName,
+ expr7,
+ functionName,
+ expr8,
+ functionName,
+ expr9));
+ int columnCount = resultSet.getMetaData().getColumnCount();
+ assertEquals(1 + 9, columnCount);
+ for (int i = 0; i < 5; i++) {
+ resultSet.next();
+ assertEquals(ANSWER1[i], resultSet.getDouble(2), 0.01);
+ assertEquals(ANSWER2[i], resultSet.getDouble(3), 0.01);
+ assertEquals(ANSWER3[i], resultSet.getDouble(4), 0.01);
+ assertEquals(ANSWER4[i], resultSet.getString(5));
+ assertTrue(resultSet.getBoolean(6));
+ assertTrue(resultSet.getBoolean(7));
+ assertEquals(ANSWER7[i], resultSet.getDouble(8), 0.01);
+ assertEquals(ANSWER8[i], resultSet.getString(9));
+ assertEquals(ANSWER9[i], resultSet.getDouble(10), 0.01);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void testStringFunctions() {
+ String[] createSqls =
+ new String[] {
+ "SET STORAGE GROUP TO root.testStringFunctions",
+ "CREATE TIMESERIES root.testStringFunctions.d1.s1 WITH
DATATYPE=TEXT, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.testStringFunctions.d1.s2 WITH
DATATYPE=TEXT, ENCODING=PLAIN",
+ };
+
+ String[] insertSqls =
+ new String[] {
+ "INSERT INTO root.testStringFunctions.d1(timestamp,s1,s2) values(1,
\"1111test1111\", \" 1111test1111 \")",
+ "INSERT INTO root.testStringFunctions.d1(timestamp,s1) values(2,
\"2222test2222\")"
+ };
+
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+
+ for (String createSql : createSqls) {
+ statement.execute(createSql);
+ }
+
+ for (String insertSql : insertSqls) {
+ // TODO statement.addBatch(insertSql);
+ statement.execute(insertSql);
+ }
+ // TODO statement.executeBatch();
+
+ testStrLength(statement);
+ testStrLocate(statement);
+ testStartsWith(statement);
+ testEndsWith(statement);
+ testConcat(statement);
+ testSubStr(statement);
+ testUpper(statement);
+ testLower(statement);
+ testTrim(statement);
+ testStrCmp(statement);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ private void testTrim(Statement statement) {
+ String s2;
+ try (ResultSet resultSet =
+ statement.executeQuery("select s2, trim(s2) " + "from
root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ s2 = resultSet.getString(3);
+ if (s2 == null) {
+ continue;
+ }
+ assertEquals(resultSet.getString(2).trim(), resultSet.getString(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testStrCmp(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, s2, strcmp(s1, s2) " + "from
root.testStringFunctions.d1")) {
+ String s1, s2;
+ while (resultSet.next()) {
+ s1 = resultSet.getString(2);
+ s2 = resultSet.getString(3);
+ if (s1 == null || s2 == null) {
+ continue;
+ }
+ assertEquals(s1.compareTo(s2), resultSet.getInt(4));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testLower(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery("select s1, lower(s1) " + "from
root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).toLowerCase(),
resultSet.getString(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testUpper(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery("select s1, upper(s1) " + "from
root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).toUpperCase(),
resultSet.getString(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testSubStr(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, substr(s1, \"start\"=\"3\", \"end\"=\"7\") "
+ + "from root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).substring(3, 7),
resultSet.getString(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testConcat(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, s2, "
+ + "concat(s1, s2, \"target1\"=\"IoT\", \"target2\"=\"DB\"), "
+ + "concat(s1, s2, \"target1\"=\"IoT\", \"target2\"=\"DB\",
\"series_behind\"=\"true\") "
+ + "from root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(
+ (resultSet.getString(2) + resultSet.getString(3) +
"IoTDB").replace("null", ""),
+ resultSet.getString(4));
+ assertEquals(
+ ("IoTDB" + (resultSet.getString(2) +
resultSet.getString(3))).replace("null", ""),
+ resultSet.getString(5));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testEndsWith(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, endsWith(s1, \"target\"=\"1111\") " + "from
root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).endsWith("1111"),
resultSet.getBoolean(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testStartsWith(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, startsWith(s1, \"target\"=\"1111\") "
+ + "from root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).startsWith("1111"),
resultSet.getBoolean(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testStrLocate(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery(
+ "select s1, locate(s1, \"target\"=\"1111\"), locate(s1,
\"target\"=\"1111\", \"reverse\"=\"true\") from root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).indexOf("1111"),
resultSet.getInt(3));
+ assertEquals(resultSet.getString(2).lastIndexOf("1111"),
resultSet.getInt(4));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ private void testStrLength(Statement statement) {
+ try (ResultSet resultSet =
+ statement.executeQuery("select s1, length(s1) from
root.testStringFunctions.d1")) {
+ while (resultSet.next()) {
+ assertEquals(resultSet.getString(2).length(), resultSet.getInt(3));
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
index 4208735bcb..a5b72955f8 100644
---
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
@@ -19,6 +19,17 @@
package org.apache.iotdb.commons.udf.builtin;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFConcat;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFEndsWith;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFLower;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFStartsWith;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFStrCompare;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFStrLength;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFStrLocate;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFSubstr;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFTrim;
+import org.apache.iotdb.commons.udf.builtin.String.UDTFUpper;
+
/** All built-in UDFs need to register their function names and classes here.
*/
public enum BuiltinTimeSeriesGeneratingFunction {
CONST("CONST", UDTFConst.class),
@@ -46,6 +57,16 @@ public enum BuiltinTimeSeriesGeneratingFunction {
SQRT("SQRT", UDTFSqrt.class),
STRING_CONTAINS("STRING_CONTAINS", UDTFContains.class),
STRING_MATCHES("STRING_MATCHES", UDTFMatches.class),
+ STRING_LENGTH("LENGTH", UDTFStrLength.class),
+ STRING_LOCATE("LOCATE", UDTFStrLocate.class),
+ STRING_STARTS_WITH("STARTSWITH", UDTFStartsWith.class),
+ STRING_ENDS_WITH("ENDSWITH", UDTFEndsWith.class),
+ STRING_CONCAT("CONCAT", UDTFConcat.class),
+ STRING_SUBSTR("SUBSTR", UDTFSubstr.class),
+ STRING_UPPER("UPPER", UDTFUpper.class),
+ STRING_LOWER("LOWER", UDTFLower.class),
+ STRING_TRIM("TRIM", UDTFTrim.class),
+ STRING_CMP("STRCMP", UDTFStrCompare.class),
DIFFERENCE("DIFFERENCE", UDTFCommonValueDifference.class),
NON_NEGATIVE_DIFFERENCE("NON_NEGATIVE_DIFFERENCE",
UDTFNonNegativeValueDifference.class),
TIME_DIFFERENCE("TIME_DIFFERENCE", UDTFTimeDifference.class),
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFConcat.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFConcat.java
new file mode 100644
index 0000000000..03af33f7e8
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFConcat.java
@@ -0,0 +1,76 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function Returns the concat string by input series and targets.
+startsEnd: Indicates whether series behind targets. The default value is
false.*/
+public class UDTFConcat implements UDTF {
+
+ private boolean seriesBehind;
+ private final StringBuilder concatTargets = new StringBuilder();
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ int size = validator.getParameters().getChildExpressions().size();
+ for (int i = 0; i < size; i++) {
+ validator.validateInputSeriesDataType(i, TSDataType.TEXT);
+ }
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ parameters
+ .getAttributes()
+ .forEach(
+ (key, value) -> {
+ if (key.startsWith("target") && value != null)
concatTargets.append(value);
+ });
+ seriesBehind = parameters.getBooleanOrDefault("series_behind", false);
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.TEXT);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ StringBuilder concatSeries = new StringBuilder();
+ for (int i = 0; i < row.size(); i++) {
+ if (row.isNull(i)) {
+ continue;
+ }
+ concatSeries.append(row.getString(i));
+ }
+
+ collector.putString(
+ row.getTime(),
+ seriesBehind
+ ? concatSeries.insert(0, concatTargets).toString()
+ : concatSeries.append(concatTargets).toString());
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFEndsWith.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFEndsWith.java
new file mode 100644
index 0000000000..2f267e7b23
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFEndsWith.java
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns if input series ends with the specified suffix.*/
+public class UDTFEndsWith implements UDTF {
+
+ private String target;
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ target = parameters.getString("target");
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.BOOLEAN);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putBoolean(row.getTime(), row.getString(0).endsWith(target));
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFLower.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFLower.java
new file mode 100644
index 0000000000..0a1db7d92c
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFLower.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*Returns a string with all characters of target changed to lowercase, or NULL
if target is NULL*/
+public class UDTFLower implements UDTF {
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.TEXT);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putString(row.getTime(), row.getString(0).toLowerCase());
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStartsWith.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStartsWith.java
new file mode 100644
index 0000000000..2382473410
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStartsWith.java
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns if input series starts with the specified prefix.*/
+public class UDTFStartsWith implements UDTF {
+ private String target;
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ target = parameters.getString("target");
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.BOOLEAN);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putBoolean(row.getTime(), row.getString(0).startsWith(target));
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrCompare.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrCompare.java
new file mode 100644
index 0000000000..52b8366434
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrCompare.java
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns 0 if targets are the same, -1 if targtet1 is smaller
than targtet2,
+and NULL if either argument is NULL. It returns 1 otherwise.*/
+public class UDTFStrCompare implements UDTF {
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator
+ .validateInputSeriesNumber(2)
+ .validateInputSeriesDataType(0, TSDataType.TEXT)
+ .validateInputSeriesDataType(1, TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.INT32);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ if (row.isNull(0) || row.isNull(1)) {
+ return;
+ }
+ collector.putInt(row.getTime(),
row.getString(0).compareTo(row.getString(1)));
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLength.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLength.java
new file mode 100644
index 0000000000..9e546615ae
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLength.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns length of string from an input series.*/
+public class UDTFStrLength implements UDTF {
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.INT32);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putInt(row.getTime(), row.getString(0).length());
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLocate.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLocate.java
new file mode 100644
index 0000000000..6fe7b59fe6
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFStrLocate.java
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns position of target in an input series.*/
+public class UDTFStrLocate implements UDTF {
+ private String target;
+ private boolean reverse;
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator
+ .validateInputSeriesNumber(1)
+ .validateInputSeriesDataType(0, TSDataType.TEXT)
+ .validate(
+ target -> ((String) target).length() > 0,
+ "target should not be empty",
+ validator.getParameters().getString("target"));
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ target = parameters.getString("target");
+ reverse = parameters.getBooleanOrDefault("reverse", false);
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.INT32);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putInt(
+ row.getTime(),
+ reverse ? row.getString(0).lastIndexOf(target) :
row.getString(0).indexOf(target));
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java
new file mode 100644
index 0000000000..4b8673955c
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java
@@ -0,0 +1,72 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function return a substring from target string, starting at position
start and ending at position end - 1.
+If parameter "end" is not existed or more than length of target, return the
substring from start to end of target.*/
+public class UDTFSubstr implements UDTF {
+
+ int start;
+ int end;
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ int start = validator.getParameters().getInt("start");
+ validator
+ .validateInputSeriesNumber(1)
+ .validateInputSeriesDataType(0, TSDataType.TEXT)
+ .validate(
+ startPosition -> ((int) startPosition) >= 0,
+ "start should be more or equal than 0",
+ start)
+ .validate(
+ end -> ((int) end) >= start,
+ "end should be more or equal than start",
+ validator.getParameters().getIntOrDefault("end",
Integer.MAX_VALUE));
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ start = parameters.getInt("start");
+ end = parameters.getIntOrDefault("end", Integer.MAX_VALUE);
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.TEXT);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ String series = row.getString(0);
+ collector.putString(
+ row.getTime(),
+ (end >= series.length())
+ ? row.getString(0).substring(start)
+ : row.getString(0).substring(start, end));
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFTrim.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFTrim.java
new file mode 100644
index 0000000000..2c30dc2541
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFTrim.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*This function returns the string whose value is target, with all leading and
trailing space removed.*/
+public class UDTFTrim implements UDTF {
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.TEXT);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putString(row.getTime(), row.getString(0).trim());
+ }
+}
diff --git
a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFUpper.java
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFUpper.java
new file mode 100644
index 0000000000..829756939a
--- /dev/null
+++
b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFUpper.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.iotdb.commons.udf.builtin.String;
+
+import org.apache.iotdb.commons.udf.api.UDTF;
+import org.apache.iotdb.commons.udf.api.access.Row;
+import org.apache.iotdb.commons.udf.api.collector.PointCollector;
+import org.apache.iotdb.commons.udf.api.customizer.config.UDTFConfigurations;
+import
org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameterValidator;
+import org.apache.iotdb.commons.udf.api.customizer.parameter.UDFParameters;
+import
org.apache.iotdb.commons.udf.api.customizer.strategy.RowByRowAccessStrategy;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+
+/*Returns a string with all characters of target changed to uppercase, or NULL
if target is NULL.*/
+public class UDTFUpper implements UDTF {
+
+ @Override
+ public void validate(UDFParameterValidator validator) throws Exception {
+ validator.validateInputSeriesNumber(1).validateInputSeriesDataType(0,
TSDataType.TEXT);
+ }
+
+ @Override
+ public void beforeStart(UDFParameters parameters, UDTFConfigurations
configurations)
+ throws Exception {
+ configurations
+ .setAccessStrategy(new RowByRowAccessStrategy())
+ .setOutputDataType(TSDataType.TEXT);
+ }
+
+ @Override
+ public void transform(Row row, PointCollector collector) throws Exception {
+ collector.putString(row.getTime(), row.getString(0).toUpperCase());
+ }
+}
